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 — 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 covers hosting.

Built with the MCP Playbook 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.

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

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.