Collaboration Database Schema

This document details the PostgreSQL schema supporting collaborative show notes in show.fm.

Schema Overview

Enum Types

show_note_section_type

Determines visibility and edit permissions for sections.

CREATE TYPE show_note_section_type AS ENUM (
  'shared',        -- Everyone can view and edit
  'host_private',  -- Host/Producer/Co-host only (hidden from guests)
  'guest'          -- Owner guest can edit, host can edit, others view only
);

show_notes_template_section

Identifies which section a notification_templates row of type show_notes is intended for. Mirrors show_note_section_type but is a distinct type so future template-only values can diverge.

CREATE TYPE show_notes_template_section AS ENUM (
  'shared',
  'host_private',
  'guest'
);

show_notes_template_mode

Per-section override mode used by booking-link columns (and in-memory by the per-episode override path).

CREATE TYPE show_notes_template_mode AS ENUM (
  'inherit',   -- Fall through to the podcast default
  'template',  -- Use the paired _template_id
  'blank'      -- Force a blank section, ignoring the podcast default
);

episode_message_type

Classifies messages in the episode chat.

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

Tables

show_notes

Parent document for collaborative show notes. One per episode.

Column	Type	Description
id	UUID	Primary key
episode_id	UUID	Foreign key to episodes (unique)
created_from_template_id	UUID	Optional template reference (the shared-section template at creation, kept as the most representative source)
created_from_guest_template_id	UUID	Snapshot of the resolved guest-section template at creation. Used by createGuestSection so guests added later render with the same template the original guests received. Validated by trigger to point at a target_section='guest' template owned by the same podcast
version	INT	Incremented on any section change
last_edited_by	UUID	User who last edited
last_edited_at	TIMESTAMPTZ	When last edited

Indexes:

• idx_show_notes_episode on episode_id
• idx_show_notes_template on created_from_template_id (partial)

See Show Notes Auto-Create & Per-Section Templates for how these columns are populated and consumed.

show_note_sections

Individual sections within a show notes document.

Column	Type	Description
id	UUID	Primary key
show_notes_id	UUID	Foreign key to show_notes
section_type	ENUM	shared, host_private, or guest
title	TEXT	Section title
content	JSONB	TipTap JSON document
owner_guest_id	UUID	For guest sections only
display_order	INT	Ordering within document
is_collapsed	BOOLEAN	UI state
last_edited_by	UUID	User who last edited
last_edited_by_name	TEXT	Snapshot of editor name

Content Format (TipTap JSON):

{
	"type": "doc",
	"content": [
		{
			"type": "paragraph",
			"content": [{ "type": "text", "text": "Sample content" }]
		}
	]
}

Constraint:

CONSTRAINT valid_guest_section CHECK (
  (section_type = 'guest' AND owner_guest_id IS NOT NULL) OR
  (section_type != 'guest' AND owner_guest_id IS NULL)
)

episode_messages

Real-time chat and activity feed for episode collaboration. For comprehensive chat documentation including reactions, mentions, and guest RPCs, see the Chat Database Schema.

Column	Type	Description
id	UUID	Primary key
episode_id	UUID	Foreign key to episodes
user_id	UUID	Sender (for team members)
guest_id	UUID	Sender (for guests)
message_type	ENUM	chat, activity, or note
sender_name	TEXT	Snapshot of sender name
sender_avatar_url	TEXT	Optional avatar
content	TEXT	Message content
activity_data	JSONB	For activity messages
mentions	JSONB	Array of MentionData objects (see Chat Schema)
is_pinned	BOOLEAN	Pinned messages stay visible
is_system	BOOLEAN	System-generated messages

Activity Data Example:

{
	"action": "edited_section",
	"section_id": "uuid",
	"section_title": "Shared Notes"
}

show_note_presence

Tracks who is currently viewing/editing show notes.

Column	Type	Description
id	UUID	Primary key
show_notes_id	UUID	Foreign key to show_notes
user_id	UUID	Team member (or guest_id)
guest_id	UUID	Guest user (or user_id)
name	TEXT	Display name
color	TEXT	Cursor color (hex)
current_section_id	UUID	Active section
cursor_position	JSONB	{ "from": 0, "to": 0 }
last_seen_at	TIMESTAMPTZ	Heartbeat timestamp

Unique Constraints:

• One presence record per user per document
• One presence record per guest per document

