Text-to-SQL Agents in Production: Schema Grounding, Read-Only Guardrails, and Execution-Based Evals

"Ask your data a question in plain English" is one of the most requested AI features in any company. The demo takes an afternoon: paste the schema into a prompt, ask for SQL, run it. Production takes much longer, because the failure modes are not obvious. The query runs, returns rows, and the rows are wrong. Or the query is correct but scans a 2-billion-row table and takes the warehouse down. Or a cleverly worded question gets the model to emit a DELETE.

This post covers the three things that separate a demo from a system you can ship: schema grounding, layered safety, and execution-based evaluation.

The problem: SQL that runs is not SQL that is right

A text-to-SQL system can fail in four distinct ways, and each needs a different fix.

Failure	Example	Fix
Wrong schema reference	Hallucinated column `customer_region`	Schema grounding, validation before execution
Wrong semantics	"Revenue" computed from `gross_amount` instead of `net_amount`	Business-definition layer, few-shot examples
Unsafe query	`DROP`, unbounded scan, cross-tenant read	Layered guardrails
Silent wrong answer	Join fan-out doubles the totals	Execution-based evals, result sanity checks

Most teams only fix the first row and wonder why users stop trusting the tool.

Step 1: Ground the schema, don't dump it

Pasting the full DDL for 400 tables into the prompt wastes context and hurts accuracy. The model picks plausible-looking but wrong tables. Instead, retrieve a relevant slice of the schema per question.

A workable approach:

Store a short description for every table and column (human-written, or LLM-drafted then reviewed).
Embed those descriptions.
At query time, retrieve the top-k tables, then add tables reachable by foreign keys so joins are possible.
Include sample values for low-cardinality columns (status enums, country codes). This alone fixes many WHERE status = 'Active' vs 'active' bugs.

from dataclasses import dataclass

@dataclass
class TableDoc:
    name: str
    description: str
    columns: list[tuple[str, str, str]]  # (name, type, description)
    fk_targets: list[str]

def build_schema_context(question: str, retriever, catalog: dict[str, TableDoc], k: int = 5) -> str:
    hits = retriever.search(question, k=k)            # returns table names
    tables = set(hits)
    for t in list(tables):                            # pull in join neighbours
        tables.update(catalog[t].fk_targets)
    lines = []
    for name in sorted(tables):
        doc = catalog[name]
        lines.append(f"TABLE {name}: {doc.description}")
        for col, typ, desc in doc.columns:
            lines.append(f"  - {col} ({typ}): {desc}")
    return "\n".join(lines)

Also add a small metrics glossary ("revenue = SUM(net_amount) where status = 'paid'"). Ambiguous business terms are the single biggest source of silently wrong answers, and no amount of schema retrieval resolves them.

Step 2: Generate, then validate before executing

Never send model output straight to the database. Parse it first. Using a SQL parser such as sqlglot lets you enforce rules structurally instead of with regexes (which are trivially bypassed with comments or casing).

import sqlglot
from sqlglot import exp

ALLOWED_TABLES = {"orders", "customers", "products"}
MAX_LIMIT = 1000

class UnsafeSQL(Exception):
    pass

def validate(sql: str, dialect: str = "postgres") -> str:
    statements = sqlglot.parse(sql, read=dialect)
    if len(statements) != 1:
        raise UnsafeSQL("exactly one statement allowed")
    tree = statements[0]

    if not isinstance(tree, exp.Select):
        raise UnsafeSQL("only SELECT is allowed")

    forbidden = (exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create, exp.Command)
    if any(tree.find(node) for node in forbidden):
        raise UnsafeSQL("write or DDL node found")

    tables = {t.name for t in tree.find_all(exp.Table)}
    if not tables <= ALLOWED_TABLES:
        raise UnsafeSQL(f"tables not allowed: {tables - ALLOWED_TABLES}")

    if not tree.args.get("limit"):
        tree = tree.limit(MAX_LIMIT)

    return tree.sql(dialect=dialect)

When validation fails, feed the error back to the model and let it retry, with a cap of two or three attempts. The error message is the best prompt you have: "column customer_region does not exist; available columns on customers: id, name, region".

Step 3: Enforce safety in layers

The validator above is one layer. It must not be the only one, because parsers have gaps and dialects have quirks. Assume any single control will eventually fail.

Layer	Control	Stops
1. Prompt	"Only write SELECT"	Honest mistakes (not attacks)
2. Parser	AST validation, table allowlist, forced LIMIT	Writes, off-limits tables
3. Database role	Dedicated read-only user, grants on allowed views only	Anything the parser missed
4. Row-level security	Tenant filter enforced by the database, not the query	Cross-tenant reads
5. Resource limits	`statement_timeout`, read replica, warehouse size cap	Runaway scans
6. Audit log	Store question, SQL, user, row count	Forensics, eval data

