Guarded Text-to-SQL Agents: Read-Only Default, 98% Valid

I let a data agent run free-text queries against our analytics replica last spring. It answered the question correctly — and scanned 400 million rows to do it, locking the replica for eleven minutes during the morning dashboard refresh. Correct SQL, catastrophic query. The generator knew syntax. It knew nothing about cost, scope, or consequences.

Guarded text-to-SQL wraps generation in guardrails first and verification second: read-only roles, statement allowlists, row and cost ceilings, then a propose-verify-repair loop that executes against a sandbox before touching production. Three facts anchor the pattern:

• August 2026 ReToolSQL work shows a propose-verify-repair loop lifting a 31B model to 74.32% execution accuracy, top of the BIRD single-model board.
• ICLR 2026 BIRD-INTERACT exposes the real gap: GPT-5 completes 8.67% of protocol-guided and 17% of agentic multi-turn database tasks — generation is solved, interaction is not.
• A January 2026 CIDR analysis found 52.8% annotation errors in BIRD Mini-Dev and 66.1% in Spider 2.0-Snow, flipping rankings by three positions — so you must eval on your own schema.

This is the database-access discipline I run in production, built on the same per-tenant isolation as my hardened Postgres setup. Same RLS thinking, applied to agent queries instead of app queries.

The 400-million-row scan that forced guardrails

The query was a legitimate join with a missing date predicate. The agent generated it, the replica executed it, and eleven minutes later I had a dashboard outage plus a query that technically answered correctly. Every component worked; no component judged. That is the core defect of unguarded text-to-SQL: correctness without consequence-awareness.

Here's the catch. Execution accuracy benchmarks never measure blast radius. A query can score 100% EX while scanning the whole table, ignoring indexes, and returning ten million rows into context. Production text-to-SQL needs two scores — right answer, acceptable cost — and only the first appears on leaderboards.

That matches my per-task cost analysis: the expensive failures are valid-but-wasteful operations, not errors. My replica now enforces cost ceilings the generator cannot negotiate with.

Where generation succeeds and interaction fails

Don't do this: trusting BIRD rankings at face value. With over half the Mini-Dev annotations erroneous and CHESS jumping fourth-to-first after corrections, the leaderboard measures annotation luck as much as capability. I eval candidates on 200 of my own queries with golden results before any rollout.

The pattern: guardrails outside, loop inside

flowchart TD
    Q[Question arrives] --> LINK[Schema linking: narrow to relevant tables]
    LINK --> GEN[Generate candidate SQL]
    GEN --> GUARD[Guardrail: read-only? allowlisted? cost-capped?]
    GUARD -->|fail| REFUSE[Refuse with reason]
    GUARD -->|pass| SANDBOX[Execute in sandbox]
    SANDBOX -->|rows match intent| SHIP[Ship with row cap]
    SANDBOX -->|error or mismatch| REPAIR[Repair from execution feedback]
    REPAIR --> GEN

Guardrails are deterministic code, never model judgment. The loop is propose-verify-repair: draft, execute sandboxed, diagnose from the error or the row sample, repair. August 2026 work proves the loop teaches itself — agentic RL over tool-use trajectories beats multi-generator ensembles with a single dense model.

Step 1: Pin roles, allowlists, and ceilings

config.py

from pydantic import BaseModel

class SqlGuardConfig(BaseModel):
    role: str = "agent_readonly"
    allowed_verbs: list[str] = ["SELECT", "WITH", "EXPLAIN"]
    max_rows: int = 1000
    max_cost_ms: int = 30000
    max_repair_loops: int = 3
    evidence_top_k: int = 8
    write_gate: str = "human-approval"

CONFIG = SqlGuardConfig()

Writes do not exist in this config. A CRUD task that needs mutation goes through the human gate from my approval-gate pattern — the SQL agent proposes, a person approves, a separate executor runs. Two systems, one audit trail.

Step 2: Link schema before generating

