Data Models

:::info Source Sourced from docs/12-data-models.md in the documentation repo. :::

Companion: 02 DDD & Bounded Contexts · 03 Microservices · 04 Event-Driven

This doc consolidates the canonical TypeScript-style schemas used across the platform. Each service owns its tables; cross-service reads are projections, never direct DB queries.

0. Common Primitives

type Branded<T, B extends string> = T & { readonly __brand: B };
type ISODate = string;             // RFC 3339, UTC
type ISODuration = string;         // ISO 8601 duration, e.g. "P14D"
type ULID = string;
type SHA256 = string;
type JWS = string;
type Locale = string;              // BCP 47 (e.g., 'en-US', 'ar-SA')
type ISO4217 = string;
type Email = string;
type RRULEString = string;
type SemVer = string;
type JSONValue = string | number | boolean | null | { [k: string]: JSONValue } | JSONValue[];
type I18nString = Record<Locale, string>;
type I18nMarkup = Record<Locale, string>;

interface Money { amountMicro: number; currency: ISO4217; }
interface MediaRef { assetId: MediaAssetId; variant?: string; }
interface AddressLine { ... }
interface ABACQuery { /* expression tree */ }
type DateRange = { from: ISODate; to: ISODate };

interface AIProvenance {
  model: string;
  version?: string;
  promptId?: string;
  promptVersion?: SemVer;
  traceId: string;
  decisionId?: string;             // links to HITL acceptance record
  local: boolean;
  generatedAt: ISODate;
  reviewedBy?: UserId;
  reviewedAt?: ISODate;
  cost?: { microUSD: number; tokens: { in: number; out: number } };
}

interface VectorClock { [deviceId: string]: number; }

1. ERD Overview (text)

Tenant ─── 1..* Membership *──1 User
Tenant ─── 1..* OrgUnit
Tenant ─── 1..* Role
Tenant ─── 1..* DynamicGroup
Course ─── 1..* CourseVersion
Course ─── 1..* Listing (provider) ─── 1..* PricingPlan
Listing ─── 1..* Order ─── 1..* OrderLine
Order  ─── 0..* License (granted) ─── 0..* Seat
License ── 1..* Enrollment
Assignment ─── 1..* AssignmentTarget
Assignment ─── 1..* ComplianceWindow ─── 0..1 Enrollment
Enrollment ─── 1..* Attempt ─── 1..* Statement
Attempt    ─── 0..1 CompletionRecord ─── 0..1 Certificate
Course ─── 1..* PlayPackage ─── 1..* PlayPackageBundle ─── 0..* LicenseEnvelope
MediaAsset ─── 1..* AssetVariant / CaptionTrack / Transcript
Prompt ─── 1..* PromptVersion
Embedding *── 1 SourceRef (block | lesson | listing | document)
SyncRegistration · SyncCursor · LocalMutation · ConflictRecord

2. Identity Schemas (identity-service)

type UserId = Branded<string, 'UserId'>;
type SessionId = Branded<string, 'SessionId'>;
type DeviceId = Branded<string, 'DeviceId'>;
type APIKeyId = Branded<string, 'APIKeyId'>;

interface User {
  id: UserId; primaryEmail: Email; emailVerified: boolean;
  status: 'active' | 'locked' | 'disabled' | 'pending_verification';
  homeTenantId?: TenantId; createdAt: ISODate;
}

interface Credential {
  userId: UserId; kind: 'password' | 'webauthn' | 'magic_link';
  hash?: string; webauthn?: WebAuthnPublicKey;
  rotatedAt: ISODate; failedAttempts: number; lockedUntil?: ISODate;
}

interface Session {
  id: SessionId; userId: UserId; deviceId: DeviceId;
  issuedAt: ISODate; expiresAt: ISODate;
  refreshTokenHash: string; ip: string; ua: string; revokedAt?: ISODate;
}

interface Device {
  id: DeviceId; userId: UserId; fingerprint: string; publicKey: string;
  trustedAt?: ISODate; lastSeenAt: ISODate;
}

interface MFAFactor { kind: 'totp' | 'sms' | 'webauthn' | 'recovery_codes'; metadata: JSONValue; }

