Let a language model only propose SQL: validate it, run it read-only in the browser, log everything

Context

The revival adds an optional text-to-SQL feature: a visitor asks a question in plain English and a language model writes the query. Model output is untrusted code. The site is static and has no budget for AI, so it cannot hold a key or run a model, and the demo database ships to the browser anyway (the SQL playground runs it there with sql.js). The feature also has to show what responsible use of a model looks like: what it is allowed to do, how often it is right, and who decides.

Decision

The visitor brings their own key (Anthropic by default, Claude Haiku 4.5 or Sonnet 5.5; or OpenAI with a model id they choose). It is kept in sessionStorage unless they choose to remember it on the device, and calls go straight from the browser to the provider. The model sees the documented schema, never the data, and must return one query in a fixed JSON shape that is checked with zod on arrival. The query then has to pass three layers before and while it runs:

1. A validator (validateSql) works on tokens, not on raw text. It allows exactly one statement starting with SELECT or WITH; only the 19 documented tables and 2 views, or a common table expression defined before its use; no PRAGMA, ATTACH, DDL, DML or transaction statements; no table-valued functions, schema-qualified names or system tables; no file, extension or unbounded-allocation functions (load_extension, readfile, zeroblob, randomblob, printf, ...); no recursion, including a common table expression that refers to itself or to a later one without the RECURSIVE keyword; no bound parameters. A missing top-level LIMIT becomes LIMIT 200; one above 500 is lowered to 500, and the page says so.
2. SQLite itself runs the query on an in-memory copy of the database inside a Web Worker, opened with PRAGMA query_only = ON, so even a query that slipped past the validator cannot write.
3. A time limit: a query still running after 10 seconds is stopped by terminating the worker.

Every output is labelled AI-generated; the person accepts, edits (re-validated, logged as a linked record) or rejects it; and every call is appended to an audit log in the browser's IndexedDB, without the key, viewable and exportable at /ai-log. A Content Security Policy limits the page's network requests to this site and the two providers.

Options considered

1. A deny-list of regular expressions over the SQL text. Easy to write and easy to fool: keywords inside strings or comments cause false alarms, and comment tricks hide real ones.
2. A full SQL parser library. Thorough, but the parsers that run in a browser add hundreds of kilobytes and still disagree with SQLite on edge cases, so the engine would remain the real judge anyway.
3. A token-based validator plus engine-level read-only mode plus a time limit. Chosen.
4. Trust the model and rely on the read-only copy alone. Writes would fail, but nothing would stop runaway recursion or huge allocations, and the visitor would get no explanation of why a query was refused.

Why

Each layer covers what the one before it cannot: the validator explains refusals in plain words and blocks the expensive cases early, SQLite enforces read-only whatever the validator missed, and the timeout ends anything still too slow. Running in the browser means no server ever holds a key or executes model output. The design is informed by the transparency principles in the Australian Government's policy for the responsible use of AI in government, the EU AI Act's transparency obligations for AI-generated content, and the NIST AI Risk Management Framework's map, measure and manage functions. It does not claim compliance with any of them.

What happened

• The validator's tests block 55 adversarial cases (stacked statements, writes hidden behind WITH, PRAGMA, ATTACH, system and qualified tables, tables hidden in parenthesised joins or behind a same-named CTE, x IN table and x IN table_function(...), table-valued functions, file and allocation functions, three forms of recursion, parameters, non-literal LIMITs and unterminated strings or comments), plus over-long input.
• A self-review before the first commit found one gap: a table wrapped in parentheses, as in SELECT * FROM (sqlite_master), which SQLite accepts, escaped the allow-list because the scanner treated every parenthesis after FROM as a subquery. Parenthesised joins are now scanned like any other FROM clause, with tests.
• Two independent reviews before merging found two more gaps, both confirmed to run in sql.js on the demo database. First, a CTE named after a system table (WITH sqlite_master AS (...) inside a subquery) licensed the real sqlite_master elsewhere in the same query, because any name matching any CTE was skipped without checking where the CTE was in scope; one such query returned all 50 schema rows on /ask. Second, SQLite's x IN table and x IN table_function(...) forms were never scanned, so 'THREADSAFE=1' IN pragma_compile_options ran. CTE names now count only inside their own WITH scope, system-table names are refused before CTE names are considered, and a name after IN is checked like a FROM relation. The harm was small (a read-only copy of synthetic data whose schema is public), but this is the control the safeguards text describes, so the earlier claim that the allow-list gap was closed was wrong until this fix.
• All ten business-question queries and all 20 gold evaluation queries pass it unchanged except for an added LIMIT, and none of the gold answers comes near the 200-row default, so the rewrite never changes a correct answer in the evaluation. A test also confirms that SQLite refuses a DELETE once query_only is on.
• The self-referencing CTE without RECURSIVE was covered from the start, because a review of a sibling project had found exactly that gap there.
• No accuracy figures are published. The site has no AI budget and does not use anyone else's key, so the evaluation harness (/eval) is there for visitors to run; it reports execution accuracy with Wilson 95% intervals and compares runs with an exact McNemar test.
• The allow-list is table-level, not column-level: an unknown column is caught by SQLite, not by the validator, so the model gets a SQLite error message rather than a policy message.
• A deliberately heavy but legal query (a cross join of the largest tables with a LIMIT) is not caught by the validator; only the time limit stops it, and terminating the worker means the next query reloads the database.
• The Content Security Policy needs 'unsafe-inline' for scripts, because the site does not use nonces, so it narrows where a stolen key could be sent but is not a complete defence; the real protection is that the site loads no third-party scripts.

What I'd change

• A column-level allow-list generated from the schema documentation, so unknown columns get a clear policy message.
• Reject plans that EXPLAIN QUERY PLAN shows to be full cross joins of large tables before running them.
• Make the audit log tamper-evident (hash-chain the records) and offer an export the visitor can sign or store elsewhere, since a browser-only log disappears with the site data.
• Re-check the default model ids each quarter; they age faster than anything else on the site.