Shop Analytics MCP

MCP server in TypeScript for secure analytics of an online store's SQLite database. Works only via stdio and provides six specialized tools; arbitrary SQL is not accepted.

Requirements

• Node.js 24.10+;

• npm;

• sqlite3 CLI only for re-applying the migration.

Related MCP server: mcp-analytics-server

Installation and Running

npm ci
npm run build
SHOP_DB_PATH=./shop.db npm start

SHOP_DB_PATH takes precedence. If the variable is not set, the server looks for shop.db in the current working directory. The path to the database is not hardcoded in the source code.

Data

The repository contains a ready-migrated shop.db. It contains customer countries and data for 2025. Monetary values are interpreted as EUR and are not converted.

To apply the migration to the original database once:

npm run migrate

The original schema is in database/schema.sql, and the deterministic migration is in database/001-add-analytics-data.sql.

Connecting to a Client

Ready-made configuration templates are in config/:

• claude-code.mcp.json;

• codex.mcp.json;

• cursor.mcp.json.

Replace /absolute/path/to/mcp-server in the chosen template with the path to this project. All configurations run dist/src/index.js via stdio and pass the path to shop.db through SHOP_DB_PATH.

Tools

Financial and product metrics, as well as order counts, exclude cancelled. Amounts are returned in fields with the Eur suffix. Rankings accept a limit from 1 to 100; periods use YYYY-MM-DD dates.

Example Usage

Below shows how an MCP client calls get_database_schema to describe tables, and then get_customer_metrics to count customers from Germany.

Security

• SQLite is opened with readOnly: true and PRAGMA query_only = ON.

• The SQLite authorizer forbids writes, DDL, ATTACH, DETACH, and transactions.

• All values are bound as SQL parameters.

• Tools do not accept SQL, so the agent cannot pass a destructive statement.

• Validation errors do not reveal SQL, absolute paths, or stack traces.

Tests

npm test

The tests cover reference answers to eight acceptance questions, SQL injection in the country parameter, the prohibition of DELETE, the immutability of the SHA-256 of a temporary copy of the DB, and MCP interaction via stdio.

Acceptance Questions

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?