Core Data Model Spec

Part 1 — TRW Data-Model Style, Distilled

1.1 Identity, key, and time conventions

Member.ts uses a helper because object-key order can duplicate logical Mongo IDs. SQL removes that hazard with UNIQUE(server_id,user_id); independent message sort_id remains a sound domain idea.

1.2 Polymorphic channels

TRW stores all six variants in channels as a TypeScript discriminated union (SavedMessages, DirectMessage, Group, TextChannel, JobWorkspace, Thread). This gives every message one channel reference and lets threads reuse message history, unread, permission, pin, and pagination code. The price is recipients arrays, embedded maps, and type-specific nullable fields.

Preserve one channels table with a type discriminator. It is the least surprising model for Zero .related() queries and keeps a single messages.channel_id FK. Normalize participants, pins, and permission overrides. Every canonical channel, including a DM, group, or saved surface, belongs to one server; global private conversations are a future separate product model, not a nullable tenant exception. Drop JobWorkspace from the core; a future job product can add a channel extension.

1.3 Embedded maps and ordered arrays

1.4 Denormalized projections

TRW stores first_message_id and last_message_id on channels because channel entry, unread placement, present-tail detection, and history termination need them without an aggregate. It stores reaction_counts on messages to avoid counting all reactors for every visible message.

Preserve channel first/last pointers and reaction counts, but not on hot/wide JSON objects:

• Channel boundaries live on channels and are updated in the same transaction as message writes.
• Reaction totals live in narrow message_reaction_counts rows.
• Do not initially persist server or thread member counts; query indexed membership rows.
• Do not persist hydrated user/member response blobs or the whole course tree.

Every retained projection has one writer, one atomic update contract, and one reconciliation query.

1.5 Attachments and back-links

Preserve attachment entities, upload-before-send, single-use ownership where required, dimensions, placeholder data, and derived variants. Replace embedded snapshots and message_id with message_attachments(message_id, attachment_id, position), written in the message transaction.

1.6 Permission layering

Canonical permission authority is human-readable rows with the logical shape (scope, principal, capability_key, effect), where effect is allow or deny. Capability keys come from the code-generated namespaced manifest (e.g., message.send, channel.manage, server.billing.manage). Concrete authority tables are server_default_permissions, server_role_permissions, server_attribute_permissions, and channel_overrides.

Evaluate each capability in exactly this order: server defaults, rank-ordered roles, attributes, channel default, channel roles in rank order, channel attributes, timeout mask (the typed ALLOW_IN_TIMEOUT capability set), then bounded owner/privileged bypass. Within one layer, deny wins; later layers override earlier layers. Lower numeric rank is stronger, so weaker roles or attributes apply first and stronger ones last.

1.7 Unreads, nonce, and write helpers

Use one read_states row per (user_id, channel_id) pair. Normalize exact unread mentions as message_mentions acknowledgement state rather than an array.

The client nonce is a stable operation identity across optimistic retry and message-ID refresh. message_send_receipts is the canonical idempotency authority at (principal, channel_id, client_nonce) scope; a collision returns the recorded canonical result.

Part 2 — Canonical Zero-Friendly PostgreSQL Style

2.1 Naming and identity

• Tables and columns use plural snake_case and singular _id FKs.
• Entity and relationship rows have id text PRIMARY KEY containing a ULID.
• Relationship tables also have a natural UNIQUE constraint; never rely on the surrogate ID alone.
• Better Auth organization rows are identity/admin primitives, not aliases for product servers or memberships.
• Configure Better Auth user IDs as ULIDs; if an adapter-owned table cannot, it is the documented identity exception.
• sort_id is text COLLATE "C". ULIDs are canonical uppercase so bytewise lexical order is time order.
• Every mutable row has created_at timestamptz NOT NULL DEFAULT now() and updated_at timestamptz NOT NULL DEFAULT now(). Mutators explicitly set updated_at; no hidden generic update trigger.
• Boolean names start with is_, has_, or can_. Timestamps end in _at. Counts end in _count.
• Money is integer minor units plus ISO currency, never floating point.

