Multi-Tenancy Architecture

show.fm uses a sophisticated multi-tenant architecture that separates authorization, attribution, participation, and guest access into distinct tables. This design supports complex real-world scenarios like team changes, historical credits, and guest collaboration.

Core Philosophy

┌─────────────────────────────────────────────────────────────────────┐
│                    MULTI-TABLE PERMISSION MODEL                      │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  ┌─────────────────────┐                                            │
│  │   podcast_members   │  Authorization - Who can access NOW        │
│  │   (The "Now")       │  DELETE row = instant access revocation    │
│  └──────────┬──────────┘                                            │
│             │                                                        │
│             ▼                                                        │
│  ┌─────────────────────┐      ┌─────────────────────┐              │
│  │   episode_people    │─────▶│   episode_credits   │              │
│  │  (Participation)    │      │   (The "Forever")   │              │
│  │  Who is active on   │      │   RSS attribution   │              │
│  │  this episode       │      │   (via credit_id)   │              │
│  └──────────┬──────────┘      └─────────────────────┘              │
│             │                                                        │
│             ▼                                                        │
│  ┌─────────────────────┐                                            │
│  │   episode_guests    │  Portal Access - Magic link authentication │
│  │   (The "Temporary") │  Expires when episode is archived          │
│  └─────────────────────┘                                            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Table Definitions

podcast_members (Authorization)

Controls who can access the podcast dashboard right now.

Column	Type	Purpose
podcast_id	UUID	Podcast being accessed
user_id	UUID	Supabase auth user
role	podcast_role	Permission level
invited_by	UUID	Who invited this member
joined_at	TIMESTAMPTZ	When access was granted

Roles (ordered by permission level):

CREATE TYPE podcast_role AS ENUM ('member', 'admin', 'owner');

