Skip to content

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).

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
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 = why the learner may access.
  • Enrollment = progress container.
  • Player requires entitlement ACTIVE, enrollment ACTIVE, and LearnerMembership not SUSPENDED. Do not put hasPaid on Course or LmsUser.
  • Organization records live on Orchestrator (staff, invoices, settlement). LMS stores organizationId + planned cohortId (and a name/email snapshot on SeatAllocation) only.

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.