Course Enrollment
Data Entity
Description
Records a user's enrollment in a specific course, tracking status, completion, and linking to resulting certifications. Enforces prerequisite certification checks and real-time seat availability constraints at enrollment time to prevent overbooking.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the enrollment record, generated at creation time | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who is enrolled. Scoped by Supabase RLS to the authenticated session or coordinator acting on behalf of a peer mentor. | required |
course_id |
uuid |
Foreign key referencing the course being enrolled in. Validated against the courses table before insertion. | required |
status |
enum |
Lifecycle state of the enrollment. Drives UI presentation and business logic gates (e.g., completion triggers certification issuance). | required |
enrolled_at |
datetime |
Timestamp when the enrollment record was created. Used for seat allocation ordering on waitlists and for reporting periods. | required |
completed_at |
datetime |
Timestamp when the user completed the course. Null until status transitions to 'completed'. Triggers certification issuance workflow. | - |
completion_evidence |
json |
Structured proof of course completion. May include test scores, attendance records, facilitator sign-off reference, or external validation tokens. Required when status is 'completed'. | - |
prerequisite_check_passed |
boolean |
Records whether prerequisite certification validation was performed and passed at enrollment time. Immutable after creation to preserve the audit trail. | required |
prerequisite_check_at |
datetime |
Timestamp of when the prerequisite check was evaluated. Supports audit trails and re-validation logic if certifications expire between check and enrollment confirmation. | - |
seat_reserved_at |
datetime |
Timestamp of when a course seat was atomically reserved during enrollment. Used with database-level locking to prevent overbooking under concurrent enrollment requests. | - |
enrolled_by_user_id |
uuid |
Foreign key to the coordinator or admin who enrolled the user on their behalf. Null if the user self-enrolled. Used for delegation audit trail. | - |
certification_id |
uuid |
Foreign key to the certification record issued upon successful course completion. Null until status is 'completed' and certificate has been issued by CertificateService. | - |
cancellation_reason |
text |
Free-text reason for cancellation or withdrawal, required when status transitions to 'cancelled' or 'withdrawn'. Stored for coordinator visibility and reporting. | - |
cancelled_at |
datetime |
Timestamp of cancellation or withdrawal. Set automatically when status transitions to 'cancelled' or 'withdrawn'. Triggers seat release to allow waitlisted users to be confirmed. | - |
waitlist_position |
integer |
Position in the course waitlist when status is 'waitlisted'. Computed relative to enrolled_at ordering. Null when status is not 'waitlisted'. | - |
created_at |
datetime |
Record creation timestamp managed by Supabase. Mirrors enrolled_at but kept separate for system auditing. | required |
updated_at |
datetime |
Last modification timestamp, automatically updated by a Supabase trigger on any field change. Used by mobile client for cache invalidation. | required |
Database Indexes
idx_course_enrollments_user_course_unique
Columns: user_id, course_id
Prevents duplicate active enrollments. Application-level duplicate check is supplemented by this DB constraint.
idx_course_enrollments_course_id
Columns: course_id
Supports fast seat-count queries: SELECT COUNT(*) WHERE course_id = ? AND status IN ('confirmed','waitlisted')
idx_course_enrollments_user_id
Columns: user_id
Supports peer mentor profile page queries fetching all enrollments and completion history for a user
idx_course_enrollments_status
Columns: status
Supports admin and coordinator dashboard queries filtering by enrollment status
idx_course_enrollments_course_status
Columns: course_id, status
Composite index for seat availability check: WHERE course_id = ? AND status = 'confirmed' — most frequent query path at enrollment time
idx_course_enrollments_enrolled_at
Columns: enrolled_at
Supports waitlist ordering and reporting period queries
idx_course_enrollments_certification_id
Columns: certification_id
Supports reverse lookup from certification to originating enrollment for certificate detail pages
Validation Rules
user_id_must_reference_active_user
error
Validation failed
course_id_must_reference_active_course
error
Validation failed
completion_evidence_required_on_complete
error
Validation failed
cancellation_reason_required
error
Validation failed
completed_at_after_enrolled_at
error
Validation failed
waitlist_position_only_for_waitlisted_status
error
Validation failed
certification_id_only_for_completed_status
error
Validation failed
enrolled_by_must_be_coordinator_or_admin
error
Validation failed
Business Rules
prerequisite_certification_required
A user may not enroll in a course if they lack any prerequisite certifications defined on the courses record. The CourseEnrollmentService calls checkPrerequisites(userId, courseId) before writing the enrollment. If any required certification is missing or expired, enrollment is blocked with an error response. The prerequisite_check_passed flag is set to true only on successful validation.
real_time_seat_availability
Enrollment is only permitted when the course has at least one available seat (seats_total - confirmed_enrollment_count > 0). The check is performed atomically using a database-level advisory lock or SELECT FOR UPDATE on the courses row to prevent race conditions under concurrent enrollment. If no seats are available, the user is placed on the waitlist (status = 'waitlisted') rather than blocked outright.
no_duplicate_active_enrollment
A user may not have more than one active enrollment (status in: pending, confirmed, waitlisted) for the same course simultaneously. Enforced both by the unique DB index on (user_id, course_id) and by a pre-insert check in CourseEnrollmentService. A cancelled or completed enrollment does not block re-enrollment.
completion_triggers_certification_issuance
When the enrollment status transitions to 'completed', CertificateService is invoked to create a new certifications record linked back via certification_id. The certifications record sets the validity window per the course's configured certificate_validity_months. This linkage is the primary mechanism by which HLF enforces peer mentor eligibility gating.
cancellation_releases_seat
When status transitions to 'cancelled' or 'withdrawn', the freed seat is made available. If the course has waitlisted enrollments, the earliest waitlisted record (by enrolled_at) is automatically promoted to 'confirmed' and a push notification is dispatched to the newly confirmed user.
coordinator_enrollment_delegation
A coordinator or admin may enroll a peer mentor on their behalf by supplying enrolled_by_user_id. The system verifies that the acting user holds coordinator or admin role via RLS and the Permission Guard before writing the record. The enrolled_by_user_id is stored for audit traceability.
valid_status_state_machine
Status transitions must follow the defined state machine: pending → confirmed | cancelled; confirmed → completed | failed | withdrawn | cancelled; waitlisted → confirmed | cancelled | withdrawn; completed and failed are terminal states that cannot be changed. Invalid transitions are rejected with an error.
rls_user_scope
Supabase RLS policies enforce that a peer mentor may only read and write their own enrollment records. Coordinators may read and create enrollments for users within their organizational scope. Global admins may access all records.