mcp-ecommerce

An MCP server that lets an AI agent investigate stuck orders, surface fulfillment anomalies, and create escalation records — turning a multi-tab ops investigation into a single natural-language query.

Deployed on Railway with a PostgreSQL database. Connect any MCP-compatible client to the hosted URL and start querying immediately — no local setup required to evaluate.

---

Hosted MCP Server

https://ecom-mcp-production-f5bf.up.railway.app/sse

The server and its PostgreSQL database are both running on Railway. The /sse endpoint is live and accepting MCP client connections.

Connect via the included Groq client

git clone https://github.com/beinghadibadami/ecom-mcp.git
cd ecom-mcp
npm install
cp .env.example .env 
# add GROQ_API_KEY  and MCP_SERVER_URL=https://ecom-mcp-production-f5bf.up.railway.app

npm run client:remote -- "why is order ord_pick_002 stuck?"

The client prints each tool call and result as it runs, then a [final answer].

---

MCP Tools

Tool	Access	Description
get_order	Read	Full order details and line items for a given order ID
get_payment_status	Read	Payment state (captured / failed / refunded / pending) and amount
get_fulfillment_status	Read	Current stage, hours in stage, carrier, tracking, and error reason
list_flagged_orders	Read	Orders breaching SLA thresholds with hours overdue and escalation status
create_escalation	Write	Creates or returns an existing escalation (idempotent). Blocked at the business layer if no confirmed anomaly exists

Anomaly thresholds (hardcoded in src/constants.ts)

Stage	Threshold
Picking	8 hours
Packing	4 hours
Carrier handoff	48 hours

Thresholds are enforced in code — the model never invents or infers them.

---

Demo Orders (seeded synthetic data)

Order ID	Scenario
ord_clean_001	Fully delivered, no issues
ord_pick_002	Stuck in picking (~12 h, threshold: 8 h)
ord_pack_003	Stuck in packing (~7 h, threshold: 4 h)
ord_carrier_004	Stuck at carrier handoff (~60 h, threshold: 48 h)
ord_payfail_005	Failed payment, no fulfillment triggered
ord_escalated_006	Stuck in picking, already has an existing escalation

---

Architecture

Ops Engineer (natural-language query)
        ↓
Groq client — llama-3.3-70b-versatile
  drives multi-turn tool-call loop (max 10 iterations)
        ↓  MCP JSON-RPC over SSE
MCP Server (Railway) — SSEServerTransport
        ↓  pg connection pool
PostgreSQL (Railway) — orders, payments, fulfillments, escalations

Transport is selected by environment: MCP_TRANSPORT=http → SSE (Railway), otherwise stdio (local dev).

---

Key Product Decisions

Escalation enforcement — 3 layers

A write operation that creates a manager-review ticket needs to be safe. Enforcement works at three independent levels:

1. System prompt — instructs the model to only call create_escalation on explicit single-order investigation requests, never during general listing queries
2. Business layer — hasConfirmedAnomaly() re-queries the database before any insert; if no SLA breach or payment failure is confirmed, the function returns a blocked error and nothing is written
3. Database constraint — UNIQUE on escalations.order_id prevents duplicates even under concurrent requests

Listing vs. investigation intent

General listing queries ("show me flagged orders") call list_flagged_orders and return evidence only. Specific order queries ("investigate ord_pick_002") follow the full chain and may escalate. This distinction is enforced in the system prompt and backed by the business layer — listing can never accidentally trigger a write.

Idempotency

create_escalation called twice for the same order returns created: false with the existing record. The anomaly gate still runs on the second call, so only genuinely anomalous orders can ever hold an escalation.

---

Running Tests

Tests use Node's built-in test runner against a live local database — no mocking.

# make sure local DB is running and seeded first
npm test

Test	What it checks
Allowed path	ord_pick_002 (stuck in picking) → created: true
Blocked path	ord_clean_001 (delivered, healthy) → isError: true, error contains "blocked"
Idempotency	ord_escalated_006 called twice → both return created: false, same escalation ID
Payment failure	ord_payfail_005 (failed payment) → created: true

---

Out of Scope

Frontend, authentication, real payment provider integrations, and CI/CD are intentionally excluded per the assignment brief.