# Sales Analytics MCP Server

A **Model Context Protocol (MCP)** server that gives an LLM safe, structured, read-only
access to a sales database. Built with the official MCP Python SDK (**FastMCP**), it
exposes analytics tools that a client like Claude Desktop can call to answer questions like
*"which region has the highest revenue?"* or *"show me the top 5 customers"* — grounded in
real query results, not the model's guesses.

## What MCP is (and why this matters)

MCP is an open protocol that standardizes how LLM applications connect to external tools and
data. A **server** advertises a set of tools; the **client's LLM** decides which to call and
with what arguments; the server executes and returns structured results. Instead of an LLM
hallucinating an answer about your data, it *calls a tool* and answers from what came back.

This server exposes five tools over stdio:

| Tool | What it does |
|---|---|
| `list_tables` | list tables in the database |
| `describe_table` | column names and types for a table |
| `run_sql` | run a single **read-only SELECT** and return rows |
| `revenue_by_region` | pre-built aggregate: orders, revenue, AOV per region |
| `top_customers` | highest lifetime-value customers |

## The decision worth defending: read-only by construction

`run_sql` is the interesting tool, because an MCP tool is invoked by an **LLM**, and an LLM
can be prompt-injected. So the tool must be safe *even if the model is tricked into asking
for something destructive.* The guard rejects anything that isn't a single `SELECT`:

- must start with `SELECT` (or `WITH … SELECT`)
- no statement chaining (a semicolon is rejected outright)
- no write/DDL keywords anywhere (`DROP`, `DELETE`, `UPDATE`, `INSERT`, …)

There are **6 parametrized tests** firing real attack strings (`DROP TABLE customers`,
`SELECT 1; DROP TABLE orders`, …) and asserting every one is blocked. Treating the LLM as
an untrusted caller is the core security posture of exposing tools this way.

## Try it (no LLM required)

```bash
pip install -r requirements.txt
make db        # build the sample SQLite database
make test      # 16 tests, incl. 6 SQL-injection guards
```

## Use it with Claude Desktop

1. `make db` to build the database.
2. Add `src/server.py` to `claude_desktop_config.json` (see
   `claude_desktop_config.example.json` — use an absolute path).
3. Restart Claude Desktop. The tools appear, and you can ask questions like *"what's revenue
   by region?"* and watch the model call `revenue_by_region` and answer from the result.

## Design: pure logic + thin protocol layer

`src/tools.py` holds the tool logic as **plain, dependency-free functions** — so they're
exhaustively unit-testable without a running MCP client. `src/server.py` is a thin FastMCP
wrapper that turns each function into an advertised tool via `@mcp.tool()`, using type hints
and docstrings to build the schema the LLM sees. Separating logic from protocol is what
makes the security guards easy to test in isolation.

## Skills demonstrated

| Skill | Where |
|---|---|
| Model Context Protocol (MCP) | `src/server.py` — FastMCP server, tool registration |
| LLM tool/function calling | the five `@mcp.tool()` functions |
| Prompt-injection–aware security | `run_sql` guards + 6 attack tests |
| SQL | analytical queries, schema introspection |
| Clean architecture / testing | pure logic vs. protocol layer; 16 tests |

## Structure

```
src/build_db.py   sample SQLite sales database
src/tools.py      pure tool logic + SQL safety guards (no MCP dependency)
src/server.py     FastMCP server exposing the tools
tests/            16 tests incl. injection guards
claude_desktop_config.example.json
```

## Note

The database is generated locally (`src/build_db.py`) so the server is self-contained. Point
`tools.DB_PATH` at a real database with the same shape and the server is unchanged. The tools
are read-only by design; adding write tools would mean adding authentication and per-tool
authorization, which is deliberately out of scope for a read-only analytics surface.