Packages
phoenix_kit
1.7.37
1.7.210
1.7.209
1.7.208
1.7.207
1.7.206
1.7.205
1.7.204
1.7.203
1.7.202
1.7.201
1.7.200
1.7.199
1.7.198
1.7.197
1.7.196
1.7.194
1.7.193
1.7.192
1.7.191
1.7.190
1.7.189
1.7.187
1.7.186
1.7.185
1.7.184
1.7.183
1.7.182
1.7.181
1.7.180
1.7.179
1.7.178
1.7.177
1.7.176
1.7.175
1.7.174
1.7.173
1.7.172
1.7.171
1.7.170
1.7.169
1.7.168
1.7.167
1.7.166
1.7.165
1.7.164
1.7.162
1.7.161
1.7.160
1.7.159
1.7.157
1.7.156
1.7.155
1.7.154
1.7.153
1.7.152
1.7.151
1.7.150
1.7.149
1.7.146
1.7.145
1.7.144
1.7.143
1.7.138
1.7.133
1.7.132
1.7.131
1.7.130
1.7.128
1.7.126
1.7.125
1.7.121
1.7.120
1.7.119
1.7.118
1.7.117
1.7.116
1.7.115
1.7.114
1.7.113
1.7.112
1.7.111
1.7.110
1.7.109
1.7.108
1.7.107
1.7.106
1.7.105
1.7.104
1.7.103
1.7.102
1.7.101
1.7.100
1.7.99
1.7.98
1.7.97
1.7.96
1.7.95
1.7.94
1.7.93
1.7.92
1.7.91
1.7.90
1.7.89
1.7.88
1.7.87
1.7.86
1.7.85
1.7.84
1.7.83
1.7.82
1.7.81
1.7.80
1.7.79
1.7.78
1.7.77
1.7.76
1.7.75
1.7.74
1.7.71
1.7.70
1.7.69
1.7.66
1.7.65
1.7.64
1.7.63
1.7.62
1.7.61
1.7.59
1.7.58
1.7.57
1.7.56
1.7.55
1.7.54
1.7.53
1.7.52
1.7.51
1.7.49
1.7.44
1.7.43
1.7.42
1.7.41
1.7.39
1.7.38
1.7.37
1.7.36
1.7.34
1.7.33
1.7.31
1.7.30
1.7.29
1.7.28
1.7.27
1.7.26
1.7.25
1.7.24
1.7.23
1.7.22
1.7.21
1.7.20
1.7.19
1.7.18
1.7.17
1.7.16
1.7.15
1.7.14
1.7.13
1.7.12
1.7.11
1.7.10
1.7.9
1.7.8
1.7.7
1.7.6
1.7.5
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.20
1.6.19
1.6.18
1.6.17
1.6.16
1.6.15
1.6.14
1.6.13
1.6.12
1.6.11
1.6.10
1.6.9
1.6.8
1.6.7
1.6.6
1.6.5
1.6.4
1.6.3
1.5.2
1.5.1
1.5.0
1.4.9
1.4.8
1.4.7
1.4.6
1.4.5
1.4.4
1.4.3
1.4.2
1.4.1
1.4.0
1.3.2
1.3.1
1.3.0
1.2.10
1.2.9
1.2.8
1.2.7
1.2.5
1.2.4
1.2.2
1.2.1
1.2.0
1.1.0
1.0.0
A foundation for building Elixir Phoenix apps — SaaS, social networks, ERP systems, marketplaces, and more
Current section
Files
Jump to
Current section
Files
lib/phoenix_kit/migrations/uuid_fk_columns.ex
defmodule PhoenixKit.Migrations.UUIDFKColumns do
@moduledoc """
Adds UUID FK columns alongside integer FKs across PhoenixKit tables.
Called from V56 migration. This helper module keeps V56 manageable by
extracting the UUID FK column creation logic (~80 columns across ~40 tables).
## How It Works
For each FK column, three operations:
1. `ALTER TABLE ... ADD COLUMN IF NOT EXISTS {uuid_fk} UUID`
2. Backfill via JOIN: `UPDATE t SET {uuid_fk} = s.uuid FROM source s WHERE s.id = t.{int_fk}`
3. `CREATE INDEX IF NOT EXISTS ... ON table({uuid_fk})`
Large tables use batched backfills (PL/pgSQL DO block, 10k rows/batch)
to avoid long-running transactions.
After columns are created and backfilled, `add_constraints/1` adds:
- NOT NULL constraints where the integer FK is NOT NULL
- FK constraints where the integer FK has an explicit DB-level FK constraint
## Safety
- All operations wrapped in `table_exists?` + `column_exists?` checks
- FK constraint creation uses `pg_constraint` existence check (idempotent)
- NOT NULL uses `ALTER COLUMN SET NOT NULL` (idempotent in PostgreSQL)
- Idempotent — safe to run multiple times
"""
use Ecto.Migration
@batch_size 10_000
# ── Group A: FK columns → phoenix_kit_users.uuid ──────────────────────
@user_fk_columns [
# Core
{:phoenix_kit_users_tokens, "user_id", "user_uuid"},
{:phoenix_kit_user_role_assignments, "user_id", "user_uuid"},
{:phoenix_kit_user_role_assignments, "assigned_by", "assigned_by_uuid"},
{:phoenix_kit_admin_notes, "user_id", "user_uuid"},
{:phoenix_kit_admin_notes, "author_id", "author_uuid"},
{:phoenix_kit_user_oauth_providers, "user_id", "user_uuid"},
{:phoenix_kit_audit_logs, "target_user_id", "target_user_uuid"},
{:phoenix_kit_audit_logs, "admin_user_id", "admin_user_uuid"},
{:phoenix_kit_role_permissions, "granted_by", "granted_by_uuid"},
# Comments module (standalone)
{:phoenix_kit_comments, "user_id", "user_uuid"},
{:phoenix_kit_comments_likes, "user_id", "user_uuid"},
{:phoenix_kit_comments_dislikes, "user_id", "user_uuid"},
# Posts module
{:phoenix_kit_posts, "user_id", "user_uuid"},
{:phoenix_kit_post_comments, "user_id", "user_uuid"},
{:phoenix_kit_post_likes, "user_id", "user_uuid"},
{:phoenix_kit_post_dislikes, "user_id", "user_uuid"},
{:phoenix_kit_post_views, "user_id", "user_uuid"},
{:phoenix_kit_post_mentions, "user_id", "user_uuid"},
{:phoenix_kit_post_groups, "user_id", "user_uuid"},
{:phoenix_kit_comment_likes, "user_id", "user_uuid"},
{:phoenix_kit_comment_dislikes, "user_id", "user_uuid"},
# Tickets module
{:phoenix_kit_tickets, "user_id", "user_uuid"},
{:phoenix_kit_tickets, "assigned_to_id", "assigned_to_uuid"},
{:phoenix_kit_ticket_comments, "user_id", "user_uuid"},
{:phoenix_kit_ticket_status_history, "changed_by_id", "changed_by_uuid"},
# Connections module
{:phoenix_kit_user_blocks, "blocker_id", "blocker_uuid"},
{:phoenix_kit_user_blocks, "blocked_id", "blocked_uuid"},
{:phoenix_kit_user_blocks_history, "blocker_id", "blocker_uuid"},
{:phoenix_kit_user_blocks_history, "blocked_id", "blocked_uuid"},
{:phoenix_kit_user_follows, "follower_id", "follower_uuid"},
{:phoenix_kit_user_follows, "followed_id", "followed_uuid"},
{:phoenix_kit_user_follows_history, "follower_id", "follower_uuid"},
{:phoenix_kit_user_follows_history, "followed_id", "followed_uuid"},
{:phoenix_kit_user_connections, "requester_id", "requester_uuid"},
{:phoenix_kit_user_connections, "recipient_id", "recipient_uuid"},
{:phoenix_kit_user_connections_history, "user_a_id", "user_a_uuid"},
{:phoenix_kit_user_connections_history, "user_b_id", "user_b_uuid"},
{:phoenix_kit_user_connections_history, "actor_id", "actor_uuid"},
# Storage module
{:phoenix_kit_files, "user_id", "user_uuid"},
# Shop module
{:phoenix_kit_shop_carts, "user_id", "user_uuid"},
{:phoenix_kit_shop_products, "created_by", "created_by_uuid"},
{:phoenix_kit_shop_import_logs, "user_id", "user_uuid"},
# Billing module
{:phoenix_kit_billing_profiles, "user_id", "user_uuid"},
{:phoenix_kit_orders, "user_id", "user_uuid"},
{:phoenix_kit_invoices, "user_id", "user_uuid"},
{:phoenix_kit_transactions, "user_id", "user_uuid"},
{:phoenix_kit_subscriptions, "user_id", "user_uuid"},
{:phoenix_kit_payment_methods, "user_id", "user_uuid"},
# AI module
{:phoenix_kit_ai_requests, "user_id", "user_uuid"},
# Sync module
{:phoenix_kit_sync_connections, "approved_by", "approved_by_uuid"},
{:phoenix_kit_sync_connections, "suspended_by", "suspended_by_uuid"},
{:phoenix_kit_sync_connections, "revoked_by", "revoked_by_uuid"},
{:phoenix_kit_sync_connections, "created_by", "created_by_uuid"},
{:phoenix_kit_sync_transfers, "approved_by", "approved_by_uuid"},
{:phoenix_kit_sync_transfers, "denied_by", "denied_by_uuid"},
{:phoenix_kit_sync_transfers, "initiated_by", "initiated_by_uuid"},
# Entities module
{:phoenix_kit_entities, "created_by", "created_by_uuid"},
{:phoenix_kit_entity_data, "created_by", "created_by_uuid"},
# Emails module
{:phoenix_kit_email_logs, "user_id", "user_uuid"},
{:phoenix_kit_email_blocklist, "user_id", "user_uuid"},
{:phoenix_kit_email_templates, "created_by_user_id", "created_by_user_uuid"},
{:phoenix_kit_email_templates, "updated_by_user_id", "updated_by_user_uuid"},
# Referrals module
{:phoenix_kit_referral_codes, "created_by", "created_by_uuid"},
{:phoenix_kit_referral_codes, "beneficiary", "beneficiary_uuid"},
{:phoenix_kit_referral_code_usage, "used_by", "used_by_uuid"},
# Legal module
{:phoenix_kit_consent_logs, "user_id", "user_uuid"}
]
# ── Group B: FK columns → phoenix_kit_user_roles.uuid ─────────────────
@role_fk_columns [
{:phoenix_kit_user_role_assignments, "role_id", "role_uuid"},
{:phoenix_kit_role_permissions, "role_id", "role_uuid"}
]
# ── Group C: FK columns → phoenix_kit_entities.uuid ───────────────────
@entity_fk_columns [
{:phoenix_kit_entity_data, "entity_id", "entity_uuid"}
]
# ── Group D: Internal module FK columns ───────────────────────────────
# Each tuple: {target_table, int_fk, uuid_fk, source_table}
@module_fk_columns [
# Shop module internal FKs
{:phoenix_kit_shop_cart_items, "cart_id", "cart_uuid", "phoenix_kit_shop_carts"},
{:phoenix_kit_shop_cart_items, "product_id", "product_uuid", "phoenix_kit_shop_products"},
{:phoenix_kit_shop_carts, "shipping_method_id", "shipping_method_uuid",
"phoenix_kit_shop_shipping_methods"},
{:phoenix_kit_shop_carts, "merged_into_cart_id", "merged_into_cart_uuid",
"phoenix_kit_shop_carts"},
{:phoenix_kit_shop_carts, "payment_option_id", "payment_option_uuid",
"phoenix_kit_payment_options"},
{:phoenix_kit_shop_products, "category_id", "category_uuid", "phoenix_kit_shop_categories"},
{:phoenix_kit_shop_categories, "parent_id", "parent_uuid", "phoenix_kit_shop_categories"},
{:phoenix_kit_shop_categories, "featured_product_id", "featured_product_uuid",
"phoenix_kit_shop_products"},
# Billing module internal FKs
{:phoenix_kit_orders, "billing_profile_id", "billing_profile_uuid",
"phoenix_kit_billing_profiles"},
{:phoenix_kit_invoices, "order_id", "order_uuid", "phoenix_kit_orders"},
{:phoenix_kit_transactions, "invoice_id", "invoice_uuid", "phoenix_kit_invoices"},
{:phoenix_kit_subscriptions, "plan_id", "plan_uuid", "phoenix_kit_subscription_plans"},
{:phoenix_kit_subscriptions, "billing_profile_id", "billing_profile_uuid",
"phoenix_kit_billing_profiles"},
{:phoenix_kit_subscriptions, "payment_method_id", "payment_method_uuid",
"phoenix_kit_payment_methods"},
# Email module internal FKs
{:phoenix_kit_email_events, "email_log_id", "email_log_uuid", "phoenix_kit_email_logs"},
# AI module internal FKs
{:phoenix_kit_ai_requests, "endpoint_id", "endpoint_uuid", "phoenix_kit_ai_endpoints"},
{:phoenix_kit_ai_requests, "prompt_id", "prompt_uuid", "phoenix_kit_ai_prompts"},
# Sync module internal FKs
{:phoenix_kit_sync_transfers, "connection_id", "connection_uuid",
"phoenix_kit_sync_connections"},
# Referrals module internal FKs
{:phoenix_kit_referral_code_usage, "code_id", "code_uuid", "phoenix_kit_referral_codes"}
]
# Tables likely to have many rows — use batched backfill
@batch_tables [
"phoenix_kit_users_tokens",
"phoenix_kit_audit_logs",
"phoenix_kit_email_logs",
"phoenix_kit_email_events",
"phoenix_kit_ai_requests",
"phoenix_kit_entity_data",
"phoenix_kit_posts",
"phoenix_kit_post_comments",
"phoenix_kit_post_likes",
"phoenix_kit_post_views",
"phoenix_kit_consent_logs"
]
# ── NOT NULL UUID FK columns ────────────────────────────────────────────
# UUID FK columns where the integer FK counterpart is NOT NULL.
# Applied AFTER backfill to ensure no NULL values remain.
@not_null_uuid_fks [
# Group A — User FKs
{:phoenix_kit_users_tokens, "user_uuid"},
{:phoenix_kit_user_role_assignments, "user_uuid"},
{:phoenix_kit_admin_notes, "user_uuid"},
{:phoenix_kit_admin_notes, "author_uuid"},
{:phoenix_kit_user_oauth_providers, "user_uuid"},
{:phoenix_kit_audit_logs, "target_user_uuid"},
{:phoenix_kit_audit_logs, "admin_user_uuid"},
{:phoenix_kit_posts, "user_uuid"},
{:phoenix_kit_post_comments, "user_uuid"},
{:phoenix_kit_post_likes, "user_uuid"},
{:phoenix_kit_post_dislikes, "user_uuid"},
{:phoenix_kit_post_mentions, "user_uuid"},
{:phoenix_kit_post_groups, "user_uuid"},
{:phoenix_kit_comment_likes, "user_uuid"},
{:phoenix_kit_comment_dislikes, "user_uuid"},
{:phoenix_kit_ticket_comments, "user_uuid"},
{:phoenix_kit_ticket_status_history, "changed_by_uuid"},
{:phoenix_kit_user_blocks, "blocker_uuid"},
{:phoenix_kit_user_blocks, "blocked_uuid"},
{:phoenix_kit_user_blocks_history, "blocker_uuid"},
{:phoenix_kit_user_blocks_history, "blocked_uuid"},
{:phoenix_kit_user_follows, "follower_uuid"},
{:phoenix_kit_user_follows, "followed_uuid"},
{:phoenix_kit_user_follows_history, "follower_uuid"},
{:phoenix_kit_user_follows_history, "followed_uuid"},
{:phoenix_kit_user_connections, "requester_uuid"},
{:phoenix_kit_user_connections, "recipient_uuid"},
{:phoenix_kit_user_connections_history, "user_a_uuid"},
{:phoenix_kit_user_connections_history, "user_b_uuid"},
{:phoenix_kit_user_connections_history, "actor_uuid"},
{:phoenix_kit_files, "user_uuid"},
{:phoenix_kit_comments_likes, "user_uuid"},
{:phoenix_kit_comments_dislikes, "user_uuid"},
{:phoenix_kit_entities, "created_by_uuid"},
{:phoenix_kit_entity_data, "created_by_uuid"},
{:phoenix_kit_invoices, "user_uuid"},
{:phoenix_kit_transactions, "user_uuid"},
{:phoenix_kit_payment_methods, "user_uuid"},
{:phoenix_kit_subscriptions, "user_uuid"},
{:phoenix_kit_referral_codes, "created_by_uuid"},
{:phoenix_kit_referral_code_usage, "used_by_uuid"},
# Group B — Role FKs
{:phoenix_kit_user_role_assignments, "role_uuid"},
{:phoenix_kit_role_permissions, "role_uuid"},
# Group C — Entity FKs
{:phoenix_kit_entity_data, "entity_uuid"},
# Group D — Module Internal FKs
{:phoenix_kit_shop_cart_items, "cart_uuid"},
{:phoenix_kit_subscriptions, "plan_uuid"},
{:phoenix_kit_email_events, "email_log_uuid"},
{:phoenix_kit_referral_code_usage, "code_uuid"}
]
# ── FK Constraints for UUID FK columns ──────────────────────────────────
# Only where the integer FK has an explicit DB-level FK constraint.
# ON DELETE behavior matches the integer FK's behavior.
# Format: {table, uuid_fk, ref_table, ref_column, on_delete}
@fk_constraints [
# User FKs → phoenix_kit_users(uuid)
{:phoenix_kit_users_tokens, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_role_assignments, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_role_assignments, "assigned_by_uuid", "phoenix_kit_users", "uuid",
"SET NULL"},
{:phoenix_kit_admin_notes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_admin_notes, "author_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_oauth_providers, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
# Posts module
{:phoenix_kit_posts, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_comments, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_likes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_dislikes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_views, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_mentions, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_post_groups, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_comment_likes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_comment_dislikes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
# Tickets module
{:phoenix_kit_tickets, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_tickets, "assigned_to_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_ticket_comments, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_ticket_status_history, "changed_by_uuid", "phoenix_kit_users", "uuid",
"SET NULL"},
# Connections module
{:phoenix_kit_user_blocks, "blocker_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_blocks, "blocked_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_blocks_history, "blocker_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_blocks_history, "blocked_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_follows, "follower_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_follows, "followed_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_follows_history, "follower_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_follows_history, "followed_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_connections, "requester_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_connections, "recipient_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_user_connections_history, "user_a_uuid", "phoenix_kit_users", "uuid",
"CASCADE"},
{:phoenix_kit_user_connections_history, "user_b_uuid", "phoenix_kit_users", "uuid",
"CASCADE"},
{:phoenix_kit_user_connections_history, "actor_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
# Storage module
{:phoenix_kit_files, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
# Comments module (standalone)
{:phoenix_kit_comments, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_comments_likes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
{:phoenix_kit_comments_dislikes, "user_uuid", "phoenix_kit_users", "uuid", "CASCADE"},
# Billing module
{:phoenix_kit_billing_profiles, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_orders, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_invoices, "user_uuid", "phoenix_kit_users", "uuid", "RESTRICT"},
{:phoenix_kit_transactions, "user_uuid", "phoenix_kit_users", "uuid", "RESTRICT"},
# AI module
{:phoenix_kit_ai_requests, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
# Shop module
{:phoenix_kit_shop_carts, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_shop_products, "created_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_shop_import_logs, "user_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
# Sync module
{:phoenix_kit_sync_connections, "approved_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_connections, "suspended_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_connections, "revoked_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_connections, "created_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_transfers, "approved_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_transfers, "denied_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
{:phoenix_kit_sync_transfers, "initiated_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
# Role permissions
{:phoenix_kit_role_permissions, "granted_by_uuid", "phoenix_kit_users", "uuid", "SET NULL"},
# Role FKs → phoenix_kit_user_roles(uuid)
{:phoenix_kit_user_role_assignments, "role_uuid", "phoenix_kit_user_roles", "uuid",
"CASCADE"},
{:phoenix_kit_role_permissions, "role_uuid", "phoenix_kit_user_roles", "uuid", "CASCADE"},
# Entity FKs → phoenix_kit_entities(uuid)
{:phoenix_kit_entity_data, "entity_uuid", "phoenix_kit_entities", "uuid", "CASCADE"},
# Shop module internal FKs
{:phoenix_kit_shop_cart_items, "cart_uuid", "phoenix_kit_shop_carts", "uuid", "CASCADE"},
{:phoenix_kit_shop_cart_items, "product_uuid", "phoenix_kit_shop_products", "uuid",
"SET NULL"},
{:phoenix_kit_shop_carts, "shipping_method_uuid", "phoenix_kit_shop_shipping_methods", "uuid",
"SET NULL"},
{:phoenix_kit_shop_carts, "payment_option_uuid", "phoenix_kit_payment_options", "uuid",
"SET NULL"},
{:phoenix_kit_shop_products, "category_uuid", "phoenix_kit_shop_categories", "uuid",
"SET NULL"},
{:phoenix_kit_shop_categories, "parent_uuid", "phoenix_kit_shop_categories", "uuid",
"SET NULL"},
{:phoenix_kit_shop_categories, "featured_product_uuid", "phoenix_kit_shop_products", "uuid",
"SET NULL"},
# Billing module internal FKs
{:phoenix_kit_orders, "billing_profile_uuid", "phoenix_kit_billing_profiles", "uuid",
"SET NULL"},
{:phoenix_kit_invoices, "order_uuid", "phoenix_kit_orders", "uuid", "SET NULL"},
{:phoenix_kit_transactions, "invoice_uuid", "phoenix_kit_invoices", "uuid", "RESTRICT"},
# Email module internal FKs
{:phoenix_kit_email_events, "email_log_uuid", "phoenix_kit_email_logs", "uuid", "CASCADE"},
# AI module internal FKs
{:phoenix_kit_ai_requests, "endpoint_uuid", "phoenix_kit_ai_endpoints", "uuid", "SET NULL"},
{:phoenix_kit_ai_requests, "prompt_uuid", "phoenix_kit_ai_prompts", "uuid", "SET NULL"},
# Sync module internal FKs
{:phoenix_kit_sync_transfers, "connection_uuid", "phoenix_kit_sync_connections", "uuid",
"SET NULL"},
# Referrals module internal FKs
{:phoenix_kit_referral_code_usage, "code_uuid", "phoenix_kit_referral_codes", "uuid",
"CASCADE"}
]
# ── Public API ────────────────────────────────────────────────────────
def up(%{prefix: prefix} = opts) do
escaped_prefix = Map.get(opts, :escaped_prefix, prefix)
# Group A: FK columns → phoenix_kit_users.uuid
process_fk_group(@user_fk_columns, "phoenix_kit_users", prefix, escaped_prefix)
# Group B: FK columns → phoenix_kit_user_roles.uuid
process_fk_group(@role_fk_columns, "phoenix_kit_user_roles", prefix, escaped_prefix)
# Group C: FK columns → phoenix_kit_entities.uuid
process_fk_group(@entity_fk_columns, "phoenix_kit_entities", prefix, escaped_prefix)
# Group D: Internal module FKs (each has its own source table)
process_module_fk_group(@module_fk_columns, prefix, escaped_prefix)
end
def down(%{prefix: prefix} = opts) do
escaped_prefix = Map.get(opts, :escaped_prefix, prefix)
all_columns =
Enum.map(@user_fk_columns, fn {t, _i, u} -> {t, u} end) ++
Enum.map(@role_fk_columns, fn {t, _i, u} -> {t, u} end) ++
Enum.map(@entity_fk_columns, fn {t, _i, u} -> {t, u} end) ++
Enum.map(@module_fk_columns, fn {t, _i, u, _s} -> {t, u} end)
for {table, uuid_fk} <- all_columns do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) do
drop_uuid_fk_index(table_str, uuid_fk, prefix, escaped_prefix)
drop_uuid_fk_column(table_str, uuid_fk, prefix, escaped_prefix)
end
end
end
@doc """
Adds NOT NULL constraints and FK constraints to UUID FK columns.
Must be called AFTER `up/1` so that columns exist and are backfilled.
Order: NOT NULL first (data already backfilled), then FK constraints.
"""
def add_constraints(%{prefix: prefix} = _opts) do
escaped_prefix = String.replace(prefix, "'", "\\'")
for {table, uuid_fk} <- @not_null_uuid_fks do
set_not_null(table, uuid_fk, prefix, escaped_prefix)
end
for {table, uuid_fk, ref_table, ref_col, on_delete} <- @fk_constraints do
add_fk_constraint(table, uuid_fk, ref_table, ref_col, on_delete, prefix, escaped_prefix)
end
end
@doc """
Drops FK constraints and NOT NULL from UUID FK columns.
Must be called BEFORE `down/1` so constraints are removed before columns are dropped.
Order: FK constraints first (unblocks column removal), then NOT NULL.
"""
def drop_constraints(%{prefix: prefix} = _opts) do
escaped_prefix = String.replace(prefix, "'", "\\'")
for {table, uuid_fk, _ref_table, _ref_col, _on_delete} <- @fk_constraints do
drop_fk_constraint(table, uuid_fk, prefix, escaped_prefix)
end
for {table, uuid_fk} <- @not_null_uuid_fks do
drop_not_null(table, uuid_fk, prefix, escaped_prefix)
end
end
# ── Group Processing ──────────────────────────────────────────────────
defp process_fk_group(columns, source_table, prefix, escaped_prefix) do
for {table, int_fk, uuid_fk} <- columns do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) and
table_exists?(source_table, escaped_prefix) and
column_exists?(table_str, int_fk, escaped_prefix) do
add_uuid_fk_column(table_str, uuid_fk, prefix, escaped_prefix)
backfill_uuid_fk(table_str, int_fk, uuid_fk, source_table, prefix, escaped_prefix)
create_uuid_fk_index(table_str, uuid_fk, prefix, escaped_prefix)
end
end
end
defp process_module_fk_group(columns, prefix, escaped_prefix) do
for {table, int_fk, uuid_fk, source_table} <- columns do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) and
table_exists?(source_table, escaped_prefix) and
column_exists?(table_str, int_fk, escaped_prefix) do
add_uuid_fk_column(table_str, uuid_fk, prefix, escaped_prefix)
backfill_uuid_fk(table_str, int_fk, uuid_fk, source_table, prefix, escaped_prefix)
create_uuid_fk_index(table_str, uuid_fk, prefix, escaped_prefix)
end
end
end
# ── Column Operations ─────────────────────────────────────────────────
defp add_uuid_fk_column(table_str, uuid_fk, prefix, escaped_prefix) do
unless column_exists?(table_str, uuid_fk, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
execute("""
ALTER TABLE #{table_name}
ADD COLUMN #{uuid_fk} UUID
""")
end
end
defp backfill_uuid_fk(table_str, int_fk, uuid_fk, source_table, prefix, _escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
source_name = prefix_table_name(source_table, prefix)
# Always attempt backfill — the SQL is idempotent (WHERE uuid_fk IS NULL).
# Skipping via column_exists? can fail when columns were just added in the
# same transaction (information_schema visibility lag).
if table_str in @batch_tables do
batched_backfill(table_name, int_fk, uuid_fk, source_name)
else
simple_backfill(table_name, int_fk, uuid_fk, source_name)
end
rescue
# If column doesn't exist yet (shouldn't happen), silently skip
_ -> :ok
end
defp simple_backfill(table_name, int_fk, uuid_fk, source_name) do
execute("""
UPDATE #{table_name} t
SET #{uuid_fk} = s.uuid
FROM #{source_name} s
WHERE s.id = t.#{int_fk}
AND t.#{uuid_fk} IS NULL
AND t.#{int_fk} IS NOT NULL
""")
end
defp batched_backfill(table_name, int_fk, uuid_fk, source_name) do
execute("""
DO $$
DECLARE
batch_count INTEGER;
BEGIN
LOOP
UPDATE #{table_name} t
SET #{uuid_fk} = s.uuid
FROM #{source_name} s
WHERE s.id = t.#{int_fk}
AND t.#{uuid_fk} IS NULL
AND t.#{int_fk} IS NOT NULL
AND t.ctid IN (
SELECT t2.ctid FROM #{table_name} t2
WHERE t2.#{uuid_fk} IS NULL
AND t2.#{int_fk} IS NOT NULL
LIMIT #{@batch_size}
);
GET DIAGNOSTICS batch_count = ROW_COUNT;
EXIT WHEN batch_count = 0;
PERFORM pg_sleep(0.01);
END LOOP;
END $$;
""")
end
defp create_uuid_fk_index(table_str, uuid_fk, prefix, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
index_name = "#{table_str}_#{uuid_fk}_idx"
index_name =
case prefix do
nil -> index_name
"public" -> index_name
p -> "#{p}.#{index_name}"
end
unless index_exists?(table_str, "#{table_str}_#{uuid_fk}_idx", escaped_prefix) do
execute("""
CREATE INDEX IF NOT EXISTS #{index_name}
ON #{table_name}(#{uuid_fk})
""")
end
end
# ── NOT NULL Operations ───────────────────────────────────────────────
defp set_not_null(table, uuid_fk, prefix, escaped_prefix) do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) and
column_exists?(table_str, uuid_fk, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
# SET NOT NULL is idempotent in PostgreSQL (no-op if already NOT NULL)
execute("""
ALTER TABLE #{table_name}
ALTER COLUMN #{uuid_fk} SET NOT NULL
""")
end
end
defp drop_not_null(table, uuid_fk, prefix, escaped_prefix) do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) and
column_exists?(table_str, uuid_fk, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
execute("""
ALTER TABLE #{table_name}
ALTER COLUMN #{uuid_fk} DROP NOT NULL
""")
end
end
# ── FK Constraint Operations ──────────────────────────────────────────
defp add_fk_constraint(table, uuid_fk, ref_table, ref_col, on_delete, prefix, escaped_prefix) do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) and
column_exists?(table_str, uuid_fk, escaped_prefix) and
table_exists?(ref_table, escaped_prefix) and
column_exists?(ref_table, ref_col, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
ref_name = prefix_table_name(ref_table, prefix)
constraint = fk_constraint_name(table_str, uuid_fk)
# Use DO block with pg_constraint check for idempotency (matches V51 pattern)
execute("""
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = '#{constraint}'
AND conrelid = '#{table_name}'::regclass
) THEN
ALTER TABLE #{table_name}
ADD CONSTRAINT #{constraint}
FOREIGN KEY (#{uuid_fk})
REFERENCES #{ref_name}(#{ref_col})
ON DELETE #{on_delete};
END IF;
END $$;
""")
end
end
defp drop_fk_constraint(table, uuid_fk, prefix, escaped_prefix) do
table_str = Atom.to_string(table)
if table_exists?(table_str, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
constraint = fk_constraint_name(table_str, uuid_fk)
execute("""
DO $$
BEGIN
IF EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = '#{constraint}'
AND conrelid = '#{table_name}'::regclass
) THEN
ALTER TABLE #{table_name}
DROP CONSTRAINT #{constraint};
END IF;
END $$;
""")
end
end
# ── Rollback Operations ───────────────────────────────────────────────
defp drop_uuid_fk_column(table_str, uuid_fk, prefix, escaped_prefix) do
if column_exists?(table_str, uuid_fk, escaped_prefix) do
table_name = prefix_table_name(table_str, prefix)
execute("""
ALTER TABLE #{table_name}
DROP COLUMN #{uuid_fk}
""")
end
end
defp drop_uuid_fk_index(table_str, uuid_fk, prefix, _escaped_prefix) do
index_name = "#{table_str}_#{uuid_fk}_idx"
index_name =
case prefix do
nil -> index_name
"public" -> index_name
p -> "#{p}.#{index_name}"
end
execute("DROP INDEX IF EXISTS #{index_name}")
end
# ── Existence Checks ──────────────────────────────────────────────────
defp table_exists?(table_str, escaped_prefix) do
query = """
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_name = '#{table_str}'
AND table_schema = '#{escaped_prefix}'
)
"""
case repo().query(query, [], log: false) do
{:ok, %{rows: [[true]]}} -> true
_ -> false
end
end
defp column_exists?(table_str, column_str, escaped_prefix) do
query = """
SELECT EXISTS (
SELECT FROM information_schema.columns
WHERE table_name = '#{table_str}'
AND column_name = '#{column_str}'
AND table_schema = '#{escaped_prefix}'
)
"""
case repo().query(query, [], log: false) do
{:ok, %{rows: [[true]]}} -> true
_ -> false
end
end
defp index_exists?(table_str, index_name, escaped_prefix) do
query = """
SELECT EXISTS (
SELECT FROM pg_indexes
WHERE tablename = '#{table_str}'
AND indexname = '#{index_name}'
AND schemaname = '#{escaped_prefix}'
)
"""
case repo().query(query, [], log: false) do
{:ok, %{rows: [[true]]}} -> true
_ -> false
end
end
# ── Naming Helpers ────────────────────────────────────────────────────
defp prefix_table_name(table_name, nil), do: table_name
defp prefix_table_name(table_name, "public"), do: "public.#{table_name}"
defp prefix_table_name(table_name, prefix), do: "#{prefix}.#{table_name}"
defp fk_constraint_name(table_str, uuid_fk) do
short = String.replace_prefix(table_str, "phoenix_kit_", "")
"fk_#{short}_#{uuid_fk}"
end
end