agentboard — review

Target: packages/mcp-server-supabase/src/tools/database-operation-tools.ts Intent: Issue (the problem): list_tables verbose output represented composite foreign keys incorrectly. Originally a pg-meta SQL cross-join paired every source column with every target column (N^2 rows, fabricating relationships). An interim fix paired columns positionally but still emitted one row per column pair, so nothing signaled that two rows belong to one atomic constraint vs two independent FKs.

0 confirmed gap(s) · 12 covered/handled · 0 test didn't run · 12 behaviors reviewed

Domain: this change operates on PostgreSQL relational schema metadata introspection for the MCP list_tables tool, especially JSON representation of table constraints and foreign-key relationships.already covered

already covered — domain identified from list_tables verbose tests and pg-meta/table metadata code

axis: correctness

A single-column foreign key is represented as one constraint object whose source_columns and target_columns are one-element arrays.already covered

already covered — existing list_tables verbose foreign key assertion expects source_columns: ['user_id'] and target_columns: ['id']

axis: correctness

A composite foreign key is grouped into one atomic constraint object instead of one row per column pair.already covered

already covered — existing test 'composite FK is grouped as one constraint with positionally ordered columns' asserts one child_parent_fk constraint with two source and target columns

axis: correctness

Composite foreign-key column arrays preserve the constraint-definition order even when that order differs from physical column/attnum order.handled

not covered by existing tests — the existing composite FK test uses table column order that matches the FK definition order, so it does not prove ordering is by constraint ordinality rather than attnum

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('composite FK column arrays use constraint definition order rather than table column order', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create table parent (
      x int,
      y int,
      primary key (y, x)
    );
    create table child (
      a int,
      b int,
      constraint child_parent_fk
        foreign key (b, a) references parent (y, x)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const tableNames = result.tables
    .map((t: { name: string }) => t.name)
    .sort();
  expect(tableNames).toHaveLength(2);
  expect(tableNames).toEqual(['public.child', 'public.parent']);

  const childTable = result.tables.find(
    (t: { name: string }) => t.name === 'public.child'
  );
  const constraints = [...(childTable.foreign_key_constraints ?? [])].sort(
    (a, b) => a.name.localeCompare(b.name)
  );

  expect(constraints).toHaveLength(1);
  expect(constraints).toEqual([
    {
      name: 'child_parent_fk',
      source_table: 'public.child',
      source_columns: ['b', 'a'],
      target_table: 'public.parent',
      target_columns: ['y', 'x'],
    },
  ]);
});

Two independent single-column foreign keys between the same source and target tables remain two separate constraint objects rather than being merged or mistaken for one composite key.handled

not covered by existing tests — none found

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('independent FKs between the same tables remain separate constraint objects', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create table users (
      id int primary key
    );
    create table orders (
      id int primary key,
      buyer_id int,
      seller_id int,
      constraint orders_buyer_fk foreign key (buyer_id) references users (id),
      constraint orders_seller_fk foreign key (seller_id) references users (id)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const tableNames = result.tables
    .map((t: { name: string }) => t.name)
    .sort();
  expect(tableNames).toHaveLength(2);
  expect(tableNames).toEqual(['public.orders', 'public.users']);

  const ordersTable = result.tables.find(
    (t: { name: string }) => t.name === 'public.orders'
  );
  const constraints = [...(ordersTable.foreign_key_constraints ?? [])].sort(
    (a, b) => a.name.localeCompare(b.name)
  );

  expect(constraints).toHaveLength(2);
  expect(constraints).toEqual([
    {
      name: 'orders_buyer_fk',
      source_table: 'public.orders',
      source_columns: ['buyer_id'],
      target_table: 'public.users',
      target_columns: ['id'],
    },
    {
      name: 'orders_seller_fk',
      source_table: 'public.orders',
      source_columns: ['seller_id'],
      target_table: 'public.users',
      target_columns: ['id'],
    },
  ]);
});

