Reconciliation of PSP payments and reports

TL; DR

Reconciliation is the daily automated stitching of your ledger and events (auth/capture/refund/payout) with PSP/acquirer/bank reports. The key to success: a single data model, deterministic matching keys, strict idempotency, sum/FX/time tolerances, DLQ queue for controversial cases and auto-correction playbooks. KPI: Recon Mismatch Rate↓, Aging of Unreconciled↓, Auto-match%↑.

1) Why and what we check

Objectives: confirmation of revenue and commissions, duplicate/loss detection, correct settlement by days and currencies, refund-to-source control, compliance with audit/regulator.

• Deposits: `auth → capture → settle`
• Refunds: full/partial, statuses and amounts
• Payouts/Withdrawals: Outgoing Method Payments
• Fees & Adjustments: PSP fees, interchange schemes, corrections
• Chargebacks/Disputes: Beyond Your Initiative
• FX/Conversions: rates, spreads, fixing point

2) Data sources

Internal Events: event bus/Kafka, 'payments _ flat', 'refunds', 'payouts', your Ledger.

• Transactions (operational statements)
• Settlements/Batches
• Fees/Statements
• Chargebacks/Disputes
• Payouts/OCT/RTP/SEPA registries
• Bank Statements: CAMT MT940/CSV/ISO20022, credit lift.

3) Matching keys

1. provider_txid ↔ provider_txid (unique PSP ID)

2. idempotency_key/ merchant_reference (if stable in PSP)

3. (amount, currency, timestamp_bucket, last4/bin, auth_code)

4. Fuzzy layer: ± tolerances by sum/time, BIN/issuer country, status family

• Keep both 'payment _ id' and 'provider _ txid'.
• For partial/refund, add'sequence _ index'or'refund _ line _ id'.
• Для payouts — `payout_batch_id + line_id`.
• For FX - 'exec _ id' (conversion) and the source of the rate.

4) Data model (normalized layer)

json
{
"source": "INTERNAL    PSP_TX    PSP_SETTLEMENT    BANK",
"provider": "Acquirer_A",
"payment_id": "pay_123",
"provider_txid": "psp_tx_789",
"kind": "AUTH    CAPTURE    REFUND    PAYOUT    FEE    SETTLEMENT    CHARGEBACK",
"sequence": 0,
"amount": 100. 00,
"currency": "EUR",
"fee_amount": 1. 20,
"fx_rate": 1. 0000,
"fx_src": "PSP    ECB    BANK",
"status": "APPROVED    CAPTURED    SUCCESS    FAILED    SETTLED",
"event_ts": "2025-11-03T12:00:00Z",
"settlement_date": "2025-11-05",
"account": "PSP_MERCHANT_CARD_A",
"matching_keys": {
"provider_txid": "psp_tx_789",
"merchant_ref": "mr_456",
"idem_key": "idem_abc"
},
"hash_row": "sha256(...)"
}

5) Reconciliation process (ETL/orchestration)

1. Ingest: we take PSP reports (SFTP/API), validate the scheme/signatures, save to 'raw'.
2. Normalize: mappim of the field to the unified format (currency ISO, decimals, UTC time zone).
3. Match: algorithm for matching the key tree with tolerances.
4. Post-match: form diff (discrepancies) and journal entries for the ledger/corrections.
5. Settle: stitch 'PSP _ SETTLEMENT ↔ BANK' (crediting to the account), scatter by day/batch.
6. Report: dashboard, alerts; controversial in DLQ for manual parsing/auto-replaying.

Idempotence: for each file/page - 'ingest _ id'. Reloading does not change the result.

6) Tolerances and rules

Time: '± 15 min' for transactions, '± 1 day' for settlement.
Amount: '≤ 0. 01 'base currency or' ≤ 10 bps' for FX/fee differences.
FX: we allow discrepancy with the bank if the source of the exchange rate is different; fix 'fx _ src'.
Partial/Multiple: The sum of partial/refund lines must equal the internal balance.

7) Diff taxonomy

8) Ledger & Accounting

Capture: `DR Accounts Receivable / CR Revenue` и `DR Cash (upon settle) / CR Accounts Receivable`

Fee: `DR Fees / CR Cash or Payable`

Refund: reverse postings pro rata partial

Chargeback: separate accounts and reserve for disputes

FX reval: daily revaluation of AR/cache balance at 'fx _ src _ policy'

9) KPIs and objectives

Auto-match% = auto-match lines/all lines (95% ≥ target)

Recon Mismatch Rate = diff lines/all lines (≤ 1-2%)

Aging of Unconciled: p50/p95 days in DLQ (p95 ≤ 3 days)

Settlement Time: proportion of batches stitched with D-day bank (≥ 99%)

Fee Accuracy: provider fee discrepancies (≤ 0. 1% of turnover)

Duplicate/Orphan Incidents: Aiming for 0

10) SQL slices

10. 1 Basic provider_txid matching

