Relationship Mapping

Tables do not exist in isolation — they are connected through foreign keys, join tables, and implicit relationships. An AI model that knows customers has a one-to-many relationship with orders can construct meaningful queries: "show me the top 5 customers by order volume." Without relationship metadata, the model sees disconnected tables and cannot reason about cross-table queries. Exposing foreign key relationships as a dedicated resource — db://schema/relationships — gives the model a graph view of your data model.

Foreign key extraction from information_schema gives you the explicit relationships — customer_id in orders references id in customers. But many real-world schemas have implicit relationships that are not enforced by foreign keys: a user_email column in logs that corresponds to email in users, or a product_sku in warehouse_inventory that matches sku in products. These should be documented in your relationship resource as "logical" relationships alongside the "physical" foreign key relationships.

interface Relationship {
  type: "foreign_key" | "logical";
  from: { table: string; column: string };
  to: { table: string; column: string };
  cardinality: "one-to-one" | "one-to-many" | "many-to-many";
  description?: string;
}

// Extract foreign key relationships from the database
async function getForeignKeys(): Promise<Relationship[]> {
  const fks = await pool.query(`
    SELECT
      tc.table_name AS from_table,
      kcu.column_name AS from_column,
      ccu.table_name AS to_table,
      ccu.column_name AS to_column
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage ccu
      ON tc.constraint_name = ccu.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND tc.table_name = ANY($1)
  `, [ALLOWED_TABLE_NAMES]);

  return fks.rows.map((fk) => ({
    type: "foreign_key" as const,
    from: { table: fk.from_table, column: fk.from_column },
    to: { table: fk.to_table, column: fk.to_column },
    cardinality: "one-to-many",
  }));
}

// Add logical relationships not enforced by FK constraints
const LOGICAL_RELATIONSHIPS: Relationship[] = [
  {
    type: "logical",
    from: { table: "audit_log", column: "actor_email" },
    to: { table: "users", column: "email" },
    cardinality: "one-to-many",
    description: "Audit entries linked to users by email address",
  },
];