# Shop Database MCP Server

A local, read-only Model Context Protocol server for exploring and analyzing the included educational SQLite shop fixture. It exposes exactly three tools: `list_tables`, `describe_table`, and `query_database`.

The committed `shop.db` contains synthetic educational country values. They are deterministic fixture data, not inferred personal attributes.

## 1. Install

Prerequisites: Node.js 20 or newer, npm, and a platform supported by `better-sqlite3` (or a local C/C++ build toolchain if npm cannot obtain a prebuilt native binary).

```bash
npm install
```

## 2. Configure

No configuration is required when using the included `shop.db`. To select a different compatible database, set an absolute or relative path:

```bash
export SHOP_DB_PATH=/path/to/shop.db
```

The server resolves the default database relative to its own module, not the caller's working directory. It will never create a missing database. See `.env.example`; environment files are not loaded automatically.

## 3. Prepare or verify the fixture

```bash
npm run prepare-db
```

This explicit setup command atomically adds or validates the deterministic synthetic `customers.country` fixture and its index. It preserves existing customer fields and IDs and is idempotent. It is never invoked by `start`, `dev`, or server runtime. The repository already contains the prepared database, so this normally acts as verification.

## 4. Build

```bash
npm run typecheck
npm run build
```

## 5. Run

```bash
npm run start
```

The process speaks MCP over stdin/stdout and waits for a client. It does not print a startup banner; stdout is reserved exclusively for MCP messages. Development mode is available as `npm run dev`.

## 6. Connect

A generic stdio MCP configuration is provided in `configuration.example.json`:

```json
{
  "mcpServers": {
    "shop_database": {
      "command": "node",
      "args": ["/absolute/path/to/project/dist/server.js"],
      "env": { "SHOP_DB_PATH": "/absolute/path/to/project/shop.db" }
    }
  }
}
```

For Codex CLI, either add the server at the command line:

```bash
codex mcp add shop_database --env SHOP_DB_PATH=/absolute/path/to/project/shop.db -- node /absolute/path/to/project/dist/server.js
codex mcp list
```

Or copy the template from `config/codex-config.example.toml` into your Codex configuration and replace the placeholders:

```toml
[mcp_servers.shop_database]
command = "node"
args = ["/absolute/path/to/project/dist/server.js"]
tool_timeout_sec = 15
required = true
enabled_tools = ["list_tables", "describe_table", "query_database"]

[mcp_servers.shop_database.env]
SHOP_DB_PATH = "/absolute/path/to/project/shop.db"
```

Run `codex mcp list`, start Codex, and use `/mcp` to verify that `shop_database` and all three tools are available.

## 7. Test

```bash
npm test
```

The suite covers input boundaries, SQL tokenization and refusal, serialization, schema discovery, all acceptance analytics, pagination, named bindings, stdio protocol behavior, and database immutability across the destructive-query matrix.

## Example prompts

1. Show me all available tables and explain what information each table contains.
2. How many customers are from Germany?
3. Which country has the most customers?
4. Who is the customer who spent the most money?
5. What are the top 5 best-selling products?
6. What are the top 3 product categories by revenue?
7. How much revenue did we generate in 2025?
8. Which customer placed the most orders?

## Query rules and read-only guarantee

The database is opened with `readonly: true` and `fileMustExist: true`, then placed in SQLite query-only mode. The SQL policy independently permits only one `SELECT` or non-recursive `WITH ... SELECT`; mutation, DDL, PRAGMA, attachment, maintenance, extension loading, recursive CTEs, and extra statements are refused. Prepared statements must also identify as readers. User values are passed only through named bindings.

Each result page contains at most 500 rows. Use a deterministic `ORDER BY` and continue with `next_offset` while `has_more` is true. The synchronous SQLite driver cannot interrupt a CPU-heavy query in-process; v1 relies on query restrictions, bounded output, and the recommended 15-second MCP client timeout rather than a hard server-side execution deadline.

Revenue, spending, units sold, and product/category sales exclude cancelled orders. Placed-order counts include every status. Whole-order revenue and customer spending use `orders.total_amount`; historical product/category revenue uses `order_items.quantity * order_items.unit_price`. Dates are timezone-naive, and calendar-year filters use half-open intervals. The database declares no currency, so amounts are monetary units.

## Troubleshooting

- `DATABASE_UNAVAILABLE`: verify `SHOP_DB_PATH`, file existence, and read permission. The server does not create databases.
- `DATABASE_SCHEMA_MISMATCH`: use the committed fixture or run `npm run prepare-db`; a custom database must have every required table, column, foreign key, and fixture prerequisite.
- Native dependency installation fails: use a supported Node.js release and install your platform's compiler/build tools, then rerun `npm install`.
- MCP framing or JSON errors: do not add `console.log` or other stdout logging to runtime code. Send diagnostics to stderr only.
- A query returns `SQL_ERROR`: inspect tables first, use unique aliases for duplicate result-column names, and verify placeholder names and SQL syntax.