PSP to’lovlari va hisobotlarini solishtirish

TL; DR

Solishtirish - bu har kuni PSP/ekvayer/bank hisobotlari bilan ledjer va hodisalarni (auth/capture/refund/payout) avtomatlashtirilgan tikishdir. Muvaffaqiyat kaliti: ma’lumotlarning yagona modeli, determinatsiyalangan taqqoslash kalitlari, qat’iy idempotentlik ,/FX/vaqt yig’indisi bo’yicha toleranslar, bahsli keyslar va avto tuzatish pleybuklari uchun DLQ navbati. KPI: Recon Mismatch Rate↓, Aging of Unreconciled↓, Auto-match%↑.

1) Nima uchun va nimani solishtiryapmiz

Maqsadlar: tushum va komissiyalarni tasdiqlash, dubllar/yo’qotishlarni aniqlash, kunlar va valyutalar bo’yicha to’g "ri settlement, refund-to-source nazorati, audit/tartibga soluvchiga muvofiqlik.

• Deposits: `auth → capture → settle`
• Refunds: full/partial, maqomi va summasi
• Payouts/Withdrawals: usullar bo’yicha chiqadigan to’lovlar
• Fees & Adjustments: PSP komissiyalari, intercheynj sxemalari, tuzatishlar
• Chargebacks/Disputes: tashabbusingizdan tashqari
• FX/Konvertatsiyalar: kurslar, spredlar, qayd etish momenti

2) Ma’lumotlar manbalari

Internal Events (sizniki): Voqealar shinasi/Kafka,’payments _ flat’,’refunds’,’payouts’, sizning Ledger.

• Transactions (operatsion ko’chirmalar)
• Settlements/Batches (T + N ajratmalari)
• Fees/Statements (komissiya, tuzatishlar)
• Chargebacks/Disputes
• Payouts/OCT/RTP/SEPA reyestrlari
• Bank Statements: MT940/CSV/ISO20022 CAMT, o’tkazmalarni tortish.

3) Taqqoslash kalitlari (matching keys)

1. provider_txid provider_txid (noyob PSP ID)

2. idempotency_key/ merchant_reference (agar PSPda barqaror boʻlsa)

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

4. Fuzzy-qatlam: yigʻindisi/vaqti boʻyicha ± toleranslar, BIN/issuer country, status family

• Ikkalasini ham saqlang:’payment _ id’va’provider _ txid’.
• partial/refund uchun’sequence _ index’yoki’refund _ line _ id’qoʻshing.
• Для payouts — `payout_batch_id + line_id`.
• FX uchun -’exec _ id’(konvertatsiya) va kurs manbai.

4) Ma’lumotlar modeli (normallashtirilgan qatlam)

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) Solishtirish jarayoni (ETL/orkestrlash)

1. Ingest: PSP (SFTP/API) hisobotlarini olib, sxema/imzolarni tasdiqlaymiz,’raw’da saqlaymiz.
2. Normalize: yagona formatdagi maydon mappimi (currency ISO, decimals, UTC taymzon).
3. Match: yogʻoch kalitlarini tolerans bilan taqqoslash algoritmi.
4. Post-match: diff (tafovutlar) va journal entries ni tuzatish/tuzatish uchun shakllantiramiz.
5. Settle:’PSP _ SETTLEMENT BANK’ni tikamiz (hisobvaraqqa o’tkazamiz), kunlar/kunlar bo’yicha tarqatamiz.
6. Report: dashbord, alertlar; DLQda bahs-munozaralar qo’lda tahlil qilish/avto qayta o’ynash uchun.

Idempotentlik: har bir fayl/sahifa uchun -’ingest _ id’. Qayta yuklash natijani oʻzgartirmaydi.

6) Tolerans (tolerances) va qoidalar

Vaqt: tranzaksiyalar uchun’± 15 daqiqa’, settlement uchun’± 1 kun’.
Summa:’0 ≤. 01’bazaviy valyuta yoki’≤ 10 bps’uchun FX/fee-farqlar.
FX: agar kurs manbai boshqacha bo’lsa, bank bilan tafovutga yo’l qo’yamiz; ’fx _ src’ ni aniqlaymiz.
Partial/Multiple: partial/refund liniyalari bo’yicha summa ichki qoldiqga teng bo’lishi kerak.

7) Tafovutlarni qayta ishlash (diff taxonomy)

8) Ledger & Accounting (simlar)

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

Fee: `DR Fees / CR Cash or Payable`

Refund: partial proporsional teskari simlar

Chargeback: alohida hisobvaraqlar va nizolar uchun zaxira

FX reval: «fx _ src _ policy» kursi bo’yicha har kuni AR/kesh qoldig’ini qayta baholash

9) KPI va maqsadlar

Auto-match% = avtomatik ravishda taqqoslangan satrlar/barcha satrlar (maqsad ≥ 95%)

Recon Mismatch Rate = diff-satrlar/barcha satrlar (1-2% ≤)

Aging of Unreconciled: p50/p95 DLQ da bo’lish kuni (p95 ≤ 3 kun)

Settlement Timeliness: bank bilan tikilgan batchalar ulushi D-kun (99% ≥)

