What do you use to keep your coding agent oriented in a big Postgres schema?

Codex Codex CLI
,
1

Hey guys, quick question(s) and a trick from me (if useful).

I run a Postgres-native SaaS. 120+ tables, RLS on everything, the public API is security_invoker views plus security definer RPCs behind PostgREST. The real access logic lives in the catalog, not in the app code, which is great right up until your agent needs to know what is actually in there right now.

My problem was never that the agent could not find the answer. It was that finding it cost a fortune. It would reconstruct the schema from migrations top to bottom, or grep a pg_dump that reads like a phone book, connect ten things by hand, and burn half the context window before saying anything useful. A dump is a wall, not an index.

So before I keep tweaking mine, what do you actually use for this? Live MCP server on prod, tbls, a hand-written doc you pray is current, just the dump? Curious what survives past 100 tables.

Here is my version. One read-only script introspects the catalogs and writes the current state into committed files: a navigable database.json, one full database.md, and one self-contained file per table and view. It is deterministic and stamped with the migration version, so a rerun on an unchanged schema is byte-identical and every git diff is a real change. Everything about one table (columns, keys, indexes, triggers, the RLS policies with their real expressions, grants) sits in one file.

I just left a short line in the instructions saying, roughly, when you have a database question the answer is probably already sitting in state/, go look there first (+command examples how to find stuff).

So when I ask something like “what enforces access to document shares”, it opens the state, treats it as plain searchable text, and lands on the relevant policy and the code behind it almost immediately. Often faster than chasing IDs and parsing the source blind, and without polluting the window with a reconstruction nobody asked for.

jq '.core.tables.documents."foreign-keys"' state/database.json

The bit I want opinions on is comments. The tool marks UNCOMMENTED wherever a COMMENT ON is missing, so the output doubles as a to-do list. But I do not want comments that restate the obvious (status holds the status, thank you very much). The rule I am moving to: a comment carries what the architect knows and the tools cannot see, why the thing exists, how it connects to the rest, and the blast radius if you touch it. Drop everything you can already read from the definition, keep only the reasoning. Store it as a real COMMENT ON so it travels with the schema and gets picked up automatically.

Two questions:

  1. What do you use to keep an agent oriented in a large database, and does it survive schema churn without babysitting?
  2. If you store context as DB comments, do you have a rule for what goes in versus what is just noise?

Happy to share the script, it is tiny. Cheers.

2

Spent another couple of hours building this:

Help yourself, help your agents helping you :rofl:

3

For large schemas, I would avoid giving the agent either the full DDL dump or only natural-language notes. Both fail in different ways: the full dump is too noisy, and prose gets stale too easily.

What has worked best for me is a small generated “schema orientation pack” checked into the repo:

  • table purpose in one sentence
  • primary keys and important foreign keys
  • ownership boundary, for example billing vs auth vs product data
  • common join paths
  • columns the agent must not casually change
  • a few canonical queries used by tests or dashboards
  • the exact command that regenerates the pack from the live catalog

The key is that it should be deterministic and diffable. If the agent proposes a migration, it should also update the generated schema summary or explain why it does not need to. That catches a surprising number of wrong assumptions before they become code.

I would also keep two files separate:

  1. machine-generated catalog facts, regenerated from Postgres
  2. human-written domain rules, reviewed like normal code

Agents are much easier to steer when facts and judgment are not mixed together.

4

Thanks for details. Sounds totally reasonable and useful. The tool above tries to follow this approach. I’d definitely love to hear your feedback on the tool of you have some time to test it. Thanks.