# Payment/Webhook Reconciliation MCP

A remotely hosted MCP server that lets an ops engineer investigate, classify,
and triage drift between an internal order/payment record and a payment
provider's ground truth — and escalate confirmed drift for human review. It
does not retry, void, capture, or refund anything automatically; every
correction is applied by a person, outside this MCP.

## Why this exists

Commerce backends keep two records of the same payment: the internal
order/payment state, and the provider's ground truth, delivered via webhooks.
These drift apart in practice — webhooks arrive late, get missed, or arrive
out of order — so an order can end up marked unpaid when the provider
actually captured funds, or paid when no capture ever completed. Today an
ops engineer manually cross-references the internal DB, the provider
dashboard, and raw webhook logs to spot this. This MCP encapsulates that
investigation and classification logic directly, rather than exposing plain
CRUD over the tables.

## Scope

**In**: single-order investigation, fleet-wide drift triage (including
cross-order duplicate-charge detection), an idempotent human-review
escalation queue, a bearer-token gate, remote hosting on Vercel + Neon
Postgres, synthetic seeded data.

**Out** (by design, not oversight): no frontend, no real payment-provider
integration (provider data is simulated via seed data), no multi-tenant
support, no accounts/login system (bearer token only — see Assumptions), and
no tool that mutates payment/order state. This is the direction confirmed by
the team: diagnosis and evidence gathering are in scope, automatic
correction is not.

## Architecture

- Next.js 16 App Router, single route: `app/api/mcp/route.ts`.
- `mcp-handler` v2 (`createMcpHandler`) + `experimental_withMcpAuth` for a
  bearer-token gate (`Authorization: Bearer <MCP_BEARER_TOKEN>`).
- Neon Postgres via `@neondatabase/serverless` (HTTP-based driver, avoids
  connection-pool exhaustion in serverless functions).
- **The diagnostic decisions live in Postgres, not in application code.**
  `order_diagnoses` (a view defined in `schema.sql`) computes the 5-way drift
  classification (`clean`, `missed_capture`, `missed_refund`, `false_capture`,
  `false_refund`) directly via a `CASE` expression over `orders`,
  `webhook_events`, and `provider_charges`, and pre-shapes the JSON both
  `get_order_snapshot` and `scan_fleet_for_drift` return. Both tools `SELECT`
  from this one view rather than each re-deriving the classification, and
  rather than pulling raw rows into the app to classify in TypeScript.
- Duplicate-charge detection is the same story: `lib/queries.ts` holds the
  raw, parameterized SQL (`DUPLICATE_CANDIDATES_FOR_ORDER_SQL` for the
  single-order case, `DUPLICATE_PAIRS_SINCE_SQL` for the fleet-wide self-join,
  `DUPLICATE_PAIR_FOR_ORDERS_SQL` for confirming one specific pair — see
  below) as the one place the 10-minute rule is enforced. Every row either
  query returns is already a confirmed match — same customer/amount/currency,
  distinct orders, within `DUPLICATE_CHARGE_WINDOW_MS` — so there's no
  re-verification step left in application code *within* a single read tool
  call, only presentation (`toDuplicateEvidence`, in `lib/reconciliation.ts`)
  and pair-level dedup. `escalate_for_review` is a different trust boundary,
  though: its `evidence` argument crosses a tool-call boundary (an agent
  copies it from an earlier `get_order_snapshot`/`scan_fleet_for_drift` call,
  or could fabricate it outright), so that tool does not persist it as
  given — it re-runs the same diagnosis/duplicate-match queries itself
  (`verifyDuplicatePair`, using `DUPLICATE_PAIR_FOR_ORDERS_SQL`, and
  `order_diagnoses` for the four single-order drift types) and stores that
  result instead, rejecting the call if the claimed drift no longer
  reproduces. `DUPLICATE_PAIR_FOR_ORDERS_SQL` deliberately does *not* reuse
  `DUPLICATE_CANDIDATES_FOR_ORDER_SQL`'s anchor-on-one-charge approach: that
  query (correctly, for its own read-only purpose) looks only at the
  order's single most-recent succeeded charge, which is fine for "does this
  order look double-charged" but would be wrong for a gate that decides
  whether to accept or reject a write — an order with more than one
  succeeded charge could have the real match on an older charge, and an
  anchor-based check would not only miss it but do so asymmetrically
  depending on which order_id the caller listed first.
  `DUPLICATE_PAIR_FOR_ORDERS_SQL` instead joins every succeeded charge on one
  order against every succeeded charge on the other, so the answer is
  complete and order-independent — see the regression tests in
  `lib/reconciliation.integration.test.ts`.
