Constellation binds foreign_key_constraint_on: <column> to a composite FK when the same column is in two constraints

I have a table with two foreign keys that share a column. One is a normal single-column FK (org_id → orgs.id). The other is a composite FK ((user_id, org_id) → org_users(id, org_id)).

The object relationship is the Hasura shortcut:

using: foreign_key_constraint_on: org_id Hasura binds that to orgs. Constellation binds it to org_users.

After that, a nested select permission like org.users.user_id fails while the GraphQL roots are built, and the whole Postgres connector shows up as inconsistent. The error looks like:

building database connector: failed to create postgres connector for default: failed to create sql connector: failed to build GraphQL roots: failed to initialize table gamemode.session_catches: failed to fix select permission columns for role user: failed to fix permission columns for relationship session: column or relationship not found in table: members not found in table session_members

or, with the generic repro below:

users not found in table org_users What happened: Postgres default-names the composite constraint orders_user_id_org_id_fkey, which sorts before orders_org_id_fkey. Constellation introspects FKs ORDER BY conname and then takes the first row whose column name matches. So the composite key wins even though the metadata only named org_id.

To Reproduce

Apply this schema:

CREATE TABLE public.orgs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);
CREATE TABLE public.org_users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid NOT NULL,
  user_id uuid NOT NULL,
  UNIQUE (id, org_id),
  CONSTRAINT org_users_org_id_fkey
    FOREIGN KEY (org_id) REFERENCES public.orgs (id)
);
CREATE TABLE public.orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid NOT NULL,
  user_id uuid NOT NULL,
  CONSTRAINT orders_org_id_fkey
    FOREIGN KEY (org_id) REFERENCES public.orgs (id),
  CONSTRAINT orders_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES public.org_users (id),
  CONSTRAINT orders_user_id_org_id_fkey
    FOREIGN KEY (user_id, org_id)
    REFERENCES public.org_users (id, org_id)
);

Track the tables and add:

• orders.org → foreign_key_constraint_on: org_id
• orgs.users → array rel from org_users.org_id
• user select on orders with filter:

filter:
  org:
    users:
      user_id:
        _eq: X-Hasura-User-Id

Reload metadata / start Constellation against that database.

Connector for default is inconsistent. Permission walk is looking for users on org_users instead of orgs.

Same metadata works on Hasura.

Expected behavior

foreign_key_constraint_on: org_id should use the constraint whose local columns are exactly {org_id}, i.e. orders_org_id_fkey → orgs. Nested filters on org.users should then resolve.

Screenshots N/A.

Desktop OS: Windows 11 Version: Constellation current main (reproduced against local Nhost + Hasura metadata)

Additional context

Workaround that works today: skip the shortcut and use manual_configuration with org_id → id on orgs.

If it helps: the first-match sits in LookupForwardFKTarget (connector/sql/introspection/object.go). Postgres introspection in populateForeignKeys unnests composite FKs per column and orders by conname, with no constraint identity on ForeignKey. pairForwardColumns has the same first-match for joins.