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

- **Pattern:** `ab-000534` (`booking-calendar-availability.recurring.rule-based-storage`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000534
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000534)

## Why it matters

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.

## Severity rationale

Low because pre-expanded storage causes maintenance debt and stale-schedule bugs rather than immediate data loss, but it blocks correct exception handling and makes schedule edits error-prone.

## Remediation

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.

```sql
-- 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
);
```

```ts
// 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)
}
```

## Detection

- **ID:** `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:

  ```sql
  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:

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

## External references

- iso-25010 functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000534