- Because the diagnostic and duplicate-matching *decisions* now live in SQL,
  they can't be unit-tested as pure functions anymore — verifying them
  requires a real Postgres. See "Automated tests" below for how that's done
  without depending on the live deployed database.
- `lib/reconciliation.ts` keeps only what's still genuinely pure: shared
  types, the `DUPLICATE_CHARGE_WINDOW_MS` constant (imported by both the app
  and, transitively, the SQL call sites, so there's exactly one number rather
  than independently hardcoded copies), evidence formatting
  (`toDuplicateEvidence`), and idempotency-key construction
  (`buildIdempotencyKey`) — none of which is a diagnostic decision, so these
  stay unit-tested.
- `lib/queries.ts` exists specifically so the duplicate-charge SQL text is
  identical between production (executed via the Neon driver's
  `sql.query(text, params)`) and the integration test suite (executed via
  `pg` against a local Postgres) — one string, two drivers, rather than the
  test suite re-typing an equivalent query that could silently drift from
  what actually runs in production.

## Data model

Five tables (`schema.sql`):

- `orders` — internal order/payment record (`status`: `unpaid | paid | refunded`).
- `webhook_events` — what our system actually recorded from provider
  webhooks. Can be missing an event even when the provider-side fact exists.
- `provider_charges` — the provider's ground truth, independent of whether a
  webhook for it was ever processed.
- `reconciliation_actions` — the human-review escalation queue, holding only
  the **current** state of each escalation (one row per `idempotency_key`,
  overwritten in place on re-escalation). Nothing here executes a payment
  operation. `idempotency_key` is unique, so re-scanning and re-escalating
  the same drift updates the existing row instead of duplicating it.
- `reconciliation_action_history` — append-only, never updated or deleted.
  Before `escalate_for_review` reopens a previously-acknowledged escalation
  (overwriting `status`/`acknowledged_at`/`evidence` on the row above), it
  archives that acknowledge+reopen cycle here first, so the fact that an
  escalation was acknowledged before — when, on what evidence, and when it
  was reopened — is never lost to a later re-diagnosis. See
  `get_escalation_history`.

These two tables are the only ones the MCP ever writes to — nothing in
either executes a payment operation; both are pure record-keeping around the
human-review process.

`seed.sql` seeds 7 orders covering 5 distinct drift types plus one clean
control case (see comments in the file for the exact scenario each order
represents): `missed_capture`, `missed_refund`, `false_capture`,
`false_refund`, and a `duplicate_charge` pair (two orders, same
customer/amount/currency, provider charges 5 minutes apart).

`schema.sql` also defines `order_diagnoses`, a view over `orders`,
`webhook_events`, and `provider_charges` that computes the drift diagnosis
and pre-shapes the JSON the MCP tools return — see Architecture above.

## MCP tools

