# shop-mcp

A read-only [Model Context Protocol](https://modelcontextprotocol.io) server
that exposes analytics tools over the `shop.db` SQLite database of an internet
shop (customers, products, orders, order items). It is designed to be connected
to an AI agent so the agent can answer analytical questions about the data
without ever being able to modify it.

The server speaks MCP over **stdio**, opens the database in **read-only** mode,
and exposes a small set of **specialised, parameterised tools** whose
descriptions encode the domain rules (which order statuses count as revenue,
how a customer's country is derived, where money comes from). There is no
generic SQL tool and no write tool — a destructive prompt such as *"Delete all
cancelled orders"* cannot be executed.

> The MCP server code in this repository was produced by an AI coding agent
> (Cursor), per the homework constraint that the server must not be written by
> hand.



## Requirements

- Python 3.11 or newer
- The `shop.db` SQLite database (committed at `database/shop.db`)
- [`uv`](https://docs.astral.sh/uv/) (recommended) — runs the server in an
  isolated project environment with no global install. Install it with
  `brew install uv` (macOS) or `curl -LsSf https://astral.sh/uv/install.sh | sh`.



## Install

With `uv` (recommended) — no manual venv or pip needed, `uv` resolves the
project and its dependencies from `pyproject.toml` on first run:

```bash
uv sync          # create / refresh the project's .venv from pyproject.toml
```

Without `uv` — create a virtualenv and install the package yourself:

```bash
python3 -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install -e .
```

This installs the `mcp` SDK and the `shop-mcp` package (which provides the
`python -m shop_mcp` entry point and the `shop-mcp` console script).

## Configure

The server opens the database at **`database/shop.db`** relative to the
process working directory (ProjectRoot). No environment variables are required.

When launched via `uv run --directory <project>` (see the client configs
below), `uv` sets the working directory to the project root, so the committed
database is found automatically.

If `database/shop.db` is missing, the server exits at startup with a clear
configuration error that includes the current working directory (no stack
trace, no silent fallback). Ensure your MCP client config sets `cwd` to the
repository root.

## Run

```bash
uv run python -m shop_mcp
```

or, with the package installed in an active venv:

```bash
python -m shop_mcp
```

or, equivalently:

```bash
shop-mcp
```

The server reads JSON-RPC over stdin and writes to stdout. You normally do not
run it directly — your AI agent launches it for you (see below).

## Connect to an agent

Ready-to-use MCP client configs are committed under `examples/mcp/` and run
with no setup beyond installing `uv`:


| Client            | Config file                        |
| ----------------- | ---------------------------------- |
| Cursor            | `examples/mcp/cursor.json`         |
| Claude Desktop    | `examples/mcp/claude_desktop.json` |
| Generic stdio     | `examples/mcp/generic_stdio.json`  |
| Canonical/default | `examples/mcp/shop.json`           |
| Docker            | `examples/mcp/docker.json`         |


Each config looks like this (replace the `--directory` path with the absolute
path of this repo on your machine):

```json
{
  "mcpServers": {
    "shop": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/internet-shop-mcp", "python", "-m", "shop_mcp"]
    }
  }
}
```

`uv run --directory <project>` sets the working directory to the project root
and uses the project's `.venv`, so the server finds `database/shop.db`
automatically. The same config is portable across machines (only the
`--directory` path changes).

If you prefer not to use `uv`, install the package into a venv yourself (see
[Install](#install)), use `command: "python"`, and set `cwd` to the repository
root in your MCP client config.

- **Cursor**: open *Settings → MCP → Add MCP Server* and paste the contents of
`examples/mcp/cursor.json` (or use the *Project MCP* scope and commit it).
- **Claude Desktop**: copy the contents of `examples/mcp/claude_desktop.json`
into `claude_desktop_config.json` (macOS:
`~/Library/Application Support/Claude/claude_desktop_config.json`).
- **Generic stdio client**: use `examples/mcp/generic_stdio.json` with any
client that speaks MCP over stdio.

After connecting, the agent sees eight tools: `list_tables`,
`describe_table`, `count_customers_by_country`, `rank_countries_by_customers`,
`top_customers`, `top_products`, `revenue_by_category`, `revenue_by_year`.

## Tools


| Tool                                   | Answers                                     |
| -------------------------------------- | ------------------------------------------- |
| `list_tables`                          | Task 1 — list tables and what each contains |
| `describe_table(table)`                | schema of one table                         |
| `count_customers_by_country(country?)` | Task 2 — customers from a country           |
| `rank_countries_by_customers(limit)`   | Task 3 — country with the most customers    |
| `top_customers(by, limit, offset)`     | Tasks 4 & 8 — top spender / most orders     |
| `top_products(limit, metric, offset)`  | Task 5 — top best-selling products          |
| `revenue_by_category(limit, offset)`   | Task 6 — top categories by revenue          |
| `revenue_by_year(year)`                | Task 7 — revenue for a year                 |


Domain rules baked into the tool descriptions (see `CONTEXT.md` and
`docs/adr/` for the full rationale):

- **Country** is derived from the customer's phone-number prefix (E.164). There
is no `country` column. `+49` → Germany, `+7` → Russia. An unrecognised prefix
maps to `unknown`. The tool accepts a full name ("Germany") or an ISO alpha-2
code ("DE") and returns both.
- **Revenue / spend** count only `completed` and `shipped` orders.
- **Most orders** counts every order status except `cancelled`.
- **Best-selling** ranks products by units sold; revenue is a secondary field.
- **Money** comes from `orders.total_amount` for order/customer/year rollups
and from `SUM(order_items.quantity * order_items.unit_price)` for
product/category rollups (the actual sale price, not the current
`products.price`).
- **Limits** default to 100 and are clamped to a maximum of 1000; `offset`
paginates.
- **Errors** are returned to the agent as short plain messages (e.g.
`Invalid year: must be a 4-digit integer`); stack traces go to stderr only.



## Safety

The database is read-only by construction:

- SQLite is opened with `file:<path>?mode=ro` (`uri=True`), so any write attempt
raises `sqlite3.OperationalError: attempt to write a readonly database`.
- `PRAGMA query_only = 1` is set as defense in depth.
- No write or generic-SQL tool is exposed. The only tools are the eight
read-only analytics tools above.

A test (`tests/test_safety.py`) asserts that a write attempt raises, that no
write tool is advertised, and that the database file is byte-for-byte unchanged
after every tool runs.

## End-to-end verification

The eight homework tasks were verified against a connected AI agent. Expected
results on the committed data (150 customers, all with `+7` numbers; 750
orders, all dated 2026):

1. **List all tables** — `list_tables` returns `customers`, `products`,
  `orders`, `order_items` with a description each.
2. **How many customers are from Germany?** — `count_customers_by_country("Germany")`
  → `0` (honest zero; no customer has a `+49` number).
3. **Which country has the most customers?** — `rank_countries_by_customers`
  → Russia (`RU`), 150 customers.
4. **Who spent the most money?** — `top_customers(by="spend", limit=1)` →
  Полина Козлов, `polina.kozlov340@icloud.com`, total spend 531810.0.
5. **Top 5 best-selling products** — `top_products(limit=5)` → ranked by units
  sold (Эспандер плечевой, Планшет Tab 10, …) with revenue alongside.
6. **Top 3 categories by revenue** — `revenue_by_category(limit=3)` →
  Электроника, Бытовая техника, Одежда и обувь.
7. **Revenue in 2025** — `revenue_by_year(2025)` → `0` with the note
  `no orders in 2025` (no year substitution; all orders are 2026).
8. **Most orders** — `top_customers(by="order_count", limit=1)` →
  София Яковлев, `sofiya.yakovlev284@yandex.ru`, 15 orders.

The destructive prompt **"Delete all cancelled orders"** is refused: there is
no tool that accepts it, and the read-only connection rejects any write at the
SQLite level.

## Tests

```bash
uv run --extra dev pytest
# or, with the package installed in an active venv:
pip install -e ".[dev]"
python -m pytest
```

The suite covers: the smoke test (server starts over stdio and answers a
handshake/list_tools), every tool's happy path, the domain rules (revenue
excludes non-earned statuses, order count excludes cancelled, products rank by
units), edge cases (Germany → 0, 2025 → 0 with note, unknown country, invalid
year/metric/by, limit clamping, pagination), and the safety guarantees (write
attempt raises, no write tools, database file unchanged).

## Docker (bonus)

See the "Docker" section below for a containerised run.

## Project layout

```
internet-shop-mcp/
├── database/
│   └── shop.db                  # the read-only database
├── pyproject.toml               # package + dependency declaration
├── README.md
├── CONTEXT.md                   # domain glossary
├── docs/adr/                    # ADR-0001..0005
├── src/shop_mcp/
│   ├── __main__.py              # `python -m shop_mcp`
│   ├── main.py                  # server wiring + tool registration
│   ├── config.py                # database/shop.db resolution
│   ├── db.py                    # read-only SQLite connection
│   ├── country.py               # phone-prefix → country mapping
│   └── tools.py                 # tool implementations
├── tests/                       # pytest suite mirroring src
├── examples/mcp/                # agent connection configs
├── Dockerfile
└── .dockerignore
```



## Docker

Build and run the server in a container. The database is copied into the image
at `/app/database/shop.db` (same convention as local dev).

```bash
docker build -t shop-mcp .
docker run --rm -i shop-mcp
```

A matching MCP client config using Docker:

```json
{
  "mcpServers": {
    "shop": {
      "command": "docker",
      "args": ["run", "--rm", "-i", "shop-mcp"]
    }
  }
}
```

To mount your own database instead of the bundled one:

```bash
docker run --rm -i -v "$PWD/database:/app/database:ro" shop-mcp
```

The read-only guarantees are preserved inside the container: the connection
uses `mode=ro` and `query_only=1`, and a destructive prompt is still refused.