Escritorio de ingeniería con estados de cuenta y recibos en papel ordenados y unidos con hilos de colores

Diseño de un sistema de conciliación de pagos: del modelo al matching

•12 min de lectura

La mayoría de los equipos empieza a conciliar con una hoja de cálculo y un script, y ambos se rompen el primer mes en que se duplica el volumen. Esta guía cubre el diseño de un sistema de conciliación de pagos desde el punto de vista de ingeniería: el modelo de datos, cómo ingerir datos de procesadores y bancos sin duplicados, cómo construir un motor de matching en el que se pueda confiar y cómo manejar las fallas que hacen difícil la conciliación. Si primero necesitas el proceso operativo (qué conciliar, en qué orden y el checklist de finanzas), lee nuestra guía de 7 pasos de conciliación automática de pagos. Este artículo trata del software que hay debajo.

Lo que el sistema realmente tiene que demostrar

Un sistema de conciliación responde una pregunta con evidencia: por cada unidad de dinero que debía moverse, ¿se movió, por el monto correcto, al lugar correcto, y podemos mostrar por qué lo creemos?

En la práctica, eso significa vincular tres fuentes de verdad independientes:

  • Tu ledger: órdenes, facturas, reembolsos y créditos que creó tu aplicación.
  • El procesador: cobros, comisiones, reembolsos, disputas y los payouts (liquidaciones) que los agrupan.
  • El banco: depósitos y retiros tal como aparecieron realmente en el estado de cuenta.

Ninguna de estas fuentes comparte una llave primaria de punta a punta. El ID de orden vive en tu base de datos y quizá en la metadata del procesador; el ID del payout vive en el procesador y quizá en la descripción del depósito bancario; la línea del banco tiene un monto, una fecha y una referencia en texto libre. El trabajo del sistema es construir y mantener los vínculos entre ellas, y que cada vínculo sea explicable.

Diseño de un sistema de conciliación de pagos: el modelo de datos

La decisión más importante es separar lo que dijo una fuente de lo que tú concluiste sobre ello. Los registros crudos son hechos inmutables; los matches son conclusiones que se pueden revisar.

