Customer event store for a subscription business

The company reported churn from a monthly snapshot of the subscriptions table, which meant a customer who cancelled and resubscribed inside a month was invisible, and a downgrade looked like nothing at all. The growth team had been making retention decisions on that number for about a year. What they needed was the event history — every state change with its timestamp and cause — so that churn, downgrade and reactivation could be counted separately and any figure could be recomputed from source.

Data engineer on a team of three, with an analytics engineer and the growth team's analyst. I owned ingestion and the event model; the analyst owned the definitions.

I built the change-capture from the application database, the event model, and the backfill that reconstructed about three years of history from audit logs and billing records. The backfill was most of the work and most of the risk, because the audit log had a format change eighteen months back that nobody had documented. I owned the daily consistency check that compares reconstructed state against the live subscriptions table and reports any drift, which is the only reason anyone believes the numbers.

I insisted the event store be the source of truth for anything historical and that the subscriptions table be treated as a cache of current state, which was a bigger cultural change than a technical one. The alternative — keeping snapshots and adding events beside them — leaves two numbers that disagree, and the team would have kept quoting whichever was convenient. I also chose to model a downgrade as its own event type rather than a cancel-plus-subscribe pair, because collapsing them is exactly the error we were fixing. For capture I used the database's logical replication rather than application-emitted events, since application events would have needed changes in eleven places and would have missed anything written by a script.

The backfill could only be as good as the audit log, and for one eleven-week window in the second year the log is incomplete — I flagged that period explicitly in the model rather than interpolating, and the analyst reports around it. The application team could not take on work, so nothing about capture could require their involvement. We also had a hard reporting date at quarter end, which meant the backfill shipped before the tooling around it.

Real churn turned out to be about 1.4 points higher than reported, and roughly a fifth of what had been counted as churn was actually downgrade — which changed where the growth team spent the following quarter. Reactivation became visible for the first time and is now a tracked number. Any historical retention question is now recomputable rather than a research task, which took the analyst's ad-hoc turnaround from days to about an hour.

The analytics engineer built the models on top of my event store and pushed back hard on my first event schema, correctly. The growth analyst defined every metric and did the painful work of explaining to leadership why the old numbers had been wrong. A backend engineer helped me read the audit log format change.