Payment Ledger Model
The payments table works like a bank ledger: every financial event is a new row. Financial fields are never mutated after insert. This is not an accident; it is the core invariant that makes balance calculation correct.
Financial fields (frozen at insert): status, amount, gateway_transaction_id, gateway_key, gateway_status, sale_type, plan_type, recurring_cycle.
Non-financial fields (may be backfilled later by downstream integrations and lifecycle operations): operational metadata stored in payment_payload, accounting identifiers like invoice numbers, contact data such as email, and the deleted_at soft-delete column.
Writing UPDATE payments SET status = ... breaks the system. Backfilling an invoice id, redacting an email after anonymization, or appending payload metadata does not, as long as the financial fields stay untouched.
Why insert-only
A mutable status field creates an ambiguity problem: did this payment start as pending and get approved, or was it always approved? How many times was it retried? When was it refunded? None of those questions can be answered from a single row that gets overwritten.
With insert-only semantics, each state transition is a new row with its own timestamp. The full history is always available. Balance calculations sum rows rather than reading a stored field, so they can never be corrupted by an in-place update.
The same event delivered twice by a gateway produces one row, not two: a composite idempotency key makes inserts idempotent.
The idempotency key is the combination of:
gateway_idtenant_idgateway_transaction_idgateway_keystatusorder_iduser_plan_id
Because status is part of the key, a pending row and an approved row for the same transaction produce two separate rows, not one updated row. When writing a new IPN handler, do not expect to "update" a pending row into approved: insert the approved row and let the ledger carry both events.
Concurrent IPN deliveries for the same order are serialized by a per-order distributed lock (store-payment-order-{order_id}). The lock plus the composite idempotency key together prevent duplicate ledger rows.
Refunds
A refund does not modify the original approved row. It inserts a new row with status = 'refunded'. The original row remains intact as the permanent record of the charge.
Refund metadata is stored inside the refund row's payment_payload JSON field; see the Glossary entry for payment_payload for the schema (it holds the source payment id under original_payment_id; there is no separate column).
This applies to all three refund paths:
| Path | Trigger |
|---|---|
| IPN-driven | Gateway notifies Farfalla; status maps to refunded |
| Dashboard-initiated | Admin initiates via the refund UI; service calls gateway API then inserts |
| Subscription cancellation | Cancel flow delegates to gateway, then inserts |
Supported gateways: Stripe, MercadoPago, Yuno. PayU and Manual have no refund path.
Yuno pending refunds (separate from the ledger)
Yuno refunds can return as pending. When that happens, the platform does not insert a refund row into payments yet. Instead, it writes a row into the pending_payment_refunds table, and the VerifyPendingRefunds job polls the gateway until the refund is confirmed (then the ledger row is inserted) or the attempt limit is reached (the pending row is marked failed). MercadoPago and Stripe refunds are synchronous and never produce a pending row.
Balance calculation
The available refund balance for a payment is computed by summing the gross_sale_in_cents of all refund rows for that same billing cycle, then subtracting from the approved row's gross_sale_in_cents. No stored balance field is read.
The field to sum is gross_sale_in_cents. Refund rows store the refunded amount as a positive value in this column; the balance is the approved row's gross_sale_in_cents minus the sum of gross_sale_in_cents over all refund rows for the same order_id, recurring_cycle, and user_plan_id (or plan_type for shipping).
For shipping payments (no associated UserPlan), the sum is scoped by plan_type instead of user_plan_id. See Shipping Payment.
Idempotency
IPN handlers can be retried safely. The same gateway transaction at the same status produces exactly one row. If the row already exists, the insert is a no-op.
What this means in practice
- Never UPDATE a payment row. If a gateway changes a transaction's status, the IPN inserts a new row.
- Never compute a balance from a single row. Always sum the ledger.
- Missing rows mean a missed event; fix the event delivery, not the data.