2.2 Foreign keys and deletion

• Index every FK used from the child side; PostgreSQL does not create those indexes automatically.
• Owned join rows use ON DELETE CASCADE.
• Shared resources and audit-relevant parents use RESTRICT; product deletion is an explicit workflow.
• Optional presentation references use SET NULL.
• User deletion anonymizes authored content before Better Auth identity deletion. Message author FKs may set null; server ownership is RESTRICT until transferred.
• Duplicate server_id on a deeply owned child only when it materially improves authorization/query plans. When duplicated, enforce tenant consistency with composite FKs.

2.3 Enum strategy

Use text plus CHECK constraints for closed system states. This is easiest to introspect into Zero and TypeScript and supports additive rollout without PostgreSQL enum coordination. A migration adding a value expands the CHECK before any writer emits it. Use lookup tables when values have metadata, administrative lifecycle or FKs. Do not use native PostgreSQL enums in the sync schema.

2.4 JSONB policy

JSONB is permitted for bounded, non-relational payloads that are rendered or delivered as a unit: link embeds, message action-block presentation, system-message payloads, lesson form/content bodies, notification presentation metadata, audit diffs, and outbox event payloads.

JSONB is forbidden for IDs/objects that are filtered, joined, ordered, permission-relevant, billed, or unbounded: roles, participants, recipients, permissions, mentions, attachment links, host lists, course hierarchy, progress, entitlement targets, provider identifiers, and read states. A JSONB field must have an owning schema/version and a size limit at the mutation boundary.

2.5 Message partitioning and history

Do not partition messages initially. The required (channel_id, sort_id) indexes give bounded range scans, while partitions complicate FKs, uniqueness, logical-replication identity, Zero schema management, and two-sided around queries.

Start with one published messages table and measure zero-cache storage, replication lag, vacuum, and index size. If evidence requires a cold seam, move immutable old rows to a server-only message_archive outside the publication and serve them through a history API.

2.6 Index conventions

• Name indexes ix_<table>__<query_shape>; name unique indexes ux_<table>__<identity>.
• Put equality/tenant columns first, then range/order columns.
• Core history index: messages(channel_id, sort_id DESC).
• Private feeds start with user_id; tenant feeds start with server_id.
• Use partial indexes for active/unread/pending work; avoid standalone low-cardinality booleans.
• Add INCLUDE only from measured index-only scans. Avoid speculative duplicate indexes.

2.7 Delete and retention policy

• Messages, channels, servers, course content, emojis, and attachments use tombstone soft deletion because replicas, replies, audit, and private R2 objects may still reference them.
• Pure joins such as member_roles, reactions, pins, and attachment links hard-delete.
• Notifications may hard-delete after product retention; audit and billing ledgers are append-only with explicit reversal/redaction.
• GDPR erasure is an orchestrated anonymize/purge operation, not casual cascading.
• deleted_at is not a universal column: add it only to models with a defined tombstone behavior.

2.8 Denormalization ledger

2.9 Zero publication and sensitivity

2.10 Logical-replication migration discipline

1. Use expand/backfill/switch/contract. Never rename, drop, or change a replicated column in place.
2. Add nullable columns and expanded CHECKs first; deploy readers; backfill in bounded batches; deploy writers; add NOT VALID constraints; validate; only later enforce NOT NULL.
3. Keep stable PKs and replica identity. Every published table has a non-null primary key.
4. Version and review the publication allowlist beside migrations and the generated Zero schema.
5. Do not add a large table to the publication in the same deploy that introduces its queries.
6. Test old-client/new-schema and new-client/old-schema windows, zero-cache rebuild, slot lag, and revocation before release.
7. Contract only after the oldest supported app and every zero-cache deployment have moved past the old shape. Destructive cleanup is a separate migration.

Part 3 — Core Model Specifications

3.1 Users / user_profiles

Purpose: safe application profile layered over Better Auth identity. The physical product table is user_profiles; Better Auth's user/account/session tables remain untouched.

