Text-to-SQL in Production: Letting Non-Technical People Ask the Database Questions Safely

Text-to-SQL is a party trick until a natural-language question drops a table or hands one customer another customer's revenue. The interesting engineering isn't teaching a model to write SELECT; it's making sure the worst sentence a tired ops manager can type still can't hurt you.

The demo everyone loves and the incident nobody wants

Every text-to-SQL demo lands the same way. Someone types "which regions grew fastest last quarter" into a box, a chart appears in two seconds, and the room decides analysts are optional. I have built this demo. It is genuinely good. Lumen, one of my own tools, does exactly this in plain English and people lean forward when they see it.

Then you ship it, and the questions stop being clean. A customer success lead types "show me everyone who hasn't logged in and delete the stale ones," half as a question and half as a wish. A model tuned to be helpful is very willing to interpret that literally. Or someone on a shared analytics account at a multi-tenant SaaS asks "top 50 accounts by spend" and the model, with no notion of who is asking, cheerfully returns fifty accounts that belong to four different companies. Nobody typed anything malicious. The system just did what the words said.

The gap between the demo and production is not model quality. It is that the demo has no adversary and production has thousands of them, most of them well-meaning. Natural language to SQL is easy to make impressive and hard to make safe, and the safety is the entire product.

The threat model: destructive queries, data exfiltration, cost bombs

Three failure modes matter, and they are different problems with different fixes. Conflating them is how vendors end up with one flimsy regex pretending to cover all three.

The first is destruction: DELETE, UPDATE, DROP, TRUNCATE, or an ALTER that quietly changes a column type. Rare from users, but catastrophic once, and the LLM will happily generate it if the connection allows it.

The second is exfiltration: a syntactically perfect read that returns rows the asker was never entitled to see. Another tenant's data, salaries, a table of password reset tokens. This is the dangerous one because nothing looks wrong. The query runs, results appear, and the leak is invisible until someone screenshots it.

The third is the cost bomb: a five-word question that becomes a cross join over three fact tables and scans a billion rows. No data lost, no rules broken, just a warehouse bill that spikes and a dashboard that times out for everyone else. On usage-billed engines a careless SELECT * across a wide events table can cost real money per run, and a self-service tool invites people to run it hundreds of times a day.

Notice that only one of these is about writing bad SQL. The other two are about writing perfectly good SQL that should never have been allowed. That framing decides the architecture.

Read-only by construction: database roles over prompt-based pleading

The most common mistake I see is defending against destruction in the prompt. "You are a helpful analyst. Only generate SELECT statements. Never modify data." This is not security. It is a polite request to a system whose entire job is to be talked into things, and prompt injection through the data itself (a customer name literally set to a SQL fragment) walks straight past it.

The destruction problem is solved one layer down, in the database, before the model exists. The application connects through a role that is physically incapable of writing:

CREATE ROLE analytics_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO analytics_ro;
GRANT USAGE ON SCHEMA public TO analytics_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_ro;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA public FROM analytics_ro;
ALTER ROLE analytics_ro SET default_transaction_read_only = on;
ALTER ROLE analytics_ro SET statement_timeout = '8s';

Now "delete the stale accounts" produces either nothing or a permission error, no matter how the model was manipulated. The statement_timeout caps the cost bomb at the engine level, so even a runaway join dies after eight seconds instead of billing you for a full scan. Point the read role at a replica, not the primary, and heavy analytical questions never touch the database your customers are transacting against.

Every safety rule you can move out of the prompt and into a database grant is a rule the model can no longer be argued out of.

This is the load-bearing principle. The prompt is where you ask for good behavior. The role is where you make bad behavior impossible. Only the second one holds under an adversary.

Schema-aware prompting so the model asks about columns that exist

Read-only stops disasters; it does nothing for correctness. A model that invents a signup_date column when the table actually has created_at generates SQL that errors, or worse, silently joins on the wrong key and returns a confident wrong number.

