Learner Progress & Completion State Schema
Learner Progress & Completion State Schema
Introduced in: v1.0.5
This page describes the database schema that underpins learner progress tracking across the platform. It covers what data is captured, how it maps to the learner experience, and how administrators can use it for reporting.
Overview
The progress schema provides a persistent record of each learner's interaction with course content. It is the single source of truth for:
- Which lessons a learner has viewed
- When each lesson was completed
- How far through a course a learner has progressed
This data layer supports both the learner-facing product (progress indicators, resume functionality) and admin-facing reporting tools (completion rates, engagement analytics).
Schema Design
Lesson View Records
Each time a learner views a lesson, a record is created or updated that associates:
- The learner (user identity)
- The lesson (content unit within a course)
- A viewed flag indicating the lesson has been accessed
This enables the platform to accurately restore a learner's progress state between sessions.
Completion Timestamps
When a learner completes a lesson, a completion timestamp is written to the record. This timestamp:
- Marks the precise moment the lesson was finished
- Serves as an immutable audit record
- Is used to order completion events for reporting and analytics
Course Progress Percentage
A progress percentage is calculated and persisted at the course level for each learner. This value represents:
progress_percentage = (completed_lessons / total_lessons_in_course) × 100
Storing this value directly in the schema avoids expensive recalculation on every page load and allows efficient querying for reporting purposes.
How This Powers the Learner Experience
| Feature | Schema Dependency |
|---|---|
| Progress bar on course page | Course progress percentage |
| Lesson completion checkmarks | Lesson completion timestamp |
| "Resume where you left off" | Lesson view records |
| Completion certificate eligibility | All lessons having completion timestamps |
How This Powers Admin Reporting
Administrators can leverage the progress schema to answer questions such as:
- What percentage of enrolled learners have completed a given course?
- Which lessons have the highest drop-off rate (viewed but not completed)?
- How long does it take learners to complete a course from first access to final lesson?
- Which learners are overdue based on assigned completion deadlines?
Because progress percentages and timestamps are persisted at the database level, these queries are efficient even across large learner populations.
Current Status
v1.0.5 delivers the schema definition only. The following are not yet available in this version but will follow in subsequent releases:
- REST API endpoints for reading/writing progress state
- Learner-facing UI components consuming this schema
- Admin dashboard views and reports