Design text-to-SQL around the query it gets wrong

Text-to-SQL will sometimes write the wrong query, so the design work is limiting what that query can do and putting the SQL where a reviewer can see it.

Question Schema retriever SQL generator Parse and check Executor Answer Feedback log read-only, timeout, row cap with SQL shown rejected, with the error
Generated SQL has to pass a parse and rule check before a read-only role runs it, and a rejected statement goes back with its error.
On this page
  1. Six tables out of 84
  2. A role that can only read
  3. Five rules before anything runs
  4. 120 questions at Dunmore
  5. The SQL goes next to the number

The model will write wrong SQL. Not often, if you do the work, but often enough that I’d spend more design time on the wrong query than on the right one.

A wrong query can go bad in two ways. It can hurt the database, with a write nobody intended or a join that multiplies two large tables. Or it can run cleanly and return a believable number that’s wrong. The first you can make impossible. The second you can only make visible, and each stop in the diagram above does one job or the other.

I’ll walk the route with a retailer I made up, Dunmore Cycles. Its Postgres reporting schema has 84 tables.

Six tables out of 84

GPT-4 Turbo is still in preview, and its 128K context window would hold all 84 table definitions. I wouldn’t send them. Dunmore has four tables with “order” in the name, and every one in the prompt is another chance for the model to pick the wrong one.

So each table gets a short written description covering what one row is and how it relates to the tables around it. The descriptions are embedded and stored in the same Postgres with pgvector, which is where I keep vectors at this size anyway. The retriever embeds the question and puts the six closest tables into the prompt. At 84 rows there’s no need for an HNSW index. An exact scan is fast enough, and it never misses a neighbor.

The generator returns its query as the argument of a function call, so there’s no chatty reply to dig the SQL out of.

A role that can only read

Generated SQL runs under its own database role. The hard guarantees live here, where no prompt can reach them.

-- Run once as an admin. Set the password separately.
CREATE ROLE ask_reader LOGIN CONNECTION LIMIT 8;

-- Databases created before PostgreSQL 15 let any role create tables in public.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

GRANT USAGE ON SCHEMA reporting TO ask_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO ask_reader;
-- Applies to tables this admin role creates later.
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO ask_reader;

ALTER ROLE ask_reader SET default_transaction_read_only = on;
ALTER ROLE ask_reader SET statement_timeout = '5s';
ALTER ROLE ask_reader SET search_path = reporting;

The grants are the wall. This role can read reporting and can’t change a row of it, whatever the model was talked into. If store managers should only see their own store’s rows, that’s a row-level security policy on the same tables, again in the database.

The three settings are weaker than they look. They’re session defaults, and a session can switch any of them off with a SET. That’s one reason the next stop accepts a single SELECT and nothing else. A SELECT can still call set_config(), though, so the executor runs RESET ALL on the connection before every query, which puts the role’s defaults back.

Five rules before anything runs

Every statement is parsed with SQLGlot before the executor sees it, then checked against five rules. A rejection goes back to the generator with the error text, once.

import sqlglot
from sqlglot import exp
from sqlglot.errors import SqlglotError

ALLOWED = {"orders", "order_lines", "customers", "products", "stores", "returns"}
WIDE = {"orders", "customers"}  # 30 or more columns each
ROW_CAP = 500


class Rejected(Exception):
    """The message is sent back to the model as the retry prompt."""


def check(sql: str) -> str:
    try:
        parsed = [s for s in sqlglot.parse(sql, read="postgres") if s is not None]
    except SqlglotError as err:
        raise Rejected(f"SQL did not parse: {err}") from err

    # 1. One statement, and it is a SELECT.
    if len(parsed) != 1 or not isinstance(parsed[0], exp.Select):
        raise Rejected("Send exactly one SELECT statement.")
    tree = parsed[0]

    # 2. Only allow-listed tables. CTE names are not tables.
    ctes = {cte.alias for cte in tree.find_all(exp.CTE)}
    tables = {t.name for t in tree.find_all(exp.Table)} - ctes
    if tables - ALLOWED:
        raise Rejected(f"Unknown tables: {sorted(tables - ALLOWED)}")

    # 3. Every join carries a predicate. Comma joins fail here too.
    for join in tree.find_all(exp.Join):
        if join.args.get("on") is None and not join.args.get("using"):
            raise Rejected("Every join needs an ON or USING clause.")

    # 4. No SELECT * when a wide table is involved.
    if tables & WIDE and any(s.is_star for s in tree.find_all(exp.Select)):
        raise Rejected(f"List the columns you need from {sorted(tables & WIDE)}.")

    # 5. A LIMIT no larger than the cap, added if missing.
    limit = tree.args.get("limit")
    count = limit.args.get("expression") if limit is not None else None
    if count is None or not count.name.isdigit() or int(count.name) > ROW_CAP:
        tree = tree.limit(ROW_CAP)

    return tree.sql(dialect="postgres")

The messages are written for the model, because they become the retry prompt. In the real thing ALLOWED is every table that has a description, so a table nobody has described can’t be retrieved or queried.

These rules are stricter than they need to be. A UNION fails the first one, and I’d loosen that only when the log shows real questions running into it. They also won’t stop someone determined: JOIN ... ON true satisfies rule three. I’m fine with that. Hostile SQL is the role’s problem, and the rules are there for the careless, expensive kind.

Rule five rewrites the statement where the others reject it. The executor still stops fetching at 500 rows, in case that rewrite ever has a bug.

120 questions at Dunmore

Say Dunmore’s analysts write 120 test questions with known answers before launch. The results below are invented along with the shop, in proportions I’d call a decent start.

Outcome Questions
Right on the first try 97
Rejected, then right after one retry 14
Rejected twice and never run 6
Ran and returned a wrong number 3

The 14 are the retry loop paying for itself. Each failed the check, got its error back, and came out right. That’s 111 of 120.

The six never touch the database. Each is still joining without a predicate on its second attempt, so the person asking gets “I couldn’t write a safe query for that” where a number would have been. I count those as the system working.

The last three are the ones that worry me. They parse, pass all five rules, finish well inside five seconds, and return a figure in the right range. All three sum order_lines and never subtract returns, where Dunmore’s finance team reports revenue net of returns. No parser rule knows that.

The SQL goes next to the number

So an answer never travels alone. Under it sit the SQL that produced it and the row count. In this story the reviewers caught all three wrong answers, and only because the query on the screen never mentioned returns. A fluent sentence with a number in it is persuasive. Put the query under it and someone who knows the business has something to object to.

Plenty of the people asking can’t read SQL. The row count is for them: zero rows means a filter matched nothing, and exactly 500 almost always means the cap cut the result short. The SQL is for the analysts they forward the answer to, and analysts are the ones who press thumbs-down.

That press writes a row to the feedback log: the question, the tables retrieved, the SQL, the row count. The fix goes in the order_lines description, as one line saying revenue questions subtract matching rows in returns. A sixth rule wouldn’t have caught the missing subtraction. Then the three questions join the test set.

I’d put this in front of people with three in 120 still wrong, as long as the SQL is showing. With the SQL hidden, I wouldn’t at any score.