Esquema de ledger de facturación que reconcilia: facturas y pagos

Por qué los datos de facturación dejan de reconciliarse

Para finanzas, “reconciliar” es una promesa simple: los totales en los informes coinciden con los registros fuente y cada cifra puede rastrearse. Si el mes muestra $12,430 recaudados, debes poder apuntar a los pagos exactos (y cualquier reembolso), ver a qué facturas se aplicaron y explicar cada diferencia con un registro fechado.

Los datos de facturación suelen dejar de reconciliarse cuando la base de datos almacena resultados en lugar de hechos. Columnas como paid_amount, balance o amount_due se actualizan con el tiempo por la lógica de la aplicación. Un error, un reintento o una “corrección” manual pueden cambiar la historia en silencio. Semanas después, la tabla de facturas dice que una factura está “pagada”, pero las filas de pagos no suman, o existe un reembolso sin un crédito coincidente.

Otra causa común es mezclar distintos tipos de documentos. Una factura no es un pago. Un memo de crédito no es un reembolso. Un ajuste no es lo mismo que un descuento. Cuando se encajan en una sola fila “transactions” con muchos campos opcionales, el reporting se convierte en conjetura y las auditorías en discusiones.

La discordancia subyacente es simple: las aplicaciones a menudo cuidan el estado actual (“¿está el acceso activo?”), mientras que finanzas necesita la pista (“¿qué pasó, cuándo y por qué?”). Un esquema de ledger de facturación debe soportar ambos, pero la trazabilidad debe primar.

Diseña pensando en este resultado:

• Totales claros por cliente, por factura y por periodo contable
• Cada cambio registrado como una fila nueva (no sobrescribiendo)
• Una cadena completa desde la factura hasta pagos, créditos, reembolsos y ajustes
• La capacidad de recalcular totales desde entradas en bruto y obtener la misma respuesta

Ejemplo: si un cliente paga $100 y luego recibe un crédito de $20, tus informes deberían mostrar $100 recaudados, $20 acreditados y $80 netos, sin editar el importe original de la factura.

Separa facturas, pagos, créditos y ajustes

Si quieres un esquema de ledger que reconcilie, trata cada tipo de documento como un evento distinto. Mezclarlos en una sola tabla “transactions” parece ordenado, pero difumina el significado.

Una factura es una reclamación: “el cliente nos debe dinero.” Guárdala como un documento con un encabezado (cliente, número de factura, fecha de emisión, fecha de vencimiento, moneda, totales) y líneas separadas (qué se vendió, cantidad, precio unitario, categoría de impuesto). Está bien guardar totales del encabezado para velocidad, pero siempre debes poder explicarlos a partir de las líneas.

Un pago es movimiento de dinero: “dinero pasó del cliente a nosotros.” En flujos con tarjeta, a menudo ves autorización (el banco aprueba) y captura (el dinero efectivamente se cobra). Muchos sistemas mantienen autorizaciones como registros operativos y solo ponen los pagos capturados en el ledger, para no inflar el reporte de caja.

Un memo de crédito reduce lo que el cliente debe sin necesariamente devolver dinero. Un reembolso es efectivo saliendo. A menudo ocurren juntos, pero no son lo mismo.

• Factura: aumenta cuentas por cobrar e ingresos (o ingresos diferidos)
• Pago: aumenta caja y reduce cuentas por cobrar
• Memo de crédito: reduce cuentas por cobrar
• Reembolso: reduce caja

Un ajuste es una corrección hecha por tu equipo cuando la realidad no coincide con los registros. Los ajustes necesitan contexto para que finanzas confíe en ellos. Guarda quién lo creó, cuándo se registró, un código de motivo y una nota corta. Ejemplos: “castigar 0.03 por redondeo” o “migrar saldo legacy”.

Una regla práctica: pregúntate, “¿existiría esto si nadie cometiera un error?” Facturas, pagos, memos de crédito y reembolsos sí existirían. Los ajustes deberían ser raros, claramente etiquetados y fáciles de revisar.