| Tool | Type | Description |
|---|---|---|
| `get_order_snapshot` | read-only | Investigate one order: internal record, webhook events, provider charges, and a computed diagnosis (`clean`, `missed_capture`, `missed_refund`, `false_capture`, or `false_refund`). Does not check for duplicate charges — use `check_duplicate_charge` for that. |
| `check_duplicate_charge` | read-only | Given one `order_id`, checks whether its successful charge matches another order's charge (same customer, amount, currency, within 10 minutes) and reports whether a duplicate was found. Built for the "customer says they were charged twice" support workflow, where the answer needs to be a direct yes/no for one order rather than a full investigation. |
| `scan_fleet_for_drift` | read-only | Triage orders from the last `window_hours` (default 24) for drift, and cross-reference provider charges for possible duplicate successful charges (same customer, amount, and currency within 10 minutes). Duplicate-charge detection always covers the full window regardless of `limit`, so a real pair can never be split across a row-count cutoff. |
| `escalate_for_review` | write (idempotent) | Upserts a human-review record for a diagnosed drift. Validates `order_ids` (right count for `drift_type`, must exist) and re-derives the stored evidence from the current diagnosis/duplicate-match state rather than trusting the caller's `evidence` argument — rejects the call if the claimed drift doesn't currently reproduce. Never touches `orders` or `provider_charges`. |
| `list_open_escalations` | read-only | Shows the current human-review queue, most recent first, capped by an optional `limit` (default 100). |
| `acknowledge_escalation` | write (idempotent) | Marks an open escalation `acknowledged` once a human has resolved it out-of-band, stamping `acknowledged_at`. Re-acknowledging an already-acknowledged escalation is a no-op — it returns the existing record instead of moving `acknowledged_at` again. Never touches `orders` or `provider_charges`. |
| `get_escalation_history` | read-only | Shows every past acknowledge+reopen cycle for an escalation from `reconciliation_action_history` — when it was acknowledged, on what evidence, and when it was reopened by a later re-diagnosis. |

## Setup

1. Create a Neon Postgres database and copy its connection string.
2. Copy `.env.example` to `.env.local` and fill in `DATABASE_URL` and
   `MCP_BEARER_TOKEN` (any long random string you control).
3. Apply the schema and seed data:
   ```bash
   psql "$DATABASE_URL" -f schema.sql
   psql "$DATABASE_URL" -f seed.sql
   ```
4. `npm install`
5. `npm run dev` (local) or deploy to Vercel with the same two environment
   variables set in the project settings.
6. For the automated test suite, Docker is required (`npm test` brings up its
   own throwaway Postgres — see Automated tests below). No Neon credential is
   needed to run tests.

## Connecting from Claude Code

Register the deployed server as a remote HTTP MCP server with a bearer-token
header:

```bash
claude mcp add --transport http stuck-order-mcp \
  "https://stuck-order-mcp.vercel.app/api/mcp" \
  --header "Authorization: Bearer $MCP_BEARER_TOKEN"
```

(swap the URL for `http://localhost:3000/api/mcp` to point at a local
`npm run dev` instance instead).

- `--scope local` (the default) keeps the server private to you in this
  project; use `--scope project` to check it into `.mcp.json` for
  collaborators, or `--scope user` to make it available across all projects.
- Verify the connection with `claude mcp get stuck-order-mcp` — it should
  report `Status: ✔ Connected`.
- MCP servers are loaded when a Claude Code session starts, so if you added
  it mid-session, start a new session (or restart) before its tools show up.
- To remove it: `claude mcp remove stuck-order-mcp -s local`.

