How to Build a Context Layer for Your Postgres Database

The four passes, in one paragraph

To build a context layer for a Postgres database, run four passes: inventory what exists from the system catalogs, harvest meaning from COMMENT metadata and column statistics, map relationships through declared and undeclared foreign keys, and assemble the results into one schema context document that your agent reads before it writes a query. That is the whole method. It covers the triad a context layer has to answer: what's there, what it means, and how it connects.

Everything below is the executable version. Every query runs against a stock Postgres install with no extensions, and every catalog and column name is taken from the current PostgreSQL documentation. The framing is database-agnostic (MySQL has information_schema too, and every engine has some catalog), but Postgres is the worked example because its catalogs are the most complete.

The three passes each answer one part of the triad, and all of them land in a single document.

Why a schema dump is not the answer

The obvious shortcut is pg_dump --schema-only pasted into the agent's context. It fails in three ways. It says nothing about meaning: status smallint is in the dump, but "3 means blocked in sales and priority in logistics" is not. It says nothing about liveness: the dump renders an abandoned table identically to the hottest table in the system. And it only contains the relationships someone bothered to declare, which in older databases is a minority of the joins the application actually performs. An agent given only a dump fills all three gaps by guessing from names, and name-guessing is how confident, plausible, wrong SQL gets written.

Pass 1: inventory what's there

Start with tables, their sizes, and any comments that already exist. pg_class holds one row per relation, relkind distinguishes ordinary tables (r) and partitioned tables (p), and reltuples is the planner's row estimate (it reads -1 if the table was never analyzed, so run ANALYZE first).

SELECT n.nspname                        AS schema,
       c.relname                        AS table_name,
       c.reltuples::bigint              AS estimated_rows,
       obj_description(c.oid, 'pg_class') AS table_comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY c.reltuples DESC;

Two things to read out of the result. Tables with high row estimates and no comment are your documentation debt, ranked. Tables with an estimate near zero are candidates for the "probably dead" list, which is context an agent badly needs so it does not join against a table nobody has written to since 2019.

Then columns. information_schema.columns gives the portable inventory, and col_description (columns have no OID of their own, so obj_description cannot be used here) attaches the comment by table OID and ordinal position:

SELECT c.table_name,
       c.column_name,
       c.data_type,
       c.is_nullable,
       c.column_default,
       col_description(pgc.oid, c.ordinal_position) AS column_comment
FROM information_schema.columns c
JOIN pg_namespace n  ON n.nspname = c.table_schema
JOIN pg_class pgc    ON pgc.relname = c.table_name
                    AND pgc.relnamespace = n.oid
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;

Pass 2: harvest what it means

Meaning lives in two places: comments people wrote, and statistics the database computed.

Comments first. Postgres stores exactly one comment per object, and a new COMMENT replaces the old one (Postgres docs), so treat comments as a maintained field, not an append-only log. The two statements you need:

COMMENT ON TABLE public.orders IS
  'One row per confirmed order. Draft carts live in cart_sessions, not here.';

COMMENT ON COLUMN public.orders.status IS
  'Lifecycle code: 1 pending, 2 shipped, 3 blocked. Value 4 retired in 2021, still present in old rows.';

The retrieval side is already wired into the pass 1 queries via obj_description and col_description. The useful discipline is closing the gap: take the pass 1 output, sort by row count, and write comments for every uncommented column in the top tables. This is the highest-leverage hour of the whole runbook, because a comment is the one piece of metadata that travels with the database itself rather than living in a wiki that drifts.

Statistics second. pg_stats exposes what ANALYZE learned about each column: null_frac is the fraction of entries that are null, n_distinct estimates distinct values (positive is an absolute count, negative is a ratio of distinct values to rows, so -1 means unique), and most_common_vals lists the dominant values.

SELECT tablename, attname, null_frac, n_distinct, most_common_vals
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY tablename, attname;

Read this as a meaning detector. A column with null_frac above 0.95 is probably abandoned, and the agent should be told so. A status column whose most_common_vals contains a value no comment explains is an undocumented convention waiting to cause a wrong WHERE clause. A column named like an identifier with a small positive n_distinct is probably an enum in disguise. Each anomaly becomes one line of prose in the context document.

Pass 3: map how it connects

Declared foreign keys live in pg_constraint with contype = 'f'. conrelid is the referencing table, confrelid the referenced one, and conkey and confkey are aligned arrays of column numbers, which is why the unnest below pairs them by ordinality:

SELECT con.conname,
       con.conrelid::regclass  AS from_table,
       a.attname               AS from_column,
       con.confrelid::regclass AS to_table,
       af.attname              AS to_column