Fee Accuracy: provayderlar boʻyicha komissiya tafovutlari (≤ 0. 1% aylanma)

Duplicate/Orphan Incidents: 0 ga intiladi

10) SQL-kesmalar

10. 1 provider_txid bo’yicha bazaviy taqqoslash

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) Relslar/keyslar bo’yicha xususiyatlar

Xaritalar:’auth’va’capture’orasidagi farqlar, kech’settlement’tuzatishlar, intercheynj/sxema fee - alohida chiziqlar.
A2A/Open Banking/RTP: bir zumda tasdiqlash, lekin «reversal» bo’lishi mumkin; ’payout’ va qaytarishlarni tekshiring.
Hamyonlar: ko’pincha ideal’provider _ txid’, tez’refund’; fee-liniyalarni kuzatib boring.
Vaucherlar: simmetrik refund yo’q - siyosat va hisobotlarda to’g’ri aks ettiring.
Kripto: on-cheyn hash provider_txid; N konfirmalar; tarmoq komissiyalari va mumkin bo’lgan rebeytlar hisobi; kurs - konvertatsiya qilish vaqtida.

12) Operatsion pleybuklar

MISSING_INTERNAL koʻtarilishi: vebxuklarni/retraylarni yoʻqotishni tekshirish, ingestionni ortda qoldirish, API pollingi yoqish.
AMOUNT_MISMATCH bitta PSPdan: yaxlitlash/QQS/fee-modelni solishtirish, tuzatuvchi statement so’rash.
Settlement bank bilan tikilmaydi: value date, bank komissiyasini tekshirish, kechikishlar T + N; vaqtincha «Suspense Account» ga qo’yish.
Ommaviy REFUND_OVER: avto-refandlarni darhol to’xtatish, idempotentlik auditi, qo’lda tuzatish.
FX_DRIFT: kursning policy manbaini (ECB/PSP/BANK) qayd etish, P&L farqini qayta hisoblash.

13) Nazorat va xavfsizlik

ingestion idempotentligi:’file _ id + checksum’va yuklash jurnali.
Kirish (RBAC) va 4 ko’zli nazorat: qo’lda tuzatish/jurnal o’tkazmalariga.
Audit-trail: barcha o’yinlar/diflar/tuzatishlar - o’zgarmas jurnalda.
Ma’lumotlar sifati: sxemalar, majburiy maydonlar, valyuta/skayl validatsiyasi.

14) Dashbord va alertlar

Vidjetlar: Auto-match%, Mismatch Rate, Aging DLQ, Settlement Timeliness, Fee Accuracy, Diff-tiplar xaritasi.

• ’Auto-match% <90%’ provayder/kun → P1
• ’Aging p95> 3 kun’ → P2
• `AMOUNT_MISMATCH spike` → P1
• «Bank ≠ Settlement» summasi/valyutasi bo’yicha → P0

15) Test-keyslar (UAT/Prod)

1. Shu faylni qayta yuklash → 0 nojo’ya ta’sirlar (idempotentlik).
2. Qisman refandlar (3 satr) → summa mos keladi, sequence match.
3. FX konvertatsiyasi: kursning tolerans doirasidagi tafovuti → to’g "ri match.
4. Dublikatlar hisobotda provider_txid → dedup va alert.
5. Yoʻqolgan webhook capture → polling gapni yopdi, maqomi tenglashtirildi.
6. Settlement batch bilan fee-liniya → Revenue/Fee/Net’da to’g’ri bo’lingan.

16) Tez-tez xatolar va qanday qilib qochish mumkin

Qiyoslash bazalarini aralashtirish (compare’attempt’vs’capture’) → bir xil granulyarlikni saqlang.
’provider _ txid’ log’da yo’qligi o’yinning aniqligini yo’qotadi.
Signor taymzon → settlement sanalari boʻyicha siljishlar.
Hech qanday DLQ/retraj yo’q → «abadiy» tafovutlar.
Jurnalsiz qoʻlda tuzatishlar → audit bilan mos kelmaslik.
Noaniq toleranslar → yoki pere-match yoki «hammasi DLQda».

17) Joriy etishning nazorat chek-varaqasi

• Yagona normallashtirish sxemasi va PSP/usullar/akkauntlar ma’lumotnomalari
• Taqqoslash kalitlari daraxti (txid → merchant_ref → fuzzy)
• Summa/vaqt/FX bo’yicha tolerans, kurs manbai siyosati
• Idempotent ingestion, DLQ, retray, alertlar
• Settlement Bank, Suspense Account siyosatini solishtirish
• Dashbord KPI, moliya/audit uchun hisobot
• Pleybuklar va diff-keyslarni tahlil qilish SLA

Xulosa

Solishtirish - bu muhandislik fanidir: normallashtirish, ishonchli kalitlar, toleranslar, avtomatik o’yinlar va shaffof tuzatishlar. Bunday kontur bilan siz tushum va komissiyalarni barqarorlashtirasiz, «qora teshiklarni» minimallashtirasiz, davrni yopishni tezlashtirasiz va og’riqsiz auditdan o’tasiz: Auto-match ↑, Mismatch ↓, Aging ↓.