When a listed table is the target of a foreign key, the relationship still reports the original source_table and source_columns rather than implying the listed target table is the source.handled

not covered by existing tests — none found; the existing composite FK test checks the source/child table only

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('target table FK relationship keeps the original source table', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create table parent (
      y int,
      x int,
      primary key (y, x)
    );
    create table child (
      b int,
      a int,
      constraint child_parent_fk
        foreign key (b, a) references parent (y, x)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const tableNames = result.tables
    .map((t: { name: string }) => t.name)
    .sort();
  expect(tableNames).toHaveLength(2);
  expect(tableNames).toEqual(['public.child', 'public.parent']);

  const parentTable = result.tables.find(
    (t: { name: string }) => t.name === 'public.parent'
  );
  const constraints = [...(parentTable.foreign_key_constraints ?? [])].sort(
    (a, b) => a.name.localeCompare(b.name)
  );

  expect(constraints).toHaveLength(1);
  expect(constraints).toEqual([
    {
      name: 'child_parent_fk',
      source_table: 'public.child',
      source_columns: ['b', 'a'],
      target_table: 'public.parent',
      target_columns: ['y', 'x'],
    },
  ]);
});

Cross-schema foreign keys preserve schema-qualified source_table and target_table names while still grouping composite columns positionally.handled

not covered by existing tests — none found

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('cross-schema composite FK reports schema-qualified source and target tables', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create schema private;
    create table private.parent (
      y int,
      x int,
      primary key (y, x)
    );
    create table public.child (
      b int,
      a int,
      constraint child_private_parent_fk
        foreign key (b, a) references private.parent (y, x)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public', 'private'],
      verbose: true,
    },
  });

  const tableNames = result.tables
    .map((t: { name: string }) => t.name)
    .sort();
  expect(tableNames).toHaveLength(2);
  expect(tableNames).toEqual(['private.parent', 'public.child']);

  const childTable = result.tables.find(
    (t: { name: string }) => t.name === 'public.child'
  );
  const constraints = [...(childTable.foreign_key_constraints ?? [])].sort(
    (a, b) => a.name.localeCompare(b.name)
  );

  expect(constraints).toHaveLength(1);
  expect(constraints).toEqual([
    {
      name: 'child_private_parent_fk',
      source_table: 'public.child',
      source_columns: ['b', 'a'],
      target_table: 'private.parent',
      target_columns: ['y', 'x'],
    },
  ]);
});

A table with no foreign keys does not fabricate foreign_key_constraints in verbose output.handled

not covered by existing tests — none found for the changed FK representation specifically

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('verbose list_tables does not fabricate FK constraints for tables without FKs', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create table solo (
      id int primary key
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const tableNames = result.tables
    .map((t: { name: string }) => t.name)
    .sort();
  expect(tableNames).toHaveLength(1);
  expect(tableNames).toEqual(['public.solo']);

  const soloTable = result.tables.find(
    (t: { name: string }) => t.name === 'public.solo'
  );
  expect(soloTable.foreign_key_constraints).toBeUndefined();
});

Repeated verbose list_tables calls for the same composite FK return the same complete constraint object and column order.handled

not covered by existing tests — none found; existing tests call the tool once per schema setup

axis: consistency

Observed: test passed — the tool already does this

test the agent wrote

