Skip to main content
Glama
simone202120

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 SQLite 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@localhost:5432/shop" }
    }
  }
}

Configuration

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

Variable

Default

Purpose

DATABASE_URL

postgresql://shop:shop@localhost: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.

ParametersJSON Schema
NameRequiredDescriptionDefault
limitNoMaximum rows to return (1-50). Default 10.
customer_emailYesThe customer's email address; exact match, case-insensitive (no partial matches).

Output Schema

ParametersJSON Schema
NameRequiredDescription
ordersYes
customer_nameYes
customer_emailYes

TDQS

A4.3/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare the operation read-only, idempotent, and non-destructive. The description adds useful behavioral details beyond the annotations: result ordering, response contents, and explicit error behavior for an unknown email.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two tight sentences convey the result set, ordering, contents, and failure mode with no redundancy. All sentences earn their place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple read-only lookup with an output schema and full parameter documentation, the description covers the important non-obvious aspects: ordering, response fields, and the no-match error. Nothing needed for correct invocation is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 100%, with customer_email and limit fully described in the schema. The description does not add parameter-specific meaning beyond the schema, but it reinforces the email as the lookup key and implies the limit controls the number of recent orders.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly identifies the tool's output: a customer's most recent orders, ordered newest first, with status, line items, and total. It is distinct from sibling tools like search_products or sales_summary, which address different resources.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The intended use case is evident: retrieve order history for a specific customer by email. It does not name alternative tools or exclusion conditions, but the context is unambiguous and the sibling tools are clearly unrelated.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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.

ParametersJSON Schema
NameRequiredDescriptionDefault
thresholdNoProducts with stock strictly below this value are listed (0-10000). Default 10.

Output Schema

ParametersJSON Schema
NameRequiredDescription
productsYes
thresholdYes

TDQS

A4.2/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint, idempotentHint, and destructiveHint, covering the safety profile. The description adds valuable behavioral context: the sorting order (lowest stock first), the time window (last 30 days), and the included metrics (units sold and revenue). This goes beyond the annotations and helps the agent understand what the result set contains.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, well-structured sentence. It front-loads the primary action (products running low on stock) and appends the key details (lowest first, units sold, revenue, last 30 days). There is zero redundancy or unnecessary wording.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

The tool is simple with one optional parameter and an output schema present. The description covers the core behavior (which products are returned, sorting, metrics, time window). It does not mention pagination or response limits, but given the read-only nature and the presence of an output schema, this is not a significant gap. The description is complete enough for correct invocation.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema fully documents the single 'threshold' parameter with description, default, min, and max (schema coverage 100%). The description does not add any additional meaning about the parameter, so it relies entirely on the schema. Baseline 3 is appropriate given the high schema coverage.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool's purpose: it returns products running low on stock, sorted by lowest stock first, along with units sold and revenue over the last 30 days. This distinguishes it from siblings like top_products (which focuses on bestsellers) and sales_summary (aggregate sales). The verb+resource is specific and actionable.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies a clear use case ('to decide what to restock') but does not explicitly mention when not to use this tool or compare it with alternatives like top_products or sales_summary. The context is clear enough for an agent to infer, but explicit exclusions are missing.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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.

ParametersJSON Schema
NameRequiredDescriptionDefault
end_dateYesLast day included, YYYY-MM-DD (UTC).
group_byNoPeriod size for the breakdown. Default 'day'.day
start_dateYesFirst day included, YYYY-MM-DD (UTC). Must be on or before end_date; the range may span at most 731 days.

Output Schema

ParametersJSON Schema
NameRequiredDescription
periodsYes
end_dateYes
group_byYes
start_dateYes
total_ordersYes
total_revenueYes
average_order_valueYes

TDQS

A4/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already mark the tool read-only, idempotent, and non-destructive, so the description does not need to re-state safety. It adds valuable behavior beyond the schema: canceled orders are excluded, empty ranges return zero totals, and week grouping starts on Monday. These are meaningful edge-case disclosures.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

A single, information-dense sentence front-loads the core metrics, then covers date range, grouping options, exclusions, and empty-range behavior. Every clause earns its place; there is no filler or repetition of schema details.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the read-only annotations, a fully descriptive input schema, and an existing output schema, the definition covers the essential behavioral nuances: aggregation scope, grouping, cancellation exclusion, and zero-total behavior. Nothing an agent needs to decide whether and how to call this tool is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 100%, so the baseline is 3, but the description adds semantic value by specifying that 'week' grouping starts on Monday and that the result includes both a total and a per-period breakdown. This goes beyond the schema's terse 'Period size for the breakdown'.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly identifies the tool's output: revenue, order count, and AOV for a date range, with optional daily/weekly/monthly breakdowns. It does not use an explicit verb like 'returns' and does not name a sibling alternative, but the metrics and date scope make the purpose unmistakable and distinguish it from order/product/inventory siblings.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies when to use the tool—when aggregate sales metrics over a date range are needed—but it does not explicitly state when not to use it or point to alternatives such as get_customer_orders for per-customer detail. The grouping and exclusion behavior help, but there is no direct routing guidance.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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).