interface APIKey { id: APIKeyId; tenantId: TenantId; ownerUserId?: UserId; scopes: string[]; hash: string; createdAt: ISODate; expiresAt?: ISODate; }

interface ExternalIdentity { userId: UserId; provider: 'oidc' | 'saml' | 'google' | 'microsoft' | 'keycloak' | 'okta' | 'cognito' | 'firebase_auth' | 'generic_oidc' | 'generic_saml'; subject: string; issuer: string; metadata: JSONValue; }

3. Tenant Schemas (tenant-service)

type TenantId = Branded<string, 'TenantId'>;
type OrgUnitId = Branded<string, 'OrgUnitId'>;
type RoleId = Branded<string, 'RoleId'>;

interface Tenant { id: TenantId; type: 'org' | 'provider' | 'individual' | 'org+provider'; name: string; slug: string; homeRegion: 'us' | 'eu' | 'me' | 'ap'; plan: { id: string; addons: string[] }; status: 'active' | 'trial' | 'suspended' | 'closed'; settings: TenantSettings; createdAt: ISODate; }

interface OrgUnit { id: OrgUnitId; tenantId: TenantId; parentId?: OrgUnitId; name: I18nString; ltreePath: string; }

interface Membership { id: ULID; tenantId: TenantId; userId: UserId; roleIds: RoleId[]; orgUnitIds: OrgUnitId[]; status: 'invited' | 'active' | 'suspended'; invitedAt?: ISODate; joinedAt?: ISODate; }

interface Role { id: RoleId; tenantId: TenantId | null; name: string; permissions: Permission[]; isSystem: boolean; }

interface Permission { resource: string; action: string; condition?: ABACQuery; }

interface DynamicGroup { id: ULID; tenantId: TenantId; name: string; query: ABACQuery; lastEvaluatedAt?: ISODate; }

interface FeatureFlagOverride { tenantId: TenantId; flag: string; value: JSONValue; reason?: string; }

4. Catalog Schemas

type CourseId = Branded<string, 'CourseId'>;
type CourseVersionId = Branded<string, 'CourseVersionId'>;

interface Course { id: CourseId; tenantId: TenantId; slug: string; title: I18nString; description: I18nString; defaultLocale: Locale; visibility: 'private' | 'org' | 'marketplace' | 'public'; status: 'active' | 'archived'; latestVersionId: CourseVersionId; createdAt: ISODate; }

interface CourseVersion { id: CourseVersionId; courseId: CourseId; versionLabel: SemVer; publishedAt: ISODate; publishedBy: UserId; durationMinutes: number; locales: Locale[]; playPackageRef: PlayPackageId; status: 'published' | 'deprecated' | 'withdrawn'; }

interface Taxonomy { id: ULID; tenantId?: TenantId; namespace: string; tree: TaxonomyNode[]; }
interface TaxonomyNode { id: string; label: I18nString; children: TaxonomyNode[]; }

5. Authoring Schemas (highlights; full block taxonomy in 10)

type CourseDraftId = Branded<string, 'CourseDraftId'>;
type LessonId = Branded<string, 'LessonId'>;
type BlockId = Branded<string, 'BlockId'>;

interface CourseDraft { id: CourseDraftId; tenantId: TenantId; publishedCourseId?: CourseId; title: I18nString; defaultLocale: Locale; modules: ModuleDraft[]; state: 'editing' | 'in_review' | 'approved' | 'publishing' | 'published_idle'; collaborators: UserId[]; draftVersion: number; createdAt: ISODate; updatedAt: ISODate; }

interface ModuleDraft { id: string; title: I18nString; lessons: LessonDraft[]; }
interface LessonDraft { id: LessonId; title: I18nString; blocks: Block[]; estimatedMinutes?: number; }

interface BlockBase { id: BlockId; aiProvenance?: AIProvenance; status: 'draft' | 'draft_ai' | 'reviewed' | 'published'; reviewedBy?: UserId; reviewedAt?: ISODate; }

