AskData — Natural Language Data Query Agent

Ask your database a question in plain English. Get back the answer, the SQL that produced it, and a self-repair trail if anything went wrong along the way. This is how a Text2SQL agent works!

In research center and enterprise settings, analysts often rely on technical teams to run SQL queries — a bottleneck that slows decision-making. AskData removes this dependency by letting users ask questions in natural language: the system retrieves the relevant schema, plans the query step by step, generates and executes SQL, and validates its own answer before returning it.

How it works

question
   │
   ▼
┌─────────────────────────────────────────────────┐
│ 1. Schema retrieval                             │
│    keyword (BM25) + vector search → RRF fusion  │
│    → cross-encoder rerank                       │
├─────────────────────────────────────────────────┤
│ 2. CoT planning                                 │
│    schema graph + question → structured plan    │
│    (database, objects, instruction, output)     │
├─────────────────────────────────────────────────┤
│ 3. SQL generation & execution                   │
│    plan → SQL (read-only, guarded) → SQLite     │
├─────────────────────────────────────────────────┤
│ 4. Validation & staged repair                   │
│    LLM judge reviews the full trace; on failure │
│    rolls back to the faulty stage and retries   │
└─────────────────────────────────────────────────┘
   │
   ▼
answer + SQL + data table

Tech stack

Backend: Python, FastAPI, LangChain, OpenAI (GPT-4o-mini), ChromaDB, rank-bm25, sentence-transformers (cross-encoder rerank), SQLite

Frontend: React (Vite)

Key design decisions

Hybrid retrieval instead of vector-only. Vector search handles fuzzy phrasing ("how much interest do users pay") but misses exact identifiers; BM25 nails exact table/field names but has no semantic understanding. The two result lists are merged with Reciprocal Rank Fusion — rank-based, so the incompatible score scales of BM25 and cosine similarity never need to be reconciled — then re-ranked by a cross-encoder that reads the query and each candidate field together.

Plan before SQL. The model is never asked to go from question to SQL in one shot. A planning step first decomposes the question into a structured four-tuple (database, objects, instruction, output) against the retrieved schema; SQL generation is then a constrained translation of that plan and is explicitly told not to re-interpret the question. Splitting "understanding" from "translation" shrinks the space in which the model can hallucinate.

The parser, not the vector store, is the source of schema truth. ChromaDB metadata cannot hold nested structures like foreign-key relations, so retrieval results only decide which tables are relevant. The actual table structures and join relations are always re-read from the schema parser — retrieval ranks, the parser states facts. Involved tables are expanded to their full column list so join keys survive even when they didn't rank highly.

Staged repair instead of blind retry. Two independent repair layers: (1) if generated SQL crashes, the DB error is fed back to the generator — "no such column: X" is usually enough for a one-shot fix; (2) an LLM judge then reviews the full trace (question, schema, plan, SQL, result) and can catch answers that executed fine but are wrong. On failure it locates the faulty stage and rolls back only downstream state: a planning fault keeps the retrieval results, an SQL fault keeps retrieval and plan. The judge sees previous repair attempts, so a fix that didn't work escalates the diagnosis upstream instead of repeating itself.

Generated SQL is treated as untrusted input. Defence in depth: the generator rejects anything that isn't a single SELECT/WITH statement, and the executor additionally opens the database in read-only mode, so a write cannot land even if the first check were bypassed. Row counts are capped, and execution errors are returned as data rather than raised — the repair loop consumes them.

Project structure

backend/
├── core/         # shared LLM factory
├── schema/       # DB introspection → schema JSON → ChromaDB indexes
├── retrieval/    # keyword extraction, BM25 + vector hybrid, RRF, rerank
├── cot/          # schema graph assembly, four-tuple planning
├── sql/          # SQL generation (guarded) and read-only execution
├── validation/   # crash-retry loop + LLM judge with staged rollback
├── pipeline.py   # end-to-end CLI entry
└── main.py       # FastAPI layer
frontend/         # React (Vite) query UI
data/             # bundled SQLite demo databases

Run it locally

Requires Python 3.10+ and Node 18+.

# 1. backend setup
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt

# 2. create the demo database and build the schema index
python backend/schema/init_db.py
python backend/schema/parser.py
python backend/schema/indexer.py

# 3. configure your OpenAI key
cp .env.example .env        # then edit .env

# 4. start the API (from the project root)
uvicorn backend.main:app --reload --port 8001

# 5. start the frontend (second terminal)
cd frontend
npm install
npm run dev

Open http://localhost:5173 and ask a question on our mock database, e.g. "Which users have interest rate higher than 3.5?"

You can also skip the UI entirely:

python backend/pipeline.py "What is the average interest rate?"

Future development

The reference design this project is based on includes several capabilities not yet implemented here, roughly in planned order:

Evaluation harness. A small benchmark set (~20–30 questions with expected result sets) against the bundled databases, so accuracy claims are measured on this implementation rather than inherited from the reference design.

Multi-database support. Currently all queries run against a single SQLite database. The next step is schema-level database routing: the CoT planner decides which database each step belongs to, queries are decomposed into per-database steps, and intermediate results are merged — the four-tuple's database slot already carries the information this needs.

MCP as the data access layer. Replace the direct SQLite executor with MCP (Model Context Protocol) servers, one per database, so the agent only produces {database, sql} pairs and never touches connection details. This decouples the model from database types and makes adding a new database a registration step instead of a code change.

Dynamic intent routing. Not every message needs the full Text2SQL pipeline. An intent classifier will route data questions to the query chain and follow-ups ("what does this number mean?") to a plain conversational path, cutting latency and cost for non-query turns.

Conversation memory. Short-term: a sliding window over recent turns with async summarisation of older context, so multi-turn sessions stay coherent without unbounded prompts. Long-term: user-curated saving of query results and conclusions into a vector store, recalled via RAG in later sessions so repeated questions don't re-run the whole pipeline.

LLM-based keyword extraction. The current rule-based extractor is free and sufficient for the demo schema; swapping in the existing LLM path would handle synonyms and business aliases ("churn" → attrition_rate) on larger, messier schemas.