DataPilot

中文说明 · Architecture · Benchmark · Safety · Debug report · Raw benchmark artifacts

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

Verified engineering snapshot

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

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)]

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

Related MCP server: MCP PostgreSQL

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.

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.

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/.

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

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

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:

LLM_BACKEND=openai_compatible
LLM_BASE_URL=https://your-endpoint/v1
LLM_API_KEY=...
LLM_MODEL=...

Local development without Docker

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}.

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

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

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