AskData MCP Server
README.md
# AskData: a semantic-layer text-to-SQL agent with guardrails, evals and an MCP server
[](https://github.com/Shibukedang27/askdata-semantic-agent/actions/workflows/ci.yml)
Ask a sales database questions in plain English ("Top 3 cities by revenue in 2025") and get back an answer,
the SQL that produced it, a chart and a step-by-step trace. It runs **fully offline with no API key**.
An LLM planner (OpenAI or a local Ollama model) is optional.
I built this as a final-year B.Tech (AI & ML) project to learn how "AI for analytics" works in practice:
how to stop an LLM from writing SQL that is plausible but wrong, how to keep an agent read-only,
and how to measure all of it with evals instead of eyeballing outputs.
## The problem
Text-to-SQL demos look great until you check the numbers. A model that writes raw SQL against raw tables can
silently use the wrong definition of revenue (cancelled orders? discounts?), make up columns, or run a
`DELETE`. Recent write-ups (LinkedIn Engineering's SQL Bot, dbt's 2026 semantic-layer benchmark) point the
same way: **let the model choose governed metrics and dimensions, and let deterministic code write the SQL.**
AskData is a small, end-to-end version of that idea:
1. **Semantic layer** (`askdata/semantic_layer.yaml`): 6 governed metrics (revenue, orders, AOV, units,
unique customers, return rate) and 8 dimensions, each defined once.
2. **Planner**: turns the question into a small JSON `SemanticQuery` (names only, never SQL).
The default is an offline rule-based planner. An LLM planner can be switched on with an env var.
3. **Compiler**: deterministic `SemanticQuery → SQL`, with values passed as bound parameters.
4. **Guardrails** in three layers: a write-intent check before any planner, a static SQL checker, and a
read-only SQLite connection with an authorizer callback.
5. **Evals**: 47 golden questions with hand-written gold SQL, plus a 20-query SQL attack suite. CI runs them on every push.
6. **MCP server**: exposes the agent as tools (`ask`, `run_sql`, `list_metrics`, `describe_schema`) for any MCP client.
## Architecture
```mermaid
flowchart LR
U["User question"] --> I{"Write-intent check"}
I -- "delete / drop / update" --> R["Refuse"]
I -- "read-only" --> P["Planner: rule-based (default)<br/>or LLM via OpenAI / Ollama"]
SL[("semantic_layer.yaml<br/>metrics + dimensions")] --> P
P -- "SemanticQuery JSON, names only" --> V["Validate against semantic layer"]
V -- "invalid" --> F["Fallback to rule-based or refuse"]
V --> C["Deterministic compiler<br/>SQL + bound params"]
C --> G["Static SQL guardrail<br/>SELECT-only, table allowlist"]
G --> X[("SQLite read-only<br/>mode=ro + authorizer + row cap")]
X --> S["Template summarizer<br/>no invented numbers"]
S --> O["Answer + SQL + table + chart + trace"]
O --> CLI["CLI"]
O --> ST["Streamlit app"]
O --> MCP["MCP server"]
E["Eval harness<br/>47 golden Qs + 20 SQL attacks"] -. "runs in CI" .-> P
```
## Features
- **Governed metrics.** Revenue always means net of discount, completed orders only. The definition lives in one YAML file.
- **No SQL from the model.** The planner outputs JSON (`metrics`, `dimensions`, `filters`, dates, `order_by`, `limit`), and code writes the SQL.
- **Defense-in-depth guardrails.** Write intent is refused before planning. The static checker blocks multiple statements, DDL/DML, `ATTACH`, `PRAGMA`, `load_extension`, system tables and unknown tables. The SQLite connection is opened `mode=ro` with `PRAGMA query_only` and an authorizer that denies anything except reads. Results are capped at 200 rows.
- **Injection-safe filters.** Filter values are bound parameters (there's a test that passes `Hyderabad' OR 1=1 --` as a city name).
- **Optional LLM, works offline.** With `OPENAI_API_KEY` or `OPENAI_BASE_URL` set, the LLM planner is used. Invalid LLM output (unknown metric, bad JSON, network error) falls back to the rule-based planner, and the trace records why.
- **Observability.** Every answer carries a step trace (`intent_check → plan → compile → guardrail → execute → summarize`) with timings.
- **Eval harness.** Execution accuracy against hand-written gold SQL, split into *dev* (used while building) and *holdout* (written up front, never tuned on), plus refusal accuracy, a guardrail attack suite and latency percentiles.
- **Three interfaces.** CLI, Streamlit app and MCP server, all built on the same `AskDataAgent`.
## Quickstart
```bash
git clone https://github.com/Shibukedang27/askdata-semantic-agent.git
cd askdata-semantic-agent
python -m venv .venv && source .venv/bin/activate
pip install -r requirements-dev.txt
python -m askdata ask "Top 3 cities by revenue in 2025" # builds data/shop.db from the CSVs on first run
python -m askdata eval # golden evals + guardrail suite
python -m pytest # test suite
streamlit run app/streamlit_app.py # web demo
python -m askdata mcp # MCP server over stdio
```
### Optional: use an LLM planner
```bash
# OpenAI
export OPENAI_API_KEY=sk-... # never commit this; see .env.example
export ASKDATA_LLM_MODEL=gpt-4o-mini
# or a local model with Ollama (no key, nothing leaves your machine)
export OPENAI_BASE_URL=http://localhost:11434/v1
export ASKDATA_LLM_MODEL=qwen2.5:3b
```
`ASKDATA_PLANNER=rule` forces the offline planner (CI does this).
### Use it from an MCP client
Add this to your MCP client config (Claude Desktop, Cursor, VS Code and others):
```json
{
"mcpServers": {
"askdata": {
"command": "/path/to/askdata-semantic-agent/.venv/bin/python",
"args": ["-m", "askdata.mcp_server"],
"cwd": "/path/to/askdata-semantic-agent"
}
}
}
```
Tools: `ask(question)`, `run_sql(sql)` (guarded), `list_metrics()`, `describe_schema()`.
## Demo output (real terminal output)
```text
$ python -m askdata ask "Top 3 cities by revenue in 2025"
Q: Top 3 cities by revenue in 2025
Status: ok | planner: rule-based | 9.2 ms
A: Top 3 by revenue (city) (2025-01-01 to 2025-12-31): Hyderabad (₹3,857,858), Bengaluru (₹3,070,323), Mumbai (₹2,018,628).
SQL:
SELECT c.city AS city, SUM(CASE WHEN o.status = 'completed' THEN oi.quantity * oi.unit_price * (1 - oi.discount) ELSE 0 END) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01' AND o.order_date <= '2025-12-31'
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 3
city revenue
Hyderabad 3857857.65
Bengaluru 3070323.35
Mumbai 2018627.75
Trace: intent_check 0.09ms -> plan 3.43ms -> compile 0.01ms -> guardrail 0.05ms -> execute 4.97ms -> summarize 0.61ms
$ python -m askdata sql "SELECT * FROM sqlite_master"
Q: SELECT * FROM sqlite_master
Status: refused | planner: raw-sql | 0.0 ms
A: Blocked by guardrail: system tables are not allowed
$ python -m askdata eval
planner: rule-based
dev execution accuracy: 28/28 = 100.0%
holdout execution accuracy: 13/19 = 68.4%
by category: simple 1/1, time_filter 3/3, group_by 7/7, time_series 6/6, top_n 8/8, filter 8/9, multi_metric 1/1, refusal 7/7, paraphrase 0/5
latency per question: p50 3.92 ms, p95 8.39 ms
failed: h07, h10, h11, h12, h13, h15
guardrails: 15/15 attacks blocked, 5/5 benign queries allowed; DB layer alone blocks 14/15
```
| Top-N question | Write request refused |
|---|---|
|  |  |
## Results (measured, `python -m askdata eval`)
All numbers come from running this repo on my machine (16 Oct 2026). Raw per-question outputs are in
[`evals/results.json`](evals/results.json) (rule-based) and
[`evals/results_llm_qwen2.5-3b.json`](evals/results_llm_qwen2.5-3b.json) (local LLM).
**Execution accuracy** means the agent's result rows match the result of the hand-written gold SQL
(floats rounded to 2 decimals; row order checked only for top-N and time-series questions).
| Planner | Dev (28) | Holdout (19) | Refusals (7) | Latency p50 / p95 |
|---|---|---|---|---|
| Rule-based (offline, default) | **28/28 (100%)** | **13/19 (68.4%)** | 7/7 | 3.8 / 8.0 ms |
| LLM: Qwen2.5-3B-Instruct Q4 via Ollama, CPU only | 17/28 (60.7%) | 5/19 (26.3%) | 6/7 | 8.2 / 11.0 s |
The LLM row was measured on an 8-vCPU machine with no GPU, using the prompt in `askdata/planners/llm.py` as-is (no prompt
tuning on the eval set). In that run, 2 of the 47 questions fell back to the rule planner because the LLM's JSON failed validation,
and "Delete all orders from 2024" was refused by the intent check before the LLM was called.
| Guardrail suite | Result |
|---|---|
| Attack queries blocked (all layers) | **15/15** |
| Benign queries allowed (e.g. `LIKE '%drop%'`) | **5/5** |
| Attacks blocked by the read-only DB layer alone | 14/15 (the one it lets through is `SELECT * FROM sqlite_master`, which is a read; the static layer blocks it) |
(Latency numbers move by a millisecond or two between runs. The table uses `evals/results.json`.)
### What I learned from the numbers
- **Dev at 100% doesn't mean the system is good.** I iterated on the dev questions, so they overfit by design.
The holdout set (written before the planner, never used to tune it) shows the real picture: 68%.
All 5 paraphrase questions fail ("make the most money", "share of orders got returned", "how many books did students buy").
"Revenue per weekday" is worse: there's no weekday dimension, so the rule planner silently drops it and returns the total
instead of refusing.
- **A small local LLM was not a drop-in fix.** Qwen2.5-3B on CPU was about 2,000× slower (8.2 s vs 3.8 ms median) and less accurate than the rule
planner on this set. Most failures were about the *shape* of the query: grouping by a dimension it was only meant to filter on,
using "Q4" as a filter value, or adding extra columns. Without the semantic layer, those mistakes would have been invented SQL.
With it, they are at least valid, inspectable queries.
- **Evals caught a safety bug.** On the first LLM run, the model answered "Delete all orders from 2024" with an order count
instead of refusing. The DB was read-only so nothing happened, but I moved the write-intent check *before* every planner
and added a regression test (`test_write_intent_refused_before_llm_is_called`). The same run also surfaced a crash
on `null` filter values from the LLM, which is now handled in `SemanticQuery.from_dict`.
## Dataset
`data/*.csv` is a **synthetic** retail dataset made by `askdata/data_gen.py` (seed 27): 800 customers in 6 Indian
cities, 32 products in 5 categories, 6,107 orders and 9,136 order lines from Jan 2024 to Dec 2025. It has
festive-season seasonality and discounts. None of it is real company or customer data.
`python -m askdata build-db` regenerates it.
## Project structure
```
askdata/
semantic_layer.yaml # governed metrics + dimensions
semantic.py # SemanticModel, SemanticQuery (+ validation of untrusted JSON)
planners/ # rule_based.py (offline), llm.py (OpenAI/Ollama), get_planner()
compiler.py # SemanticQuery -> parameterised SQL
guardrails.py # static SQL checks
executor.py # read-only SQLite + authorizer + row cap
agent.py # orchestration + trace
summarize.py # template answers
evals.py # golden + guardrail evals
mcp_server.py # MCP tools
cli.py
app/streamlit_app.py # demo UI
evals/ # golden.yaml, sql_attacks.yaml, results*.json
scripts/ # make_charts.py, screenshots.py
tests/ # pytest suite (69 tests)
```
## Tech stack
Python 3.11+, SQLite, pandas, PyYAML, the MCP Python SDK (v2), the OpenAI Python SDK (optional, also used for
Ollama's OpenAI-compatible API), Streamlit, matplotlib, pytest, Playwright (screenshots only) and GitHub Actions.
## Testing and CI
`python -m pytest` runs 69 tests covering guardrails (every attack in the suite), the read-only executor,
compiler and validation, the rule planner, the LLM planner (with a fake client, so no network), the agent
(totals cross-checked against an independent pandas calculation from the CSVs), the MCP tools, the CLI
and data generation. The GitHub Actions workflow runs the tests on Python 3.11 and 3.12, then an **eval
gate**: the build fails if dev-split accuracy drops below 100% or any refusal case regresses.
## Limitations and future work
- **Paraphrases.** The rule planner is keyword-based. Next I want a hybrid router: rule planner first, LLM only
when it can't map a metric, plus a better prompt with few-shot examples, measured on the same holdout.
- **Unknown dimensions should refuse.** "Revenue per weekday" should say "weekday isn't a dimension"
instead of answering something else.
- **Bigger and stronger models.** Re-run the LLM column with a hosted model and with larger local models, and report cost per question.
- **Period-over-period metrics** (growth %, YoY) in the semantic layer.
- **OpenTelemetry export** of the trace, so runs can be viewed in Langfuse, Phoenix and similar tools.
- **More data backends** (DuckDB, Postgres) behind the same compiler.
## License
MIT, see [LICENSE](LICENSE).
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues