shop-mcp

A read-only Model Context Protocol 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 (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:

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

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

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

uv run python -m shop_mcp

or, with the package installed in an active venv:

python -m shop_mcp

or, equivalently:

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):

{
  "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), 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

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).

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

A matching MCP client config using Docker:

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

To mount your own database instead of the bundled one:

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.