Key indexes: ux_user_profiles__username on lower(username); ix_user_profiles__deleted partial on deleted users for cleanup workers.

3.2 Servers

One row per community workspace. Channels, roles, categories, and members are scoped under a server.

3.3 Roles and Permissions

roles: one row per server role.

id:text PK, server_id:text FK CASCADE, name:text, color?:text,
rank:integer NOT NULL, -- lower = stronger; @everyone is max integer
is_mentionable:boolean=false, is_managed:boolean=false,
is_default:boolean=false, -- exactly one default per server
created_at:timestamptz=now(), updated_at:timestamptz=now()
UNIQUE(server_id, name), UNIQUE(server_id, rank)

server_default_permissions: one row per capability at server scope for the default (@everyone) role.

id:text PK, server_id:text FK CASCADE, capability_key:text NOT NULL,
effect:text NOT NULL CHECK(effect IN ('allow','deny')),
UNIQUE(server_id, capability_key)

server_role_permissions: per-role capability overrides.

id:text PK, server_id:text FK CASCADE, role_id:text FK CASCADE,
capability_key:text NOT NULL, effect:text NOT NULL CHECK(effect IN ('allow','deny')),
UNIQUE(role_id, capability_key)

channel_permission_overrides: per-channel capability overrides for a role or user attribute.

id:text PK, channel_id:text FK CASCADE, server_id:text FK (denorm),
principal_type:text CHECK('role','attribute'), principal_id:text,
capability_key:text NOT NULL, effect:text NOT NULL CHECK(effect IN ('allow','deny')),
UNIQUE(channel_id, principal_type, principal_id, capability_key)

member_roles: join table linking server members to their assigned roles.

id:text PK, server_id:text FK (denorm), user_id:text FK CASCADE,
role_id:text FK CASCADE, assigned_at:timestamptz=now(), assigned_by_user_id?:text FK,
UNIQUE(server_id, user_id, role_id)

3.4 Channels

channel_members: for DM/group_dm channels.

id:text PK, channel_id:text FK CASCADE, user_id:text FK CASCADE,
joined_at:timestamptz=now(), is_owner:boolean=false,
UNIQUE(channel_id, user_id)
INDEX ix_channel_members__user(user_id, channel_id)

3.5 Messages

INDEX ix_messages__channel_history(channel_id, sort_id DESC)
INDEX ix_messages__author(author_user_id, created_at DESC) WHERE NOT is_deleted
INDEX ix_messages__thread_parent(reply_to_message_id) WHERE reply_to_message_id IS NOT NULL

message_send_receipts: canonical idempotency store for the message outbox.

id:text PK, principal_user_id:text NOT NULL, channel_id:text FK RESTRICT,
client_nonce:text NOT NULL, canonical_message_id:text FK RESTRICT,
created_at:timestamptz=now()
UNIQUE(principal_user_id, channel_id, client_nonce)

message_revisions: server-only edit/moderation history.

id:text PK, message_id:text FK RESTRICT, content:text, edited_by_user_id?:text FK,
edit_type:text CHECK('user_edit','moderator_redact','legal_purge'),
created_at:timestamptz=now()

message_mentions:

id:text PK, message_id:text FK CASCADE, channel_id:text FK (denorm),
server_id:text FK (denorm), mentioned_user_id:text FK CASCADE,
mention_type:text CHECK('user','role','everyone','here'),
is_acknowledged:boolean=false
UNIQUE(message_id, mentioned_user_id, mention_type)
INDEX ix_message_mentions__user_unack(mentioned_user_id, is_acknowledged, channel_id) WHERE NOT is_acknowledged

3.6 Attachments

id:text PK, uploader_user_id:text FK, server_id?:text FK (scoped),
r2_object_key:text -- server-only; excluded from publication
media_type:text, byte_size:bigint,
width?:integer, height?:integer, duration_ms?:integer,
blurhash?:text, alt_text?:text,
processing_state:text CHECK('pending','processing','ready','failed'),
moderation_state:text CHECK('pending','approved','rejected'),
is_deleted:boolean=false, deleted_at?:timestamptz,
created_at:timestamptz=now(), updated_at:timestamptz=now()

