Upload a messy, real-world Excel workbook and ask questions about it in plain English. The system infers schema, generates DuckDB SQL via Groq-hosted GPT-OSS 120B, validates it for safety before execution, and self-corrects on failure.
This is schema-aware NL→SQL generation — not RAG. There is no chunking, embedding, or vector retrieval. The LLM sees the full inferred schema and generates precise SQL against it.
What this demonstrates: safety-first LLM systems (an AST validator the model can't talk its way past), real messy-spreadsheet ingestion, and evaluation rigor — a golden-answer eval that scores the returned row set, not just "did SQL run". Ships with API-key auth, per-caller rate limiting, upload bomb guards, a normalized-question result cache, and token/cost metrics.
Excel file
│
▼
[Ingestion] openpyxl + pandas
│ • Detects header row (scan first 10 rows, score by uniqueness/string-ratio)
│ • Forward-fills merged cells from top-left value
│ • Infers column types by sampling all non-null values (90% threshold)
│ • Loads each sheet into its own DuckDB in-memory table
▼
[Schema Serializer] → compact table/column/sample-values string for LLM context
▼
[NL→SQL Generator] Groq API — openai/gpt-oss-120b
│ • System prompt: schema + dialect + 3 few-shot examples
│ • Parses SQL out of fenced code block defensively
▼
[Safety Validator] sqlglot (non-negotiable gate, zero LLM/DB dependencies)
│ ✓ Must parse cleanly
│ ✓ Exactly one statement
│ ✓ Must be SELECT — no DDL/DML (DROP/INSERT/UPDATE/DELETE/CREATE/ALTER…)
│ ✓ No forbidden file/network functions (read_csv, read_parquet, httpfs…)
│ ✓ All table references in known schema
│ ✓ Qualified column references valid against their table
│ ✓ LIMIT injected if absent (never runs unbounded LLM queries)
├── fails ──► [Self-Correction Loop] original question + bad SQL + error → LLM retry
▼
[Executor] DuckDB
├── error ──► [Self-Correction Loop] (same retry budget, max 2 retries)
▼
[API Response] {sql, rows, columns, attempts, latency_ms, status, attempt_log}
Each box is an independent module with zero cross-dependencies — the Safety Validator is testable with no LLM or DB; Ingestion is testable with no API layer.
# 1. Clone and install
git clone <repo>
cd nl2sql
pip install -r requirements.txt
# 2. Set your API key (get one at https://console.groq.com)
cp .env.example .env
echo "LLM_API_KEY=gsk_..." >> .env
# 3. Generate test fixtures (optional, needed for tests)
python scripts/make_fixtures.py
# 4. Run
uvicorn app.main:app --reload --port 8000Open http://localhost:8000 for the demo UI, or http://localhost:8000/docs for the API.
LLM_API_KEY=gsk_... docker compose upIncludes Prometheus on :9090 and Grafana on :3000 (admin/admin).
| Env var | Default | Effect |
|---|---|---|
API_KEYS |
(empty) | Comma-separated keys. When set, /datasets and /query require Authorization: Bearer <key> or X-API-Key. Empty ⇒ open (dev only). |
RATE_LIMIT_PER_MINUTE |
120 |
Per-key (per-IP when auth off) sliding-window limit; 0 disables. |
MAX_SHEETS / MAX_CELLS |
50 / 2,000,000 |
Upload caps. |
MAX_DECOMPRESSION_RATIO |
120 |
Rejects .xlsx zip bombs. |
QUERY_CACHE_ENABLED / QUERY_CACHE_TTL_SECONDS |
true / 3600 |
Normalized-question result cache. |
Upload an Excel workbook (.xlsx / .xlsm). Returns dataset_id + inferred schema.
curl -X POST http://localhost:8000/datasets \
-F "file=@sales_data.xlsx"{
"dataset_id": "f3a2b1...",
"tables": [
{
"table_name": "sales",
"sheet_name": "Sales Data",
"row_count": 1234,
"columns": [
{"name": "date", "inferred_type": "date", "sample_values": ["2023-01-01", "2023-01-02"]},
{"name": "revenue", "inferred_type": "float", "sample_values": [1200.50, 980.00]}
]
}
]
}Run a natural-language question through the full pipeline.
curl -X POST http://localhost:8000/datasets/f3a2b1.../query \
-H "Content-Type: application/json" \
-d '{"question": "What is the total revenue by region for Q1 2023?"}'{
"status": "success",
"sql": "SELECT region, SUM(revenue) AS total_revenue\nFROM sales\nWHERE date >= DATE '2023-01-01' AND date < DATE '2023-04-01'\nGROUP BY region\nORDER BY total_revenue DESC\nLIMIT 1000",
"rows": [{"region": "North", "total_revenue": 245000.0}, ...],
"columns": ["region", "total_revenue"],
"attempts": 1,
"latency_ms": 1340.2,
"error": null,
"attempt_log": [...]
}Return the inferred schema for a previously uploaded dataset.
Prometheus metrics (query count, success/failure, retry count, latency histogram).
{"status": "ok"}
The ingestion module is designed for real-world files, not toy examples:
| Problem | How it's handled |
|---|---|
| Headers not on row 1 | Scores each of the first 10 rows by unique-string-ratio; picks the winner |
| Blank leading rows | Ignored — scoring naturally skips them |
| Title row before headers | Title row scores low (one long non-unique string); header row wins |
| Merged cells | Top-left value forward-filled across the merge range via openpyxl merge map |
| Mixed-type columns | Type inference samples all non-null values; requires 90% agreement for a typed column |
| Numeric strings | Attempted int/float parse in the else branch of type inference |
| Duplicate column names | Suffixed with _1, _2, etc. |
| Empty / fully-merged sheets | Skipped with a warning; rest of workbook proceeds |
| Special characters in names | Replaced with _, leading digits prefixed, truncated to 60 chars |
The validator (app/validation/validator.py) is the strictest module in the codebase. It uses sqlglot to parse and walk the AST — never string matching.
Rejection rules (in order):
- Parse failure — malformed SQL → retry
- Multi-statement — semicolons, chained statements → reject
- Non-SELECT — DROP / INSERT / UPDATE / DELETE / CREATE / ALTER / PRAGMA / TRUNCATE → reject
- Forbidden functions —
read_csv,read_parquet,read_json,glob,httpfs, etc. → reject - Unknown tables — any table not in the ingested schema → reject
- Unknown columns — qualified
table.columnreferences validated against schema - No LIMIT → injected (not rejected); default 1000 rows
The validator has no dependency on the LLM or DuckDB and is 100% unit-testable in isolation. It has the highest test coverage in the project by design.
# Generate fixtures first
python scripts/make_fixtures.py
# All tests
pytest tests/ -v
# Just the validator (the safety story)
pytest tests/unit/test_validator.py -v
# Just ingestion (the messy-spreadsheet story)
pytest tests/unit/test_ingestion.py -v
# Integration (full API with mocked LLM)
pytest tests/integration/test_api.py -v120+ tests, 0 failures. The LLM is mocked in every test, so the suite is fast, deterministic, and needs no API key.
Test breakdown (highlights):
test_validator.py+test_validator_adversarial.py— every rejection rule, plus adversarial cases: prompt-injectedDROP, stacked statements,PRAGMA/ATTACH/COPY/SET, cased/hiddenread_csv, unknown tables in subqueries/UNION, CTE shadowing — and safe-but-scary SQL that must not be flagged.test_ingestion.py— scoring heuristic, header detection, merged cells, type inference, safe names, real fixtures.test_api.py+test_auth_and_guards.py— upload/schema/query, plus auth (401/Bearer/X-API-Key), rate limiting (429 +Retry-After), and upload guards (zip-bomb ratio, sheet/cell caps).test_cache.py+test_cache_pipeline.py— normalization, TTL, LRU, dataset-scoped invalidation, and "repeated question is served without calling the LLM".
Every question has a hand-verified expected answer. A case passes only if
the returned row set matches that answer (values, order- and
column-name-independent) — not merely "the SQL ran". This is stricter than
run_benchmark.py (which only checks that rows came back) and is the figure
to quote.
export LLM_API_KEY=gsk_... # Groq key
python -m benchmark.golden_eval================================================================
NL→SQL Golden-Answer Evaluation (openai/gpt-oss-120b via Groq)
================================================================
Cases : 29
Row-set-exact accuracy : 28/29 (96.6%)
First-attempt accuracy : 89.7%
Avg latency : 3868 ms
Tokens (prompt/compl) : 19,739 / 3,451
Est. cost : $0.0055 ($0.00019/query)
By category:
clean 8/8 100.0%
messy_header 9/9 100.0%
multi_sheet 6/6 100.0%
merged 2/2 100.0%
mixed_type 3/4 75.0% (in_stock stored as "TRUE"/"FALSE" text)
================================================================
Full per-case results (SQL, attempts, pass/fail) → benchmark/golden_report.json.
Run on the free Groq tier; latency is model- and load-dependent. Swapping
LLM_MODEL=openai/gpt-oss-20b trades a few points of accuracy for ~4× lower
latency.
The original 23-question harness. It scores "did valid SQL run and return rows", which is a weaker bar than the golden eval — kept for continuity.
python -m benchmark.run_benchmarkEvery query emits a structured JSON log line:
{
"event": "pipeline_success",
"dataset_id": "f3a2b1...",
"attempt": 1,
"rows": 42,
"latency_ms": 1340.1,
"level": "info",
"timestamp": "2024-01-15T10:23:45.123Z"
}Prometheus metrics at /metrics:
| Metric | Type | Description |
|---|---|---|
nl2sql_queries_total |
Counter | All queries received |
nl2sql_queries_success_total |
Counter | Queries that returned rows |
nl2sql_queries_failure_total |
Counter | Queries that exhausted retries |
nl2sql_retries_total |
Counter | Self-correction retry attempts |
nl2sql_query_latency_seconds |
Histogram | End-to-end pipeline latency |
nl2sql_query_cache_hits_total / _misses_total |
Counter | Result-cache effectiveness |
nl2sql_llm_tokens_total{kind} |
Counter | Prompt / completion tokens consumed |
nl2sql_llm_cost_usd_total |
Counter | Estimated spend (tokens × configured unit price) |
nl2sql_uploads_total |
Counter | File uploads attempted |
nl2sql_validation_rejects_total |
Counter | SQL rejections by reason |
nl2sql/
├── app/
│ ├── ingestion/
│ │ ├── parser.py # Excel → DuckDB (header detection, merged cells, type inference)
│ │ └── schema_serializer.py # DatasetSchema → LLM prompt string + JSON dict
│ ├── nlsql/
│ │ ├── generator.py # Groq API calls (first attempt + retry)
│ │ └── pipeline.py # Orchestrates generate→validate→execute→retry loop
│ ├── validation/
│ │ └── validator.py # sqlglot-based safety gate
│ ├── executor/
│ │ └── runner.py # Runs validated SQL on DuckDB
│ ├── api/
│ │ ├── routes.py # FastAPI endpoints
│ │ ├── models.py # Pydantic request/response models
│ │ └── store.py # In-process dataset registry
│ ├── static/
│ │ └── index.html # Demo UI
│ ├── config.py # Pydantic settings
│ ├── logging_config.py # structlog JSON logging
│ ├── metrics.py # Prometheus metric definitions
│ └── main.py # FastAPI app factory
├── tests/
│ ├── fixtures/ # Generated by scripts/make_fixtures.py
│ ├── unit/
│ │ ├── test_ingestion.py # 35 tests
│ │ └── test_validator.py # 38 tests
│ └── integration/
│ └── test_api.py # 14 tests
├── benchmark/
│ └── run_benchmark.py # Eval harness + report
├── scripts/
│ └── make_fixtures.py # Generates messy test workbooks
├── deploy/
│ └── prometheus.yml
├── Dockerfile
├── docker-compose.yml
└── requirements.txt
Why DuckDB? In-process, zero setup, fast on DataFrames, first-class SQL dialect support in sqlglot. One connection per dataset gives isolation without a separate DB process.
Why not multi-provider LLM abstraction? This project's value is in the ingestion quality and the safety validator. An abstraction layer would add complexity and testing surface without proving anything. One provider, done well.
Why score header rows instead of just using row 1? Real spreadsheets routinely have report titles, company names, or blank rows before the actual headers. The scoring heuristic is simple but robust — unique strings score high, blank rows score zero, title rows score low (one cell, non-unique pattern).
Why 90% type agreement threshold? A column with 91% integers and 9% strings is almost certainly a numeric column with a few bad cells. Treating it as VARCHAR would hurt SQL generation quality. 90% is a practical balance between strictness and handling dirty data.
Why inject LIMIT instead of rejecting? A missing LIMIT is a generation quality issue, not a safety issue. Injecting it lets good SQL through without burning a retry; the cap prevents unbounded scans of large tables.
In-process dataset registry. Uploaded datasets and their DuckDB
connections live in a dict in one process (app/api/store.py). No
persistence across restarts, no horizontal scaling, and connections are only
freed on explicit DELETE — a TTL evictor and a SQLite/Redis-backed
registry are the obvious next step.
The cache is normalized-exact, not semantic. app/nlsql/cache.py keys on
(dataset_id, whitespace/case-normalized question). "revenue by region" and
"revenue per region" miss each other. The get/put/invalidate interface
is the seam where an embedding-similarity backend would drop in.
Column validation is best-effort. Only qualified table.column
references are checked against the schema; unqualified columns are skipped
(too many false positives from aliases, CTE outputs, and subquery columns).
Hallucinated table names are always caught; a hallucinated unqualified
column name reaches DuckDB and fails there, then feeds the self-correction
loop.
Single provider. Groq-hosted GPT-OSS 120B only. A provider comparison (accuracy/latency/cost on the golden eval) would be a stronger story than an abstraction layer, and is future work.
Eval set is small. 30 golden cases over 6 fixtures. Enough to catch regressions and quote a defensible number; not enough to claim production coverage. Growing it is the highest-value next task.