type Block = TextBlock | ImageBlock | VideoBlock | AudioBlock | QuizBlockRef | BranchingBlockRef | EmbedBlock | InteractionBlock | DividerBlock | AIBlock;

6. Content-Packaging Schemas

type PlayPackageId = Branded<string, 'PlayPackageId'>;
type BundleId = Branded<string, 'BundleId'>;

interface PlayPackage { id: PlayPackageId; tenantId: TenantId; courseVersionId: CourseVersionId; locale: Locale; manifest: PackageManifest; assets: AssetReference[]; builtAt: ISODate; formats: FormatArtifacts; hash: SHA256; signature: JWS; status: 'building' | 'built' | 'revoked'; }

interface PackageManifest { version: '1.0'; course: { id: CourseId; versionLabel: SemVer; title: I18nString; durationMinutes: number }; modules: ModuleManifest[]; assistant?: AssistantConfig; navigation: NavigationModel; prerequisites?: PrerequisiteRule[]; }

interface PlayPackageBundle { id: BundleId; playPackageId: PlayPackageId; tenantId: TenantId; url: string; sha256: SHA256; signature: JWS; encryption: { alg: 'AES-256-GCM'; kid: string }; license: LicenseEnvelope; builtAt: ISODate; status: 'available' | 'revoked'; }

interface LicenseEnvelope { bundleId: BundleId; enrollmentId: EnrollmentId; userId: UserId; deviceId: DeviceId; issuedAt: ISODate; expiresAt: ISODate; features: { aiTutor: boolean; assessments: boolean; certificate: boolean; copyDownloadable: boolean }; signature: JWS; }

7. Marketplace Schemas

type ListingId = Branded<string, 'ListingId'>;
type LicenseId = Branded<string, 'LicenseId'>;
type OrderId = Branded<string, 'OrderId'>;

interface Listing { id: ListingId; providerTenantId: TenantId; courseId: CourseId; visibility: 'unlisted' | 'public'; pricingPlans: PricingPlan[]; marketing: ListingMarketing; state: 'draft' | 'submitted' | 'approved' | 'live' | 'suspended' | 'retired'; }

interface PricingPlan { id: string; kind: 'one_time' | 'subscription' | 'seat_pack' | 'site_license'; currency: ISO4217; price: Money; seats?: number; intervalMonths?: number; perpetualOfflineAccess: boolean; }

interface Order { id: OrderId; buyerTenantId: TenantId; buyerUserId: UserId; lines: OrderLine[]; totals: Money; status: 'created' | 'pending_payment' | 'paid' | 'fulfilled' | 'refunded' | 'failed'; placedAt: ISODate; }

interface License { id: LicenseId; tenantId: TenantId; listingId: ListingId; courseId: CourseId; scope: 'individual' | 'org'; seats: number; remainingSeats: number; validFrom: ISODate; validUntil?: ISODate; state: 'active' | 'expired' | 'revoked'; source: 'purchase' | 'gift' | 'manual'; refundDeadline?: ISODate; }

8. Billing Schemas

type SubscriptionId = Branded<string, 'SubscriptionId'>;
type InvoiceId = Branded<string, 'InvoiceId'>;
type PaymentId = Branded<string, 'PaymentId'>;

interface Subscription { id: SubscriptionId; tenantId: TenantId; planId: string; state: 'trialing' | 'active' | 'past_due' | 'canceled' | 'paused'; currentPeriod: { start: ISODate; end: ISODate }; trialEnd?: ISODate; cancelAt?: ISODate; itemQuantities: Record<string, number>; }

interface Invoice { id: InvoiceId; tenantId: TenantId; customerId: string; lines: InvoiceLine[]; subtotals: Money; taxes: TaxLine[]; total: Money; currency: ISO4217; status: 'draft' | 'open' | 'paid' | 'void' | 'uncollectible'; dueAt: ISODate; pdfUrl?: string; }

interface Payment { id: PaymentId; invoiceId?: InvoiceId; orderId?: OrderId; tenantId: TenantId; amount: Money; currency: ISO4217; processor: string; processorRef: string; status: 'requires_action' | 'pending' | 'succeeded' | 'failed' | 'refunded'; }

9. Enrollment + Assignment Schemas

