CRUD Patterns Guide

This guide documents the standard patterns used for CRUD operations throughout show.fm. Following these patterns ensures consistency, security, and maintainability.

Response Format Standard

All Hono API endpoints return a consistent response structure:

typescript

// Success response
{
  success: true,
  data: { /* entity or array of entities */ }
}

// Error response
{
  error: "Error message description"
}

// List response
{
  success: true,
  data: [ /* array of entities */ ]
}
// OR (for paginated responses)
{
  templates: [ /* array */ ],
  total: 100,
  limit: 50,
  offset: 0
}

Authentication Pattern

All authenticated endpoints use Bearer token authentication:

typescript

async function getAuthenticatedUser(c: Context): Promise<{
	user: { id: string; email?: string } | null;
	supabase?: SupabaseClient;
	error: string | null;
}> {
	const authHeader = c.req.header('Authorization');
	if (!authHeader?.startsWith('Bearer ')) {
		return { user: null, error: 'Unauthorized' };
	}

	const token = authHeader.slice(7);
	const supabase = createClient(supabaseUrl, supabaseKey);
	const {
		data: { user },
		error
	} = await supabase.auth.getUser(token);

	if (error || !user) {
		return { user: null, error: 'Invalid token' };
	}

	return { user, supabase, error: null };
}

Permission Checking Pattern

Multi-tenant access control via podcast_members table:

typescript

async function checkPodcastMemberAccess(
	supabase: SupabaseClient,
	userId: string,
	podcastId: string
) {
	const { data: membership, error } = await supabase
		.from('podcast_members')
		.select('role')
		.eq('user_id', userId)
		.eq('podcast_id', podcastId)
		.single();

	const role = membership?.role ?? null;
	return { hasAccess: !error && !!membership, role };
}

Role Hierarchy:

owner - Full access including billing and deletion
admin - Full access except billing
member - View and edit, no publish/delete/team management

typescript

// Role check example
const { hasAccess, role } = await checkPodcastMemberAccess(supabase, user.id, podcastId);

// For operations requiring elevated permissions
if (!hasAccess || (role !== 'owner' && role !== 'admin')) {
	return c.json({ error: 'Not authorized' }, 403);
}

Admin Client Pattern (INSERT Operations)

Critical Pattern

Due to Supabase SSR client limitations with JWT propagation to PostgreSQL RLS policies, INSERT operations require the admin client pattern.

typescript

// For INSERT operations - use admin client
const adminClient = createClient(supabaseUrl, supabaseServiceRoleKey);
const { data, error } = await adminClient.from('table').insert(data).select().single();

// For SELECT/UPDATE/DELETE - authenticated client works
const { data } = await supabase.from('table').update(updateData).eq('id', id).select().single();

When to use Admin Client:

Creating new records (INSERT)
Operations spanning multiple users (e.g., creating a podcast and adding owner membership)
Background jobs/workers without user context

When to use Authenticated Client:

Reading data (SELECT) - RLS policies apply
Updating existing records (UPDATE)
Deleting records (DELETE)

Validation Pattern

All input validation uses Zod schemas with @hono/zod-validator:

typescript

import { z } from 'zod';
import { zValidator } from '@hono/zod-validator';

const createEntitySchema = z.object({
	podcast_id: z.string().uuid(),
	name: z.string().min(1).max(100),
	description: z.string().max(500).optional()
});

// Apply to route
app.post('/', zValidator('json', createEntitySchema), async (c) => {
	const body = c.req.valid('json'); // Fully typed
	// ...
});

Query Parameter Validation:

typescript

const listQuerySchema = z.object({
	podcast_id: z.string().uuid(),
	limit: z.coerce.number().min(1).max(100).default(50),
	offset: z.coerce.number().min(0).default(0)
});

app.get('/', zValidator('query', listQuerySchema), async (c) => {
	const { podcast_id, limit, offset } = c.req.valid('query');
	// ...
});

CRUD Operation Patterns

CREATE Pattern

typescript

.post('/', zValidator('json', createSchema), async (c) => {
  // 1. Authenticate
  const { user, supabase, error } = await getAuthenticatedUser(c);
  if (error || !user || !supabase) {
    return c.json({ error: error || 'Unauthorized' }, 401);
  }

  const body = c.req.valid('json');

  // 2. Check permissions
  const { hasAccess, role } = await checkPodcastMemberAccess(
    supabase, user.id, body.podcast_id
  );
  if (!hasAccess || (role !== 'owner' && role !== 'admin')) {
    return c.json({ error: 'Not authorized' }, 403);
  }

  // 3. Validate business rules (if any)
  // e.g., check for conflicts, validate templates, etc.

  // 4. Create record (use admin client for INSERT)
  const { data, error: createError } = await supabase
    .from('table')
    .insert({ ...body, created_by: user.id })
    .select()
    .single();

  if (createError) {
    console.error('Failed to create:', createError);
    return c.json({ error: 'Failed to create' }, 500);
  }

  // 5. Return success with created entity
  return c.json({ success: true, data }, 201);
});

