# analytics-mcp-server

A **[Model Context Protocol](https://modelcontextprotocol.io) (MCP) server, built with [FastMCP](https://gofastmcp.com)**, that lets an LLM safely explore and analyse a **SQLite** database through well-designed tools — list tables, inspect schema, run **guarded read-only SQL**, compute aggregations, and import CSVs.

It ships with a seeded sample e-commerce dataset, so you can clone and run it in under a minute with **zero API keys or external services**.

- **Language:** Python 3.10+
- **Framework:** FastMCP (`fastmcp`)
- **Data:** SQLite (stdlib) + pandas
- **Transport:** stdio (local) — the standard for desktop MCP clients
- **Tested:** 16 pytest cases, incl. read-only safety and pagination

---

## Why this exists

MCP servers expose *tools* that an LLM can call. The hard parts are (1) **safety** — never letting a model mutate or exfiltrate data it shouldn't — and (2) **ergonomics** — tools with clear schemas, pagination, and actionable errors so the model uses them correctly. This project demonstrates both.

---

## Quick start

```bash
git clone https://github.com/kshitiz305/analytics-mcp-server.git
cd analytics-mcp-server

python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt
pip install -e .

python scripts/seed_data.py     # generates sample.db
```

Run the server over stdio:

```bash
analytics-mcp            # console script
# or:  python -m analytics_mcp.server
```

### Try it without an MCP client

Use the built-in MCP Inspector:

```bash
npx @modelcontextprotocol/inspector analytics-mcp
```

### Register it with Claude Desktop

Add to `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "analytics": {
      "command": "analytics-mcp",
      "env": { "ANALYTICS_DB_PATH": "/absolute/path/to/sample.db" }
    }
  }
}
```

Point `ANALYTICS_DB_PATH` at **any** SQLite file to analyse your own data.

---

## Tools

| Tool | Purpose | Write? |
|------|---------|--------|
| `analytics_list_tables` | List tables with row counts | read-only |
| `analytics_describe_table` | Column schema, row count, sample rows | read-only |
| `analytics_run_query` | Run a guarded, paginated `SELECT` | read-only |
| `analytics_aggregate` | Group-by + `count/sum/avg/min/max` (no SQL needed) | read-only |
| `analytics_import_csv` | Load a CSV into a table (validated via pandas) | **write** |

Every tool supports `response_format="markdown"` (default, human-readable) or `"json"` (machine-readable, full precision), carries MCP annotations (`readOnlyHint`, `destructiveHint`, …), and returns actionable `Error: …` messages.

### Example output

`analytics_list_tables`:

```
### Tables

| table | rows |
| --- | --- |
| customers | 200 |
| order_items | 2420 |
| orders | 800 |
| products | 40 |
```

`analytics_aggregate(table="orders", group_by="status", agg="count")`:

```
### count(*) by status in orders

| status | value |
| --- | --- |
| completed | 379 |
| shipped | 175 |
| processing | 119 |
| cancelled | 87 |
| returned | 40 |
```

`analytics_run_query` with a join + pagination (top customers by spend):

```
Returned 5 of 196 rows (offset 0, next_offset 5)

| name | country | spend |
| --- | --- | --- |
| Arjun Khan | Japan | 22462.72 |
| Hiro Gupta | Japan | 21656.45 |
| Fatima Lee | Japan | 21249.44 |
| Liam Gupta | Canada | 19013.48 |
| Liam Brown | India | 18822.85 |
```

Attempting a write is rejected:

```
analytics_run_query(sql="DROP TABLE customers")
→ Error: Only read-only queries are permitted. The statement must start with SELECT or WITH.
```

---

## Safety model

User-supplied SQL is treated as untrusted and guarded on **three independent layers**:

1. **Read-only connection** — queries execute over a `file:…?mode=ro` SQLite URI, so writes are rejected at the storage engine level.
2. **Authorizer callback** — an allow-list [`set_authorizer`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.set_authorizer) permits only read actions (`SELECT`/`READ`/`FUNCTION`), blocking `ATTACH`, `PRAGMA` writes, etc.
3. **Statement validation** — `analytics_run_query` accepts a single `SELECT`/`WITH` statement only, with fast, clear errors before touching the database.

Tools that build SQL internally (`list_tables`, `describe_table`, `aggregate`) never interpolate raw user text — table/column names are validated against the live schema and quoted, so they are injection-safe. The only write path, `analytics_import_csv`, validates the destination name against an identifier allow-list.

---

## Sample dataset

`scripts/seed_data.py` generates a deterministic (seeded) e-commerce dataset:

- **customers** (200) — id, name, email, country, signup_date
- **products** (40) — id, name, category, price
- **orders** (800) — id, customer_id, order_date, status
- **order_items** (2420) — id, order_id, product_id, quantity, unit_price

Because the RNG is seeded, the numbers above are reproducible on any machine.

---

## Testing

```bash
pip install -e ".[dev]"
pytest
```

The suite (`tests/test_server.py`) covers schema discovery, pagination, aggregation, CSV import, rejection of write/multi-statement SQL, and an end-to-end call through FastMCP's in-memory client.

---

## Docker

```bash
docker build -t analytics-mcp .
docker run --rm -i analytics-mcp        # serves MCP over stdio
```

The image installs the package and bundles a freshly seeded `sample.db`.

---

## Project structure

```
analytics-mcp-server/
├── src/analytics_mcp/
│   ├── server.py        # FastMCP server + tool definitions
│   ├── database.py      # SQLite access layer (read-only safety)
│   ├── models.py        # Enums for tool inputs
│   ├── formatting.py    # JSON / Markdown formatting + pagination
│   └── sample_data.py   # Deterministic dataset generator
├── scripts/seed_data.py # CLI to (re)build sample.db
├── tests/test_server.py # pytest suite
├── Dockerfile
└── pyproject.toml
```

---

## License

[MIT](LICENSE) © 2026 Kshitiz Gupta