AI SQL Assistant: Schema Prompts and Read-Only Guardrails That Hold Up

An AI SQL assistant fails in two ways. It writes a query that does damage, or it writes a query that runs fine and returns the wrong number. The first needs a hard guardrail in code. The second needs a better schema prompt and tests. This post builds both on a small SQLite database, and everything below that says "I ran" was run on 2026-10-09 (Python 3.13.16, SQLite 3.45.1).

If you want the full agent loop with charting, read build a data analyst agent with text-to-SQL first. This post zooms into the two pieces that decide whether you can trust it: what goes into the schema prompt, and what stops a bad query before it reaches the database.

What belongs in a schema prompt for text-to-SQL?

A bare CREATE TABLE dump is the usual starting point and it is not enough. The model can read column names, but it cannot know that status = 'paid' is what your finance team calls revenue, or that dates are text.

The sample database has four tables: customers, products, orders and order_items, with rupee prices as integers. The customers table also has name and email columns that I do not want the model to see. A short script (not shown, about 30 lines of PRAGMA table_info and PRAGMA foreign_key_list calls) built the prompt from the live database, so it cannot drift from the real schema. It hides the blocked columns, adds join keys, and lists the allowed values of low-cardinality columns. Here is the output I got:

SQLite schema (only these columns exist for you):
TABLE customers (
  id INTEGER,
  city TEXT,
  signup_date TEXT
)
TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT,
  price_inr INTEGER
)
TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  order_date TEXT,  -- ISO text YYYY-MM-DD; use strftime('%Y-%m', order_date) for months
  status TEXT,  -- paid | refunded | cancelled; revenue means status = 'paid' only
  payment_method TEXT
)
TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  qty INTEGER,
  unit_price_inr INTEGER  -- whole rupees, price at time of sale
)
JOINS: orders.customer_id -> customers.id; order_items.product_id -> products.id; order_items.order_id -> orders.id
VALUES orders.status: ['cancelled', 'paid', 'refunded']
VALUES orders.payment_method: ['card', 'cod', 'upi']
VALUES products.category: ['mug', 'snack', 'tea']

Five things in that prompt earn their place:

1. Only the columns the model may use. No email, no name. It cannot misuse what it never saw. (That is a convenience, not security. The guardrail below is the security.)
2. Join keys spelled out. Models guess joins well on tidy schemas and badly on legacy ones with cust_cd and ord_hdr_no.
3. Allowed values. Without ['cancelled', 'paid', 'refunded'] the model will write status = 'completed' and silently return zero rows.
4. The date format. SQLite has no date type. Say so, and name the function to use.
5. Business definitions next to the column they apply to. "Revenue means paid only" belongs on orders.status, not in a paragraph the model may skim past.

Then add the rules. This is an untested template; I did not run it against a live model, so adjust it to your database and test it (see the last section):

You write one read-only SQLite query per question.
Rules:
- Output only the SQL, no commentary, no markdown fences.
- Use only the tables and columns listed in the schema. Name columns; never SELECT *.
- If the question cannot be answered from the schema, output exactly: CANNOT_ANSWER: <reason>
- Add LIMIT 100 unless the question asks for a single aggregate.
- Money is in whole rupees. Do not divide or format it.
- Use table aliases and qualify every column when joining.

The CANNOT_ANSWER escape hatch matters. Without it, a model asked about a column that does not exist will invent one.

How do you make AI-generated SQL read-only?

Do not parse SQL with regular expressions and hope. Let the database enforce the rules. In SQLite, three layers work together:

• A connection opened with mode=ro plus PRAGMA query_only = ON, so writes fail at the engine level.
• An authorizer callback (Python's Connection.set_authorizer), which SQLite calls while compiling the statement, once per table column read. It can deny any column you did not allow, even inside a CTE or subquery.
• A progress handler that aborts a query after a time limit, and fetchmany to cap rows.

Here is the guardrail I ran. The only text check is a cheap first filter; the authorizer does the real work.

import sqlite3, time

ALLOWED_TABLE_COLS = {
    "customers": {"id", "city", "signup_date"},          # name and email hidden
    "products": {"id", "name", "category", "price_inr"},
    "orders": {"id", "customer_id", "order_date", "status", "payment_method"},
    "order_items": {"order_id", "product_id", "qty", "unit_price_inr"},
}

class Rejected(Exception):
    pass

def make_authorizer(real_tables):
    def authorizer(action, arg1, arg2, db_name, source):
        if action in (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_FUNCTION, sqlite3.SQLITE_RECURSIVE):
            return sqlite3.SQLITE_OK
        if action == sqlite3.SQLITE_READ:
            table, col = arg1, arg2
            if table.startswith("sqlite_"):
                return sqlite3.SQLITE_DENY
            if table not in ALLOWED_TABLE_COLS:
                # a CTE or subquery alias is fine; a real table we did not list is not
                return sqlite3.SQLITE_DENY if table in real_tables else sqlite3.SQLITE_OK
            if col == "" or col in ALLOWED_TABLE_COLS[table]:   # "" is COUNT(*) style
                return sqlite3.SQLITE_OK
            return sqlite3.SQLITE_DENY
        return sqlite3.SQLITE_DENY               # INSERT, UPDATE, DELETE, DROP, ATTACH, PRAGMA...
    return authorizer

def run_safe(sql, db_path, max_rows=50, timeout_s=2.0):
    s = sql.strip().rstrip(";").strip()
    if not s.lower().startswith(("select", "with")):
        raise Rejected("only SELECT / WITH queries allowed")
    con = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    try:
        con.execute("PRAGMA query_only = ON")
        real = {r[0] for r in con.execute("SELECT name FROM sqlite_master")}
        con.set_authorizer(make_authorizer(real))
        start = time.monotonic()
        con.set_progress_handler(lambda: 1 if time.monotonic() - start > timeout_s else 0, 1000)
        try:
            cur = con.execute(s)                 # raises if more than one statement
            rows = cur.fetchmany(max_rows + 1)
        except sqlite3.DatabaseError as e:       # includes Programming/Operational errors
            raise Rejected(f"{type(e).__name__}: {e}")
        return [d[0] for d in cur.description], rows[:max_rows], len(rows) > max_rows
    finally:
        con.close()

One lesson from building it: my first version rejected a harmless recursive CTE with a bare "not authorized". I printed every authorizer call and found SQLite reports a read of the CTE's own name n as a table read with an empty column. A strict "table must be in the allowlist" rule is therefore too strict, so names that are not real tables pass. The catch, found in review: if that branch simply returns OK, any real table you forgot to list (say api_keys) is readable. That is why the code asks sqlite_master for the real table names first and denies those that are not on the list. I tested that case with a throwaway table and it is rejected. Expect to tune the authorizer against your own query shapes.

Does the guardrail actually stop bad queries?

I ran thirteen queries through run_safe: two good ones, ten hostile or broken ones, and one oversized result. This is the real output of the code above (re-run after the unlisted-table fix, with identical results); the script prints the elapsed time only when a query took over half a second:

[OK ] good: revenue by payment method: cols=['payment_method', 'revenue'] rows=3 truncated=False
[OK ] good: CTE: cols=['MAX(n)'] rows=1 truncated=False
[REJ] bad: DROP: only SELECT / WITH queries allowed
[REJ] bad: stacked statements: ProgrammingError: You can only execute one statement at a time.
[REJ] bad: UPDATE inside CTE: DatabaseError: not authorized
[REJ] bad: read email column: DatabaseError: access to customers.email is prohibited
[REJ] bad: SELECT * on customers: DatabaseError: access to customers.name is prohibited
[REJ] bad: hallucinated column: OperationalError: no such column: total_amount
[REJ] bad: sqlite_master peek: DatabaseError: access to sqlite_master.sql is prohibited
[REJ] bad: ATTACH: only SELECT / WITH queries allowed
[REJ] bad: PRAGMA: only SELECT / WITH queries allowed
[REJ] runaway: recursive CTE to 100 million: OperationalError: interrupted (2.00s)
[OK ] big result gets truncated: cols=['order_id', 'product_id', 'qty', 'unit_price_inr'] rows=50 truncated=True

Read the interesting lines:

• UPDATE hidden inside a WITH passes the "starts with SELECT or WITH" prefix check and is stopped by the engine. That is the point of not trusting text filters.
• SELECT * FROM customers fails because * expands to name, and the authorizer sees the read. Your prompt should tell the model to name columns, so this failure rarely occurs.
• The hallucinated column is not a security event, just an error. This is the most common failure in practice, and it is the one you can feed back to the model.
• The runaway query took 2.00 seconds and was interrupted. Note that the progress handler is what catches this, not the authorizer.

What this does not cover: a different database engine. PostgreSQL and MySQL have no authorizer hook, so there you rely on a database role with SELECT granted on specific views (and on specific columns, which both support), a read replica, and a statement timeout. The principle is the same: permissions live in the database, not the prompt. Also see prompt injection against tool-using agents; a poisoned value inside a table can try to steer your assistant, and a read-only role limits what that can do.

What happens when the model gets it wrong? Feed the error back once

The Rejected message is useful input. A small retry loop sends the error text back and asks for a corrected query. This is an untested template:

Your last query failed.
Query: {previous_sql}
Error: {error_message}
Write a corrected query using only the schema above. If the schema cannot answer the question, output CANNOT_ANSWER: <reason>.

Cap retries at two. If the second attempt fails, show the user the error and the schema it was working from. Looping until it works is how a model eventually finds a query that passes the guardrail but answers a different question.

Safe queries can still be wrong

A guardrail cannot see meaning. On the sample data I ran three plain queries:

revenue, no status filter: 95060
revenue, status='paid':    57720
orders counted after join to items: 200 | real order count: 100

The unfiltered revenue is 65 percent higher than the real paid revenue (95,060 against 57,720 rupees). Counting orders after joining to order_items returned 200, double the true 100, because every order with several line items appears several times. Both queries are read-only, in-schema and fast. Both would pass the guardrail and sit confidently in a dashboard.

The defences are boring and they work:

• Business rules in the schema prompt, as above. "Revenue means paid only" removed the first error by construction.
• Golden questions. Keep 10 to 20 questions with known answers computed by SQL you wrote by hand, and rerun them whenever the prompt or model changes. Building evaluation datasets covers how to structure them.
• Show the SQL with every answer. An analyst can spot a missing WHERE status = 'paid' in two seconds. A bare number hides it.
• Prefer COUNT(DISTINCT o.id) in your examples when joins are involved, and put one such example in the prompt.

For more prompt wording on the analyst side (EDA, window functions, query optimisation), the data analyst prompt pack has ready-to-copy versions.

Checklist before you point it at real data

• Connection is read-only at the database level (role, replica, or mode=ro), not by prompt.
• Table and column allowlist enforced by the driver or by views, with PII columns excluded.
• Row cap, time limit and a single-statement rule.
• Schema prompt lists allowed values, date format, join keys and business definitions.
• CANNOT_ANSWER path exists, and the retry loop stops after two attempts.
• Golden questions pass, and every answer shows its SQL.
• Logs record the question, the SQL, the rejection reason and the row count, so you can audit it later.

If you must send real customer data to a hosted model, strip personal fields before the rows reach the prompt; PII redaction before LLM calls shows a regex and Presidio approach.

What I could not test here: a live model writing the queries. The prompts above are templates, and the sample data is synthetic, 100 orders generated by a script. The guardrail results are real; the model behaviour is for you to measure on your own schema.