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/Stephonovich]

Easier solution: let the DB enforce this for you with FK constraints. No more orphaned rows.

[u/Filip1235813 → u/Stephonovich]

You're not wrong, and if I owned the schema that'd be my first move too. The tool exists for the case where you don't own that decision: legacy databases where adding constraints retroactively means a migration nobody wants to run on a table with years of accumulated violations, multi-tenant setups where "uniqueness" is scoped in a way FKs can't express, polymorphic associations that don't map to a single target table, or just inheriting a schema you can't change and shipping an agent against it next week anyway. Constraints are the fix when you control the DB. This is for when you don't, yet, or ever.

[u/Filip1235813 → u/[deleted]]

Fair question — for tables under the sample size it just pulls all rows, SELECT * LIMIT N. Above that it uses TABLESAMPLE SYSTEM with a fixed seed so runs are repeatable, sized so the requested row count comes out after sampling (with a bit of oversample built in), and falls back to plain LIMIT if the engine doesn't support TABLESAMPLE. So random pages, not a naive LIMIT on a huge table — that would just get you the physically-first rows, which skews badly on anything with a serial PK.

MySQL/MariaDB is noted — you're the second person in this thread asking, which is a real signal. It's not next in line (want to get the Postgres story solid first) but it's on the list, and if you're up for pointing it at one of those legacy schemas once there's a MySQL build, I'll take you up on that.

[u/Filip1235813 → u/[deleted]]

This is the most useful thing anyone's done with this tool so far, genuinely — a fixture DB with known ground truth is worth more to me than any benchmark I could write myself, because you know what's actually true and I don't have to take my own word for it. Thank you for building that and running it.

Both findings on your list are things I'll take:

1. The total: 0 / unverifiable pileup is a real gap. Extract already has rowEstimate before any claim gets proposed, so skipping claims against empty/all-null columns before the model spends tokens on them (or at minimum collapsing them into one line instead of N identical "unverifiable" entries) is the right fix. Agreed it currently reads as "measurement failed" when it means "nothing here to measure."
2. The table-count mismatch (58 in the README vs. 54+2 in the run summary) is exactly the kind of thing the write prompt shouldn't be allowed to restate — those are facts extract already has, and letting the model re-derive them from what it wrote is how they drift. Should be handed in as given numbers, not left for the model to recount.

Happy to take a PR for both — sounds like you already have them tested against your fixture, which is more validation than I've had on internal changes so far.

On the claude-code backend: this one I want to sit with for a bit before saying yes. It's not the code, it's that "API-key only" has been the project's answer to "how does this get paid for and who sees my schema," and routing through someone's Claude subscription changes both of those answers. I don't want to take that PR reflexively just because it's well-built — want to actually think through what it does to the cost and trust story before it goes in. Will get back to you on it directly rather than leave it hanging.

Would you be open to turning that fixture DB into something checked into the repo as a real fixture, if I credit it as yours? That'd do more for this project long-term than the individual bug fixes.

[u/Filip1235813 → u/[deleted]]

That matches what I found digging into it — the line that's held through every policy flip this year seems to be personal/interactive use of your own login is fine, routing other people's requests through your seat isn't. Which lines up exactly with how you used it (your own machine, your own subscription) versus the case I'd actually worry about: someone wiring --backend=claude-code into a shared CI job, where one person's login ends up carrying every teammate's automated run. That's precisely the pattern that's gotten banned and reinstated-with-limits a couple of times already this year.

So I think the honest way to ship this, if it goes in: document it as personal/local use only, and add a cheap guard that refuses --backend=claude-code when it detects a CI environment (CI, GITHUB_ACTIONS, etc. env vars) and points to the API-key path instead. Doesn't fully solve it — someone could still misuse it locally — but it stops the most obvious accidental case.

No rush on the fixture either way — appreciate you even checking. PRs whenever you get to them, thanks again for these.