Recurring schedules stored as rule definitions, not pre-expanded individual rows

Storing a host's Monday-through-Friday 9-to-5 availability as 260 individual database rows — one per workday — is an ISO 25010 functional-correctness failure masquerading as a schema decision. Pre-expanded rows mean adding a single exception (a vacation day) requires a DELETE and INSERT instead of an exception record. Querying a year's availability requires scanning hundreds of rows instead of parsing one RRULE string. Editing the recurring pattern requires a bulk UPDATE. The real business risk: a host who changes their hours sees stale slots appear because the expansion was done at write time, not at query time.

Store recurring availability as a single RRULE string per rule, with a separate exceptions table for overrides. Parse and expand at query time using the rrule package.

-- supabase/migrations/YYYYMMDDHHMMSS_recurring_availability.sql
CREATE TABLE recurring_availability (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL,
  rrule       TEXT NOT NULL  -- e.g. FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR
);

CREATE TABLE availability_exceptions (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  recurring_id UUID NOT NULL REFERENCES recurring_availability(id),
  exception_date DATE NOT NULL,
  is_available BOOLEAN NOT NULL DEFAULT FALSE
);

// src/lib/availability.ts
import { rrulestr } from 'rrule'

export async function getSlots(resourceId: string, start: Date, end: Date) {
  const rec = await db.recurringAvailability.findFirst({ where: { resourceId } })
  if (!rec) return []
  return rrulestr(rec.rrule, { dtstart: start }).between(start, end)
}