03 — Database Schema

Three stores, three jobs:

• PostgreSQL — source of truth for identity, tenancy, rooms, membership, schedules and run history, audit.
• Redis — ephemeral runtime state (presence, signaling registry, credentials cache, rate limits). Never a source of truth.
• ClickHouse — append-only telemetry at high ingest volume.

---

PostgreSQL

Multi-tenant from day one: every business row carries tenant_id. All tables get created_at timestamptz NOT NULL DEFAULT now() and, where mutable, updated_at. IDs are UUIDv7 (time-ordered, index-friendly).

Authentication Service (schema auth)

CREATE TABLE tenants (
    id              uuid PRIMARY KEY,
    name            text NOT NULL,
    slug            text NOT NULL UNIQUE,
    status          text NOT NULL DEFAULT 'active',   -- active | suspended
    settings        jsonb NOT NULL DEFAULT '{}',      -- webhook URLs, room defaults, region policy
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
    id              uuid PRIMARY KEY,
    tenant_id       uuid NOT NULL REFERENCES tenants(id),
    external_id     text,                              -- tenant's own user id (BaaS mode)
    email           citext,
    password_hash   text,                              -- argon2id; NULL for federated/BaaS users
    display_name    text NOT NULL,
    platform_role   text NOT NULL DEFAULT 'user',      -- user | tenant_admin
    status          text NOT NULL DEFAULT 'active',    -- active | suspended | deleted
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, email),
    UNIQUE (tenant_id, external_id)
);

CREATE TABLE refresh_tokens (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash      bytea NOT NULL,                    -- sha256 of opaque token
    family_id       uuid NOT NULL,                     -- rotation family for reuse detection
    issued_at       timestamptz NOT NULL DEFAULT now(),
    expires_at      timestamptz NOT NULL,
    revoked_at      timestamptz,
    device_info     jsonb
);
CREATE INDEX ON refresh_tokens (user_id) WHERE revoked_at IS NULL;
CREATE UNIQUE INDEX ON refresh_tokens (token_hash);

