Data Model

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

Blueprint doc 6 of 17. Companion: 12 Data Models | DOMAIN_MODEL | SECURITY_MODEL

1. Database Topology

Attribute	Value
Engine	PostgreSQL 16
Schema	`tenant`
Extensions	`uuid-ossp`, `ltree`, `pg_trgm`, `pgcrypto`
Isolation	Row-Level Security (RLS) on every table except globally-scoped registries
Connection	PgBouncer (transaction pooling) with per-request `SET LOCAL app.tenant_id`
Replication	1 primary + 2 read replicas per region; logical replication to analytics
Backup	Continuous WAL archival to S3; nightly base backup; PITR 35 days
Encryption	AES-256 at rest via cloud provider KMS; per-tenant KEKs for sensitive columns

2. Entity-Relationship Diagram

                ┌─────────────────┐
                │     tenants     │
                │   (global AR)   │
                └────────┬────────┘
                         │ 1
          ┌──────────────┼──────────────┬──────────────┐
          │ 0..*         │ 0..*         │ 0..*         │ 0..*
          ▼              ▼              ▼              ▼
   ┌──────────────┐ ┌──────────────┐ ┌────────────┐ ┌──────────────┐
   │  org_units   │ │ memberships  │ │   roles    │ │ dynamic_     │
   │  (ltree)     │ │              │ │ (tenant +  │ │   groups     │
   │              │ │              │ │  system)   │ │              │
   └──────┬───────┘ └──────┬───────┘ └─────┬──────┘ └──────────────┘
          │                │                │
          │ *              │ *              │ *
          └────────────────┤                │
                           │ (join tables)  │
           ┌───────────────┴────────────────┴─────────────┐
           │   membership_org_units  |  membership_roles │
           └──────────────────────────────────────────────┘

          ┌─────────────────────┐     ┌────────────────────┐
          │ feature_flag_       │     │  sso_providers     │
          │   overrides         │     │                    │
          └─────────────────────┘     └────────────────────┘

          ┌─────────────────────┐     ┌────────────────────┐
          │     outbox          │     │      inbox         │
          │  (event publishing) │     │ (idempotent        │
          │                     │     │  consumption)      │
          └─────────────────────┘     └────────────────────┘

          ┌─────────────────────┐     ┌────────────────────┐
          │ authz_decisions_    │     │ data_residency_    │
          │   audit             │     │   migrations       │
          └─────────────────────┘     └────────────────────┘

3. Table Definitions

3.1 tenants

CREATE TABLE tenant.tenants (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          text NOT NULL UNIQUE,
  name          text NOT NULL,
  type          text NOT NULL CHECK (type IN ('org','provider','individual','org+provider')),
  home_region   text NOT NULL CHECK (home_region IN ('us','eu','me','ap')),
  status        text NOT NULL CHECK (status IN ('trial','active','suspended','closed')),
  plan_id       text NOT NULL,
  plan_addons   text[] NOT NULL DEFAULT '{}',
  settings      jsonb NOT NULL DEFAULT '{}',
  version       int NOT NULL DEFAULT 1,           -- optimistic locking
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  closed_at     timestamptz,
  suspended_at  timestamptz,
  suspend_reason text
);

CREATE INDEX tenants_status_idx ON tenant.tenants (status);
CREATE INDEX tenants_home_region_idx ON tenant.tenants (home_region);
CREATE INDEX tenants_slug_trgm ON tenant.tenants USING gin (slug gin_trgm_ops);

Note: tenants itself is not RLS-protected — it's the source of truth for tenant-id resolution. Access is controlled at the application layer via platform_admin-only endpoints for listing; individual lookups are by explicit ID.

3.2 org_units

CREATE TABLE tenant.org_units (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id      uuid NOT NULL REFERENCES tenant.tenants(id),
  parent_id      uuid REFERENCES tenant.org_units(id),
  name           jsonb NOT NULL,                  -- I18nString: { "en-US": "...", "ar-SA": "..." }
  ltree_path     ltree NOT NULL,
  depth          smallint NOT NULL,
  version        int NOT NULL DEFAULT 1,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),
  deleted_at     timestamptz
);