-- 1. Registros crudos e inmutables, tal como se recibieron
CREATE TABLE source_record (
  id            BIGSERIAL PRIMARY KEY,
  source        TEXT NOT NULL,        -- 'ledger' | 'stripe' | 'bank_x'
  external_id   TEXT NOT NULL,        -- ID propio del proveedor o un hash estable
  payload       JSONB NOT NULL,       -- datos originales, nunca se editan
  ingested_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

-- 2. Vista normalizada que usa el motor de matching
CREATE TABLE txn (
  id              BIGSERIAL PRIMARY KEY,
  source_record_id BIGINT NOT NULL REFERENCES source_record(id),
  kind            TEXT NOT NULL,      -- charge | fee | refund | dispute | payout | deposit
  amount_minor    BIGINT NOT NULL,    -- con signo, en unidades menores (centavos)
  currency        CHAR(3) NOT NULL,
  occurred_on     DATE NOT NULL,
  reference       TEXT,               -- id de orden, id de payout, referencia bancaria
  group_ref       TEXT                -- p. ej. id del payout que agrupa esta línea
);

-- 3. Conclusiones: un grupo de match vincula N txns que deben sumar cero
CREATE TABLE match_group (
  id          BIGSERIAL PRIMARY KEY,
  rule        TEXT NOT NULL,          -- qué regla o persona lo creó
  status      TEXT NOT NULL,          -- active | reversed
  created_by  TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE match_line (
  match_group_id BIGINT REFERENCES match_group(id),
  txn_id         BIGINT REFERENCES txn(id),
  PRIMARY KEY (match_group_id, txn_id)
);

Algunas decisiones aquí son deliberadas:

  • Enteros en unidades menores más un código de moneda. El punto flotante tarde o temprano produce una diferencia de un centavo que nadie puede explicar. Guarda los montos como enteros con signo y lleva la moneda en cada fila. Ten en cuenta que no todas las monedas tienen dos decimales, así que guarda el exponente en una tabla de monedas en lugar de fijar en el código una división entre 100.
  • Montos con signo. El dinero que entra es positivo y el que sale es negativo, desde el punto de vista de una cuenta. Un grupo de match es válido cuando sus líneas suman cero (o una diferencia registrada y aprobada explícitamente). Esa única invariante reemplaza mucho código de casos especiales.
  • Matches de muchos a muchos. Un depósito bancario suele corresponder a un payout, que corresponde a cientos de cobros menos comisiones y reembolsos. Una orden también puede pagarse en dos parcialidades. Un match_group con N líneas maneja todas esas formas; una columna matched_to_id en la tabla de transacciones no maneja bien ninguna.
  • Conclusiones reversibles. Deshacer un match nunca borra filas. Marcas el grupo como reversed y creas uno nuevo. Los auditores, y tú mismo en el futuro, preguntarán por qué algo se concilió en determinada fecha.

Ingesta: webhooks para velocidad, consultas programadas para la verdad

Los webhooks son excelentes para el estado casi en tiempo real, pero son un canal de notificación, no un registro completo. Los eventos pueden llegar tarde, dos veces o fuera de orden, y tú también tendrás caídas de tu lado. Un diseño robusto usa ambos:

  1. Webhooks que actualizan el estado operativo (orden pagada, reembolso emitido) y encolan una consulta.
  2. Consultas programadas a los datos de liquidación del procesador, que son la fuente de verdad de la conciliación. En Stripe, por ejemplo, los datos de liquidación se exponen mediante la API de Balance Transactions, y la documentación muestra cómo obtener balance transactions y filtrarlas por tipo.
  3. Estados de cuenta bancarios, que llegan como archivos o mediante una API del banco, al ritmo del banco, no al tuyo.

Cada vía de ingesta escribe con el mismo upsert idempotente, con llave (source, external_id):

INSERT INTO source_record (source, external_id, payload)
VALUES ($1, $2, $3)
ON CONFLICT (source, external_id) DO NOTHING
RETURNING id;

Las líneas bancarias son el caso difícil, porque muchos bancos no entregan un ID de línea estable. Derívalo de campos que no cambian entre exportaciones (cuenta, fecha contable, monto, referencia y un número de secuencia por día) y conserva el archivo original para poder volver a derivarlo si el banco cambia su formato.

La misma disciplina aplica a las llamadas que haces hacia afuera, como emitir reembolsos. La documentación de idempotencia de Stripe explica que guarda el código de estado y el cuerpo de la primera solicitud para una llave dada, y que las llaves pueden eliminarse cuando tienen al menos 24 horas. Eso convierte a la llave en una protección de reintentos para una sola solicitud, no en un control de duplicados a nivel de negocio, así que tu propia base de datos sigue necesitando una restricción única sobre "reembolso de la orden X, intento Y".

El motor de matching

Ejecuta las reglas de la más certera a la menos certera, y deja de considerar una transacción en cuanto queda conciliada. Una estructura típica por niveles:

  1. Match exacto por referencia. Tu ID de orden guardado en la metadata del procesador es igual al ID de orden del ledger, con el mismo monto y moneda. Totalmente automático.
  2. Agrupación por liquidación. Todas las líneas del procesador con el mismo group_ref (el payout) suman el monto del payout, y ese monto es igual a un depósito bancario dentro de una ventana de fechas. Totalmente automático cuando las sumas son exactas.
  3. Match candidato. Mismo monto, misma moneda, fecha dentro de N días y similitud de referencia por encima de un umbral. Se propone, no se confirma, salvo que la regla tenga un historial comprobado.
  4. Excepción. Todo lo que queda va a una cola para una persona.
def reconcile_payout(payout, bank_lines, processor_lines):
    lines = [l for l in processor_lines if l.group_ref == payout.external_id]
    if sum(l.amount_minor for l in lines) != payout.amount_minor:
        return exception("payout_lines_do_not_sum", payout, lines)

    candidates = [
        b for b in bank_lines
        if b.currency == payout.currency
        and b.amount_minor == payout.amount_minor
        and abs((b.occurred_on - payout.arrival_date).days) <= DATE_WINDOW
        and not b.is_matched
    ]
    if len(candidates) == 1:
        return commit_group(rule="payout_rollup", txns=[payout, *lines, candidates[0]])
    if len(candidates) > 1:
        return exception("ambiguous_bank_line", payout, candidates)
    return pending(payout)  # la línea del banco puede no haber llegado aún

Observa los tres resultados además del éxito: una excepción real, una ambigüedad y "todavía no". Tratar "todavía no" como error inunda la cola cada mañana; tratarlo como normal para siempre esconde dinero faltante. Asigna antigüedad a los pendientes y escálalos después de un umbral que acuerdes con finanzas.

Reglas deterministas vs. scoring

Un modelo de scoring (similitud ponderada de monto, fecha y referencia) encuentra más matches, pero es más difícil de explicar y de auditar. Las reglas deterministas son aburridas y explicables. Un punto medio práctico: las reglas deterministas confirman automáticamente, el scoring solo propone y una persona acepta o rechaza. Registra cada aceptación; después de suficientes decisiones consistentes tendrás evidencia para convertir un patrón en regla determinista.

Arquitectura: batch, por eventos o híbrida

  • Batch nocturno. Simple, fácil de volver a ejecutar y alineado con la forma en que los bancos entregan los estados de cuenta. La desventaja es que los problemas aparecen con un día de retraso.
  • Por eventos. Cada registro nuevo dispara el matching de sus candidatos. Retroalimentación más rápida, pero debes manejar la concurrencia: dos workers pueden intentar tomar la misma línea bancaria. Usa bloqueos de fila (SELECT ... FOR UPDATE SKIP LOCKED) o una restricción única para que una transacción pertenezca a un solo grupo activo.
  • Híbrida (casi siempre la respuesta correcta). Por eventos para los matches de nivel 1, que solo necesitan datos del ledger y del procesador, más una ejecución programada cuando llegan los datos del banco, más un comando para reprocesar cualquier rango de fechas.

Elijas lo que elijas, haz que el motor de matching sea una función pura de los datos almacenados: mismas entradas, mismas salidas. Eso es lo que te permite reprocesar un mes después de corregir un bug en una regla y comparar los resultados antes de confirmarlos.

Manejo de fallas: los casos que generan la mayor parte del trabajo

  • Datos tardíos y desordenados. Un reembolso puede llegar después del payout que lo incluyó. Nunca asumas que un payout es definitivo hasta que sus líneas sumen correctamente.
  • Reembolsos parciales y disputas. Modélalos como transacciones negativas separadas, vinculadas al cobro original, no como ediciones del cobro.
  • Conversión de moneda. Si el procesador liquida en una moneda distinta a la del cobro, guarda ambos montos y el tipo de cambio que reportó el procesador. No lo recalcules con tu propio tipo de cambio.
  • Cambios de formato del banco. Versiona tus parsers y conserva los archivos crudos. Cuando cambie un parser, reprocesa desde los crudos y compara.
  • Correcciones manuales. Una persona que concilia dos partidas es un grupo de match con created_by igual a esa persona y una nota obligatoria. Considera exigir un segundo aprobador para los grupos que llevan una diferencia distinta de cero.

Rendimiento

El matching es sobre todo un problema de búsqueda. Evita comparar cada transacción sin conciliar contra todas las demás:

  • Acota los candidatos por moneda, monto y ventana de fechas antes de aplicar cualquier lógica difusa. Un índice sobre (currency, amount_minor, occurred_on) filtrado a filas sin conciliar hace la mayor parte del trabajo.
  • Particiona por fecha cuando las tablas crezcan, ya que casi toda consulta está acotada por un rango de fechas.
  • Agrupa las escrituras en una transacción por grupo de match, para que una caída nunca deje medio grupo confirmado.

Seguridad y auditoría

  • Usa las credenciales de API más restringidas que permita cada procesador, de solo lectura donde la conciliación no necesite escribir.
  • Nunca guardes números de tarjeta; los IDs del procesador bastan para conciliar.
  • Cifra en reposo los archivos bancarios crudos y restringe quién puede descargarlos.
  • Mantén un log de auditoría de solo inserción para cambios de reglas, matches manuales y reversiones.

Errores comunes

  • Usar floats o decimales con escala inconsistente para el dinero.
  • Guardar una sola columna matched_to, que no puede representar payouts ni parcialidades.
  • Tratar los webhooks como un registro completo de lo que pasó.
  • Borrar o editar registros crudos para "arreglar" una diferencia.
  • Confirmar automáticamente matches difusos sin revisión humana ni registro.
  • No tener un estado "pendiente", de modo que los retrasos normales de liquidación parecen errores.

Checklist de implementación

  1. Registros crudos guardados de forma inmutable con un (source, external_id) único.
  2. Montos como enteros con signo en unidades menores, con la moneda en cada fila.
  3. Grupos de match de muchos a muchos que deben sumar cero.
  4. Webhooks para frescura, consultas programadas para la verdad.
  5. Reglas ordenadas de exactas a difusas; las difusas solo proponen.
  6. Estados pendiente, ambiguo y excepción, con antigüedad.
  7. Motor de matching reproducible y un comando para reprocesar cualquier rango de fechas.
  8. Log de auditoría para cada acción manual y cambio de regla.

Construir esto bien requiere ingeniería cuidadosa, y la recompensa es un equipo de finanzas que confía en los números. Si prefieres un equipo que ya lo ha hecho antes, conoce cómo abordamos el desarrollo de software a la medida para operaciones con muchos pagos. Las empresas de venta directa tienen una pierna más que conciliar, el pago de comisiones a distribuidores, y alrededor de eso está diseñada nuestra plataforma de venta directa con pagos y comisiones integrados.

Preguntas frecuentes

¿Conviene construir la conciliación o comprar una herramienta?

Compra si tus fuentes son estándar y una herramienta soporta tus procesadores y bancos desde el inicio. Construye, o extiende, cuando tengas flujos poco comunes, como splits de marketplace, parcialidades o pago de comisiones, o cuando la conciliación deba alimentar los datos de tu propio producto.

¿Se puede conciliar en tiempo real?

En parte. Los matches entre ledger y procesador pueden ser casi en tiempo real con webhooks más consultas de confirmación. El matching del lado del banco avanza al ritmo en que tu banco entrega los datos del estado de cuenta.

¿Cómo manejo una diferencia de un centavo?

No la escondas en una regla de redondeo. Regístrala como una línea de diferencia explícita dentro del grupo de match, con un motivo y un aprobador, para que el grupo siga sumando cero y la diferencia se pueda reportar.

Comentarios

Todavía no hay comentarios. Sé el primero en compartir lo que piensas.

Deja un comentario

Nunca se publica.