A revenue ledger between Stripe and the CRM

Over time, separate workflows had grown up for subscriptions, invoices and contraction. Each solved one need. None agreed on what a subscription change meant, and every new question from Finance meant another workflow.

The most painful gap was contraction: customers paying less but not leaving. Finance pulled it from Stripe every week and filed it by hand.

• A customer can have several subscriptions, each with several items.
• Stripe sends events out of order, sometimes twice, and retries for up to three days.
• Discount-only changes arrive without the previous values, so there is nothing to compare against.
• An existing CRM module and its reports had to keep working.

Before any code, I agreed the terms with the people who report on them. Contraction means an account pays less but still has an active subscription. Churn means every subscription on the account is gone. The first version replayed events in order to decide; the final rule is simpler and sturdier: look at the account's total MRR at the daily cutoff. Zero is churn. Anything else is contraction.

The design has three layers, each with one job:

• Capture. Every Stripe event is stored as it arrives, with a unique key so duplicates are ignored. Instead of trusting the payload, the workflow re-fetches the event from Stripe.
• Net. Once a day, each subscription's events are reduced to a single movement using its first and last state, so swaps, duplicates and retries cancel out.
• Review. Movements carry a needs-attention flag and an exclude option, so Finance reviews exceptions instead of compiling the list.
• Unmatched customers. Anything that can't be linked to an account is kept, flagged and sent to a saved worklist with an alert. Nothing disappears.

• Plan swaps doubled starting MRRWhen one item replaced another, both were counted as the starting point. Moving to first-and-last netting removed the double count.
• Starting MRR silently became zeroOne missing line meant the previous state was never saved, so every movement looked new. It surfaced only when totals failed to reconcile.
• One duplicate row, one missing rowOn multi-item changes, the sheet matched rows on too few columns. Adding the item and import date to the match key fixed both at once.
• Coupons changed MRR but produced nothingStripe omits the old values on discount-only changes. A parallel branch now handles discount events directly.
• Dates rejected by the CRMOne endpoint wanted an explicit offset where another wanted none. Small, but it stopped the whole sync until found.

Finance moved from compiling contraction to reviewing it. The new model gives every future billing question one place to look, instead of another workflow.