~/projects/VocalSQL · README.md
VocalSQL — Natural Language to SQL
A self-correcting 7-node LangGraph agent that turns plain-English questions into safe, validated SQL — grounded by RAG schema retrieval, scored for confidence before it runs anything, and backed by a 9-layer defense against hallucinated tables, columns, and queries.
01 · overview
The problem
Asking an LLM to turn a question straight into SQL is easy to demo and risky to ship. A single generation call has no way to check itself — it can reference a column that doesn't exist, silently misjoin two tables, or return a confident, wrong answer with no signal that anything went wrong. For a tool meant to sit in front of a real database, "usually right" isn't good enough.
VocalSQL treats text-to-SQL as a pipeline problem, not a single-call problem: retrieve the real schema before generating anything, score the model's own confidence before running the query, validate syntax and schema-compliance before execution, and give the model a chance to fix its own mistakes — with a bounded number of retries — before ever falling back to silence rather than a wrong answer.
The demo ships with a self-contained 15-table e-commerce schema (customers, orders, products, inventory, payments, and more) seeded with 2,000+ records, and the same pipeline connects to SQLite, PostgreSQL, or MySQL databases registered at runtime.
02 · architecture
System architecture
A LangGraph state machine — nine nodes, three conditional gates — served behind a FastAPI backend.
keyword gate
ChromaDB
Q→SQL pairs
temp = 0.0
0–100 score
+ safety checks
guard
data table
The router blocks data-modification intent (DROP / DELETE / UPDATE / INSERT) with zero-cost keyword matching before any LLM call is made, and routes genuinely unclear questions straight to a clarification response.
If the fused confidence score signals real ambiguity, the graph skips validation entirely and asks the user a clarifying question instead of guessing at intent.
An invalid or failed query routes to the corrector, which feeds the exact error back to the LLM and returns to the validator — up to 3 times — before the pipeline gives up gracefully.
router → blocked/unclear? → format_response # else continue → retrieve_schema → retrieve_fewshots → generate_sql → confidence_analyzer needs_clarification? → format_response else → validate_sql is_valid? → execute_sql → format_response invalid, retries < 3 → correct_sql ↩ back to validate_sql invalid, retries ≥ 3 → format_response # graceful failure execute_sql error, retries < 3 → correct_sql ↩ back to validate_sql
03 · how it works
The seven nodes
Router
Classifies intent with fast keyword matching — no LLM call yet. Data-modification requests are blocked immediately; everything else is tagged simple_query, aggregation, join_query, or unclear and passed on.
Schema retriever (RAG)
Embeds the question and pulls the top-5 most relevant table/column descriptions from a ChromaDB vector store, instead of stuffing the model's context with the entire database schema on every call.
Few-shot retriever (RAG)
Separately retrieves the top-3 most similar past question → SQL pairs, so the generator has concrete, verified patterns to follow rather than inventing SQL style from scratch each time.
SQL generator
Calls Llama 3.3 70B (via Groq) at temperature = 0.0 with a structured prompt combining the retrieved schema, the few-shot examples, and the question — generation is deliberately deterministic, not creative.
Confidence analyzer
Fuses four signals — schema coverage, few-shot similarity, query complexity, and the LLM's own self-rated confidence — into a single 0–100 score, plus a structured report of which tables were used, what terms were ambiguous, and what assumptions were made. When the score drops below 70 and the model has flagged specific ambiguous terms, the graph short-circuits straight to a clarification question instead of guessing.
Validator + corrector loop
Three checks run before anything touches the database: sqlglot syntax parsing, schema-compliance (every referenced table/column has to actually exist), and a safety guard restricting execution to single SELECT statements. A failure routes to the corrector, which hands the exact error back to the LLM for a repair attempt — capped at 3 retries — before the pipeline fails gracefully instead of returning something wrong.
Executor + response formatter
The validated query runs with a 10-second timeout guard, and the result set — along with the SQL that produced it — is turned into a natural-language answer plus a data table for the UI.
04 · guardrails
Nine layers against hallucination
Each layer is cheap on its own; stacked together, they catch different failure modes at different stages of the pipeline — before generation, right after it, and as a last-resort repair.
| # | Layer | Type | Cost | What it catches |
|---|---|---|---|---|
| 1 | Schema scoping via RAG | Preventive | ~50ms | Never shows the model a table or column that doesn't exist |
| 2 | Few-shot examples | Preventive | ~30ms | Anchors generation to real, previously-solved query patterns |
| 3 | Temperature 0.0 | Preventive | Free | Removes sampling randomness — same question, same SQL |
| 4 | Structured prompts | Preventive | Free | Explicit constraints (read-only, dialect, output format) baked into the system prompt |
| 5 | sqlglot syntax check | Detective | <5ms | Parses the generated SQL before it ever reaches the database |
| 6 | Schema compliance | Detective | <1ms | Cross-checks every referenced table/column against the real schema |
| 7 | Safety guards | Detective | <1ms | Rejects anything beyond a single read-only SELECT |
| 8 | Self-correction loop | Corrective | ~500ms/retry | Feeds the exact validator error back to the LLM for a repair attempt, up to 3× |
| 9 | Graceful failure | Fallback | Free | After retries are exhausted, tells the user it couldn't answer confidently — instead of guessing |
The split matters: preventive layers keep bad SQL from being generated in the first place, detective layers catch what slips through before execution, and the corrective layer gives the system one more chance to fix itself — with a hard ceiling so a stubborn failure can't loop forever. Layer 9 is the one that matters most for trust: the system is designed to say "I'm not confident enough to answer that" rather than return a fabricated result.
05 · worked example
One question through the pipeline
Schema and output shown are accurate to the live demo database (15-table e-commerce schema); this is an illustrative walkthrough of one query, not a logged benchmark run.
"Who are the top 5 customers by total spend in the last 90 days?"
customers, orders
SELECT c.first_name || ' ' || c.last_name AS customer_name, c.customer_tier, ROUND(SUM(o.total_amount), 2) AS total_spend, COUNT(o.id) AS order_count FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.order_date >= date('now', '-90 days') AND o.status != 'cancelled' GROUP BY c.id ORDER BY total_spend DESC LIMIT 5;
Score 91 / 100 · Risk level Low
Signals: high schema coverage, close few-shot match, moderate join complexity
"Last 90 days" resolved relative to the current date; cancelled orders excluded from spend totals.
06 · tech stack
Built with
| Method | Endpoint | Purpose |
|---|---|---|
| POST | /api/query | Convert a natural-language question to SQL and execute it |
| POST | /api/feedback | Submit a correction to improve future queries |
| GET | /api/databases | List registered databases |
| POST | /api/databases | Register a new database connection |
| DELETE | /api/databases/{id} | Remove a registered database |
| POST | /api/databases/{id}/reindex | Re-index a database's schema into ChromaDB |
| GET | /api/health | Health check |