Table Discovery & Column Metadata

An AI model cannot write useful queries against a database it does not understand. Schema exposure — making table structures, column types, and constraints visible as MCP resources — gives the model the structural context it needs to formulate intelligent data requests. Without schema resources, the model guesses at table names and column types, producing errors that waste tokens and frustrate users. With schema resources, the model can explore the data model, understand relationships, and construct precise queries.

The schema resource pattern exposes database metadata through information_schema or system catalogs. A single resource at db://schema returns the full catalog of exposed tables, their columns, data types, nullability, and default values. This is filtered through the same allowlist that governs data access — the schema resource only reveals tables the MCP server is authorized to expose. The model reads this resource first, builds a mental model of the schema, and then reads specific table resources with informed queries.

Column metadata should include more than just names and types. Include column descriptions (PostgreSQL COMMENT ON), whether the column is part of a primary key, whether it has a NOT NULL constraint, and its default value. This metadata helps the model understand which columns are required for lookups (primary keys), which might be null (optional fields), and what kind of values to expect. Rich metadata reduces the number of exploratory reads the model needs to understand the data.

// Expose filtered schema metadata as a resource
server.resource("schema", "db://schema", async (uri) => {
  const schema = await pool.query(`
    SELECT
      c.table_name,
      c.column_name,
      c.data_type,
      c.is_nullable,
      c.column_default,
      pgd.description AS column_comment,
      CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END AS is_primary_key
    FROM information_schema.columns c
    LEFT JOIN pg_catalog.pg_description pgd
      ON pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass
      AND pgd.objsubid = c.ordinal_position
    LEFT JOIN (
      SELECT ku.column_name, ku.table_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage ku
        ON tc.constraint_name = ku.constraint_name
      WHERE tc.constraint_type = 'PRIMARY KEY'
    ) pk ON pk.table_name = c.table_name AND pk.column_name = c.column_name
    WHERE c.table_schema = 'public'
      AND c.table_name = ANY($1)
    ORDER BY c.table_name, c.ordinal_position
  `, [ALLOWED_TABLE_NAMES]);

  return {
    contents: [{
      uri: uri.href,
      mimeType: "application/json",
      text: JSON.stringify(groupByTable(schema.rows)),
    }],
  };
});

1. Build the schema resource Query information_schema.columns filtered by your allowlist. Include data types, nullability, defaults, and primary key indicators.
2. Add column descriptions Use COMMENT ON COLUMN in your migrations to describe business meaning. Expose these comments in the schema resource so the model understands what each column represents, not just its type.
3. Group by table Structure the schema response as a table-keyed object with column arrays. This is easier for the model to parse than a flat list of column records.