CREATE INDEX org_units_tenant_idx ON tenant.org_units (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX org_units_parent_idx ON tenant.org_units (parent_id) WHERE deleted_at IS NULL;
CREATE INDEX org_units_ltree_gist ON tenant.org_units USING gist (ltree_path);
CREATE UNIQUE INDEX org_units_path_unique ON tenant.org_units (tenant_id, ltree_path) WHERE deleted_at IS NULL;

ALTER TABLE tenant.org_units ENABLE ROW LEVEL SECURITY;
CREATE POLICY org_units_tenant_isolation ON tenant.org_units
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.3 memberships

CREATE TABLE tenant.memberships (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenant.tenants(id),
  user_id      uuid NOT NULL,                     -- from identity context
  email        text NOT NULL,                     -- for invite matching before user exists
  status       text NOT NULL CHECK (status IN ('invited','active','suspended')),
  invited_at   timestamptz,
  joined_at    timestamptz,
  suspended_at timestamptz,
  suspend_reason text,
  invited_by   uuid,
  invite_token_hash text,                         -- hashed; raw token only in email
  invite_expires_at timestamptz,
  version      int NOT NULL DEFAULT 1,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

-- One active membership per (tenant, user)
CREATE UNIQUE INDEX memberships_active_unique ON tenant.memberships (tenant_id, user_id)
  WHERE status = 'active';

CREATE INDEX memberships_tenant_status_idx ON tenant.memberships (tenant_id, status);
CREATE INDEX memberships_user_idx ON tenant.memberships (user_id);
CREATE INDEX memberships_email_idx ON tenant.memberships (email) WHERE status = 'invited';

ALTER TABLE tenant.memberships ENABLE ROW LEVEL SECURITY;
CREATE POLICY memberships_tenant_isolation ON tenant.memberships
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.4 roles

CREATE TABLE tenant.roles (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid REFERENCES tenant.tenants(id),  -- NULL for system roles
  name         text NOT NULL,
  permissions  jsonb NOT NULL DEFAULT '[]',
  is_system    boolean NOT NULL DEFAULT false,
  version      int NOT NULL DEFAULT 1,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX roles_name_unique ON tenant.roles (COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'), name);
CREATE INDEX roles_tenant_idx ON tenant.roles (tenant_id);
CREATE INDEX roles_system_idx ON tenant.roles (is_system);

ALTER TABLE tenant.roles ENABLE ROW LEVEL SECURITY;
-- System roles visible to all tenants (tenant_id IS NULL)
CREATE POLICY roles_read ON tenant.roles FOR SELECT
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id')::uuid);
-- Writes only to tenant's own roles
CREATE POLICY roles_write ON tenant.roles FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

3.5 membership_roles (join)

CREATE TABLE tenant.membership_roles (
  membership_id uuid NOT NULL REFERENCES tenant.memberships(id) ON DELETE CASCADE,
  role_id       uuid NOT NULL REFERENCES tenant.roles(id),
  tenant_id     uuid NOT NULL,                    -- denormalized for RLS
  assigned_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (membership_id, role_id)
);

CREATE INDEX membership_roles_role_idx ON tenant.membership_roles (role_id);
CREATE INDEX membership_roles_tenant_idx ON tenant.membership_roles (tenant_id);

ALTER TABLE tenant.membership_roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY membership_roles_tenant_isolation ON tenant.membership_roles
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.6 membership_org_units (join)

CREATE TABLE tenant.membership_org_units (
  membership_id uuid NOT NULL REFERENCES tenant.memberships(id) ON DELETE CASCADE,
  org_unit_id   uuid NOT NULL REFERENCES tenant.org_units(id),
  tenant_id     uuid NOT NULL,
  assigned_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (membership_id, org_unit_id)
);

CREATE INDEX membership_org_units_ou_idx ON tenant.membership_org_units (org_unit_id);
CREATE INDEX membership_org_units_tenant_idx ON tenant.membership_org_units (tenant_id);

ALTER TABLE tenant.membership_org_units ENABLE ROW LEVEL SECURITY;
CREATE POLICY mou_tenant_isolation ON tenant.membership_org_units
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.7 dynamic_groups

CREATE TABLE tenant.dynamic_groups (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         uuid NOT NULL REFERENCES tenant.tenants(id),
  name              text NOT NULL,
  query             jsonb NOT NULL,
  query_hash        bytea NOT NULL,               -- sha256(canonical(query)) for dedup
  last_evaluated_at timestamptz,
  last_member_count int,
  version           int NOT NULL DEFAULT 1,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX dynamic_groups_tenant_idx ON tenant.dynamic_groups (tenant_id);
CREATE UNIQUE INDEX dynamic_groups_name_unique ON tenant.dynamic_groups (tenant_id, name);

ALTER TABLE tenant.dynamic_groups ENABLE ROW LEVEL SECURITY;
CREATE POLICY dg_tenant_isolation ON tenant.dynamic_groups
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.8 dynamic_group_evaluations (snapshot history)

CREATE TABLE tenant.dynamic_group_evaluations (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id       uuid NOT NULL REFERENCES tenant.dynamic_groups(id) ON DELETE CASCADE,
  tenant_id      uuid NOT NULL,
  evaluated_at   timestamptz NOT NULL DEFAULT now(),
  member_count   int NOT NULL,
  member_ids     uuid[] NOT NULL,                 -- capped; for larger groups, paginated events
  added_members  uuid[] NOT NULL DEFAULT '{}',
  removed_members uuid[] NOT NULL DEFAULT '{}',
  query_hash     bytea NOT NULL
);

CREATE INDEX dge_group_time_idx ON tenant.dynamic_group_evaluations (group_id, evaluated_at DESC);
CREATE INDEX dge_tenant_idx ON tenant.dynamic_group_evaluations (tenant_id);

ALTER TABLE tenant.dynamic_group_evaluations ENABLE ROW LEVEL SECURITY;
CREATE POLICY dge_tenant_isolation ON tenant.dynamic_group_evaluations
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.9 feature_flag_overrides

CREATE TABLE tenant.feature_flag_overrides (
  tenant_id   uuid NOT NULL REFERENCES tenant.tenants(id),
  flag        text NOT NULL,
  value       jsonb NOT NULL,
  reason      text,
  set_by      uuid,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, flag)
);

ALTER TABLE tenant.feature_flag_overrides ENABLE ROW LEVEL SECURITY;
CREATE POLICY ff_tenant_isolation ON tenant.feature_flag_overrides
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.10 sso_providers

CREATE TABLE tenant.sso_providers (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id          uuid NOT NULL REFERENCES tenant.tenants(id),
  protocol           text NOT NULL CHECK (protocol IN ('saml','oidc')),
  name               text NOT NULL,
  entity_id          text NOT NULL,
  metadata_url       text,
  metadata_xml       text,
  attribute_mapping  jsonb NOT NULL DEFAULT '{}',
  client_secret_enc  bytea,                       -- envelope-encrypted with tenant KEK
  client_secret_kid  text,                        -- KMS key id for decryption
  enabled            boolean NOT NULL DEFAULT true,
  version            int NOT NULL DEFAULT 1,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX sso_providers_tenant_idx ON tenant.sso_providers (tenant_id);
CREATE UNIQUE INDEX sso_providers_entity_unique ON tenant.sso_providers (tenant_id, entity_id);

ALTER TABLE tenant.sso_providers ENABLE ROW LEVEL SECURITY;
CREATE POLICY sso_tenant_isolation ON tenant.sso_providers
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

3.11 outbox

CREATE TABLE tenant.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,                    -- e.g. 'tenant.org.provisioned.v1'
  envelope      jsonb NOT NULL,
  published_at  timestamptz,
  attempts      int NOT NULL DEFAULT 0,
  last_error    text
);

CREATE INDEX outbox_unpublished_idx ON tenant.outbox (occurred_at) WHERE published_at IS NULL;
CREATE INDEX outbox_tenant_time_idx ON tenant.outbox (tenant_id, occurred_at DESC);

3.12 inbox (idempotent consumption)

CREATE TABLE tenant.inbox (
  event_id      uuid PRIMARY KEY,
  topic         text NOT NULL,
  processed_at  timestamptz NOT NULL DEFAULT now(),
  result        text NOT NULL,                    -- 'ok' | 'skipped' | 'error'
  error_detail  text
);

CREATE INDEX inbox_topic_time_idx ON tenant.inbox (topic, processed_at DESC);

3.13 authz_decisions_audit (sampled)

CREATE TABLE tenant.authz_decisions_audit (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  decision_id    text NOT NULL,
  tenant_id      uuid NOT NULL,
  user_id        uuid NOT NULL,
  resource       text NOT NULL,
  action         text NOT NULL,
  allowed        boolean NOT NULL,
  matched_roles  text[],
  evaluated_at   timestamptz NOT NULL DEFAULT now(),
  request_context jsonb NOT NULL
);

-- Partitioned monthly; retention 7 years
CREATE INDEX authz_audit_tenant_time_idx ON tenant.authz_decisions_audit (tenant_id, evaluated_at DESC);
CREATE INDEX authz_audit_user_idx ON tenant.authz_decisions_audit (user_id);

ALTER TABLE tenant.authz_decisions_audit ENABLE ROW LEVEL SECURITY;
CREATE POLICY authz_audit_tenant_isolation ON tenant.authz_decisions_audit
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Sampling: 100% of allowed=false decisions, 1% of allowed=true decisions (configurable per tenant).

3.14 data_residency_migrations

CREATE TABLE tenant.data_residency_migrations (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL REFERENCES tenant.tenants(id),
  source_region   text NOT NULL,
  target_region   text NOT NULL,
  status          text NOT NULL CHECK (status IN ('scheduled','in_progress','validating','completed','failed','rolled_back')),
  current_step    text,
  step_history    jsonb NOT NULL DEFAULT '[]',
  scheduled_at    timestamptz NOT NULL,
  started_at      timestamptz,
  completed_at    timestamptz,
  initiated_by    uuid NOT NULL,
  rollback_reason text
);

CREATE INDEX drm_tenant_idx ON tenant.data_residency_migrations (tenant_id);
CREATE INDEX drm_status_idx ON tenant.data_residency_migrations (status);

4. Seed Data

4.1 System Roles

Role	`tenant_id`	Key Permissions
`platform_admin`	NULL	`:`
`compliance_officer`	NULL	`audit:read`, `gdpr:*`
`org_owner`	NULL (template)	`tenant:`, `role:`, `member:`, `org_unit:`
`org_admin`	NULL (template)	`member:`, `role:read`, `org_unit:`, `assignment:*`
`org_manager`	NULL (template)	scoped ABAC predicates (see DOMAIN_MODEL.md §7)
`provider_admin`	NULL (template)	`course:`, `listing:`, `payout:read`
`author`	NULL (template)	`course_draft:*` with ownership ABAC
`reviewer`	NULL (template)	`course_draft:review`
`publisher`	NULL (template)	`course:publish`
`learner`	NULL (template)	`enrollment:read_own`, `play_session:*_own`, `progress:read_own`
`individual`	NULL (template)	`order:_own`, `enrollment:_own`, `play_session:*_own`

Template roles are copied into each new tenant's roles table at provisioning time (with is_system=true to prevent modification) so permission resolution is a single-table query.

4.2 Resource/Action Registry

Maintained as a static TypeScript constant (not a DB table) imported at startup:

export const RESOURCE_ACTIONS = {
  tenant: ['read', 'write', 'suspend', 'close'],
  member: ['read', 'invite', 'update', 'remove', 'suspend'],
  role: ['read', 'create', 'update', 'delete'],
  org_unit: ['read', 'create', 'update', 'move', 'delete'],
  course: ['read', 'create', 'update', 'publish', 'delete'],
  course_draft: ['read', 'write', 'review', 'submit'],
  listing: ['read', 'create', 'update', 'submit', 'approve'],
  order: ['read', 'create', 'refund'],
  payout: ['read', 'initiate'],
  enrollment: ['read', 'read_own', 'create', 'update'],
  assignment: ['read', 'create', 'update', 'assign', 'reassign'],
  play_session: ['read', 'read_own', 'create', 'update_own'],
  progress: ['read', 'read_own'],
  certificate: ['read', 'read_own', 'issue', 'revoke'],
  audit: ['read'],
  gdpr: ['read', 'process', 'complete'],
} as const;

5. Migration Strategy

Migrations managed via Knex (tracked in ./migrations/). Rules:

Forward-only migrations; no down migrations in production (documented rollback via compensating migration).
Schema change + backfill always in separate migrations.
Destructive changes (DROP COLUMN, DROP TABLE) require 2-milestone deprecation window.
Index creation uses CREATE INDEX CONCURRENTLY in production runs.
Every migration covered by an idempotency check at the top (IF NOT EXISTS).

6. Sensitive Column Encryption

Table	Column	Rationale
`sso_providers`	`client_secret_enc`	OIDC client secrets
`memberships`	`invite_token_hash`	Never store raw token
`authz_decisions_audit`	`request_context.pii` fields	Scrubbed before insert

Envelope encryption: per-tenant DEK (data encryption key) fetched from KMS, cached in-memory with 1h TTL; rotation supported without re-encryption until next write.

7. Capacity Planning

Entity	Expected size (per tenant)	Total (10k tenants)
`tenants`	1	10,000
`org_units`	50 avg, 5,000 max	500,000
`memberships`	500 avg, 100,000 max	5,000,000
`roles`	15 (11 system + 4 custom avg)	150,000
`dynamic_groups`	10 avg	100,000
`outbox`	Continuous churn; archived after publish	Peak ~1M rows
`authz_decisions_audit`	High volume (~100/user/day)	TB-scale; partitioned monthly

1. Database Topology​

2. Entity-Relationship Diagram​

3. Table Definitions​

3.1 tenants​

3.2 org_units​

3.3 memberships​

3.4 roles​

3.5 membership_roles (join)​

3.6 membership_org_units (join)​

3.7 dynamic_groups​

3.8 dynamic_group_evaluations (snapshot history)​

3.9 feature_flag_overrides​

3.10 sso_providers​

3.11 outbox​

3.12 inbox (idempotent consumption)​

3.13 authz_decisions_audit (sampled)​

3.14 data_residency_migrations​

4. Seed Data​

4.1 System Roles​

4.2 Resource/Action Registry​

5. Migration Strategy​

6. Sensitive Column Encryption​

7. Capacity Planning​