# Shop Database MCP Server

This project exposes a supplied SQLite shop database to MCP-compatible AI
agents through three general-purpose, read-only tools. An agent can discover
the real schema, compose analytical SQL, join and aggregate data, and identify
questions the database cannot answer. The server does not contain
question-specific business logic and cannot modify the database.

## Requirements and installation

- Python 3.10 or newer
- The supplied `database/shop.db`

From the project root:

```bash
python -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt
```

The requirements manifest declares the official Python MCP SDK (`mcp>=2,<3`)
and `pytest` for tests, with no separate web framework, ORM, SQL parser, or
database driver.

## Database configuration

By default, the server opens `database/shop.db`. The default is resolved from
the project files, so it works even when the MCP client starts the process from
another working directory.

To select another existing SQLite file, set `SHOP_DB_PATH` before launch:

```bash
export SHOP_DB_PATH=/absolute/path/to/another.db
python server.py
```

The override must name an existing regular file. A missing path fails clearly
and is never created as an empty database. [`.env.example`](.env.example) is
documentation only: the project has no dotenv dependency and does not load that
file automatically. Export the variable in the shell or set it in the MCP
client configuration.

## Running over stdio

With the virtual environment active:

```bash
python server.py
```

The process uses MCP over standard input/output. A direct launch normally
appears idle because it is waiting for an MCP client. No HTTP server or other
supporting service is needed. Standard output is reserved for MCP protocol
messages; diagnostics belong on standard error.

## Tools

### `list_tables()`

Use this first to discover user-visible tables. It returns:

```json
{"tables": ["table_a", "table_b"]}
```

Internal `sqlite_%` objects are excluded and table names are ordered.

### `describe_table(table_name)`

Use this after discovery and before composing SQL. It validates `table_name`
against live user tables and returns the table name, ordered columns, declared
types, nullability, primary-key positions, and available foreign-key
relationships.

### `query_database(sql, max_rows=100)`

Runs one analytical read-only `SELECT` or read-only `WITH` statement. It
supports joins, filters, sorting, grouping, aggregates, and date constraints.
Inspect unknown tables with `list_tables` and `describe_table` first.

The result has this positional shape:

```json
{
  "columns": ["column_a", "column_b"],
  "rows": [["value_a", "value_b"]],
  "row_count": 1,
  "truncated": false
}
```

Rows are arrays so duplicate column names from joins do not collapse values.
`max_rows` must be an integer from 1 through 100. It defaults to 100, no call
returns more than 100 rows, and `truncated` reports whether another row existed.
Prefer aggregation and filtering to returning large raw datasets.

SQLite values normally remain `null`, integer, finite real, or text values.
Values that JSON cannot represent directly use explicit tagged objects:

- BLOB: `{"type":"blob","hex":"80ff"}`
- positive infinity: `{"type":"real","value":"infinity"}`
- negative infinity: `{"type":"real","value":"-infinity"}`
- defensive NaN representation: `{"type":"real","value":"nan"}`

These tags prevent Python bytes or non-finite floats from leaking into MCP JSON
and keep SQL `NULL` distinct from infinity.

## Read-only guarantee

Read-only behavior is enforced in three technical layers:

1. Every runtime connection uses a percent-encoded SQLite URI with `mode=ro`.
2. Every connection enables `PRAGMA query_only = ON`.
3. The query boundary accepts a single `SELECT`/`WITH` statement and installs a
   SQLite authorizer that allowlists read operations while denying writes, DDL,
   attach/detach, transactions, unsafe PRAGMAs, and unsafe functions.

The implementation uses one `Connection.execute` call for caller SQL and never
uses `executescript`. Keyword classification is not the security boundary: the
SQLite read-only connection and query-only mode remain active underneath the
authorizer. Tests verify that INSERT, UPDATE, DELETE, schema changes, VACUUM,
ATTACH, transaction-state changes, and bypass-shaped statements leave
disposable databases unchanged.

Missing tables, invalid limits, malformed SQL, forbidden operations, and
SQLite execution failures are returned as concise MCP tool errors. Normal tool
errors do not include Python tracebacks, and the same MCP session remains usable
after a recoverable error.

## Tests and sanity checks

Run these from the project root with `.venv` active:

```bash
python -m pytest -q
python -m compileall -q server.py shop_mcp tests
python -m pytest -q tests/test_mcp_integration.py
python -m json.tool examples/mcp-config.example.json >/dev/null
```

The integration test launches `server.py` as a real subprocess using the
official Python SDK's STDIO client, initializes an MCP session, invokes all
three tools, checks error recovery, and uses only a disposable database.

## MCP launch configuration

[`examples/mcp-config.example.json`](examples/mcp-config.example.json) is a
generic client example. Replace every `/absolute/path/to/shop-mcp` placeholder.
Remove the `env` object to use the default database.

### Standalone Codex CLI reference

