Building a Payment Reconciliation Worker (Building A Payment Reconciliation Worker)

The previous part showed how a lease safely establishes ownership of a job. But even the sturdiest lease can't change one fact: sometimes a worker times out before getting a definitive answer from the PSP, the process crashes, or a lease expires and the job gets marked 'expired' — right when the payment actually succeeded on the PSP's side.

That's the reason a reconciliation worker exists: a sweeper that periodically scans for drift between the local system and the PSP's own records, and corrects it.

Local record: Payment #123 → Expired
PSP record:   Payment #123 → Succeeded
                  │
                  ▼
        Reconciliation worker detects the drift
                  │
                  ▼
        Local record → corrected to Captured

Where the concepts first show up

📦 Reconciliation
The process of comparing two independent sources of truth (local system, PSP) and correcting any difference.

📦 Drift
When the local state diverges from the PSP's actual record, usually due to a fault or timeout.

📦 Sweeper
A background job that periodically scans for records matching a criterion — such as 'aged' or 'expired'.

📦 FinalizePending
An intermediate status meaning the payment may have already resolved on the PSP's side, but the local system hasn't reached a definitive state yet.

Reconciliation isn't real-time correction; it's a safety net. The main path (webhook, synchronous response) works correctly most of the time; reconciliation cleans up whatever falls outside that 'most of the time'.

The real sources of drift

Drift is rarely random; it usually comes from a handful of recurring scenarios: a worker sends the request and the process crashes before reading the response; a network fault means the response never arrives even though the PSP completed the operation; or the lease TTL is set shorter than the PSP's response time, and the job gets marked expired too early.

Scenario 1: Worker crashed
  Request sent → PSP processed it → worker never read the response

Scenario 2: Network fault
  Request sent → PSP processed it → response got lost on the wire

Scenario 3: Lease expired too early
  Request sent → PSP responded slowly → lease expired → stuck watcher reset the job → but the PSP had already succeeded

All three share the same shape: the local record sits in an ambiguous or wrong status while the PSP's own record already knows the real outcome.

The sweeper's query: which records to scan

A reconciliation worker doesn't compare every record against the PSP continuously — that's expensive and unnecessary. It targets only 'suspect' records: ones past a given age that are still sitting in an intermediate status (FinalizePending, Expired, stuck in Processing for too long).

SELECT id, provider_ref FROM payments
WHERE status IN ('FinalizePending', 'Expired')
  AND updated_at < now() - interval '10 minutes';

That '10 minutes' threshold isn't arbitrary; it comes from an SLA for how long the normal path should take to resolve. Records younger than that aren't 'suspect' yet, just possibly slow.

Querying the PSP and deciding

For each candidate record, the worker checks the PSP's status query API (if available) or its own archived webhook history. Three outcomes are possible:

PSP: Succeeded → move the local record to Captured, publish the semantic event
PSP: Failed    → move the local record to Failed
PSP: Not Found / Unknown → treat as genuinely unresolved, route to the recovery flow

The key point here is that this transition must also be idempotent: even if the reconciliation worker processes the same record twice, the outcome shouldn't change (if the record is already Captured, it shouldn't republish the same event).

Alerting: a sweeper shouldn't run silently

Every drift the reconciliation worker finds should produce an observability signal. A sudden increase in drift count usually points to a problem in the main path (webhook processing, lease TTL, network) — reconciliation shouldn't hide that problem, it should surface it.

Metric	What it tells you
Candidate records scanned	How 'clean' the main path is running
Drift records corrected	Actual volume of data inconsistency
Records still unresolved	The queue requiring manual review

Distinctions that get blurred

❌ Reconciliation is real-time correction
✓ Reconciliation is a periodic safety net; it doesn't replace the main path

❌ Drift count should be zero, or the system is broken
✓ A low, stable drift rate is normal; a rising rate is the actual signal

❌ Every record should be compared against the PSP
✓ Only aged, intermediate-status records should be targeted

Main path vs. reconciliation

Dimension	Main path (webhook/sync)	Reconciliation worker
Speed	Seconds	Minutes to hours
Scope	Every payment	Only suspect/aged records
Purpose	The normal route	Safety net

Checklist for building a reconciliation worker

1. Is the 'aged' threshold in the sweeper query derived from a real business SLA, or picked arbitrarily?
2. Does querying the PSP respect your own rate-limit and retry policy?
3. Is the drift correction idempotent — does processing the same record twice leave the outcome unchanged?
4. Do unresolvable records (also unknown to the PSP) land in a visible queue for manual review?
5. Is drift count tracked as a metric that alerts on sudden increases?

What to take away

1. Reconciliation complements the main path, not replaces it; the main path works most of the time, the sweeper cleans up the rest.
2. The sweeper targets suspect records by age and status, not every record.
3. Any correction after querying the PSP must be idempotent and follow its own retry discipline.
4. Drift count is an observability signal; it should quietly trend toward zero, and a sudden spike is a warning.

A reconciliation worker doesn't show how perfect a system is — it shows how honest it is.

The next part covers the most uncomfortable shape of this drift: the customer was charged on the PSP's side, but no order exists locally — and how to heal that safely.