ChatPlotDB: a look under the hood

The obvious way to build this is to hand a model your schema and a question and let it write SQL. That version demos well and fails in production, for a reason worth being precise about: it is right often enough that you stop checking, and wrong in ways that look like answers.

The configuration layer

A schema tells you what columns exist. It does not tell you which of the four date columns on an order means the thing a human means by “ordered”, or that the finance team's “revenue” excludes a tax column the product team includes. A model asked to infer that will infer something — plausibly, differently each time.

So we made it explicit. A config names the tables worth exposing, the joins that are real, the metrics that bind a business term to a SQL expression, the synonyms that pin your team's words to one canonical meaning, and golden queries: verified question-and-SQL pairs to work from.

That config is rendered into the agent's prompt — and then enforced against what comes back out of it. SQL referring to a table the config does not list is rejected before it reaches the database. A query that names a configured metric without using that metric's verified expression is flagged. The prompt is where the config is a suggestion; the gate after generation is what makes it a rule.

Planning, and asking rather than guessing

Given a question, the agent resolves it against the config first: which tables, which joins, which definition of the metric you named. When the config offers more than one answer to that last question, it asks you instead of picking. An ambiguous question answered confidently is the most expensive output this system can produce, so ambiguity is treated as a result, not an error.

Read-only, then verified

Generated SQL can be held for approval before it runs — in the app or from a Slack thread, with the buttons in the thread. Every query then executes inside a transaction that opens with SET TRANSACTION READ ONLY, carries a statement timeout, is wrapped in a row cap, and ends in a rollback rather than a commit.

Worth saying what that is not. We do not require you to hand us a credential that is already scoped read-only — you should, and the advice holds whatever tool you use — because a guarantee resting on how you provisioned a role is not a guarantee we can make on your behalf. The transaction is ours to control, so that is where the promise lives.

After execution, a second pass reads the result before the agent answers. It is warn-only and never blocks: what it finds is appended to what the agent sees, so the agent corrects or caveats rather than the system quietly rewriting your answer behind you.

What it looks for are the failures that raise no error at all. A query that returned zero rows. An aggregation question that came back with thousands of them, which usually means a missing GROUP BY. A query ending in LIMIT 100 that returned exactly 100 rows, where reporting 100 as the total would be wrong. Inf or NaN in a column, which is almost always an unguarded division by zero. A ROW_NUMBER or LAG with no ORDER BY anywhere, whose ordering is therefore undefined. And a “top N per group” question whose SQL has no PARTITION BY, which answers a different question perfectly well.

Why a stitched config starts out broken

A stitched config composes two to five connections into one queryable surface, and the join keys are the whole trick. They are also the one thing that cannot be derived from the children: each child's config describes its own database in isolation and says nothing about how it relates to a sibling.

So a join key is treated as a factual claim — that a column here and a column there hold the same identifier — and checked against the actual databases before it is allowed to persist. The check fails closed: if a child cannot be reached, nothing saves. A claim you could not check is not a claim you may save. We learned that from a production config saved with a join key naming a table that did not exist in the target database at all.

That friction is deliberate. Within one database a wrong join usually breaks loudly. Across two it produces a number: rows multiplied by a cardinality nobody checked, or dropped by an inner join between systems that disagree about which customers exist. There is no error to surface, so the only place to catch it is before the first query. A stitched parent that is still a bare skeleton — no identity, no per-child role, nothing telling the agent when to reach for which child — is blocked from chat outright rather than allowed to answer badly.

Child schemas are not loaded up front either. The parent prompt carries one pointer per child, and the agent pulls a child's full schema only once it has committed to that child. Four idle schemas stay out of the context window instead of being paid for on every turn.

The through-line: every stage that could be silently wrong is made visible instead. The config is readable, the SQL is shown, the result is verified, and the join keys need a signature.