mcp-shop-server

An MCP server that gives an AI agent read-only access to an online store's SQLite database (customers, products, orders, order_items). Through it, the agent answers analytical questions about the data: database structure, aggregates by customers, products, categories, and revenue. Transport — stdio.

Writing to the database is impossible by design: three independent layers of protection — a mode=ro connection, request validation before execution (only SELECT / WITH ... SELECT), and the sqlite3-authorizer.

Measurements, evidence, and deviations from the spec — REPORT.md.

Usage

1. Clone

git clone https://github.com/andreykutsenko/mcp-shop-server.git
cd mcp-shop-server

The repository already contains shop.db (150 customers, 50 products, 750 orders, 1900 line items).

2. Install dependencies

uv venv .venv
uv pip install --python .venv/bin/python -r requirements.txt

Without uv — the same using standard tools:

python3 -m venv .venv
.venv/bin/pip install -r requirements.txt

Python 3.11+ is required. Dependencies: mcp (official MCP SDK) and pytest for tests; database work uses sqlite3 from the standard library.

3. Add to the agent config

Minimal configuration form:

{
  "command": "python",
  "args": ["/absolute/path/to/mcp-shop-server/server.py"]
}

A working example for a client with an mcpServers block (Claude Desktop, Cursor, and compatible):

{
  "mcpServers": {
    "shop-db": {
      "command": "/absolute/path/to/mcp-shop-server/.venv/bin/python",
      "args": ["/absolute/path/to/mcp-shop-server/server.py"],
      "env": {
        "MCP_SHOP_DB": "/absolute/path/to/mcp-shop-server/shop.db"
      }
    }
  }
}

For Claude Code, a single command is enough:

claude mcp add shop-db -- /absolute/path/to/mcp-shop-server/.venv/bin/python /absolute/path/to/mcp-shop-server/server.py

MCP_SHOP_DB is optional: if the variable is not set, the server takes shop.db next to server.py. Set it if the database is located elsewhere. It's better to specify the interpreter from .venv — otherwise the system python may not find the mcp package.

4. Launch

The server is launched by the agent; doing it by hand is rarely needed:

.venv/bin/python server.py

The process silently waits for JSON-RPC on stdin; diagnostics go to stderr, stdout is occupied by the MCP protocol.

5. Verification and questions for the agent

.venv/bin/python -m pytest -q

After connecting, the agent sees three tools. Questions are asked in plain language.

The eight tasks from the homework text — these are the ones worth running for verification:

1. Show me all available tables and explain what information each table contains.
2. How many customers are from Germany?
3. Which country has the most customers?
4. Who is the customer who spent the most money?
5. What are the top 5 best-selling products?
6. What are the top 3 product categories by revenue?
7. How much revenue did we generate in 2025?
8. Which customer placed the most orders?

⚠️ Tasks 2, 3, and 7 have no solution in the provided database, and that's expected. customers has no country column — all 150 customers have Russian phone numbers; all 750 orders are dated 2026, there is no data for 2025.

In this case, the server does not invent data: it reports that no such field exists in the schema and lists the existing columns. Nothing is hardcoded — the schema is read from the database, so on another database where country exists, the same questions work normally.

Additionally, questions that the database fully covers are checked: top-5 customers by order total, revenue by category, order distribution by status, average check, product stock.

Write-protection check. On "Delete all cancelled orders" the agent gets a clear refusal, not an error: the server is read-only, and 102 cancelled orders remain in place.

Tools

Output is limited: 100 rows by default, 1000 maximum. When truncated, the response reports how many rows were returned, how many were found in total, and with which offset to continue reading.

If a requested field is not in the database (for example, customer country), the server honestly says so and lists the existing columns — nonexistent fields are not invented.

Related MCP server: Shop Analytics MCP Server

How it's made

The project was generated with a single prompt — the file SPEC-mcp-shop.md, sent to the agent in full, without any follow-up clarifications.

Internally, the agent worked in a loop using the skill repo-task-proof-loop (Denis Shiryaev, Apache-2.0): freeze the spec → build → package evidence → verify with a fresh session → minimal fix → verify again, until a PASS verdict.

Run evidence is stored in the repository, in .agent/tasks/mcp-shop-server/:

• spec.md — the frozen spec with acceptance criteria AC1…AC17;

• evidence.md / evidence.json — for each criterion, a verdict and concrete evidence;

• verdict.json — the result of an independent check by a fresh session;

• problems.md — discrepancies found by the reviewer;

• raw/ — raw run logs: tests, a live MCP session, stdout cleanliness check.

What's checked is not the source code but the server's behavior with a live agent: the harness raw/mcp_session_check.py starts server.py over stdio with a real MCP client, calls all tools, runs the eight analytical tasks, gets a refusal on deletion, and verifies that stdout contains only JSON-RPC frames.

The development skill itself lives locally in .claude/skills/ and is not committed to the repository — it's third-party code.

Decisions on spec ambiguities