RLS Policies

Show Notes Access

-- Team members can view show notes for their podcasts
CREATE POLICY "Team can view show notes"
ON show_notes FOR SELECT
USING (is_episode_team_member(episode_id));

-- Active guests can view show notes for their episodes
CREATE POLICY "Guests can view assigned show notes"
ON show_notes FOR SELECT
USING (is_active_episode_guest(episode_id));

Section Visibility (Critical)

Guests have restricted visibility - they cannot see host_private sections or other guests' sections.

-- Guests can view shared sections and their own guest section
CREATE POLICY "Guests can view appropriate sections"
ON show_note_sections FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM show_notes sn
    WHERE sn.id = show_notes_id
    AND is_active_episode_guest(sn.episode_id)
  )
  AND (
    section_type = 'shared' OR
    (section_type = 'guest' AND owner_guest_id = get_my_guest_id(
      (SELECT episode_id FROM show_notes WHERE id = show_notes_id)
    ))
  )
);

Edit Permissions

Section Type	Team Members	Guest (Owner)	Guest (Other)
shared	Edit	Edit	View
host_private	Edit	Hidden	Hidden
guest	Edit	Edit (own only)	Hidden

Helper Functions

is_episode_team_member(episode_id UUID)

Checks if the current user is a team member for an episode's podcast.

