Data Model

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

Companion: 02 Domain Model · 12 Data Models · 13 Security & Tenancy

1. Database Overview

Attribute	Value
Engine	PostgreSQL 16
Schema	`authoring`
Connection pool	PgBouncer (transaction mode), max 50 per instance
Migrations	Drizzle Kit, versioned sequentially
Tenant isolation	Row-Level Security (RLS) on every table
Session config	`SET app.tenant_id = '...'` per request
Extensions	`pg_trgm`, `btree_gin`, `pgcrypto`

2. Schema Overview

┌────────────────────────────────────────────────────────────────┐
│                     authoring (schema)                         │
├────────────────────────────────────────────────────────────────┤
│ course_drafts ◄───┐                                            │
│      │            │                                            │
│      │            │                                            │
│      ▼            │                                            │
│ module_drafts     │                                            │
│      │            │                                            │
│      ▼            │                                            │
│ lesson_drafts     │                                            │
│      │            │                                            │
│      ▼            │                                            │
│ blocks ───► block_versions                                     │
│                                                                 │
│ collaboration_sessions ──► yjs_state                            │
│ publish_sagas                                                   │
│ ai_jobs                                                         │
│ draft_collaborators                                             │
│ outbox                                                          │
│ event_inbox                                                     │
│ idempotency_keys                                                │
│ scorm_imports                                                   │
└────────────────────────────────────────────────────────────────┘

3. Table: `course_drafts`

CREATE TABLE authoring.course_drafts (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  published_course_id   uuid,
  title                 jsonb NOT NULL,              -- I18nString
  default_locale        text NOT NULL,
  state                 text NOT NULL DEFAULT 'editing'
                          CHECK (state IN ('editing','in_review','approved','publishing','published_idle')),
  draft_version         bigint NOT NULL DEFAULT 1,
  settings              jsonb NOT NULL DEFAULT '{}',
  created_by            uuid NOT NULL,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  deleted_at            timestamptz,                  -- soft delete
  CONSTRAINT chk_title_has_default CHECK (title ? default_locale)
);