CREATE TABLE api_keys (                                -- tenant server-to-server auth
    id              uuid PRIMARY KEY,
    tenant_id       uuid NOT NULL REFERENCES tenants(id),
    name            text NOT NULL,
    key_prefix      text NOT NULL,                     -- first 8 chars, for lookup/display
    secret_hash     bytea NOT NULL,
    scopes          text[] NOT NULL DEFAULT '{}',      -- e.g. {rooms:write, tokens:mint}
    last_used_at    timestamptz,
    expires_at      timestamptz,
    revoked_at      timestamptz,
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON api_keys (key_prefix);

CREATE TABLE signing_keys (                            -- JWT JWKS rotation
    kid             text PRIMARY KEY,
    algorithm       text NOT NULL,                     -- EdDSA | RS256
    public_jwk      jsonb NOT NULL,
    private_pem_ref text NOT NULL,                     -- reference into secret manager, never the key
    active          boolean NOT NULL DEFAULT true,
    created_at      timestamptz NOT NULL DEFAULT now(),
    retired_at      timestamptz
);

Room Service (schema rooms)

CREATE TABLE rooms (
    id              uuid PRIMARY KEY,
    tenant_id       uuid NOT NULL,
    slug            text NOT NULL,                     -- human-friendly join code
    name            text NOT NULL,
    description     text,
    visibility      text NOT NULL DEFAULT 'private',   -- private | public | unlisted
    status          text NOT NULL DEFAULT 'created',   -- created | active | idle | closed
    max_participants integer NOT NULL DEFAULT 50,
    media_region    text,                              -- pinned SFU region, set on first join
    settings        jsonb NOT NULL DEFAULT '{}',       -- e.g. {"listeners_can_request_speak": true}
    created_by      uuid NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT now(),
    activated_at    timestamptz,
    idle_since      timestamptz,                       -- set when the last participant left; NULL unless status='idle'
    closed_at       timestamptz,
    close_reason    text,                              -- explicit | tenant_suspended | idle_timeout | abandoned
    deleted_at      timestamptz                        -- soft delete
);
CREATE UNIQUE INDEX ON rooms (tenant_id, slug) WHERE deleted_at IS NULL;
CREATE INDEX ON rooms (tenant_id, status);
-- Inactivity-reaper hot path: only idle/created rooms are ever candidates.
CREATE INDEX ON rooms (status, idle_since, created_at)
    WHERE deleted_at IS NULL AND status IN ('idle', 'created');

CREATE TABLE room_participants (                       -- durable membership + role, one row per (room,user)
    room_id         uuid NOT NULL REFERENCES rooms(id),
    user_id         uuid NOT NULL,
    role            text NOT NULL DEFAULT 'listener',  -- moderator | speaker | listener
    muted_by_mod    boolean NOT NULL DEFAULT false,
    banned          boolean NOT NULL DEFAULT false,
    invited_by      uuid,
    first_joined_at timestamptz,
    last_left_at    timestamptz,
    PRIMARY KEY (room_id, user_id)
);

CREATE TABLE room_sessions (                           -- one row per actual connection episode (audit/analytics join key)
    id              uuid PRIMARY KEY,
    room_id         uuid NOT NULL REFERENCES rooms(id),
    user_id         uuid NOT NULL,
    joined_at       timestamptz NOT NULL,
    left_at         timestamptz,
    leave_reason    text,                              -- left | kicked | disconnected | room_closed
    sfu_node        text,
    client_platform text                               -- web | android | ios | desktop
);
CREATE INDEX ON room_sessions (room_id, joined_at);
CREATE INDEX ON room_sessions (user_id, joined_at);

CREATE TABLE moderation_actions (                      -- audit trail
    id              uuid PRIMARY KEY,
    room_id         uuid NOT NULL REFERENCES rooms(id),
    actor_id        uuid NOT NULL,
    target_id       uuid,
    action          text NOT NULL,                     -- mute | unmute | kick | ban | promote | demote | close_room
    reason          text,
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON moderation_actions (room_id, created_at);

Room inactivity reaping. idle_since is the clock the automatic close is measured from. It is stamped in the same transaction that ends a room's last live session and cleared when the room is rejoined, so it is only ever non-NULL while status = 'idle'. A ticker loop on every room replica (ROOM_REAPER_INTERVAL, default 5m) claims a bounded batch of expired rooms via FOR UPDATE SKIP LOCKED on rooms_reap_idx and closes them — idle_since < now() - ROOM_IDLE_TTL as idle_timeout, never-joined rooms with created_at < now() - ROOM_IDLE_TTL as abandoned (default TTL 24h). The close re-checks the status/age predicates and NOT EXISTS (live sessions) inside the same transaction, so a room rejoined mid-tick is skipped rather than closed. The point is not resource cost but the unique index on (tenant_id, slug) WHERE deleted_at IS NULL: a room that never closes squats its slug permanently.

Scheduler Service (schema scheduler)

Owns scheduler_db (full DDL: services/scheduler/migrations/001_init.sql; design: ADR 0032–0041). Enum-ish columns are text + CHECK (billing convention — CHECKs swap atomically in one migration; ALTER TYPE … ADD VALUE cannot).

CREATE TABLE schedule (                                -- the aggregate: tenant-declared time trigger
    id              uuid PRIMARY KEY,
    tenant_id       uuid NOT NULL,
    name            text NOT NULL,                     -- UNIQUE (tenant_id, name) while live: create-idempotency handle
    kind            text NOT NULL,                     -- cron | interval | one_shot
    cron_expr       text,                              -- 5-field cron, no seconds (1-minute granularity, ADR 0035)
    interval_seconds integer,                          -- >= 60
    timezone        text NOT NULL DEFAULT 'UTC',       -- IANA name
    tzdata_version  text,                              -- tzdata used for next_fire_at; guarded recompute on upgrade
    starts_at       timestamptz, ends_at timestamptz,  -- optional window; one_shot fire instant
    action          text NOT NULL,                     -- webhook | event
    target          jsonb NOT NULL DEFAULT '{}',       -- webhook: {url, timeout_ms<=10000, headers}; secret lives elsewhere
    payload         jsonb NOT NULL DEFAULT '{}',       -- tenant-authored, <= 64 KiB (CHECK is the authoritative guard)
    jitter_seconds  integer NOT NULL DEFAULT 0,        -- 0–900, deterministic (ADR 0037)
    misfire_policy  text NOT NULL DEFAULT 'fire_once', -- fire_once | skip | catch_up (ADR 0036)
    overlap_policy  text NOT NULL DEFAULT 'skip',      -- skip | allow | queue_one
    retry_policy    jsonb NOT NULL,                    -- {max_attempts<=8, base_seconds, cap_seconds}, plan-bounded
    max_staleness_seconds integer NOT NULL DEFAULT 3600,
    status          text NOT NULL DEFAULT 'active',    -- active | paused | suspended_tenant | disabled_failing
                                                       --   | disabled_invalid | completed | deleted (soft)
    version         bigint NOT NULL DEFAULT 1,         -- bumped on semantic edit; pinned onto each run
    next_fire_at    timestamptz,                       -- NULL when not schedulable
    last_fire_at    timestamptz,
    consecutive_failures integer NOT NULL DEFAULT 0,   -- breaker state: 100 in a row → disabled_failing
    deleted_at      timestamptz
);
CREATE UNIQUE INDEX ON schedule (tenant_id, name) WHERE deleted_at IS NULL;
CREATE INDEX ON schedule (next_fire_at) WHERE status = 'active';   -- planner hot path: only active, due-ordered

CREATE TABLE schedule_secret (                         -- HMAC secret, envelope-encrypted (ADR 0040); own table so
    schedule_id     uuid PRIMARY KEY REFERENCES schedule(id) ON DELETE CASCADE,   -- aggregate SELECTs never carry ciphertext
    secret_ciphertext bytea NOT NULL,                  -- AES-GCM: nonce || ciphertext || tag
    key_ref         text NOT NULL,                     -- External Secrets KEK version used
    rotated_at      timestamptz
);

CREATE TABLE job_run (                                 -- one row per planned occurrence; highest-volume table
    id              uuid NOT NULL,
    schedule_id     uuid NOT NULL REFERENCES schedule(id),
    schedule_version bigint NOT NULL,
    scheduled_for   timestamptz NOT NULL,              -- canonical UN-jittered instant; partition key; idempotency anchor
    fire_after      timestamptz NOT NULL,              -- scheduled_for + hash(schedule_id) mod jitter
    idempotency_key text NOT NULL,                     -- sha256(schedule_id || scheduled_for), sent as X-Idempotency-Key
    status          text NOT NULL DEFAULT 'pending',   -- pending | running | retrying | succeeded | failed | skipped | cancelled
    skip_reason     text,                              -- overlap | tenant_suspended | quota_exhausted | stale
    attempts        integer NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL,              -- the single ordering key the executor claims on
    claimed_by      text,                              -- lease holder pod identity
    claim_expires_at timestamptz,                      -- lease: now() + 2 × delivery timeout
    last_error_class text,                             -- dns | tls | timeout | conn_refused | http_4xx | http_5xx | blocked_ssrf
    completed_at    timestamptz,
    PRIMARY KEY (id, scheduled_for),
    UNIQUE (schedule_id, scheduled_for)                -- THE correctness invariant: exactly-once materialisation
) PARTITION BY RANGE (scheduled_for);
CREATE INDEX ON job_run (next_attempt_at) WHERE status IN ('pending','retrying');  -- executor claim path
CREATE INDEX ON job_run (claim_expires_at) WHERE status = 'running';               -- lease reaper (crash → reclaim)

CREATE TABLE delivery_attempt (                        -- one row per outbound HTTP attempt: the tenant's delivery log
    job_run_id      uuid NOT NULL,                     -- app-enforced ref (no FK between partitioned tables — see notes)
    scheduled_for   timestamptz NOT NULL,              -- copied from the run: shared partition key + retention calendar
    attempt_no      integer NOT NULL,                  -- 1–8
    started_at      timestamptz NOT NULL DEFAULT now(),
    duration_ms     integer,
    response_status integer,
    error_class     text,
    response_snippet text,                             -- <= 2 KiB, control chars stripped (ADR 0025 §5 redaction stance)
    PRIMARY KEY (job_run_id, scheduled_for, attempt_no)
) PARTITION BY RANGE (scheduled_for);

CREATE TABLE outbox (                                  -- transactional outbox → schedule-events (auth's RelayOutbox
    id uuid PRIMARY KEY, topic text NOT NULL,          --   pattern; scheduler is the only writer)
    key text NOT NULL, payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz, attempts int NOT NULL DEFAULT 0
);

CREATE TABLE tenant_status (                           -- projection ← platform-events (ADR 0025 shape,
    tenant_id text PRIMARY KEY, status text NOT NULL,  --   occurred_at-guarded; fail-open on missing row)
    event_occurred_at timestamptz NOT NULL
);

CREATE TABLE tenant_entitlements (                     -- projection ← tenant.entitlements_updated (ADR 0030;
    tenant_id text PRIMARY KEY,                        --   monotonic entitlement_version is the ordering guard)
    entitlements jsonb NOT NULL DEFAULT '{}',          -- max_schedules, min_schedule_interval_seconds, ...
    entitlement_version bigint NOT NULL DEFAULT 0,
    quota_blocked text[] NOT NULL DEFAULT '{}'         -- 'scheduled_runs_per_cycle' here → skipped(quota_exhausted)
);

Notes:

• Claim pattern. Both loops coordinate through FOR UPDATE SKIP LOCKED — the planner claims due schedules and advances next_fire_at in the same transaction that inserts the run; the executor claims due runs and takes a lease (claimed_by, claim_expires_at = now() + 2×timeout). A crashed executor's lease expires and the run is reclaimed (at-least-once); UNIQUE (schedule_id, scheduled_for) makes materialisation exactly-once regardless of replica races or clock jumps (ADR 0033/0034). All due-time decisions use Postgres now() — one clock authority.
• Partitioning and retention. job_run and delivery_attempt are range-partitioned monthly on scheduled_for; a maintenance loop keeps the current month + 3 ahead, and retention is DETACH PARTITION, never DELETE (hot window 30 days, plan-dependent up to 90; older history lives in ClickHouse via schedule-events). Because scheduled_for is the partition key, the parent-level UNIQUE (schedule_id, scheduled_for) and PRIMARY KEY (id, scheduled_for) are legal — every candidate duplicate routes to the same partition. delivery_attempt deliberately has no FK to job_run: an FK between partitioned tables makes detach order-dependent; attempt rows are only written in the run-state transaction by the lease holder, so integrity is app-enforced by design. No DEFAULT partition — a stray row would block future ATTACHes, and the domain forbids far-future rows anyway.
• Redis is ephemeral only. Per-tenant concurrency token buckets and per-destination-host breaker counters live in Redis; both are reconstructible and never authoritative — consistent with the platform stance above.

Permission model

• Platform layer: users.platform_role (tenant_admin manages tenant settings, API keys, any room in tenant).
• Room layer: room_participants.role — moderator (moderate + speak), speaker (publish audio), listener (subscribe only). Role → media capability mapping happens at token mint time: the media access token carries canPublish = role IN (moderator, speaker) AND NOT muted_by_mod, canSubscribe = NOT banned. Changing a role re-mints/updates the grant and takes effect at the SFU immediately.
• Tenant API keys: scoped capabilities (rooms:write, tokens:mint, analytics:read) checked at the gateway.

---

Redis Keyspace (ephemeral, TTL-driven)

Key pattern	Type	TTL	Purpose
presence:user:{user_id}	hash {status, region, session_id, ws_instance}	45 s, refreshed by heartbeat	online/idle liveness
presence:room:{room_id}	set of user_id	member-level cleanup on leave/expiry sweep	occupancy
presence:room:{room_id}:speaking	set	5 s rolling	active speakers (from SFU events)
room:{room_id}:state	hash {status, sfu_node, participant_count}	while active	hot room state (avoids PG on hot path)
signal:session:{session_id}	hash {user_id, room_id, ws_instance, seq}	90 s past disconnect	reconnect resume window
signal:room:{room_id}	pub/sub channel	—	cross-instance signaling fan-out
ratelimit:{scope}:{id}:{window}	counter	window length	gateway rate limiting
turncred:{user_id}:{session_id}	string (issued-credential record)	= credential TTL (600 s)	audit/limit duplicate issuance
jwks:cache	string	300 s	gateway/service JWKS cache

Deployment: Redis Cluster, per-region; presence keys are region-local, global queries aggregate across regions via the Presence API (not cross-region Redis).

---

ClickHouse (schema analytics)

CREATE TABLE qos_samples (                             -- one row per client stats snapshot (~every 10 s)
    ts              DateTime64(3),
    tenant_id       UUID,
    room_id         UUID,
    session_id      UUID,                              -- joins to rooms.room_sessions
    user_id         UUID,
    platform        LowCardinality(String),
    region          LowCardinality(String),
    sfu_node        LowCardinality(String),
    direction       Enum8('up' = 1, 'down' = 2),
    rtt_ms          Float32,
    jitter_ms       Float32,
    packet_loss_pct Float32,
    bitrate_kbps    Float32,
    audio_level     Float32,
    candidate_type  LowCardinality(String)             -- host | srflx | relay
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (tenant_id, room_id, session_id, ts)
TTL toDateTime(ts) + INTERVAL 90 DAY;

CREATE TABLE session_summaries (                       -- one row per completed session
    session_id      UUID,
    tenant_id       UUID,
    room_id         UUID,
    user_id         UUID,
    joined_at       DateTime64(3),
    left_at         DateTime64(3),
    duration_s      UInt32,
    platform        LowCardinality(String),
    region          LowCardinality(String),
    avg_rtt_ms      Float32,
    avg_loss_pct    Float32,
    avg_jitter_ms   Float32,
    mos             Float32,                           -- E-model estimate
    used_turn_relay UInt8,
    leave_reason    LowCardinality(String)
) ENGINE = ReplacingMergeTree(left_at)
PARTITION BY toYYYYMM(joined_at)
ORDER BY (tenant_id, room_id, session_id);

CREATE TABLE domain_events (                           -- Kafka ETL sink, long-horizon product analytics
    ts              DateTime64(3),
    tenant_id       UUID,
    event_type      LowCardinality(String),
    room_id         UUID,
    user_id         UUID,
    payload         String                             -- JSON
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (tenant_id, event_type, ts)
TTL toDateTime(ts) + INTERVAL 365 DAY;

Materialized views roll qos_samples into per-room/per-minute aggregates for dashboards (avg loss, p95 RTT, concurrent users) so Grafana queries never scan raw samples.