# MCP E-commerce Server

A comprehensive Model Context Protocol (MCP) server for e-commerce product management with CRUD operations, AI-powered descriptions, and database integration.

Agents and MCP clients (Cursor, Claude Desktop, MCP Inspector, Apps SDK) can create products, look them up, delete them, browse the catalog, and generate copy through tools, resources, and prompts. Product data lives in MySQL.

## Features

- 🛍️ **Complete CRUD Operations** — Create, read, update, and delete products (MCP tools plus the service/demo layer)
- 🤖 **AI-Powered Descriptions** — Automatic product description generation using MCP sampling when you skip a description
- 📊 **Database Integration** — MySQL with a dedicated `products` schema, unique SKUs, and timestamps
- 🔍 **Smart Search** — Search products by name with limit/offset pagination
- 📦 **Low Stock Monitoring** — Resource for products with quantity below 5
- 🌐 **Dual Transport Support** — Stdio for MCP clients; HTTP/SSE types exported from the library for further wiring
- 🔧 **TypeScript** — Fully typed with Zod validation on inputs
- 📋 **MCP Resources** — Full catalog (`products://catalog`) and low-stock (`products://low-stock`)
- 🎯 **MCP Prompts** — Pre-built prompt template for marketing-style product descriptions

## Quick start

```bash
# 1) Install deps
npm i

# 2) Prepare env
cp .env.example .env

# 3) Create schema (optional, run in your MySQL)
# See sql/schema.sql

# 4) Run demo (non-MCP) usage
npm run dev:demo

# 5) Run MCP server (stdio transport)
npm run dev:mcp
```

Put your real MySQL password in `.env` (not in `.env.example`). The MCP server prints nothing special; it waits on stdio for a client like Claude Desktop, MCP Inspector, Apps SDK, or Cursor to connect.

Environment variables (see `.env.example`):

| Variable | Meaning | Example |
|----------|---------|---------|
| `MYSQL_HOST` | Server host | `localhost` |
| `MYSQL_PORT` | Port | `3306` |
| `MYSQL_USER` | User | `root` |
| `MYSQL_PASSWORD` | Password | your password |
| `MYSQL_DATABASE` | Database name | `e_commerce_mcp` |

This repo already includes `.cursor/mcp.json`, which starts `npx tsx src/mcp/server.ts` with that `.env` file.

## How it is put together

| Layer | Location | Role |
|-------|----------|------|
| MCP server | `src/mcp/server.ts` | Tools, resources, and prompts |
| Service | `src/services/ProductService.ts` | Zod validation and business calls |
| Repository | `src/repo/ProductRepository.ts` | SQL against `products` |
| Model | `src/models/Product.ts` | Product type |
| Database pool | `src/db.ts` | `mysql2` pool from environment variables |
| Demo CLI | `src/index.ts` | Create → read → update → list → delete without MCP |

`src/index.ts` also re-exports `ProductService` and `Product` so other Node code can use the same library.

## Product data model

Each row in `products` (`sql/schema.sql`) has:

| Field | Type | Notes |
|-------|------|--------|
| `id` | integer | Auto-increment primary key |
| `sku` | string (max 64) | Unique stock-keeping unit |
| `name` | string (max 255) | Required |
| `description` | text | Optional |
| `price` | decimal(10,2) | Non-negative; default `0.00` |
| `quantity` | integer | Non-negative; default `0` |
| `created_at` | timestamp | Set on insert |
| `updated_at` | timestamp | Updated on every change |

There is an index on `name` (`idx_products_name`) to support search.

## MCP tools

Registered on server `ecommerce-custom-mcp` (v1.0.0). Inputs are validated with Zod.

**`add_product`** — Create a product. Required: `sku`, `name`, `price` (≥ 0), `quantity` (integer ≥ 0). Optional: `description`. Returns the inserted row, including `id` and timestamps.

**`add_product_smart`** — Same fields. If `description` is omitted, the server asks the connected model (MCP sampling, up to 100 tokens) for a short 2–3 sentence description from the name and price. If you pass a description, sampling is skipped.

**`get_product_by_id`** — Fetch one product by positive integer `id`. Missing ids return `product {id} not found`.

**`delete_product`** — Delete by `id`. Returns `{ deleted: true }` or `{ deleted: false }`.

List, update, and name search are available on `ProductService` (demo, catalog resource, repository). They are not separate MCP tools.

## MCP resources and prompt

| Name | URI | Behavior |
|------|-----|----------|
| `products-catalog` | `products://catalog` | Up to 200 products, newest `id` first |
| `low-stock-products` | `products://low-stock` | Same list, then quantity below 5 |

**`generate-product-description-template`** takes `productName` (required) plus optional `features` and `targetAudience`. It asks the model for a 5–8 word headline, 2–3 paragraphs, 3–5 benefit bullets, and a call to action.

## Service and database

`ProductService` and `ProductRepository` support create, get by id, get by SKU, partial update, delete, list (`ORDER BY id DESC` with limit/offset), and search by name (`LIKE` with the same pagination). The pool in `src/db.ts` uses a limit of 10 connections, a 60s idle timeout, and keep-alive.

`npm run dev:demo` inserts `DEMO-001`, reads it, updates price/quantity, lists up to 10 rows, deletes the demo product, and closes the pool — useful to verify MySQL before attaching an MCP client.

## Scripts and layout

| Script | What it does |
|--------|----------------|
| `npm i` | Install dependencies |
| `npm run dev:demo` | CRUD smoke test against MySQL |
| `npm run dev:mcp` | MCP server on stdio (`tsx`) |
| `npm run check` | Typecheck without emit |
| `npm run build` | Compile to `dist/` |

After a build, the binary name is `ecommerce-products-mcp` (`dist/mcp/server.js`).

```
src/
  mcp/server.ts
  services/ProductService.ts
  repo/ProductRepository.ts
  models/Product.ts
  db.ts
  index.ts
sql/schema.sql
.cursor/mcp.json
.env.example
```
## Author

**Santhan Sai**