mcp-analytics-server

🔌 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

Interactive/exportable version: 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

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