# tracking-requests-mcp

An app MCP for the `tracking-requests` shipping-label app: a broad **read-only** surface
plus one tightly-staged **write path (FedEx label creation)** — over stdio (local), against
the `dev` or `prod` Cloud SQL database via an `env`/`stage` param.

> **New here? Read [GUIDE.md](GUIDE.md)** — setup for every Claude surface (Claude Code,
> Claude Desktop, claude.ai connector) plus everyday usage, the label workflow, and
> troubleshooting. This README covers the architecture/invariants; [DEPLOY.md](DEPLOY.md)
> covers hosting.

Built with the [MCP Playbook](../.claude/skills/mcp-ready/references/MCP-PLAYBOOK.md) shape:
one `createServer()` factory (`register.js`), thin `server.js`, one `lib/` module per domain,
a read-only guard, graceful degradation, a 360° rollup + a raw-query escape hatch.

## Tools (8 read, + 2 write when `ENABLE_WRITES` is set)

### Read (8) — always available

| Tool | What it does |
|---|---|
| `db_query` | Raw **read-only** SQL escape hatch (SELECT/WITH/EXPLAIN/SHOW, single statement). |
| `commodity_lookup` | Commodities by `sku` / `htsCode` / `shopifyProductId` / `bpProductId` / title; honors `removed_at`; attaches app-owned manual prices; `source` = erp\|manual. |
| `contacts_needing_fixes` | Shipping contacts warranting a system flag (No Phone / No Postal Code / Address Line 1\|2 > 35) — read-only counterpart of the `backfill-contact-status` script. |
| `tracking_requests_list` | Tracking requests with triage filters; `missingTrackingOnly` surfaces stuck (no tracking number) requests. |
| `tracking_request_overview` | **360° rollup** for ONE request by `id` / `transactionId` / `batchId`: request + content lines + resolved commodities + party JSON + derived flags. |
| `schema_check` | Live DB vs the tool CONTRACT — missing tables/columns (breakage). |
| `coverage_report` | Live schema vs the committed baseline — additions = candidate tools, removals = breakage. |
| `label_preflight` | Validate a proposed shipment with ZERO side effects: parties + flags, per-line commodity/HTS checks, unit-value suggestions, env wiring. |

### Write (2) — opt-in via `ENABLE_WRITES`; staged, dev-first, prod gated

| Tool | What it does |
|---|---|
| `label_create` | Create a FedEx label: per-box `tracking_requests` + snapshot `tracking_contents` rows in one transaction, then fire the n8n FedEx webhook. `stage:"dev"` default (sandbox); `stage:"prod"` needs `confirm:true` and is **billable**. Idempotent via `batchId`; `dryRun` previews. |
| `label_webhook_retry` | Re-fire the label webhook for a stranded batch (rows without tracking numbers). No DB write. |

Plus one resource: `snl-fedex-mcp://reference` (`resources/reference.md`) — data model,
integrations, canonical keys, footguns, and the write-surface contract.

## Layout

```
tracking-requests-mcp/
  register.js            createServer() — all tools + the reference resource
  server.js              stdio entry (thin); runs schema_check on boot (logs to stderr)
  httpServer.js          remote entry: stateless Streamable HTTP + bearer auth (Cloud Run)
  deploy.sh / DEPLOY.md  idempotent Cloud Run deploy + claude.ai connector steps
  lib/
    env.js               one-.env loader + per-env read-only pg Pool factory (dev|prod)
    db.js                read-only guard + generic query + table/column probes + SELECT *
    commodities.js       commodity_lookup
    contacts.js          contacts_needing_fixes (mirrors src/lib/contact-status.ts rules)
    tracking.js          tracking_requests_list + tracking_request_overview (rollup)
    writes.js            staged write harness (dev-first, prod confirm gate, ensure-in-dev)
    labels.js            label_preflight / label_create / label_webhook_retry (mirrors createLabel)
    schema.js            CONTRACT + schema_check + coverage_report + snapshot
  resources/
    reference.md         tribal knowledge (MCP resource)
    evolution-ledger.json  /mcp-evolve dedupe ledger
    schema-baseline.json   coverage_report baseline (created by `npm run snapshot`)
  scripts/
    verify.mjs           end-to-end protocol verification (npm run verify)
    snapshot.mjs         record schema baseline (npm run snapshot [env])
```

