Repository navigation
migrate: AddColumn on existing table omits the column's FK (no CreateForeignKey op) → integrity check fails #80
Description
Summary
DataProviderMigrate migrate against an existing Postgres DB, when a schema change adds a new column with a foreign key to an existing table, applies the AddColumnOperation (+ index + RLS policies) but does not plan a CreateForeignKeyOperation for that column. The post-migration integrity check then fails because the FK declared in the schema is absent from the DB.
Repro
Schema adds to existing table usage_events:
- column
end_user_id Uuid(nullable) - FK
end_user_id -> tenant_end_users(id) ON DELETE SET NULL - index + RLS policies
Run migrate --provider postgres against a DB that already has usage_events.
Observed
Phase: all — applying 7 of 7 operation(s):
AddColumnOperation
CreateIndexOperation
CreateRlsPolicyOperation x5
Migration completed successfully
SCHEMA INTEGRITY CHECK FAILED
public.usage_events: missing foreign key FK_usage_events_end_user_id on (end_user_id)
No CreateForeignKeyOperation is ever planned. Re-running reports "Schema is up to date — no operations needed" yet the same integrity check still fails → migrate can never converge for this change on an existing DB; exit code 1.
Expected
Adding a column that declares a FK should also plan a CreateForeignKeyOperation (as it would when creating the table fresh — a fresh-DB run creates the FK with the table, so this only repros incrementally).
Impact
Blocks clean incremental prod migrations that add a FK-bearing column; forces operators toward a raw-SQL workaround (which our project policy forbids). Cosmetic aside: integrity check also flags default expected 'pending' but found 'pending'::text (Postgres text-cast normalization false positive).
Activity
MelbourneDeveloper commentedon Sep 30, 2026
Reviewed the current existing-database upgrade tests.
AdditiveUpgrade_AddsUnnamedForeignKeyAlongsideNewColumnandAdditiveUpgrade_RepairsMissingForeignKeyAfterColumnWasAppliedtarget this defect correctly.Please carry the shared #79 review/acceptance cases into this fix: real FK enforcement, newly introduced referenced tables, correct referenced schema/composite-column order, and repeat migration with zero drift. Close #79/#80 together only once the fixed migrator is published and the CLI upgrade path passes. NAP's concrete case is
processed_stripe_events.tenant_id -> tenants(id).MelbourneDeveloper commentedon Sep 30, 2026
Second NAP review, 2026-09-30, of local
fix/nap-migration-blockers(7f514faplus working changes). The newMigrationPlatformRegressionTestsnow uses YAML through the CLI entry point for both parent/column additions and the partially migrated database. This addresses the previous request for CLI-path coverage; I have not independently executed the suite.Before closure, add actual orphan-write rejection after repair and an assertion that the unchanged CLI rerun emits no operations. The current helper accepts any successful migration with matching final catalog state, so it can miss repeated constraint replacement. Carry over the composite/cross-schema inspection cases in my new #79 comment and resolve the SQL Server test/CLI mismatch noted on #109. Publish the fixed version and demonstrate the NAP
processed_stripe_events.tenant_id -> tenants(id)upgrade; #79/#80 remain one shared fix.