How I let an LLM write SQL against my database — safely

I recently rebuilt my manufacturing copilot, ShopFloor, around an idea that sounds reckless: let the LLM write its own SQL.

The first version had no LLM in the answer path at all. A regex-based intent parser routed questions to four hand-coded "specialist" agents, each with its own fixed queries. It was fast and deterministic — and rigid. Ask it "why did Line 3's yield drop last week?" and it shone. Ask it "what's the yield difference between Line 3 and Line 5?" and it collapsed, because the parser could only ever extract one production line from a sentence. Every new question shape meant new routing code, new specialist logic, new tests. The system could only answer questions I had anticipated.

The fix was to invert the architecture: instead of me writing every query in advance, a planner model reads the schema and the question, then a tool-using loop writes whatever SQL the question actually needs. Comparisons, rankings, cross-domain joins — shapes I never coded for — just work.

The interesting part is what makes this safe.

Layer 1: the parser is the gatekeeper, not the prompt

Prompt instructions like "only write SELECT statements" are a courtesy, not a control. The real gate is an AST check: every statement the model produces is parsed with sqlglot before execution, and rejected unless it is a single pure SELECT over an explicit whitelist of the sixteen domain tables.

CTEs are resolved so a WITH sneaky AS (SELECT * FROM secrets) can't smuggle a table in through an alias. INFORMATION_SCHEMA, pg_catalog, and sqlite_master are named-and-banned so the model can't map the database beyond what I hand it. DML of any kind — even wrapped three subqueries deep — kills the statement before it touches a connection.

The SQL that reaches the executor is intentionally ordinary and inspectable:

SELECT
  pl.name AS production_line,
  ROUND(100.0 * SUM(qi.pass_qty) / NULLIF(SUM(qi.inspected_qty), 0), 2) AS yield_pct
FROM production_runs AS pr
JOIN production_lines AS pl ON pl.line_id = pr.line_id
JOIN quality_inspections AS qi ON qi.run_id = pr.run_id
WHERE pl.name IN ('Line 3', 'Line 5')
GROUP BY pl.name
LIMIT 200;

Layer 2: the connection can't do harm even if layer 1 fails

Defense in depth means assuming your own parser has a bug. The execution path opens a read-only connection (PRAGMA query_only on SQLite, default_transaction_read_only on Postgres), applies a 2-second statement timeout via a watchdog, and injects a LIMIT if the outer query lacks one. A malicious statement that somehow survived the AST check would still land on a connection that is physically unable to write, and a pathological join dies at the two-second mark instead of eating the instance.

Layer 3: the model must look before it queries

Hallucinated column names were my most common failure in early testing. The fix is a tool-contract rule enforced at runtime, not in the prompt: the loop's query_domain tool refuses to run until describe_schema has been called in the same turn. The model literally cannot query a table whose real columns it hasn't fetched. When a query still fails — a typo, a wrong join — the error message goes back into the loop and the model gets exactly one retry.

Layer 4: everything is written down

Every statement — executed or rejected — lands in an append-only audit table with the turn ID, the tables touched, row counts, timing, and the rejection reason. The UI surfaces this as a "reasoning" panel under every answer: the plan, each tool call, the exact SQL. When someone asks "why should I trust this number?", the answer is a click away, not a shrug.

The human stays above the loop

Two interrupts frame every conversation turn. Before the investigation runs, the model presents a plan in plain English — "I'll compare weekly yield for Line 3 and Line 5, then check downtime" — and the user can run, edit, or cancel it. Nothing touches the database before that click. And when the copilot wants to do something (quarantine a lot, open a work order), the action is queued behind an approval gate with idempotent execution and a full audit trail. The LLM proposes; tools execute; a person approves.

Assorted lessons from the build

Gemini's structured output beats prompt discipline. Asking nicely for "JSON only" failed roughly one turn in five; Gemini would drift into prose mid-plan. Switching to response_schema (server-side constrained decoding) fixed it completely — the planner and the tool loop have returned parseable JSON every single time since.

Cancel must be a hard kill. A demo where you watch a wrong investigation grind on for thirty seconds is a failed demo. Cancellation is a flag checked before every tool dispatch and every model call, plus a statement interrupt on the database side — measured under 500 ms from click to stop.

Budgets are a feature. The whole system runs on a hobby budget: a hard monthly cost guard in the metrics layer, client-side request pacing with jittered retry for the days quota gets tight, and scale-to-zero infrastructure. Cost discipline forced better engineering — smaller prompts, cached schema blocks, cheap models for the loop and an expensive one only for hard synthesis.

The result: sixty-eight tests, guarded queries that run in about a millisecond, and a copilot that answers question shapes I never wrote code for — without ever holding write access to anything.

The build log for this project — including the eval harness that gates deploys and the Cloud Run setup it now runs on — will be the subject of the next post.