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.
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.
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.
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.
// 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:
- Static map lookup (exact match)
- Pattern matching on price ID string
- 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:
// 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:
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
// +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
// +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
| Scenario | Method | Client | Table |
|---|---|---|---|
| Dashboard Users | Supabase Auth (JWT) | Browser/Server Client | podcast_members |
| Guest Portal | Magic Token | N/A (query param) | episode_guests |
| API Requests | Bearer Token | Admin Client | N/A |
Correct Auth Pattern
// +page.server.ts - CORRECT
const {
data: { user }
} = await locals.supabase.auth.getUser();
if (!user) throw redirect(303, '/login');NEVER Do This
// 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:
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:
| Table | Purpose | Lifecycle |
|---|---|---|
podcast_members | Authorization - who can access NOW | Mutable |
episode_credits | Attribution - who appears in history | Immutable |
episode_guests | Portal Access - magic link auth | Temporary |
Participation Layer:
| Table | Purpose | Lifecycle |
|---|---|---|
episode_people | Roster - who is active on an episode | Mutable (links to credits via credit_id) |
RLS Helper Functions
-- 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 uuidRLS Policy Pattern
-- 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:
| Date | Migration | Purpose |
|---|---|---|
| 20251230 | initial_schema.sql | Core tables, enums, RLS functions |
| 20260102 | fix_rls_recursion.sql | SECURITY DEFINER for episode_guests |
| 20260106 | epic3_guest_booking_engine.sql | Calendars, booking links, bookings |
| 20260107 | automation_engine.sql | Rules, actions, templates, executions |
| 20260109 | add_delay_action_type.sql | Delay action and waiting status |
| 20260112 | add_fts_columns.sql | Full-text search indexes |
Core Enums
-- 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'
);Full-Text Search
show.fm uses PostgreSQL FTS for server-side search with GIN indexes.
FTS-Enabled Tables
| Table | Column | Weights |
|---|---|---|
episodes | fts | title (A), description (B) |
automation_rules | fts | name (A), description (B) |
notification_templates | fts | name (A), description+subject (B) |
FTS Column Generation
-- 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
// Search with websearch syntax (quotes, OR, negation)
const { data } = await supabase
.from('episodes')
.select('*')
.textSearch('fts', '"guest interview" OR podcast -draft', { type: 'websearch' });Trigram Fuzzy Search
For typo-tolerant search, trigram indexes are also enabled:
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:
// 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:
// 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:
| Table | Purpose |
|---|---|
show_note_sections | Content synchronization |
episode_messages | Chat messages |
show_note_presence | Presence tracking (backup) |
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
| Event | Payload | Purpose |
|---|---|---|
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
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:
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)
PUBLIC_SUPABASE_URL=https://[project].supabase.co
PUBLIC_SUPABASE_PUBLISHABLE_KEY=eyJ... # or PUBLIC_SUPABASE_ANON_KEY (legacy)Private (Server-Only)
SUPABASE_SECRET_KEY=sb_secret_... # Recommended
SUPABASE_SERVICE_ROLE_KEY=eyJ... # Legacy JWT formatType Generation
Regenerate TypeScript types after migrations:
pnpm run db:typesTypes are written to src/lib/types/database.types.ts:
// 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
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
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().
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 ofgetSession()- Invalid token handling
- Session expiration handling
- Network error graceful handling
Critical Rules
DO
- Use
getUser()for auth validation on server - Use
podcast_membersfor 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.tsfor APIs (use Hono) - NEVER trust
episode_creditsfor access control
Related Documentation
- Architecture Overview
- Multi-Tenancy Model
- Automation Database Schema
- Testing Patterns - API and security testing
- Mock Factories - Supabase client mocking