Database Reference

This page lists Comms's domain tables. All tables live in your own Supabase project — not ARK's. RLS is enabled on every table.

Shared platform tables

profiles — user metadata.
workspaces — tenant boundary.
workspace_members — user ↔ workspace with role.
audit_log — who did what and when.

Comms domain tables

`channels`

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid FK → workspaces
`name`	text	Lowercased, unique per workspace.
`kind`	text	`public`, `private`, `dm`, `group_dm`, `inbox`.
`purpose`	text
`retention_policy`	text	`keep_all`, `30d`, `90d`, `180d`, `365d`, `custom`.
`retention_days`	int	Used when policy = `custom`.
`is_archived`	bool
`created_by`	uuid FK → auth.users
`created_at`	timestamptz

`channel_members`

Column	Type	Notes
`channel_id`	uuid FK → channels
`user_id`	uuid FK → auth.users
`role`	text	`admin`, `member`, `guest`.
`notification_pref`	text	`all`, `mentions`, `none`.
`last_read_at`	timestamptz	For unread calculation.
`joined_at`	timestamptz

`messages`

Column	Type	Notes
`id`	uuid PK
`channel_id`	uuid FK → channels
`thread_id`	uuid FK → messages	Nullable — points to the parent message of a thread.
`sender_id`	uuid FK → auth.users
`kind`	text	`text`, `email`, `file`, `system`, `poll`.
`body`	text	Markdown.
`rich`	jsonb	Structured representation (blocks, attachments, etc.).
`email_headers`	jsonb	Headers for email-kind messages.
`reply_to_id`	uuid FK → messages	For email threading.
`edited_at`	timestamptz	Null if never edited.
`deleted_at`	timestamptz	Soft delete.
`created_at`	timestamptz

`message_reactions`

Column	Type	Notes
`message_id`	uuid FK → messages
`user_id`	uuid FK → auth.users
`emoji`	text	Shortcode.
`created_at`	timestamptz

`message_pins`

Column	Type	Notes
`channel_id`	uuid FK → channels
`message_id`	uuid FK → messages
`pinned_by`	uuid FK → auth.users
`pinned_at`	timestamptz

`mail_accounts`

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid FK → workspaces
`channel_id`	uuid FK → channels	Inbox channel.
`local_part`	text	e.g. `support`.
`domain`	text	Matches `COMMS_INBOUND_EMAIL_DOMAIN`.
`signature`	text
`auto_rules`	jsonb	Routing rules.

`mail_assignments`

Column	Type	Notes
`message_id`	uuid FK → messages
`assigned_to`	uuid FK → auth.users
`status`	text	`open`, `snoozed`, `resolved`.
`snooze_until`	timestamptz

`huddles`

Column	Type	Notes
`id`	uuid PK
`channel_id`	uuid FK → channels
`started_by`	uuid FK → auth.users
`started_at`	timestamptz
`ended_at`	timestamptz

`huddle_participants`

Column	Type	Notes
`huddle_id`	uuid FK → huddles
`user_id`	uuid FK → auth.users
`joined_at`	timestamptz
`left_at`	timestamptz

`channel_links` (Docs View)

Column	Type	Notes
`id`	uuid PK
`channel_id`	uuid FK → channels
`message_id`	uuid FK → messages	Source message.
`url`	text	Canonicalized.
`title`	text	OG title.
`preview`	jsonb	OG preview data.
`shared_by`	uuid FK → auth.users
`is_pinned`	bool
`shared_at`	timestamptz

`channel_tasks`

Column	Type	Notes
`id`	uuid PK
`channel_id`	uuid FK → channels
`source_message_id`	uuid FK → messages	Nullable.
`title`	text
`description`	text
`assigned_to`	uuid FK → auth.users
`due_date`	date
`status`	text	`open`, `in_progress`, `done`.
`created_by`	uuid FK → auth.users
`created_at`	timestamptz

`notification_rules` and `notification_events`

User-level rules (keywords, schedule, DND) and a log of dispatched notifications. Referenced by comms-notification-dispatcher.

`search_index`

Materialized tsvector + trigram index covering messages, email bodies, file names, and task titles. See Channels for syntax.

Relationships at a glance

workspaces
 └─ channels
     ├─ channel_members
     ├─ messages
     │   ├─ message_reactions
     │   ├─ message_pins
     │   └─ channel_links
     ├─ channel_tasks
     ├─ huddles
     │   └─ huddle_participants
     └─ mail_accounts
         └─ mail_assignments

RLS summary

Channel-scoped tables: rows visible if user_id is a member of the channel (via channel_members).
Workspace-scoped tables: rows visible if user_id is a workspace member.
Edge Functions use the service-role client for fan-out writes (inbound email, notifications, huddle signaling).

Database Reference

Shared platform tables

Comms domain tables

channels

channel_members

messages

message_reactions

message_pins

mail_accounts

mail_assignments

huddles

huddle_participants

channel_links (Docs View)

channel_tasks

notification_rules and notification_events

search_index