Learning platform

Why this design

Every course platform - Udemy, Coursera, corporate training, a bootcamp's own site - has the same four nouns: courses, lessons, enrolments, progress. The schema looks obvious until the first time an author edits a live course. Insert a lesson at position 3 and progress keyed on position now points at the wrong lessons for every learner; overwrite a lesson row and the record of what a learner completed now describes content they never saw. This board is about separating what authors edit from what learners did, so that each can change without corrupting the other.

What you will learn

• Why a lesson is two tables - a stable identity (lessons) and immutable content (lesson_revisions) - and progress points at the identity.
• Why a published course_version is a frozen snapshot of its curriculum, and an enrolment pins the version it started on.
• Why curriculum_items can use an integer position while a kanban board cannot: nobody reorders a published list.
• Why progress is one upserted row per (enrolment, lesson) instead of a log of player heartbeats, and why completion only moves forward.
• How a certificate records the version it certifies, so it stays true after the course changes.
• The deliberate hole: progress cannot say which revision was completed, so a mandatory re-take cannot be expressed.

How to read this board

1. CONTENT along the top: courses, course_versions, curriculum_items, lesson_revisions, lessons.
2. Then PEOPLE AND ENROLMENT (users, enrolments, certificates) and PROGRESS (lesson_progress, quiz_attempts). Press play for the walkthrough.
3. Page 1 · Requirements - the guarantees, written so a constraint can enforce them.
4. Pages 2 and 3 · Capacity and Storage - heartbeats, progress rows and why the content tables are tiny.
5. Page 4 · Availability and integrity - constraints, the publish transaction and the one-way completion rule.
6. Page 5 · How it works, then 6 · Deployment and migrations.

Ask these before drawing anything

• Can authors edit a course while people are enrolled? Always yes, and it is the requirement everything else bends around.
• When a course changes, do existing learners see the new version, the old one, or choose? The usual answer: new learners get the new version, existing learners stay unless they opt in or an admin migrates them.
• Is any course compliance training, where completing an outdated version does not count?
• Are certificates verifiable by a third party, and must they stay valid after the course changes?
• Is progress per lesson only, or within a lesson (video position, quiz attempts)?

Guarantees, as sentences a constraint can enforce

• A learner is enrolled in a course at most once at a time: unique (user_id, course_id) where status is active.
• An enrolment always refers to a published version of the same course it is enrolled in.
• A published version's curriculum never changes: curriculum_items are insert-only once the version has published_at.
• There is at most one progress row per (enrolment, lesson).
• completed_at, once set, is never cleared or moved later.
• A certificate refers to exactly one enrolment and the version that enrolment completed.

Out of scope

• Payments and pricing - an enrolment may be created by a purchase, but the order lives elsewhere.
• Video hosting and transcoding - lesson_revisions holds a reference, not bytes.