# mcp-server-playground

A hands-on MCP (Model Context Protocol) server built over a SQLite mock database,
for learning how an AI client discovers and calls your own tools.

The dataset is a fictional general online store — 1,200 customers, 1,358 SKUs
across 540 distinct products, 25,000 orders, 61,835 line items and 4,000
reviews, spanning 2024-01 to 2026-07.

Ten categories, deliberately spanning an order of magnitude in price:

```
家電  食品  日用品  ファッション  書籍  インテリア  スポーツ  文具  ペット用品  コスメ
      ¥160 (パスタソース) … ¥36,480 (ハンディクリーナー 上位モデル)
```

The data is shaped rather than uniformly random, so aggregating it yields
findings instead of noise:

- **Seasonality.** December runs ~1.65× a normal month, February ~0.8×.
- **Growth.** Volume trends up ~1.8% per month across the whole period.
- **Concentration.** A minority of SKUs and a minority of customers account for
  most of the volume, so rankings and customer segments are not flat.
- **Brands.** 9 brands × 60 product types, each brand with its own price
  positioning. `sales_summary` groups by brand, so "which brand sells best" is
  answerable — and the answer is not the obvious one: プレミアムセレクト leads on
  revenue while デイリープラス takes 70% more orders to reach last place.
- **Price vs volume.** 日用品 has the most orders but the least revenue;
  家電 is the reverse. Ranking by revenue and by units gives different answers,
  which is exactly the distinction `top_products` exposes.

## Why this stack

| Choice | Reason |
| --- | --- |
| TypeScript on Node ≥24.10 | Node's type stripping runs `.ts` files directly — no build step, no `dist/` |
| `node:sqlite` | SQLite is built into Node; no native module to compile |
| Three transports | stdio, Streamable HTTP, and Streamable HTTP with OAuth 2.1 |
| Few dependencies | `@modelcontextprotocol/sdk` and `zod`; `express` only for the OAuth server |

`src/server.ts` defines *what* the server exposes; `src/stdio.ts` and
`src/http.ts` define *how* it is reached. The tool definitions know nothing
about either transport, which is why supporting both cost one extra file.

```
src/server.ts        tools, resources, prompts  (transport-agnostic)
src/db.ts            connections + read-only guard
src/seed.ts          deterministic mock data
src/stdio.ts         entry point: stdin/stdout
src/http.ts          entry point: Streamable HTTP
src/http-oauth.ts    entry point: Streamable HTTP + OAuth 2.1
src/auth-provider.ts in-memory authorization server for the demo
src/session-store.ts HTTP session bookkeeping: idle timeout + cap
```

Node 24.10 is the floor because of `DatabaseSync.setAuthorizer()`, which the
read-only connection depends on. Type stripping itself only needs 23.6.

Three entry points, one `server.ts`. Adding OAuth did not change a single tool
definition — that separation is the whole point of the layout.

## Quick start

```bash
npm install
npm run tools        # print everything the server advertises
npm run smoke        # stdio: acts as an MCP client and exercises everything
npm run smoke:http   # Streamable HTTP: same, plus two concurrent sessions
npm run smoke:oauth  # OAuth 2.1: the whole authorization flow, step by step
```

`npm run tools` shows exactly what the model receives — name, description and
argument schema, nothing more. If a tool reads as ambiguous there, it reads as
ambiguous to the model too, which makes it the fastest way to review a tool
definition you just wrote.

`npm run smoke` is the fastest way to see the whole protocol in action: it spawns
the server exactly like a real client does, lists the capabilities, calls every
tool, reads both resources, fetches a prompt, and finally proves that a restart
wipes the database back to its initial state.

There is no separate setup step — the server builds `data/shop.db` itself on
start. `npm run seed` exists only for rebuilding the file by hand, e.g. to poke
at it with the `sqlite3` CLI.

## A fresh database on every start

The server deletes and rebuilds `data/shop.db` each time it starts, so every
session begins from the same known state and nothing a previous session wrote
survives. The seed uses a fixed PRNG value, so "rebuilt" means the identical
dataset every time — you can memorise a number from one session and still
recognise it in the next.

