ecommerce-mcp-server
This MCP server provides safe, read-only access to an e-commerce PostgreSQL database for product search, order lookup, sales analytics, and inventory alerts.
Search products semantically with pgvector, optionally filtered by category.
Fetch a customer's recent orders, line items, and totals by email.
Generate sales summaries with revenue, orders, and AOV by day, week, or month.
Rank top products by revenue or units sold over a date range.
List low-stock products with last-30-day sales to support restocking.
Read the
schema://tablesresource to understand the data model.Use it from Claude Desktop, an agent CLI, or a Streamlit chat UI via the included LangGraph agent.
Rely on read-only, parameterized, row-limited queries with validated arguments and clear errors.
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-server What 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-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@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 |
|
| 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.
Inputs: customer_email (required), limit. Returns: orders, customer_name, customer_email
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.
Inputs: threshold. Returns: products, threshold
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.
Inputs: end_date (required), start_date (required), group_by. Returns: periods, end_date, group_by, start_date, +3 more
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).
Inputs: query (required), limit, category. Returns: query, category, products
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.
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
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.
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.
Shopify store analysis and product-catalog extraction as agent tools.
Related MCP Servers
- FlicenseNot gradedqualityDmaintenanceEnables safe, read-only analysis of an online store's SQLite database, providing schema introspection, restricted SELECT queries, and specialized analytics tools through MCP.-
- AlicenseAqualityDmaintenanceA 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
- AlicenseAqualityDmaintenanceEnables natural-language querying and analysis of an e-commerce SQLite database via MCP, with read-only SQL execution, table inspection, and AI-generated answers.4MIT
- AlicenseAqualityBmaintenanceEnables 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.5MIT