Skip to main content
Glama
Sign In
deenesjakoruzh
Shop Analytics MCP

Shop Analytics MCP

by Myrhoiazov
OverviewSchemaSourceRelated ServersDiscussionsAdmin
TypeScript
Local

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: Shop SQLite MCP

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

Tool

Purpose

get_database_schema

Tables, columns, keys, and relationships.

get_customer_metrics

Number of customers in a country or the leading country.

get_product_sales

Product ranking by units sold and revenue.

get_category_revenue

Category ranking by revenue.

get_revenue_by_period

Revenue for the UTC period [from, to).

get_order_leaders

Customer with the highest spending or number of orders.

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.

Example of calling MCP tools

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?

Available Tools

6 tools
get_category_revenueA

Rank non-cancelled categories by EUR revenue. Optional YYYY-MM-DD from/to and integer limit 1-100.

Inputs: no named parameters.

get_customer_metricsB

Count customers in a country or find the top country. Parameters: mode=count_by_country with country, or mode=top_country without country.

Inputs: no named parameters.

get_database_schemaA

Inspect tables, columns, keys, and relationships. Parameters: none.

Inputs: no named parameters.

get_order_leadersA

Find a customer with highest spend or most non-cancelled orders. mode=highest_spend accepts optional YYYY-MM-DD from/to; mode=most_orders accepts no dates.

Inputs: no named parameters.

get_product_salesA

Rank non-cancelled products by units sold and EUR revenue. Optional YYYY-MM-DD from/to and integer limit 1-100.

Inputs: no named parameters.

get_revenue_by_periodA

Calculate non-cancelled EUR revenue. Optional YYYY-MM-DD from/to define [from, to).

Inputs: no named parameters.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    Deploy Server
    One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

    TDQS

    A3.9/5.0

    Scored across 6 tools

    Disambiguation5/5

    Each tool targets a distinct entity or aggregation level: database schema, customer counts, product sales rankings, category revenue, total revenue, and customer leaders. Even though get_customer_metrics and get_order_leaders both involve customers, their purposes (country-level counts vs. individual customer extremes) are clearly separated. No two tools are likely to cause misselection.

    Naming Consistency5/5

    All tool names follow the consistent pattern 'get_' followed by a descriptive noun phrase, using snake_case throughout. Examples: get_database_schema, get_product_sales, get_revenue_by_period. This uniform phrasing makes the set predictable and easy to navigate.

    Tool Count5/5

    With 6 tools, the server is well-scoped for a shop analytics use case. It covers the essential query types without unnecessary proliferation, and each tool earns its place by addressing a distinct analytical question. The count is within the ideal range for a focused server.

    Completeness4/5

    The tool set covers key analytics workflows: schema inspection, customer metrics, product and category performance, total revenue, and top customer identification. Minor gaps include lack of time-series revenue breakdown (e.g., by day/month) and no list of customers beyond the leader, but the core analytical needs are met. These omissions are workable around.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Resources

    Related MCP Connectors

    Related MCP Servers

    • Local Database Analytics MCP

      manohar135
      F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants like Claude to query, analyze, and summarize local SQLite e-commerce databases using natural language through 11 tools, schema resources, and pre-built analytical prompts.
      -

    • Shop SQLite MCP

      mixe-deep
      F
      license
      Not graded
      quality
      D
      maintenance
      Enables safe, read-only analysis of an online store's SQLite database, providing schema introspection, restricted SELECT queries, and specialized analytics tools through MCP.
      -

    • db-mcp

      denezhkinia
      A
      license
      A
      quality
      B
      maintenance
      Enables AI agents to safely interact with a SQLite shop database through schema discovery, read-only SQL queries, and pre-built analytics reports like top customers, top products, and revenue summaries.
      6
      236 npm
      MIT

    • Shop Analytics MCP Server

      bogdaamn
      F
      license
      A
      quality
      C
      maintenance
      Enables AI agents to answer analytical questions about an online store's SQLite database through specialized read-only tools, without any risk of modifying the underlying data.
      8
      -