list_tables reports primary key columns in attnum order, and includes non-key INCLUDE columns

[Rjabov]

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

list_tables returns primary_keys sorted by the columns' physical position in the table rather than by their position in the key. When a composite primary key is declared in an order that differs from the table's column order, the reported key is wrong. Separately, for a PRIMARY KEY ... INCLUDE (...) index, the INCLUDE payload columns are reported as part of the primary key.

To Reproduce

create schema app;

create table app.membership (
  org_id  int not null,          -- physically column 1
  user_id int not null,          -- physically column 2
  role    text,
  primary key (user_id, org_id)
);

create table app.triple (
  p int not null, q int not null, r int not null,
  primary key (r, q, p)
);

create table app.inc (a int not null, b int not null, c int not null);
create unique index inc_pk_idx on app.inc (a) include (b);
alter table app.inc add constraint inc_pk primary key using index inc_pk_idx;

Call list_tables with verbose: true:

table	declared	primary_keys returned
app.membership	(user_id, org_id)	["org_id","user_id"]
app.triple	(r, q, p)	["p","q","r"]
app.inc	(a) INCLUDE (b)	["a","b"]

Single-column primary keys are unaffected, which is probably why this has gone unnoticed.

Root cause

pg-meta/tables.sql, the primary key subquery:

        and a.attnum = any (i.indkey)
        and i.indisprimary

Two problems:

• = any (indkey) is a set-membership test - it discards each column's position within the key. Rows come back in pg_attribute scan order (attnum order), and the enclosing jsonb_agg has no order by, so the emitted array is attnum-ordered.
• indkey holds all index columns (indnatts), not just key columns (indnkeyatts). A PRIMARY KEY (a) INCLUDE (b) index has indkey = {1,2}, so the payload column is reported as a key column.

This is the same defect #317 fixed in the foreign key subquery directly below, which now walks columns positionally with unnest(conkey, confkey) with ordinality and aggregates order by cols.ord. That PR's description states the goal explicitly - columns in "constraint-definition order (not attnum, not alphabetical)". The primary key subquery a few lines above was not updated.

Expected behavior

primary_keys should list the key's columns in constraint-definition order, and should contain only key columns.

Why it matters

primary_keys is part of the published output of @supabase/mcp-server-supabase. Consumers that read it programmatically - schema/codegen tooling, the schema-docs work in #278 - have no way to notice the discrepancy and no reason to re-check.

For the ordering case, the leading column determines which queries the index can serve, so a reversed key inverts that reasoning. For the INCLUDE case the report is not merely mis-ordered: it claims a uniqueness guarantee across (a, b) that the database does not enforce.

I did check whether this actually misleads an agent in practice, and mostly it does not - in my testing models re-queried pg_index/pg_constraint through execute_sql rather than trusting the field, so the answer usually came out right anyway. That is the honest result. But it means the tool is paying for a catalog round-trip it exists to save, and it does not help a non-agent consumer at all. (Details, and the one case that did go wrong, in the PR.)

System information

• @supabase/mcp-server-supabase at main (fc54ea2)
• Reproduced against PostgreSQL 16.4
• Node.js 22 LTS, pnpm 10

Additional context

I have the fix written and tested locally - two regression tests, full unit suite green, Biome and typecheck clean. Happy to open a PR.

While in this query I also noticed that a foreign key referencing a partitioned table is reported once per leaf partition, because the FK subquery does not filter conparentid = 0. Separate defect - happy to file it on its own if that would be useful.