supabase / mcp / 29614297311

96%

push

github

fix(pg-meta): pair composite FK columns positionally to avoid cartesi… (#317)

### What kind of change does this PR introduce?

Bug fix: data-integrity issue in `list_tables` (verbose) foreign-key
output.

### What is the current behavior?

For any composite (multi-column) foreign key, `list_tables` (verbose)
returns the **cartesian product** of the source and target columns. An
N-column FK produces N² `foreign_key_constraints` entries instead of N :
reporting column pairings that do not exist in the schema.

Repro:

```sql
create table public.parent (a int not null, b int not null, primary key (a, b));
create table public.child (
  a int not null, b int not null,
  constraint child_parent_fk foreign key (a, b) references public.parent (a, b)
);
```

`list_tables` with `verbose: true` returns 4 constraints for
`child_parent_fk`:

```
public.child.a => public.parent.a
public.child.a => public.parent.b   <- does not exist
public.child.b => public.parent.a   <- does not exist
public.child.b => public.parent.b
```

Only `a=>a` and `b=>b` are real. Because this tool exists so an AI agent
can reason about database structure, the fabricated relationships are
silently trusted - an agent could infer key relationships that don't
exist.

Root cause is in `pg-meta/tables.sql`. The FK subquery joins source and
target columns independently:

```sql
... on sa.attrelid = c.conrelid  and sa.attnum = any (c.conkey)
... on ta.attrelid = c.confrelid and ta.attnum = any (c.confkey)
```

`any(conkey)` against `any(confkey)` cross-joins every source column
with every target column, instead of pairing them positionally.

### What is the new behavior?

Each foreign key is now returned as **one constraint object** with the
grouped shape discussed in the review below:

```json
{
  "name": "child_parent_fk",
  "source_table": "public.child",
  "source_columns": ["a", "b"],
  "target_table": "public.parent",
  "target_columns": ["a", "b"]
}
```

- Columns are walked in lockstep w... (continued)

326 of 360 branches covered (90.56%)

Branch coverage included in aggregate %.

12 of 12 new or added lines in 2 files covered. (100.0%)

2803 of 2888 relevant lines covered (97.06%)

47.18 hits per line