# Ecommerce MCP Server (BigQuery)

[![CI](https://github.com/Ethan-Shapiro/Ecommerce-MCP-Server/actions/workflows/ci.yml/badge.svg)](https://github.com/Ethan-Shapiro/Ecommerce-MCP-Server/actions/workflows/ci.yml)

Python MCP server using the [MCP Python SDK](https://github.com/modelcontextprotocol/python-sdk) and `google-cloud-bigquery`, with guarded `dry_run_sql` and `execute_sql` tools.

It ships with an **A/B benchmark** that measures what live schema access is actually worth: the same model answering the same questions with structured MCP tools versus wiki-style documentation search. Results and methodology: **[docs/BENCHMARK_RESULTS.md](docs/BENCHMARK_RESULTS.md)**.

## Setup

```bash
python -m venv .venv
.\.venv\Scripts\activate   # Windows
pip install -e .
```

### Configuration

Settings come from optional YAML (`MCP_CONFIG_PATH`), then **environment variables override YAML** (see `mcp_server.config.Settings`). A `.env` file in the working directory is also loaded when present.

| Variable | Required | Description |
|----------|----------|-------------|
| `GCP_PROJECT_ID` | Yes | Project that runs BigQuery jobs (billing) when querying data. Alias: `GOOGLE_CLOUD_PROJECT`. |
| `GOOGLE_APPLICATION_CREDENTIALS` or `GCP_CREDENTIALS_PATH` | No | Path to service account JSON; omit to use [ADC](https://cloud.google.com/docs/authentication/application-default-credentials) (e.g. `gcloud auth application-default login`). |
| `BIGQUERY_DEFAULT_DATASET` | No | Primary ecommerce `project.dataset` for unqualified names and schema FQNs (default in code: `bigquery-public-data.thelook_ecommerce`; override in YAML for your own dataset). |
| `BIGQUERY_SUPPLEMENTAL_DATASET` | No | Supplemental dataset (e.g. US ZCTA `zip_codes`), default `bigquery-public-data.geo_us_boundaries`. |
| `BIGQUERY_EXECUTE_MAX_ROWS` | No | Row cap for `execute_sql` (default `500`, max `10000`). |
| `BIGQUERY_MAX_BYTES_BILLED` | No | Optional bytes billed cap per **execute** job. |
| `MCP_CONFIG_PATH` | No | Path to YAML; keys mirror the table above in snake_case (`gcp_project_id`, `gcp_credentials_path`, `default_dataset`, `supplemental_dataset`, `execute_max_rows`, `max_bytes_billed`). |

Example YAML: `config/config.example.yaml`. Copy it to `config/config.yaml` and set `MCP_CONFIG_PATH` to that path.

> **Credentials:** `config/config.yaml` and `config/*.json` are gitignored — `config.yaml` holds your real project id, and service account keys must never be committed. Keep the key file **outside the repository** (e.g. `~/.gcp/`) and point `gcp_credentials_path` / `GOOGLE_APPLICATION_CREDENTIALS` at that absolute path. Prefer [ADC](https://cloud.google.com/docs/authentication/application-default-credentials) over a key file where you can.

## Run (stdio)

```bash
set GCP_PROJECT_ID=your-project
thelook-mcp
```

Or: `python -m mcp_server`. (Installed console script names are `thelook-mcp` / `thelook-benchmark`; see `pyproject.toml`.)

### Cursor MCP (stdio)

Cursor connects to MCP servers over stdio. After `pip install -e .`, add a server entry that runs this repo’s venv Python with `-m mcp_server`.

1. Open **Cursor Settings → MCP** (or edit the MCP config file Cursor shows there—often **project** `.cursor/mcp.json` or **user-level** MCP config).
2. Use absolute paths on Windows for `command` and credential files.

Example **`.cursor/mcp.json`** (adjust paths and project id):

```json
{
  "mcpServers": {
    "ecommerce-bigquery": {
      "command": "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe",
      "args": ["-m", "mcp_server"],
      "env": {
        "GCP_PROJECT_ID": "your-gcp-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "C:\\path\\to\\your-service-account.json",
        "MCP_CONFIG_PATH": "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"
      }
    }
  }
}
```

Omit `GOOGLE_APPLICATION_CREDENTIALS` if you rely on ADC for that environment. You can pass the same variables via `env` or rely solely on `MCP_CONFIG_PATH` plus `GCP_PROJECT_ID` in `env`.

**Claude Desktop:** Edit `claude_desktop_config.json` (see [Claude Desktop MCP](https://modelcontextprotocol.io/quickstart/user)); use the same `command`, `args`, and `env` shape under `mcpServers`.

### OpenAI Codex (CLI / IDE extension)

Codex discovers MCP servers from **`config.toml`**: default **`~/.codex/config.toml`**, or a **project** **`.codex/config.toml`** in trusted projects. Stdio servers use `command`, optional `args`, and optional `env`. See [Model Context Protocol – Codex](https://developers.openai.com/codex/mcp).

1. `pip install -e .` in this repo and set GCP env vars (same as Cursor).
2. Add a `[mcp_servers.<name>]` block. Prefer **hyphens** in `<name>` (e.g. `ecommerce-bigquery`).

**Example `~/.codex/config.toml`** (adjust paths; use your real venv `python.exe` on Windows):

```toml
[mcp_servers.ecommerce-bigquery]
command = "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe"
args = ["-m", "mcp_server"]

[mcp_servers.ecommerce-bigquery.env]
GCP_PROJECT_ID = "your-gcp-project-id"
GOOGLE_APPLICATION_CREDENTIALS = "C:\\path\\to\\your-service-account.json"
MCP_CONFIG_PATH = "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"
```

Or use the CLI: `codex mcp add --help` and `codex mcp add` (see the doc above). In the Codex TUI, `/mcp` lists active servers.

### Gemini CLI

The Gemini CLI reads **`mcpServers`** from **`settings.json`** (user or project scope). It spawns stdio MCP processes the same way: `command`, `args`, `env`, optional `cwd` / `timeout` / `trust`. See [MCP servers with the Gemini CLI](https://geminicli.com/docs/tools/mcp-server/).

**Example** (project `.gemini/settings.json` or user `~/.gemini/settings.json`):

```json
{
  "mcpServers": {
    "ecommerce-bigquery": {
      "command": "C:\\path\\to\\Ecommerce-MCP-Server\\.venv\\Scripts\\python.exe",
      "args": ["-m", "mcp_server"],
      "env": {
        "GCP_PROJECT_ID": "your-gcp-project-id",
        "GOOGLE_APPLICATION_CREDENTIALS": "C:\\path\\to\\your-service-account.json",
        "MCP_CONFIG_PATH": "C:\\path\\to\\Ecommerce-MCP-Server\\config\\config.yaml"
      }
    }
  }
}
```

You can also run `gemini mcp add` (see the doc). After discovery, tools are exposed to the model with a **namespace prefix** (e.g. `mcp_ecommerce-bigquery_<toolname>`); use `/mcp` in the CLI to inspect connection and tools.

## GCP IAM and BigQuery

BigQuery jobs are **created in your GCP project** (that project is billed for bytes processed). Tables may live in **`bigquery-public-data`** or in **your own** `project.dataset` (e.g. a linked or copied ecommerce dataset); you do **not** grant your service account on Google’s hosting project when querying public datasets—public datasets are queried from your project using your identity.

1. **Enable the API:** In [Google Cloud Console](https://console.cloud.google.com/), select your project → **APIs & Services** → enable **BigQuery API**.
2. **Billing:** Ensure the project has a billing account attached if your organization requires it for BigQuery jobs.
3. **Service account (recommended for servers and benchmarks):** **IAM & Admin → Service Accounts → Create**, then **Keys → Add key → JSON** for a key file used by `GOOGLE_APPLICATION_CREDENTIALS` / `GCP_CREDENTIALS_PATH`.
4. **Roles on your project** (principal = that service account; scope = your project):

   - **BigQuery Job User** (`roles/bigquery.jobUser`) — create and run query jobs in your project.
   - **BigQuery Data Viewer** (`roles/bigquery.dataViewer`) — read dataset metadata and tables your jobs need (including public datasets referenced from those jobs).

   For interactive development only, your user account can use the same roles instead of a key file, with **Application Default Credentials** after `gcloud auth application-default login`.

5. **Least privilege:** Do not grant `bigquery.admin`, `owner`, or write roles unless you have a separate requirement. This server only issues read-only `SELECT` paths after validation.

Official references: [BigQuery IAM roles](https://cloud.google.com/bigquery/docs/access-control), [Querying public datasets](https://cloud.google.com/bigquery/public-data).

## Tests

```bash
pip install -e ".[dev]"
pytest
```

The suite runs **fully offline** — no GCP credentials or network needed — and covers the read-only SQL guard (DML/DDL rejection, stacked-statement injection), benchmark scoring, and task/schema integrity. CI runs it on Python 3.10, 3.11, and 3.12.

## Documentation

- **[docs/BENCHMARK_RESULTS.md](docs/BENCHMARK_RESULTS.md)** — A/B results, methodology, and the benchmark defects found and fixed along the way.
- **[docs/PROJECT_GUIDE.md](docs/PROJECT_GUIDE.md)** — repository layout, architecture, data flow, benchmark runner, and glossary (MCP, BigQuery, wiki baseline vs MCP tools).

### Benchmarks (optional)

```bash
pip install -e ".[benchmark]"
set GEMINI_API_KEY=...   # default LLM; see table below for OpenAI
set GCP_PROJECT_ID=...
thelook-benchmark --condition both
```

See [docs/PROJECT_GUIDE.md](docs/PROJECT_GUIDE.md) for architecture and glossary; [benchmarks/README.md](benchmarks/README.md) and [benchmarks/report_template.md](benchmarks/report_template.md) for runner details and summarizing a run.

#### Results summary

Three trials, 22 tasks, both arms, `gemini-3.1-flash-lite-preview` at temperature 0. Correctness is
measured by executing the model's SQL and the task's `golden_sql` and comparing result sets — not
by whether the query parses.

| | baseline (wiki) | mcp (live tools) |
|---|---|---|
| correct | 53/65 (82%) | **63/66 (95%)** |
| — staleness tasks | 3/9 (33%) | **9/9 (100%)** |
| — structural tasks | 42/45 (93%) | 44/45 (98%) |
| context tokens (total) | 287,137 | **183,601 (−36%)** |
| context worst case | 18,604 | **10,815** |
| wall clock (median) | **3.2s** | 6.0s |

Read that as a narrow claim: on structural questions where the documentation is accurate, reading
docs works about as well as querying the warehouse. The advantage concentrates in **stale
documentation** — values that were right when written and since changed. MCP is also slower per
task. Full methodology, per-task failures, caveats, and the benchmark defects found and fixed
along the way: **[docs/BENCHMARK_RESULTS.md](docs/BENCHMARK_RESULTS.md)**.

#### Reproducing A/B (baseline vs MCP) results

The runner compares **baseline** (BigQuery dialect + **internal wiki simulation**: `search` and `fetch` over in-memory pages built from the same schema definitions—no structured catalog API, no in-loop BigQuery tools) and **MCP** (**`list_tables`**, **`describe_table`**, **`suggest_joins`**, plus **`dry_run_sql`** and **`execute_sql`**, with prompts that require dry-run before finalizing). **Gemini** or **OpenAI** via `--provider`. Validation still dry-runs (and optionally executes) the model’s final SQL with the same BigQuery settings as the live server.

**Prerequisites**

- `pip install -e ".[benchmark]"` (installs `google-genai` and `openai`).
- **Gemini (default):** `GEMINI_API_KEY` or `GOOGLE_API_KEY` from [Google AI Studio](https://aistudio.google.com/apikey).
- **OpenAI:** `set BENCHMARK_PROVIDER=openai` and `OPENAI_API_KEY`, or pass `--provider openai`.
- Same GCP setup as above: `GCP_PROJECT_ID` and credentials unless `BENCHMARK_SKIP_BIGQUERY=1` (then validation is mostly parse-only).

**Environment variables**

| Variable | Purpose |
|----------|---------|
| `GEMINI_API_KEY` or `GOOGLE_API_KEY` | Gemini API key (default `--provider gemini`). |
| `OPENAI_API_KEY` | Required when `--provider openai`. |
| `BENCHMARK_PROVIDER` | `gemini` or `openai` (default `gemini`). |
| `BENCHMARK_MODEL` | Overrides default model if you omit `--model` (Gemini default: `gemini-3.1-flash-lite-preview`; OpenAI default: `gpt-4o-mini`). |
| `BENCHMARK_SKIP_BIGQUERY` | Set to `1` / `true` to skip constructing a BigQuery client (no dry-run/execute validation against GCP). |
| `GCP_PROJECT_ID`, credentials | Same as MCP server; used only when BigQuery is not skipped. |

**Example: full A/B with Gemini + validation**

```bash
set GEMINI_API_KEY=your-key
set GCP_PROJECT_ID=your-gcp-project-id
set GOOGLE_APPLICATION_CREDENTIALS=C:\path\to\key.json
thelook-benchmark --condition both --temperature 0 --output-dir benchmarks\results
```

**Example: OpenAI instead**

```bash
set OPENAI_API_KEY=sk-...
set BENCHMARK_PROVIDER=openai
thelook-benchmark --condition both
```

**Reproducibility notes**

- Default `--temperature` is `0` for minimal sampling variance; some APIs may still show rare drift.
- Pin **`--model`** and **`--tasks`** to a fixed file so comparisons across machines use the same task set and endpoint.
- Each run writes a unique `run_id` (timestamp + short uuid) to `benchmarks/results/<run_id>_results.json` and `<run_id>_summary.csv`. To compare arms, filter the CSV by `condition` (`baseline` vs `mcp`) or load the JSON traces.
- For a **deterministic, no-LLM** check that tasks and BigQuery connectivity are valid, run:  
  `thelook-benchmark --validate-golden-only` (uses `golden_sql` from `benchmarks/tasks.yaml` when present).
- `golden_sql` is written against `{dataset}` / `{supplemental_dataset}` placeholders, resolved at load time from `default_dataset` / `supplemental_dataset`. Point those at **your** warehouse (a copy or linked ecommerce dataset) and the golden queries validate without editing the task file.
- The plan suggests repeating each condition several times and taking a **median** for wall-clock if you need stable timing numbers under load.

**Useful flags** (full list: `thelook-benchmark --help`)

| Flag | Meaning |
|------|---------|
| `--provider` | `gemini` or `openai` (default: `gemini`; env `BENCHMARK_PROVIDER`). |
| `--condition` | `baseline`, `mcp`, or `both` (A/B uses `both`). |
| `--temperature FLOAT` | Passed through to the chat API (default `0`). |
| `--model NAME` | Overrides `BENCHMARK_MODEL`. |
| `--tasks PATH` | Alternate task YAML. |
| `--max-tasks N` | Run only the first N tasks. |
| `--max-tool-rounds N` | Cap tool rounds per task for both baseline (wiki) and MCP (default `24`). |
| `--no-execute` | Dry-run validation only; skip execute step. |
| `--output-dir DIR` | Where JSON and CSV are written. |

## License

[MIT](LICENSE)