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.