CREATE INDEX idx_drafts_tenant_state ON authoring.course_drafts (tenant_id, state) WHERE deleted_at IS NULL;
CREATE INDEX idx_drafts_tenant_updated ON authoring.course_drafts (tenant_id, updated_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_drafts_published_course ON authoring.course_drafts (published_course_id) WHERE published_course_id IS NOT NULL;

ALTER TABLE authoring.course_drafts ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_drafts_tenant ON authoring.course_drafts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

4. Table: `module_drafts`

CREATE TABLE authoring.module_drafts (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  title                 jsonb NOT NULL,
  sort_order            integer NOT NULL,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE (course_draft_id, sort_order) DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idx_modules_draft ON authoring.module_drafts (course_draft_id, sort_order);

ALTER TABLE authoring.module_drafts ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_modules_tenant ON authoring.module_drafts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

5. Table: `lesson_drafts`

CREATE TABLE authoring.lesson_drafts (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  module_draft_id       uuid NOT NULL REFERENCES authoring.module_drafts(id) ON DELETE CASCADE,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  title                 jsonb NOT NULL,
  sort_order            integer NOT NULL,
  estimated_minutes     integer,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE (module_draft_id, sort_order) DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idx_lessons_module ON authoring.lesson_drafts (module_draft_id, sort_order);
CREATE INDEX idx_lessons_draft ON authoring.lesson_drafts (course_draft_id);

ALTER TABLE authoring.lesson_drafts ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_lessons_tenant ON authoring.lesson_drafts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

6. Table: `blocks`

CREATE TABLE authoring.blocks (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  lesson_draft_id       uuid NOT NULL REFERENCES authoring.lesson_drafts(id) ON DELETE CASCADE,
  kind                  text NOT NULL
                          CHECK (kind IN (
                            'text','heading','list','callout','divider',
                            'image','image_grid','video','audio','embed','code_snippet',
                            'quiz','branching','hotspot','drag_drop','sortable',
                            'click_reveal','flashcards','accordion','tabs',
                            'timeline','gallery','button','downloadable_attachment',
                            'interaction','ai'
                          )),
  status                text NOT NULL DEFAULT 'draft'
                          CHECK (status IN ('draft','draft_ai','reviewed','published')),
  sort_order            integer NOT NULL,
  required              boolean NOT NULL DEFAULT false,
  data                  jsonb NOT NULL,           -- kind-specific payload
  ai_provenance         jsonb,                    -- AIProvenance VO
  reviewed_by           uuid,
  reviewed_at           timestamptz,
  created_by            uuid NOT NULL,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE (lesson_draft_id, sort_order) DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT chk_ai_provenance CHECK (
    (status = 'draft_ai' AND ai_provenance IS NOT NULL)
    OR (status <> 'draft_ai')
  ),
  CONSTRAINT chk_ai_not_required CHECK (
    NOT (status = 'draft_ai' AND required = true)
  )
);

CREATE INDEX idx_blocks_lesson ON authoring.blocks (lesson_draft_id, sort_order);
CREATE INDEX idx_blocks_draft ON authoring.blocks (course_draft_id);
CREATE INDEX idx_blocks_status ON authoring.blocks (course_draft_id, status);
CREATE INDEX idx_blocks_ai_pending ON authoring.blocks (course_draft_id) WHERE status = 'draft_ai';

-- GIN index on jsonb data for search/filter
CREATE INDEX idx_blocks_data ON authoring.blocks USING GIN (data jsonb_path_ops);

ALTER TABLE authoring.blocks ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_blocks_tenant ON authoring.blocks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

7. Table: `block_versions`

Immutable audit history of every block change.

CREATE TABLE authoring.block_versions (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  block_id              uuid NOT NULL,
  course_draft_id       uuid NOT NULL,
  version_number        integer NOT NULL,
  snapshot              jsonb NOT NULL,          -- full block state at version
  change_type           text NOT NULL
                          CHECK (change_type IN ('created','updated','ai_generated','reviewed','deleted')),
  changed_by            uuid NOT NULL,
  changed_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE (block_id, version_number)
);

CREATE INDEX idx_block_versions_block ON authoring.block_versions (block_id, version_number DESC);
CREATE INDEX idx_block_versions_draft ON authoring.block_versions (course_draft_id, changed_at DESC);

ALTER TABLE authoring.block_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_block_versions_tenant ON authoring.block_versions
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

8. Table: `draft_collaborators`

CREATE TABLE authoring.draft_collaborators (
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  tenant_id             uuid NOT NULL,
  user_id               uuid NOT NULL,
  role                  text NOT NULL DEFAULT 'editor'
                          CHECK (role IN ('owner','editor','reviewer','viewer')),
  added_at              timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (course_draft_id, user_id)
);

CREATE INDEX idx_collab_user ON authoring.draft_collaborators (tenant_id, user_id);

ALTER TABLE authoring.draft_collaborators ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_collab_tenant ON authoring.draft_collaborators
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

9. Table: `collaboration_sessions`

CREATE TABLE authoring.collaboration_sessions (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  started_at            timestamptz NOT NULL DEFAULT now(),
  last_activity_at      timestamptz NOT NULL DEFAULT now(),
  ended_at              timestamptz
);

CREATE INDEX idx_sessions_draft ON authoring.collaboration_sessions (course_draft_id) WHERE ended_at IS NULL;

ALTER TABLE authoring.collaboration_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_sessions_tenant ON authoring.collaboration_sessions
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

10. Table: `yjs_state`

Stores Yjs binary update log per draft. M4+ only.

CREATE TABLE authoring.yjs_state (
  course_draft_id       uuid PRIMARY KEY REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  tenant_id             uuid NOT NULL,
  snapshot              bytea NOT NULL,           -- Y.encodeStateAsUpdate
  snapshot_version      bigint NOT NULL,
  updates               bytea,                    -- concatenated updates since snapshot
  update_count          integer NOT NULL DEFAULT 0,
  last_updated_at       timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE authoring.yjs_state ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_yjs_tenant ON authoring.yjs_state
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Compaction policy: when update_count > 500, merge updates into snapshot and reset.

11. Table: `publish_sagas`

CREATE TABLE authoring.publish_sagas (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id),
  current_step          text NOT NULL
                          CHECK (current_step IN ('building','cataloging','bundling','ready','failed','compensating')),
  started_at            timestamptz NOT NULL DEFAULT now(),
  completed_at          timestamptz,
  timeout_at            timestamptz NOT NULL,
  failure_reason        text,
  completed_steps       jsonb NOT NULL DEFAULT '[]',     -- SagaStepRecord[]
  triggering_user_id    uuid NOT NULL,
  UNIQUE (course_draft_id) DEFERRABLE INITIALLY DEFERRED   -- only one active saga per draft
);

CREATE INDEX idx_sagas_timeout ON authoring.publish_sagas (timeout_at) WHERE completed_at IS NULL;
CREATE INDEX idx_sagas_step ON authoring.publish_sagas (current_step) WHERE completed_at IS NULL;

ALTER TABLE authoring.publish_sagas ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_sagas_tenant ON authoring.publish_sagas
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

12. Table: `ai_jobs`

CREATE TABLE authoring.ai_jobs (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  lesson_id             uuid,
  block_id              uuid,
  job_type              text NOT NULL
                          CHECK (job_type IN ('generate_block','improve_block','generate_quiz','translate','summarize')),
  status                text NOT NULL DEFAULT 'queued'
                          CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
  prompt_id             text NOT NULL,
  prompt_version        text NOT NULL,
  ai_gateway_job_id     text,                    -- correlation with ai-gateway-service
  input                 jsonb NOT NULL,
  result                jsonb,
  error                 text,
  requested_by          uuid NOT NULL,
  requested_at          timestamptz NOT NULL DEFAULT now(),
  started_at            timestamptz,
  completed_at          timestamptz
);

CREATE INDEX idx_ai_jobs_draft ON authoring.ai_jobs (course_draft_id, requested_at DESC);
CREATE INDEX idx_ai_jobs_status ON authoring.ai_jobs (status) WHERE status IN ('queued','running');
CREATE INDEX idx_ai_jobs_gateway ON authoring.ai_jobs (ai_gateway_job_id) WHERE ai_gateway_job_id IS NOT NULL;

ALTER TABLE authoring.ai_jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_ai_jobs_tenant ON authoring.ai_jobs
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

13. Table: `scorm_imports`

CREATE TABLE authoring.scorm_imports (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL,
  course_draft_id       uuid NOT NULL REFERENCES authoring.course_drafts(id) ON DELETE CASCADE,
  source_url            text NOT NULL,
  scorm_version         text NOT NULL CHECK (scorm_version IN ('1.2','2004')),
  status                text NOT NULL DEFAULT 'pending'
                          CHECK (status IN ('pending','parsing','mapping','succeeded','failed')),
  manifest              jsonb,
  warnings              jsonb NOT NULL DEFAULT '[]',
  error                 text,
  requested_by          uuid NOT NULL,
  requested_at          timestamptz NOT NULL DEFAULT now(),
  completed_at          timestamptz
);

CREATE INDEX idx_scorm_draft ON authoring.scorm_imports (course_draft_id);

ALTER TABLE authoring.scorm_imports ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_scorm_tenant ON authoring.scorm_imports
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

14. Table: `outbox`

(See 05-EVENT_SCHEMAS.md §6.)

15. Table: `event_inbox`

(See 05-EVENT_SCHEMAS.md §7.)

16. Table: `idempotency_keys`

CREATE TABLE authoring.idempotency_keys (
  key                   text PRIMARY KEY,
  tenant_id             uuid NOT NULL,
  user_id               uuid NOT NULL,
  request_hash          text NOT NULL,
  response_status       integer NOT NULL,
  response_body         jsonb NOT NULL,
  created_at            timestamptz NOT NULL DEFAULT now(),
  expires_at            timestamptz NOT NULL DEFAULT (now() + interval '24 hours')
);

CREATE INDEX idx_idempotency_expires ON authoring.idempotency_keys (expires_at);

Cleanup job runs hourly: DELETE FROM idempotency_keys WHERE expires_at < now().

17. Views (Read Projections)

17.1 `v_draft_summary`

CREATE VIEW authoring.v_draft_summary AS
SELECT
  d.id,
  d.tenant_id,
  d.title,
  d.state,
  d.default_locale,
  d.draft_version,
  d.created_by,
  d.created_at,
  d.updated_at,
  COUNT(DISTINCT m.id) AS module_count,
  COUNT(DISTINCT l.id) AS lesson_count,
  COUNT(DISTINCT b.id) AS block_count,
  COUNT(DISTINCT b.id) FILTER (WHERE b.status = 'draft_ai') AS pending_ai_blocks,
  COUNT(DISTINCT dc.user_id) AS collaborator_count
FROM authoring.course_drafts d
LEFT JOIN authoring.module_drafts m ON m.course_draft_id = d.id
LEFT JOIN authoring.lesson_drafts l ON l.course_draft_id = d.id
LEFT JOIN authoring.blocks b ON b.course_draft_id = d.id
LEFT JOIN authoring.draft_collaborators dc ON dc.course_draft_id = d.id
WHERE d.deleted_at IS NULL
GROUP BY d.id;

18. Indexes Summary

Table	Index	Purpose
course_drafts	`(tenant_id, state)`	Filter by state in lists
course_drafts	`(tenant_id, updated_at DESC)`	Recent-first listings
blocks	`(lesson_draft_id, sort_order)`	Ordered block retrieval
blocks	`(course_draft_id, status)`	Status filtering
blocks (partial)	`(course_draft_id)` where status='draft_ai'	Pending AI blocks
blocks (GIN)	`data jsonb_path_ops`	JSON payload search
publish_sagas	`(timeout_at)` where completed_at IS NULL	Timeout poller
ai_jobs	`(status)` where status IN (queued,running)	Active jobs scan
outbox	`(occurred_at)` where published_at IS NULL	Outbox relay

19. Storage Estimates

Table	Rows per 1000 drafts	Disk
course_drafts	1,000	~1 MB
module_drafts	5,000	~2 MB
lesson_drafts	25,000	~8 MB
blocks	250,000	~500 MB (average block data ~2 KB)
block_versions	2,500,000 (10 versions avg)	~5 GB
outbox	500,000 (cleared as published)	~200 MB steady
yjs_state	1,000	~100 MB (100 KB avg per draft)

20. Retention & Archival

Table	Hot retention	Archive
course_drafts	Indefinite (active tenants)	7y after tenant closure
block_versions	2 years	S3 parquet after 2y
outbox	Until published	Deleted 48h after published
event_inbox	90 days	Deleted
idempotency_keys	24 hours	Deleted
ai_jobs	90 days	S3 parquet
publish_sagas	180 days	S3 parquet

21. Backup Strategy

Daily logical backups (pg_dump) + PITR via WAL archiving
Per-tenant export available via compliance API
Backup restore tested quarterly in staging

1. Database Overview​

2. Schema Overview​

3. Table: course_drafts​

4. Table: module_drafts​

5. Table: lesson_drafts​

6. Table: blocks​

7. Table: block_versions​

8. Table: draft_collaborators​

9. Table: collaboration_sessions​

10. Table: yjs_state​

11. Table: publish_sagas​

12. Table: ai_jobs​

13. Table: scorm_imports​

14. Table: outbox​

15. Table: event_inbox​

16. Table: idempotency_keys​

17. Views (Read Projections)​

17.1 v_draft_summary​

18. Indexes Summary​

19. Storage Estimates​

20. Retention & Archival​

21. Backup Strategy​