migrate does not ADD a foreign key to a pre-existing table (only creates FKs at table-creation time)

[MelbourneDeveloper]

Summary

DataProviderMigrate migrate does not emit ALTER TABLE ... ADD CONSTRAINT for a foreign key added to a table that already exists in the target DB. It creates the new column but silently skips the FK constraint. New tables get their FKs correctly at creation time; only the "add a FK to a pre-existing table" path is missing.

The bug is masked because the apply phase reports success — it is the post-apply integrity check that then fails, leaving the operator with a half-applied schema and no automated way to converge it.

Reproduction

1. Have a table conversations already present in the DB (created by an earlier migrate).
2. In schema.yaml, add a new nullable column to it with a foreign key:

- name: end_user_id
  type: Uuid          # nullable
foreignKeys:
  - columns: [end_user_id]
    referencedTable: tenant_end_users     # a NEW table created in the same run
    referencedColumns: [id]
    onDelete: Cascade

3. Run DataProviderMigrate migrate --schema schema.yaml --provider postgres.

Actual

• Apply phase succeeds (creates tenant_end_users, adds conversations.end_user_id, applies grants/RLS — 53 ops here).
• Post-apply integrity check FAILS:

  SCHEMA INTEGRITY CHECK FAILED
  public.conversations: missing foreign key FK_conversations_end_user_id on (end_user_id)
  

• Re-running migrate reports "Schema is up to date — no operations needed" and still fails the same integrity check. So the apply planner never diffs/produces the missing FK on the existing table — it is not a transient ordering issue.

Expected

The planner emits ALTER TABLE public.conversations ADD CONSTRAINT "FK_conversations_end_user_id" FOREIGN KEY (end_user_id) REFERENCES public.tenant_end_users(id) ON DELETE CASCADE; so a re-run converges to zero drift.

Impact

Any FK added to an existing table never lands via the declarative pipeline. This breaks the "schema.yaml is the single source of truth, re-running is idempotent" contract for the common case of evolving an existing table. The only remediation is a manual ALTER TABLE ... ADD CONSTRAINT out of band, which defeats the declarative model.

Contrast with the FK-at-creation path, which works: tenant_end_users(tenant_id) -> tenants(id) (created in the same run) was applied correctly. The gap is specifically ADD FK on an ALTER of a pre-existing table.

Related secondary finding (integrity-check normalization, lower severity)

The same integrity check also reports a false positive on string-literal column defaults — it does not normalize Postgres's implicit ::text cast:

public.topup.status: default expected 'pending' but found 'pending'::text

'pending' and 'pending'::text are the same default; the check should normalize the cast before comparing (same class of round-trip mismatch as the filtered/expression-index issue in #65). Happy to split this into its own issue if preferred.

Environment

• DataProvider 0.9.12-beta line (same as the gaps tracked in Nimblesite/NimblesiteAgenticPlatform docs/specs/deployment.md / docs/plans/authorization.md).
• Postgres (Supabase), --provider postgres.

[MelbourneDeveloper]

Severity escalation: this gap just cost NAP prod every single foreign key.

Sequence (2026-07-02):

1. NAP ran migrate --allow-destructive for the [AUTHZ-ONE-ROLE] policy cut.
2. Destructive mode treated every existing FK as drift (prod constraint names didn't match the migrator's expected FK_<table>_<column> naming) and dropped all of them — 27 FKs across 17 tables.
3. Because of this issue, the same run could not re-create them: FKs are only emitted at table-creation time, so the integrity check now reports every FK as missing and the schema cannot converge:

public.tenant_credit: missing foreign key FK_tenant_credit_tenant_id on (tenant_id)
public.tenant_members: missing foreign key FK_tenant_members_tenant_id on (tenant_id)
... (25 more — every declared FK)

Result: NAP production currently has zero FK constraints (select count(*) from pg_constraint where contype='f' and connamespace='public'::regnamespace → 0). Referential integrity and every onDelete: Cascade behavior (tenant deletion cascade!) are silently gone. Data is still clean (0 orphan rows verified), so re-adding will validate — but only DataProvider is allowed to do it.

Two asks:

1. Fix this issue (emit AddForeignKey ops for declared FKs missing on existing tables) — this is now the only way to restore prod integrity, since NAP policy forbids hand-written DDL.
2. Destructive-mode ordering guard: never drop a drifted-name FK unless the same plan contains the op that re-creates its declared replacement. Drop-without-recreate must be a plan-validation error, not a silent outcome.

Related: destructive mode also left app_admin's table grants behind after the role was removed from the yaml (grants on 17 tables still present; role is NOLOGIN + policy-less so it's inert, but it's the same "declared state not converged" class — happy to file separately if you want it tracked on its own).