Data Model

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

Companion: DOMAIN_MODEL · 12 Data Models · 13 Security Compliance Tenancy

1. Storage Choice

Primary store: PostgreSQL 16 (Aurora-compatible in prod).
Row-Level Security (RLS): enabled on every tenant-scoped table.
Partitioning: compliance_window is LIST-partitioned by tenant_id and RANGE sub-partitioned by occurrence_start (monthly).
JSONB: used sparingly for structured-but-flexible fields (targets, escalation, reminder_policy).
Redis: L1 target-resolver cache and dynamic-group snapshots (TTL 5 min).
No NoSQL.

2. Schemas

2.1 `assignment`

CREATE TABLE assignment (
  id                      TEXT        PRIMARY KEY,        -- "asn_" + ULID
  tenant_id               TEXT        NOT NULL,
  created_by              TEXT        NOT NULL,
  title                   JSONB       NOT NULL,           -- I18nString
  description             JSONB,
  course_id               TEXT        NOT NULL,
  course_version_policy   TEXT        NOT NULL CHECK (course_version_policy IN ('pin','latest')),
  pinned_version_id       TEXT,
  targets                 JSONB       NOT NULL,           -- AssignmentTarget[]
  rrule                   TEXT,                            -- RFC 5545, null=one-shot
  start_date              DATE        NOT NULL,
  due_offset              INTERVAL    NOT NULL,
  grace_period            INTERVAL    NOT NULL,
  escalation              JSONB       NOT NULL,
  reminder_policy         JSONB       NOT NULL,
  state                   TEXT        NOT NULL CHECK (state IN ('draft','active','paused','archived')),
  ai_suggested            BOOLEAN     NOT NULL DEFAULT false,
  ai_provenance           JSONB,
  version                 INTEGER     NOT NULL DEFAULT 1, -- optimistic lock
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  activated_at            TIMESTAMPTZ,
  archived_at             TIMESTAMPTZ,
  deleted_at              TIMESTAMPTZ
);

CREATE INDEX idx_assignment_tenant_state     ON assignment (tenant_id, state);
CREATE INDEX idx_assignment_tenant_course    ON assignment (tenant_id, course_id);
CREATE INDEX idx_assignment_active_rrule     ON assignment (tenant_id) WHERE state='active' AND rrule IS NOT NULL;

ALTER TABLE assignment ENABLE ROW LEVEL SECURITY;
CREATE POLICY assignment_tenant_isolation ON assignment
  USING (tenant_id = current_setting('ghasi.tenant_id', true));

2.2 `compliance_window`

CREATE TABLE compliance_window (
  id                 TEXT        NOT NULL,        -- "win_" + ULID
  tenant_id          TEXT        NOT NULL,
  assignment_id      TEXT        NOT NULL REFERENCES assignment(id) ON DELETE CASCADE,
  user_id            TEXT        NOT NULL,
  occurrence_start   DATE        NOT NULL,
  due_at             TIMESTAMPTZ NOT NULL,
  grace_until        TIMESTAMPTZ NOT NULL,
  resolved_version_id TEXT       NOT NULL,
  state              TEXT        NOT NULL CHECK (state IN ('open','in_progress','completed','overdue','closed_missed')),
  enrollment_id      TEXT,
  completed_at       TIMESTAMPTZ,
  overdue_at         TIMESTAMPTZ,
  closed_at          TIMESTAMPTZ,
  escalation_level   INTEGER     NOT NULL DEFAULT 0,
  reminders_sent     INTEGER     NOT NULL DEFAULT 0,
  last_reminder_at   TIMESTAMPTZ,
  version            INTEGER     NOT NULL DEFAULT 1,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

-- Per-tenant sub-partitioning by month:
-- CREATE TABLE compliance_window_tnt_acme PARTITION OF compliance_window
--   FOR VALUES IN ('tnt_acme') PARTITION BY RANGE (occurrence_start);

CREATE UNIQUE INDEX ux_window_dedup
  ON compliance_window (tenant_id, assignment_id, user_id, occurrence_start);

CREATE INDEX idx_window_state_due
  ON compliance_window (tenant_id, state, due_at) INCLUDE (assignment_id, user_id);

CREATE INDEX idx_window_state_grace
  ON compliance_window (tenant_id, state, grace_until);

CREATE INDEX idx_window_user
  ON compliance_window (tenant_id, user_id, state);

CREATE INDEX idx_window_enrollment
  ON compliance_window (tenant_id, enrollment_id) WHERE enrollment_id IS NOT NULL;

ALTER TABLE compliance_window ENABLE ROW LEVEL SECURITY;
CREATE POLICY window_tenant_isolation ON compliance_window
  USING (tenant_id = current_setting('ghasi.tenant_id', true));

2.3 `escalation_log`

Audit trail + idempotency for fired escalations.

CREATE TABLE escalation_log (
  id              BIGSERIAL    PRIMARY KEY,
  tenant_id       TEXT         NOT NULL,
  window_id       TEXT         NOT NULL,
  assignment_id   TEXT         NOT NULL,
  level           INTEGER      NOT NULL,
  action_hash     TEXT         NOT NULL,   -- sha256 of action JSON
  fired_at        TIMESTAMPTZ  NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, window_id, level, action_hash)
);
CREATE INDEX idx_escalation_tenant_window ON escalation_log (tenant_id, window_id);

