conversations

Overview

The conversations table stores chat conversation metadata. Conversations can be either direct messages (type='dm') between two users or group chats (type='group') with a name. DM conversations are created via the get_or_create_dm() helper function which prevents duplicates.

Relevant Invariants

• Invariant #9: "Time Is Not Weaponized" -- No read receipts, typing indicators, or "last seen" on conversations

Schema

-- From 20260131_messaging_system.sql
CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type TEXT NOT NULL DEFAULT 'dm' CHECK (type IN ('dm', 'group')),
  name TEXT,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	gen_random_uuid()	Primary key
type	text	No	'dm'	Conversation type: dm or group
name	text	Yes	--	Display name (group chats only)
created_by	uuid	Yes	--	User who created the conversation
created_at	timestamptz	No	NOW()	Creation timestamp
updated_at	timestamptz	No	NOW()	Last activity timestamp

RLS Policies

-- SELECT: Participants can view their conversations
CREATE POLICY "Participants can view conversations"
  ON conversations FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM conversation_participants
    WHERE conversation_participants.conversation_id = conversations.id
    AND conversation_participants.user_id = auth.uid()
  ));

-- INSERT: Authenticated users can create conversations
CREATE POLICY "Users can create conversations"
  ON conversations FOR INSERT
  WITH CHECK (auth.uid() = created_by);

Related

• conversation_participants -- Members of the conversation
• messages -- Messages within the conversation

---

Last updated: 2026-02-07