Role	Dashboard	Edit Episodes	Publish	Manage Team	Billing	Delete Podcast
member	Rostered episodes only (#293)	No (read-only + carve-outs)	No	No	No	No
admin	Full (analytics preview only)	Yes	Yes	Yes	No	No
owner	Full	Yes	Yes	Yes	Yes	Yes

episode_credits (Attribution)

Controls who appears in episode history forever. Persists even if the user:

• Is removed from podcast_members
• Deletes their account
• Changes their display name

Column	Type	Purpose
episode_id	UUID	Episode being credited
user_id	UUID	Optional link to user (NULL if deleted)
name	TEXT	Snapshot of name at credit time
role_label	TEXT	Display text: "Host", "Co-host", "Guest"
bio	TEXT	Bio for this specific appearance
display_order	INT	Order in credits list

Key Design Decisions:

1. user_id can be NULL: If a user deletes their account, their credits remain with name/role intact
2. name is a snapshot: Changing your display name doesn't affect historical credits
3. Immutable history: RSS feeds and public pages show accurate historical attribution

episode_people (Participation)

Tracks who is active on an episode. Bridges participation and RSS attribution by linking to episode_credits via credit_id. Managed through the Episode People API.

Column	Type	Purpose
episode_id	UUID	Episode being participated in
source_type	TEXT	Origin: team, guest, or external
user_id	UUID	Link to team member (if team source)
guest_id	UUID	Link to episode guest (if guest source)
name	TEXT	Display name (snapshot)
role_label	TEXT	Role: "Host", "Co-host", "Producer", etc.
is_active	BOOLEAN	Whether actively participating
credit_id	UUID	Link to episode_credits (NULL = not in RSS)

Key Design Decisions:

1. Separation of concerns: A person can be active on an episode (is_active: true) without appearing in RSS credits (credit_id: null)
2. Three source types: Team members come from podcast_members, guests from episode_guests, externals are manually entered
3. Credit metadata override: RSS credit name/role can differ from roster entry (e.g., display name vs. legal name)
4. The row is the access grant, so the two halves are surfaced apart (2026-08-10): writing a team row is what makes an episode visible to a Co-host, so episode access is granted only from the Collaborators tab. The Publish tab's RSS credits card reads the same rows but can only create external people, who have no account and gain nothing. A UI that lets "add a credit" also grant access is a permission bug wearing a layout costume.
5. Credits default ON for team, OFF for guests: adding a team member (API or the auto-add trigger) creates the credit too, because the host has decided they were on the episode; a guest can still decline, so their credit waits for ensure_guest_credit_on_activation. Imported episodes (episodes.origin = 'import') never gain a show.fm-authored credit at all, though they do keep roster rows, because those drive notification fan-out. See Episode People API.

episode_guests (Portal Access)

Controls guest access to the collaboration portal via magic links.

Column	Type	Purpose
episode_id	UUID	Episode being accessed
user_id	UUID	Optional link to user
email	TEXT	Guest's email address
access_token	TEXT	Magic link token
status	guest_status	Access state
token_expires_at	TIMESTAMPTZ	Optional expiration

Status Lifecycle:

CREATE TYPE guest_status AS ENUM ('invited', 'active', 'completed', 'expired');

invited → active → completed
    │
    └→ expired (if token_expires_at passed)

Scenario Walkthroughs

Scenario A: Bill Transitions from Co-host to Guest

Step 1: Bill is an active Co-host

podcast_members:  { podcast_id, bill_user_id, 'member' }  ✅
episode_credits:  { ep_1-10, bill_user_id, "Bill", "Co-host" }  ✅
episode_guests:   (empty)

Step 2: Bill leaves the show

podcast_members:  (row DELETED) ❌
episode_credits:  { ep_1-10, bill_user_id, "Bill", "Co-host" }  ✅ (unchanged!)

Result: Bill immediately loses dashboard access. RSS feed still shows "Co-host: Bill" on episodes 1-10.

Step 3: Bill returns as Guest on Episode 20

podcast_members:  (still no row - no dashboard access)
episode_guests:   { ep_20, bill_user_id, "[email protected]", token123, 'active' }  ✅
episode_credits:  { ep_20, bill_user_id, "Bill", "Guest" }  ✅

Result: Bill can access episode 20 via magic link. Episodes 1-10 show "Co-host", episode 20 shows "Guest".

Scenario B: User Across Multiple Podcasts

Bob can have different roles on different podcasts:

podcast_members:
  { gemini_podcast, bob_id, 'owner' }     -- Bob owns Gemini Podcast
  { chatgpt_podcast, bob_id, 'member' }   -- Bob is member on ChatGPT Podcast

Bob sees both podcasts in the podcast picker but has different permissions on each.

RLS Helper Functions

All helper functions use SECURITY DEFINER to access podcast_members without triggering RLS recursion.

get_podcast_role(podcast_id uuid)

Returns the authenticated user's role for a podcast, or NULL if not a member.

CREATE OR REPLACE FUNCTION get_podcast_role(lookup_podcast_id UUID)
RETURNS podcast_role AS $$
  SELECT role FROM podcast_members
  WHERE podcast_id = lookup_podcast_id
  AND user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Usage in RLS:

CREATE POLICY "Members can view episodes"
ON episodes FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

has_podcast_role(podcast_id uuid, min_role podcast_role)

Returns TRUE if user has at least the specified role level.

CREATE OR REPLACE FUNCTION has_podcast_role(lookup_podcast_id UUID, min_role podcast_role)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM podcast_members
    WHERE podcast_id = lookup_podcast_id
    AND user_id = auth.uid()
    AND role >= min_role  -- Relies on enum ordering
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Usage in RLS:

CREATE POLICY "Admins can publish"
ON episodes FOR UPDATE
USING (has_podcast_role(podcast_id, 'admin'));

is_podcast_owner(podcast_id uuid)

Returns TRUE if user is the owner.

CREATE OR REPLACE FUNCTION is_podcast_owner(lookup_podcast_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM podcast_members
    WHERE podcast_id = lookup_podcast_id
    AND user_id = auth.uid()
    AND role = 'owner'
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

get_user_podcast_ids()

Returns all podcast IDs the user has access to.

CREATE OR REPLACE FUNCTION get_user_podcast_ids()
RETURNS SETOF UUID AS $$
  SELECT podcast_id FROM podcast_members
  WHERE user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Usage in RLS:

CREATE POLICY "Users can view their podcasts"
ON podcasts FOR SELECT
USING (id IN (SELECT get_user_podcast_ids()));

get_episode_podcast_id(episode_id uuid)

Gets the podcast_id for an episode. Uses SECURITY DEFINER to prevent RLS recursion when checking episode_guests access.

CREATE OR REPLACE FUNCTION get_episode_podcast_id(lookup_episode_id UUID)
RETURNS UUID AS $$
  SELECT podcast_id FROM episodes WHERE id = lookup_episode_id
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Why needed: When episode_guests RLS checks get_podcast_role(episodes.podcast_id), it needs to read the episode without triggering the episode's RLS policy.

is_on_episode_roster(episode_id uuid) (#293)

Returns TRUE when the authenticated user holds an ACTIVE episode_people team row on the episode: the roster grant primitive.

CREATE OR REPLACE FUNCTION is_on_episode_roster(lookup_episode_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM episode_people
    WHERE episode_id = lookup_episode_id
    AND user_id = auth.uid()
    AND is_active = TRUE
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

is_episode_team_member(episode_id uuid) (redefined by #293)

The single lever that flips the show-notes family (show_notes, show_note_sections, episode_messages, show_note_presence) and other episode-scoped policies to roster scoping. Admin+ passes unconditionally; a member passes only with a roster grant:

-- Body shape (see migration 20260808110000 for the authoritative text):
has_podcast_role(get_episode_podcast_id(lookup_episode_id), 'admin')
OR (
  get_podcast_role(get_episode_podcast_id(lookup_episode_id)) IS NOT NULL
  AND is_on_episode_roster(lookup_episode_id)
)

The Episode Roster Grant (#293)

Role sets the ceiling; the roster grants the episode. Roles stay owner/admin/member (Host/Producer/Co-host); a Co-host's episode surface is exactly the union of their ACTIVE episode_people team rows. Host/Producer rights are roster-independent (their roster rows drive avatars, calendar attendees, notifications, and automation context only). The full capability matrix lives in docs/planning/roles-permissions-matrix.md.

Every visibility change lands in BOTH planes:

1. /api/* (Hono) runs a service-role client. RLS is bypassed and the middleware chain is that plane's entire boundary. The roster-aware guard is requirePodcastRoleOrEpisodeRoster(episodeResolver) (src/api/middleware/permissions.ts): passes admin+, or a member holding an active roster row on the resolved episode; a missing episode 404s. It sets podcastRole, podcastId, and episodeId on context and preserves the lifecycle block.
2. Page loads + browser clients hit PostgREST with RLS live. Member-facing episode loads additionally run requireEpisodeRosterForMember() (src/lib/server/require-podcast-access.ts): the episode read alone is NOT a roster check, because the guest SELECT arm of episodes RLS passes a member who is ALSO an episode guest while holding no roster row (the full write-up lives in src/lib/server/workspace-episode.ts).

Related #293 boundaries:

• Analytics is owner-only on every endpoint; Producers and Co-hosts render AnalyticsPreviewPanel from a static sample dataset, so no live data reaches their clients.
• Host-private guest notes (podcast_guest_private_notes) split out of podcast_guests with admin-only RLS on all four commands; Co-hosts keep base-table read.
• Transcript access per episode via episodes.cohost_transcript_access (editable/read_only/hidden), binding member-role users in both planes.
• Auto-add: podcast_members.auto_add_to_new_episodes applied by the auto_add_team_on_episode_create trigger on every episode INSERT (its in-app notification is exception-guarded so it can never abort the episode write).

RLS Policy Patterns

Cascading Access

Access to a podcast grants access to all child resources:

-- Team can view episodes
CREATE POLICY "Team can view episodes"
ON episodes FOR SELECT
USING (get_podcast_role(podcast_id) IS NOT NULL);

-- Team can view bookings
CREATE POLICY "Team can view bookings"
ON bookings FOR SELECT
USING (get_podcast_role(
  (SELECT podcast_id FROM booking_links WHERE id = booking_link_id)
) IS NOT NULL);

Role-Based Modification

Different operations require different role levels:

-- Any member can create episodes
CREATE POLICY "Members can create episodes"
ON episodes FOR INSERT
WITH CHECK (get_podcast_role(podcast_id) IS NOT NULL);

-- Only admins can delete episodes
CREATE POLICY "Admins can delete episodes"
ON episodes FOR DELETE
USING (has_podcast_role(podcast_id, 'admin'));

-- Only owner can delete podcast
CREATE POLICY "Owner can delete podcast"
ON podcasts FOR DELETE
USING (is_podcast_owner(id));

Public Content Access

Published content is publicly accessible (for RSS feeds):

-- Public can view published episodes
CREATE POLICY "Public can view published episodes"
ON episodes FOR SELECT
USING (status = 'published');

-- Public can view credits for published episodes
CREATE POLICY "Public can view published credits"
ON episode_credits FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM episodes e
    WHERE e.id = episode_id
    AND e.status = 'published'
  )
);

Guest Portal Access

Guests can access their assigned episodes:

-- Guests can view their assigned episodes
CREATE POLICY "Guests can view assigned episodes"
ON episodes FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM episode_guests eg
    WHERE eg.episode_id = id
    AND eg.user_id = auth.uid()
    AND eg.status = 'active'
  )
);

-- Guests can view their own record
CREATE POLICY "Guests can view own record"
ON episode_guests FOR SELECT
USING (user_id = auth.uid() OR email = auth.email());

Account Linking (Guest → User)

When a guest creates a full account, their episode_guests records are automatically linked via bidirectional database triggers.

Migration: supabase/migrations/20260127000002_epic6a_account_linking_triggers.sql

Trigger 1: link_episode_guest_to_user()

Fires BEFORE INSERT on episode_guests. When a new guest record is created, this trigger checks if an existing auth.users account matches the guest's email. If found, it sets user_id automatically.

-- Simplified logic
CREATE FUNCTION link_episode_guest_to_user()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id IS NULL THEN
    SELECT id INTO NEW.user_id FROM auth.users
    WHERE lower(email) = lower(NEW.email)
      AND email_confirmed_at IS NOT NULL;   -- confirmed users only (20260906200000)
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

Only a confirmed user can claim a guest row on insert. A host inviting an address that has an unconfirmed signup leaves user_id NULL; the confirmation triggers below link it once the address is proven.

Trigger 2: link_orphaned_guests_to_user()

Fires when the user's email is confirmed: trigger_link_orphaned_guests_on_confirm (AFTER UPDATE OF email_confirmed_at, NULL to timestamp) plus trigger_link_orphaned_guests (AFTER INSERT, only for a row inserted already confirmed). Since migration 20260906200000 nothing links on a bare signup: an unconfirmed account owns nothing, so a guest record can never be claimed by an address that was not proven. The trigger links all orphaned episode_guests records that match the user's email.

-- Simplified logic
CREATE FUNCTION link_orphaned_guests_to_user()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE episode_guests
  SET user_id = NEW.id
  WHERE lower(email) = lower(NEW.email)
  AND user_id IS NULL;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

Data Flow

Security Notes

• Both triggers use SECURITY DEFINER SET search_path = public to safely query auth.users without RLS recursion
• Email matching is case-insensitive (lower())
• The migration includes a one-time backfill operation for any existing orphaned records

Context Switching (Podcast Picker)

When a user has access to multiple podcasts:

// In hooks.server.ts or layout.server.ts
const currentPodcastId = event.cookies.get('podcast_context');

if (currentPodcastId) {
	// Verify user still has access
	const { data: membership } = await supabase
		.from('podcast_members')
		.select('role')
		.eq('podcast_id', currentPodcastId)
		.eq('user_id', user.id)
		.single();

	if (!membership) {
		// User lost access - clear context and redirect
		event.cookies.delete('podcast_context');
		throw redirect(302, '/p');
	}

	event.locals.podcastRole = membership.role;
	event.locals.currentPodcastId = currentPodcastId;
}

API Permission Enforcement

Beyond RLS, the API layer should also enforce permissions:

// src/api/middleware/permissions.ts
export const requirePermission = (permission: Permission) => {
	return createMiddleware<AuthEnv>(async (c, next) => {
		const role = c.get('podcastRole');

		if (!role || !rolePermissions[role]?.includes(permission)) {
			throw new HTTPException(403, {
				message: `Permission denied: requires higher role`
			});
		}

		await next();
	});
};

// Usage in routes
export const podcastRoutes = new Hono<AuthEnv>().delete(
	'/:id',
	requirePermission('delete_podcast'),
	async (c) => {
		// Only owner reaches here
	}
);

Critical Rules

DO

• Always check podcast_members for current access
• Use episode_credits for display/attribution only
• Validate with getUser() on server
• Use helper functions in RLS policies

DON'T

• NEVER trust episode_credits for access control
• NEVER rely on UI hiding alone
• NEVER use getSession() for auth
• NEVER assume ownership from a single field

Related Documentation

• Supabase Integration
• Architecture Overview
• Guest Account Flow
• Roles & Permissions Matrix