The fix is to stop guessing at the schema and hand it over. Before generating anything, I inject a compact description of the real tables in scope: table names, column names and types, primary and foreign keys, and one line of human context per column that isn't self-explanatory (status: one of open, pending, resolved, closed). For a wide schema you don't dump all 300 tables; you retrieve the handful relevant to the question first, the same pattern that powers a RAG assistant like Verba, then prompt against only those.

Two details earn their keep. Include enumerated values for low-cardinality columns, because "closed tickets" is useless if the model doesn't know the column stores resolved. And include the actual join keys, because the single most common wrong-but-runs answer comes from a plausible-looking join on a column that isn't the real relationship. Schema-aware prompting is the difference between a model that asks about columns that exist and one that hallucinates a database it wishes you had.

Validating and sandboxing the generated query before it runs

Even read-only and schema-aware, the generated query gets one more gate before it touches data. I parse it into an abstract syntax tree rather than grepping the string, because string matching on SQL is a losing game against comments and casing. From the parsed tree I enforce a short allow-list: exactly one statement, it must be a SELECT, no data-definition or data-modification nodes, no calls to functions that read the filesystem or open network connections, and no reference to tables outside the set I explicitly exposed.

Then I wrap the query rather than trusting it to be polite. A mandatory LIMIT is injected if the model didn't add one, so "show me all events" returns 1,000 rows and a note, not ten million. The whole thing runs inside a read-only transaction with its own timeout, belt and suspenders over the role setting. Anything the parser can't cleanly understand is rejected outright. A query I can't prove is safe is not a query I run; it's a clarifying question waiting to happen.

Showing the SQL: turning a black box into something a manager trusts

Here is the design decision that changes adoption more than any model upgrade: always show the SQL, and show the row count and the assumptions in plain language above it. "Counting resolved tickets where closed_at is in the last 7 days, grouped by assignee. 1,240 rows."

Two things happen. Technical users skim the SQL, catch a wrong join in three seconds, and correct it, so they become QA instead of skeptics. And non-technical users get a sentence they can forward to someone who can read SQL. The output stops being an oracle you either believe or don't and becomes a claim with its work shown. Plain-English analytics that hides its reasoning asks for blind trust; plain-English analytics that shows the query earns real trust. I would rather a manager caught a mistake in my generated SQL than trusted a number they couldn't check, and every user who has ever been burned by a confidently wrong dashboard feels the same.

Handling ambiguity: when to ask a clarifying question instead of guessing

"How are we doing this month" is not a query. It is a conversation. The failure mode of every mediocre text-to-SQL tool is that it never says "I'm not sure"; it picks an interpretation, returns a number, and lets you discover later that "this month" meant calendar month when you meant a trailing 30 days.

A production system needs a calibrated flinch. When a question maps cleanly to the schema, answer it. When a key term is undefined (which date, which of two amount columns, does "active" mean logged-in or subscribed), ask one sharp clarifying question before generating anything. The skill is in the threshold, and the honest answer is that you tune it against real logs, not in a vacuum. Ask too often and the tool feels like a slow intern; never ask and it feels like a confident liar. The correct posture is the one a good analyst has: happy to answer, unwilling to guess about money.

Results: who uses it now and what they stopped asking analysts for

The honest scorecard is not "we replaced the analytics team." It is narrower and more durable. The recurring, shaped questions that used to arrive as Slack messages to a data analyst now get self-served: how many onboarding calls happened last week, which plan tier is churning, what's the open-ticket backlog by team. On the teams running this pattern, that class of ad-hoc request dropped sharply, and the analysts got their afternoons back for the modeling work that actually needs judgment.

What did not change: the genuinely hard questions, the ones requiring a new metric definition or a caveat about messy data, still go to a human, and should. A useful boundary emerged on its own. Text-to-SQL absorbs the questions where the SQL is routine and the risk of a wrong answer is low, and it should decline everything else loudly rather than fake competence.

The demo is the easy 20% and it is what gets people to sign. The read-only role, the AST allow-list, the row scoping, the shown query, the calibrated clarifying question: that unglamorous 80% is the entire difference between a tool people trust with the real database and a liability you quietly turn off after the first incident. Generating SQL was never the hard part. Constraining it is the product.