mufassall

Cheat sheet · Business processes

Payment reconciliation

14 min · Intermediate · mufassall.dtwin.uz/en/l/reconciliation/

← Open the lesson

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
Final scene

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_pager

Mermaid: 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 0

D2

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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Mismatches0/day35/day
Unmatched amount0 $1 950 $
Fixed automatically0%68%
Open review cases011

Steps

  1. Case The ledger is written from answers and callbacks

    A buyer pays at checkout.

  2. Problem The ledger and the money drift apart

    The charge call times out after 10 s, so the API marks the order failed.

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

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

  5. Case Every difference falls into one of four classes

    The job labels each row that did not match cleanly.

  6. Fix Auto-fix the safe class, with an audit record

    For each missing_ours row the job asks the provider's status API once more.

  7. Fix Everything else goes to a review queue with an SLA

    The other 11 cases become review cases.

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

  9. Problem A re-run fixes the same thing twice

    The provider found an error and re-sent yesterday's report.

  10. Fix Every fix has a key; re-runs change nothing

    Before changing the ledger the job inserts the fix into recon_fixes with a unique key.

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