READ Pattern (Single)

typescript

.get('/:id', async (c) => {
  const { user, supabase, error } = await getAuthenticatedUser(c);
  if (error || !user || !supabase) {
    return c.json({ error: error || 'Unauthorized' }, 401);
  }

  const id = c.req.param('id');

  // Fetch with relations
  const { data, error: fetchError } = await supabase
    .from('table')
    .select('*, related_table(*)')
    .eq('id', id)
    .single();

  if (fetchError || !data) {
    return c.json({ error: 'Not found' }, 404);
  }

  // Check access (entity must have podcast_id)
  const { hasAccess } = await checkPodcastMemberAccess(
    supabase, user.id, data.podcast_id
  );
  if (!hasAccess) {
    return c.json({ error: 'Access denied' }, 403);
  }

  return c.json({ success: true, data });
});

READ Pattern (List with Pagination)

typescript

.get('/', zValidator('query', listQuerySchema), async (c) => {
  const { user, supabase, error } = await getAuthenticatedUser(c);
  if (error || !user || !supabase) {
    return c.json({ error: error || 'Unauthorized' }, 401);
  }

  const { podcast_id, limit, offset, filter } = c.req.valid('query');

  // Check access
  const { hasAccess } = await checkPodcastMemberAccess(
    supabase, user.id, podcast_id
  );
  if (!hasAccess) {
    return c.json({ error: 'Access denied' }, 403);
  }

  // Build query with optional filters
  let query = supabase
    .from('table')
    .select('*', { count: 'exact' })
    .eq('podcast_id', podcast_id)
    .order('created_at', { ascending: false })
    .range(offset, offset + limit - 1);

  if (filter) {
    query = query.eq('some_field', filter);
  }

  const { data, error: queryError, count } = await query;

  if (queryError) {
    return c.json({ error: 'Failed to fetch' }, 500);
  }

  return c.json({
    data: data || [],
    total: count || 0,
    limit,
    offset
  });
});

UPDATE Pattern

typescript

.put('/:id', zValidator('json', updateSchema), async (c) => {
  const { user, supabase, error } = await getAuthenticatedUser(c);
  if (error || !user || !supabase) {
    return c.json({ error: error || 'Unauthorized' }, 401);
  }

  const id = c.req.param('id');
  const body = c.req.valid('json');

  // Fetch existing to check ownership
  const { data: existing, error: fetchError } = await supabase
    .from('table')
    .select('podcast_id')
    .eq('id', id)
    .single();

  if (fetchError || !existing) {
    return c.json({ error: 'Not found' }, 404);
  }

  // Check permissions
  const { hasAccess, role } = await checkPodcastMemberAccess(
    supabase, user.id, existing.podcast_id
  );
  if (!hasAccess || (role !== 'owner' && role !== 'admin')) {
    return c.json({ error: 'Not authorized' }, 403);
  }

  // Build update object with only provided fields
  const updates: Record<string, unknown> = {
    updated_at: new Date().toISOString()
  };
  if (body.name !== undefined) updates.name = body.name;
  if (body.description !== undefined) updates.description = body.description;

  // Update
  const { data, error: updateError } = await supabase
    .from('table')
    .update(updates)
    .eq('id', id)
    .select()
    .single();

  if (updateError) {
    return c.json({ error: 'Failed to update' }, 500);
  }

  return c.json({ success: true, data });
});

DELETE Pattern

typescript

.delete('/:id', async (c) => {
  const { user, supabase, error } = await getAuthenticatedUser(c);
  if (error || !user || !supabase) {
    return c.json({ error: error || 'Unauthorized' }, 401);
  }

  const id = c.req.param('id');

  // Fetch existing
  const { data: existing, error: fetchError } = await supabase
    .from('table')
    .select('podcast_id, status')
    .eq('id', id)
    .single();

  if (fetchError || !existing) {
    return c.json({ error: 'Not found' }, 404);
  }

  // Check permissions (often owner/admin only for delete)
  const { hasAccess, role } = await checkPodcastMemberAccess(
    supabase, user.id, existing.podcast_id
  );
  if (!hasAccess || (role !== 'owner' && role !== 'admin')) {
    return c.json({ error: 'Not authorized to delete' }, 403);
  }

  // Check business rules (e.g., status restrictions)
  if (existing.status === 'confirmed') {
    return c.json({
      error: 'Cannot delete confirmed records. Cancel first.'
    }, 400);
  }

  // Check for dependencies
  const { count } = await supabase
    .from('related_table')
    .select('*', { count: 'exact', head: true })
    .eq('parent_id', id);

  if (count && count > 0) {
    return c.json({
      error: 'Cannot delete: has related records',
      related_count: count
    }, 409);
  }

  // Delete
  const { error: deleteError } = await supabase
    .from('table')
    .delete()
    .eq('id', id);

  if (deleteError) {
    return c.json({ error: 'Failed to delete' }, 500);
  }

  return c.json({ success: true });
});

