Natural-language analytics agent
Non-technical staff ask questions in plain English. The agent finds the right columns across four internal systems, writes read-only SQL, and repairs only the clause that failed.
- ~98%
- gold-column recall
- 3.2k
- token schema budget
- 400+
- queries a month
Context
The answers non-technical staff needed lived across four internal systems — ERP, MES, project management, and warehouse management — and getting them meant writing SQL.
I built core components of a production analytics agent in Python and FastAPI that turns plain-English questions into safe, read-only queries.
How it works
- 1
Link the schema
Hierarchical retrieval narrows the schema to the columns that matter: hybrid BM25 and pgvector search, then cross-encoder reranking, all inside a 3.2k-token budget.
- 2
Generate SQL
The model writes SQL against the linked schema only.
- 3
Guard
Every query runs through MCP tools behind three read-only layers.
- 4
Repair
When execution fails, a log-driven loop regenerates only the failing clause instead of the whole query.
A run, step by step
Which work orders slipped past their promised ship date last week?
Synthetic example with made-up data. Real runs are traced the same way.
- retrieve_schemaBM25 + pgvector · 41 candidates212 ms
- rerankcross-encoder · kept 6 columns96 ms
- generate_sqlone read-only statement1.38 s
- guard3 read-only layers passed4 ms
- executeerror: unknown column ship_dt31 ms
- repairregenerated the WHERE clause only640 ms
- execute14 rows28 ms
7 steps · 2.39 s · 1 repair
Key decisions
Retrieve the schema, don't stuff it
Four systems are too much schema to put in a prompt. Hierarchical hybrid retrieval with reranking kept about 98% gold-column recall inside a 3.2k-token context budget.
Make unsafe queries impossible, not unlikely
A prompt can be ignored; a tool boundary can't. All SQL goes through MCP tools behind three read-only layers, so a bad generation can't change data.
Repair the clause, not the query
Regenerating a whole query to fix one mistake can break parts that were already right. The repair loop reads the error log and rewrites only the failing clause.
How it shipped
- 600+ tests across both agent systems, run in a 3-stage GitHub Actions pipeline
- Redis-backed circuit breakers shared across services
- OpenTelemetry tracing with Grafana dashboards
- An LLM-as-judge regression harness that runs on every change
Stack
- Python
- FastAPI
- PostgreSQL
- pgvector
- BM25
- Cross-encoder reranking
- MCP
- Redis
- OpenTelemetry
- Grafana