# Shop MCP Server

A read-only [MCP](https://modelcontextprotocol.io) server that exposes the
`shop.db` SQLite database to AI consumers as **domain entities** and
**analytic answers** — not as raw tables and SQL.

- Exposes `customers`, `products`, `orders`, `order_items` as first-class
  read tools.
- Answers reporting questions (top customers by spend, best-selling products,
  top categories by revenue, revenue by period, customers by order count).
- **Read-only by design and by engine**: no tool accepts arbitrary SQL, every
  prepared statement is asserted to be a `SELECT`, and the database is opened
  with `readOnly: true`.

## Requirements

- **Node.js >= 22.5** (uses the built-in `node:sqlite` module — no native
  dependencies).
- `npm` for building.

## Setup

```bash
npm install
npm run build     # compiles TypeScript to dist/
npm test          # runs the read-only unit tests
```

The compiled server entry point is `dist/index.js`. Re-run `npm run build`
after any change to `src/`.

## Configuration

By default the server reads `./shop.db` in the working directory. Set the
`DB_PATH` environment variable to point at a different database file:

```bash
DB_PATH=/abs/path/to/shop.db node dist/index.js
```

The server speaks MCP over **stdio** and is intended to be launched by an MCP
client, e.g.:

```json
{
  "mcpServers": {
    "shop": {
      "command": "node",
      "args": ["/abs/path/to/shop-mcp/dist/index.js"],
      "env": { "DB_PATH": "/abs/path/to/shop.db" }
    }
  }
}
```

Claude Code (`.mcp.json`), ChatGPT, and Antigravity all accept this shape.

## Tools

### Entity tools

| Tool | Params |
|------|--------|
| `get_customers` | `search?`, `limit?`, `offset?` |
| `get_products` | `category?`, `inStock?`, `minPrice?`, `maxPrice?`, `limit?`, `offset?` |
| `get_orders` | `status?`, `customer_id?`, `from_date?`, `to_date?`, `include_items?`, `limit?`, `offset?` |
| `get_order_items` | `order_id?`, `product_id?`, `limit?`, `offset?` |

`get_orders` with `include_items: true` attaches each order's line items.

### Analytic tools

All take optional `from`/`to` date filters and a `limit` (default 10), except
`revenue_by_period` which takes `group_by: "year" | "month"`.

| Tool | Answers |
|------|---------|
| `top_customers_by_spend` | Who spent the most? |
| `top_products_by_quantity` | Best-selling products? |
| `top_categories_by_revenue` | Top categories by revenue? |
| `revenue_by_period` | Revenue in 2025 / by month? |
| `customers_by_order_count` | Which customer placed the most orders? |

### Cancelled-order semantics

Orders with `status = 'cancelled'` are **excluded** from all analytic
aggregates (spend / revenue / quantity), but remain visible via `get_orders`
(filterable by `status`).

## Read-only model

Read-only is enforced in three independent layers:

1. **No raw SQL** — each tool is a fixed `SELECT` template with validated,
   whitelisted identifiers and value parameters bound via `?` placeholders.
2. **Runtime assertion** — every built statement is asserted to match
   `/^\s*SELECT\b/i` before it is prepared.
3. **Engine guarantee** — the database is opened with `readOnly: true`, so
   SQLite itself refuses any write regardless of tool input.

## Data model

| Table | Purpose |
|-------|---------|
| `customers` | Customer profiles (name, email, phone, created_at) |
| `products` | Products (name, category, price, stock_quantity) |
| `orders` | Orders (customer, order_date, status, total_amount) |
| `order_items` | Order line items (product, quantity, unit_price) |

`orders.total_amount` equals the sum of its `order_items`
(`unit_price * quantity`); revenue is derived from `orders.total_amount`.

## Project layout

```
shop.db                     the SQLite database
docs/implementation-plan.md the design and tool plan
src/db.ts                   DB open (readOnly) + identifier/SELECT guards
src/index.ts                MCP server setup + tool registration
test/db.test.ts             read-only unit tests (node:test)
dist/                       tsc build output
```