Once connected, ask Claude Code to investigate an order (e.g. "check order
ord_missed_capture for drift") or triage the fleet ("scan for drift in the
last 24 hours") — see [MCP tools](#mcp-tools) below for the full list and
[Verifying the workflow end to end](#verifying-the-workflow-end-to-end) for
the exact scenario each seeded order represents.

## Verifying the workflow end to end

Against the deployed URL (or `http://localhost:3000/api/mcp` locally), using
any MCP-compatible client, or directly with `curl` against the Streamable
HTTP endpoint:

```bash
curl -X POST "$MCP_URL/api/mcp" \
  -H "Authorization: Bearer $MCP_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"get_order_snapshot","arguments":{"order_id":"ord_missed_capture"}}}'
```

Expected walkthrough:

1. `get_order_snapshot` on `ord_missed_capture` returns diagnosis
   `missed_capture`.
2. `scan_fleet_for_drift` (default `window_hours: 24`) returns findings for
   `ord_missed_capture`, `ord_missed_refund`, `ord_false_capture`,
   `ord_false_refund`, and the `ord_dup_1`/`ord_dup_2` duplicate-charge pair —
   but nothing for `ord_clean`. All seeded orders are timestamped within the
   last 20 hours specifically so they're visible under the default window
   without needing a wider `window_hours` value.
3. `check_duplicate_charge` on `ord_dup_1` returns `duplicate_found: true`
   against `ord_dup_2` — the "customer says they were charged twice"
   workflow, answered directly for one order without a full fleet scan. It
   fetches candidate charges pre-filtered to the same customer/amount/
   currency within a wide window and checks the 10-minute rule directly
   against them, so it doesn't need the fleet-wide scan either.
   `get_order_snapshot` on the same order returns its diagnosis only —
   duplicate-charge checking is deliberately not duplicated into that tool,
   since `check_duplicate_charge` already exists for it.
4. `escalate_for_review` with that evidence creates an open record; calling
   it again with the same `drift_type` + `order_ids` updates the same row
   rather than creating a second one (check `list_open_escalations` before
   and after — the count doesn't change on the second call).
5. `list_open_escalations` shows the escalation, closing the loop:
   diagnose → escalate → visible to ops.
6. `acknowledge_escalation` with that escalation's `id` marks it
   `acknowledged` and stamps `acknowledged_at`; it then disappears from
   `list_open_escalations` (which only shows `open` records). Calling
   `acknowledge_escalation` again with the same id returns the existing
   record (`already_acknowledged: true`) instead of erroring or moving
   `acknowledged_at` a second time.
7. If the same drift is diagnosed again later, calling `escalate_for_review`
   with the same `drift_type` + `order_ids` reopens the acknowledged record
   (back to `open`, `acknowledged_at` cleared) rather than leaving it
   invisible in `list_open_escalations` — and archives the acknowledge+reopen
   cycle being overwritten into `reconciliation_action_history` first, so
   `get_escalation_history` on that escalation's `id` still shows when it was
   acknowledged, on what evidence, and when it was reopened, even though
   `reconciliation_actions` itself has already moved on to its new state.

## Automated tests

```bash
npm test
```

This brings up a throwaway Postgres via `docker-compose.test.yml`, runs the
integration suite against it, runs the unit suite, then tears the container
down — self-contained, no live/deployed database credential required. (Docker
must be available; `npm run test:db:up` / `npm run test:db:down` manage the
container directly if you want it to stay up between runs, and
`npm run test:integration` / `npm run test:unit` run either suite alone.)

**Integration tests** (`lib/reconciliation.integration.test.ts`, run against
real Postgres via `pg`) are now the primary coverage, because the
diagnostic and duplicate-matching decisions live in SQL (see Architecture)
and can't be exercised as pure functions anymore. They apply `schema.sql` +
`seed.sql` plus a few extra boundary fixtures, then assert: all 5 drift types
classify correctly via `order_diagnoses` (plus the clean case), evidence
JSON references the right underlying charge, the duplicate-charge queries
find the seeded pair, a pair exactly 10 minutes apart still matches
(confirming the boundary is inclusive), a pair 10 minutes and 1 second apart
does not, and a currency mismatch never matches regardless of timing. Because
`lib/queries.ts` is imported directly rather than re-typed, these tests are
verifying the literal SQL text that runs in production, not an equivalent
reimplementation of it. Further tests cover `DUPLICATE_PAIR_FOR_ORDERS_SQL`
specifically — the query `escalate_for_review`'s `verifyDuplicatePair` runs —
proving: it finds the seeded pair regardless of which order_id is listed
first; a refunded leg is rejected in either order; and, as a regression test
for a real bug caught during review (an anchor-on-most-recent-succeeded-charge
approach silently missed a match, asymmetrically, when an order had a second,
unrelated succeeded charge), that an extra unrelated charge on either order
doesn't hide the real match, in either argument order.

**Unit tests** (`lib/reconciliation.test.ts`) cover what's still pure:
`toDuplicateEvidence`'s formatting, that `buildIdempotencyKey` is
order-independent (which is what guarantees the database-level upsert
actually dedupes repeated escalations), and `validateEscalationOrderIds` —
`escalate_for_review`'s order_ids shape rule (exactly two distinct ids for
`duplicate_charge`, exactly one otherwise), pulled out as a pure function
specifically so this part of the escalation-boundary validation doesn't
depend on a database to test.

## Key assumptions

- The provider is simulated via seeded data rather than a real Stripe test
  account, to keep this self-contained and synthetic.
- No accounts/user-management system: access is gated by a single shared
  bearer token, not per-user login. There is one actor (the ops engineer),
  so a single permission level is sufficient.
- `reconciliation_actions` records an escalation, never a correction —
  applying the actual fix (issuing a refund, re-triggering a capture, etc.)
  happens outside this system, by a human, using the provider's own tools.
- Duplicate-charge detection matches on customer + amount + currency within
  a 10-minute window. `scan_fleet_for_drift` finds it fleet-wide via a single
  indexed self-join directly in Postgres (`idx_provider_charges_dup_lookup`),
  independently of the `limit`-bounded per-order diagnosis pass — the
  independence matters because a row-count cutoff, unlike a time-based one,
  can silently split a real duplicate pair across the boundary. Only rows
  that already match come back from the database; charges with no match are
  never pulled into app memory at all. `check_duplicate_charge` finds it for
  one order the same way, by fetching only the charges that could plausibly
  match it, rather than requiring a fleet-wide scan — `get_order_snapshot`
  deliberately doesn't duplicate that check, since a dedicated tool for it
  already exists. Both queries are a complete, exact implementation of the
  rule (not a wide prefilter re-verified in code) — see Architecture.
- The automated test suite never runs against the live/deployed Neon
  database. It uses a disposable local Postgres via `docker-compose.test.yml`
  instead, so running tests never requires — or risks — a shared production
  credential.

## Remaining risks / explicitly out of scope

- No pagination on `scan_fleet_for_drift`'s per-order diagnosis pass or on
  `list_open_escalations` beyond a simple `limit`/`window_hours` — fine at
  this data volume, would need it at real scale.
- `escalate_for_review` validates `order_ids` (exact count for `drift_type`,
  existence in `orders`) and re-derives the evidence it stores from the
  current diagnosis/duplicate-match state rather than trusting the caller's
  `evidence` payload — a stale or fabricated submission is rejected, not
  persisted. The one case this deliberately does *not* auto-resolve: if a
  `duplicate_charge` pair was already escalated and a human later refunds one
  of the two charges out-of-band, the pair stops matching (duplicate
  detection requires both charges `succeeded`), so a subsequent
  `escalate_for_review` call for it errors instead of reopening a
  now-resolved escalation — but the original open record isn't
  auto-acknowledged either. A human still has to call `acknowledge_escalation`
  to close it; nothing here infers "refunded" from "no longer matches" and
  acts on that inference automatically, consistent with this MCP never
  applying corrections itself.
- Putting the classification decision in a SQL view trades away pure-function
  unit testability for it (see Architecture) — that trade was made
  deliberately, but it does mean the `order_diagnoses` CASE logic can now
  only be verified with a real Postgres connection (the integration suite),
  not with a fast, dependency-free unit test.
- `acknowledge_escalation` has no manual "reopen" action — an escalation only
  moves back to `open` if `escalate_for_review` re-diagnoses the same drift
  (see Verifying the workflow, step 7). A human can't manually reopen a
  closed escalation without a fresh scan re-detecting it. There's also no
  notion of *who* acknowledged it (no per-user auth exists to attribute it
  to — see Key assumptions). Fine for a single shared ops actor; would need
  an identity if this grew multi-user.