CREATE OR REPLACE FUNCTION is_episode_team_member(episode_id_param UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM episodes e
    JOIN podcast_members pm ON pm.podcast_id = e.podcast_id
    WHERE e.id = episode_id_param
    AND pm.user_id = auth.uid()
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

is_active_episode_guest(episode_id UUID)

Checks if the current user is an active guest for an episode.

CREATE OR REPLACE FUNCTION is_active_episode_guest(episode_id_param UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM episode_guests
    WHERE episode_id = episode_id_param
    AND (user_id = auth.uid() OR email = auth.email())
    AND status = 'active'
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

get_my_guest_id(episode_id UUID)

Returns the guest ID for the current user on an episode.

CREATE OR REPLACE FUNCTION get_my_guest_id(episode_id_param UUID)
RETURNS UUID AS $$
  SELECT id FROM episode_guests
  WHERE episode_id = episode_id_param
  AND (user_id = auth.uid() OR email = auth.email())
  AND status = 'active'
  LIMIT 1
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Show Notes Template Plumbing

Per-section show-notes templates ride on existing tables (notification_templates, podcasts, booking_links) rather than a dedicated table. Three pieces of schema connect them:

notification_templates.target_section

Templates of template_type='show_notes' carry a target_section so each template is scoped to exactly one of the three section kinds:

ALTER TABLE notification_templates
  ADD COLUMN target_section show_notes_template_section NULL;

ALTER TABLE notification_templates
  ADD CONSTRAINT notification_templates_target_section_consistency CHECK (
    (template_type = 'show_notes') = (target_section IS NOT NULL)
  );

CREATE INDEX idx_notification_templates_section
  ON notification_templates (podcast_id, target_section)
  WHERE template_type = 'show_notes';

The CHECK constraint enforces "required iff template_type='show_notes'". The API additionally rejects mutating target_section after creation, since dependent FKs (below) rely on it staying stable.

podcasts.default_<section>_template_id

Three nullable columns on podcasts hold the host's per-section defaults:

Column	Section
default_shared_template_id	shared
default_host_private_template_id	host_private
default_guest_template_id	guest

Each REFERENCES notification_templates(id) ON DELETE SET NULL and is validated by trigger to point at a template with the matching target_section and the same podcast_id.

booking_links.show_notes_<section>_mode + _template_id

Six columns on booking_links provide per-section overrides:

Column	Type	Default
show_notes_shared_mode	show_notes_template_mode	'inherit'
show_notes_shared_template_id	UUID NULL	NULL
show_notes_host_private_mode	show_notes_template_mode	'inherit'
show_notes_host_private_template_id	UUID NULL	NULL
show_notes_guest_mode	show_notes_template_mode	'inherit'
show_notes_guest_template_id	UUID NULL	NULL

Per-pair CHECK constraints enforce that _template_id IS NOT NULL iff _mode = 'template':

CONSTRAINT booking_links_show_notes_shared_consistency CHECK (
  (show_notes_shared_mode = 'template') = (show_notes_shared_template_id IS NOT NULL)
)
-- (parallel constraints for host_private and guest)

The booking-links API mirrors these constraints with a Zod superRefine so client errors return 400 from validation rather than 500 from a constraint violation.

Triggers

Show Notes Template Section Match

enforce_show_notes_template_section_match() validates every default_*_template_id on podcasts and every show_notes_*_template_id on booking_links points to a notification_templates row whose target_section matches the column and whose podcast_id matches the row's owning podcast. A plain FK can guarantee existence but cannot validate either invariant.

CREATE OR REPLACE FUNCTION enforce_show_notes_template_section_match()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- required: trigger reads RLS-protected notification_templates
SET search_path = public
AS $$
  -- … RAISE EXCEPTION 'must reference a template with target_section=…'
  -- … RAISE EXCEPTION 'must reference a template owned by this podcast'
$$;

CREATE TRIGGER enforce_podcast_show_notes_template_sections
  BEFORE INSERT OR UPDATE OF
    default_shared_template_id,
    default_host_private_template_id,
    default_guest_template_id
  ON podcasts
  FOR EACH ROW EXECUTE FUNCTION enforce_show_notes_template_section_match();

CREATE TRIGGER enforce_booking_link_show_notes_template_sections
  BEFORE INSERT OR UPDATE OF
    show_notes_shared_template_id,
    show_notes_host_private_template_id,
    show_notes_guest_template_id
  ON booking_links
  FOR EACH ROW EXECUTE FUNCTION enforce_show_notes_template_section_match();

SECURITY DEFINER is required because the trigger reads notification_templates, which has RLS. We bypass RLS so validation works regardless of the calling user's read access. See the Supabase rules for the broader recursion-prevention pattern.

Show Notes Guest Template Snapshot Match

enforce_show_notes_guest_template_match() validates show_notes.created_from_guest_template_id on insert/update: it must reference a template with target_section='guest' belonging to the same podcast as the linked episode (resolved via episodes.podcast_id).

CREATE TRIGGER enforce_show_notes_guest_template
  BEFORE INSERT OR UPDATE OF created_from_guest_template_id, episode_id
  ON show_notes
  FOR EACH ROW EXECUTE FUNCTION enforce_show_notes_guest_template_match();

Version Tracking

Automatically increments show_notes.version when any section changes.

CREATE TRIGGER trigger_update_show_notes_on_section_change
  AFTER INSERT OR UPDATE OR DELETE ON show_note_sections
  FOR EACH ROW
  EXECUTE FUNCTION update_show_notes_on_section_change();

Edit Timestamp

Automatically updates last_edited_at when section content changes.

CREATE TRIGGER trigger_section_edited_at
  BEFORE UPDATE OF content ON show_note_sections
  FOR EACH ROW
  EXECUTE FUNCTION update_section_edited_at();

Presence Cleanup

Function to clean up stale presence records (called periodically or on demand).

CREATE OR REPLACE FUNCTION cleanup_stale_presence()
RETURNS void AS $$
BEGIN
  DELETE FROM show_note_presence
  WHERE last_seen_at < NOW() - INTERVAL '5 minutes';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Realtime Subscriptions

These tables are enabled for Supabase Realtime:

ALTER PUBLICATION supabase_realtime ADD TABLE show_note_sections;
ALTER PUBLICATION supabase_realtime ADD TABLE episode_messages;
ALTER PUBLICATION supabase_realtime ADD TABLE show_note_presence;

Migration Files

The schema was applied across multiple incremental migrations:

Migration	Purpose
20260119185222	Core tables (show_notes, show_note_sections)
20260119185239	Episode messages table
20260119185248	Presence tracking table
20260119185302	Helper functions
20260119185314-40	RLS policies
20260119185355	Triggers
20260119185406	Realtime subscriptions
20260119190352	Show notes template type
20260427101943	Show notes auto-create: per-section enums, notification_templates.target_section, podcast defaults, booking-link overrides, and the enforce_show_notes_template_section_match trigger
20260427160743	show_notes.created_from_guest_template_id snapshot column and enforce_show_notes_guest_template_match trigger

Related Documentation

• Collaboration Overview
• Show Notes Auto-Create & Per-Section Templates
• Collaboration Provider
• Chat Database Schema - Message reactions, read tracking, guest RPCs
• Multi-Tenancy Model
• Automation Database Schema