# ecommerce-sql-mcp

A Model Context Protocol (MCP) server that exposes a small e-commerce SQLite
database as tools, plus an agent that answers natural-language questions by
driving those tools over the real MCP wire protocol (streamable HTTP, mcp 2.x).

An agent asks "which product category sold the most units?", the MCP server
discoverable tools let it inspect the schema, write a `SELECT`, and read the
result — all through the standard MCP tool-call round-trip.

```
┌────────────┐    MCP streamable HTTP    ┌──────────────┐     ┌──────────┐
│  DeepSeek  │ ◀──── tools / result ───▶ │  MCP server  │ ──▶ │  SQLite  │
│  (agent)   │                           │  (mcp 2.x)   │     │ ecommerce│
└────────────┘                           └──────────────┘     └──────────┘
```

## Tools

| Tool          | Read/write | What it does                                         |
|---------------|-----------|------------------------------------------------------|
| `list_tables` | read      | Enumerate tables with row counts                     |
| `get_schema`  | read      | Columns, foreign keys, and up to 3 sample rows       |
| `run_query`   | read      | Run a single `SELECT`/`WITH`; capped rows, no writes |
| `run_mutation`| write     | `INSERT`/`UPDATE`/`DELETE`, gated by `confirm=True`  |

## Design decisions

- **Read-only at the storage layer.** `run_query` opens SQLite with `file:...?mode=ro`,
  so even a slipped keyword can't write. A token guard rejects non-`SELECT` and
  every write keyword, plus multi-statement (`;`) and trailing-semicolon inputs.
- **Writes are explicitly gated.** `run_mutation` refuses unless `confirm=True`;
  the agent must state the change before a side effect happens. This is a small
  but real "permissioned write" boundary, the same shape as OAuth-2.1-tool
  designs meant for production.
- **Errors feed the model, not the user.** SQL errors are returned with a hint
  ("table X doesn't exist → list_tables(); no such column → get_schema()"), the
  MCP-idiomatic way to let the agent self-correct instead of returning a bare
  stack message.
- **Reproducible data.** The seed writes a fixed-RNG dataset, so the same
  question yields the same rows run over run.

## Running

```bash
python -m venv .venv && .venv/Scripts/python -m pip install -r requirements.txt

# 1. Build the sample DB
.venv/Scripts/python scripts/seed_db.py

# 2. Smoke-test the MCP protocol end to end (no LLM needed)
.venv/Scripts/python scripts/test_mcp.py

# 3. Ask the agent a question (needs an OpenAI-compatible LLM endpoint)
set MCP_LLM_BASE=https://api.deepseek.com
set MCP_LLM_MODEL=deepseek-v4-flash
set MCP_LLM_KEY=sk-...
.venv/Scripts/python scripts/demo.py "销量最高的商品类别是哪个?卖了多少件?"
```

## Layout

```
server/   MCP server: db.py (safety + introspection), main.py (MCPServer + tools)
client/   agent: llm.py (thin OpenAI-compatible client), agent.py (tool-call loop)
scripts/  seed_db.py, test_mcp.py, demo.py
data/     generated ecommerce.db (gitignored)
```

## Limitations

- Single SQLite file, local-only; no persistence layer beyond the seed.
- The agent talks to exactly one MCP server endpoint.
- `max_rows`/statement-length caps and the token guard are the current safety
  boundary; a production version would add a DB role with `SELECT`-only on a
  separate connection and per-request auth.
- DeepSeek's `reasoning_content` must be echoed back in the assistant message on
  follow-up turns (handled in `client/agent.py`); providers without that field
  simply omit it.