Availability Slot Systems: Weekly Recurrence with Date Overrides

On TidyLinker, cleaners set their weekly availability once and then block specific dates when they can't work. Sounds simple. It took three attempts to get the data model right.

The first attempt was a single table with every available slot as a row. Monday 9am, Monday 10am, Monday 11am... for every cleaner, for every week, going forward indefinitely. The table grew to millions of rows within a month and the queries were abysmal.

The second attempt was a JSON column on the user profile. Flexible, sure, but impossible to query efficiently. "Find all cleaners available next Tuesday at 2pm" became a full table scan with JSON parsing.

The third attempt — two tables — is what stuck.

The Schema

Table 1: Recurring Weekly Slots

These represent the cleaner's regular weekly schedule:

Schema::create('availability_slots', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->unsignedTinyInteger('day_of_week'); // 0 = Monday, 6 = Sunday
    $table->time('start_time');
    $table->time('end_time');
    $table->boolean('is_active')->default(true);
    $table->timestamps();

    $table->index(['user_id', 'day_of_week', 'is_active']);
});

A cleaner who works Monday to Friday, 9am to 5pm, has five rows:

A cleaner who does mornings on Monday/Wednesday and full days on Friday has three rows with different times. The model is flexible without being complex.

Table 2: Date Overrides

These represent exceptions to the weekly schedule — either blocking a day or adding extra availability:

Schema::create('availability_overrides', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->date('date');
    $table->enum('type', ['blocked', 'available']);
    $table->time('start_time')->nullable(); // null = entire day
    $table->time('end_time')->nullable();
    $table->string('reason')->nullable();
    $table->timestamps();

    $table->unique(['user_id', 'date', 'start_time']);
    $table->index(['date', 'type']);
});

A cleaner blocking next Monday:

A cleaner adding Saturday availability for one week:

The Models

class AvailabilitySlot extends Model
{
    protected $casts = [
        'day_of_week' => 'integer',
        'is_active' => 'boolean',
    ];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function scopeForDay(Builder $query, int $dayOfWeek): Builder
    {
        return $query->where('day_of_week', $dayOfWeek)
            ->where('is_active', true);
    }

    public function coversTime(string $time): bool
    {
        return $time >= $this->start_time && $time < $this->end_time;
    }
}

class AvailabilityOverride extends Model
{
    protected $casts = [
        'date' => 'date',
        'type' => OverrideType::class,
    ];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function isFullDay(): bool
    {
        return $this->start_time === null && $this->end_time === null;
    }
}

The Core Query: "Is This Person Available?"

The question "is cleaner X available on date Y at time Z?" follows this logic:

1. Check for overrides on date Y
2. If there's a "blocked" override covering the time, they're NOT available
3. If there's an "available" override covering the time, they ARE available
4. Otherwise, check the recurring weekly slot for that day of the week

class CheckAvailability
{
    public function execute(User $user, Carbon $dateTime): bool
    {
        $date = $dateTime->toDateString();
        $time = $dateTime->format('H:i');
        $dayOfWeek = $dateTime->dayOfWeekIso - 1; // 0 = Monday

        // Check overrides first — they take priority
        $overrides = AvailabilityOverride::where('user_id', $user->id)
            ->where('date', $date)
            ->get();

        foreach ($overrides as $override) {
            if ($override->isFullDay()) {
                return $override->type === OverrideType::Available;
            }

            if ($time >= $override->start_time && $time < $override->end_time) {
                return $override->type === OverrideType::Available;
            }
        }

        // No override applies — check recurring slots
        return AvailabilitySlot::where('user_id', $user->id)
            ->forDay($dayOfWeek)
            ->get()
            ->contains(fn (AvailabilitySlot $slot) => $slot->coversTime($time));
    }
}

The Search Query: "Who's Available?"

The more common query is "find all cleaners available on date Y at time Z in area A":

