Version: dataprovidermigrate 0.9.12-beta (dotnet tool)
Scenario (TradiSite prod): public.sites is declared in schema.yaml with exactly ONE foreign key (org_id -> orgs). The live DB additionally had a legacy FK_sites_user_id FOREIGN KEY (user_id) REFERENCES public.users(id) left over from an old schema generation (public.users itself is no longer in the YAML at all).
Actual: DataProviderMigrate reports Migration completed successfully and the schema integrity check does not mention the extra FK at all. The stale constraint stayed in place and made every INSERT INTO sites fail with 23503 FK_sites_user_id — a hard prod outage (every new-user site creation 500'd), invisible to the migration tooling.
Expected (either is fine, both is best):
- The integrity verifier should report extra/undeclared foreign keys on tables it manages, not just missing/mismatched ones; and/or
- The planner should drop constraints on managed tables that are absent from the declared schema (perhaps behind a
--prune-constraints flag).
A tool that owns a table's DDL but silently tolerates undeclared constraints on it cannot be trusted as the single source of truth for that table.
Workaround used: manual ALTER TABLE public.sites DROP CONSTRAINT "FK_sites_user_id" (+ dropped the orphaned public.users table) via psql.
Found while operating TradiSite prod (MelbourneDeveloper/TradiSite, spec [DB-MIGRATE-CLI-ONLY]).
Version: dataprovidermigrate 0.9.12-beta (dotnet tool)
Scenario (TradiSite prod):
public.sitesis declared in schema.yaml with exactly ONE foreign key (org_id -> orgs). The live DB additionally had a legacyFK_sites_user_id FOREIGN KEY (user_id) REFERENCES public.users(id)left over from an old schema generation (public.usersitself is no longer in the YAML at all).Actual:
DataProviderMigratereportsMigration completed successfullyand the schema integrity check does not mention the extra FK at all. The stale constraint stayed in place and made everyINSERT INTO sitesfail with23503 FK_sites_user_id— a hard prod outage (every new-user site creation 500'd), invisible to the migration tooling.Expected (either is fine, both is best):
--prune-constraintsflag).A tool that owns a table's DDL but silently tolerates undeclared constraints on it cannot be trusted as the single source of truth for that table.
Workaround used: manual
ALTER TABLE public.sites DROP CONSTRAINT "FK_sites_user_id"(+ dropped the orphanedpublic.userstable) via psql.Found while operating TradiSite prod (MelbourneDeveloper/TradiSite, spec [DB-MIGRATE-CLI-ONLY]).