Skip to main content
Glama
ecommerce-mcp-server

ecommerce-mcp-server

by simone202120

ecommerce-mcp-server

MCP server exposing safe, read-only tools over an e-commerce PostgreSQL database (with pgvector semantic product search), plus a LangGraph agent that uses it from a CLI, a Streamlit chat UI and Claude Desktop.

Agent answering "Top 5 products by revenue this quarter" with a tool call, chart, table and cost metrics

flowchart LR
    subgraph Clients
        CD[Claude Desktop] -- stdio --> S
        CLI[Agent CLI] -- streamable HTTP --> S
        UI[Streamlit UI] --> A[LangGraph agent] -- streamable HTTP --> S
        CLI --> A
    end
    A -- OpenRouter --> LLM[(LLM)]
    A -. optional .-> LF[(Langfuse)]
    S[FastMCP server<br/>5 read-only tools<br/>schema://tables] --> C[core: validation,<br/>SQL, models]
    C -- read-only pool --> PG[(PostgreSQL 17<br/>+ pgvector)]
    S -- query embedding --> FE[FastEmbed<br/>bge-small-en-v1.5]
    SEED[seed job<br/>Faker + FastEmbed] --> PG

Tools

Tool

What it answers

search_products(query, limit=5, category?)

Semantic search (pgvector cosine distance)

get_customer_orders(customer_email, limit=10)

A customer's orders with items and totals

sales_summary(start_date, end_date, group_by=day|week|month)

Revenue, orders, average order value

top_products(start_date, end_date, limit=10, by=revenue|quantity)

Best sellers in a period

low_stock_alert(threshold=10)

Products under a stock threshold with last-30-days sales

Resource schema://tables describes the data model. Every tool is read-only by construction: the connection pool opens sessions with default_transaction_read_only=on and a statement timeout, all SQL is parameterized, and every multi-row query has a row limit. Bad arguments get clear error messages (e.g. start_date (2026-09-01) must be on or before end_date (2026-08-01)).

Related MCP server: shop-mcp

Quick start (Docker)

cp .env.example .env         # add OPENROUTER_API_KEY; Langfuse keys are optional
docker compose up --build    # postgres, seed job, MCP server (:8000), UI (:8501)
  • UI: http://localhost:8501

  • Agent CLI: docker compose exec mcp python -m ecommerce_mcp.agent "Which products are running low and how much did they sell last month?"

Local development

uv sync
docker compose up -d postgres
uv run python -m ecommerce_mcp.infra.seed                       # schema + deterministic data
uv run python -m ecommerce_mcp.server --transport streamable-http
uv run python -m ecommerce_mcp.agent "Top 5 products by revenue this quarter"
uv run streamlit run src/ecommerce_mcp/ui/app.py

The CLI prints the tool calls, the answer, latency, tokens, the estimated cost and the Langfuse trace link (when tracing is configured).

Claude Desktop

Seed the database first, then add to claude_desktop_config.json:

{
  "mcpServers": {
    "ecommerce": {
      "command": "uv",
      "args": ["--directory", "/path/to/ecommerce-mcp-server", "run", "python", "-m", "ecommerce_mcp.server"],
      "env": { "DATABASE_URL": "postgresql://shop:shop@127.0.0.1:5432/shop" }
    }
  }
}

Use 127.0.0.1 rather than localhost: Docker publishes the port on IPv4 only, and psycopg's async connect would try IPv6 (::1) first and hang.

Configuration

All settings are environment variables (or .env), read by src/ecommerce_mcp/config.py.

Variable

Default

Purpose

DATABASE_URL

postgresql://shop:shop@127.0.0.1:5432/shop

PostgreSQL connection

DB_POOL_SIZE / DB_STATEMENT_TIMEOUT_MS

5 / 5000

Tool connection pool

EMBEDDING_MODEL

BAAI/bge-small-en-v1.5

FastEmbed model (must output 384-dim vectors)

MCP_HOST / MCP_PORT

127.0.0.1 / 8000

Streamable HTTP bind address

MCP_URL

http://localhost:8000/mcp

Where the agent and UI reach the server

OPENROUTER_API_KEY

—

LLM access (OpenRouter, OpenAI-compatible)

OPENROUTER_BASE_URL

https://openrouter.ai/api/v1

OpenRouter API base URL

LLM_MODEL

google/gemini-3.8-flash

Chat model

LLM_TIMEOUT_SECONDS / AGENT_MAX_STEPS

60 / 12

Agent limits

LLM_INPUT_USD_PER_MTOK / LLM_OUTPUT_USD_PER_MTOK

0.30 / 2.50

Cost estimate shown per run

LANGFUSE_PUBLIC_KEY / LANGFUSE_SECRET_KEY

empty

Optional tracing (both required to enable it)

LANGFUSE_HOST

https://cloud.langfuse.com

Langfuse instance URL

Security

  • Read-only by construction: SELECT-only parameterized SQL, row limits, and database sessions opened with default_transaction_read_only=on and a statement timeout.

  • Tool arguments are validated (JSON schema bounds plus semantic checks); database errors reach the client as a generic message, with details only in the server logs.

  • The agent's system prompt treats tool results as data, never as instructions.

  • Local demo only. Authentication on the MCP server is a non-goal, and the compose file uses a throwaway shop/shop database password. Compose publishes ports on 127.0.0.1 only; do not expose these services on a shared network without a reverse proxy with auth and TLS.

Tests

uv run pytest tests/unit -q          # fast, no services
uv run pytest -m integration         # needs a running postgres (seeds it)
uv run pytest -m llm                 # one real agent question end to end (costs money)

CI runs lint, format, mypy --strict, vulture, deptry, and unit + integration tests with coverage (>= 80%) on a pgvector service, and builds the Docker image on pull requests.

Docs

Development

This project is developed with an AI-assisted workflow using Claude Code: project context in CLAUDE.md, specialized agents, slash commands and hooks in .claude/ (auto-formatting, secret protection, session context).

License

MIT

Available Tools

5 tools
get_customer_ordersA
Read-onlyIdempotent

A customer's most recent orders (newest first), each with status, line items and total. Fails with a clear message if no customer has that email.

Inputs: customer_email (required), limit. Returns: orders, customer_name, customer_email

low_stock_alertA
Read-onlyIdempotent

Products running low on stock (lowest first) with their units sold and revenue in the last 30 days, to decide what to restock.

Inputs: threshold. Returns: products, threshold

sales_summaryA
Read-onlyIdempotent

Revenue, number of orders and average order value (AOV) between two dates, in total and per day, week (starting Monday) or month. Cancelled orders are excluded; a range with no orders returns zero totals.

Inputs: end_date (required), start_date (required), group_by. Returns: periods, end_date, group_by, start_date, +3 more

search_productsA
Read-onlyIdempotent

Semantic product search: finds products whose meaning matches the query, even without shared keywords. Returns price, stock and a cosine similarity score (1 = identical meaning; above ~0.8 is a strong match).

Inputs: query (required), limit, category. Returns: query, category, products

top_productsA
Read-onlyIdempotent

Best-selling products between two dates, ranked by revenue or by units sold. Cancelled orders are excluded; a range with no sales returns an empty list.

Inputs: end_date (required), start_date (required), by, limit. Returns: end_date, products, ranked_by, start_date

Tool Schema Changelog

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

Loading tool schema changelog…

    TDQS

    A4/5.0

    Scored across 5 tools

    Disambiguation4/5

    Most tools have clearly distinct purposes: customer-specific orders, semantic product search, aggregate sales metrics, product ranking, and stock alerts. However, sales_summary and top_products both use date ranges and revenue, so an agent might briefly hesitate between aggregate reporting and per-product ranking.

    Naming Consistency3/5

    All names use lowercase snake_case and are descriptive, but they mix verb-led names like get_customer_orders and search_products with noun-phrase names like sales_summary, top_products, and low_stock_alert. The inconsistency is minor and readable rather than chaotic.

    Tool Count5/5

    Five tools is a well-scoped size for a focused ecommerce server. Each tool covers a meaningful slice of ecommerce needs without redundancy or bloat.

    Completeness3/5

    The set covers customer order lookup, product discovery, sales reporting, product ranking, and stock alerts, which forms a useful read-only analytics surface. However, there are notable gaps for a broad 'ecommerce' domain, such as product detail retrieval, order lookup by ID, inventory management, or customer management.

    Related MCP Connectors

    Related MCP Servers

    • 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.
      -
    • A
      license
      A
      quality
      D
      maintenance
      A read-only MCP server that lets AI agents run safe, specialized analytics over an internet shop's SQLite database, covering customers, products, orders, and revenue. It exposes no generic SQL or write tools, so agents can answer questions without modifying data.
      8
      MIT
    • A
      license
      A
      quality
      D
      maintenance
      Enables natural-language querying and analysis of an e-commerce SQLite database via MCP, with read-only SQL execution, table inspection, and AI-generated answers.
      4
      MIT
    • A
      license
      A
      quality
      B
      maintenance
      Enables AI agents to securely access and reason over WooCommerce orders, products, and inventory through read-only MCP tools, with PII redaction, rate-limit handling, and bounded pagination.
      5
      MIT