messages

Overview

The messages table stores individual messages within conversations. Messages support text content, reply threading (via reply_to_id), and configurable data retention with automatic expiration. Messages can be set to auto-delete after a configured number of days.

Relevant Invariants

• Invariant #14: "Privacy Is Infrastructure" -- Message retention and auto-deletion are built in
• Invariant #9: "Time Is Not Weaponized" -- No read receipts on messages

Schema

-- From 20260131_messaging_system.sql + 20260201_message_replies.sql + 20260205_data_retention.sql
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  content TEXT NOT NULL CHECK (char_length(content) <= 4000),
  type TEXT DEFAULT 'text' CHECK (type IN ('text', 'system')),
  edited_at TIMESTAMPTZ DEFAULT NULL,
  original_content TEXT DEFAULT NULL,
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  -- Reply threading (20260201_message_replies.sql)
  reply_to_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  -- Data retention (20260205_data_retention.sql)
  expires_at TIMESTAMPTZ,
  retention_days INTEGER
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	gen_random_uuid()	Primary key
conversation_id	uuid	No	--	Parent conversation
sender_id	uuid	Yes	--	Message author (FK to profiles, ON DELETE SET NULL)
content	text	No	--	Message text (max 4000 chars)
type	text	No	'text'	Message type: 'text' or 'system'
edited_at	timestamptz	Yes	--	When message was last edited
original_content	text	Yes	--	Original content before editing
deleted_at	timestamptz	Yes	--	Soft-deletion timestamp
created_at	timestamptz	No	NOW()	Send timestamp
reply_to_id	uuid	Yes	--	Message being replied to
expires_at	timestamptz	Yes	--	Auto-deletion timestamp
retention_days	integer	Yes	--	Days until auto-deletion

RLS Policies

-- SELECT: Conversation participants can read messages
-- Uses is_conversation_participant() SECURITY DEFINER helper
CREATE POLICY "Users can view messages"
  ON messages FOR SELECT TO authenticated
  USING (is_conversation_participant(conversation_id, auth.uid()));

-- INSERT: Participants can send messages
CREATE POLICY "Users can send messages"
  ON messages FOR INSERT TO authenticated
  WITH CHECK (
    sender_id = auth.uid()
    AND is_conversation_participant(conversation_id, auth.uid())
  );

-- UPDATE: Sender can edit their own messages
CREATE POLICY "Users can edit own messages"
  ON messages FOR UPDATE TO authenticated
  USING (sender_id = auth.uid());

Related

• conversations -- Parent conversation
• conversation_participants -- Who can see these messages
• message_reactions -- Reactions to messages
• profiles -- Message sender

---

Last updated: 2026-02-07