How to Build a Recurring Events System? Database Schema vs Cron vs RRULE

Scheduling recurring events (Recurring Events) is one of the most common architectural challenges backend developers face when building booking or calendar systems. Whether you are building a system to manage trainer availability or a medical clinic, how you represent "time" in the database fundamentally impacts system performance and scalability.

In this article, we will explore three different approaches to tackling this challenge, analyzing the strengths and weaknesses of each technology to help you make the right architectural decision for your project.

Approach 1: Custom Database Schema

The initial intuitive approach is to translate scheduling rules into tables and relationships within a relational database (Relational Model). This typically involves creating a table for availability and another table for exceptions.

Here, we will use a trainer availability model as a practical example of custom recurrence representation inside a database.

// Migration for the initial availability schema
Schema::create('trainer_availability', function (Blueprint $table) {
    $table->id();
    $table->foreignId('trainer_id')->constrained('trainers')->onDelete('cascade');

    // Storing the day as an integer (e.g., 0 for Sunday, 1 for Monday)
    $table->integer('day_of_week')->unsigned()->default(0);
    $table->timeTz('start_time');
    $table->timeTz('end_time');

    $table->timestamps();
});

// Migration for exceptions (e.g., holidays, sick leaves)
Schema::create('trainer_availability_exceptions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('trainer_id')->constrained('trainers')->onDelete('cascade');
    $table->enum('type', ['available', 'unavailable']);
    $table->date('date');
    $table->timestamps();
});

Approach 2: Cron Expressions

Cron expressions are widely used in operating systems and task schedulers (such as Laravel Task Scheduling or NestJS Schedule).

Approach 3: iCalendar RRULE Standard (RFC 5545)

When building calendar systems requiring human interaction, iCalendar (RFC 5545) is the established industry standard for defining recurrence patterns. This standard uses a property called Recurrence Rule (RRULE) to represent a wide array of temporal patterns within a single string.

FREQ=WEEKLY;BYDAY=MO,WE;UNTIL=20241231T000000Z

How Does It Work Architecturally?

Rather than persisting every future occurrence as an independent database record, the system stores the RRULE string in the database. Occurrences are then parsed and generated on demand using dedicated libraries.

// Migration for the RRULE-based schema
Schema::create('trainer_schedules', function (Blueprint $table) {
    $table->id();
    $table->foreignId('trainer_id')->constrained('trainers')->onDelete('cascade');

    // The anchor date-time for the recurrence (When does this pattern start?)
    $table->dateTime('dtstart');

    // Store the RFC 5545 recurrence rule as a string
    $table->string('rrule_string');

    // Duration of the event in minutes (e.g., a 60-minute session)
    $table->integer('duration_minutes');

    // The timezone in which the recurring event is conceptually anchored
    $table->string('timezone');

    $table->timestamps();
});

In this architecture, DTSTART and timezone are stored as distinct database fields rather than embedding DTSTART within the raw string. dtstart is then passed to the php-rrule library when instantiating the rule object. This guarantees clean database queryability and explicit timezone handling.

When checking availability, a specialized library (such as rlanvin/php-rrule in PHP) is utilized:

use RRule\RRule;
use Carbon\Carbon;
use App\Models\TrainerSchedule;

// 1. Fetch the schedule from the database (includes rrule + timezone)
$schedule = TrainerSchedule::where('trainer_id', $trainerId)->first();

// 2. Build the RRULE using dtstart from the DB, so the recurrence is
//    anchored to the correct start date and interpreted in the right timezone.
$rrule = new RRule(
    $schedule->rrule_string,
    new DateTime($schedule->dtstart, new DateTimeZone($schedule->timezone))
);

// 3. Parse the requested date in the schedule's own timezone.
//    This is critical: a booking for "9 AM" means different UTC moments
//    in different timezones. We must interpret the time in the schedule's timezone.
$requestedDate = Carbon::parse(
    '2023-11-15 09:00:00',
    $schedule->timezone
);

// 4. Check whether the requested date-time is an occurrence of the recurrence rule.
if ($rrule->occursAt($requestedDate)) {
    // The requested time matches the recurrence rule.
    // Availability still requires checking exceptions and existing bookings.
    return response()->json(['status' => 'matches_schedule']);
}

Anatomy of RRULE Properties

From an architectural standpoint, hardcoding RRULE strings manually is error-prone. Modern libraries (such as rlanvin/php-rrule in PHP) provide array-based configuration interfaces or fluent builders to assemble rules dynamically before persisting them as standard RFC strings.

Here is a comprehensive example demonstrating how to construct an advanced rule: "Monthly recurrence on Mondays and Wednesdays, selecting only the last matching day, ending at the end of the year":

use RRule\RRule;

$rrule = new RRule([
    'FREQ'       => 'MONTHLY',
    'INTERVAL'   => 1,
    'DTSTART'    => new DateTime(
        '2023-11-01 00:00:00',
        new DateTimeZone('UTC')
    ), // The anchor date for the recurrence
    'BYDAY'      => ['MO', 'WE'],    // Filter: Generate all Mondays and Wednesdays
    'BYSETPOS'   => -1,              // Filter: Pick only the last occurrence from the generated set
    'UNTIL'      => '20241231T235959Z'     // End condition
]);

// Convert the object to a standard RFC 5545 string to store in the DB
$rruleString = $rrule->rfcString();
// Output: FREQ=MONTHLY;UNTIL=20241231T235959Z;BYDAY=MO,WE;BYSETPOS=-1

FREQ=MONTHLY;UNTIL=20241231T235959Z;BYDAY=MO,WE;BYSETPOS=-1

To master this technology, you must understand the core properties defined in iCalendar (RFC 5545):

1. Core Frequency: FREQ and INTERVAL

• FREQ (Frequency): The only mandatory property. Specifies the base recurrence interval (YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY).
• INTERVAL: Acts as a frequency multiplier. FREQ=WEEKLY;INTERVAL=2 means "every 2 weeks." Defaults to 1.

2. Anchor Point: DTSTART

In RFC 5545, DTSTART defines the start time of the recurrence set. Certain libraries like php-rrule enforce specific runtime behaviors, so ensuring DTSTART aligns with your rule semantics is crucial. It also carries temporal context missing from the rule string itself, such as start time of day.

3. Filtering Rules: BY-Rules

This is where the true power of RRULE resides, allowing you to refine or expand instances within the frequency cycle:

• BYDAY: Target specific days of the week (MO, TU, WE...). Can be prefixed with numbers to specify positional days (1FR = first Friday, -1TH = last Thursday). Note that BYDAY behavior depends on the FREQ context.
• BYMONTHDAY: Target specific days of the month (1 to 31). Negative values count backward (-1 = last day of the month).
• BYMONTH: Restrict evaluation to specific months (1 to 12).
• BYSETPOS: An advanced positional index operating on the generated set. BYSETPOS=3 extracts only the 3rd matching occurrence from the generated group.

4. Termination Rules

Prevents infinite iteration:

• COUNT: Terminates recurrence after a fixed number of occurrences (e.g., FREQ=WEEKLY;COUNT=10).
• UNTIL: Terminates recurrence at a specific timestamp. (Architectural Note: RFC 5545 explicitly forbids combining COUNT and UNTIL in the same rule).

The Architectural Layer: Beyond RRULE

A vital engineering distinction often overlooked: storing an RRULE does not mean saving every single slot as an individual database record. The RRULE defines the rule specification, while dates calculated from it are Generated Occurrences. Systems can generate these instances on the fly or pre-generate a bounded future window for indexing and performance. In both cases, persistent records are created only for exceptions, actual bookings, or modified slots.

Consequently, an RRULE describes only the core template. A complete production system requires a full availability pipeline:

1. Occurrence Generation: Expand the RRULE for a specific query window.
2. Exception Filtering: Exclude blacklisted dates or holiday exceptions.
3. Booking Verification: Cross-reference existing reservations.
4. Buffer & Constraint Checks: Apply buffer times, max capacity, and booking lead-time limits.
5. Slot Output: Return final available slots to the client.

This processing pipeline is what transforms a simple RRULE parser into a robust enterprise scheduling engine.

How to Decide as a Software Architect?

There is no silver bullet; the optimal choice depends entirely on your system domain:

1. Choose Cron Jobs when building system-level background routines (database backups, scheduled report distribution, batch email queues).
2. Choose Custom Database Schema if your domain requires basic, static schedules (e.g., fixed weekly shifts without external calendar sync) and direct SQL query performance is your top priority.
3. Choose RRULE (RFC 5545) when building human-centric scheduling applications (booking platforms, clinic appointments, event management), especially where requirements will evolve or require interoperability with standard calendar clients.

As software engineers, our goal is to evaluate existing standards and avoid reinventing complex wheels when battle-tested industry specifications like RFC 5545 are readily available.