# 🔌 MCP Analytics Server: talk to your database through the Model Context Protocol

> An **MCP server** that exposes a SQLite e-commerce database to any MCP client (Claude
> Desktop, Cursor, the Anthropic SDK, …) as **safe, structured, schema-typed tools**, 
> plus a **Claude agent** that answers business questions by calling those tools. Built on
> the standard `mcp` Python SDK (FastMCP). Includes a **read-only SQL guard**, an
> **auth-gated mutation**, and a **schema resource**.

MCP is the 2026 standard for connecting agents to tools and data, 10,000+ servers already
published, native in ChatGPT, Claude, Cursor, Copilot, and VS Code. This repo is a clean,
production-shaped example of the thing teams keep needing: **a governed gateway between an
LLM and a database**, not raw SQL access, but typed tools with guards and authorization.

---


## Architecture

![architecture](docs/assets/architecture.png)

*Interactive/exportable version: [`docs/assets/architecture.html`](docs/assets/architecture.html).*

## What it exposes

| Tool | Kind | Notes |
|---|---|---|
| `list_tables` | read | tables in the database |
| `describe_table(table)` | read | columns, types, primary keys |
| `run_query(sql)` | read | **read-only** ad-hoc SELECT/WITH, single statement, mutation keywords blocked, row-capped |
| `top_products(limit)` | read | best-sellers by units (completed orders) |
| `revenue_summary()` | read | revenue + order/customer counts |
| `create_support_ticket(...)` | **write** | **auth-gated**, requires the write API key |
| `schema://database` | resource | the full SQL schema |

The split is the senior point: a **bash/raw-SQL tool** hands the model unbounded power; these
are **dedicated, typed tools** the host can validate, gate, and audit. `run_query` is
read-only and capped; the only mutation is behind an API key.

---

## Two ways to drive it

```bash
PY=~/miniconda3/envs/personal/bin/python
$PY -m pip install -e ".[all]"

# 1) OFFLINE, exercise the live MCP protocol end-to-end, no API key, no LLM:
$PY -m mcp_analytics.client demo
#   [mcp] connected, 6 tools: list_tables, describe_table, run_query, ...
#   [mcp] revenue_summary -> {"revenue": 184293.5, "orders": 968, "customers": 188}
#   [mcp] top_products(3) -> [{"name": "Product 7", "category": "Books", "units": 142}, ...]
#   [mcp] blocked mutation -> tool error: only SELECT / WITH queries are allowed

# 2) AGENT, let Claude answer a question by calling the tools (needs a key):
export ANTHROPIC_API_KEY=sk-ant-...
$PY -m mcp_analytics.client ask "Which country has the most customers, and what's total revenue?"
```

The server itself runs over **stdio** (`python -m mcp_analytics.server`), point Claude
Desktop / Cursor / any MCP client at that command and the tools appear.

### Use it from Claude Desktop / Cursor

```json
{
  "mcpServers": {
    "analytics": { "command": "python", "args": ["-m", "mcp_analytics.server"] }
  }
}
```

---

## Architecture

```
  MCP client (Claude Desktop · Cursor · Anthropic SDK · this client.py)
        │  JSON-RPC over stdio
        ▼
  FastMCP server  (server.py)  ──tools──►  list_tables · describe_table · run_query
        │                                  top_products · revenue_summary
        │                                  create_support_ticket (auth-gated)
        │  ──resource──►  schema://database
        ▼
  db.py   pure, testable query layer  ──►  SQLite e-commerce DB (customers · products
          read-only guard · auth · seed                          orders · order_items · tickets)
```

Tool *logic* lives in `db.py` (unit-tested without the protocol); `server.py` is the thin
FastMCP adapter. The Claude agent (`client.py`) converts the server's MCP tools to Anthropic
tools via `anthropic.lib.tools.mcp` and runs the tool loop.

---

## Safety & governance

- **Read-only SQL guard**, `run_query` accepts only a single `SELECT`/`WITH`, rejects
  `INSERT/UPDATE/DELETE/DROP/ALTER/ATTACH/PRAGMA/CREATE`, blocks multi-statement injection,
  and caps rows. Executed on a `mode=ro` SQLite connection as defence-in-depth.
- **Auth-gated mutation**, `create_support_ticket` requires the write API key
  (`MCP_WRITE_API_KEY`); every other tool is read-only.
- **Validated inputs**, unknown tables/customers raise typed errors surfaced to the agent.

These guards are the security-critical surface and are covered by the test suite.

---

## Repo layout

```
mcp-analytics-server/
├── src/mcp_analytics/
│   ├── db.py        SQLite schema + seed + pure query layer (read-only guard, auth)
│   ├── server.py    FastMCP server: tools + schema resource (stdio)
│   ├── client.py    MCP client: offline protocol `demo` + Claude `ask` agent
│   └── config.py    paths, write API key, model
├── tests/           db + query-guard + auth tests (key-free) — 11 cases
└── pyproject.toml · Dockerfile · Makefile · .github/workflows/ci.yml
```

---

## Résumé framing

> *Built an MCP (Model Context Protocol) server exposing a database as governed,
> schema-typed tools, read-only SQL guard, auth-gated mutations, and a schema resource, 
> on the standard `mcp` SDK; plus a Claude agent that answers business questions through it.
> Demonstrates the 2026 agent-integration standard end-to-end (stdio transport, tool
> conversion, tool loop).*

## License
MIT (`LICENSE`).