type EnrollmentId = Branded<string, 'EnrollmentId'>;
type AssignmentId = Branded<string, 'AssignmentId'>;

interface Enrollment { id: EnrollmentId; tenantId: TenantId; userId: UserId; courseId: CourseId; courseVersionId: CourseVersionId; source: { kind: 'assignment' | 'purchase' | 'manual' | 'self_signup'; ref: string }; state: 'active' | 'completed' | 'expired' | 'revoked'; enrolledAt: ISODate; completedAt?: ISODate; expiresAt?: ISODate; lastAccessedAt?: ISODate; attemptCounter: number; }

interface Assignment { id: AssignmentId; tenantId: TenantId; createdBy: UserId; title: I18nString; courseId: CourseId; courseVersionPolicy: 'pin' | 'latest'; pinnedVersionId?: CourseVersionId; targets: AssignmentTarget[]; rrule?: RRULEString; startDate: ISODate; dueOffset: ISODuration; gracePeriod: ISODuration; escalation: EscalationPolicy; reminderPolicy: ReminderPolicy; state: 'draft' | 'active' | 'paused' | 'archived'; aiSuggested?: boolean; }

interface ComplianceWindow { id: ULID; assignmentId: AssignmentId; userId: UserId; occurrenceStart: ISODate; dueAt: ISODate; graceUntil: ISODate; state: 'open' | 'in_progress' | 'completed' | 'overdue' | 'closed_missed'; enrollmentId?: EnrollmentId; }

10. Delivery + Progress + Assessment + Certification Schemas

type PlaySessionId = Branded<string, 'PlaySessionId'>;
type AttemptId = Branded<string, 'AttemptId'>;
type StatementId = Branded<string, 'StatementId'>;
type CertificateId = Branded<string, 'CertificateId'>;

interface PlaySession { id: PlaySessionId; tenantId: TenantId; userId: UserId; enrollmentId: EnrollmentId; courseVersionId: CourseVersionId; deviceId: DeviceId; attemptNumber: number; state: 'init' | 'active' | 'paused' | 'completed' | 'abandoned'; cursor: NavigationCursor; startedAt: ISODate; lastActivityAt: ISODate; endedAt?: ISODate; offlineMountId?: string; }

interface Attempt { id: AttemptId; tenantId: TenantId; userId: UserId; enrollmentId: EnrollmentId; courseVersionId: CourseVersionId; attemptNumber: number; startedAt: ISODate; endedAt?: ISODate; outcome?: 'passed' | 'failed' | 'incomplete' | 'abandoned'; score?: number; durationSeconds?: number; state: 'open' | 'closed'; }

interface Statement { id: StatementId; tenantId: TenantId; actor: Actor; verb: Verb; object: Activity | StatementRef; result?: Result; context?: Context; timestamp: ISODate; stored: ISODate; authority: Account; cmi5?: { sessionId: string; registration: string }; attemptId: AttemptId; }

interface QuizBank { id: ULID; tenantId: TenantId; title: I18nString; questions: Question[]; gradingRule: GradingRule; }
interface BranchingScenario { id: ULID; tenantId: TenantId; title: I18nString; rootNodeId: string; nodes: ScenarioNode[]; scoring: ScenarioScoring; }
interface AttemptResult { attemptId: AttemptId; quizBankId?: ULID; scenarioId?: ULID; responses: Response[]; scaledScore: number; passed: boolean; durationSeconds: number; }

interface Certificate { id: CertificateId; tenantId: TenantId; userId: UserId; courseId: CourseId; courseVersionId: CourseVersionId; enrollmentId: EnrollmentId; templateId: string; issuedAt: ISODate; expiresAt?: ISODate; state: 'pending_offline_verification' | 'issued' | 'revoked'; evidence: { completionRecordId: string }; proof: JWS; artifacts: CertificateArtifacts; verificationToken: string; }

11. Notification + Media + Search + Analytics

interface Notification { id: ULID; tenantId: TenantId; userId: UserId; templateKey: string; channel: 'email' | 'sms' | 'push' | 'inapp' | 'webhook'; variables: Record<string, JSONValue>; locale: Locale; status: 'queued' | 'sending' | 'sent' | 'delivered' | 'failed' | 'suppressed'; }

