Cheat sheet · Business processes
Payment reconciliation
The problem
A shop records card payments from the provider's answers and callbacks. Some of them get lost, time out or arrive twice, so the ledger and the real money drift apart. Every morning a job compares the ledger with the provider's settlement report, fixes what is safe to fix and hands the rest to a person.
What you learn
- Why a ledger built from callbacks drifts from the money, and why amounts are integer minor units
- Matching the settlement report by provider transaction id, currency and a date window
- Four mismatch classes: which one the job may fix itself and which go to a review queue
- Day-end totals per currency, alerts, and re-runs that never fix the same thing twice
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_api["Payments API"]
n_provider>"Provider"]
n_ledger[("Ledger")]
n_files[("Settlement reports")]
n_recon("Recon job")
n_review[["Review queue"]]
n_analyst(("Finance analyst"))
n_pager>"On-call alert"]
n_api --> n_provider
n_provider -.-> n_api
n_api --> n_ledger
n_provider --> n_files
n_files --> n_recon
n_recon --> n_ledger
n_recon --> n_provider
n_recon --> n_review
n_review --> n_analyst
n_recon --x n_pagerMermaid: request sequence
sequenceDiagram
participant n_provider as Provider
participant n_api as Payments API
participant n_files as Settlement reports
participant n_recon as Recon job
participant n_ledger as Ledger
participant n_review as Review queue
participant n_pager as On-call alert
Note over n_provider,n_api: The ledger is written from answers and callbacks
n_provider->>n_api: POST /payments/callback HTTP/1.1
n_api-->>n_provider: HTTP/1.1 200 OK
Note over n_api,n_provider: The ledger and the money drift apart
n_api->>n_provider: 10:20:31 POST /v1/charges ord_3307 amount_minor=4500
Note over n_provider,n_files: The provider's settlement report
n_provider->>n_files: settlement_2026-11-17.csv (T+1, ready at 06:00 UTC)
Note over n_recon,n_ledger: The recon job joins the report with the ledger
n_recon->>n_ledger: SELECT s.provider_txn_id, s.amount_minor AS theirs,
n_ledger-->>n_recon: 48 202 rows
Note over n_recon,n_ledger: Every difference falls into one of four classes
n_recon->>n_ledger: recon 2026-11-17: 48 198 provider lines, 48 183 ledger rows
Note over n_recon,n_provider: Auto-fix the safe class, with an audit record
n_recon->>n_provider: GET /v1/payments/ptx_8813 HTTP/1.1
n_provider-->>n_recon: HTTP/1.1 200 OK
Note over n_recon,n_review: Everything else goes to a review queue with an SLA
n_recon->>n_review: review_case (new)
Note over n_recon,n_pager: Day-end totals per currency
n_recon->>n_pager: ALERT recon.totals day=2026-11-17
Note over n_recon,n_ledger: A re-run fixes the same thing twice
n_recon->>n_ledger: 06:40 provider re-sent settlement_2026-11-17.csv (v2)
Note over n_recon,n_ledger: Every fix has a key#59; re-runs change nothing
n_recon->>n_ledger: INSERT INTO recon_fixes (report_date, provider_txn_id, action)
n_ledger-->>n_recon: INSERT 0 0D2
direction: right
n_api: "Payments API" {
shape: rectangle
}
n_provider: "Provider" {
shape: cloud
}
n_ledger: "Ledger" {
shape: cylinder
}
n_files: "Settlement reports" {
shape: stored_data
}
n_recon: "Recon job" {
shape: rectangle
style.multiple: true
}
n_review: "Review queue" {
shape: queue
}
n_analyst: "Finance analyst" {
shape: person
}
n_pager: "On-call alert" {
shape: cloud
}
n_api -> n_provider
n_provider -> n_api {
style.stroke-dash: 3
}
n_api -> n_ledger
n_provider -> n_files
n_files -> n_recon
n_recon -> n_ledger
n_recon -> n_provider
n_recon -> n_review
n_review -> n_analyst
n_recon -> n_pager {
target-arrowhead.shape: cross
}PlantUML: request sequence
@startuml
boundary "Provider" as n_provider
participant "Payments API" as n_api
database "Settlement reports" as n_files
participant "Recon job" as n_recon
database "Ledger" as n_ledger
queue "Review queue" as n_review
boundary "On-call alert" as n_pager
note over n_provider, n_api : The ledger is written from answers and callbacks
n_provider -> n_api : POST /payments/callback HTTP/1.1
n_api --> n_provider : HTTP/1.1 200 OK
note over n_api, n_provider : The ledger and the money drift apart
n_api -> n_provider : 10:20:31 POST /v1/charges ord_3307 amount_minor=4500
note over n_provider, n_files : The provider's settlement report
n_provider -> n_files : settlement_2026-11-17.csv (T+1, ready at 06:00 UTC)
note over n_recon, n_ledger : The recon job joins the report with the ledger
n_recon -> n_ledger : SELECT s.provider_txn_id, s.amount_minor AS theirs,
n_ledger --> n_recon : 48 202 rows
note over n_recon, n_ledger : Every difference falls into one of four classes
n_recon -> n_ledger : recon 2026-11-17: 48 198 provider lines, 48 183 ledger rows
note over n_recon, n_provider : Auto-fix the safe class, with an audit record
n_recon -> n_provider : GET /v1/payments/ptx_8813 HTTP/1.1
n_provider --> n_recon : HTTP/1.1 200 OK
note over n_recon, n_review : Everything else goes to a review queue with an SLA
n_recon -> n_review : review_case (new)
note over n_recon, n_pager : Day-end totals per currency
n_recon -> n_pager : ALERT recon.totals day=2026-11-17
note over n_recon, n_ledger : A re-run fixes the same thing twice
n_recon -> n_ledger : [duplicate] 06:40 provider re-sent settlement_2026-11-17.csv (v2)
note over n_recon, n_ledger : Every fix has a key; re-runs change nothing
n_recon -> n_ledger : INSERT INTO recon_fixes (report_date, provider_txn_id, action)
n_ledger --> n_recon : INSERT 0 0
@endumlPlantUML: components
@startuml
left to right direction
component "Payments API" as n_api
cloud "Provider" as n_provider
database "Ledger" as n_ledger
storage "Settlement reports" as n_files
collections "Recon job" as n_recon
queue "Review queue" as n_review
actor "Finance analyst" as n_analyst
cloud "On-call alert" as n_pager
n_api --> n_provider
n_provider ..> n_api
n_api --> n_ledger
n_provider --> n_files
n_files --> n_recon
n_recon --> n_ledger
n_recon --> n_provider
n_recon --> n_review
n_review --> n_analyst
n_recon -[#c62828]-> n_pager
@endumlRecap
Five rules of reconciliation.
1) Store amounts as integer minor units with their currency. 2) Every day, match the provider's report with your ledger by their transaction id, currency and a widened date window. 3) Classify every difference; auto-fix only rows the provider confirms, moving them forward, with an audit record. 4) Send the rest to a review queue with both sides and an SLA, and check totals per currency so nothing unexplained hides. 5) Key every fix and case so a re-run changes nothing.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Mismatches | 0/day | 35/day |
| Unmatched amount | 0 $ | 1 950 $ |
| Fixed automatically | 0% | 68% |
| Open review cases | 0 | 11 |
Steps
- Case The ledger is written from answers and callbacks
A buyer pays at checkout.
- Problem The ledger and the money drift apart
The charge call times out after 10 s, so the API marks the order
failed. - Fix The provider's settlement report
Every morning the provider publishes yesterday's report as a file (or an API you page through): one line per charge, refund and fee it booked.
- Fix The recon job joins the report with the ledger
A recon job loads the file into a staging table and joins it with the ledger on the provider's transaction id and currency.
- Case Every difference falls into one of four classes
The job labels each row that did not match cleanly.
- Fix Auto-fix the safe class, with an audit record
For each
missing_oursrow the job asks the provider's status API once more. - Fix Everything else goes to a review queue with an SLA
The other 11 cases become review cases.
- Fix Day-end totals per currency
Row-by-row matching can miss things a sum catches: a fee rule that changed, a line the parser skipped.
- Problem A re-run fixes the same thing twice
The provider found an error and re-sent yesterday's report.
- Fix Every fix has a key; re-runs change nothing
Before changing the ledger the job inserts the fix into
recon_fixeswith a unique key. - Recap Recap
Five rules of reconciliation.
Terms
- Reconciliation
- Comparing your own ledger with an outside record of the same money and resolving every difference.
- Settlement report
- The payment provider's daily (T+1) list of every charge, refund and fee it actually booked.
- Minor units
- Money stored as an integer of the smallest unit (tiyin, cents), so sums are exact.