Layers 3 to 5 are the ones teams skip, and they are the ones that matter. In Postgres, for example:

CREATE ROLE sql_agent LOGIN PASSWORD '...' NOSUPERUSER;
GRANT USAGE ON SCHEMA analytics TO sql_agent;
GRANT SELECT ON analytics.orders_v, analytics.customers_v TO sql_agent;
ALTER ROLE sql_agent SET statement_timeout = '15s';
ALTER ROLE sql_agent SET default_transaction_read_only = on;

Point the agent at views, not base tables. Views let you drop PII columns, rename cryptic fields, and bake in tenant filters. Check your own database's documentation for the equivalent controls, since syntax and capabilities differ by engine.

Multi-tenant products need special care: never rely on the model to add WHERE tenant_id = .... Use row-level security or per-tenant views so the database enforces it regardless of what SQL arrives.

Step 4: Evaluate by execution, not by string match

Two different SQL strings can return identical results, and one SQL string can look right while returning wrong rows. Comparing generated SQL to a reference with string similarity is a poor metric. Use execution accuracy: run both queries on a fixed snapshot and compare result sets.

import pandas as pd

def results_match(gold_sql: str, pred_sql: str, conn, order_matters: bool = False) -> bool:
    gold = pd.read_sql(gold_sql, conn)
    try:
        pred = pd.read_sql(pred_sql, conn)
    except Exception:
        return False
    if gold.shape != pred.shape:
        return False
    # compare values, ignoring column names the model may have aliased differently
    g = gold.copy(); p = pred.copy()
    g.columns = p.columns = range(g.shape[1])
    if not order_matters:
        g = g.sort_values(list(g.columns)).reset_index(drop=True)
        p = p.sort_values(list(p.columns)).reset_index(drop=True)
    return g.equals(p)

def run_eval(cases, generate_sql, conn):
    passed = 0
    for case in cases:
        pred_sql = generate_sql(case["question"])
        ok = results_match(case["gold_sql"], pred_sql, conn, case.get("order_matters", False))
        passed += ok
    return passed / len(cases)

Building the dataset is the real work. Start with 50 to 100 real questions pulled from users, tagged by difficulty (single table, join, aggregation, window function, ambiguous term). Track accuracy per tag, because an overall 85% can hide 40% on multi-join questions. Your audit log from layer 6 is the best source of new cases: every thumbs-down and every retry becomes a candidate. For the broader method, see our posts on mining production traces into eval datasets and calibrating LLM-as-judge.

Use the judge only for the leftovers: questions where the result is subjective ("show me our top customers").

Step 5: Sanity-check results before showing them

Even a passing query can mislead. Add cheap post-execution checks:

Row count: zero rows often means a wrong filter value, so retry with sample values shown. A result exactly equal to your LIMIT means truncation, so tell the user.
Fan-out detection: if the question asks for a total and the query joins a one-to-many table before aggregating, flag it.
Show the SQL: always display the generated query and the tables used. Analysts will verify it, and non-technical users at least know what was assumed.
Confidence by agreement: generate two candidates at different temperatures, execute both, and flag disagreement.

Common pitfalls

Regex-based safety. Blocking the string DROP misses dRoP, comments, and dialect-specific commands. Parse, then restrict at the database role as well.
Full schema in the prompt. More tables means more wrong-table errors. Retrieve a slice.
No business glossary. "Active user", "revenue", and "churn" mean something specific at your company. Write them down.
Testing on a tiny dev database. Queries that are instant on 10k rows time out on production volume. Evaluate on realistic data size, and enforce timeouts.
Evaluating with string match. It both penalises correct alternate SQL and rewards near-misses.
Letting the agent loop unbounded. Cap retries and total tokens per question. See our post on agent loop control for stop conditions.
Ignoring PII. The model will happily SELECT email if the column is visible. Expose masked views only.

Checklist before you ship

Dedicated read-only database role on a replica
Views with PII removed, tenant isolation enforced by the database
statement_timeout and a forced LIMIT
AST validation with table allowlist, and retry-on-error with a cap
Schema retrieval with column descriptions and sample values
Metrics glossary injected into the prompt
50+ execution-based eval cases, tracked by difficulty
Audit log of every question, SQL, and result size
Generated SQL shown to the user

Next steps

Start with the safety layers, since they cost the least and protect you from the worst outcomes. Then build the eval set from real questions, and only then spend time on prompt tuning. When accuracy plateaus, the fix is almost always better schema documentation and a business glossary rather than a bigger model. Verify dialect-specific syntax and library APIs (sqlglot, your database's role and row-level security features) against their official docs before relying on the snippets here.

If you want to build and evaluate agents like this with feedback from practitioners, take a look at the Joinloop engineering cohort. Product managers scoping data-assistant features may prefer the PM cohort.