🔌 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

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

{
  "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).