Case study 15 · LLM agent · text-to-SQL

Ask in English,check the SQL

A text-to-SQL agent for the retail database from my retail analytics project. Business questions go in; an answer, a chart, the table and the SQL behind it come out. It only helps if it can be trusted, so it is built around two independent locks and measured against known answers.

In progressLangGraph · Claude / Gemini · sqlglot · DuckDB · Streamlit
GitHub ↗

DataUCI Online Retail II, about 1 million cleaned order lines, Dec 2009 to Dec 2011, in GBP. The agent sees 13 tables; scratch and raw tables are removed.

13Tables the agent may read
2Independent locks on the database
30Eval questions with gold SQL
60Tests, no API key needed
Data flow · how a question moves

A question to a checked answer

Each dot is a question. Queries the guard rejects drop out and go back to the model with the reason, up to two retries; the rest run in a locked, read-only database. Click a stage to read what happens there.

01 · The loop

Errors become feedback

If the guard or the database rejects a query, the error goes back to the model, which gets up to two more tries. API failures stop at once rather than retrying, so a broken key never loops.

Schema notesThe model is told an order is a distinct invoice, guest checkouts have no customer id, December 2011 is a partial month and percentages run from 0 to 100.

02 · Two locks

Either one alone blocks every write

The guard gives the model useful feedback. The connection is the real security boundary, and it holds even if the parser is fooled. In the tests, each lock on its own blocks every write and every file read.

G1One SELECTExactly one statement, and it must be a SELECT.
G213 tablesOnly the allowed tables in main, or CTEs built on them.
G3No filesRejects read_csv and functions that touch files, settings or the environment.
D1Read-onlyread_only=True, enable_external_access=false, then lock_configuration.
D2Limits10-second timer and a 1,000-row cap.

Why bothA parser can be wrong about an edge case. A read-only connection with external access locked off can't be talked out of it.

03 · Evaluation

Measured, not demoed

30 business questions, each with a hand-written gold query. The score is execution accuracy: the agent's rows must match the gold rows, with numbers within 0.5%, text ignoring case and dates by day. Extra columns, other names and other column orders are allowed.

StatusThe eval set and scorer are built and tested. Accuracy numbers aren't in the repo yet, so none are shown here.

04 · How to use it

For the long tail, not the dashboard

R1Long tailHeadline KPIs already live in summary tables. The agent's value is the one-off question that would wait in a queue.
R2Show SQLEvery answer shows its query and rationale.
R3Grow evalsEvery answer a user flags as wrong becomes a test case with a gold query.
R4Keep locksNever trade the read-only connection for convenience.
PythonLangGraphClaude APIGeminisqlglotDuckDBStreamlitpytest

Built onThe cleaning and modelling SQL from the retail analytics case study, so the agent answers over tables already checked by hand.