Shop Analytics MCP Server

A read-only MCP 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 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

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 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/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 — 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:

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.

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 by a deterministic seed script — re-running it produces byte-identical data every time (fixed PRNG seed, no wall-clock dependency):

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.

Development

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)