Conciliación de pagos e informes PSP

TL; DR

La conciliación es la costura automatizada diaria de su guardabosques y eventos (auth/capture/refund/payout) con informes PSP/ecuador/bancos. Clave del éxito: modelo único de datos, claves deterministas de mapeo, idempotencia estricta, tolerancias por sumas/FX/tiempo, cola DLQ para casos controvertidos y playbooks de autocorrección. KPI: Recon Mismatch Rate↓, Aging of Unreconciled↓, Auto-match%↑.

1) ¿Por qué y qué estamos probando?

Objetivos: confirmación de ingresos y comisiones, detección de tomas/perdidas, settlement correcto por día y moneda, control de refund-to-source, cumplimiento de auditoría/regulador.

• Deposits: `auth → capture → settle`
• Refundidos: full/partial, estados y sumas
• Payouts/Withdrawals: pagos salientes por método
• Fees & Adjustments: comisiones PSP, esquemas de interchainge, correcciones
• Chargebacks/Disputes: fuera de su iniciativa
• FX/Conversiones: cursos, spreads, momento de fijación

2) Fuentes de datos

Eventos internos (los suyos): bus de eventos/Kafka, 'payments _ flat', 'refunds', 'payouts', tu Ledger.

• Transactions (extractos operativos)
• Settlements/Batches (desglose de las matriculaciones T + N)
• Fees/Statements (comisiones, ajustes)
• Chargebacks/Disputes
• Registros de Payouts/OCT/RTP/SEPA
• Estados Bancarios: MT940/CSV/ISO20022 CAMT, estiramiento de las inscripciones.

3) Claves de asignación (llaves matching)

1. provider_txid ↔ provider_txid (ID PSP único)

2. idempotency_key/ merchant_reference (si son estables en PSP)

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

4. Fuzzy-capa: ± tolerancias en suma/tiempo, BIN/issuer country, status family

• Almacene ambos: 'payment _ id' y 'provider _ txid'.
• Para parte/refund: agregue 'sequence _ index' o 'refund _ line _ id'.
• Для payouts — `payout_batch_id + line_id`.
• Para FX, 'exec _ id' (conversión) y origen del curso.

4) Modelo de datos (capa normalizada)

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) Proceso de conciliación (ETL/orquestación)

1. Ingest: recogemos los informes PSP (SFTP/API), validamos el esquema/firmas, guardamos en 'raw'.
2. Normalizar: Mappim campos en formato unificado (currency ISO, decimals, timesone UTC).
3. Match: algoritmo de correlación del árbol de claves con tolerancias.
4. Post-match: formamos diff (discrepancias) y journal entries para ledger/correcciones.
5. Settle: cosemos 'PSP _ SETTLEMENT ↔ BANK' (créditos a cuenta), esparcimos por días/batches.
6. Informe: dashboard, alertas; controvertido en DLQ para el análisis manual/juego automático.

Idempotencia: por cada archivo/página - 'ingest _ id'. Volver a cargar no modifica el resultado.

6) Tolerances (tolerances) y reglas

Tiempo: '± 15 minutos' para transacciones, '± 1 día' para settlement.
Suma: '≤ 0. 01 'moneda básica o' ≤ 10 bps 'para las diferencias FX/fee.
FX: Permitimos discrepancias con el banco si la fuente de la tasa es diferente; fijamos 'fx _ src'.
Parcial/Múltiple: el importe de las líneas parcial/refund debe ser igual al saldo interno.

7) Tratamiento de discrepancias (diff taxonomy)

8) Ledger & Accounting (cableado)

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

Fee: `DR Fees / CR Cash or Payable`

Refund: cableado inverso proporcional a la parte

Chargeback: cuentas individuales y reserva para disputas

FX reval: revalorización diaria del saldo de AR/caché en el curso 'fx _ src _ policy'

9) KPI y objetivos

Auto-match% = cadenas automáticamente asignadas/todas las filas (objetivo ≥ 95%)

Recon Mismatch Rate = líneas diff/todas las filas (≤ 1-2%)

Aging of Unreconciled: p50/p95 días de estar en DLQ (p95 ≤ 3 días)

Settlement Timeliness: proporción de batches cosidos con el banco D-day (≥ 99%)

