Content
# 🔌 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.
---
## 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`).