sql
WITH i AS (
SELECT provider, provider_txid, kind, amount, currency, event_ts
FROM internal_norm
),
p AS (
SELECT provider, provider_txid, kind, amount, currency, event_ts
FROM psp_norm
)
SELECT
COALESCE(i. provider_txid, p. provider_txid) AS txid,
COALESCE(i. provider, p. provider) AS provider,
i.kind AS kind_internal, p. kind AS kind_psp,
i.amount AS amount_internal, p. amount AS amount_psp,
i.currency, p. currency,
CASE
WHEN i.provider_txid IS NULL THEN 'MISSING_INTERNAL'
WHEN p. provider_txid IS NULL THEN 'MISSING_PSP'
WHEN ABS(i. amount - p. amount) > 0. 01 THEN 'AMOUNT_MISMATCH'
ELSE 'MATCHED'
END AS recon_status
FROM i
FULL OUTER JOIN p USING (provider, provider_txid);

10. 2 Settlement ↔ Bank

sql
SELECT s. settlement_date, s. batch_id, s. currency,
s. amount_settled, b. amount_bank,
(b. amount_bank - s. amount_settled) AS diff
FROM psp_settlements s
LEFT JOIN bank_statements b
ON b. value_date = s. settlement_date
AND b. currency = s. currency
AND ABS(b. amount_bank - s. amount_settled) <= 0. 5;

10. 3 Aging DLQ

sql
SELECT diff_type,
COUNT() AS cnt,
PERCENTILE_CONT(0. 5) WITHIN GROUP (ORDER BY AGE(NOW(), created_at)) AS p50_age,
PERCENTILE_CONT(0. 95) WITHIN GROUP (ORDER BY AGE(NOW(), created_at)) AS p95_age
FROM recon_dlq
GROUP BY diff_type
ORDER BY cnt DESC;

11) Features on rails/cases

Maps: differences between 'auth' and 'capture', late 'settlement' adjustments, interchange/circuit fee - separate lines.
A2A/Open Banking/RTP: instant confirmations, but 'reversal' possible; check 'payout' and returns.
Wallets: often perfect 'provider _ txid', quick 'refund'; watch the fee lines.
Vouchers: no symmetric refund - reflect correctly in policy and reports.
Crypto: on-chain hash ↔ provider_txid; confirmations N; accounting of network commissions and possible rebates; exchange rate - at the time of conversion.

12) Operational playbooks

Surge in MISSING_INTERNAL: check for loss of webhooks/retrays, replay ingestion, enable API polling.
AMOUNT_MISMATCH from one PSP: compare rounding/VAT/fee model, request a correction statement.

Settlement does not link to the bank: check value date, bank fees, T + N delays; temporarily put in the "Suspense Account."

Mass REFUND_OVER: immediate stop auto-refands, idempotency audit, manual correction.
FX_DRIFT: fix the policy of the exchange rate source (ECB/PSP/BANK), recalculate P&L differences.

13) Control and safety

Idempotence of ingestion: 'file _ id + checksum' and download history.
Access (RBAC) and 4-eye control: for manual corrections/journal entries.
Audit trail: all matches/diffs/corrections - in an unchangeable log.
Data quality: schemes, mandatory fields, currency/scale validation.

14) Dashboard and alerts

Widgets: Auto-match%, Mismatch Rate, Aging DLQ, Settlement Timing, Fee Accuracy, top PSP by diff, diff-type map.

• 'Auto-match% <90% 'by provider/day → P1
• 'Aging p95> 3 days' → P2
• `AMOUNT_MISMATCH spike` → P1
• 'Bank≠Settlement' by amount/currency → P0

15) Test Cases (UAT/Prod)

1. Reloading the same file → 0 side effects (idempotency).
2. Partial refands (3 lines) → amount matches, match by sequence.
3. FX conversion: exchange rate discrepancy within the tolerance → correct match.
4. Duplicate provider_txid in the report → dedup and alert.
5. The missing webhook capture → polling closed the gap, the status is aligned.
6. Settlement batch with fee line → correct breakdown on Revenue/Fee/Net.

16) Frequent mistakes and how to avoid

Compare 'attempt' vs' capture '→ keep the same granularity.
The absence of 'provider _ txid' in the → log loses the accuracy of the match.
Ignore timezone → offset by settlement dates.
There are no DLQ/retras → "eternal" discrepancies.
Manual edits without a log → inconsistency with auditing.

Fuzzy tolerances → either a re-match or "all in DLQ."

17) Implementation checklist

• Unified normalization scheme and PSP/method/account directories
• Mapping Key Tree (txid → merchant_ref → fuzzy)
• Amount/Time Tolerances/FX, Course Source Policy
• Idempotent ingestion, DLQ, retrai, alerts
• Settlement↔Bank Reconciliation, Suspense Account Policy
• Dashboard KPI, financial/audit reporting
• Playbooks and SLA parsing diff cases

Summary

Reconciliation is an engineering discipline: normalization, reliable keys, tolerances, automatic matches and transparent corrections. With such a contour, you stabilize revenue and commissions, minimize "black holes," accelerate period closing and get audited without pain: Auto- match%↑, Mismatch↓, Aging↓.