PhoenixKit V40 Migration: UUID Column Addition for Legacy Tables

This migration adds UUID columns to all legacy tables that currently use bigserial primary keys. This is Phase 1 of the graceful UUID migration strategy, designed to be completely non-breaking for existing installations.

Strategy

This migration is designed to work with PhoenixKit as a library dependency:

• Non-breaking: Only adds new columns, doesn't change existing PKs
• Backward compatible: Existing code using integer IDs continues to work
• Forward compatible: New code can start using UUIDs immediately
• Optional module aware: Skips tables that don't exist (disabled modules)

Changes

For each of the 33 legacy tables:

1. Adds a uuid column (UUID type, using UUIDv7 for time-ordering)
2. Backfills existing records with generated UUIDv7 values
3. Creates a unique index on the uuid column
4. Sets the column to NOT NULL after backfill
5. Keeps DEFAULT for database-level inserts (Ecto changesets override with UUIDv7)

UUIDv7

This migration uses UUIDv7 (time-ordered UUIDs) which provide:

• Time-based ordering (first 48 bits are Unix timestamp in milliseconds)
• Better index locality than random UUIDs
• Sortable by creation time
• Compatible with standard UUID format

A PostgreSQL function uuid_generate_v7() is created to generate UUIDv7 values at the database level.

Tables Affected

Core Auth (V01)

• phoenix_kit_users
• phoenix_kit_users_tokens
• phoenix_kit_user_roles
• phoenix_kit_user_role_assignments

Settings & Referrals (V03-V04)

• phoenix_kit_settings
• phoenix_kit_referral_codes
• phoenix_kit_referral_code_usage

Email System (V07, V09, V15, V22)

• phoenix_kit_email_logs
• phoenix_kit_email_events
• phoenix_kit_email_blocklist
• phoenix_kit_email_templates
• phoenix_kit_email_orphaned_events
• phoenix_kit_email_metrics

OAuth (V16)

• phoenix_kit_user_oauth_providers

Entities (V17)

• phoenix_kit_entities
• phoenix_kit_entity_data

Audit (V22)

• phoenix_kit_audit_logs

Billing (V31, V33)

• phoenix_kit_currencies
• phoenix_kit_billing_profiles
• phoenix_kit_orders
• phoenix_kit_invoices
• phoenix_kit_transactions
• phoenix_kit_payment_methods
• phoenix_kit_subscription_plans
• phoenix_kit_subscriptions
• phoenix_kit_payment_provider_configs
• phoenix_kit_webhook_events

AI System (V32, V34, V38)

• phoenix_kit_ai_endpoints
• phoenix_kit_ai_requests
• phoenix_kit_ai_prompts

DB Sync (V37)

• phoenix_kit_db_sync_connections
• phoenix_kit_db_sync_transfers

Admin Notes (V39)

• phoenix_kit_admin_notes

Performance Considerations

• Uses batched updates for large tables to avoid long locks
• Checks table existence before migration (for optional modules)
• UUIDv7 provides better index performance than random UUIDs

Usage

# Migrate up
PhoenixKit.Migrations.Postgres.up(prefix: "public", version: 40)

# Rollback
PhoenixKit.Migrations.Postgres.down(prefix: "public", version: 39)