Shop Analytics MCP Server

A read-only MCP server, over stdio, that lets an AIagent answer analytical questions about an online store's SQLite database(customers, products, orders, order_items) — without ever being able tomodify 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 theread-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 toregenerate it deterministically from the schema, run npm run seed (seeDatabase below).
SHOP_DB_PATH is optional; it defaults to shop.db in the current workingdirectory. No absolute path is hard-coded anywhere in the source.
The server speaks MCP over stdio only — there is no HTTP server andnothing else to run.

Connect an AI agent

Config examples for two clients are in config/:

config/claude-code.mcp.json — copy into aproject's .mcp.json, or run claude mcp add-json with its shop-analyticsentry. Fill in absolute paths for args/env first.
config/codex.mcp.toml — copy the[mcp_servers.shop-analytics] table into ~/.codex/config.toml (or aproject-scoped .codex/config.toml), or use the codex mcp add command inthe file's header comment.

To poke at the server manually without any specific agent, use thetool-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 orexecutes arbitrary SQL. Every successful response is { "data": [...], "meta": {...} };every error is a plain, safe, human-readable message (no SQL, file paths, orstack 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 metricsexclude orders with status cancelled. Full per-tool contracts (exact responseshapes, tie-break rules) are in SPEC.md §4.

Safety

Three independent, defense-in-depth layers guarantee the database is nevermodified, even by an adversarial prompt like "Delete all cancelled orders":

The SQLite connection is opened with readOnly: true.
PRAGMA query_only = ON is set immediately after opening.
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 — everyquery is a fixed prepared statement, and every input is validated with zodand passed as a bound parameter, never string-interpolated.

Database

shop.db is generated from database/schema.sql by adeterministic seed script — re-running it produces byte-identical dataevery 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 noambiguous leaderboards (e.g. a unique top country, a unique top spender) andnon-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 waswritten first, then the implementation, tool by tool. The integration suitecovers all 8 acceptance scenarios end-to-end, SQL-injection-shaped inputs,invalid parameter combinations, and asserts the database file's SHA-256 hashis 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)

Shop Analytics MCP Server