Elige un modelo de ledger que finanzas pueda auditar

Un esquema de ledger de facturación que reconcilia parte de una idea: los documentos describen lo que pasó y los asientos del ledger prueban los totales. Una factura, pago o memo de crédito es un documento. El ledger es el conjunto de entradas que suman, punto.

Documentos vs. asientos (almacena ambos)

Conserva los documentos (encabezado y líneas de factura, recibo de pago, memo de crédito) porque las personas necesitan leerlos. Pero no dependas solo de los totales del documento como fuente de verdad para la reconciliación.

En su lugar, contabiliza cada documento en una tabla de ledger como una o más entradas inmutables. Entonces finanzas puede sumar entradas por cuenta, cliente, moneda y fecha de contabilización y obtener la misma respuesta siempre.

Un modelo simple y audit-friendly sigue unas pocas reglas:

• Entradas inmutables: nunca edites montos publicados; los cambios son nuevas entradas.
• Evento de contabilización claro: cada documento crea un lote de asientos con una referencia única.
• Lógica balanceada: las entradas cuadran correctamente (a menudo debe cumplirse débito = crédito a nivel empresa).
• Fechas separadas: conserva la fecha del documento (lo que ve el cliente) y la fecha de contabilización (lo que impacta los informes).
• Referencias estables: guarda la referencia externa (número de factura, ID del procesador de pagos) junto a los IDs internos.

Claves naturales vs IDs sustitutas

Usa IDs sustitutos para joins y rendimiento, pero también guarda una clave natural estable que sobreviva migraciones y reimportaciones. Finanzas preguntará por “Factura INV-10483” mucho después de que cambien los IDs de la base de datos. Trata números de factura e IDs de proveedor (como un charge ID del procesador) como campos de primera clase.

Reversiones sin borrar historia

Cuando algo deba deshacerse, no borres ni sobrescribas. Publica una reversión: nuevas entradas que reflejen los montos originales con signos opuestos, enlazadas al asiento original.

Ejemplo: un pago de $100 aplicado a la factura equivocada se corrige en dos pasos: revierte el asiento mal aplicado y luego publica una nueva aplicación a la factura correcta.

Plano de esquema paso a paso (tablas y claves)

Un esquema de ledger de facturación reconcilia más fiable cuando cada tipo de documento tiene su propia tabla y las conectas con registros de asignación explícitos (en lugar de adivinar relaciones luego).

Empieza con un pequeño conjunto de tablas centrales, cada una con una clave primaria clara (UUID o bigserial) y claves foráneas requeridas:

• customers: customer_id (PK), además identificadores estables como external_ref (único)
• invoices: invoice_id (PK), customer_id (FK), invoice_number (único), issue_date, due_date, currency
• invoice_lines: invoice_line_id (PK), invoice_id (FK), line_type, description, qty, unit_price, tax_code, amount
• payments: payment_id (PK), customer_id (FK), payment_date, method, currency, gross_amount
• credits: credit_id (PK), customer_id (FK), credit_number (único), credit_date, currency, amount

Luego añade tablas que hagan auditable los totales: las asignaciones. Un pago o crédito puede cubrir varias facturas, y una factura puede pagarse con varios pagos.

Usa tablas de unión con sus propias claves (no solo claves compuestas):

• payment_allocations: payment_allocation_id (PK), payment_id (FK), invoice_id (FK), allocated_amount, posted_at
• credit_allocations: credit_allocation_id (PK), credit_id (FK), invoice_id (FK), allocated_amount, posted_at

Finalmente, mantiene los ajustes separados para que finanzas vea qué cambió y por qué. Una tabla adjustments puede referenciar el registro objetivo con invoice_id (nullable) y guardar el monto delta, sin reescribir la historia.

Agrega campos de auditoría en todos los sitios donde publiques dinero:

• created_at, created_by
• reason_code (castigo, redondeo, goodwill, contracargo)
• source_system (manual, import, Stripe, support tool)

