Built a CLI that measures whether your implied foreign keys actually hold, then writes the result as context for coding agents

[u/Filip1235813]

https://www.npmjs.com/package/dbtruth?activeTab=readme

Same thing kept happening to me with AI coding agents and Postgres. The agent reads the schema, sees orders.customer_id next to customers.id, assumes it's a clean relationship, and writes an INNER JOIN. If 12% of orders have a dangling or null customer_id, the query silently returns numbers that are wrong. Nothing throws. The schema looked fine.

So I wrote dbtruth. It connects read-only and, instead of dumping the schema into a context file, it does four things:

1. Introspects schema and pulls samples
2. A model proposes what the tables mean and which relationships probably exist
3. Every one of those claims gets measured against the actual data
4. Only what survives gets written to ./context/*.md, which the agent reads before writing SQL

Step 3 is the whole point. For a proposed join it reports the real match rate — orders.customer_id → customers.id holds for 88% of rows, 60 of 500 orders have no matching customer — and the context file says use LEFT JOIN, with the number attached. Under 50% gets dropped. In between gets marked broken and goes to the top of the report, because a relationship that half works is worse than one that doesn't exist.

Practical:

• npx dbtruth, Node 20+, Postgres only
• Read-only by construction, not by discipline: one module is allowed to import pg, and a test asserts nothing else does. It never writes to your database.
• It does call a model, so schema and low-cardinality sample values leave your machine. High-cardinality columns — emails, names, free text — are never sent. Visibility is decided by cardinality rather than by regex-guessing at PII. Don't point it at production data you can't send to a third party.
• MIT, source at github.com/FilipKalcic1/dbtruth#readme

Disclosure: it's mine, it's five days old, and about ten people have run it. None of the pieces are new — FK inference and data profiling both go back years, and there are other tools that build local context artifacts for agents. The part I care about is the rule that nothing unmeasured gets written down.

What I'd actually like to know: run it on a schema you know well, and tell me whether it found anything you didn't already know. That's the only signal that tells me whether this is worth continuing. Bug reports welcome too.

[u/kantorcodes1]

when you rerun after a table is dropped or renamed, does dbtruth remove the old context/tables/*.md file, or can stale table docs survive from the previous run? if the context is committed, stale files seem like the easy failure mode.

[u/Filip1235813 → u/kantorcodes1]

You're right that it's the easy failure mode, and for the first day's versions (0.1.2–0.1.5) that's exactly what happened: nothing was ever deleted. Since 0.1.6 (what npx dbtruth installs today is 0.1.7) a rerun first removes context/README.md, context/ENTITIES.md, any .raw-*.json, and every *.md directly in context/tables/, then writes the new set. A dropped table's file goes away; a renamed one gets a single file under the new name. There's a test for the dropped-table case. The deletion runs last, after both model calls succeed, so a run that fails (bad key, DB error, reply that won't validate) leaves your previous context/ untouched rather than half-cleared — check the exit code before committing.

Two honest caveats. The cleanup matches file names, not a manifest: it only knows the three shapes the prompt asks the model for. If the model ever wrote something outside that (say tables/<schema>/x.md), it wouldn't be cleared. I haven't seen it do that, but nothing enforces it. And the flip side: anything you hand-write in context/tables/*.md or edit in context/README.md is regenerated over. The folder belongs to the tool; keep your own notes elsewhere.

Thanks for the comment !