Subscription delinquency and dunning

Keep attempts in an events table, one row per attempt, with date, reason and gateway. Delinquency, recovery and the queue count at the invoice or subscription grain. Automatic retries, duplicate slips and reissues are the same debt.

The month's cohort is the subscriptions with at least one failure in the month, anchored on the month's first failure. Recovered is one with a successful payment within one billing cycle from it: about 30 days monthly, 90 quarterly, one year annual. Every billing interval goes in, each with its own window. In a real base, recovery in closed months sat near half.

In recent months, the one-cycle window is still open. Flag the month as maturing until the window closes (monthly: end of month plus 31 days) and never read the low rate of an open month as a decline. A weekly reading with a short window is a different indicator and doesn't compare with the monthly one.

Normalize each gateway's decline codes into a single table, with category and whether a retry is worth it. Invalid number, CVC or expiry (incorrect_number, incorrect_cvc, invalid_expiry_month, invalid_expiry_year in Stripe) need a new card; a generic issuer decline or insufficient funds need a retry or a reminder. Codes that arrive with and without a leading zero are the same. A code missing from the table shows as unmapped, never as other, and someone reviews it every week.

The queue takes live subscriptions with an overdue open invoice and no retry scheduled by billing (next_payment_attempt empty in Stripe), plus those canceled by billing in the last 30 days whose last attempt failed and nothing was paid afterwards. While billing is still retrying, it sends its own notice: don't duplicate the contact. The queue is a snapshot, so check the invoice live before chasing.

A simple ladder works: up to 14 days from the failure, recover now; invalid card, ask for a new card; up to 60 days, resend the charge with the invoice link; beyond that, likely loss. Count days from the due date of the oldest open invoice, not from the last payment, which includes the cycle already paid. Sort by MRR within each priority.

Delinquent is whoever has a non-canceled charge that is overdue and unpaid on the reference date: month end for closed months, today for the current month. Due today doesn't count yet. There is no grace period: one day late already counts, and severity comes from the days-late bucket. Having been late at some point in the month is a different, historical-only metric. Delinquent is not inactive: the customer stays in the base until cancellation.

A past_due subscription stays in MRR flagged as in collections until it becomes canceled or unpaid. Removing it and adding it back when paid creates churn and reactivation that never happened. If the company decides to remove it after a grace period (for example, 7 days), the rule is written and the same in every month.

A cancellation by billing, or by the team under a delinquency policy, after failures with no payment is involuntary churn. Declare churn only after the recovery window, at the MRR from before the failure. A common policy is to cut off whoever has two or more overdue bills, the oldest more than 60 days late.

Billing and product only store whether the customer is blocked now. Record block and unblock events, via webhook or a daily snapshot, to know how long each customer stayed blocked and whether they paid afterwards. Blocking after a deadline is an operational decision, not automatic: don't infer a block from days late.