# Shop Analytics MCP Server

A read-only [MCP](https://modelcontextprotocol.io) server, over stdio, that lets an AI
agent answer analytical questions about an online store's SQLite database
(`customers`, `products`, `orders`, `order_items`) — without ever being able to
modify it.

See [SPEC.md](./SPEC.md) for the full design rationale (decisions log, schema,
security model, testing strategy).

## Requirements

- Node.js **>= 24.10.0** (needed for `node:sqlite`'s `setAuthorizer`, used by the
  read-only guarantee below). Check with `node --version`.
- No other runtime dependencies beyond what `npm ci` installs.

## Install → configure → run → connect

```bash
npm ci
npm run build
SHOP_DB_PATH=./shop.db npm start
```

- `shop.db` ships in this repository, ready to use. If you ever need to
  regenerate it deterministically from the schema, run `npm run seed` (see
  [Database](#database) below).
- `SHOP_DB_PATH` is optional; it defaults to `shop.db` in the current working
  directory. No absolute path is hard-coded anywhere in the source.
- The server speaks MCP over **stdio only** — there is no HTTP server and
  nothing else to run.

### Connect an AI agent

Config examples for two clients are in [`config/`](./config):

- [`config/claude-code.mcp.json`](./config/claude-code.mcp.json) — copy into a
  project's `.mcp.json`, or run `claude mcp add-json` with its `shop-analytics`
  entry. Fill in absolute paths for `args`/`env` first.
- [`config/codex.mcp.toml`](./config/codex.mcp.toml) — copy the
  `[mcp_servers.shop-analytics]` table into `~/.codex/config.toml` (or a
  project-scoped `.codex/config.toml`), or use the `codex mcp add` command in
  the file's header comment.

To poke at the server manually without any specific agent, use the
tool-agnostic [MCP Inspector](https://github.com/modelcontextprotocol/inspector):

```bash
SHOP_DB_PATH=$(pwd)/shop.db npx @modelcontextprotocol/inspector node dist/src/index.js
```

## Tools

The server exposes exactly 8 specialized, read-only tools — no tool accepts or
executes arbitrary SQL. Every successful response is `{ "data": [...], "meta": {...} }`;
every error is a plain, safe, human-readable message (no SQL, file paths, or
stack traces), flagged with `isError: true`.

| Tool | Answers | Key parameters |
| --- | --- | --- |
| `get_database_schema` | "Show me all tables and what they contain." | *(none)* |
| `get_customers_by_country` | "How many customers are from Germany?" | `country` (required) |
| `get_top_countries_by_customers` | "Which country has the most customers?" | `limit` (default 1) |
| `get_top_customers_by_spend` | "Who spent the most money?" | `limit`, `from`, `to` |
| `get_top_selling_products` | "What are the top 5 best-selling products?" | `limit` (default 5), `from`, `to` |
| `get_top_categories_by_revenue` | "What are the top 3 categories by revenue?" | `limit` (default 3), `from`, `to` |
| `get_revenue_for_period` | "How much revenue did we generate in 2025?" | `from`, `to` |
| `get_top_customers_by_orders` | "Which customer placed the most orders?" | `limit`, `from`, `to` |

`from`/`to` are `YYYY-MM-DD` and define a half-open UTC interval `[from, to)`;
`from` must be strictly earlier than `to`. All financial and count metrics
exclude orders with status `cancelled`. Full per-tool contracts (exact response
shapes, tie-break rules) are in [SPEC.md §4](./SPEC.md#4-mcp-tools-specialized-set-one-per-analytical-need).

## Safety

Three independent, defense-in-depth layers guarantee the database is never
modified, even by an adversarial prompt like *"Delete all cancelled orders"*:

1. The SQLite connection is opened with `readOnly: true`.
2. `PRAGMA query_only = ON` is set immediately after opening.
3. A SQLite `authorizer` explicitly denies every write/DDL action
   (`INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`, `ATTACH`, `DETACH`,
   transactions, ...).

On top of that, no tool accepts raw SQL, table names, or column names — every
query is a fixed prepared statement, and every input is validated with `zod`
and passed as a bound parameter, never string-interpolated.

## Database

`shop.db` is generated from [`database/schema.sql`](./database/schema.sql) by a
**deterministic** seed script — re-running it produces byte-identical data
every time (fixed PRNG seed, no wall-clock dependency):

```bash
npm run seed   # builds, then (re)writes ./shop.db from schema.sql + the seed script
```

The seed script also asserts, at generation time, that the dataset has no
ambiguous leaderboards (e.g. a unique top country, a unique top spender) and
non-zero 2025 revenue — see [SPEC.md §3](./SPEC.md#3-database-design-to-be-authored-not-borrowed).

## Development

```bash
npm run build           # tsc + copy database/schema.sql into dist/
npm run test:unit        # business logic, in isolation, against fixture databases
npm run test:integration # spawns the built server over stdio via the MCP SDK client
npm test                 # both
```

This project was built with **TDD**: for every module, a failing test was
written first, then the implementation, tool by tool. The integration suite
covers all 8 acceptance scenarios end-to-end, SQL-injection-shaped inputs,
invalid parameter combinations, and asserts the database file's SHA-256 hash
is unchanged after every run.

### Project structure

```
database/       schema.sql + the deterministic seed generator
src/
  db.ts          read-only SQLite connection (see Safety above)
  errors.ts      error taxonomy, safe error formatting
  validation.ts  zod schemas shared across tools (dates, limits, periods)
  period.ts      half-open period SQL clause builder
  tools/         one module per tool: pure query function + types
  server.ts      registers all 8 tools on the MCP server
  index.ts       stdio entrypoint
test/
  unit/          one file per module/tool, fixture-based
  integration/   spawns dist/src/index.js over stdio via the MCP SDK client
config/         example client configuration (Claude Code, Codex CLI)
```