Work / 04 / sql-analyst-agent
Chat with your database, safely.
Business users want answers from the company database without waiting on an analyst. This open-source agent turns a plain-English question into SQL, shows the query it ran, charts and explains the result, and cannot change your data. It was measured on a public text-to-SQL benchmark, not on hand-picked demos.
The problem
Letting a language model write SQL against production data raises three worries. It might change or delete data. It might hang the database. And it might return a confident wrong number.
This agent makes the first two impossible by construction: three independent read-only layers, statement timeouts and row caps. It attacks the third by being checkable. Every answer comes with:
- the SQL that ran, the row count and the result table;
- a chart picked by rules from the shape of the result;
- a short explanation whose every number is matched against the rows;
- the assumptions it made, and a step trace with tokens, cost and latency.
When a question is ambiguous (“best customers: by revenue or by orders?”) it asks instead of guessing. When a question asks it to change data, it declines and says why.
Read-only is a property of the system, not of the prompt
A prompt that says “only write SELECT” is a request, not a control. So the agent never relies on it. Each layer blocks writes by itself, and each was tested with the others switched off.
| Layer | Result | Test |
|---|---|---|
| 1 · SQL guard. sqlglot parses the query, allows a single SELECT, walks the tree for forbidden nodes and system catalogs, sets a LIMIT, and re-renders what it checked. | 45 / 45 | hostile SQL strings refused |
2 · Read-only connection. SQLite mode=ro + query_only + an authorizer; Postgres BEGIN READ ONLY with a statement timeout, always rolled back. | 37 / 37 | writes refused by SQLite alone, guard bypassed; database hash unchanged |
3 · Database role. Postgres role analyst_ro with SELECT grants only, read-only by default, 5 s timeout. | 11 / 11 | writes refused by the role, guard bypassed, real compose image |
| End to end. 25 hostile questions and prompt injections. | 0 | non-SELECT statements executed; all 25 declined with a reason |
Safety that blocks real work is not safety anyone keeps. The guard accepts all 500 / 500 BIRD gold queries, and re-rendering them changes 0 / 500 results, so it can safely add LIMITs. Six harmless questions that contain words like “delete”, “update” or “drop” were all answered.
cannot_answer field to the structured output let the model decline all 25 in one call, with a reason the user can read.
DROP TABLE. One model call, declined with a reason, nothing sent to the database. Real run against the Postgres demo, with the schema browser on the left.Retrying only on signals that mean “wrong”
The agent is a small LangGraph state machine. The model writes SQL as structured JSON: a query, a clarifying question, or a reason to decline. Everything after that is code.
A retry fires only on a concrete signal: a database error, a guard refusal, a timeout, or an empty or all-NULL result. The exact error goes back to the model with the failed SQL, up to three attempts. If the model writes the same query twice for an empty result, the empty result is accepted as the answer.
On the 400 test questions, 31 were retried and 20 were rescued (wrong on the first attempt, right at the end): 12 after an empty result, 5 after a timeout, 3 after a guard refusal. None were broken. Queries ending in an execution error fell from 6 to 2.
A model “review” pass over every result was built and measured, then left off. On the dev split it rescued 8 questions but broke 2, scored 69% against 70% without it, and doubled calls per question (2.1), prompt tokens and p95 latency (18.1 s vs 9.5 s).
Results on BIRD
BIRD is a public text-to-SQL benchmark of realistic, messy databases. Its mini-dev set has 500 questions over 11 SQLite databases, 460 of them labelled challenging. Questions were split once with a fixed seed: 100 for all iteration, 400 held out and run once per configuration.
| Configuration (test split) | Exec. acc. | 95% CI | N | $ / question | p50 / p95 |
|---|---|---|---|---|---|
| Baseline: one shot, full schema, no samples, no retries · Haiku | 62.7% | 58.0–67.2 | 400 | $0.00058 | 4.4 / 10.5 s |
| Full schema + sample values + self-correction · Haiku | 73.2% | 68.8–77.5 | 400 | $0.00065 | 4.5 / 11.8 s |
| Agent, production pipeline · Haiku | 71.8% | 67.2–76.2 | 400 | $0.00068 | 4.4 / 14.8 s |
| Agent, schema linking forced · Haiku | 66.2% | 61.8–70.8 | 400 | $0.00079 | 4.7 / 14.3 s |
| Agent, shipped default · Sonnet | 87.0% | 80.0–93.0 | 100 | $0.0063 | 2.8 / 8.1 s |
Paired differences on the same questions, with bootstrap 95% intervals:
- Agent vs baseline: +9.0 pp (+4.0 to +14.0), N = 400.
- Schema linking forced vs agent: −5.5 pp (−10.0 to −1.0), N = 400.
- Sonnet vs Haiku, both running the agent: +18.0 pp, 87% vs 69% (+8 to +28), N = 100.
Execution accuracy: the result rows must match the gold query's rows (BIRD set comparison, floats rounded to 6 dp). Claude Haiku 5.5 and Sonnet 5.5, effort medium, BIRD hints given to every configuration, response cache off. Dataset: BIRD mini-dev (birdsql/bird_mini_dev), CC BY-SA 4.0. Each configuration was run once; two functionally identical Haiku pipelines differed by 1.5 pp on test, so gaps under about 5 pp are noise. Total API spend for the whole project, including development and all eval runs: $2.53 of a $5.00 cap enforced in code.
Schema linking, only when it pays
Big warehouses don't fit in a prompt, so the agent has a schema linker: a lexical match on table and column names, plus a value index that finds the table holding “Marvel Comics”, plus foreign-key bridge tables. On the dev split, table recall went from 76% to 82% with the value index, then to 94% with top-6 retrieval and stemming.
Then the test split said no. Forcing the linker on BIRD's small schemas (3–13 tables) cost 5.5 points to save 12% of prompt tokens. When it missed a needed table (43 of 400 questions), accuracy on those fell to 12%, against 74% when it didn't, and execution errors rose from 2 to 33.
So the linker runs only when the schema card is over 3,000 tokens, and the agent widens to the full schema on a missing-table error, an empty result or a clarification request. BIRD's schemas fit the budget and are sent whole.
Choosing the model by measurement
All iteration ran on Claude Haiku 5.5, on the dev split, under a $5 budget. Medium effort beat low in all three dev pairs (+4, +7, +7 pp) for about 20% more output tokens.
Then Sonnet 5.5 ran the same agent on 100 of the test questions. It scored 87% against 69%, and it was also faster (p50 2.8 s vs 4.4 s), because it wrote 390 output tokens per question against Haiku's 1,192. At $0.0063 per question, about $6 per 1,000 questions, Sonnet became the default. Haiku stays available as a budget mode.
Limits, stated plainly
- It is not always right, and it can't tell when it's wrong. 87% (Sonnet, N = 100) and 71.8% (Haiku, N = 400) on BIRD's hard questions. It shows SQL, rows and assumptions so a person can check: an analyst's draft, not an oracle. Most remaining errors run cleanly and return a plausible but different result, which is a question of interpretation, not syntax.
- The Sonnet number has a wide interval (80–93%) because it rests on 100 questions.
- The linker is lexical. It misses synonyms (“revenue” →
Invoice.Total) and would need a better retriever for warehouses with hundreds of tables. - Safety covers writes, escapes, catalog reads and runaway queries. Row- and column-level permissions belong in the database role; heavy concurrent load is not covered.
- Single-turn questions plus one clarification step; no follow-up memory. SQLite and Postgres only. English only.
- The benchmark runs on SQLite. The Postgres path is covered by integration tests and the demo, not by a benchmark run.
Quality
- 162 tests: 135 offline unit and integration tests with a faked model and per-test SQLite fixtures, plus 27 Postgres integration tests against the real compose image.
- GitHub Actions CI on Python 3.12 and 3.13 with ruff lint and format checks, and a Postgres job that builds the database image.
- Eval harness in the repo: resumable threaded runner, per-question JSONL, bootstrap intervals and paired deltas, a safety suite and a guard-fidelity check.
- Six architecture decision records, one per trade-off: read-only by construction, the sqlglot guard, linking with a budget, evaluation on BIRD, model effort and result review, SQLite and Postgres.
Stack
Fits more than one sample database
The same pattern (a model that drafts, code that checks, and a database that refuses anything but reads) fits finance and ops reporting, product analytics, CRM and ERP data, or any internal database your team asks questions of every day.