Bug: PostgreSQL migrator does not detect missing unique constraint on existing table

[MelbourneDeveloper]

Bug

DataProviderMigrate did not detect or create a declared uniqueConstraints entry on an existing PostgreSQL table.

Reproduction context

Schema YAML contains an existing table public.agent_configs. A new table-level unique constraint was added:

- name: agent_configs
  schema: public
  columns:
    - name: id
      type: Uuid
      isNullable: false
      defaultLqlExpression: gen_uuid()
    - name: tenant_id
      type: Uuid
      isNullable: false
    - name: name
      type: Text
      isNullable: false
  primaryKey:
    columns:
      - id
  foreignKeys:
    - columns:
        - tenant_id
      referencedTable: tenants
      referencedSchema: public
      referencedColumns:
        - id
      onDelete: Cascade
  uniqueConstraints:
    - name: uq_agent_configs_tenant_name
      columns:
        - tenant_id
        - name

Expected live PostgreSQL result:

ALTER TABLE public.agent_configs
ADD CONSTRAINT uq_agent_configs_tenant_name UNIQUE (tenant_id, name);

Actual CLI result:

Loaded schema 'nap' with 14 tables
Connected to PostgreSQL database
Schema is up to date -- no operations needed

Structural-only result:

Schema is up to date for phase 'structural' -- no operations needed

Live PostgreSQL catalog after migration did not include the constraint:

FK_agent_configs_tenant_id | f | FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
PK_agent_configs | p | PRIMARY KEY (id)
agent_configs_agent_type_check | c | CHECK (...)
agent_configs_base_agent_check | c | CHECK (...)
agent_configs_provisioning_bundle_sha256_check | c | CHECK (...)
agent_configs_workspace_host_kind_check | c | CHECK (...)
INDEX | PK_agent_configs | CREATE UNIQUE INDEX "PK_agent_configs" ON public.agent_configs USING btree (id)
INDEX | ix_agent_configs_tenant_id | CREATE INDEX ix_agent_configs_tenant_id ON public.agent_configs USING btree (tenant_id)

No uq_agent_configs_tenant_name constraint existed, and no unique index existed over (tenant_id, name).

Expected behavior

The PostgreSQL inspector/diff/apply path must compare desired uniqueConstraints against live pg_constraint / unique index state for existing tables.

If a YAML-declared unique constraint is missing, the migrator must generate and apply an add-constraint operation.

If duplicates already exist and PostgreSQL rejects the constraint, the migrator must fail loudly with the PostgreSQL error. It must not report up to date.

Acceptance criteria

• Adding uniqueConstraints to an existing table produces an operation.
• Re-running after successful creation reports no operations needed.
• Missing unique constraints are detected during post-migration verification.
• Composite unique constraints such as (tenant_id, name) are handled correctly.
• Constraint names from YAML are preserved in PostgreSQL.

[MelbourneDeveloper]

This was fixed in PR #52 (commit f015e3e). Verified in the current main by:

• DataProviderMigrateIntegrityTests.Migrate_AddsDeclaredCompositeUniqueConstraintToExistingPostgresTable (file) — runs the actual DataProviderMigrate.Program.Main against a live Postgres container, applies the baseline schema, then re-runs with the uniqueConstraints entry added. Asserts the second run produces an AddUniqueConstraintOperation and that the constraint exists in pg_constraint after.
• Two new regression locks: SchemaDiffUniqueConstraintIssue55Tests (pure-diff) and PostgresUniqueConstraintIssue55E2ETests (composite unique on a table that also has a foreign key, the exact issue scenario).

All three pass on main. Closing as fixed in 0.9.11-beta and verified on subsequent versions. If you can still reproduce on 0.9.12-beta+, please re-open with the full schema YAML so we can replay it.