Transaction-Like Operations

For operations that modify multiple related records:

typescript

.post('/', async (c) => {
  // ... auth checks ...

  try {
    // 1. Create primary record
    const { data: primary, error: primaryError } = await supabase
      .from('primary_table')
      .insert(primaryData)
      .select()
      .single();

    if (primaryError) throw primaryError;

    // 2. Create related record
    const { data: related, error: relatedError } = await supabase
      .from('related_table')
      .insert({ primary_id: primary.id, ...relatedData })
      .select()
      .single();

    if (relatedError) throw relatedError;

    // 3. Update primary with reference
    await supabase
      .from('primary_table')
      .update({ related_id: related.id })
      .eq('id', primary.id);

    // 4. Side effects (emails, events, jobs)
    // These should not fail the main operation
    try {
      await sendEmail(/* ... */);
      await emitEvent(/* ... */);
      await scheduleJobs(/* ... */);
    } catch (sideEffectError) {
      console.error('Side effect failed:', sideEffectError);
      // Continue - don't fail the main operation
    }

    return c.json({ success: true, data: primary }, 201);

  } catch (err) {
    console.error('Operation failed:', err);
    return c.json({ error: 'Operation failed' }, 500);
  }
});

Side Effects Pattern

Side effects (emails, automation events, scheduled jobs) should:

Run after the main database operation succeeds
Not fail the main operation if they error
Be awaited in Workers (to prevent early termination)

typescript

// After successful database update
const { data: updatedBooking } = await supabase
	.from('bookings')
	.update({ status: 'confirmed' })
	.eq('id', bookingId)
	.select()
	.single();

// Side effects - must await in Workers!
try {
	await sendBookingConfirmedEmail(/* ... */);
} catch (emailError) {
	console.error('Failed to send email:', emailError);
}

try {
	await emitBookingEvent(supabase, 'booking.confirmed', bookingId /* ... */);
} catch (eventError) {
	console.error('Failed to emit event:', eventError);
}

try {
	await scheduleTimeBasedJobs(/* ... */);
} catch (jobError) {
	console.error('Failed to schedule jobs:', jobError);
}

return c.json({ success: true, data: updatedBooking });

Error Handling

Standard HTTP status codes:

Code	Meaning	When to Use
`200`	OK	Successful GET, PUT, DELETE
`201`	Created	Successful POST that creates a resource
`400`	Bad Request	Invalid input, validation errors
`401`	Unauthorized	Missing or invalid auth token
`403`	Forbidden	Valid auth but insufficient permissions
`404`	Not Found	Resource doesn't exist
`409`	Conflict	Duplicate, in-use resource, or status conflict
`500`	Server Error	Database errors, unexpected failures

Cascade Operations

When status changes affect related records:

typescript

// Example: Canceling a booking
// 1. Update primary record
await supabase
	.from('bookings')
	.update({ status: 'canceled', canceled_at: new Date().toISOString() })
	.eq('id', bookingId);

// 2. Update related episode
if (booking.episode_id) {
	await supabase.from('episodes').update({ status: 'draft' }).eq('id', booking.episode_id);
}

// 3. Update related guest
if (booking.guest_id) {
	await supabase.from('episode_guests').update({ status: 'expired' }).eq('id', booking.guest_id);
}

// 4. Cancel external resources
if (booking.google_event_id) {
	await deleteCalendarEvent(accessToken, calendarId, eventId);
}

// 5. Cancel scheduled automation jobs
await cancelPendingTimeBasedJobs(supabase, { booking_id: bookingId });

// 6. Emit automation event
await emitBookingEvent(supabase, 'booking.canceled', bookingId /* ... */);

Multi-Tenancy Architecture - Permission model details
Hono API Overview - API structure and RPC client
Automation API - Automation CRUD specifics
Booking Flow - Complete booking lifecycle

CRUD Patterns Guide ​

Response Format Standard ​

Authentication Pattern ​

Permission Checking Pattern ​

Admin Client Pattern (INSERT Operations) ​

Validation Pattern ​

CRUD Operation Patterns ​

CREATE Pattern ​

READ Pattern (Single) ​

READ Pattern (List with Pagination) ​

UPDATE Pattern ​

DELETE Pattern ​

Transaction-Like Operations ​

Side Effects Pattern ​

Error Handling ​

Cascade Operations ​

Related Documentation ​

CRUD Patterns Guide

Response Format Standard

Authentication Pattern

Permission Checking Pattern

Admin Client Pattern (INSERT Operations)

Validation Pattern

CRUD Operation Patterns

CREATE Pattern

READ Pattern (Single)

READ Pattern (List with Pagination)

UPDATE Pattern

DELETE Pattern

Transaction-Like Operations

Side Effects Pattern

Error Handling

Cascade Operations

Related Documentation