Reconciliation answers a different question from checkout: does every verified payment map to exactly one local order with the expected amount and final state? A robust process uses your client_txn_id as the primary bridge, gateway order_id as a secondary reference, and UTR or provider reference as settlement evidence when available.
Do not match on amount alone. Common prices, split attempts, late payments, and retries make amount-only matching ambiguous. The reconciliation job should classify exceptions without silently changing fulfilled orders, then give operators enough references to resolve them safely.
Design principles
The boundaries that keep this flow reliable
Use layered identifiers
Start with client_txn_id, verify gateway order_id, compare amount and currency, and retain provider/UTR evidence for investigation.
Make exceptions explicit
Unknown reference, amount mismatch, late success, duplicate evidence, and missing local order each need a distinct review category.
Preserve an audit trail
Record who or what changed state, the evidence used, previous value, resulting value, and timestamp.
Implementation workflow
From request to a durable result
- 1
Select a closed time window
Process a bounded period with overlap so delayed events are included. Store a checkpoint but make reruns idempotent.
- 2
Load local and verified records
Join by stable references within the tenant boundary and compare expected amount, status, and event history.
- 3
Classify every row
Mark matched, still pending, late success, amount mismatch, duplicate, or orphaned. Do not hide unresolved rows in a generic failed bucket.
- 4
Resolve through controlled actions
Automate unambiguous transitions and send risky mismatches to an authorized operator with a recorded resolution reason.
Reconciliation decision outline
sqlSELECT
o.client_txn_id,
o.expected_amount_paise,
p.status AS payment_status,
p.amount_paise AS verified_amount_paise,
CASE
WHEN p.id IS NULL THEN 'payment_missing'
WHEN p.amount_paise <> o.expected_amount_paise THEN 'amount_mismatch'
WHEN p.status = 'paid' AND o.status = 'paid' THEN 'matched'
WHEN p.status = 'paid' THEN 'late_success_review'
ELSE 'pending'
END AS reconciliation_result
FROM orders o
LEFT JOIN verified_payments p USING (tenant_id, client_txn_id);Production checklist
Verify before going live
- References are unique inside each tenant
- Money is compared in integer minor units
- Rerunning a time window produces no duplicate fulfilment
- Every unresolved category has an owner and runbook
- Manual resolutions capture actor, reason, and evidence
Failure recovery
Reconciliation anti-patterns
Matching by amount
Two customers can pay the same amount. Require stable order references and use amount as validation, not identity.
Ignoring late success
An expired local order may receive later verified evidence. Route it to a defined refund, honour, or support policy.
Editing rows manually
Direct database edits erase accountability. Use an authorized resolution action that writes an immutable audit event.
FAQ
Questions developers ask
Is payment reconciliation the same as settlement reconciliation?
Payment reconciliation maps verified customer payments to orders. Settlement reconciliation additionally compares provider or bank settlement evidence and may follow a different timing cycle.
Can reconciliation run more than once?
Yes. Design the job and every resulting state transition to be idempotent so overlapping windows are safe.
What should happen to amount mismatches?
Do not auto-fulfil. Hold them in an exception queue with order, payment, and reference evidence for authorized review.