type MediaAssetId = Branded<string, 'MediaAssetId'>;
interface MediaAsset { id: MediaAssetId; tenantId: TenantId; ownerUserId: UserId; kind: 'image' | 'audio' | 'video' | 'document' | 'subtitle' | 'ai_image' | 'ai_audio'; source: 'upload' | 'ai_generated' | 'imported'; storage: { bucket: string; key: string; sizeBytes: number; sha256: SHA256 }; mime: string; status: 'uploading' | 'scanning' | 'transcoding' | 'ready' | 'failed' | 'quarantined'; }

interface SearchableDocument { id: string; tenantId: TenantId; type: 'course' | 'lesson' | 'block' | 'listing' | 'user' | 'assignment' | 'certificate'; title: I18nString; body: I18nMarkup; tags: string[]; taxonomy: string[]; visibility: 'private' | 'org' | 'marketplace' | 'public'; embedding?: number[]; embeddingModelId?: string; updatedAt: ISODate; }

interface AnalyticsEvent { tenantId: TenantId; eventName: string; properties: Record<string, JSONValue>; actor: { type: string; id: string }; occurredAt: ISODate; }

12. AI Gateway Schemas

type PromptId = Branded<string, 'PromptId'>;
type ModelId = Branded<string, 'ModelId'>;
type CompletionId = Branded<string, 'CompletionId'>;
type EmbeddingId = Branded<string, 'EmbeddingId'>;

interface Prompt { id: PromptId; tenantId: TenantId | null; name: string; version: SemVer; template: string; inputSchema: JSONSchema; outputSchema?: JSONSchema; modelPreference: ModelPreference; safetyPolicy: SafetyPolicy; evalSetRef?: string; status: 'draft' | 'active' | 'deprecated'; createdBy: UserId; }

interface Model { id: ModelId; family: 'chat' | 'embedding' | 'image' | 'tts' | 'stt' | 'moderation' | 'classifier'; vendor: string; contextWindow?: number; costPer1KIn: number; costPer1KOut: number; capabilities: string[]; status: 'available' | 'deprecated'; locality: 'local' | 'cloud'; }

interface AICompletion { id: CompletionId; tenantId: TenantId; userId: UserId; promptId?: PromptId; promptHash: string; modelId: ModelId; inputTokens: number; outputTokens: number; costMicroUSD: number; latencyMs: number; output: JSONValue; safety: { input: SafetyVerdict; output: SafetyVerdict }; cacheHit: boolean; traceId: string; startedAt: ISODate; finishedAt: ISODate; decisionId?: string; }

interface Embedding { id: EmbeddingId; tenantId: TenantId; modelId: ModelId; vector: number[]; sourceRef: { kind: 'block' | 'lesson' | 'listing' | 'document'; id: string }; createdAt: ISODate; }

interface AIBudget { tenantId: TenantId; period: 'day' | 'month'; limitMicroUSD: number; usedMicroUSD: number; resetAt: ISODate; }

interface SafetyPolicy { categories: Record<'sexual'|'violence'|'hate'|'self_harm'|'illegal', 'block' | 'warn' | 'allow'>; piiRedaction: 'block' | 'redact' | 'allow'; promptInjectionPolicy: 'shield' | 'detect' | 'allow'; }

interface SafetyVerdict { categories: Record<string, { score: number; action: 'allow' | 'warn' | 'block' }>; promptInjectionScore: number; piiFound: PIIFinding[]; }

13. Sync Schemas (server-side)

interface SyncRegistration { service: string; entityType: string; conflictPolicy: 'append_only' | 'crdt_yjs' | 'lww' | 'server_authoritative'; deltaProjector: string; pushHandler: string; versionField: string; schemaRef: string; }

interface SyncCursor { tenantId: TenantId; userId: UserId; deviceId: DeviceId; scope: string; lamport: number; updatedAt: ISODate; }