ParametersJSON Schema
NameRequiredDescriptionDefault
limitNoMaximum rows to return (1-50). Default 5.
queryYesWhat the customer is looking for, in natural language (e.g. 'gift for a runner').
categoryNoOptional exact category name, case-insensitive: Electronics, Home & Kitchen, Sports & Outdoors, Books, Clothing, Beauty & Personal Care. An unknown category returns no products.

Output Schema

ParametersJSON Schema
NameRequiredDescription
queryYes
categoryYes
productsYes

TDQS

A4/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint, idempotentHint, and destructiveHint, covering the safety profile. The description adds valuable behavioral context: it explains that the search is semantic (not keyword-based) and describes the output format, including a cosine similarity score with a threshold interpretation (~0.8 = strong match). This goes beyond the annotations and helps the agent interpret results.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is two concise sentences that front-load the core purpose and immediately explain the distinguishing feature (semantic matching). It also includes the key output detail (similarity score and threshold) without any fluff. Every sentence earns its place, making it an efficient and well-structured definition.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

The description is complete for an agent to call the tool correctly: it explains the semantic matching behavior, the return fields (price, stock, similarity score), and the threshold for a strong match. An output schema exists, so the return structure is further documented. The only minor omission is a mention of result ordering or pagination, but the limit parameter is described in the schema, so this is not a critical gap.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema provides full descriptions for all three parameters (query, limit, category) with a 100% coverage, so the baseline is 3. The description does not add any parameter-specific details beyond what the schema already includes; it mentions the query concept implicitly but does not clarify limit or category behavior. Thus it adds no extra parameter semantics.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the verb (semantic search) and resource (products), and highlights the distinctive feature that it matches meaning even without shared keywords. This distinguishes it from the sibling tools, which are focused on orders, sales, top products, and low stock alerts, all non-search utilities.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies usage for semantic product discovery but does not explicitly state when to prefer this tool over alternatives or when not to use it. It mentions 'even without shared keywords' which suggests a fuzzy-match use case, but no explicit guidance or exclusions are given. Since the siblings are clearly different (reporting/analytics vs. search), the lack of explicit direction is a minor gap.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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.

ParametersJSON Schema
NameRequiredDescriptionDefault
byNoRank by 'revenue' or units sold ('quantity').revenue
limitNoMaximum rows to return (1-50). Default 10.
end_dateYesLast day included, YYYY-MM-DD (UTC).
start_dateYesFirst day included, YYYY-MM-DD (UTC). Must be on or before end_date; the range may span at most 731 days.

Output Schema

ParametersJSON Schema
NameRequiredDescription
end_dateYes
productsYes
ranked_byYes
start_dateYes

TDQS

A4.3/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint, idempotentHint, and non-destructive behavior, so the safety profile is established. The description adds valuable behavioral context by stating that cancelled orders are excluded and that a salesless range returns an empty list, which helps set agent expectations.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is two sentences with no filler, front-loading the core purpose and adding an edge-case behavior second. Every word contributes to understanding the tool's function.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Combined with the complete input schema, an output schema, and annotations, the description covers the remaining operational nuances: date-bounded ranking, exclusion of cancelled orders, and empty-list behavior. Nothing essential for correct invocation is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Input schema coverage is 100%, with thorough descriptions for start_date, end_date, limit, and the by enum. The description does not need to repeat those details; it adds only the high-level ranking concept already implied by the schema.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description opens with 'Best-selling products between two dates, ranked by revenue or by units sold,' clearly specifying the verb, resource, and ranking dimension. This makes the tool's purpose distinct from siblings like get_customer_orders or sales_summary without ambiguity.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides clear context for when to use the tool: retrieving top products in a date range, optionally ranked by revenue or quantity. It does not explicitly name sibling alternatives or when-not-to-use conditions, but the use case is evident enough for an agent to select it appropriately.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

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

  1. 5 tool updatesv0.1.0
    • First observedget_customer_orders
    • First observedlow_stock_alert
    • First observedsales_summary
    • First observedsearch_products
    • First observedtop_products

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.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • A
    license
    Not graded
    quality
    B
    maintenance
    Enables LLMs to interact with a SQLite e-commerce database via safe, typed MCP tools with read-only guards and auth-gated mutations, plus a Claude agent for answering business questions.
    1
    MIT
  • F
    license
    Not graded
    quality
    C
    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
    B
    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
  • F
    license
    A
    quality
    B
    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
    -