FROM pg_constraint con
CROSS JOIN LATERAL unnest(con.conkey)  WITH ORDINALITY AS k(attnum, ord)
JOIN LATERAL unnest(con.confkey) WITH ORDINALITY AS fk(attnum, ord)
     ON fk.ord = k.ord
JOIN pg_attribute a  ON a.attrelid = con.conrelid  AND a.attnum = k.attnum
JOIN pg_attribute af ON af.attrelid = con.confrelid AND af.attnum = fk.attnum
WHERE con.contype = 'f'
ORDER BY from_table::text, con.conname;

Then the harder half: relationships nobody declared. In databases that grew up around ORMs or bulk loads, many real join paths have no constraint. Surface candidates by naming convention, excluding columns already covered by a declared key:

SELECT c.table_name,
       c.column_name,
       regexp_replace(c.column_name, '_id$', '') AS implied_parent
FROM information_schema.columns c
WHERE c.table_schema = 'public'
  AND c.column_name LIKE '%\_id'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_constraint con
    JOIN pg_class pgc ON pgc.oid = con.conrelid
    JOIN pg_attribute a ON a.attrelid = con.conrelid
                       AND a.attnum = ANY (con.conkey)
    WHERE con.contype = 'f'
      AND pgc.relname = c.table_name
      AND a.attname   = c.column_name
  );

Each candidate is a hypothesis, not a fact. Validate it with an orphan check: LEFT JOIN from the child column to the proposed parent key and count non-matches. Near zero orphans supports the relationship; anything else means the guess is wrong, the data is dirty, or the convention is more subtle than the name suggests. The full validation workflow, including value-overlap checks when the names do not cooperate at all, is in reverse engineering an undocumented schema.

Pass 4: assemble the context document

The output of the three passes is one document, small enough to fit in an agent's context window, generated by script so it can be regenerated on every migration. Structure matters more than format; YAML shown here because it is compact and diff-friendly in review.

Copy-paste template: minimal schema context document for an AI agent (fill one tables entry per live table).

database: shopdb
generated_at: 2026-07-13T09:00:00Z   # regenerate on every migration
engine: postgres 17

tables:
  - name: public.orders
    rows_estimate: 4800000            # pg_class.reltuples
    status: live                      # live | low-traffic | dead
    description: >
      One row per confirmed order. Draft carts live in
      cart_sessions, not here.
    columns:
      - name: status
        type: smallint
        meaning: "1 pending, 2 shipped, 3 blocked; 4 retired 2021, still in old rows"
        gotcha: "never filter status = 4 as an error state"
      - name: legacy_ref
        type: text
        meaning: unknown
        null_frac: 0.97               # from pg_stats; probably abandoned

relationships:
  - from: public.orders.customer_id
    to: public.customers.id
    declared: true                    # pg_constraint, contype = 'f'
  - from: public.orders.warehouse_id
    to: public.warehouses.id
    declared: false                   # inferred; orphan check: 0 rows
    evidence: "naming convention + zero orphans on 2026-07-13"

conventions:
  - "monetary amounts are integer cents, never numeric euros"
  - "soft delete via deleted_at; no row is ever hard-deleted"

do_not_touch:
  - public.audit_log                  # append-only, no agent writes

Three rules keep the document trustworthy. Regenerate it in the same pipeline that applies migrations, so it cannot drift from the schema. Keep meaning: unknown as an explicit value, because an honest gap is safer than a fluent guess. And version it in git, so "who decided warehouse_id joins to warehouses" has an answer with a commit hash.

Point your agent at the document with one instruction: read this before writing any SQL, and prefer its conventions and gotcha entries over anything inferred from names. For MCP-based setups, serve it as a resource next to your query tool.

What this runbook does not solve

Be honest about the limits. This is a batch snapshot: it goes stale between regenerations, it profiles only what ANALYZE sampled, and every semantic claim in it was validated by whoever ran the runbook, with no workflow for a second pair of eyes. It also does nothing to stop an agent that read the document and then wrote a destructive query anyway; context and enforcement are different layers, and the enforcement side is covered in safe AI agent access to production databases.

Where Datapace fits

This runbook is the manual version of what Datapace is building. Datapace is the semantic context layer for AI on databases: it runs this discovery continuously instead of as a one-off script, keeps the context document from drifting, and serves it to agents through a Context API and MCP, with a policy gate in front of the database rather than advice inside the prompt. We are pre-product and building it in the open. If you would rather not maintain the script version by hand, see datapace.ai or book a call.

Sources

1. PostgreSQL Documentation: pg_class
2. PostgreSQL Documentation: pg_constraint
3. PostgreSQL Documentation: Comment Information Functions (obj_description, col_description)
4. PostgreSQL Documentation: information_schema.columns
5. PostgreSQL Documentation: pg_stats view