Créditos, reembolsos y castigos sin totales rotos

La mayoría de los problemas de reconciliación empiezan cuando créditos y reembolsos se registran como “pagos negativos”, o cuando los castigos se mezclan en líneas de factura. Un esquema limpio mantiene cada tipo de documento como su propio registro, y el único lugar donde interactúan es mediante asignaciones explícitas.

Un crédito debe mostrar por qué redujiste lo que el cliente debe. Si se aplica a una factura, registra un único memo de crédito y asígnalo a esa factura. Si se aplica a varias facturas, asigna el mismo memo de crédito a múltiples facturas. El crédito sigue siendo un documento con muchas asignaciones.

Los reembolsos son eventos similares a pagos, no pagos negativos. Un reembolso es efectivo que sale, así que trátalo como su propio registro (a menudo ligado al pago original para referencia), y luego asígnalo igual que un pago. Esto mantiene la pista de auditoría clara cuando el extracto bancario muestra tanto el ingreso como la salida.

Pagos parciales y créditos parciales funcionan igual: mantiene el total del pago o crédito en su propia fila y usa filas de asignación para cuánto se aplicó a cada factura.

Reglas de contabilización que previenen el doble conteo

Estas reglas eliminan la mayoría de las “diferencias misteriosas”:

• Nunca almacenes un pago negativo. Usa un registro de reembolso.
• Nunca reduzcas el total de una factura después de publicada. Usa un memo de crédito o un ajuste.
• Publica documentos una vez (con timestamp posted_at) y no edites montos después de publicada la contabilización.
• Lo único que cambia el balance de la factura es la suma de las asignaciones publicadas.
• Un castigo (write-off) es un ajuste con un código de motivo, asignado a la factura como un crédito.

Impuestos, tarifas, moneda y elecciones de redondeo

La mayoría de los problemas de reconciliación empiezan con totales que no puedes recrear. La regla más segura es simple: guarda las líneas en bruto que crearon la factura y también guarda los totales que mostraste al cliente.

Impuestos y tarifas: guárdalos a nivel de línea

Guarda los impuestos y tarifas por línea, no solo como campos resumen a nivel de factura. Distintos productos pueden tener distintos tipos de impuesto, las tarifas pueden ser gravables o no, y las exenciones aplican a partes de la factura. Si solo guardas un tax_total, tarde o temprano llegarás a un caso que no puedes explicar.

Conserva:

• Líneas en bruto (qué se vendió, qty, precio unitario, descuento)
• Totales calculados por línea (line_subtotal, line_tax, line_total)
• Totales resumen de factura (subtotal, tax_total, total)
• La tasa de impuesto y el tipo de impuesto usado
• Tarifas como líneas propias (por ejemplo, “Payment processing fee”)

Esto permite a finanzas reconstruir totales y confirmar que el impuesto se calculó de la misma forma cada vez.

Multi-moneda: almacena lo que pasó y cómo lo reportas

Si soportas múltiples monedas, registra tanto la moneda de la transacción como los valores en la moneda de reporte. Un mínimo práctico es: currency_code en cada documento monetario, un fx_rate usado al contabilizar, y montos de reporte separados (por ejemplo, amount_reporting) si tus libros cierran en una moneda.

Ejemplo: un cliente es facturado 100.00 EUR más 20.00 EUR de IVA. Guarda esas líneas y totales en EUR, además del fx_rate usado al contabilizar la factura y los totales convertidos para reporte.

El redondeo merece su propio tratamiento. Elige una regla de redondeo (por línea o por factura) y mantenla. Cuando el redondeo genera una diferencia, regístrala explícitamente como una línea de ajuste por redondeo (o una pequeña entrada de ajuste) en lugar de cambiar silenciosamente los totales.

Estados, fechas de contabilización y qué no almacenar

La reconciliación se complica cuando un “estado” se usa como atajo de verdad contable. Trata el estado como una etiqueta de flujo y los asientos publicados como fuente de verdad.