interface LocalMutation { clientMutationId: string; tenantId: TenantId; userId: UserId; deviceId: DeviceId; service: string; entityType: string; entityId: string; baseVersion?: number; vectorClock: VectorClock; op: 'create' | 'update' | 'delete' | 'crdt_update'; payload: JSONValue; occurredAt: ISODate; attempts: number; state: 'queued' | 'inflight' | 'applied' | 'conflicted' | 'rejected'; lastError?: { code: string; message: string }; }

interface ConflictRecord { id: ULID; tenantId: TenantId; userId: UserId; entityType: string; entityId: string; baseVersion: number; serverVersion: number; clientPayload: JSONValue; serverPayload: JSONValue; resolution: 'pending' | 'kept_server' | 'kept_client' | 'merged'; resolvedBy?: UserId; resolvedAt?: ISODate; }

14. Client Offline Schemas (IndexedDB / SQLite)

Same logical structure on web (Dexie) and mobile (SQLite). Tables:

Table	Columns
device	deviceId (pk), userId, tenantId, publicKey, boundAt
cursor	(tenantId, userId, deviceId, scope) (pk), lamport, updatedAt
mutations	clientMutationId (pk), tenantId, userId, service, entityType, entityId, op, baseVersion, vectorClock, payload (blob/json), occurredAt, state, attempts, lastError
entities	(entityType, entityId) (pk), tenantId, data (blob/json), version, updatedAt
conflicts	id (pk), entityType, entityId, serverPayload, clientPayload, state, createdAt
bundles	bundleId (pk), playPackageId, tenantId, licenseEnvelope (json), mountedAt, expiresAt, sha256, pinned (bool)
bundle_blobs	(bundleId, assetId) (pk), data (blob) (encrypted)
statements_outbox	id (pk), attemptId, payload (json), createdAt, pushedAt?
assistant_turns	id (pk), playSessionId, payload (json), aiProvenance (json), createdAt, pushedAt?
offline_certs	id (pk), enrollmentId, payload (json), signature, createdAt, verifiedAt?
notifications_inbox	id (pk), read, readAt?, payload (json)
kv	key (pk), value
ai_cache	(promptHash, modelId) (pk), output (blob), expiresAt, provenance (json)

Encryption:

• bundle_blobs.data AES-256-GCM with per-device-derived key.
• mutations.payload for sensitive payloads encrypted with per-tenant key.
• WebAuthn / biometric required to unlock IndexedDB on iOS/Android per tenant policy.

15. Indexing & Performance

• All *_id columns indexed; composite indexes per service doc.
• Postgres partitioning on progress.statements, analytics.events, audit (monthly + tenant).
• Vector indexes (pgvector) HNSW per tenant collection.
• OpenSearch index alias per tenant for largest customers.

16. Data Retention & Archival

Domain	Hot retention	Cold archive	Notes
identity audit	18 mo	7 y	Compliance
progress.statements	24 mo	7 y	LRS audit
ai_gateway.audit	18 mo	7 y	EU AI Act
billing	7 y	10 y	Tax/finance
certification	indefinite	indefinite	Verifiability
analytics events	13 mo	5 y	Sliding window
sync.mutations	30 d	n/a	Operational
media	tenant-defined	tenant-defined	Per-asset retention setting

GDPR erasure overrides retention except where legal hold (e.g., billing tax) applies.

17. Migration Strategy

• Each service ships migrations (Liquibase or sql-only). CI applies them against ephemeral Postgres.
• Backward-compatible migrations only between minor versions; breaking migrations gated by major version + dual-read window.
• Read-models can be rebuilt from the event log; never depend on direct DB migration.

18. Testing

• Migration tests: every migration applied + rolled back on a snapshot.
• Integrity tests: RLS isolation, FK invariants, RRULE expansion correctness, cmi5 conformance, JWS signature roundtrip.
• Schema-vs-event tests: event payloads validated against schema registry in CI.
• Property tests: vector clocks, conflict policies.

19. Why

Strong typing across services + a single TS source of truth per aggregate keeps producers and consumers aligned. Modeling client-side schemas explicitly makes offline-first concrete (rather than aspirational). AI artifacts and sync entities live alongside business aggregates in this doc to make their first-class status undeniable.