DataPilot
by Leo-Ho-d2l
README.md
# DataPilot
[](https://github.com/Leo-Ho-d2l/DataPilot/actions/workflows/ci.yml)
[中文说明](README.zh-CN.md) · [Architecture](#architecture) · [Benchmark](#benchmark) · [Safety](#safety-and-reliability) · [Debug report](DEBUG_REPORT.md) · [Raw benchmark artifacts](artifacts/benchmark/)
> **Governed data-analysis agent where the model never gets direct database or arbitrary Python execution access.**
> `Semantic Linking → Planning → Text2SQL → sqlglot Guard → MCP Execution → Bounded Self-Repair → Controlled Analysis`
### 30-second overview
| | |
|---|---|
| **Problem** | Natural-language analytics needs business-semantic mapping, safe SQL execution, protocolized tools and recoverable failures. |
| **System** | LangGraph workflow + governed semantic catalog + guarded Text2SQL + real MCP client/server + PostgreSQL read-only execution. |
| **Safety evidence** | **41/41** dangerous SQL blocked, **17/17** valid queries allowed, and **8/8** writes rejected even with the AST guard bypassed. |
| **Evaluation evidence** | 36-case executable benchmark, real MCP transports, committed raw artifacts and **130 automated tests**. |
### Verified engineering snapshot
| Signal | Verified result |
|---|---|
| Automated tests | **130 passed** + `ruff check .` |
| SQL safety | **41/41 dangerous blocked, 17/17 valid allowed; 0 FN / 0 FP** |
| DB defense in depth | Validator bypassed: **8/8 writes rejected** by read-only transaction |
| Text2SQL finding | Generic result-shape contract moved baseline **0.4167 → 0.8889** |
| MCP boundary | Real client→server calls: **4 tools + 2 resources**, in-process and Streamable HTTP |
| Full stack | Docker Compose + PostgreSQL + Redis + real LLM verified |
> **Benchmark interpretation:** the **0.4167 → 0.8889** accuracy change is attributable to a generic result-shape contract on the baseline. The full agentic workflow is **not** claimed to improve accuracy on this 36-case benchmark; its measured value is governance, safety, auditability, context reduction and recoverability.
## Architecture
```mermaid
flowchart LR
U[Browser / API] --> API[FastAPI]
API --> G[LangGraph Data Agent]
G --> LINK[Semantic Schema Linker]
LINK --> CAT[Governed YAML Catalog]
G --> LLM[OpenAI-compatible LLM]
G --> MCP[MCP Client]
MCP --> MS[MCP Analytics Server]
MS --> SQL[sqlglot AST Guard]
SQL --> PG[(PostgreSQL, read-only txn)]
MS --> RS[(Redis Result Store)]
MS --> PY[Allowlisted pandas analysis]
G --> RUNS[(Agent Runs / Tool Events / Query Audit)]
```
```text
Question
-> schema link top tables + metrics + joins from the semantic catalog
-> plan sql_only | sql_then_python
-> generate SQL
-> AST validate sqlglot guard
| invalid -> repair -> validate (bounded)
v
-> MCP query_database
| DB error -> repair -> validate (bounded)
v
-> optional MCP analyze_result
-> answer grounded in the returned rows
```
## Technical highlights
- **Semantic layer with measurable effect** — `catalog.yaml` carries table and
column descriptions, legal joins, synonyms and governed metric formulas.
Linking is scored like retrieval (token rarity, field weighting), and the
benchmark reports expected-table recall, so "we added a semantic layer" is a
number rather than a claim.
- **Guarded Text2SQL** — sqlglot AST validation, blocked internal and system
tables, denied session/file functions, a defensive LIMIT, a `READ ONLY`
transaction, a statement timeout, a row cap and a `query_audit` record of every
allowed and rejected statement.
- **Bounded self-repair** — validation and execution errors are returned to the
model as structured feedback, every repaired query re-enters the full
validation path, and attempts are capped by `AGENT_MAX_SQL_ATTEMPTS`.
- **Real MCP** — the agent reaches the database only through
`query_database` / `analyze_result` on an MCP server (official Python SDK v2),
with four tools and two resources. The same server runs over Streamable HTTP.
- **No arbitrary code execution** — a second-stage analysis is an allowlisted
operation (`summary`, `top_contributors`, `trend`, `correlation`) applied to a
stored `result_id`, not LLM-generated Python.
- **Evaluation with a real ablation** — a baseline arm and a full arm scored by
the same code, with every failure classified by cause.
## Safety and reliability
The model is treated as untrusted.
| Layer | Mechanism | Verified by |
|---|---|---|
| Statement shape | one statement, SELECT/CTE only, no comments | 41 malicious cases, 0 false negatives |
| Object policy | internal tables, `pg_catalog`, `information_schema` blocked | 58-case safety regression |
| Function policy | `pg_sleep`, `set_config`, `pg_read_file`, advisory locks denied | same corpus |
| Transaction | `SET TRANSACTION READ ONLY` | 8/8 writes blocked with the validator bypassed |
| Resource limits | `statement_timeout`, row cap, defensive LIMIT, truncation flag | timeout fired at 5006 ms against a 5000 ms budget |
| Audit | every allowed/blocked query written to `query_audit` | one row per call |
| Failure containment | structured errors returned, never raised to the API | invalid column/table/syntax paths |
The database boundary is independent of the AST guard: the read-only transaction
was verified by sending INSERT/UPDATE/DELETE/TRUNCATE/CREATE/DROP/ALTER and
`SELECT ... INTO` straight to PostgreSQL while bypassing the validator entirely.
## Benchmark
36 business questions over a deterministic synthetic Northstar Commerce warehouse
(customers, products, orders, order items, refunds, web sessions, marketing spend,
support tickets; 43k rows). Scoring runs the generated SQL and the reference SQL
against the same database and compares result sets.
```bash
python -m eval.run_baseline # full schema -> LLM -> guard -> database
python -m eval.run_full # semantic link -> plan -> guard -> MCP -> repair
python -m eval.run_ablation # baseline vs full, by category
python -m eval.run_safety # guard regression, malicious + valid
python -m eval.run_cache # cold vs warm guarded-query latency
```
Raw per-case records, CSV and a summary are written to `artifacts/benchmark/`,
including the model, temperature, catalog version, retry budget, timeout and
dataset hash for the run. Both arms run with the query cache disabled.
### Results
Every arm runs against the same seeded warehouse, the same model
(`deepseek-flash`), the same `temperature=0`, the same SQL guard and the same SQL
prompt. Raw per-case records are in `artifacts/benchmark/`.
| arm | execution accuracy | valid SQL | expected-table recall | expected-tool recall | avg SQL attempts | p50 latency |
|---|---|---|---|---|---|---|
| baseline, minimal prompt | 0.4167 | 1.00 | — | — | 1.00 | 1488 ms |
| baseline, with the result-shape contract | **0.8889** | 1.00 | — | — | 1.00 | 1626 ms |
| full system, original code | 0.6389 | 1.00 | 0.9722 | 0.9444 | 1.06 | 6328 ms |
| full system, current code | 0.8611 | 1.00 | 1.0000 | 0.9444 | 1.00 | 5797 ms |
### What the numbers actually say
**The biggest accuracy lever was the output contract, not the agent.** Specifying
the result shape — return the ranked breakdown, project only the asked-for
measure, cast date buckets to `::date` — moved the *baseline* from 0.4167 to
0.8889. Its failures were overwhelmingly shape, not computation: of 21 baseline
failures with the minimal prompt, 11 returned extra supporting measures and 9
returned `LIMIT 1` where the question wanted every group.
**On this warehouse, the semantic layer and the agentic loop did not add
accuracy.** With the same prompt, the full system scores 0.8611 against the
baseline's 0.8889 — one case apart, and the benchmark's noise floor is ±3 cases
(measured, see below). Both arms now fail on the *same four questions*: three
where the reference rounds to 2 decimals and neither arm does, and one
(`refund_category`) that needs proportional refund allocation across line items.
That is the honest result, and it is reported rather than tuned away.
What the agentic layers do buy, and what the benchmark measures separately:
- **Governance** — the model sees governed metric definitions with fanout
warnings instead of raw DDL, and legal joins only.
- **Safety** — every query passes an AST guard and runs in a read-only
transaction; the baseline path has no audit trail.
- **Prompt size** — the linked prompt is 1977 characters against a 3862-character
full schema, and the gap grows with the warehouse. On nine tables the full
schema still fits; on ninety it would not.
- **Recoverability** — structured repair fired on 2 of 36 cases in the original
configuration and recovered one.
- **Cost of all that** — 3–4 model calls per question instead of one, so p50
latency is roughly 3.5× the baseline's.
### How much of this is noise
An earlier experiment compared two full-system arms whose linked tables were
**byte-identical** for three of the seven cases that changed verdict. That puts
the run-to-run noise floor of this 36-question benchmark at roughly **±3 cases
(±8 accuracy points)** even at `temperature=0`.
So the 0.4167 → 0.8889 contract effect is far outside the noise, and the
0.8889 vs 0.8611 difference between the baseline and the full system is inside it.
The correct reading is **"no measurable difference"**, not "the agent is worse".
Settling it would need a larger benchmark and a larger warehouse.
### Reading the numbers
- Execution accuracy is an exact result-set comparison: same rows, same values.
Projection shape counts, because a BI consumer reads the table directly.
- The reference SQL is used by the evaluator only. `app/` never imports
`eval/`, there is no few-shot mechanism, and no prompt contains a benchmark
query — enforced by `tests/test_eval_assets.py`.
## Demo
```bash
curl -s -X POST http://localhost:8000/v1/analyze \
-H 'Content-Type: application/json' \
-d '{"question":"Which channel has the highest refund amount rate relative to GMV?"}'
```
The response carries the answer, the SQL, the linked tables and metrics, the
plan, the rows, an optional chart, the node trace and the latency. Every run is
also persisted (`agent_runs`, `tool_events`) and replayable through
`GET /v1/runs/{run_id}`.
## Quick start
```bash
cp .env.example .env
docker compose up --build
docker compose exec api python scripts/seed_demo.py
```
Open `http://localhost:8000` for the demo UI and `http://localhost:8000/docs` for
OpenAPI. `GET /health` reports the database and Redis separately.
`LLM_BACKEND=mock` exercises the whole service without a key. For real answers and
a meaningful benchmark, point it at any OpenAI-compatible endpoint:
```env
LLM_BACKEND=openai_compatible
LLM_BASE_URL=https://your-endpoint/v1
LLM_API_KEY=...
LLM_MODEL=...
```
### Local development without Docker
```bash
docker compose up -d postgres redis # infrastructure only
python -m pip install -e ".[dev]"
alembic upgrade head
python scripts/seed_demo.py
python -m app.serve # http://localhost:8000
```
Set `DATABASE_URL` and `REDIS_URL` to `localhost` in `.env` for this path.
On Windows use `python -m app.serve` rather than `uvicorn app.main:app`: uvicorn
0.36+ hands `asyncio.run` an explicit loop factory that hardcodes
`ProactorEventLoop`, which psycopg's async driver rejects. See `DEBUG_REPORT.md`.
## MCP server
Four tools: `get_schema_context`, `get_metric_definition`, `query_database`,
`analyze_result`. Two resources: `catalog://metrics`, `catalog://tables/{name}`.
```bash
python scripts/inspect_mcp.py # in-process client, tool discovery + a call
python -m app.mcp.server # Streamable HTTP on http://127.0.0.1:8001/mcp
```
The agent uses the in-process client by default because everything runs in one
service; the HTTP transport is there for external clients and is verified in
`DEBUG_REPORT.md`.
## Safe Python analysis instead of arbitrary code execution
A common data-agent demo lets the model emit Python and `exec()` it. DataPilot
does not: `analyze_result(result_id, operation, params)` applies one of four
allowlisted pandas operations to a previously stored result. There is no `exec`,
`eval`, `subprocess` or `pickle` anywhere in `app/`. The trade-off is deliberate —
less expressive than free-form code, but the API process never becomes a
code-execution sandbox.
## Repository layout
```text
DataPilot/
├── app/
│ ├── agent/ # LangGraph state machine and prompts
│ ├── analysis/ # allowlisted pandas operations
│ ├── api/ # FastAPI routes
│ ├── catalog/ # semantic catalog + schema linker
│ ├── core/ # settings, auth, logging, event-loop compatibility
│ ├── db/ # warehouse + run/audit models
│ ├── llm/ # OpenAI-compatible and mock clients
│ ├── mcp/ # MCP v2 server and client gateway
│ ├── services/ # run persistence and result/cache store
│ ├── sql/ # AST guard and guarded executor
│ └── web/ # demo UI
├── artifacts/benchmark # committed raw benchmark runs
├── eval/ # 36-case benchmark, safety corpus, bad-case classifier
├── sample_data/ # deterministic synthetic warehouse
├── scripts/ # seed, ask, MCP inspection
└── tests/ # unit + database-backed integration + agent E2E
```
## Tests
```bash
pytest # 130 tests
ruff check .
```
Unit tests cover the guard, the linker, metric resolution, result comparison,
analysis operations and the event-loop shim. Integration tests run against real
PostgreSQL, Redis and MCP: guarded execution (read-only, timeout, row cap, audit),
the four MCP tools and two resources over a real client, the full agent workflow
with a stubbed LLM, and the integrity of the benchmark assets. Tests force
`LLM_BACKEND=mock` so a run never depends on a remote model.
## Design decisions
**Why a semantic catalog rather than the database schema?** Raw DDL names columns
but not their business meaning. The catalog adds descriptions, legal joins, metric
formulas and fanout warnings, and gives the linker something to narrow. It also
produces the `expected_tables` signal the benchmark scores against.
**Why MCP instead of plain function calls?** It draws the boundary where a real
deployment would put it: the agent can only do what the tool schema permits, the
tools can move to another process without touching the agent, and tool arguments
and results are validated at the protocol edge.
**Why not let the model write pandas?** Executing model-authored Python in the API
process is an avoidable remote-code-execution surface for a capability that four
allowlisted operations already cover.
**Why keep the baseline arm in the repository?** A single accuracy number says
nothing about which component earned it. The baseline is scored by the same code
as the full workflow, so the delta is attributable.
## Known limitations
- The benchmark is exact-match. A correct answer phrased with an extra supporting
column is scored as a miss. The failure classifier reports shape mismatches
separately so this stays visible rather than hidden inside one accuracy number.
- The catalog's join graph is small and hand-written; the linker's selection
precision is bounded by it.
- Reference SQL is trusted input. It is validated by the same guard before being
executed, but it is not adversarially reviewed.
- The development database user owns the warehouse tables. A hardened deployment
should use a separate SELECT-only role; the project keeps one user so that a
fresh clone boots with one command.
## References used as architectural study material
DataPilot is an independent implementation, not a fork.
- DB-GPT — data-centric LLM app framework: https://github.com/eosphoros-ai/DB-GPT
- WrenAI — semantic-layer-based text-to-SQL: https://github.com/Canner/WrenAI
- Model Context Protocol — specification and Python SDK: https://github.com/modelcontextprotocol/python-sdk
- sqlglot — SQL parser and transpiler: https://github.com/tobymao/sqlglot
- LangGraph — stateful agent orchestration: https://github.com/langchain-ai/langgraph
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues