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.

Related MCP server: Lucid Observability Agent

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) 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, only presentation (toDuplicateEvidence, in lib/reconciliation.ts) and pair-level dedup.

• 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 these four tables that computes the drift diagnosis and pre-shapes the JSON the MCP tools return — see Architecture above.

MCP tools

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:

   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:

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 below for the full list and 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:

curl -X POST "$MCP_URL/api/mcp" \
  -H "Authorization: Bearer $MCP_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -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

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.

Unit tests (lib/reconciliation.test.ts) now only cover what's still pure: toDuplicateEvidence's formatting, and that buildIdempotencyKey is order-independent, which is what guarantees the database-level upsert actually dedupes repeated escalations.

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.

• 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 only has two states (open → acknowledged); there is no "reopen" transition and 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.