# Sales Analytics MCP Server

A custom **Model Context Protocol (MCP)** server that gives Claude Desktop direct, safe access to a live sales database — turning a Python analytics backend into a reusable tool any MCP-compatible AI assistant can use, without writing a single integration per application.

## What is MCP, and why build a server for it

Model Context Protocol is an open standard (created by Anthropic) that lets AI assistants connect to external tools and data sources through a common interface, instead of every assistant needing a custom-built integration for every tool. Writing an MCP server means building the connection **once**, per data source — and any MCP-compatible client (Claude Desktop, Claude Code, or other MCP-aware applications) can use it immediately, with no per-application rework.

This project exposes a real PostgreSQL sales database (the same one used in [ai-analytics-agent](../ai-analytics-agent)) as a set of MCP tools, so Claude can query it, inspect its schema, and run anomaly detection — directly inside a normal conversation.

## Architecture

```mermaid
graph LR
    A[Claude Desktop] -->|MCP protocol| B[Sales Analytics<br/>MCP Server]
    B --> C[get_database_schema]
    B --> D[run_sql_query]
    B --> E[find_sales_anomalies]
    C --> F[(Supabase / PostgreSQL)]
    D --> F
    E --> F
```

## Tools exposed

| Tool | Description |
|---|---|
| `get_database_schema` | Returns available tables and columns, so Claude knows what data exists before querying |
| `run_sql_query` | Executes a read-only SQL query and returns results. Blocks any query that isn't `SELECT`/`WITH`, and rejects destructive keywords even if they appear mid-query |
| `find_sales_anomalies` | Runs a robust median/MAD-based anomaly detection pass over daily sales history and returns flagged dates with their statistical deviation |

## Tech Stack

| Layer | Tool |
|---|---|
| Protocol | Model Context Protocol (MCP) Python SDK |
| Client | Claude Desktop |
| Database | PostgreSQL (Supabase) |
| Query layer | SQLAlchemy |
| Anomaly detection | Pandas (median + MAD, rolling window) |
| Secrets | python-dotenv |

## Engineering notes

- **Caught a breaking SDK change mid-development**: the MCP Python SDK shipped a major version (2.0.0) that renamed `FastMCP` to `MCPServer` and moved its import path, right as this project was being built. The server was updated to the new API rather than pinning an older version, so the code reflects the current SDK surface.
- **Claude chooses its own tool strategy**: when asked to find anomalies before `find_sales_anomalies` existed, Claude independently used `run_sql_query` to investigate — checking for duplicate rows, negative amounts, and per-category breakdowns on its own initiative, arriving at the same root cause the dedicated tool later confirmed. This is a useful illustration of how an LLM client actually chooses between a general-purpose tool and a specialized one, rather than always preferring the "obvious" dedicated function.
- **Same safety model as the Text-to-SQL agent**: `run_sql_query` reuses the same read-only guardrail approach as the Python-based Text-to-SQL project — a good example of why building that safety logic once, carefully, pays off when it's reused in a second, independent tool.

## Setup

1. Install dependencies:
pip install -r requirements.txt

2. Create a `.env` file with:
DATABASE_URL=your_postgresql_connection_string

3. Add the server to Claude Desktop's config file (`claude_desktop_config.json`):
```json
   {
     "mcpServers": {
       "sales-analytics": {
         "command": "python",
         "args": ["/full/path/to/server.py"]
       }
     }
   }
```

4. Fully restart Claude Desktop (quit via Task Manager / Activity Monitor, not just closing the window)

5. Start a new chat and ask Claude something like:
   > "What tables are available, and are there any sales anomalies I should know about?"

## Example interaction

![Claude Desktop using the MCP server](screenshots/claude-mcp-demo.png)