Synced columns exclude r2_object_key. Variant descriptors (thumbnails, HLS manifests) live in a separate server-only attachment_variants table. message_attachments is the join table:

id:text PK, message_id:text FK CASCADE, attachment_id:text FK RESTRICT,
position:smallint NOT NULL, created_at:timestamptz=now()
UNIQUE(message_id, attachment_id), UNIQUE(message_id, position)

3.7 Reactions

message_reactions: normalized per-user reactions.

id:text PK, message_id:text FK CASCADE, user_id:text FK CASCADE,
emoji_id?:text FK, emoji_unicode?:text, -- exactly one set
channel_id:text FK (denorm), server_id:text FK (denorm),
created_at:timestamptz=now()
UNIQUE(message_id, user_id, COALESCE(emoji_id,''), COALESCE(emoji_unicode,''))

message_reaction_counts: denormalized aggregate maintained transactionally.

message_id:text FK CASCADE, emoji_key:text, -- 'unicode:❤️' or 'custom:id'
count:integer NOT NULL CHECK(count > 0),
version:bigint NOT NULL DEFAULT 0, updated_at:timestamptz=now()
PRIMARY KEY(message_id, emoji_key)

3.8 Read State and Unread

read_states: one row per (user_id, channel_id), created lazily on first view.

id:text PK, user_id:text FK CASCADE, channel_id:text FK CASCADE,
server_id:text FK (denorm),
last_read_sort_id?:text COLLATE "C",  -- monotonically increasing; max on conflict
last_read_at?:timestamptz,
unread_mention_count:integer=0,
is_manually_unread:boolean=false,
muted_until?:timestamptz,
notification_preference:text='default' CHECK('default','all','mentions','nothing'),
created_at:timestamptz=now(), updated_at:timestamptz=now()
UNIQUE(user_id, channel_id)
INDEX ix_read_states__server(user_id, server_id)

3.9–3.21 Learning System

The learning system provides a five-level hierarchy: Server → Category → Course → Module → Lesson, with associated assets, progress tracking, and completion records.

learning_categories (3.9):

id:text PK, server_id:text FK CASCADE, name:text, description?:text,
position:integer, is_published:boolean=false,
is_deleted:boolean=false, deleted_at?:timestamptz,
created_at:timestamptz=now(), updated_at:timestamptz=now()
INDEX ix_learning_categories__server(server_id, position)

learning_courses (3.10):

id:text PK, server_id:text FK CASCADE, category_id?:text FK,
title:text, description?:text, instructor_user_id?:text FK,
thumbnail_attachment_id?:text FK, position:integer,
is_published:boolean=false, is_free:boolean=true,
requires_entitlement_key?:text,
is_deleted:boolean=false, deleted_at?:timestamptz,
created_at:timestamptz=now(), updated_at:timestamptz=now()

learning_modules (3.11):

id:text PK, course_id:text FK CASCADE, server_id:text FK (denorm),
title:text, position:integer, is_published:boolean=false,
is_deleted:boolean=false, deleted_at?:timestamptz,
created_at:timestamptz=now(), updated_at:timestamptz=now()

learning_lessons (3.12):

id:text PK, module_id:text FK CASCADE, course_id:text FK (denorm),
server_id:text FK (denorm), title:text, description?:text,
position:integer, lesson_type:text CHECK('video','audio','text','quiz','assignment'),
duration_ms?:integer, thumbnail_attachment_id?:text FK,
is_published:boolean=false, is_free_preview:boolean=false,
completion_policy:text CHECK('view','percentage','quiz_pass','manual')='view',
completion_threshold_pct?:integer,
is_deleted:boolean=false, deleted_at?:timestamptz,
created_at:timestamptz=now(), updated_at:timestamptz=now()

learning_assets (3.13): video, audio, or document resources attached to a lesson.