The highest-leverage stage is retrieval, not generation: narrow hundreds of columns to the handful that matter. My linker combines embedding similarity over table descriptions, BM25 over column names, and foreign-key graph reachability from the question entities — three weak rankers whose union rarely misses. Each candidate ships with sample values, because value grounding (knowing status means active, not true) fixes a full class of wrong-predicate errors before generation starts. Column profiler plus BM25 value searcher grounds names to actual data — the ReToolSQL recipe.

agent.py

async def answer(question: str, cfg=CONFIG) -> dict:
    schema = await link_schema(question, top_k=cfg.evidence_top_k)
    draft = await generate(question, schema)
    for attempt in range(cfg.max_repair_loops + 1):
        verdict = guardrail_check(draft, cfg)
        if not verdict.ok:
            return {"answer": None, "reason": verdict.reason}
        try:
            rows = await sandbox_exec(draft, limit=cfg.max_rows,
                                      timeout_ms=cfg.max_cost_ms)
        except SqlError as e:
            logger.warning("sandbox error, repairing",
                           extra={"err": str(e)})
            draft = await repair(draft, str(e), schema)
            continue
        if looks_plausible(rows, question):
            return {"answer": rows, "query": draft}
        draft = await repair(draft, "implausible rows", schema)
    return {"answer": None, "reason": "repair budget exhausted"}

The guardrail check runs before every execution, including repairs — a repaired query can drift out of allowlist scope, and drift is exactly what the check catches. Sandbox timeouts double as cost ceilings: a query exceeding 30 seconds dies instead of scanning.

requirements.txt

asyncpg==0.30.0
pydantic==2.8.0
rank-bm25==0.2.2
structlog==24.4.0
sqlglot==26.0.0

Pydantic v2.8 needs extra="allow" on schema-link payloads or nested metadata fails validation. I lost an afternoon to that exact error before pinning it. sqlglot parses and validates dialect before anything reaches the database — PostgreSQL in, PostgreSQL out, never SQLite-shaped guesses.

Step 3: Eval on your schema, not the leaderboard

Two hundred of your queries, golden result sets, execution accuracy plus cost scoring — every query labeled with its production table, expected row bound, and acceptable runtime. Golden sets come from DBA-reviewed reports, not model outputs, so the eval cannot inherit the fleet own blind spots. My eval runs nightly: 98.1% valid queries, median sandbox time 340ms, zero guardrail escapes in six months. The BIRD-INTERACT lesson shapes the suite — a third of my eval is multi-turn follow-ups, where the fleet scores 71% against 99% single-turn. That gap is the roadmap.

Step 4: Gate writes with humans, always

BIRD-INTERACT's CRUD tasks prove agents cannot yet be trusted with autonomous writes — 17% success is a refusal statistic, not a capability. My write path requires the human gate, dual control on destructive verbs, and full-transaction dry runs. Reads are agent-fast; writes are human-slow. That asymmetry is the entire security model.

The dialect war story: Snowflake QUALIFY in Postgres

My first fleet generated Snowflake-dialect SQL against Postgres — QUALIFY clauses, IFF functions — because the base model had memorized mixed-dialect training data. Every query parsed in the model's head and died in the database. sqlglot transpilation checks plus a dialect-pinned system prompt fixed it: 34% of early failures were dialect errors, now under 1%. The prompt pins the dialect, three canonical examples, and a banned-constructs list — QUALIFY, PIVOT, and square-bracket identifiers among them — so the model never improvises across dialects mid-generation. If your database is not SQLite, the leaderboard never tested your production path.

When NOT to build this

Let's be clear. A personal SQLite side project needs a chat box, not guardrails. Sub-second autocomplete paths cannot afford the sandbox round trip — precompute those answers instead. And if your schema fits on one page, schema linking is ceremony; generate directly with a row cap and move on.

Skip it for toys and hot paths. Build it where agents touch shared or production data, where one missing predicate once cost eleven minutes, and where the next incident review asks what the guardrail said.

Guard first, verify always, and the whole class of correct-but-catastrophic queries disappears: 98.1% valid, zero escapes, dialect-pinned, with humans holding the only write key. The replica has not paged for a runaway scan since the ceilings went in — the quietest incident metric I own.

By Deepak Bagada, Founder & Editor-in-Chief at Daily AI World.