The problem#
Asking a database a question in English is easy to demo and hard to trust. The model invents columns, guesses what a status value looks like, and a single bad statement against a customer's production database is not something you can undo.
The approach#
Every question goes through a five-stage pipeline, and the last stage is a gate rather than an executor. Nothing reaches the customer's database until a person approves the SQL.
- Classify: an intent check and safety guard decide whether the question is safe, needs a clarifying question, or is ready to generate.
- Retrieve: parallel vector search over four tenant-scoped Qdrant collections: schema docs, glossary terms, past query pairs and uploaded business documents.
- Select: a fast LLM call prunes the schema to the tables and columns the question needs.
- Generate: a separate generation-tier model writes the SQL, and a static validator feeds errors back into up to three retries.
- Execute: the SQL is risk-tiered, shown for approval, and only then run in a read-only transaction.
Schema introspection also samples low-cardinality values, so the generator knows a status column holds 'SUCCEEDED' and 'REFUNDED' rather than just "text".
Architecture#
A FastAPI backend serves a React and Vite frontend. Each tenant's own database is reached through a per-tenant asyncpg pool, kept separate from the platform's own Postgres, which holds workspaces, members, billing, audit logs and chat sessions and uses row-level security policies. Qdrant holds the embeddings, Redis holds session context and rate limits, and an ARQ worker handles embeddings, webhooks, notifications and scheduled jobs such as nightly schema diffs.
Generation is streamed: POST /v1/query/generate/stream returns server-sent events for each stage, so the UI shows intent, schema, SQL and review steps instead of a spinner. The model tiers are configured separately, and both accept any OpenAI-compatible endpoint with an optional fallback provider.
Hover or tap any part to trace what it talks to. 18 parts, 24 connections. Swipe sideways to see it all.
- 1The browser posts a question to the streaming generate endpoint and the API checks the plan's query limit.
- 2Stage 1 runs the blocklist and injection checks, then classifies intent, and may stop to ask a clarifying question.
- 3Stage 2 embeds the question once and searches four tenant-scoped Qdrant collections in parallel.
- 4Stage 3 prunes the schema with a fast model and builds the prompt; each stage is streamed to the browser as an SSE event.
- 5Stage 4 generates SQL, validates it with sqlglot and a blocklist, and retries with the error and a higher temperature if it fails.
- 6The user reviews the SQL and risk tier and approves; the executor runs it in a read-only transaction on the customer's database.
- 7Secondary path: uploading a business document stores it in MinIO, and an ARQ job extracts, chunks, embeds and writes it to Qdrant.
- 8Secondary path: nightly cron jobs re-introspect each tenant's schema and diff it, and webhook events are delivered with retries.
Making generated SQL safe to run#
Validation is five deterministic steps, none of which call an LLM: a keyword blocklist, a sqlglot parse, a single-statement check, a check that the statement is a SELECT (or a WITH ending in one), and a ban on UNION. Failures are written back into the next prompt, and the temperature rises on each retry.
temperature = (
settings.LLM_TEMPERATURE_DEFAULT
if attempt == 0
else settings.LLM_TEMPERATURE_RETRY_1
if attempt == 1
else settings.LLM_TEMPERATURE_RETRY_2
)Even SQL that passes is run inside BEGIN READ ONLY with a statement timeout and a ROLLBACK in a finally block, and a row limit is injected before execution.
What I learned#
The model is the least reliable part, so most of the code is about containing it: validating its output, grounding it in real schema, and keeping a person in the loop before anything executes.