Chat Database Schema

This document covers the PostgreSQL schema supporting episode chat, reactions, read tracking, and guest access.

Schema Overview

Enum Types

episode_message_type

CREATE TYPE episode_message_type AS ENUM (
  'chat',      -- Regular chat message
  'activity',  -- System activity (joined, edited section)
  'note'       -- Sticky note/reminder
);

Tables

episode_messages

Real-time chat messages for episode collaboration.

Column	Type	Description
id	UUID	Primary key (default gen_random_uuid())
episode_id	UUID	Foreign key to episodes
user_id	UUID	Sender (authenticated team member, nullable)
guest_id	UUID	Sender (guest portal user, nullable)
message_type	ENUM	chat, activity, or note
sender_name	TEXT	Snapshot of sender display name
sender_avatar_url	TEXT	Optional avatar URL
content	TEXT	Message text content
activity_data	JSONB	Metadata for activity messages
mentions	JSONB	Array of MentionData objects
is_pinned	BOOLEAN	Whether the message is pinned
is_system	BOOLEAN	Whether the message is system-generated
created_at	TIMESTAMPTZ	Creation timestamp
edited_at	TIMESTAMPTZ	Last edit timestamp (reserved for future use)

Constraints:

• Either user_id or guest_id must be set (not both)

Realtime: Enabled via ALTER PUBLICATION supabase_realtime ADD TABLE episode_messages;

Mentions format:

[
	{ "id": "user-uuid", "name": "Alice", "type": "team" },
	{ "id": "guest-uuid", "name": "Bob", "type": "guest" }
]

message_reactions

Emoji reactions on chat messages.

Column	Type	Description
id	UUID	Primary key
message_id	UUID	Foreign key to episode_messages
user_id	UUID	Reactor (authenticated user, nullable)
guest_id	UUID	Reactor (guest, nullable)
emoji	TEXT	Emoji character (e.g., 👍)
created_at	TIMESTAMPTZ	When the reaction was added

Unique constraint: One reaction per emoji per user/guest per message.

episode_chat_reads

Tracks last-read timestamp per user per episode for unread counting.

Column	Type	Description
id	UUID	Primary key
episode_id	UUID	Foreign key to episodes
user_id	UUID	Foreign key to auth.users
last_read_at	TIMESTAMPTZ	When the user last viewed the chat

Unique constraint: One record per user per episode. Upserted on read.

RLS Policies

episode_messages

-- Team members can view messages for their podcast's episodes
CREATE POLICY "Team can view episode messages"
ON episode_messages FOR SELECT
USING (is_episode_team_member(episode_id));

-- Team members can insert messages
CREATE POLICY "Team can send messages"
ON episode_messages FOR INSERT
WITH CHECK (is_episode_team_member(episode_id) AND user_id = auth.uid());

-- Users can delete their own messages
CREATE POLICY "Users can delete own messages"
ON episode_messages FOR DELETE
USING (user_id = auth.uid());

Guest Access

Guests cannot access episode_messages directly via RLS. All guest operations use SECURITY DEFINER RPCs that validate the guest's access_token before performing operations.

message_reactions

-- Team members can view reactions on messages they can see
CREATE POLICY "Team can view reactions"
ON message_reactions FOR SELECT
USING (EXISTS (
  SELECT 1 FROM episode_messages em
  WHERE em.id = message_id
  AND is_episode_team_member(em.episode_id)
));

-- Team members can add reactions
CREATE POLICY "Team can add reactions"
ON message_reactions FOR INSERT
WITH CHECK (user_id = auth.uid());

-- Users can remove their own reactions
CREATE POLICY "Team can remove own reactions"
ON message_reactions FOR DELETE
USING (user_id = auth.uid());

SECURITY DEFINER RPCs (Guest Access)

All guest operations bypass RLS via these functions. Each validates the guest's access_token before proceeding.

guest_send_message

Inserts a message on behalf of a guest.

CREATE OR REPLACE FUNCTION guest_send_message(
  p_episode_id UUID,
  p_access_token TEXT,
  p_content TEXT,
  p_sender_name TEXT,
  p_sender_avatar_url TEXT DEFAULT NULL,
  p_mentions JSONB DEFAULT '[]'::JSONB
) RETURNS JSONB
LANGUAGE plpgsql SECURITY DEFINER

Validation:

• Verifies access_token matches an active episode_guests record
• Enforces rate limiting (checks recent message count)
• Returns the created message as JSONB

guest_delete_message

Deletes a message owned by the guest.

CREATE OR REPLACE FUNCTION guest_delete_message(
  p_message_id UUID,
  p_access_token TEXT
) RETURNS VOID
LANGUAGE plpgsql SECURITY DEFINER

guest_fetch_reactions

Fetches reaction summaries for a set of messages, with userReacted resolved for the guest.

CREATE OR REPLACE FUNCTION guest_fetch_reactions(
  p_message_ids UUID[],
  p_access_token TEXT
) RETURNS JSONB
LANGUAGE plpgsql SECURITY DEFINER

guest_toggle_reaction

Adds or removes a reaction for a guest.

CREATE OR REPLACE FUNCTION guest_toggle_reaction(
  p_message_id UUID,
  p_emoji TEXT,
  p_access_token TEXT
) RETURNS JSONB
LANGUAGE plpgsql SECURITY DEFINER

get_guest_episode_messages

Fetches paginated messages for a guest, used both for initial load and reconnection sync.

CREATE OR REPLACE FUNCTION get_guest_episode_messages(
  p_episode_id UUID,
  p_access_token TEXT,
  p_limit INT DEFAULT 50,
  p_before_timestamp TIMESTAMPTZ DEFAULT NULL
) RETURNS JSONB
LANGUAGE plpgsql SECURITY DEFINER

guest_episode_participants

Fetches the participant roster for the episode (team members + guests).

CREATE OR REPLACE FUNCTION guest_episode_participants(
  p_episode_id UUID,
  p_access_token TEXT
) RETURNS JSONB
LANGUAGE plpgsql SECURITY DEFINER

Triggers

Rate Limiting

CREATE TRIGGER enforce_chat_message_rate_limit
  BEFORE INSERT ON episode_messages
  FOR EACH ROW
  EXECUTE FUNCTION check_message_rate_limit();

The check_message_rate_limit() function counts recent messages from the same user within a time window and raises an exception with format RATE_LIMITED retry_after=60 if exceeded.

Migrations

Migration	Purpose
20260119185239	Core episode_messages table
20260224120945	guest_delete_message RPC
20260224120952	guest_episode_participants RPC
20260224120956	Add mentions JSONB column to episode_messages
20260224121000	Update guest_send_message to accept p_mentions
20260224130256	Fix column ambiguity in participants query
20260224161954	Add mentions to get_guest_episode_messages response
20260224163332	Fix guest messages limit parameter

Related Documentation

• Chat Overview
• Real-Time Architecture - Subscription model and broadcasts
• Collaboration Database Schema - Show notes tables (separate concern)