Subscription billing reconciliation

Goal: for each customer, compute EXPECTED (from the contract schedule) and BILLED (from invoices) for one period in one reporting currency, then classify the gap. Write the rules down before coding; most errors are rule mistakes, not arithmetic.

1. Read every rule source first

Specs rarely sit in one place. Read all prose (memos, chat logs, READMEs) end to end, in time order. Later messages override earlier ones. Note every definition (period length, tolerance, rate choice, who is in scope) and every exception. Re-read the rules after your first output and check each one against the data, not just the first mention.

2. Expected amount (from the contract)

• Schedule amounts are per billing term (monthly, quarterly, annual). Normalise to the period: monthly x1, quarterly /3, annual /12, unless the spec says otherwise.
• Contract changes (upgrade, downgrade, price change, pause) take effect on their effective date. Build a per-day timeline and sum per day, rather than assuming one price for the whole period.
• Proration is by calendar days in the period (use the actual day count of the period, not a flat 30, unless told).
• Status rules: trials bill zero; the churn/termination date is normally the last day of service, inclusive; days after it earn nothing. Check which date convention the spec uses.
• Dates arrive in mixed formats; parse each explicitly and validate that none failed.

3. Billed amount (from invoices)

• An invoice export is often an event log: the same invoice id can appear many times. Keep only the latest snapshot per id (by export timestamp), then apply status filters. Draft and void never count; paid and open do. A later snapshot can cancel an invoice that an earlier one showed as paid.
• An invoice covering a multi-period service window contributes only the share of days that fall inside the reporting period: overlap days / total window days. Do not count by issue date alone.
• Credit notes reduce billed revenue. A credit note belongs wherever the invoice it corrects belongs: attribute it to the same customer AND the same service period as the original, regardless of its own issue date or its own (often blank or wrong) customer field. If the original is outside the period, the credit note is outside it too. If the original does not count (void, draft, cancelled), the credit note is moot and is ignored.
• Never infer a credit note's customer from its own text when it carries a link to the invoice it corrects.