Text-to-SQL looks solved in demos and falls apart in production, always in the same four ways: the model invents column names the schema never had, it emits syntax that parses in its training dialect but not in Postgres, it writes a join that materializes 40 million rows because the planner has no index path, and — the one that makes security people nervous — it generates DELETE or unconstrained full-table scans. A prompt engineer’s answer is “give it a better system prompt.” The one that holds up at 10k queries a day is to wrap the model in a loop that can observe its own mistakes and a sandbox that makes the consequences un-interesting. This is that architecture.

The loop: schema, generate, validate, execute, correct

Every query goes through five stages. First, schema injection: we introspect information_schema (not a made-up schema loader), filter to the tenant’s allowed tables, and drop any column flagged PII, plus password/token/secret-prefixed columns. The model simply never sees what it must never reference. We include Postgres enum types, check constraints, and index definitions in the schema text — a surprising fraction of correct queries depend on the model knowing status is an enum with six members, or that a UNIQUE index makes a point lookup fast.

Second, generation: one model call, armed with the sanitized schema and a few few-shot examples per query class. Third, deterministic validation — this is the stage that separates a real system from a toy:

1. Parse with sqlglot; reject any AST containing DELETE, UPDATE, INSERT, CREATE, DROP, or CTE/FROM recursion beyond depth 3.
2. Rewrite to enforce LIMIT (we cap at 1000 rows and insert it if absent).
3. EXPLAIN the query and reject plans whose estimated cost is absurd for the table sizes we know.

Fourth, execution on a read-only replica with a hard statement_timeout and a row budget. Fifth, correction: database errors — syntax, missing column, ambiguous join — are normalized and fed back to the generator as the previous attempt’s context, and the loop repeats with a hard cap.

The execution wrapper is where the sandbox lives:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import psycopg

class QuerySandbox:
    def __init__(self, conninfo, max_rows=1000, timeout_ms=5000):
        self.conn = psycopg.connect(conninfo, autocommit=True)
        self.max_rows = max_rows
        self.timeout_ms = timeout_ms

    def run(self, sql):
        with self.conn.transaction():
            self.conn.execute("SET LOCAL statement_timeout = %s", (self.timeout_ms,))
            cur = self.conn.execute(sql)
            if cur.description is None:
                return None, "only SELECT statements are allowed"
            cols = [d.name for d in cur.description]
            rows = cur.fetchmany(self.max_rows + 1)
            truncated = len(rows) > self.max_rows
            return cols, {"rows": rows[:self.max_rows], "truncated": truncated}

Notes on the sharp edges: SET LOCAL scopes the timeout to the transaction, so the connection is reusable and never left poisoned; a bare SELECT on a read-only replica can still burn I/O and lock rows via FOR UPDATE, which is why the timeout and the row budget both exist. And statement_timeout is measured in client round-trips, not wall clock — set it a little generous, because network jitter and a big NOT IN can trip it and send the loop into a retry death spiral.

What the correction loop buys you

On our internal warehouse benchmark (400 queries across six tenant schemas, cold prompts), pass@1 was 61%. With one correction round-trip it reaches 74%, and with the full loop (cap of 3 attempts) it hits 79% — the marginal gains after attempt two are near zero, and the cost grows linearly. That asymmetry is why the cap is non-negotiable: a query that fails three times is almost always a schema-hallucination problem that retrying will not fix. The dominant failure mode left is the model referencing a column we pruned from the schema for PII — the safety filter and the model disagree, and the loop correctly decides the safe answer is “I can’t reference that.”

The feedback channel matters as much as the cap. The error string the DB returns is noisy (“column r2.sales_amt does not exist”) and easy for a model to overfit. We normalize it into three fields — error_type (syntax / missing_column / ambiguity / timeout), the offending identifier, and available similar columns via pg_catalog fuzzy match — and hand those to the generator. We measured a ~6% absolute accuracy lift from feeding structured error data over raw tracebacks; the model can act on “did you mean sales_amount?” in a way it can’t act on a stack trace.

Production lessons

• Never run this on a writable database. Replica, read-only role, statement_timeout, row cap, connection pooling with a dedicated pool that can’t leak into app traffic. The sandbox failing open (no timeout, no LIMIT) is the incident you don’t want to explain.
• The schema sent to the model is a security boundary, not a convenience. PII and credential columns get filtered before generation. If a query can’t be answered without a pruned column, fail gracefully with a reason — do not start granting the model more schema on demand.
• Log every (prompt, query, error, correction) triple. It is your evaluation set, your regression harness, and your prompt-engineering feedback loop, all in one. We replay the last 30 days through each prompt template change before deploying it.
• Measure attempts-per-success, not just accuracy. If it takes 2.5 LLM calls to produce one correct query, your cost and latency are dominated by the correction loop, and improving the prompt (or the schema rendering) is cheaper than adding model capacity.

Text-to-SQL in production is a control problem more than a generation problem. The model proposes, the sandbox disposes, and the loop converges — as long as you’ve decided ahead of time how many wrong guesses you’re willing to pay for.