Modeling Learner Progress: Why a "Completed" Column Always Fails
Designing a data model that answers where someone stopped, how far they got, and where people drop off — without rewriting it every quarter.
Everyone starts with one boolean: `completed`. Then someone asks to resume playback at the exact second, then for an accurate completion percentage, then for a report on which lesson people abandon. One field answers none of them, and you cannot recover data you never recorded.
Record events, derive state
Instead of storing the conclusion, store what happened: started the lesson, reached second 140, finished, rewatched. Current state is derived from events, but analytics and every future question need the events themselves. You can always derive state from events; you can never derive events from state.
Three levels, not one
- Raw events: a timestamped fact, written often and read rarely.
- Aggregated state per learner and lesson: read on every page load, so it has to be fast.
- Summary per learner and course: completion percentage and last activity, for list views and reports.
Try to serve all three from one table and you pay twice: slow queries in the interface, and analytics that are impossible rather than merely hard.
Resume needs a different resolution
Recording playback position every second floods your database for no benefit. Record every five to ten seconds, plus on pause and exit, and keep only the latest position. Nobody notices resuming at second 140 instead of 145; the difference in write volume is enormous.
Precision beyond what the user can perceive is pure cost.
Define "complete" explicitly
Is a lesson complete at 90% watched? On clicking finish? On passing a quiz? That is a product decision, not a technical one, and it needs to be configurable per course — because it determines when a certificate is issued, which is the first thing a customer will disagree with you about.
Progress is where analytics come from
The right model hands you what content owners want for free: where people stop, which lesson gets rewatched most, average time to completion. Those are product questions answered from data you already have, if you designed it properly.
The theory behind storing events and deriving state from them is set out in Martin Fowler's Event Sourcing. If you would rather adopt a vocabulary for learning events than invent one, the xAPI specification was written for exactly this domain.
This model is among the hardest to redesign later, because data you never recorded cannot be recovered. Review it with us early.
FAQ
Won't the event table grow quickly?
Yes, so give it a retention policy: keep raw events for months and aggregates forever. Aggregation preserves the analytical value at a fraction of the size.
Should I use full event sourcing?
Usually not. Record events and materialize derived state; rebuilding from events on every read is complexity you do not need.
How do I handle course content changing?
Tie progress to a stable lesson identifier rather than its position, and decide explicitly whether adding a lesson reduces existing completion percentages.