test('composite FK verbose output is stable across repeated list_tables calls', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(`
    create table parent (
      x int,
      y int,
      primary key (y, x)
    );
    create table child (
      a int,
      b int,
      constraint child_parent_fk
        foreign key (b, a) references parent (y, x)
    );
  `);

  async function getChildConstraints() {
    const result = await callTool({
      name: 'list_tables',
      arguments: {
        project_id: project.id,
        schemas: ['public'],
        verbose: true,
      },
    });

    const tableNames = result.tables
      .map((t: { name: string }) => t.name)
      .sort();
    expect(tableNames).toHaveLength(2);
    expect(tableNames).toEqual(['public.child', 'public.parent']);

    const childTable = result.tables.find(
      (t: { name: string }) => t.name === 'public.child'
    );
    return [...(childTable.foreign_key_constraints ?? [])].sort((a, b) =>
      a.name.localeCompare(b.name)
    );
  }

  const firstConstraints = await getChildConstraints();
  const secondConstraints = await getChildConstraints();

  expect(firstConstraints).toHaveLength(1);
  expect(firstConstraints).toEqual([
    {
      name: 'child_parent_fk',
      source_table: 'public.child',
      source_columns: ['b', 'a'],
      target_table: 'public.parent',
      target_columns: ['y', 'x'],
    },
  ]);
  expect(secondConstraints).toHaveLength(1);
  expect(secondConstraints).toEqual(firstConstraints);
});

A self-referential composite foreign key should be reported exactly once for the table, not duplicated because the same table is both source and target.handled

