mcp-data-analyst

A natural-language data analyst: ask a question about a sales dataset in plain English, and Claude answers it by calling tools over a real MCP (Model Context Protocol) server -- not a hand-rolled function-calling shim, the actual protocol, including a real subprocess speaking real stdio JSON-RPC in the test suite -- then a FastAPI backend renders the result as a chart. 57 tests, all against real infrastructure: a real SQLite database, a real MCP client/server pair (in-process and across a real process boundary), and a real FastAPI app. The one thing that isn't real by default is the LLM call itself, and that's a deliberate, clearly-labeled choice -- see "Demo mode" below.

pip install -r requirements.txt
python3 data/generate_dataset.py          # regenerates data/sales.db (seeded, deterministic)
pytest -q                                  # 57 tests, <1s
export ANTHROPIC_API_KEY=sk-...            # optional -- omit it and the app runs in demo mode
python3 -m uvicorn api.app:app --port 8080
open http://localhost:8080

Why this exists

MCP is the protocol for connecting a model to tools and data sources -- increasingly the standard way real agentic products wire an LLM up to anything beyond its own training data. Most demos of it stop at "define a tool, watch the model call it." This project pushes on the parts that actually matter in a real deployment: a tool surface that's genuinely safe to hand an LLM raw-string access to (the SQL safety validator), a protocol boundary tested for real rather than assumed to work (a real subprocess over real stdio, not just an in-process shortcut), and an orchestration loop whose logic -- not just its happy path -- is unit-tested: multi-turn tool use, parallel tool calls in one turn, tool errors fed back to the model and recovered from, and a hard turn limit so a confused model can't loop forever.

Architecture

flowchart LR
    subgraph Dashboard["Static dashboard (HTML/JS + Chart.js)"]
        UI["question box + chart"]
    end

    subgraph API["FastAPI (api/app.py)"]
        Ask["POST /api/ask"]
        Tools["GET /api/tools"]
    end

    subgraph Agent["agent/claude_agent.py"]
        Loop["tool-calling loop\n(multi-turn, until final answer)"]
        Chart["chart extraction\n(aggregate/time_series results)"]
    end

    Claude["Claude API\n(or demo_llm.py fallback)"]

    subgraph MCPServer["mcp_server/ (real MCP server)"]
        SQLSafety["sql_safety.py\nread-only guard"]
        AnalyticsTools["list_tables / describe_table /\nrun_sql / aggregate / time_series"]
    end

    DB[("SQLite\nsynthetic sales dataset")]

    UI -->|fetch| Ask --> Loop
    Loop <-->|messages + tools| Claude
    Loop -->|MCP protocol\n(stdio or in-process)| AnalyticsTools
    AnalyticsTools --> SQLSafety
    AnalyticsTools --> DB
    Loop --> Chart --> Ask
    Tools -->|list_tools| AnalyticsTools

The MCP tools

Tool	Purpose
list_tables	Discover the schema
describe_table	Columns + row count for one table
aggregate	Single-table group-by (e.g. order count by status) -- arguments validated against the real schema, not interpolated raw
time_series	Bucket a date column into day/week/month, aggregating a value column
run_sql	Anything else -- a real SQL string from the model, gated by sql_safety.py

aggregate and time_series are deliberately narrow: their table, group_by, and metric arguments are checked against the table's real columns before touching SQL, so there's no injection surface there at all. run_sql is the one tool that takes an arbitrary string, so it's the one with an actual safety boundary: single statement only, no SQL comments, SELECT/WITH only, every identifier-shaped token checked against a write/DDL/pragma blacklist (catching WITH x AS (...) INSERT INTO ... -- valid SQL that starts with a CTE but ends in a write), and an automatic row cap. 18 tests cover this directly, including that exact CTE-smuggled-write case.

Demo mode, and why it exists

Without ANTHROPIC_API_KEY set, /api/ask still runs the entire real pipeline -- the real MCP server, the real SQL safety guard, the real chart extraction -- but with a small rule-based stand-in for the LLM (agent/demo_llm.py) picking from three canned question patterns instead of a live Claude call. Every demo response is labeled "mode": "demo" in the JSON and [demo mode] in its own text; it never pretends to be a real answer. This exists for an honest reason: spending someone else's (or this project's own CI's) API credits automatically isn't something to do without asking, so the whole pipeline needed to be exercisable, verifiably, without one. All three canned question patterns were run against the real running app during development -- the real dataset, the real MCP protocol, the real chart rendering, everything except the model itself -- confirming the bar chart for order-status breakdown, the line chart for the monthly order-volume trend (which visibly shows the seasonal ramp built into the dataset generator), and the correctly-chartless run_sql join result for revenue-by-region all render correctly end to end.

Test suite

pytest -q
# 57 passed in <1s

File	Covers
test_sql_safety.py	18 tests: write/DDL/pragma rejection, CTE-smuggled writes, comment stripping, LIMIT capping
test_tools.py	18 tests: aggregate/time_series/run_sql against a real SQLite connection, including an injection attempt in group_by
test_mcp_server.py	7 tests: tool discovery and execution over the real MCP protocol -- including one real subprocess over real stdio
test_agent.py	7 tests: the Claude tool-calling loop against a real MCP client, with a scripted-but-shape-accurate fake LLM -- multi-turn, parallel tool calls, error recovery, turn-limit enforcement
test_api.py	7 tests: FastAPI endpoints, including the demo-mode fallback path

The dataset

data/generate_dataset.py produces a synthetic e-commerce dataset (customers, products, orders) from a fixed seed -- explicitly synthetic, not sourced from any real company, and reproducible: every number in this README derived from it (5,329 completed orders, the regional revenue skew, the seasonal order-volume ramp) comes from running that exact script with its default seed.

Model boundaries

• The LLM call is the one thing not exercised for real by default -- see "Demo mode" above. Set ANTHROPIC_API_KEY to use the actual Claude API; the agent loop itself is identical either way.
• aggregate/time_series are single-table only. A question needing a join (e.g. revenue by region, which joins orders, customers, and products) goes through run_sql instead, and correspondingly doesn't get an automatic chart -- chart extraction is only wired for the two tools with a predictable group/value or period/value shape. run_sql results render as data, not a chart, honestly.
• No conversation memory. Each /api/ask call is a fresh conversation; there's no session state carrying context between questions.
• SQLite, not a production warehouse. The schema and tools would port to Postgres/DuckDB with small changes; SQLite was chosen so the whole project runs with zero external services.

Repository layout

data/
  generate_dataset.py    seeded synthetic dataset generator
mcp_server/
  sql_safety.py            read-only SQL guard
  tools.py                   analytics logic (independent of MCP plumbing)
  server.py                    wires tools.py as real MCP tools over stdio
agent/
  claude_agent.py         the tool-calling loop + chart extraction
  demo_llm.py                the honest, labeled no-API-key fallback
api/
  app.py                  FastAPI: /api/ask, /api/tools, /api/health
  static/index.html         the dashboard (vanilla JS + Chart.js)
tests/                    57 tests across all of the above