Haz que los estados sean estrictos y aburridos. Cada uno debe responder: ¿puede este documento afectar totales ya?

• Draft: solo interno, no publicado, no debe aparecer en informes
• Issued: finalizado y enviado, listo para contabilizar (o ya contabilizado)
• Void: cancelado; si fue contabilizado, debe revertirse
• Paid: totalmente saldado por pagos y créditos publicados
• Refunded: dinero devuelto mediante un reembolso publicado

Las fechas importan más de lo que la mayoría de equipos espera. Finanzas preguntará: “¿a qué mes perteneció esto?” y tu respuesta no debería depender de registros de actividad de la UI.

• issued_at: cuando la factura se volvió final
• posted_at: cuando cuenta en los informes contables
• settled_at: cuando los fondos se liquidaron o el pago se confirmó
• voided_at / refunded_at: cuando la reversión fue efectiva

Qué no almacenar como verdad: números derivados que no puedes reconstruir desde el ledger. Campos como balance_due, is_overdue y customer_lifetime_value están bien como vistas cacheadas solo si siempre puedes recomputarlos desde facturas, pagos, créditos, asignaciones y ajustes.

Un pequeño ejemplo: un reintento de pago golpea tu pasarela dos veces. Sin una idempotency_key, guardas dos pagos, marcas la factura “pagada” y finanzas ve $100 de más en caja. Guarda una idempotency_key única por intento de cargo externo y rechaza duplicados a nivel de base de datos.

Informes que finanzas espera desde el día uno

Un esquema de ledger de facturación se prueba cuando finanzas puede responder preguntas básicas rápidamente y obtener los mismos totales siempre.

La mayoría de equipos empieza con:

• Aging de cuentas por cobrar: montos aún abiertos por cliente y por bucket de antigüedad (0-30, 31-60, etc.)
• Caja recibida: dinero recaudado por día, semana y mes, basado en fechas de contabilización de pagos
• Ingresos vs caja: facturas contabilizadas vs pagos contabilizados
• Pista de auditoría para exportaciones: un camino de drill-back desde una línea de exportación del GL hasta el documento exacto y las filas de asignación que la crearon

El aging es donde las asignaciones importan más. El aging no es “total factura menos total pagos.” Es “qué queda abierto en cada factura a una fecha dada.” Eso requiere almacenar cómo cada pago, crédito o ajuste se aplicó a facturas específicas y cuándo se publicaron esas asignaciones.

Caja recibida debe basarse en la tabla de pagos, no en el estado de la factura. Los clientes pueden pagar antes, después o parcialmente.

Ingresos vs caja es la razón por la que facturas y pagos deben permanecer separados. Ejemplo: emites una factura de $1,000 el 30 de marzo, recibes $600 el 5 de abril y emites un crédito de $100 el 20 de abril. El ingreso pertenece a marzo (contabilización de la factura), la caja a abril (contabilización del pago), y el crédito reduce cuentas por cobrar cuando se publica. Las asignaciones enlazan todo.

Escenario de ejemplo: un cliente, cuatro tipos de documento

Un cliente, un mes, cuatro tipos de documento. Cada documento se almacena una vez y el dinero se mueve a través de una tabla de asignaciones (a veces llamada “applications”). Eso hace que el balance final sea fácil de recomputar y auditar.

Supongamos el cliente C-1001 (Acme Co.).

Los registros que creas

invoices

payments

credits (memo de crédito, crédito de cortesía, etc.)

adjustments (corrección posterior, no una nueva venta)

allocations (esto es lo que realmente reconcilia el balance)

Cómo se calcula el saldo de la factura

Para INV-10, un auditor puede recomputar el saldo abierto desde las filas fuente:

open_balance = invoice.total + sum(adjustments) - sum(allocations)

Así: 120.00 + 5.00 - (70.00 + 20.00) = 35.00 adeudados.

Para rastrear los $35.00:

• Parte del total de la factura (INV-10)
• Suma ajustes publicados vinculados a la misma factura (ADJ-3)
• Resta cada asignación publicada aplicada a la factura (AL-900, AL-901)
• Confirma que cada asignación apunta a un documento real (PAY-77, CR-5)
• Verifica fechas y posted_at para explicar la línea de tiempo

Errores comunes que rompen la reconciliación

La mayoría de problemas no son “bugs matemáticos.” Son reglas faltantes, de modo que el mismo evento del mundo real se registra de forma distinta según quién lo tocó.

Una trampa común es usar filas negativas como atajo. Una línea de factura negativa, un pago negativo y una línea de impuesto negativa pueden significar cosas diferentes. Si permites negativos, define una política de reversión clara (por ejemplo: solo usa una fila de reversión que referencie la fila original y no mezcles semánticas de reversión con descuentos).

Otra causa frecuente es cambiar la historia. Si una factura fue emitida, no la edites después para ajustar un precio o dirección. Conserva el documento original y publica un ajuste o un memo de crédito que explique el cambio.

Patrones que habitualmente rompen totales:

• Usar filas negativas sin una regla de reversión estricta y una referencia al registro original
• Editar facturas antiguas después de emitidas en lugar de publicar ajustes o notas de crédito
• Mezclar IDs de transacción de pasarelas con IDs internos sin una tabla de mapeo y una sola fuente de verdad
• Dejar que el código de la aplicación calcule totales mientras faltan filas auxiliares (impuesto, tarifa, redondeo, asignaciones)
• No separar “movimiento de dinero” (flujo de caja) de “dinero asignado” (qué factura paga ese dinero)

Ese último punto causa la mayor confusión. Ejemplo: un cliente paga $100, luego aplicas $60 a la Factura A y $40 a la Factura B. El pago es un movimiento de caja, pero crea dos asignaciones. Si solo guardas “pago = factura”, no puedes soportar pagos parciales, sobrepagos o reasignaciones.

Checklist y siguientes pasos

Antes de añadir más funcionalidades, asegúrate de que lo básico resista. Un esquema de ledger de facturación reconcilia cuando cada total puede trazarse hasta filas específicas y cada cambio tiene una pista de auditoría.

Controles rápidos de reconciliación

Ejecuta estas comprobaciones en una muestra pequeña (un cliente, un mes) y luego en el conjunto de datos completo:

• Cada número publicado en un informe traza a filas fuente (línea de factura, pago, memo de crédito, ajuste) con fecha de contabilización y moneda.
• Las asignaciones nunca exceden el documento al que se aplican (total de asignaciones de un pago <= total del pago; lo mismo para créditos).
• Nada se elimina. Las entradas incorrectas se revierten con un motivo y luego se corrigen con una nueva fila publicada.
• El saldo abierto es derivable, no almacenado (saldo abierto = total factura menos asignaciones y créditos publicados).
• Los totales del documento coinciden con sus líneas (total encabezado = suma de líneas, impuestos y tarifas según la regla de redondeo).

Siguientes pasos para entregar algo utilizable

Una vez que tu esquema esté sólido, construye el flujo operativo alrededor:

• Pantallas administrativas para crear, publicar y revertir facturas, pagos, créditos y ajustes con notas requeridas
• Una vista de conciliación que muestre documentos y asignaciones lado a lado, incluyendo quién publicó qué y cuándo
• Exportaciones que finanzas espere (por fecha de contabilización, por cliente, por mapeo GL si lo tienes)
• Un flujo de cierre de período: bloquear fechas de contabilización para meses cerrados y requerir entradas de reversión para correcciones tardías
• Escenarios de prueba (reembolsos, pagos parciales, castigos) que deben coincidir con los totales esperados

Si quieres un camino más rápido hacia un portal interno de finanzas funcional, AppMaster (appmaster.io) puede ayudarte a modelar el esquema en PostgreSQL, generar APIs y construir las pantallas administrativas desde la misma fuente, de modo que las reglas de contabilización y asignación se mantengan consistentes conforme la app evoluciona.