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)
}

• ID: booking-calendar-availability.recurring.rule-based-storage

• Severity: low

• What to look for: Examine the database schema for recurring availability. Search for columns named rrule, recurrence_rule, recurrence, or pattern in the schema. Check package.json for rrule, rrule.js, node-rrule, or ical-generator. Enumerate all recurring availability tables and count every recurrence-related column. Verify:

  1. Rule-based (correct): A single row with a recurrence rule (RRULE format like FREQ=WEEKLY;BYDAY=MO,WE,FR or a similar custom format) that defines the pattern.
  2. Expanded (problematic): Individual rows for each occurrence (e.g., 365 rows for a daily recurring slot). Also check for a separate exceptions table. Examine files matching prisma/schema.prisma, drizzle/*schema*, supabase/migrations/*, src/lib/*recurring*, src/lib/*schedule*.

• Pass criteria: At least 1 recurrence rule column must exist in the database schema (e.g., rrule TEXT, recurrence_rule VARCHAR). The schema must have a recurring availability table with a rule-based pattern, not pre-expanded rows. If exceptions exist, they must be stored in a separate table or column (not mixed into the recurring rule). Report: "Storage model: [rule-based/expanded]. Rule column: [name]. Exceptions table: [present/absent]."

• Fail criteria: Recurring patterns are stored as individual rows in the database (e.g., 365 rows for a yearly pattern), or no recurrence rule column exists.

• Skip (N/A) when: Platform does not support recurring bookings or recurring availability schedules.

• Detail on fail: Example: "A host's 'Monday-Friday, 9-5' availability is stored as 260 individual rows (one per workday). Adding a new exception requires a DELETE and INSERT; querying is slow for a year's worth of availability."

• Cross-reference: Check exception-handling — rule-based storage is the prerequisite for proper exception handling.

• Cross-reference: Check recurring-edit-scope — editing scopes (this/future/all) only work with rule-based storage.

• Cross-reference: Check buffer-enforcement — recurring slot generation must also respect buffer times between slots.

• Remediation: Store recurring schedules as rules:

  CREATE TABLE recurring_availability (
    id UUID PRIMARY KEY,
    resource_id UUID NOT NULL,
    rrule TEXT NOT NULL, -- e.g., "FREQ=WEEKLY;BYDAY=MO,WE,FR;BYHOUR=9;BYMINUTE=0"
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
  )
  
  CREATE TABLE availability_exceptions (
    id UUID PRIMARY KEY,
    recurring_id UUID NOT NULL REFERENCES recurring_availability(id),
    exception_date DATE NOT NULL,
    is_available BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
  )
  

  When fetching availability, parse the RRULE and generate slots on-the-fly:

  import { RRule, rrulestr } from 'rrule'
  
  async function getRecurringSlots(resourceId, startDate, endDate) {
    const recurring = await db.recurringAvailability.findFirst({
      where: { resourceId },
    })
  
    if (!recurring) return []
  
    const rule = rrulestr(recurring.rrule, { dtstart: startDate })
    const occurrences = rule.between(startDate, endDate)
  
    // Check for exceptions
    const exceptions = await db.availabilityExceptions.findMany({
      where: {
        recurringId: recurring.id,
        exceptionDate: { gte: startDate, lte: endDate },
      },
    })
  
    const exceptionMap = new Map(
      exceptions.map((e) => [e.exceptionDate.toISOString(), e.isAvailable])
    )
  
    return occurrences.filter(
      (date) => exceptionMap.get(date.toISOString()) !== false
    )
  }