LMS data model
LMS data model
Section titled “LMS data model”Audience: backend
Status: specced
Owns: backend
Depends on: LMS overview, API overview, Orchestrator data model, Coding standards
Implementation SoT: elimi-ecosystem/apps/lms/prisma/schema.prisma (migrated). Design seed also exists at ecosystem-root lms-schema.prisma. This page is the human outline. Status fields stay String + CHECK in migration SQL (same convention as CAP).
Conventions
Section titled “Conventions”| Rule | Detail |
|---|---|
| ULID PKs | @id @db.Char(26), app-generated |
| Money | BigInt minor units on Course — never Float |
| JSONB | Onboarding data, item body / quiz, completion policy, sequencing, CMI snapshot |
| Enums | Structural only (LmsPersonaType, ItemType, EntitlementSource, StaffRole, …) |
| Status fields | String + intended CHECK |
| Cross-service ids | sectorId / tradeId / qualificationLevelId / unitId / paymentId / userId / assetId are strings, not FKs |
LmsUser — identity anti-corruption layer
Section titled “LmsUser — identity anti-corruption layer”| Pattern | Rule |
|---|---|
One table holds Orchestrator userId |
LmsUser.userId unique + indexed |
All other LMS tables FK to LmsUser.id |
Not to Orchestrator ids directly |
| Provisioning | Consume user.created idempotently (dedupe on userId); lazy-create on first authenticated LMS hit is allowed |
Core models
Section titled “Core models”| Model | Notes |
|---|---|
LmsUser |
Local id + OL userId; denormalized email / displayName / photoAssetId for directory + profile photo |
OnboardingRecord |
Unique (lmsUserId, persona); JSONB data + schemaVersion |
LearnerProfile, InstructorProfile, StaffMembership |
Created on onboarding submit |
LearnerMembership |
LMS-local learner status (ACTIVE | SUSPENDED). Does not change OL User.status. |
Invite |
Instructor, staff, or learner; email + status |
Course |
Current list price, status, optional CAP linkage (unitId + qualificationLevelId), completionPolicy JSON, sequencing JSON |
CoursePriceRevision |
Append-only list-price history; current row has effectiveUntil null |
CourseModule, CourseItem |
Item types include SCORM_PACKAGE (packageAssetId) |
CourseInstructor |
OWNER / CO_INSTRUCTOR |
Entitlement |
Source + status (self_pay / admin_grant / sponsor / cap_recommendation / level_offering / organization_seat); optional organizationId + seatAllocationId. checkout JSON + paymentId are the admin payments ledger for standalone course self-pay (no new payments table). |
Enrollment |
Unique (courseId, learnerLmsUserId); percentComplete |
LevelOffering |
Sellable QualificationLevel snapshot; current list price + priceIsOverride |
LevelOfferingItem |
Member course + unitId + price snapshot at last publish |
LevelOfferingPriceRevision |
Append-only offering list-price history |
LevelEnrollment |
One payment per learner+offering; fans out child entitlements; optional organizationId / seatAllocationId / planned cohortId. Its checkout JSON is the pack row on GET /admin/payments (child entitlements are omitted). |
SeatAllocation |
LMS projection of sponsored seats (organizationId + name/email, totalSlots). Canonical cohort invoice is on OL — Organization |
SeatAllocationItem |
Course or level offering on that allocation |
SeatAllocationSeat |
Distinct learner slot; leftover seats = totalSlots − seats |
ItemProgress |
Per item status + optional CMI JSON |
AssessmentAttempt |
Native quizzes only |
ScormRuntimeSession |
LMS launch path (extracted files), not Cloudinary URL |
LmsCertificate |
1:1 enrollment when issued; OL assetId |
OutboxEvent |
Transactional outbox |
LmsPlatformSettings |
Singleton Settings screen — contact email/phone + terms/privacy/refund (text and/or OL PDF assetId) |
Entitlement vs enrollment
Section titled “Entitlement vs enrollment”- Entitlement = why the learner may access.
- Enrollment = progress container.
- Player requires entitlement
ACTIVE, enrollmentACTIVE, andLearnerMembershipnotSUSPENDED. Do not puthasPaidonCourseorLmsUser. - Organization records live on Orchestrator (staff, invoices, settlement). LMS stores
organizationId+ plannedcohortId(and a name/email snapshot onSeatAllocation) only.
SCORM storage
Section titled “SCORM storage”Canonical zip bytes: Orchestrator StorageAsset (packageAssetId). Extracted player tree: LMS-local under SCORM_EXTRACT_DIR (ScormRuntimeSession.launchPath is the dir that contains imsmanifest.xml). HTTP: GET /player/scorm/{sessionId}/player (HTML shell) and GET /player/scorm/{sessionId}/content/{path} (path-jailed files). Future OL package adapter does not change LMS FKs — still an assetId.
See also
Section titled “See also”- LMS API · CAP data model (same ACL pattern)
- Payment architecture