Skip to content

Supabase Integration

This document details the Supabase integration for show.fm, including database schema, authentication patterns, RLS policies, and client initialization.

Architecture Overview

show.fm uses Supabase as the complete backend solution:

┌─────────────────────────────────────────────────────────────────────┐
│                        APPLICATION LAYER                             │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐     │
│  │   SvelteKit     │  │   Hono API      │  │   CF Workers    │     │
│  │   (Browser)     │  │   (/api/*)      │  │   (RSS, Queue)  │     │
│  └────────┬────────┘  └────────┬────────┘  └────────┬────────┘     │
│           │                    │                    │               │
│           ▼                    ▼                    ▼               │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐     │
│  │ Browser Client  │  │  Admin Client   │  │   Hyperdrive    │     │
│  │ @supabase/ssr   │  │ @supabase/js    │  │   postgres.js   │     │
│  └────────┬────────┘  └────────┬────────┘  └────────┬────────┘     │
│           │                    │                    │               │
└───────────┼────────────────────┼────────────────────┼───────────────┘
            │                    │                    │
            ▼                    ▼                    ▼
┌─────────────────────────────────────────────────────────────────────┐
│                         SUPABASE LAYER                               │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐     │
│  │   PostgreSQL    │  │   Auth (GoTrue) │  │    Realtime     │     │
│  │   + RLS         │  │   JWT/Sessions  │  │   (Broadcast)   │     │
│  └─────────────────┘  └─────────────────┘  └─────────────────┘     │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Client Initialization Patterns

show.fm uses three distinct Supabase client patterns depending on the execution context.

Browser Client (Client-Side)

Location: src/lib/supabase/client.ts

Used for client-side operations in Svelte components.

typescript
import { createBrowserClient } from '@supabase/ssr';
import { env } from '$env/dynamic/public';
import type { Database } from '$lib/types/database.types';

// Supports new publishable keys and legacy anon keys
const SUPABASE_KEY = env.PUBLIC_SUPABASE_PUBLISHABLE_KEY || env.PUBLIC_SUPABASE_ANON_KEY;

export function createClient() {
	return createBrowserClient<Database>(env.PUBLIC_SUPABASE_URL!, SUPABASE_KEY!);
}

Usage: In-page reactive queries, form submissions, real-time subscriptions.

Server Client (Server-Side)

Location: src/lib/supabase/server.ts

Used in +page.server.ts, +layout.server.ts, and form actions.

typescript
import { createServerClient } from '@supabase/ssr';
import type { Cookies } from '@sveltejs/kit';
import type { Database } from '$lib/types/database.types';

export function createClient(cookies: Cookies) {
	return createServerClient<Database>(SUPABASE_URL!, SUPABASE_KEY!, {
		cookies: {
			getAll() {
				return cookies.getAll();
			},
			setAll(cookiesToSet) {
				cookiesToSet.forEach(({ name, value, options }) => {
					cookies.set(name, value, { ...options, path: '/' });
				});
			}
		}
	});
}

/**
 * IMPORTANT: Use getUser() for security-critical operations.
 * getSession() reads from cookies which can be tampered with.
 */
export async function getUser(cookies: Cookies) {
	const supabase = createClient(cookies);
	const {
		data: { user },
		error
	} = await supabase.auth.getUser();
	if (error) return null;
	return user;
}

Admin Client (Elevated Privileges)

Location: src/lib/supabase/admin.ts

Used for webhook handlers, background jobs, and operations requiring elevated permissions.

typescript
import { createClient } from '@supabase/supabase-js';
import type { Database } from '$lib/types/database.types';

export function createAdminClient(supabaseUrl: string, secretKey: string) {
	return createClient<Database>(supabaseUrl, secretKey, {
		auth: {
			autoRefreshToken: false,
			persistSession: false
		}
	});
}

Key Support: The admin client supports both:

  • New secret keys (sb_secret_...) - Recommended
  • Legacy service_role keys (JWT format) - Still works

Security: Admin clients bypass RLS. Never expose secret keys to the browser.

Tier Mapping

Location: src/lib/supabase/admin.ts

The getTierFromPriceId() function maps Stripe price IDs to subscription tiers for webhook processing.

typescript
// Static map for known price IDs (configure after creating products)
export const STRIPE_PRICE_TO_TIER: Record<string, SubscriptionTier> = {
	// 'price_xxx_starter_monthly': 'starter',
	// 'price_xxx_professional_monthly': 'professional',
};

// Get tier from price ID with multiple fallback strategies
export function getTierFromPriceId(priceId: string): SubscriptionTier {
	// 1. Check static map first
	if (STRIPE_PRICE_TO_TIER[priceId]) {
		return STRIPE_PRICE_TO_TIER[priceId];
	}

	// 2. Pattern matching on price ID name (case-insensitive)
	const lowerPriceId = priceId.toLowerCase();
	if (lowerPriceId.includes('professional') || lowerPriceId.includes('pro')) {
		return 'professional';
	}
	if (lowerPriceId.includes('enterprise')) {
		return 'enterprise';
	}
	if (lowerPriceId.includes('starter')) {
		return 'starter';
	}

	// 3. Default fallback
	return 'starter';
}

Resolution Priority:

  1. Static map lookup (exact match)
  2. Pattern matching on price ID string
  3. Default to 'starter'

Test Coverage (src/lib/supabase/__tests__/admin.test.ts):

  • Unknown price IDs return 'starter'
  • Case-insensitive pattern matching
  • 'pro' matches 'professional'
  • Edge cases (empty strings, partial matches)

SvelteKit Integration

hooks.server.ts Pipeline

The Supabase client is initialized in hooks.server.ts as part of a four-stage handle pipeline:

typescript
// Order: Subdomain → Hono → Supabase → Auth Guard
export const handle: Handle = sequence(
	subdomainHandle, // 1. Detect subdomain (book/app)
	honoHandle, // 2. Route /api/* to Hono
	supabaseHandle, // 3. Initialize Supabase client
	authGuardHandle // 4. Protect routes, set user
);

Supabase Handle

Creates server-side Supabase client with cookie management:

typescript
const supabaseHandle: Handle = async ({ event, resolve }) => {
	event.locals.supabase = createServerClient<Database>(SUPABASE_URL!, SUPABASE_KEY!, {
		cookies: {
			getAll() {
				return event.cookies.getAll();
			},
			setAll(cookiesToSet) {
				cookiesToSet.forEach(({ name, value, options }) =>
					event.cookies.set(name, value, { ...options, path: '/' })
				);
			}
		}
	});

	// Safe session getter that validates with getUser()
	event.locals.safeGetSession = async () => {
		const {
			data: { session }
		} = await event.locals.supabase.auth.getSession();
		if (!session) return { session: null, user: null };

		// Validate session by fetching user
		const {
			data: { user },
			error
		} = await event.locals.supabase.auth.getUser();
		if (error) return { session: null, user: null };

		return { session, user };
	};

	return resolve(event, {
		filterSerializedResponseHeaders(name) {
			return name === 'content-range' || name === 'x-supabase-api-version';
		}
	});
};

Usage in Page Loads

typescript
// +page.server.ts
export const load: PageServerLoad = async ({ locals }) => {
	const {
		data: { user }
	} = await locals.supabase.auth.getUser();
	if (!user) throw redirect(303, '/login');

	const { data: podcasts } = await locals.supabase
		.from('podcast_members')
		.select('podcasts(id, title, slug)')
		.eq('user_id', user.id);

	return { podcasts };
};

Usage in Form Actions

typescript
// +page.server.ts
export const actions = {
	create: async ({ request, locals }) => {
		const form = await request.formData();
		const { error } = await locals.supabase
			.from('podcasts')
			.insert({ title: form.get('title') as string });

		if (error) return fail(400, { error: error.message });
		return { success: true };
	}
};

Authentication Patterns

Three Auth Scenarios

ScenarioMethodClientTable
Dashboard UsersSupabase Auth (JWT)Browser/Server Clientpodcast_members
Guest PortalMagic TokenN/A (query param)episode_guests
API RequestsBearer TokenAdmin ClientN/A

Correct Auth Pattern

typescript
// +page.server.ts - CORRECT
const {
	data: { user }
} = await locals.supabase.auth.getUser();
if (!user) throw redirect(303, '/login');

NEVER Do This

typescript
// WRONG - cookies can be tampered with!
const {
	data: { session }
} = await locals.supabase.auth.getSession();

Guest Portal Auth

Guests access the portal via magic tokens, NOT Supabase Auth:

https://book.podcasterplus.com/episode/[id]?token=[access_token]

Validation happens via middleware that queries episode_guests:

typescript
const { data: guest } = await supabase
	.from('episode_guests')
	.select('*')
	.eq('access_token', token)
	.eq('episode_id', episodeId)
	.single();

if (!guest || guest.status !== 'active') {
	throw error(403, 'Invalid or expired access token');
}

Multi-Tenant Architecture

show.fm uses a multi-table permission model: three security-boundary tables plus a participation layer.

Security Boundaries:

TablePurposeLifecycle
podcast_membersAuthorization - who can access NOWMutable
episode_creditsAttribution - who appears in historyImmutable
episode_guestsPortal Access - magic link authTemporary

Participation Layer:

TablePurposeLifecycle
episode_peopleRoster - who is active on an episodeMutable (links to credits via credit_id)

RLS Helper Functions

sql
-- Get user's role for a podcast (NULL if no access)
get_podcast_role(podcast_id uuid) RETURNS podcast_role

-- Check if user is podcast owner
is_podcast_owner(podcast_id uuid) RETURNS boolean

-- Check if user has minimum role level (owner > admin > member)
has_podcast_role(podcast_id uuid, min_role podcast_role) RETURNS boolean

-- Get all podcast IDs user can access
get_user_podcast_ids() RETURNS uuid[]

-- Get podcast_id from episode (SECURITY DEFINER to prevent RLS recursion)
get_episode_podcast_id(episode_id uuid) RETURNS uuid

RLS Policy Pattern

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

-- Staff (admin/owner) can modify
CREATE POLICY "Staff can delete episodes"
ON episodes FOR DELETE
USING (has_podcast_role(podcast_id, 'admin'));

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

Database Schema

Migrations

All migrations are in supabase/migrations/ in chronological order:

DateMigrationPurpose
20251230initial_schema.sqlCore tables, enums, RLS functions
20260102fix_rls_recursion.sqlSECURITY DEFINER for episode_guests
20260106epic3_guest_booking_engine.sqlCalendars, booking links, bookings
20260107automation_engine.sqlRules, actions, templates, executions
20260109add_delay_action_type.sqlDelay action and waiting status
20260112add_fts_columns.sqlFull-text search indexes

Core Enums

sql
-- Podcast membership roles
CREATE TYPE podcast_role AS ENUM ('member', 'admin', 'owner');

-- Episode lifecycle
CREATE TYPE episode_status AS ENUM (
  'pending_confirmation', 'draft', 'scheduled', 'published', 'archived'
);

-- Guest portal status
CREATE TYPE guest_status AS ENUM ('invited', 'active', 'completed', 'expired');

-- Subscription tiers
CREATE TYPE subscription_tier AS ENUM ('free', 'starter', 'professional', 'enterprise');

-- Booking status
CREATE TYPE booking_status AS ENUM ('pending', 'confirmed', 'canceled', 'completed', 'no_show');

-- Automation types
CREATE TYPE automation_trigger_type AS ENUM (
  'booking.confirmed', 'booking.declined', 'booking.canceled', 'booking.rescheduled',
  'episode.published', 'episode.scheduled', 'episode.draft_created',
  'guest.responded', 'guest.reminder_sent',
  'time.before_recording', 'time.after_recording',
  'time.before_publish', 'time.after_publish', 'time.after_booking'
);

CREATE TYPE automation_action_type AS ENUM (
  'send_email', 'send_webhook', 'update_field', 'delay'
);

CREATE TYPE automation_execution_status AS ENUM (
  'pending', 'processing', 'completed', 'failed', 'skipped', 'retrying', 'waiting'
);

show.fm uses PostgreSQL FTS for server-side search with GIN indexes.

FTS-Enabled Tables

TableColumnWeights
episodesftstitle (A), description (B)
automation_rulesftsname (A), description (B)
notification_templatesftsname (A), description+subject (B)

FTS Column Generation

sql
-- Auto-generated tsvector column
ALTER TABLE episodes ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED;

-- GIN index for O(log n) search
CREATE INDEX idx_episodes_fts ON episodes USING GIN (fts);

FTS Query Example

typescript
// Search with websearch syntax (quotes, OR, negation)
const { data } = await supabase
	.from('episodes')
	.select('*')
	.textSearch('fts', '"guest interview" OR podcast -draft', { type: 'websearch' });

For typo-tolerant search, trigram indexes are also enabled:

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_episodes_title_trgm ON episodes USING GIN (title gin_trgm_ops);
CREATE INDEX idx_episodes_description_trgm ON episodes USING GIN (description gin_trgm_ops);

Supabase Realtime

show.fm uses Supabase Realtime for collaborative editing features.

Realtime Channels

Collaborative show notes use broadcast channels for peer-to-peer communication:

typescript
// Create a channel for a show note section
const channel = supabase.channel(`show-notes:${sectionId}`, {
	config: {
		broadcast: { self: false } // Don't receive own broadcasts
	}
});

// Listen for document updates
channel.on('broadcast', { event: 'doc-update' }, (payload) => {
	const update = new Uint8Array(payload.payload.update);
	// Apply Yjs update
});

// Subscribe and track presence
await channel.subscribe(async (status) => {
	if (status === 'SUBSCRIBED') {
		await channel.track({ id: user.id, name: user.name });
	}
});

Realtime Presence

Track who is currently viewing/editing:

typescript
// Handle presence sync
channel.on('presence', { event: 'sync' }, () => {
	const presenceState = channel.presenceState();
	// { "user-id": [{ id, name, color }] }
});

// Handle users joining
channel.on('presence', { event: 'join' }, ({ newPresences }) => {
	// New user(s) joined the channel
});

// Handle users leaving
channel.on('presence', { event: 'leave' }, ({ leftPresences }) => {
	// User(s) left the channel
});

Realtime-Enabled Tables

These tables have realtime subscriptions enabled:

TablePurpose
show_note_sectionsContent synchronization
episode_messagesChat messages
show_note_presencePresence tracking (backup)
sql
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;

Broadcast Events

EventPayloadPurpose
doc-update{ update: number[] }Yjs document changes
sync-request{ requesterId: string }Request full state
sync-response{ state: number[], requesterId }Full state reply
cursor-update{ userId, position, user }Cursor position

See Collaboration Documentation for complete details.

Cloudflare Workers Integration

Workers connect to Supabase via Hyperdrive for connection pooling.

RSS Feed Worker

Location: workers/rss-feed/src/db/client.ts

typescript
import postgres from 'postgres';

// Hyperdrive provides connection pooling
const sql = postgres(env.HYPERDRIVE.connectionString, { max: 1 });

try {
	const [podcast] = await sql<PodcastRow[]>`
    SELECT * FROM podcasts WHERE slug = ${slug} AND is_active = true
  `;
} finally {
	await sql.end(); // Always close connection
}

Automation Workers

Workers use the admin client pattern:

typescript
import { createClient } from '@supabase/supabase-js';
import type { Database } from './types';

const supabase = createClient<Database>(
	env.PUBLIC_SUPABASE_URL,
	env.SUPABASE_SECRET_KEY // Bypasses RLS
);

Environment Variables

Public (Browser-Safe)

bash
PUBLIC_SUPABASE_URL=https://[project].supabase.co
PUBLIC_SUPABASE_PUBLISHABLE_KEY=eyJ...  # or PUBLIC_SUPABASE_ANON_KEY (legacy)

Private (Server-Only)

bash
SUPABASE_SECRET_KEY=sb_secret_...       # Recommended
SUPABASE_SERVICE_ROLE_KEY=eyJ...        # Legacy JWT format

Type Generation

Regenerate TypeScript types after migrations:

bash
pnpm run db:types

Types are written to src/lib/types/database.types.ts:

typescript
// Row types
Database['public']['Tables']['episodes']['Row']; // SELECT
Database['public']['Tables']['episodes']['Insert']; // INSERT
Database['public']['Tables']['episodes']['Update']; // UPDATE

// Enum types
Database['public']['Enums']['episode_status'];
Database['public']['Enums']['subscription_tier'];

Test Coverage

show.fm includes comprehensive test coverage for Supabase client modules.

Browser Client Tests

Location: src/lib/supabase/__tests__/client.test.ts

typescript
import { describe, it, expect, vi } from 'vitest';

// Mock environment variables
vi.mock('$env/dynamic/public', () => ({
	env: {
		PUBLIC_SUPABASE_URL: 'https://test-project.supabase.co',
		PUBLIC_SUPABASE_PUBLISHABLE_KEY: 'test-publishable-key',
		PUBLIC_SUPABASE_ANON_KEY: 'test-anon-key'
	}
}));

describe('Browser Client', () => {
	it('should create browser client with publishable key', async () => {
		const { createBrowserClient } = await import('@supabase/ssr');
		const { createClient } = await import('$lib/supabase/client');

		createClient();

		expect(createBrowserClient).toHaveBeenCalledWith(
			'https://test-project.supabase.co',
			'test-publishable-key' // Uses publishable key when available
		);
	});

	it('should support auth operations', async () => {
		const { createClient } = await import('$lib/supabase/client');
		const client = createClient();

		// Mock session response
		mockBrowserClient.auth.getSession.mockResolvedValue({
			data: { session: { user: { id: 'user-123' } } },
			error: null
		});

		const { data } = await client.auth.getSession();
		expect(data.session.user.id).toBe('user-123');
	});
});

Key Tests:

  • Client creation with publishable key (fallback to anon key)
  • Type-safe Database type integration
  • Auth operations (getSession, getUser)
  • Database query chainable patterns

Server Client Tests

Location: src/lib/supabase/__tests__/server.test.ts

typescript
import { describe, it, expect, vi } from 'vitest';
import type { Cookies } from '@sveltejs/kit';

// Create mock SvelteKit Cookies
function createMockCookies(cookieStore: Record<string, string> = {}): Cookies {
	const store = { ...cookieStore };
	return {
		getAll: vi.fn(() => Object.entries(store).map(([name, value]) => ({ name, value }))),
		set: vi.fn((name: string, value: string, options?: object) => {
			store[name] = value;
		})
		// ... other methods
	} as unknown as Cookies;
}

describe('Server Client', () => {
	it('should configure cookie handlers correctly', async () => {
		const mockCookies = createMockCookies({ 'sb-auth-token': 'mock-token' });
		const { createClient } = await import('$lib/supabase/server');

		createClient(mockCookies);

		// Verify cookie config was passed to createServerClient
		expect(createServerClient).toHaveBeenCalledWith(
			expect.any(String),
			expect.any(String),
			expect.objectContaining({
				cookies: expect.objectContaining({
					getAll: expect.any(Function),
					setAll: expect.any(Function)
				})
			})
		);
	});

	it('should always set path to / for cookies', async () => {
		const mockCookies = createMockCookies();
		const { createClient } = await import('$lib/supabase/server');

		createClient(mockCookies);

		// Simulate cookie set
		const cookieConfig = (createServerClient as any).mock.calls[0][2].cookies;
		cookieConfig.setAll([{ name: 'test', value: 'value', options: {} }]);

		expect(mockCookies.set).toHaveBeenCalledWith('test', 'value', {
			path: '/' // Always set path to /
		});
	});
});

Security Pattern Tests

Critical Security Test

This test documents the security difference between getUser() and getSession().

typescript
describe('Security: getUser vs getSession', () => {
	/**
	 * IMPORTANT: This test documents why getUser() is required.
	 *
	 * getSession() - Reads from cookies (client-controlled, can be tampered)
	 * getUser() - Validates with Supabase server (secure, verified)
	 */
	it('should demonstrate why getUser is preferred', async () => {
		const { getUser } = await import('$lib/supabase/server');
		const mockCookies = createMockCookies();

		// Simulate tampered session
		mockAuthMethods.getSession.mockResolvedValue({
			data: { session: { user: { id: 'attacker-injected' } } },
			error: null
		});

		// getUser validates with server and rejects
		mockAuthMethods.getUser.mockResolvedValue({
			data: { user: null },
			error: { message: 'Invalid token' }
		});

		const user = await getUser(mockCookies);

		// Correctly returns null for invalid session
		expect(user).toBeNull();

		// CRITICAL: Verifies getUser was called, NOT getSession
		expect(mockAuthMethods.getUser).toHaveBeenCalled();
		expect(mockAuthMethods.getSession).not.toHaveBeenCalled();
	});
});

Key Security Tests:

  • getUser() always called instead of getSession()
  • Invalid token handling
  • Session expiration handling
  • Network error graceful handling

Critical Rules

DO

  • Use getUser() for auth validation on server
  • Use podcast_members for access control
  • Validate both RLS + API layer (defense in depth)
  • Close database connections in workers (await sql.end())
  • Use idempotency keys for retryable operations

DON'T

  • NEVER use getSession() for auth (cookies are spoofable)
  • NEVER rely on UI hiding alone (implement RLS)
  • NEVER expose admin/secret keys to browser
  • NEVER create SvelteKit +server.ts for APIs (use Hono)
  • NEVER trust episode_credits for access control

Internal documentation - Not for public distribution