Rebuilding ~93,000 rows costs about 250ms (all inserts run in one transaction),
which is invisible next to process startup.

```
playground-shop: database reset in 249ms — customers=1200  products=1358  orders=25000  order_items=61835  reviews=4000
```

To keep data between runs instead, set `MCP_SQLITE_PERSIST=1`:

```json
{
  "mcpServers": {
    "playground-shop": {
      "command": "node",
      "args": ["src/stdio.ts"],
      "env": { "MCP_SQLITE_PERSIST": "1" }
    }
  }
}
```

One consequence worth knowing: because each client spawns its own server
process, opening MCP Inspector while Claude Code is already connected resets the
database underneath Claude Code's session. Run one client at a time, or use
`MCP_SQLITE_PERSIST=1` when you deliberately want them to share state.

## What the server exposes

MCP has three kinds of capability. This server implements all three.

### Tools — actions the model chooses to call

| Tool | Purpose |
| --- | --- |
| `list_tables` | Every table with its row count. The natural entry point. |
| `describe_table` | Column definitions plus three sample rows. |
| `search_products` | Keyword, brand, category, price range and stock filters. |
| `search_reviews` | Full-text search over review bodies, backed by SQLite FTS5. |
| `customer_orders` | Resolve a customer by id/email/name, return orders with items joined. |
| `sales_summary` | Revenue aggregated by month, category, brand, prefecture or status. |
| `top_products` | Ranking by revenue, units sold or average rating. |
| `add_review` | The one tool that writes. Inserts a review row. |