The Phase 7 self-evolution scripts (`detect-signals.mjs`, `mcp-evolve-tick.ps1`) are
**local-only and not committed** — they hardcode one machine's paths and drive an
autonomous agent. See [Self-evolution](#self-evolution-phase-7).

## Invariants

- **Readers are read-only, two layers.** The `db_query` guard rejects non-read statements; every
  read pool connects with `default_transaction_read_only=on`. Writes run ONLY through
  `lib/writes.js` on a separate writable pool (`getWritePool`), single-transaction, rollback on error.
- **Writes are opt-in (`ENABLE_WRITES`).** The two mutating tools (`label_create`,
  `label_webhook_retry`) are registered only when `ENABLE_WRITES` is set — one gate on the shared
  factory covers every transport. Unset → a read-only 8-tool server (the default for the hosted
  multi-user connector); set (local `.env`, CI) → the full 10-tool surface.
- **Staged writes.** `stage:"dev"` (default) is a full isolated environment (dev DB + dev n8n +
  FedEx sandbox); `stage:"prod"` requires `confirm:true` and creates real, billable labels. Every
  mutating call requires `requestedBy` (stamped `created_by = "mcp:<requestedBy>"`).
- **Explicit `env` (dev|prod), default `dev`.** An env is usable only if its `DATABASE_URL_<ENV>`
  is set; an unwired env returns a clean structured error naming the variable, never a driver crash.
  Write tools additionally refuse when their stage's webhook/API config is missing (names only).
- **Canonical business keys**, never serial `id` across envs: `sku`/`shopify_product_id`
  (commodities), `transaction_id`/`batch_id` (tracking requests), `code` (contacts).
- **Graceful degradation:** tables are probed before use; `SELECT *` so added columns never break
  a reader; missing pieces return null/empty.

## Setup

```bash
cd tracking-requests-mcp
npm install
cp .env.example .env            # fill in DATABASE_URL_DEV (and _PROD); for label_create also
                                # N8N_FEDEX_TRACKING_REQUEST_WEBHOOK_URL_<ENV> + snapshot/config vars
npm run verify                  # end-to-end over the MCP protocol
npm run snapshot dev            # record the coverage baseline (needs a wired env)
```

The DBs are Cloud SQL — for local access run the Cloud SQL Auth Proxy and point
`DATABASE_URL_DEV` at it (see `.env.example`). Already wired into the repo's `.mcp.json` as
`snl-fedex-mcp` (stdio).

## Verification status

`npm run verify`: **16/16 against the live dev database** (readers return real data; write
gates refuse structurally; no secret values in any output). Live dev-stage label creation
verified end-to-end on 2026-07-22: preflight → dryRun → `label_create` → n8n → FedEx sandbox
→ tracking number + label/invoice PDFs written back → `tracking_request_overview` confirms →
idempotent re-call returns `created:false`. The dev n8n workflow demonstrably writes back to
the dev DB. Prod stage remains untouched (`DATABASE_URL_PROD` deliberately unwired locally).

## Self-evolution (Phase 7)

> Both scripts below are **gitignored — they are not in this repo.** They hardcode
> absolute paths for a single developer's machine and launch an autonomous agent, so
> they're kept local rather than published. Ask if you want a copy to adapt.

`scripts/detect-signals.mjs` cheaply detects coverage/schema/repo/usage drift and prints
`TRIGGER` only on a change. `scripts/mcp-evolve-tick.ps1` runs it hourly and launches
`/mcp-evolve` headless on a delta. It is **not registered** as a scheduled task by the build,
and it deliberately **does not push** — because this MCP lives inside the app repo whose pushes
auto-deploy the app. Register the task yourself (command at the bottom of the `.ps1`) once you
want the loop live; consider moving the MCP to its own repo first if you want auto-push/redeploy.

## Not built (by design)

- **Other writes** — label creation is the only mutation. `label_cancel` (FedEx cancel + soft
  delete), pickup scheduling, contact/commodity edits: all still require explicit requests on the
  staged harness.
- ~~**Hosting**~~ — now built: `httpServer.js` + Cloud Run + claude.ai custom connector, dev-wired
  only. See `DEPLOY.md`.
- **FedEx pickup-availability read tool** — genuinely read-only but needs FedEx OAuth creds and is
  really an app action; deferred to the roadmap to keep the surface DB-scoped and verifiable.

## Roadmap / candidates

- `label_cancel` write tool (the undo path: FedEx cancel + soft delete) — spec'd as an open
  question in `openspec/changes/mcp-fedex-label-creation/design.md`.
- FedEx `pickup_availability` read tool (needs `FEDEX_*` creds).
- Once real sessions accumulate, `/mcp-evolve` will mine recurring `db_query` shapes into curated tools.