id:text PK, lesson_id:text FK CASCADE, server_id:text FK (denorm),
asset_type:text CHECK('video','audio','document'),
attachment_id:text FK RESTRICT, position:integer,
transcript_state:text CHECK('none','pending','processing','ready','failed')='none',
is_deleted:boolean=false,
created_at:timestamptz=now(), updated_at:timestamptz=now()

lesson_content_blocks (3.14 — server-only): authored structured content.

id:text PK, lesson_id:text FK CASCADE, block_type:text, position:integer,
content:jsonb NOT NULL, is_deleted:boolean=false,
created_at:timestamptz=now(), updated_at:timestamptz=now()

content_transcripts (3.15 — server-only): timestamped transcript segments per asset.

id:text PK, asset_id:text FK CASCADE, model_version:text,
language:text, segments:jsonb, full_text:text, word_count:integer,
is_deleted:boolean=false,
created_at:timestamptz=now(), updated_at:timestamptz=now()

learning_enrollments (3.16): per-user course enrollment.

id:text PK, user_id:text FK CASCADE, course_id:text FK CASCADE,
server_id:text FK (denorm), enrolled_at:timestamptz=now(),
is_active:boolean=true,
UNIQUE(user_id, course_id)

lesson_progress (3.17): coarse per-user lesson progress; synced.

id:text PK, user_id:text FK CASCADE, lesson_id:text FK CASCADE,
course_id:text FK (denorm), server_id:text FK (denorm),
state:text CHECK('not_started','in_progress','completed')='not_started',
watched_seconds:integer=0, last_position_seconds:integer=0,
completed_at?:timestamptz, completion_version:bigint=0,
updated_at:timestamptz=now()
UNIQUE(user_id, lesson_id)
INDEX ix_lesson_progress__course(user_id, course_id)

course_completions (3.18): immutable server-authoritative completion record.

id:text PK, user_id:text FK CASCADE, course_id:text FK CASCADE,
server_id:text FK (denorm), completed_at:timestamptz=now(),
granted_role_id?:text FK, reward_state:text='pending',
UNIQUE(user_id, course_id)

quizzes / quiz_questions / quiz_attempts (3.19–3.21): quiz structure is server-only; synced fields are attempt state, score, and completion.

3.22 Live Events and Streams

scheduled_events:

id:text PK, server_id:text FK CASCADE, channel_id?:text FK,
creator_user_id:text FK, title:text, description?:text,
event_type:text CHECK('live_room','scheduled_message','course_session'),
starts_at:timestamptz, ends_at?:timestamptz,
status:text CHECK('scheduled','active','completed','canceled')='scheduled',
is_deleted:boolean=false,
created_at:timestamptz=now(), updated_at:timestamptz=now()

live_streams:

id:text PK, server_id:text FK CASCADE, channel_id:text FK RESTRICT,
scheduled_event_id?:text FK,
livekit_room_name:text UNIQUE, -- server-only; excluded from publication
status:text CHECK('waiting','live','ended','failed')='waiting',
started_at?:timestamptz, ended_at?:timestamptz,
viewer_count:integer=0, max_viewer_count:integer=0,
recording_state:text CHECK('none','recording','processing','ready')='none',
recording_attachment_id?:text FK,
created_at:timestamptz=now(), updated_at:timestamptz=now()

Synced columns exclude livekit_room_name and provider credentials. The max_viewer_count is a monotonic projection maintained by the live lifecycle worker.

3.23 Invites

invites (server-only): join invitations scoped to a server or channel.

id:text PK, server_id:text FK CASCADE, channel_id?:text FK,
code:text UNIQUE, creator_user_id:text FK,
max_uses?:integer, use_count:integer=0,
expires_at?:timestamptz, is_revoked:boolean=false,
target_type?:text CHECK('user','role'), target_id?:text,
created_at:timestamptz=now(), updated_at:timestamptz=now()

3.24 Notifications

notifications (partially synced; safe fields only):

id:text PK, recipient_user_id:text FK CASCADE,
notification_type:text, -- 'mention','dm','reply','live_start','course_complete'…
server_id?:text FK, channel_id?:text FK, message_id?:text FK,
course_id?:text FK, event_id?:text FK,
title:text, body_preview?:text, -- safe bounded preview
is_read:boolean=false, read_at?:timestamptz,
created_at:timestamptz=now()

