Data Model

:::info Source Sourced from services/enrollment-service/DATA_MODEL.md in the documentation repo. :::

1. Database

Postgres schema enrollment.

2. Tables

2.1 enrollments

CREATE TABLE enrollment.enrollments (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  user_id ULID NOT NULL,
  course_id ULID NOT NULL,
  course_version_id ULID NOT NULL,
  source_kind TEXT NOT NULL CHECK (source_kind IN ('assignment','purchase','manual','self_signup')),
  source_ref TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('active','completed','expired','revoked')),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  last_accessed_at TIMESTAMPTZ,
  attempt_counter INT NOT NULL DEFAULT 0,
  metadata JSONB,
  UNIQUE (tenant_id, user_id, course_id, source_kind, source_ref)
);
CREATE INDEX enr_by_user ON enrollment.enrollments (tenant_id, user_id, state);
CREATE INDEX enr_by_course ON enrollment.enrollments (tenant_id, course_id);
CREATE INDEX enr_expiring ON enrollment.enrollments (expires_at) WHERE state = 'active' AND expires_at IS NOT NULL;
CREATE INDEX enr_active ON enrollment.enrollments (tenant_id, user_id, course_version_id) WHERE state = 'active';

2.2 seats (for org-scope license tracking)

CREATE TABLE enrollment.seats (
  license_id ULID NOT NULL,
  user_id ULID NOT NULL,
  tenant_id UUID NOT NULL,
  consumed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (license_id, user_id)
);

2.3 outbox, inbox

Standard.

3. RLS

Enforced on all tenant-scoped tables.

4. Indexing

• (tenant_id, user_id, state) for learner's active enrollments.
• Partial index on expires_at for expiration scheduler.
• (tenant_id, user_id, course_version_id) where active — for ABAC check.

5. Retention

• Active + recent (< 2 years): hot.
• Terminal (older): cold archive.
• GDPR erasure: user data removed; aggregate audit retained.

6. Migration

Additive only. Source kinds enum additive.

7. Performance

• Bulk insert for admin batch (COPY protocol).
• Scheduler queries use partial index.
• Read-heavy: replica for UI queries.