class FindAvailableCleaners
{
    public function execute(Carbon $dateTime, string $postcode): Collection
    {
        $date = $dateTime->toDateString();
        $time = $dateTime->format('H:i:00');
        $dayOfWeek = $dateTime->dayOfWeekIso - 1;

        // Get cleaners who are blocked on this date
        $blockedUserIds = AvailabilityOverride::where('date', $date)
            ->where('type', OverrideType::Blocked)
            ->where(function (Builder $query) use ($time) {
                $query->whereNull('start_time') // Full day block
                    ->orWhere(function (Builder $q) use ($time) {
                        $q->where('start_time', '<=', $time)
                            ->where('end_time', '>', $time);
                    });
            })
            ->pluck('user_id');

        // Get cleaners with an explicit available override
        $overrideAvailableIds = AvailabilityOverride::where('date', $date)
            ->where('type', OverrideType::Available)
            ->where(function (Builder $query) use ($time) {
                $query->whereNull('start_time')
                    ->orWhere(function (Builder $q) use ($time) {
                        $q->where('start_time', '<=', $time)
                            ->where('end_time', '>', $time);
                    });
            })
            ->pluck('user_id');

        // Get cleaners with a recurring slot for this day/time
        $recurringAvailableIds = AvailabilitySlot::forDay($dayOfWeek)
            ->where('start_time', '<=', $time)
            ->where('end_time', '>', $time)
            ->pluck('user_id');

        // Combine: (recurring OR override-available) AND NOT blocked
        $availableIds = $recurringAvailableIds
            ->merge($overrideAvailableIds)
            ->unique()
            ->diff($blockedUserIds);

        return User::whereIn('id', $availableIds)
            ->where('postcode_area', PostcodeHelper::extractArea($postcode))
            ->where('is_active', true)
            ->get();
    }
}

Three queries, all using indexes, all fast. On TidyLinker with a few thousand cleaners, this runs in under 50ms.

Edge Cases

Split shifts. A cleaner works 9am-12pm and 2pm-6pm on Mondays. That's two rows in availability_slots for the same day. The query handles this naturally because it checks if any slot covers the requested time.

Partial day blocks. "I'm available Monday as usual but I've got a GP appointment from 2pm to 3pm." That's an override with type=blocked, start_time=14:00, end_time=15:00. The search query correctly excludes them for 2pm but includes them for 10am.

Midnight crossings. A cleaner works nights: 10pm to 6am. This crosses midnight, which means the slot spans two calendar days. I handle this by splitting it into two slots: 22:00-23:59 on day X and 00:00-06:00 on day X+1. It's a bit ugly but avoids time comparison nightmares.

Timezones. TidyLinker operates in the UK only, so timezones aren't a concern. If you're building for multiple timezones, store everything in UTC and convert at query time. The override dates should be in the user's local timezone (they think in "next Monday" not "next Monday UTC").

Already-booked slots. Availability doesn't mean they're not already booked. You need a separate check against existing bookings:

// After finding available cleaners, filter out those with existing bookings
$bookedUserIds = Booking::where('date', $date)
    ->where('start_time', '<=', $time)
    ->where('end_time', '>', $time)
    ->whereIn('status', [BookingStatus::Confirmed, BookingStatus::InProgress])
    ->pluck('cleaner_id');

$availableIds = $availableIds->diff($bookedUserIds);

The Frontend

For the cleaner's availability settings page, I use a weekly grid component:

function WeeklyAvailability({ slots, onUpdate }: Props) {
    const days = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'];

    return (
        <div className="grid grid-cols-7 gap-2">
            {days.map((day, index) => {
                const daySlots = slots.filter(s => s.dayOfWeek === index);

                return (
                    <div key={day} className="space-y-2">
                        <h3 className="text-sm font-medium text-center">
                            {day}
                        </h3>
                        {daySlots.map(slot => (
                            <TimeSlotPill
                                key={slot.id}
                                slot={slot}
                                onRemove={() => onUpdate('remove', slot.id)}
                            />
                        ))}
                        <AddSlotButton
                            dayOfWeek={index}
                            onAdd={(start, end) => onUpdate('add', { dayOfWeek: index, start, end })}
                        />
                    </div>
                );
            })}
        </div>
    );
}

For date overrides, a calendar view where they can click dates to block or unblock them. Nothing fancy — just clear, functional UI.

Why This Pattern Works

The two-table pattern separates the rule (weekly schedule) from the exception (date override). Queries are efficient because recurring slots are a small, bounded dataset, and overrides are sparse (most days have no overrides). The logic is easy to reason about: check exceptions first, fall back to the rule.

It's not the most sophisticated scheduling system. It doesn't handle complex recurrence patterns (every other Tuesday) or timezone-aware recurring events. But for "when is this person regularly available, with the ability to block specific dates," it's clean, fast, and battle-tested.

---

I write about Laravel, AI tooling, and building software. More at stuartmason.co.uk.