2.4 `reminder_log`

CREATE TABLE reminder_log (
  id               BIGSERIAL   PRIMARY KEY,
  tenant_id        TEXT        NOT NULL,
  window_id        TEXT        NOT NULL,
  trigger_hash     TEXT        NOT NULL,
  sent_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, window_id, trigger_hash)
);

2.5 `outbox`

CREATE TABLE outbox (
  id             BIGSERIAL   PRIMARY KEY,
  tenant_id      TEXT        NOT NULL,
  subject        TEXT        NOT NULL,
  payload        JSONB       NOT NULL,
  headers        JSONB       NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at   TIMESTAMPTZ
);
CREATE INDEX idx_outbox_unpublished ON outbox (created_at) WHERE published_at IS NULL;
CREATE INDEX idx_outbox_tenant       ON outbox (tenant_id, created_at);

2.6 `idempotency`

CREATE TABLE idempotency (
  tenant_id       TEXT        NOT NULL,
  key             TEXT        NOT NULL,
  response_body   JSONB,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (tenant_id, key)
);
CREATE INDEX idx_idempotency_expiry ON idempotency (expires_at);

2.7 `materialization_cursor`

Tracks how far we've materialised each active assignment.

CREATE TABLE materialization_cursor (
  tenant_id         TEXT        NOT NULL,
  assignment_id     TEXT        NOT NULL,
  materialized_until DATE       NOT NULL,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, assignment_id)
);

2.8 `target_membership_snapshot`

Immutable snapshot of resolved users at activation; used for audit and delta computation.

CREATE TABLE target_membership_snapshot (
  tenant_id      TEXT        NOT NULL,
  assignment_id  TEXT        NOT NULL,
  snapshot_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  member_ids     TEXT[]      NOT NULL,
  PRIMARY KEY (tenant_id, assignment_id, snapshot_at)
);
CREATE INDEX idx_snap_member_ids ON target_membership_snapshot USING GIN (member_ids);

3. Entity-Relationship Diagram

┌───────────────────┐        ┌──────────────────────────┐
│    assignment     │1──────*│    compliance_window     │
│───────────────────│        │──────────────────────────│
│ id (PK)           │        │ (tenant_id,id) (PK)      │
│ tenant_id         │        │ assignment_id (FK)       │
│ state             │        │ user_id                  │
│ rrule             │        │ state                    │
│ targets (jsonb)   │        │ due_at, grace_until      │
└───────────────────┘        └──────────────────────────┘
        │                                │
        │                                │
        ▼                                ▼
┌────────────────────┐         ┌─────────────────────┐
│ material_cursor    │         │ escalation_log      │
└────────────────────┘         └─────────────────────┘
                                       │
                                       ▼
                                ┌──────────────┐
                                │ reminder_log │
                                └──────────────┘

      All tables → outbox (event publication audit)

4. Sizing & Growth

Table	Rows / tenant / year (p95)	Notes
`assignment`	500	small
`compliance_window`	5,000,000	10k users × 500 annual/recurring assignments
`escalation_log`	200,000	per fire
`reminder_log`	30,000,000	5 reminders per window avg
`outbox`	10x event volume	pruned hourly

Retention:

outbox pruned 7 days after publish.
reminder_log retained 18 months.
escalation_log retained 7 years (regulatory).
compliance_window retained indefinitely (auditor requirement) but cold partitions moved to cheap storage via pg_partman.

5. Migrations

Tool: drizzle-kit + custom tenant-partition provisioner.
Up-only migrations in migrations/YYYYMMDD_HHMM_<slug>.sql.
Schema drift check in CI.

6. Query Patterns (hot)

Query	Index used
List open/in_progress windows past due	`idx_window_state_due` (partial)
List overdue windows past grace	`idx_window_state_grace`
Learner's own windows	`idx_window_user`
Window by enrollment	`idx_window_enrollment`
Active recurring assignments	`idx_assignment_active_rrule`

7. Consistency & Transactions

All state transitions run in a single tx that updates the aggregate + writes outbox event.
Materialization is chunked (1k windows per tx) to bound lock scope.
No cross-aggregate transactions.

8. Backups & PITR

Daily full snapshot.
WAL PITR with 14-day retention.
pg_dump --schema-only snapshots committed to ops repo.

9. Encryption

At rest: AES-256 (managed by RDS/Aurora).
Column-level: ai_provenance masked in read projections when tenant policy prohibits AI disclosure.
In transit: TLS 1.3 only.

1. Storage Choice​

2. Schemas​

2.1 assignment​

2.2 compliance_window​

2.3 escalation_log​

2.4 reminder_log​

2.5 outbox​

2.6 idempotency​

2.7 materialization_cursor​

2.8 target_membership_snapshot​

3. Entity-Relationship Diagram​

4. Sizing & Growth​

5. Migrations​

6. Query Patterns (hot)​

7. Consistency & Transactions​

8. Backups & PITR​

9. Encryption​