not covered by existing tests — proposed by critic (2nd-pass, different model)

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('list tables reports a self-referential composite foreign key exactly once', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(stripIndent`
    create table public.nodes (
      tenant_id integer not null,
      node_id integer not null,
      parent_tenant_id integer,
      parent_node_id integer,
      primary key (tenant_id, node_id),
      constraint nodes_parent_fk
        foreign key (parent_tenant_id, parent_node_id)
        references public.nodes (tenant_id, node_id)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const actualTables = result.tables
    .map((table: any) => ({
      name: table.name,
      foreign_key_constraints: [...(table.foreign_key_constraints ?? [])].sort(
        (a: any, b: any) =>
          `${a.name}|${a.source_table}|${a.target_table}`.localeCompare(
            `${b.name}|${b.source_table}|${b.target_table}`
          )
      ),
    }))
    .sort((a: any, b: any) => a.name.localeCompare(b.name));

  const expectedTables = [
    {
      name: 'public.nodes',
      foreign_key_constraints: [
        {
          name: 'nodes_parent_fk',
          source_table: 'public.nodes',
          source_columns: ['parent_tenant_id', 'parent_node_id'],
          target_table: 'public.nodes',
          target_columns: ['tenant_id', 'node_id'],
        },
      ],
    },
  ];

  expect(actualTables).toHaveLength(expectedTables.length);
  actualTables.forEach((table: any, index: number) => {
    expect(table.foreign_key_constraints).toHaveLength(
      expectedTables[index].foreign_key_constraints.length
    );
  });
  expect(actualTables).toEqual(expectedTables);
});

When only the target schema is requested, an incoming composite foreign key from a source table in an unlisted schema should still be included on the listed target table with the true source_table.handled

not covered by existing tests — proposed by critic (2nd-pass, different model)

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('list tables includes incoming cross-schema composite foreign keys from unlisted source schemas', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(stripIndent`
    create schema private;

    create table public.accounts (
      tenant_id integer not null,
      account_id integer not null,
      primary key (tenant_id, account_id)
    );

    create table private.audit_events (
      tenant_id integer not null,
      account_id integer not null,
      event_id integer not null,
      primary key (tenant_id, event_id),
      constraint audit_events_account_fk
        foreign key (tenant_id, account_id)
        references public.accounts (tenant_id, account_id)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const actualTables = result.tables
    .map((table: any) => ({
      name: table.name,
      foreign_key_constraints: [...(table.foreign_key_constraints ?? [])].sort(
        (a: any, b: any) =>
          `${a.name}|${a.source_table}|${a.target_table}`.localeCompare(
            `${b.name}|${b.source_table}|${b.target_table}`
          )
      ),
    }))
    .sort((a: any, b: any) => a.name.localeCompare(b.name));

  const expectedTables = [
    {
      name: 'public.accounts',
      foreign_key_constraints: [
        {
          name: 'audit_events_account_fk',
          source_table: 'private.audit_events',
          source_columns: ['tenant_id', 'account_id'],
          target_table: 'public.accounts',
          target_columns: ['tenant_id', 'account_id'],
        },
      ],
    },
  ];

  expect(actualTables).toHaveLength(expectedTables.length);
  actualTables.forEach((table: any, index: number) => {
    expect(table.foreign_key_constraints).toHaveLength(
      expectedTables[index].foreign_key_constraints.length
    );
  });
  expect(actualTables).toEqual(expectedTables);
});

Multiple incoming composite foreign keys with the same constraint name but different source tables should remain distinct constraint objects on the target table.handled

not covered by existing tests — proposed by critic (2nd-pass, different model)

axis: correctness

Observed: test passed — the tool already does this

test the agent wrote

test('list tables does not merge incoming composite foreign keys that share a constraint name', async () => {
  const { callTool } = await setup();

  const org = await createOrganization({
    name: 'My Org',
    plan: 'free',
    allowed_release_channels: ['ga'],
  });

  const project = await createProject({
    name: 'Project 1',
    region: 'us-east-1',
    organization_id: org.id,
  });
  project.status = 'ACTIVE_HEALTHY';

  await project.db.exec(stripIndent`
    create table public.parent_accounts (
      tenant_id integer not null,
      account_id integer not null,
      primary key (tenant_id, account_id)
    );

    create table public.documents (
      document_id integer generated by default as identity primary key,
      parent_tenant_id integer not null,
      parent_account_id integer not null,
      constraint fk_parent_account
        foreign key (parent_tenant_id, parent_account_id)
        references public.parent_accounts (tenant_id, account_id)
    );

    create table public.folders (
      folder_id integer generated by default as identity primary key,
      parent_tenant_id integer not null,
      parent_account_id integer not null,
      constraint fk_parent_account
        foreign key (parent_tenant_id, parent_account_id)
        references public.parent_accounts (tenant_id, account_id)
    );
  `);

  const result = await callTool({
    name: 'list_tables',
    arguments: {
      project_id: project.id,
      schemas: ['public'],
      verbose: true,
    },
  });

  const actualTables = result.tables
    .map((table: any) => ({
      name: table.name,
      foreign_key_constraints: [...(table.foreign_key_constraints ?? [])].sort(
        (a: any, b: any) =>
          `${a.name}|${a.source_table}|${a.target_table}`.localeCompare(
            `${b.name}|${b.source_table}|${b.target_table}`
          )
      ),
    }))
    .sort((a: any, b: any) => a.name.localeCompare(b.name));

  const expectedTables = [
    {
      name: 'public.documents',
      foreign_key_constraints: [
        {
          name: 'fk_parent_account',
          source_table: 'public.documents',
          source_columns: ['parent_tenant_id', 'parent_account_id'],
          target_table: 'public.parent_accounts',
          target_columns: ['tenant_id', 'account_id'],
        },
      ],
    },
    {
      name: 'public.folders',
      foreign_key_constraints: [
        {
          name: 'fk_parent_account',
          source_table: 'public.folders',
          source_columns: ['parent_tenant_id', 'parent_account_id'],
          target_table: 'public.parent_accounts',
          target_columns: ['tenant_id', 'account_id'],
        },
      ],
    },
    {
      name: 'public.parent_accounts',
      foreign_key_constraints: [
        {
          name: 'fk_parent_account',
          source_table: 'public.documents',
          source_columns: ['parent_tenant_id', 'parent_account_id'],
          target_table: 'public.parent_accounts',
          target_columns: ['tenant_id', 'account_id'],
        },
        {
          name: 'fk_parent_account',
          source_table: 'public.folders',
          source_columns: ['parent_tenant_id', 'parent_account_id'],
          target_table: 'public.parent_accounts',
          target_columns: ['tenant_id', 'account_id'],
        },
      ],
    },
  ];

  expect(actualTables).toHaveLength(expectedTables.length);
  actualTables.forEach((table: any, index: number) => {
    expect(table.foreign_key_constraints).toHaveLength(
      expectedTables[index].foreign_key_constraints.length
    );
  });
  expect(actualTables).toEqual(expectedTables);
});