Text-to-SQL AI in Production: A 3-Layer Implementation Guide

Why Text-to-SQL AI Is Finally Worth Taking Seriously

The idea has been around for years: let a business analyst type a plain-English question — "How did Q3 revenue in the Northeast compare to the same period last year?" — and get a SQL result without writing a single query. The demand was always there. Working solutions weren't.

Early models were the obvious culprit. On queries involving multiple joins, SQL generation accuracy routinely fell below 50%. When the model got it wrong, the results ranged from silently incorrect data to a full table scan that hammered the production database. Neither outcome is acceptable in any real deployment.

The landscape has shifted. GPT-4.1 and Claude Sonnet 4.6 now exceed 87% execution accuracy on the Spider benchmark, with meaningfully better handling of multi-table joins and window functions. Equally important, the engineering patterns around LLM-powered SQL have matured — particularly the principle of keeping model capability and safety guardrails as separate, independent concerns.

This article walks through a production-ready text-to-SQL implementation built on three layers: schema injection, SQL validation, and access control.

---

Layer 1: Schema Injection — Give the Model What It Actually Needs

About 70% of SQL generation quality comes down to schema context quality. Dumping every DDL in your warehouse into the prompt is technically the simplest approach, but a mid-sized data warehouse can have hundreds of tables. Token costs explode, and irrelevant table noise actively degrades accuracy.

The practical approach is semantic retrieval + targeted injection:

1. Offline: Vectorize each table's CREATE TABLE statement, column comments, and business description. Store in pgvector or Qdrant.
2. At query time: Run the user's natural-language question through the same embedding model, retrieve the top-K most relevant tables (typically K=5–8), and inject only those schemas into the system prompt.

relevant_tables = vector_search(user_query, top_k=6)
schema_context = "\n\n".join([
    f"-- {t.name}: {t.description}\n{t.ddl}"
    for t in relevant_tables
])

system_prompt = f"""You are a SQL expert. You may only query the following tables:
{schema_context}

Rules: Output SELECT statements only. INSERT, UPDATE, DELETE, and DROP are forbidden."""

A few details that trip up most first implementations:

• Column comments matter more than column names. gmt_create tells the model nothing. -- Order creation timestamp, UTC+8 is unambiguous.
• Enumerate enum values explicitly. If a status column only contains 1, 2, or 3, document what each value means. Without that, the model will confidently generate WHERE status = 'paid' — a filter that returns zero rows.
• Make foreign keys explicit. Don't expect the model to infer that order_id in one table and id in orders are the same thing. State it in the comment.

---

Layer 2: SQL Validation — Three Gates Before Execution

The model's output goes through three checks before a single query hits the database. All three are mandatory.

Gate 1: Syntax validation

Use the database's native EXPLAIN or PARSE interface — not a regex. PostgreSQL accepts EXPLAIN (FORMAT JSON); MySQL uses EXPLAIN FORMAT=JSON. Syntax errors throw an exception; catch it, pass the error message back to the model, and retry. One retry resolves roughly 90% of syntax failures.

Gate 2: Semantic whitelist check

Parse the AST with sqlglot or sqlparse and enforce the following:

import sqlglot

def validate_sql(sql: str, allowed_tables: list[str]) -> tuple[bool, str]:
    try:
        ast = sqlglot.parse_one(sql, dialect="postgres")
    except Exception as e:
        return False, f"Parse failed: {e}"

    # Only SELECT is allowed
    if not isinstance(ast, sqlglot.exp.Select):
        return False, "Only SELECT statements are permitted"

    # Table whitelist check
    tables_in_sql = {t.name for t in ast.find_all(sqlglot.exp.Table)}
    unauthorized = tables_in_sql - set(allowed_tables)
    if unauthorized:
        return False, f"Query references unauthorized tables: {unauthorized}"

    # Recursively check subqueries for the same violations
    for subquery in ast.find_all(sqlglot.exp.Subquery):
        sub_tables = {t.name for t in subquery.find_all(sqlglot.exp.Table)}
        unauthorized_sub = sub_tables - set(allowed_tables)
        if unauthorized_sub:
            return False, f"Subquery references unauthorized tables: {unauthorized_sub}"

    return True, "ok"

Gate 3: Performance pre-check

Full table scans are the most common production incident in text-to-SQL deployments. Run EXPLAIN before execution and inspect the plan for type: ALL (MySQL) or Seq Scan on large_table (PostgreSQL). If the estimated row count exceeds a threshold — say, 10 million rows — reject the query and ask the user to narrow the time range or add a filter.

All three gates combined add roughly 50ms to the request lifecycle. Users don't notice it.

---

Layer 3: Access Control — The Layer Most Teams Skip

Access control tends to be bolted on after the fact, or omitted entirely. That's a serious mistake, because prompt injection is a real attack surface, not a theoretical one.

Database level: Create a dedicated read-only service account for your text-to-SQL pipeline: GRANT SELECT ON analytics.* TO 'textsql_ro'. Physical write prevention beats software-layer validation — even if every other check is bypassed, this account cannot modify data.

Application level: Bind each user or role to a whitelist of visible tables. During schema injection, only inject tables that user is authorized to see. If the model never receives a table's schema, it cannot generate SQL that references it.

Row level: When row-level isolation is required — for example, a regional sales rep who should only see their own territory — wrap the final SQL in a layer that appends WHERE region = 'northeast' automatically. Never rely on the model to add this filter on its own.

Here's how the four control layers stack:

Layer	Enforced at	Consequence if bypassed
Schema visibility	Prompt construction	Model doesn't know the table exists
Table whitelist	sqlglot AST check	Query is rejected
Database account	DB-level GRANT	Execution error
Row filter	SQL wrapper	Data is scoped correctly

Each layer is independent. If one is bypassed, the next one holds.

---

Putting It Together: End-to-End Request Flow

A complete request moves through these stages:

1. User submits a natural-language question (~10ms)
2. Vector retrieval pulls relevant tables; schema context is assembled (~30ms)
3. LLM generates SQL — Claude Sonnet 4.6 or GPT-4.1-mini typically responds in 1–3s for this task
4. Three-gate validation (~50ms)
5. Query executes against the database (varies by complexity)
6. Optional: LLM interprets the result set in plain language (~1s)

End-to-end latency stays under 5 seconds in most cases, which is well within acceptable range for data analysis. When validation fails, the error is fed back to the model for one or two automatic retries before surfacing a friendly message to the user.

One operational detail worth emphasizing: log every generated SQL query and its result. That log is the best source of signal for improving schema comments and prompts over time. An hour each week reviewing failure cases translates directly into measurable accuracy gains.

---

A Note on the API Layer

Text-to-SQL pipelines tend to mix models — a lightweight model for embedding and schema retrieval, a flagship model for SQL generation, sometimes a third call for result interpretation. Managing multiple API keys, billing accounts, and SDK versions across all of that adds friction that compounds over time.

I route all of it through XycAi, which exposes a single OpenAI-compatible endpoint covering 200+ models — Claude Sonnet 4.6, GPT-4.1, and the rest — starting at 14% of official list price. For teams deploying in regulated environments, it also covers licensed LLM algorithm filing with compliant global invoicing, which makes the procurement and finance conversation considerably easier.