Tables

One page per database table. Each page covers the table's purpose, its columns, key constraints, and which backend features write to or read from it.

Phase 0 note: individual table pages to be generated during baseline generation.

Table inventory

Tables are defined in internal/database/migrations/. The schema uses two schemas: public (default) and private (sensitive/internal data).

Public schema

Table	Purpose
organizations	Org records — the top-level multi-tenant unit
users	User accounts
user_organizations	Many-to-many: user ↔ org membership with role
user_sessions	Active session tracking
rooms	Virtual room records
room_types	Room type catalogue
room_presence	Live participant presence state per room
valid_room_combinations	Allowed room type configurations
recordings	Recording metadata (egress ID, status, storage path)
recording_participants	Participants present during a recording
integrations	Available third-party integration definitions
user_integrations	Per-user integration connections (e.g. Google Calendar)
guests	Guest (unauthenticated) access records
subscription_link	Links orgs to their WorkOS subscription/plan; remains the signup/admin authorization gate until Stripe lands

Private schema

Table	Purpose
private.chat_conversations	Chat conversation records (DM, group, channel); a dm_key unique index makes DM-open race-safe
private.chat_conversation_members	Conversation membership — role, read cursor, notification level, mute, star, and per-member history window
private.chat_messages	Individual chat messages — app-generated UUIDv7 public_id, client-message-id idempotency, mentions, pin/edit/soft-delete flags, and a stored FTS vector (content_tsv)
private.chat_reactions	Emoji reactions on messages
private.chat_attachments	File attachments on messages
private.centrifugo_outbox	Transactional outbox shared by chat and presence — one row per fan-out event; a LISTEN/NOTIFY trigger (centrifugo_outbox) wakes Centrifugo's PostgreSQL consumer, which delivers and deletes each row
private.user_presence	Per-(user, org) presence status; liveness is polled by the backend against Centrifugo (no client heartbeat)
private.participant_activity_metrics	Room activity metrics per participant
private.event_cursors	WorkOS event stream cursor (used by eventsync worker)
private.event_inbox	Deduplicated inbound WorkOS events (used by eventsync worker)

Removed in migration 000032 (N1): the org_licenses and user_licenses tables and the user_organizations.license_id FK column were dropped along with all backend license code. subscription_link was intentionally left in place.

Rebuilt in migration 000039, renamed in 000042: the Supabase-era private.chat_* tables were dropped and recreated for the Centrifugo transport — the old Realtime broadcast triggers gave way to the transactional outbox. chat_user_presence was later renamed to private.user_presence (its client heartbeat_at column dropped; liveness is now backend-polled), and chat_outbox to private.centrifugo_outbox (now shared with presence).