Data Model

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

Companion: 12 Data Models · DOMAIN_MODEL · 04 Event-Driven

1. Database

Engine: PostgreSQL 16+
Schema: delivery
RLS: Enabled on all tables via tenant_id = current_setting('app.tenant_id')::uuid
Connection pooling: PgBouncer, app.tenant_id set per-transaction via proxy-init

2. Tables

2.1 play_sessions

CREATE TABLE delivery.play_sessions (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  user_id         uuid NOT NULL,
  enrollment_id   uuid NOT NULL,
  course_version_id uuid NOT NULL,
  device_id       uuid NOT NULL,
  attempt_number  int NOT NULL,
  state           text NOT NULL DEFAULT 'init'
                  CHECK (state IN ('init','active','paused','completed','abandoned')),
  cursor_module_id text NOT NULL,
  cursor_lesson_id text NOT NULL,
  cursor_block_id  text,
  cursor_branch_path text[],
  cursor_sequence_index int,
  started_at      timestamptz NOT NULL DEFAULT now(),
  last_activity_at timestamptz NOT NULL DEFAULT now(),
  ended_at        timestamptz,
  offline_mount_id uuid,
  version         int NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_play_sessions_user_course_state
  ON delivery.play_sessions (user_id, course_version_id, state)
  WHERE state IN ('active', 'paused');

CREATE INDEX idx_play_sessions_enrollment
  ON delivery.play_sessions (enrollment_id, started_at DESC);

CREATE INDEX idx_play_sessions_tenant_state
  ON delivery.play_sessions (tenant_id, state, last_activity_at);

CREATE UNIQUE INDEX idx_play_sessions_active_unique
  ON delivery.play_sessions (user_id, course_version_id, device_id)
  WHERE state = 'active';

-- RLS
ALTER TABLE delivery.play_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON delivery.play_sessions
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

2.2 assistant_turns

CREATE TABLE delivery.assistant_turns (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  session_id      uuid NOT NULL REFERENCES delivery.play_sessions(id),
  turn_id         text NOT NULL,
  prompt          text NOT NULL,
  context_lesson_id text NOT NULL,
  context_block_ids text[],
  response        text,
  tool_calls      jsonb,
  ai_provenance   jsonb NOT NULL,
  started_at      timestamptz NOT NULL DEFAULT now(),
  finished_at     timestamptz,
  rating          text CHECK (rating IN ('helpful', 'unhelpful')),
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_assistant_turns_session
  ON delivery.assistant_turns (session_id, started_at DESC);

ALTER TABLE delivery.assistant_turns ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON delivery.assistant_turns
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

2.3 offline_mounts

CREATE TABLE delivery.offline_mounts (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  user_id         uuid NOT NULL,
  device_id       uuid NOT NULL,
  bundle_id       uuid NOT NULL,
  course_version_id uuid NOT NULL,
  mounted_at      timestamptz NOT NULL DEFAULT now(),
  expires_at      timestamptz NOT NULL,
  signature_valid boolean NOT NULL DEFAULT true,
  unmounted_at    timestamptz,
  unmount_reason  text CHECK (unmount_reason IN (
    'user_initiated', 'expired', 'tamper_detected', 'license_revoked'
  )),
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_offline_mounts_device
  ON delivery.offline_mounts (device_id, user_id)
  WHERE unmounted_at IS NULL;

CREATE INDEX idx_offline_mounts_bundle
  ON delivery.offline_mounts (bundle_id)
  WHERE unmounted_at IS NULL;

ALTER TABLE delivery.offline_mounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON delivery.offline_mounts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

2.4 enrollment_projections (read model)

CREATE TABLE delivery.enrollment_projections (
  enrollment_id   uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  user_id         uuid NOT NULL,
  course_version_id uuid NOT NULL,
  status          text NOT NULL DEFAULT 'active',
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE delivery.enrollment_projections ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON delivery.enrollment_projections
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

2.5 bundle_projections (read model)

CREATE TABLE delivery.bundle_projections (
  bundle_id       uuid PRIMARY KEY,
  course_version_id uuid NOT NULL,
  checksum        text NOT NULL,
  size_bytes      bigint NOT NULL,
  available       boolean NOT NULL DEFAULT true,
  published_at    timestamptz NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE delivery.gate_status (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  session_id      uuid NOT NULL REFERENCES delivery.play_sessions(id),
  gate_id         text NOT NULL,
  gate_type       text NOT NULL DEFAULT 'quiz',
  status          text NOT NULL CHECK (status IN ('pending', 'passed', 'failed')),
  scored_at       timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (session_id, gate_id)
);

ALTER TABLE delivery.gate_status ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON delivery.gate_status
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

2.7 outbox

CREATE TABLE delivery.outbox (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  tenant_id     uuid NOT NULL,
  topic         text NOT NULL,
  envelope      jsonb NOT NULL,
  published_at  timestamptz,
  attempts      int NOT NULL DEFAULT 0,
  last_error    text
);

CREATE INDEX outbox_unpublished_idx
  ON delivery.outbox (occurred_at)
  WHERE published_at IS NULL;

2.8 inbox (idempotent consumer)

CREATE TABLE delivery.inbox (
  event_id      uuid PRIMARY KEY,
  event_type    text NOT NULL,
  processed_at  timestamptz NOT NULL DEFAULT now()
);

-- Auto-cleanup: retain 7 days
-- Managed by pg_cron job

3. ERD

┌──────────────────┐       ┌──────────────────┐
│  play_sessions   │──1:N──│ assistant_turns   │
│                  │       │                  │
│  PK: id          │       │  PK: id          │
│  FK: offline_    │       │  FK: session_id  │
│      mount_id    │       └──────────────────┘
└────────┬─────────┘
         │ 0..1
┌────────▼─────────┐       ┌──────────────────┐
│  offline_mounts  │       │  gate_status     │
│                  │       │                  │
│  PK: id          │       │  PK: id          │
└──────────────────┘       │  FK: session_id  │
                           └──────────────────┘
         ┌──────────────────┐
         │enrollment_projections│  (read model, event-sourced)
         └──────────────────┘
         ┌──────────────────┐
         │bundle_projections│  (read model, event-sourced)
         └──────────────────┘

4. Redis Cache Schema

Key Pattern	Value	TTL	Purpose
`delivery:manifest:{courseVersionId}`	PlayPackage manifest JSON	1h	Fast manifest lookup during navigation
`delivery:session:{sessionId}`	Session state JSON	30s	GET /state cache
`delivery:idempotency:{key}`	Response JSON	24h	Idempotency dedup
`delivery:gate:{sessionId}:{gateId}`	Gate status	5min	Quiz-gate check cache
`delivery:ratelimit:session:{userId}`	Counter	1min	Session creation rate limit
`delivery:ratelimit:tutor:{sessionId}`	Counter	1h	Tutor turn rate limit

5. Migration Strategy

Migrations managed by Flyway (or node-pg-migrate), stored in migrations/ directory:

migrations/
  V001__create_play_sessions.sql
  V002__create_assistant_turns.sql
  V003__create_offline_mounts.sql
  V004__create_enrollment_projections.sql
  V005__create_bundle_projections.sql
  V006__create_gate_status.sql
  V007__create_outbox_inbox.sql

All migrations are forward-only. Rollbacks create new forward migrations.

6. Data Retention

Table	Retention	Rationale
`play_sessions`	13 months hot, 7 years cold	Operational + compliance
`assistant_turns`	13 months hot, 7 years cold	AI audit trail
`offline_mounts`	13 months	Operational
`enrollment_projections`	Synced from source	Read model, eventually consistent
`bundle_projections`	Synced from source	Read model
`gate_status`	90 days	Ephemeral read model
`outbox`	7 days after published	Cleanup by pg_cron
`inbox`	7 days	Cleanup by pg_cron

1. Database​

2. Tables​

2.1 play_sessions​

2.2 assistant_turns​

2.3 offline_mounts​

2.4 enrollment_projections (read model)​

2.5 bundle_projections (read model)​

2.6 gate_status (read model for quiz-gated navigation)​

2.7 outbox​

2.8 inbox (idempotent consumer)​

3. ERD​

4. Redis Cache Schema​

5. Migration Strategy​

6. Data Retention​