ecommerce-mcp-server
Provides read-only tools for querying an e-commerce PostgreSQL database, including semantic product search, customer orders, sales summaries, top products, and low-stock alerts.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@ecommerce-mcp-serverWhat were our top products by revenue last quarter?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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.

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] --> PGTools
Tool | What it answers |
| Semantic search (pgvector cosine distance) |
| A customer's orders with items and totals |
| Revenue, orders, average order value |
| Best sellers in a period |
| 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)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.pyThe 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 |
|
| PostgreSQL connection |
|
| Tool connection pool |
|
| FastEmbed model (must output 384-dim vectors) |
|
| Streamable HTTP bind address |
|
| Where the agent and UI reach the server |
| — | LLM access (OpenRouter, OpenAI-compatible) |
|
| OpenRouter API base URL |
|
| Chat model |
|
| Agent limits |
|
| Cost estimate shown per run |
| empty | Optional tracing (both required to enable it) |
|
| Langfuse instance URL |
Security
Read-only by construction: SELECT-only parameterized SQL, row limits, and database sessions opened with
default_transaction_read_only=onand 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/shopdatabase password. Compose publishes ports on127.0.0.1only; 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
docs/design.md: scope and requirementsdocs/architecture.md: layers, request flow and decisions with trade-offsdocs/code-map.md: generated module import graph
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 toolsget_customer_ordersARead-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.
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Maximum rows to return (1-50). Default 10. | |
| customer_email | Yes | The customer's email address; exact match, case-insensitive (no partial matches). |
Output Schema
| Name | Required | Description |
|---|---|---|
| orders | Yes | |
| customer_name | Yes | |
| customer_email | Yes |
TDQS
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.
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.
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.
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.
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.
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_alertARead-onlyIdempotent
Products running low on stock (lowest first) with their units sold and revenue in the last 30 days, to decide what to restock.
| Name | Required | Description | Default |
|---|---|---|---|
| threshold | No | Products with stock strictly below this value are listed (0-10000). Default 10. |
Output Schema
| Name | Required | Description |
|---|---|---|
| products | Yes | |
| threshold | Yes |
TDQS
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.
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.
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.
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.
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.
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_summaryARead-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.
| Name | Required | Description | Default |
|---|---|---|---|
| end_date | Yes | Last day included, YYYY-MM-DD (UTC). | |
| group_by | No | Period size for the breakdown. Default 'day'. | day |
| start_date | Yes | First day included, YYYY-MM-DD (UTC). Must be on or before end_date; the range may span at most 731 days. |
Output Schema
| Name | Required | Description |
|---|---|---|
| periods | Yes | |
| end_date | Yes | |
| group_by | Yes | |
| start_date | Yes | |
| total_orders | Yes | |
| total_revenue | Yes | |
| average_order_value | Yes |
TDQS
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.
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.
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.
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.
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.
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_productsARead-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).
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Maximum rows to return (1-50). Default 5. | |
| query | Yes | What the customer is looking for, in natural language (e.g. 'gift for a runner'). | |
| category | No | Optional exact category name, case-insensitive: Electronics, Home & Kitchen, Sports & Outdoors, Books, Clothing, Beauty & Personal Care. An unknown category returns no products. |
Output Schema
| Name | Required | Description |
|---|---|---|
| query | Yes | |
| category | Yes | |
| products | Yes |
TDQS
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.
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.
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.
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.
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.
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_productsARead-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.
| Name | Required | Description | Default |
|---|---|---|---|
| by | No | Rank by 'revenue' or units sold ('quantity'). | revenue |
| limit | No | Maximum rows to return (1-50). Default 10. | |
| end_date | Yes | Last day included, YYYY-MM-DD (UTC). | |
| start_date | Yes | First day included, YYYY-MM-DD (UTC). Must be on or before end_date; the range may span at most 731 days. |
Output Schema
| Name | Required | Description |
|---|---|---|
| end_date | Yes | |
| products | Yes | |
| ranked_by | Yes | |
| start_date | Yes |
TDQS
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.
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.
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.
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.
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.
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.
5 tool updates
v0.1.0- First observed
get_customer_orders - First observed
low_stock_alert - First observed
sales_summary - First observed
search_products - First observed
top_products
TDQS
Scored across 5 tools
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.
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.
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.
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
Related MCP Connectors
Hosted MCP for e-commerce: live product catalog, stock, and pricing for AI agents.
MerchantFlow is a hosted, read-only ecommerce analytics MCP server for Shopify and WooCommerce. It gives AI assistants tenant-scoped access to revenue, profit and loss, product and SKU profitability, COGS coverage, fulfillment costs, advertising spend, ROAS, marketing performance, cohorts, LTV, and business valuation across connected commerce and marketing platforms. Connect with OAuth over Streamable HTTP. No local server installation is required.
Agentic commerce with 58 MCP tools for product search, checkout, A2A negotiation, C-Suite analytics.
Agentic commerce gateway: discovery, search, checkout across Shopify/Woo/Odoo/PrestaShop.
Related MCP Servers
- AlicenseNot gradedqualityBmaintenanceEnables 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.1MIT
- FlicenseNot gradedqualityCmaintenanceEnables safe, read-only analysis of an online store's SQLite database, providing schema introspection, restricted SELECT queries, and specialized analytics tools through MCP.-
- AlicenseAqualityBmaintenanceA 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.8MIT
- FlicenseAqualityBmaintenanceEnables 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-