3.25 Custom Emojis

id:text PK, server_id:text FK CASCADE,
name:text, attachment_id:text FK RESTRICT,
is_animated:boolean=false, is_deleted:boolean=false,
created_by_user_id:text FK,
created_at:timestamptz=now(), updated_at:timestamptz=now()
UNIQUE(server_id, name)

3.26 Billing Projections (server-only)

The billing ledger, provider IDs, and subscriptions are server-only (see Doc 11). The data model exposes only safe entitlement-sourced projections through Zero.

-- subscriptions: server-only state machine (see 11-billing-service-spec.md)
-- payment_intents, payment_methods, billing_events: server-only
-- ledger_accounts, journal_entries, postings: server-only

3.27 Entitlements

Entitlement grants are the locally-authoritative access mechanism consumed by Zero queries. The projector evaluates all active billing and admin sources and writes these rows.

id:text PK, user_id?:text FK CASCADE, server_id?:text FK CASCADE,
entitlement_key:text NOT NULL, -- e.g. 'server.member_access', 'course.access:course_id'
source_type:text CHECK('subscription','purchase','admin','course_reward','access_rule'),
source_id:text NOT NULL, -- FK to causal source
granted_at:timestamptz=now(),
valid_from:timestamptz=now(),
valid_until?:timestamptz,
is_active:boolean=true, -- projection; updated by projector
revoked_at?:timestamptz, revocation_reason?:text,
version:bigint=0,
UNIQUE(user_id, entitlement_key, source_type, source_id)

3.28 Audit Log (server-only)

id:text PK,
actor_user_id?:text FK,
actor_service_principal_id?:text,
server_id?:text,
action:text NOT NULL, -- namespaced e.g. 'channel.delete', 'role.permission.update'
resource_type:text, resource_id:text,
before_state:jsonb, after_state:jsonb, -- redacted deltas
policy_version:text,
request_id:text,
reason?:text,
retention_class:text CHECK('operational','compliance','legal')='operational',
created_at:timestamptz=now()
INDEX ix_audit_log__server(server_id, created_at DESC)
INDEX ix_audit_log__actor(actor_user_id, created_at DESC)

3.29 Feature Flags

-- feature_flag_definitions: server-only; key, description, default_value, rollout config
-- evaluated_feature_flags (partially synced): pre-evaluated flag values per user/server context
id:text PK, flag_key:text, context_type:text, context_id:text,
value:boolean, evaluated_at:timestamptz=now()
UNIQUE(flag_key, context_type, context_id)

3.30 Transaction Outbox (server-only)

Every domain-significant event writes an outbox row in the same transaction as the domain mutation. Workers claim and deliver with FOR UPDATE SKIP LOCKED.

CREATE TABLE outbox_events (
  id text PRIMARY KEY CHECK (char_length(id) = 26),
  aggregate_type text NOT NULL, -- e.g. 'message', 'subscription', 'server'
  aggregate_id text NOT NULL,
  event_type text NOT NULL,  -- e.g. 'message.created.v1'
  schema_version integer NOT NULL DEFAULT 1,
  idempotency_key text NOT NULL UNIQUE,
  payload jsonb NOT NULL DEFAULT '{}',
  server_id text,
  resource_type text,
  resource_id text,
  actor_user_id text,
  actor_service_principal_id text,
  correlation_id text,
  causation_id text,
  retention_class text NOT NULL DEFAULT 'operational',
  available_at timestamptz NOT NULL DEFAULT now(),
  locked_until timestamptz,
  locked_by text,
  attempt_count integer NOT NULL DEFAULT 0,
  last_attempt_at timestamptz,
  processed_at timestamptz,
  result jsonb,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_outbox_events__pending ON outbox_events
  (available_at, id) WHERE processed_at IS NULL;
CREATE INDEX ix_outbox_events__aggregate ON outbox_events
  (aggregate_type, aggregate_id, created_at);