Automotive Analyst

Manufacturing data warehouses answer valuable questions, but most users cannot translate an operations question into correct SQL quickly. A plain-English analyst can help, but generated SQL against a database needs stronger safety boundaries than a typical chat demo.

The project needed to let a visitor ask questions over the synthetic factory warehouse while keeping the LLM key out of the backend and preventing writes, catalog access, chained statements, or runaway queries.

Automotive Analyst uses a bring-your-own-key model. The visitor selects Claude, OpenAI, or Gemini, and the browser sends the schema-grounded prompt directly to that provider. The generated SQL is then posted to the backend for validation and execution.

The backend holds no LLM secret. Its job is deliberately narrower: serve schema context, validate SQL through an allow-list guardrail layer, execute inside a read-only transaction using a read-only database role, and return the answer with the exact SQL shown.

The project treats client-supplied SQL as untrusted by default. The app layer rejects chained statements, write operations, admin functions, catalog access, unknown tables, comment obfuscation, and unsafe query shapes before anything reaches the database.

The database then provides a second boundary: execution happens through a dedicated read-only role inside a read-only transaction with a timeout. Even if the app guardrail missed something, the database role is still not allowed to write.

This project does not claim the model writes correct SQL. It claims that nothing the model writes can do damage. That is a testable claim, and it needed a test, so I wrote an adversarial corpus of 43 attacks against the validator and 16 ordinary analytical queries that must still be allowed through.

Writing it found a real hole. The relation parser matched only bare identifiers, using a pattern restricted to letters and underscores. Against a quoted identifier it therefore matched nothing at all. An empty match set meant no unknown tables were found, and the allow-list passed by default rather than failing closed. Postgres accepts quoted identifiers, so SELECT * FROM "users" read a table that was never on the list, and so did "information_schema"."tables". Eight of nine quoting variants got through. The allow-list was not rejecting quoted names; it never saw them.

Severity, stated accurately. The database role is read-only and the query runs inside a read-only transaction, so this was unauthorised reading rather than writing, and the defense-in-depth design held. But the allow-list is the specific control that is supposed to stop arbitrary table reads, and it was not doing that. A design being layered is not a reason to under-report a failure in one of the layers.

The fix, and why it is more than a wider regex. The parser now reads quoted and bare identifiers in both the schema and object position, and folds them to a comparable form. More importantly the validator now fails closed: every FROM and JOIN has to be accounted for as either a named relation or a parenthesised subquery, and a query whose sources cannot all be classified is refused rather than passed on an empty match. That is what catches quoting styles I did not think of, including backticks and brackets.

The same pass fixed three false positives. Keyword scanning ran across string literals, so SELECT 'drop' AS label was rejected. A semicolon inside a literal read as statement chaining. A column named version tripped the admin-function denylist. These matter because a guardrail that blocks ordinary work gets disabled, so the suite scores false positives as failures alongside bypasses.

The corpus lives at backend/tests/test_guardrails_adversarial.py and runs in CI. The quoted-identifier case is the first entry in it.