# 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:

```json
{
  "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

```powershell
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

```powershell
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:

```powershell
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](#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:

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

Runtime variables required by the service:

```text
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

```text
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](./ASSUMPTIONS_AND_EXCLUSIONS.md)
- [AI worklog](./AI_WORKLOG.md)

## 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.