fix(data): defer FK checks so tenant import survives cyclic schema #442

Merged

nrupard merged 2 commits from feat/PMS-648-deferrable-fk-import into main

2026-07-13 18:27:00 +02:00

nrupard commented

2026-07-10 17:41:49 +02:00

Owner

What

Fixes the tenant data import (POST /api/v1/data/import) so wipe-and-replace works over the schema's cyclic, self-referential, and NOT-NULL foreign keys. Root cause: FK checks fire at statement end, so no single wipe/load order satisfies FK cycles (companies.primary_contact_id <-> contacts.company_id), self-refs (tasks.parent_task_id), or the excluded secret/audit/token tables that reference load tables. The round-trip integration test surfaced this twice (first the tenants->companies RESTRICT FK, then the work_types <- rate_card_items cycle).

How

migrations/088_deferrable_fk_constraints.sql makes every public FK DEFERRABLE INITIALLY IMMEDIATE (idempotent; per-statement checking unchanged for all normal operations - only a transaction that explicitly runs SET CONSTRAINTS ALL DEFERRED defers them). The import now:

runs SET CONSTRAINTS ALL DEFERRED inside its transaction,
wipes every tenant-scoped table EXCEPT the identity tables (users, user_sessions, which are referenced BY business rows rather than referencing them), which clears both the load tables and the excluded secret/audit/token tables that FK-reference load rows so nothing non-reloaded dangles at commit,
loads every envelope table in ANY order via jsonb_populate_recordset,
remaps the tenants -> load-table references (own_company_id) to the new ids.

All FK checks fire once at COMMIT, so order no longer matters. This deletes the topo_order Kahn sort (+ its unit test) and the pre-wipe tenants-FK-NULL workaround from #441.

Testing

just pre-commit green (fmt / clippy -D warnings / compile --all-targets / unit / doc).
Un-quarantines tests/data_transfer.rs (removes the #[ignore]); the export/import round-trip now runs in the Integration job, which is the only runtime validator of the import path. Integration must go green before merge.

Note: wiping the excluded secret/audit/token tables on import is intentional wipe-and-replace semantics - the operator re-enters secrets after a restore; the export never contained them.

#PMS-648

## What Fixes the tenant data import (`POST /api/v1/data/import`) so wipe-and-replace works over the schema's cyclic, self-referential, and NOT-NULL foreign keys. Root cause: FK checks fire at statement end, so no single wipe/load order satisfies FK cycles (`companies.primary_contact_id <-> contacts.company_id`), self-refs (`tasks.parent_task_id`), or the excluded secret/audit/token tables that reference load tables. The round-trip integration test surfaced this twice (first the `tenants->companies` RESTRICT FK, then the `work_types <- rate_card_items` cycle). ## How `migrations/088_deferrable_fk_constraints.sql` makes every public FK `DEFERRABLE INITIALLY IMMEDIATE` (idempotent; per-statement checking unchanged for all normal operations - only a transaction that explicitly runs `SET CONSTRAINTS ALL DEFERRED` defers them). The import now: - runs `SET CONSTRAINTS ALL DEFERRED` inside its transaction, - wipes every tenant-scoped table EXCEPT the identity tables (`users`, `user_sessions`, which are referenced BY business rows rather than referencing them), which clears both the load tables and the excluded secret/audit/token tables that FK-reference load rows so nothing non-reloaded dangles at commit, - loads every envelope table in ANY order via `jsonb_populate_recordset`, - remaps the `tenants -> load-table` references (`own_company_id`) to the new ids. All FK checks fire once at COMMIT, so order no longer matters. This deletes the `topo_order` Kahn sort (+ its unit test) and the pre-wipe tenants-FK-NULL workaround from #441. ## Testing - `just pre-commit` green (fmt / clippy -D warnings / compile --all-targets / unit / doc). - Un-quarantines `tests/data_transfer.rs` (removes the `#[ignore]`); the export/import round-trip now runs in the Integration job, which is the only runtime validator of the import path. Integration must go green before merge. Note: wiping the excluded secret/audit/token tables on import is intentional wipe-and-replace semantics - the operator re-enters secrets after a restore; the export never contained them. #PMS-648

nrupard added 1 commit

2026-07-10 17:41:49 +02:00

fix(data): defer FK checks so tenant import survives cyclic schema

Check / fmt + clippy + build + tests (pull_request) Successful in 1m30s

Details

E2E / Playwright against staging (pull_request) Failing after 3m59s

Details

Integration / integration tests (pull_request) Successful in 7m28s

Details

86ae88b4e6

The wipe-and-replace import could not be ordered by a per-statement topological sort: the schema has FK cycles (companies.primary_contact_id <-> contacts.company_id), self-referential FKs (tasks.parent_task_id), NOT-NULL FKs, and excluded secret/audit/token tables that FK-reference load tables. Any single wipe/load order violated some non-deferrable check at statement end (the tenants->companies RESTRICT FK and then the work_types<-rate_card_items cycle both surfaced in the round-trip integration test).

Migration 088 makes every public foreign key DEFERRABLE INITIALLY IMMEDIATE (idempotent, per-statement checking unchanged for normal ops). The import now runs SET CONSTRAINTS ALL DEFERRED inside its transaction and: wipes every tenant-scoped table except the identity tables (users, user_sessions - referenced BY business rows, never referencing them), loads every envelope table in ANY order via jsonb_populate_recordset, and remaps tenants->load-table references (own_company_id) to the new ids. All FK checks fire once at COMMIT, so order no longer matters. This removes the topo_order sort and the pre-wipe tenants-FK-NULL workaround. Un-quarantines the export/import round-trip integration test.

#PMS-648

nrupard added 1 commit

2026-07-13 15:07:36 +02:00

Merge remote-tracking branch 'origin/main' into feat/PMS-648-deferrable-fk-import

Check / fmt + clippy + build + tests (pull_request) Successful in 4m8s

Details

Integration / integration tests (pull_request) Successful in 10m16s

Details

E2E / Playwright against staging (pull_request) Successful in 36s

Details

Create release / Gate (release-branch merges only) (pull_request) Successful in 1s

Details

Create release / Create release from merged PR (pull_request) Has been skipped