Payment Reconciliation Copilot

A small, safety-bounded MCP server for operations investigations such as “I was charged twice” and “my refund never arrived.” It turns normalized payment-event data into an auditable timeline, detects defined reconciliation anomalies, and creates a human-review escalation. It never executes, retries, captures, voids, or refunds payments.

Try the hosted service

Endpoint	URL
Welcome	https://transactions-mcp.onrender.com/
Health	https://transactions-mcp.onrender.com/health
Streamable HTTP MCP	https://transactions-mcp.onrender.com/mcp

The hosted deployment contains synthetic data only. Configure any MCP client that supports Streamable HTTP with the /mcp URL. Client configuration syntax varies; conceptually:

{
  "mcpServers": {
    "payment-reconciliation": {
      "url": "https://transactions-mcp.onrender.com/mcp"
    }
  }
}

After connecting, ask the client or its LLM:

Investigate the duplicate-charge complaint for ORD-DUP-001. Use the connected tools, propose a resolution, and do not execute a payment action.

What it does

1. Finds a transaction from an order ID, customer email, or amount plus date range.
2. Returns its normalized event timeline and any payload conflicts.
3. Detects duplicate-charge, stuck-refund, and out-of-order-webhook evidence.
4. Produces one diagnostic escalation per anomaly for human review.

The design is intentionally read-heavy. Only anomalies and escalations may be written; transactions, provider state, and payment state are never changed by MCP tools.

MCP tools

Tool	Input	Result	Safety boundary
find_transaction	order_id or customer_email or amount + date_range	Transaction summary records	Read-only
get_transaction_timeline	transaction_id	Ordered normalized events and linked conflicts	Read-only
detect_anomaly	transaction_id	Existing or newly detected anomaly records	Writes only idempotent anomaly audit records
propose_resolution	anomaly_id	Existing or newly created escalation with reasoning	Writes only one diagnostic escalation; never executes anything

Data model and idempotency

The database, not application memory, enforces the important guarantees:

Guarantee	Database constraint	Retry behavior
First provider event wins	UNIQUE (provider_id, provider_event_id) on transaction_events	A conflicting later payload is recorded in event_conflicts; the original is never overwritten.
One conflict per distinct conflicting payload	UNIQUE (transaction_event_id, conflicting_payload_hash)	Repeated delivery of the same conflict does not create another row.
Same evidence, same anomaly	UNIQUE (transaction_id, type, evidence_hash) on anomalies	Detection updates only last_seen_at; evidence remains immutable.
One escalation per anomaly	UNIQUE (anomaly_id) on escalations	Retried proposals return the same escalation regardless of status.

evidence_hash is generated from canonically ordered evidence event IDs and the detection window. This makes detection retries safe while allowing new evidence to produce a separate anomaly.

Demo scenarios

The synthetic seed contains 24 transactions across MockStripe and MockAdyen:

Scenario	Order ID	Expected result
Duplicate charge	ORD-DUP-001	duplicate_charge from two webhook events sharing one intent_id
Stuck refund	ORD-REFUND-001	stuck_refund from an attempt without a completion
Out-of-order webhook	ORD-ORDER-001	out_of_order_webhook
Conflicting provider payload	ORD-CONFLICT-001	Timeline includes an event_conflicts record

For the duplicate case, call detect_anomaly twice and then call propose_resolution twice. The anomaly and escalation IDs should remain stable across retries.

Run locally

Prerequisites

• Node.js 20+
• PostgreSQL database dedicated to this synthetic demo

Setup

npm ci
Copy-Item .env.example .env
# Set DATABASE_URL in .env to a disposable local/development Postgres database.
npm run db:migrate
npm run db:seed

Warning: npm run db:seed deletes all records in this project’s six tables before rebuilding the deterministic synthetic fixtures. Never run it against real, shared, or production-like data.

Commands

npm run dev                 # Local development server with tsx watch
npm run build               # Emit production JavaScript to dist/
npm run start               # Run compiled production server
npx tsc --noEmit            # Strict type check
npm run test -- --run       # Focused integration suite
npm run db:generate         # Generate a Drizzle migration after schema changes
npm run db:migrate          # Apply pending migrations
npm run db:seed             # Reset and seed the synthetic database

Local endpoints are http://localhost:3000/, /health, and /mcp.

Test and verification

The focused integration suite uses the real disposable Postgres database and resets synthetic fixtures before each test:

npm run test -- --run

It verifies:

• find_transaction rejects ambiguous input and does not change mutable records.
• get_transaction_timeline returns linked payload conflicts without writes.
• Calling detect_anomaly repeatedly returns one anomaly for the same evidence.
• Calling propose_resolution repeatedly returns one escalation, including after escalation status changes.

For hosted verification, connect an MCP client to the deployed /mcp URL and run the duplicate-charge workflow in Demo scenarios. The deployment was also exercised through an LLM-connected MCP client.

Deployment

The service is deployed to Render as a Node web service. Its production build compiles TypeScript before starting Node:

Build command: npm ci --include=dev && npm run build
Start command: npm run start
Health check: /health

Runtime variables required by the service:

DATABASE_URL=<isolated synthetic/demo PostgreSQL connection string>
NODE_ENV=production
DB_POOL_MAX=3

Do not run the destructive synthetic seed automatically on every deployment. Apply migrations and seed a new isolated demo database deliberately.

Project layout

src/db/                 Drizzle schema, pooled client, and deterministic seed
src/reconciliation/     Lookup, timeline, anomaly, and escalation behavior plus tests
src/mcp/                MCP server and tool registrations
src/index.ts            Streamable HTTP endpoint, welcome/health routes, shutdown handling
drizzle/                Generated SQL migrations
ASSUMPTIONS_AND_EXCLUSIONS.md
AI_WORKLOG.md
WALKTHROUGH_SCRIPT.md

Submission assets

• Assumptions and exclusions
• AI worklog

Safety reminder

This is a synthetic-data demonstration. The public endpoint is unauthenticated by intentional assignment scope, so it must never be pointed at real customer, transaction, or payment-provider data.