Fee Accuracy: discrepancias en las tarifas de los proveedores (≤ 0. 1% del volumen de negocios)

Duplicate/Orphan Incidents: aspira a 0

10) Cortes SQL

10. 1 Comparación básica por provider_txid

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) Características en rieles/casos

Mapas: diferencias entre ajustes 'auth' y 'capture', ajustes 'settlement' tardíos, interconexión/esquema fee - líneas separadas.
A2A/Open Banking/RTP: confirmaciones instantáneas, pero es posible 'reversal'; consulte 'payout' y devoluciones.
Carteras: a menudo perfectas 'provider _ txid', rápidas 'refund'; Siga las líneas fee.
Vales: no hay refundición simétrica - refleje correctamente en las políticas e informes.
Crypto: el-chein hash ↔ provider_txid; configuraciones N; Tener en cuenta las comisiones de la red y los posibles rebates; curso - en el momento de la conversión.

12) Playbucks operativos

Ráfaga de MISSING_INTERNAL: compruebe la pérdida de webhooks/retrays, reproduzca ingestion, habilite la polling API.
AMOUNT_MISMATCH de un PSP: comparar redondeos/IVA/fee-modelo, solicitar estado correctivo.
Settlement no se junta con el banco: comprobar fecha de valor, comisiones bancarias, retrasos T + N; poner temporalmente en «Cuenta Suspense».
REFUND_OVER en masa: parada inmediata de auto-refundición, auditoría de idempotencia, corrección manual.
FX_DRIFT: fijar la política de la fuente del curso (ECB/PSP/BANK), volver a calcular las diferencias P&L.

13) Control y seguridad

Idempotencia de ingestión: 'file _ id + checksum' y registro de descargas.
Accesos (RBAC) y control de 4 ojos: en correcciones manuales/cableado de registro.
Audit Trail: todos los partidos/diphs/correcciones - en un diario inmutable.
Calidad de datos: esquemas, campos obligatorios, validación de monedas/skale.

14) Dashboard y alertas

Widgets: Auto-match%, Mismatch Rate, Aging DLQ, Settlement Timeliness, Fee Accuracy, Top PSP en diodos, diff-type card.

• 'Auto-match% <90%' por proveedor/día → P1
• ` Aging p95> 3 dn ` → P2
• `AMOUNT_MISMATCH spike` → P1
• 'Bank≠Settlement' por suma/moneda → P0

15) Casos de prueba (UAT/Prod)

1. Volver a cargar el mismo archivo → 0 efectos secundarios (idempotencia).
2. Refandos parciales (3 líneas) → suma coincide, partido por sequence.
3. Conversión FX: divergencia de curso dentro de tolerance → match correcto.
4. Los duplicados provider_txid en el informe → dedoup y alert.
5. Falta webhook capture → polling cerrado gap, estado alineado.
6. Settlement batch con línea fee → el desglose correcto en Revenue/Fee/Net.

16) Errores frecuentes y cómo evitar

Mezclar bases de comparación (comparar 'attempt' vs 'capture') → mantener la misma granularidad.
La falta de 'provider _ txid' en la guarida → pierde la precisión del partido.
Ignorar la temporización → los desplazamientos por fechas de settlement.
No hay DLQ/retraídas → divergencias «eternas».
Las ediciones manuales sin registro → incompatibilidad con la auditoría.
Las tolerancias borrosas → ya sea un partido de plumas o «todo en DLQ».

17) Lista de comprobación de implementación

• Esquema único de normalización y directorios PSP/métodos/cuentas
• Árbol de claves de asignación (txid → merchant_ref → fuzzy)
• Tolerancias por suma/tiempo/FX, política de origen del curso
• Ingestión idempotente, DLQ, retraídas, alertas
• Política de conciliación de Settlement↔Bank, Cuenta Suspense
• Dashboard KPI, informes para finanzas/auditoría
• Playbucks y SLA de análisis de casos diff

Resumen

La conciliación es una disciplina de ingeniería: normalización, claves fiables, toleranzas, partidos automáticos y correcciones transparentes. Con este circuito estabilizarás los ingresos y comisiones, minimizarás los «agujeros negros», acelerarás el cierre del periodo y pasarás la auditoría sin dolor: Auto- match%↑, Mismatch↓, Aging↓.