Data Model
Normalized PostgreSQL schema. server_id tenancy on every tenant-owned table. ULID PKs,
stable sort_id cursors, explicit created_at/updated_at.
Core Table Relationships
The schema organizes around three conceptual groups: identity and tenancy (servers, users, memberships, roles), chat (channels, messages, reactions, attachments), and billing. The diagram below shows the primary foreign-key relationships.
Identity & Tenancy Tables
These tables establish the tenant boundary. Every downstream table references server_id for tenancy isolation. Better Auth owns accounts and sessions entirely; all other rows are app-owned.
users -- ULID pk, display_name, avatar_url, created_at
accounts / sessions -- Better Auth-owned; credential + session store
servers -- ULID pk, slug, name, default_permissions (bitset cache)
server_memberships -- PK (server_id, user_id); joined_at, status
roles -- ULID pk, server_id, name, rank (lower = stronger)
membership_roles -- PK (server_id, user_id, role_id)
channels -- ULID pk, server_id, type, name, sort_order
channel_role_overrides -- channel_id × role_id; capability + effect rows
channel_member_overrides -- channel_id × user_id; direct capability grantsChat Tables
The messages table is the core of the chat domain. Its sort_id enables efficient range queries for all paging primitives without relying on sequence numbers.
messages (
id text PRIMARY KEY, -- ULID
channel_id text NOT NULL,
user_id text NOT NULL,
sort_id text COLLATE "C" NOT NULL, -- bytewise lexical = time order
client_nonce text NOT NULL, -- idempotency key
content jsonb NOT NULL, -- link embeds, rich text body
reply_to_message_id text,
thread_id text,
edited_at timestamptz,
deleted_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (channel_id, client_nonce) -- non-negotiable idempotency
)
message_revisions -- append-only edit history per message
message_reactions -- emoji · user_id · message_id; UNIQUE (msg,user,emoji)
thread_subscriptions -- user × thread subscription preferences
channel_read_states -- user × channel; last_read_sort_id + mention_count
message_mentions -- message_id × target_user_id × mention_type
attachments -- ULID pk; R2 key, mime_type, size, scan_status
message_attachments -- message_id × attachment_id join
message_send_receipts -- server ack for outbox idempotency; keyed on client_nonceKey Schema Rules
| Rule | Decision |
|---|---|
| ULID text PKs on entities | Client-generatable, globally sortable, no sequence coordination required. Enables offline-first ID creation in the outbox. |
sort_id text COLLATE "C" |
Bytewise lexical order equals time order. Enables all range cursor queries (before, after, around) with a simple index scan on (channel_id, sort_id). |
UNIQUE(channel_id, client_nonce) |
Non-negotiable message idempotency. The database enforces at-most-once semantics regardless of how many times the client retransmits an outbox item. |
| Money as integer minor units | All monetary values stored as integer minor units (e.g. cents) plus ISO 4217 currency code column. Never floating point. Prevents rounding error in ledger arithmetic. |
text + CHECK constraints (not enums) |
Easy Zero/TypeScript introspection, additive rollout without schema migrations for new values. Enums require DDL changes to extend. |
JSONB only for bounded non-relational payloads |
Permitted for rich text content bodies and link embeds. Never used for IDs, permissions, or relational keys that need indexing or foreign key constraints. |
Courses Tables
Courses are a first-class launch feature, not a later add-on. The schema supports TRW-style learning: modules, prerequisites, and server-authoritative completion-to-role rewards.
course_spaces -- grouping container within a server
course_sections -- ordered sections within a space
courses -- ULID pk, server_id, title, published_at
course_modules -- ordered modules within a course
lessons -- ULID pk, module_id, title, content_type
lesson_assets -- R2-backed media assets per lesson
course_enrollments -- user × course; enrolled_at, status
lesson_progress -- user × lesson; completed_at
course_progress -- user × course; percent_complete, completed_at
course_prerequisites -- prerequisite graph (course_id → required_course_id)
completion_rewards -- course_id → role_id; granted on completionBilling Tables
Billing is backed by an immutable double-entry ledger. Every financial fact is a journal entry. Derived state (entitlements, subscription status) is always recomputable from the ledger.
billing_events -- raw payment gateway events (append-only)
payment_intents -- NMI/value.io intent lifecycle
billing_subscriptions -- server × user; plan, status, period_end
ledger_accounts -- chart of accounts (revenue, receivable, payable, etc.)
journal_entries -- balanced header (metadata, idempotency key)
postings -- debit/credit rows; always sum to zero per journal entry
refunds -- links to original payment_intent; posts reversal entries
disputes -- chargeback lifecycle; posts chargeback + reserve entries
payouts -- seller payout batches; links to posted entries
affiliate_commissions -- referral commissions; links to originating payment
products -- sellable product catalog per server
plans -- recurring plan definitions (price, interval, currency)
entitlements -- effective capability grants derived from subscriptionsZero Publication Rules
Messages and related rows within the 90-day window are published via zero-cache to client replicas. Clients receive live push updates and execute all paging queries locally.
Messages older than 90 days are served through an authorized read-only history API. Archive rows are never promoted into fake Zero rows and never inserted into the client replica. The boundary is a hard architectural seam.
All billing tables are server-side only. Zero publishes only the safe effective entitlements view. Ledger accounts, journal entries, postings, and payment intents never appear in the Zero publication.