# mcp-data-analyst

![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue)
![Tests](https://img.shields.io/badge/tests-57%20passed-brightgreen)
![MCP](https://img.shields.io/badge/protocol-MCP-blue)
![License](https://img.shields.io/badge/license-MIT-lightgrey)

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.

```bash
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

```mermaid
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
```