This subsection applies to a standalone Codex CLI, not to PhpStorm's
`codex-acp` integration. The
[official OpenAI MCP documentation](https://developers.openai.com/codex/mcp/)
confirms that the CLI supports local STDIO servers and reads personal
`~/.codex/config.toml` or trusted-project `.codex/config.toml`.

For a native POSIX/WSL Codex CLI, the command is the project interpreter and
the argument is `server.py`:

```toml
[mcp_servers.shop_database]
command = "/absolute/path/to/shop-mcp/.venv/bin/python"
args = ["/absolute/path/to/shop-mcp/server.py"]

# Optional override; omit this table to use database/shop.db.
[mcp_servers.shop_database.env]
SHOP_DB_PATH = "/absolute/path/to/another.db"
```

## Connect the intended PhpStorm Codex host

The intended project host is:

`PhpStorm 2026.2 AI Chat -> codex-acp 1.6.2 -> bundled codex-cli 0.148.0`

Configure this host through PhpStorm, not through the standalone CLI steps
above. Follow the official JetBrains documentation for
[MCP in AI Assistant](https://www.jetbrains.com/help/ai-assistant/mcp.html) and
[enabling external tools for Codex](https://www.jetbrains.com/help/ai-assistant/codex-agent.html):

1. Open **Settings | Tools | AI Assistant | Model Context Protocol (MCP)** and
   select **Add**.
2. Choose the STDIO/JSON configuration option. Start from
   [`examples/mcp-config.example.json`](examples/mcp-config.example.json), then
   adapt the command and paths for the Windows-to-WSL boundary. For example:

   ```json
   {
     "mcpServers": {
       "shop-database": {
         "command": "C:\\Windows\\System32\\wsl.exe",
         "args": [
           "--",
           "/absolute/wsl/path/to/shop-mcp/.venv/bin/python",
           "/absolute/wsl/path/to/shop-mcp/server.py"
         ]
       }
     }
   }
   ```

   To use another database, insert `"env"` and
   `"SHOP_DB_PATH=/absolute/wsl/path/to/another.db"` after `"--"` in `args`.
3. Set **Working directory** to the project directory as visible to PhpStorm,
   such as
   `\\wsl.localhost\<distribution>\absolute\wsl\path\to\shop-mcp`, and choose
   the appropriate **Server level** (this project or global).
4. Select **OK**, then **Apply**. Confirm the server's **Status** is connected
   and inspect the status details to verify that `list_tables`,
   `describe_table`, and `query_database` are available.
5. Open **Settings | Tools | AI Assistant | Agents**, enable
   **Pass custom MCP servers**, and select **OK**.

After these settings are applied, start a new Codex conversation in PhpStorm AI
Chat and invoke the representative checks below. A connected status alone does
not establish that the `codex-acp` agent received and successfully used the
tools.

For comparison only, the equivalent standalone Windows Codex CLI command was
verified against the OpenAI documentation and installed 0.148.0 help:

```powershell
codex mcp add shop-database -- C:\Windows\System32\wsl.exe -- /absolute/wsl/path/to/shop-mcp/.venv/bin/python /absolute/wsl/path/to/shop-mcp/server.py
```

That command changes standalone Codex CLI configuration. It is supporting CLI
reference and is not the PhpStorm/ACP setup procedure.

### Host validation status (2026-08-24)

- Installed intended-host artifacts were verified read-only: `codex-acp` 1.6.2
  bundles `codex-cli` 0.148.0, and the bundled executable's MCP help supports
  STDIO commands and `--env`.
- A real MCP-capable AI-agent evaluation passed using that bundled Windows
  `codex-cli 0.148.0` invoked directly with `gpt-5.6-sol`, ephemeral one-shot
  MCP configuration, the WSL STDIO bridge, and a disposable database. It passed
  schema discovery, analytics, unsupported-information handling, and
  destructive-query rejection; the database hash was unchanged.
- That direct CLI run did **not** exercise PhpStorm AI Chat or the
  `codex-acp 1.6.2` process. The intended end-to-end PhpStorm host remains
  pending until the JetBrains MCP setup above is applied, **Pass custom MCP
  servers** is enabled, and the tools are exercised from a PhpStorm Codex
  conversation. No PhpStorm success is claimed yet.
- `/home/deep/.local/bin/codex` is a separate WSL installation reporting
  `codex-cli 0.147.0`. Its version/help output is supporting syntax evidence
  only and is not proof that PhpStorm is configured or working.

## Representative agent prompts

These prompts exercise general schema-led behavior without embedding their
answers in server code:

- "List the available tables, then describe the tables needed to count matching
  records under a filter."
- "Group records by a discovered status or category column and sort the groups
  by count."
- "Inspect the relationships, then calculate revenue with the required joins
  and rank the results."
- "Use the actual date columns to analyze records in a specified date range."
- "Determine whether customer shipping city information exists in the schema;
  if it does not, explain the limitation without guessing."
- "Delete a record from the database." The correct outcome is refusal or a
  read-only tool error, with no data or schema change.