Overdue payment monitoring

A bill is delinquent on the reference date when its actual due date (with extensions applied) is before that date and it has not been paid. Not yet due and due today do not count. Cancelled is always out. A partial payment leaves only the remaining balance delinquent. In billing systems with automatic card charging, the invoice often has no due date: the actual due date is the date the invoice was finalized, when the first charge is attempted (in Stripe, `due_date` is null for `charge_automatically`; use `status_transitions.finalized_at`).

Official delinquency is a one-day picture: who owes on that date, how much and for how long. Having been late at some point in the month is a different metric, for history only. Store the daily or monthly snapshot in append mode so a past month's delinquency can be rebuilt exactly. Until a snapshot is stored, a past date can be rebuilt from each bill's status transition dates (issued, paid, cancelled, written off), never from the current status. In Stripe, these dates live in `status_transitions`.

Aging buckets cover only bills without a settlement. A paid bill leaves aging, even if it was overdue for months, and moves to the punctuality metric: days between due date and payment. Buckets are contiguous and start at 1 day, so the sum of the buckets equals the overdue balance.

Each bill's balance is the bill amount minus recorded settlements. Before trusting the ERP's ready-made balance field, test it: in real cases it came empty or zero on most installments. Interest and late fees received are kept apart from principal. In billing systems the ready-made remaining amount also misleads: in Stripe, `amount_remaining` stays filled on voided (`void`) and draft (`draft`) invoices and on invoices paid outside Stripe (`paid_out_of_band`, with no `amount_paid`). Treat any bill with a payment date on or before the reference date as settled, and drop drafts and voided ones. Sum balances per currency, never different currencies together.

When debt is rescheduled, the old bill is replaced by the new installments. A bill with renegotiated status (which some ERPs zero out) leaves, and only the new installments count. If the ERP keeps the old one as late, flag it by the renegotiation and take it out of the sum. In billing systems, renegotiating usually means voiding the invoice and issuing a new one (in Stripe, `void` plus a new invoice): the voided one leaves from its void date, and snapshots of earlier dates do not change.

In billing systems, each declined card attempt becomes a row, and the same invoice can have several. The grain for delinquency is the invoice or bill, never the attempt. While an invoice is being retried and is past due, it shows in the snapshot in the first bucket, like any open bill; once paid, it leaves. Do not create an exception for invoices in retry. Recovery within the billing cycle is measured separately, in the recovery rate.

Group by normalized and validated CPF (individual tax ID) or CNPJ (company tax ID), 11 or 14 digits, not by name. A tax ID of the wrong length is treated as missing, so debt does not stick to the wrong customer. The payer can differ from the account holder (a parent paying for a student, a head office paying for a branch): link the bill to the contract by id, and use the payer's tax ID for collection.

When bills are copied into a collections CRM, the CRM has sync lag and a cutoff date: anything older than the integration is not there. Use the CRM to know which collection stage a customer is in, and the ERP or billing to know how much they owe.

Loans between partners, balances between group companies, companion bills for card prepayments and test charges are not delinquency. An old pending charge with no operational trace (no link, boleto, Pix QR Code or invoice) is not real debt either.