**There is no raw-SQL tool, on purpose.** An earlier version had one, and it is
worth understanding why it is gone — see [Why no raw-SQL tool](#why-no-raw-sql-tool).

The two text searches (`search_products.keyword`, `customer_orders.name`) split
the query on whitespace and require *every* term, rather than matching the whole
string. Word order stops mattering, which matters here because product names are
`<brand> <product>（<size>）` and a model has no reason to guess that order:

```
"匠工房 加湿器"  →  匠工房 超音波加湿器（標準）
"加湿器 匠工房"  →  same result
"匠工房超音波"   →  no match — terms are matched individually, not fuzzily
```

`%` and `_` are escaped, so a query of `%` finds nothing rather than everything.

Results are ordered by `sort`, which defaults to `relevance`. There is no real
relevance score behind `LIKE`, so it approximates one with name length: the
shortest matching name is the plainest product. That matters because ordering by
price put the answer to "find me a ballpoint pen" at ¥1,660 for a premium
5-pack, when a ¥200 single pen also matched:

```
sort=relevance   グッドデイ ゲルインクボールペン（1個）        ¥200
sort=price_desc  プレミアムセレクト ゲルインクボールペン（5個セット） ¥1,660
```

## Full-text search with FTS5

`search_reviews` searches review *bodies*, which `LIKE` over 4,000 rows would
scan linearly. It is backed by an FTS5 virtual table, and getting that working
for Japanese took two non-obvious decisions.

**The default tokenizer is useless for Japanese.** FTS5's `unicode61` splits on
whitespace and punctuation. A Japanese sentence has neither, so the whole
sentence becomes one token and nothing short of an exact match ever hits:

```
unicode61, indexing "音が静かで満足しています"
  MATCH '静か'   →  0 rows
```

`tokenize='trigram'` indexes every 3-character window instead, which has no
notion of word boundaries and therefore does not need one:

```
trigram, same text
  MATCH '音が静か'  →  1 row
  MATCH '静か'      →  0 rows   ← still nothing
```

**Trigram has a hard floor of three characters.** Two-character queries — 静か,
破損, 満足, all perfectly ordinary Japanese words — cannot match, ever. The tool
therefore routes each term by length: three or more characters go to the index,
shorter ones fall back to a `LIKE` scan. Mixed queries use both, and the
response says which path ran:

```
"動作音が大きくて"  →  1 review(s) via full-text index (動作音が大きくて)
"静か"             →  1 review(s) via LIKE scan
"洗濯 縮み"         →  1 review(s) via LIKE scan
```

The index earns its place even at this size — measured over 4,000 reviews:

```
FTS5 MATCH    0.059 ms/query
LIKE %...%    0.574 ms/query     ~10x slower
```

Two implementation notes worth knowing if you add FTS5 to your own server:

- **The authorizer needs `PRAGMA data_version`.** FTS5 reads it internally to
  validate its cache. Without it every FTS query fails with a bare
  `authorization denied` and nothing indicates which pragma was refused.
- **Shadow tables are hidden from `list_tables`.** An FTS5 table brings five
  companions (`_data`, `_idx`, `_content`, `_docsize`, `_config`). They are
  storage internals; listing them would waste the model's context and invite it
  to query them directly.

Writes stay in sync through triggers, so a review added via `add_review` is
immediately findable by `search_reviews`.

Query terms are quoted before being handed to FTS5, so review text containing
FTS5 operators is treated as literal text rather than syntax.

### Resources — context the client reads, not calls

- `schema://database` — full DDL for every table.
- `table://{table}/sample` — first five rows as JSON. A URI *template*: the
  server also advertises one concrete URI per table and supports completion,
  so a client can offer `table://orders/sample` as an autocomplete suggestion.

### Prompts — user-triggered templates

- `monthly_report` (`month`) — draft a sales report for one month.
- `product_deep_dive` (`keyword`) — investigate one product end to end.

In Claude Code these appear as `/mcp__playground-shop__monthly_report`.

## Why no raw-SQL tool

A `run_sql` tool taking a `SELECT` string is the obvious way to make a database
useful to a model, and it was the first tool this project had. Removing it was
the single biggest security improvement, so the reasoning is worth recording.

Guarding it took three layers, and measurement showed each one mattered:

| Attack | Stopped by | Why |
| --- | --- | --- |
| `SELECT 1; DROP TABLE reviews` | SQLite itself | `prepare()` compiles only the first statement |
| `DELETE FROM reviews` | all three layers | write, in every sense |
| `/* hi */ DELETE FROM ...` | regex | but it also rejected valid commented queries |
| `ATTACH DATABASE '/etc/passwd'` | regex + authorizer | would expose other database files |
| `PRAGMA database_list` | regex + authorizer | leaks absolute filesystem paths |
| `SELECT readfile('/etc/passwd')` | nothing — luck | the function simply does not exist in `node:sqlite` |
| unbounded `WITH RECURSIVE` | authorizer only | hung the server for as long as it was left running |

Two attacks had no answer at all:

- **`SELECT * FROM customers` reads every row.** It is a valid query, so no layer
  has grounds to refuse it. Allowing raw SQL over real data means allowing the
  model to read all of that data.
- **A big enough cross join blocks the process.** `DatabaseSync` is synchronous,
  so a slow query freezes the server. Measured: `worker_threads` +
  `terminate()` does *not* help, because the loop is inside SQLite's C code and
  never returns control to V8. Only `child_process` + `SIGKILL` actually stops
  it — which is a lot of machinery to bolt onto one tool.

The purpose-built tools need none of this. Their input is typed arguments, the
SQL is written by hand in `server.ts`, and business rules (excluding cancelled
orders, sensible defaults) are enforced in one place instead of hoped for. They
also cost fewer tokens and cannot produce a syntax error.

The trade-off is real: questions no tool covers now go unanswered instead of
being improvised. That is the intended behaviour — the server's `instructions`
tell the model to say so rather than guess.

## Safety model

The database still gets defence in depth, because a bug in a hand-written query
should fail loudly rather than corrupt data.

**A SQLite authorizer.** The read-only connection installs `setAuthorizer()`,
which SQLite consults for every operation the parser attempts. Only
`SQLITE_SELECT`, `SQLITE_READ` and `SQLITE_FUNCTION` pass, plus `PRAGMA
table_info` for `describe_table`. `SQLITE_RECURSIVE` is denied, so an unbounded
recursive CTE can never start.

**A read-only connection.** `readOnly: true` means a write fails even if the
authorizer were misconfigured. `add_review` uses a separate writable connection.

**Tool annotations.** `readOnlyHint: true` on every query tool and
`readOnlyHint: false` on `add_review` — that is the signal a client uses to
decide whether to ask the user for confirmation.

Still not covered: **no authentication on the HTTP transport** (see the
Streamable HTTP section), and the seeded data is fictional, so nothing here is
tuned for real personal data. For that, `SQLITE_IGNORE` in the authorizer can
mask individual columns — returning `NULL` for, say, `customers.email` even
under `SELECT *` and in `WHERE` clauses.

## Connecting a client

### Claude Code — project scope (recommended)

`.mcp.json` in this repository already declares the server. Start Claude Code
from this directory and approve the server when prompted:

```bash
cd ~/projects/mcp-server-playground
claude
```

Then `/mcp` shows the connection status and the available tools.

### Claude Code — available everywhere

```bash
claude mcp add playground-shop --scope user -- node ~/projects/mcp-server-playground/src/stdio.ts
```

Use an absolute path: the client sets its own working directory.

### Claude Code — one-off, non-interactive

```bash
claude -p "2026年6月のカテゴリ別売上を教えて" \
  --mcp-config ~/projects/mcp-server-playground/.mcp.json \
  --allowedTools "mcp__playground-shop__sales_summary,mcp__playground-shop__top_products"
```

### Claude Desktop

Add to `~/Library/Application Support/Claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "playground-shop": {
      "command": "node",
      "args": ["/Users/YOU/projects/mcp-server-playground/src/stdio.ts"]
    }
  }
}
```

### Streamable HTTP

Start the server yourself and let clients connect over the network:

```bash
npm run start:http
# playground-shop MCP server listening on http://127.0.0.1:4871/mcp
```

```bash
claude mcp add --transport http playground-shop http://127.0.0.1:4871/mcp
```

Or non-interactively:

```bash
claude -p "レビュー平均評価の高い商品トップ3は？" \
  --mcp-config '{"mcpServers":{"playground-shop":{"type":"http","url":"http://127.0.0.1:4871/mcp"}}}' \
  --allowedTools "mcp__playground-shop__top_products"
```

How it differs from stdio:

| | stdio | Streamable HTTP |
| --- | --- | --- |
| Process | One per client, spawned by the client | One long-lived server, started by you |
| Clients | Exactly one | Many, each with its own session |
| Addressing | A command line | A URL |
| Lifetime | Dies with the client | Runs until you stop it |
| Database reset | Every client launch | Once per server start — clients share the data |

Endpoints: `POST /mcp` for JSON-RPC, `GET /mcp` for the server-to-client SSE
stream, `DELETE /mcp` to end a session, plus a non-MCP `GET /health` that
reports the active session count.

Sessions are stateful: `initialize` returns an `mcp-session-id` header that
later requests must carry, and each session gets its own `McpServer` instance.
DNS rebinding protection is on, so the `Host` header must match the address the
server is listening on — a browser page cannot quietly reach your local server.

There is no authentication on this entry point. It binds to `127.0.0.1`
deliberately. For an authenticated server, see the next section.

Environment: `MCP_HTTP_PORT` (default `4871`), `MCP_HTTP_HOST` (default `127.0.0.1`).

**Sessions are bounded.** A plain `Map<sessionId, transport>` is enough to make
the protocol work, but clients disconnect without sending `DELETE /mcp` — a
crashed process, a closed laptop — and each abandoned session pins an
`McpServer`, a transport and any open SSE stream for the life of the process.
`src/session-store.ts` adds an idle timeout (30 min) and a cap (100 sessions,
past which new ones get a 503). Reads refresh the timer, so an active session is
never swept.

### Streamable HTTP with OAuth 2.1

```bash
npm run start:oauth
# playground-shop MCP server (OAuth) listening on http://127.0.0.1:4872/mcp
```

```bash
claude mcp add --transport http playground-shop http://127.0.0.1:4872/mcp
```

Claude Code then runs the whole flow itself — discovery, dynamic client
registration, opening a browser for the consent screen, token exchange and
refresh. You never paste a client id or a token anywhere.

`npm run smoke:oauth` walks the same flow by hand, one labelled step at a time,
which is the fastest way to see what is actually happening:

```
1.  unauthenticated request  → 401 + WWW-Authenticate: Bearer …, resource_metadata="…"
2.  GET /.well-known/oauth-protected-resource/mcp   → which server guards this API
3.  GET /.well-known/oauth-authorization-server     → where /authorize, /token, /register are
4.  POST /register                                  → dynamic client registration (RFC 7591)
5.  GET /authorize?…&code_challenge=…               → consent screen
6.  user approves                                   → redirect back with ?code=…
7.  POST /token with the PKCE verifier              → access + refresh token
8.  bad token                                       → 401 invalid_token
9.  MCP client with the access token                → tools/list, tools/call work
10. read-only token calls add_review                → rejected, missing playground-shop:write
11. full-scope token calls add_review               → succeeds
12. POST /token grant_type=refresh_token            → new access token
```

**Scopes.** `playground-shop:read` is required for any MCP request; `add_review`
additionally requires `playground-shop:write`. The check lives in `server.ts` and reads
`extra.authInfo`, which the transport populates. Under stdio and the plain HTTP
server `authInfo` is undefined and the tool stays open — the check applies
exactly where there are scopes to check, without making the tool definition
transport-aware.

Environment: `MCP_HTTP_PORT` (default `4872`), `MCP_HTTP_HOST` (default `127.0.0.1`).

#### Not production ready

The authorization server is a teaching implementation:

- **Everything is in memory.** Restart the process and every registered client
  and issued token is gone.
- **No user authentication.** The consent screen has no login — anyone who can
  reach `/authorize` can approve. A real server authenticates a user first and
  binds the token to them.
- **Tokens are opaque random strings** in a `Map`, not signed JWTs, so they
  cannot be verified by anything but this process.
- **Plain HTTP on localhost.** OAuth 2.1 requires TLS everywhere but loopback.
- **Anyone may register.** Dynamic client registration is unauthenticated and
  unthrottled beyond the SDK's default rate limiting.

What it *does* demonstrate faithfully is the protocol: discovery, PKCE,
single-use authorization codes, scope enforcement and refresh.

### MCP Inspector — the GUI debugger

```bash
npm run inspect
```

Opens a browser UI where you can see the raw JSON-RPC traffic, call tools with
arbitrary arguments and inspect errors. This is the tool to reach for while
developing a new capability.

## Things to try

1. Ask "先月いちばん売れた商品は？" and watch which tool the model picks.
2. Ask something no tool covers ("都道府県ごとの平均客単価は？") and watch what
   the model does when it cannot answer. This is the cost of dropping raw SQL —
   decide for yourself whether a `prefecture_stats` tool is worth adding.
3. Ask it to delete all reviews, and watch it discover there is no such tool.
4. Ask it to post a 5-star review, and see the client ask for confirmation
   because of `readOnlyHint: false`. Restart the client and the review is gone.
5. Delete a tool description in `src/server.ts`, restart, and observe how much
   worse the tool selection gets. Descriptions are the model's only documentation.

## Environment

- `MCP_SQLITE_DB` — override the database path. Defaults to `data/shop.db`
  resolved relative to the project root, not the working directory.
- `MCP_SQLITE_PERSIST=1` — skip the reset and reuse the existing database.
  Still seeds it if the file does not exist yet.

## Gotchas worth knowing

- **stdout belongs to the protocol.** A stray `console.log` in server code
  corrupts the JSON-RPC stream and the client drops the connection. Log to
  stderr instead — `src/stdio.ts` does.
- **The client controls the working directory.** Always resolve file paths from
  `import.meta.dirname`, as `src/db.ts` does.
- **Tool descriptions are the interface.** The model sees only the name,
  description and JSON schema. A vague description is a broken tool.