Booking and Scheduling Systems: Architecture for Real-Time Availability
Booking systems look simple from the outside. A user picks a date, picks a time, confirms. Done. Then you start building one and discover a maze of edge cases: timezones, race conditions, recurring availability, buffer times, cancellation policies, calendar syncs, and reminder emails. Each one sounds small. Together, they make booking systems one of the most deceptively complex features in SaaS.
We built the booking and scheduling module for MindHyv — an all-in-one business platform where entrepreneurs manage appointments alongside their social presence, invoicing, and storefront. This post covers the architecture patterns, database design, and implementation details that make a booking system work reliably at scale.
Data Model: Availability, Slots, and Appointments
The booking system has three core entities:
- Availability rules define when a provider is available. “I am available Monday through Friday, 9 AM to 5 PM.”
- Slots are the bookable units derived from availability rules. “Monday, May 5, 9:00 AM - 9:30 AM.”
- Appointments are confirmed bookings that claim a slot.
Here is the schema:
-- Service types offered by the provider
create table services (
id uuid primary key default gen_random_uuid(),
provider_id uuid references profiles(id) on delete cascade,
name text not null,
description text,
duration_minutes integer not null, -- 30, 60, 90, etc.
buffer_before_minutes integer default 0,
buffer_after_minutes integer default 0,
price_cents integer,
currency text default 'USD',
is_active boolean default true,
created_at timestamptz default now()
);
-- Recurring availability rules
create table availability_rules (
id uuid primary key default gen_random_uuid(),
provider_id uuid references profiles(id) on delete cascade,
day_of_week integer not null, -- 0=Sunday, 6=Saturday
start_time time not null, -- Local time in provider's timezone
end_time time not null,
provider_timezone text not null, -- e.g. 'Asia/Manila'
is_active boolean default true,
created_at timestamptz default now()
);
-- Specific date overrides (holidays, special hours)
create table availability_overrides (
id uuid primary key default gen_random_uuid(),
provider_id uuid references profiles(id) on delete cascade,
override_date date not null,
is_available boolean default false, -- false = day off
start_time time, -- null if is_available = false
end_time time,
reason text, -- 'Holiday', 'Special event', etc.
created_at timestamptz default now(),
unique(provider_id, override_date)
);
-- Confirmed appointments
create table appointments (
id uuid primary key default gen_random_uuid(),
service_id uuid references services(id) on delete cascade,
provider_id uuid references profiles(id) on delete cascade,
client_id uuid references profiles(id) on delete cascade,
start_time timestamptz not null, -- UTC
end_time timestamptz not null, -- UTC
buffer_start timestamptz not null, -- start_time minus buffer_before
buffer_end timestamptz not null, -- end_time plus buffer_after
client_timezone text not null,
status text not null default 'confirmed',
-- confirmed, cancelled_by_client, cancelled_by_provider,
-- completed, no_show
notes text,
created_at timestamptz default now()
);
-- Exclusion constraint: no overlapping appointments (including buffers)
-- for the same provider
alter table appointments
add constraint no_overlapping_appointments
exclude using gist (
provider_id with =,
tstzrange(buffer_start, buffer_end) with &&
) where (status = 'confirmed');The exclusion constraint on appointments is the most important line in this schema. PostgreSQL GiST exclusion constraints enforce at the database level that no two confirmed appointments for the same provider can have overlapping time ranges (including buffer time). This is your last line of defense against double-booking, and it works even if your application code has bugs.
Generating Available Slots
The client-facing booking page needs to show available time slots. These are generated dynamically by combining availability rules, overrides, existing appointments, and the service duration.
import {
addMinutes,
startOfDay,
endOfDay,
isAfter,
isBefore,
getDay,
format,
parseISO,
} from 'date-fns';
import { toZonedTime, fromZonedTime } from 'date-fns-tz';
interface AvailableSlot {
start: Date; // UTC
end: Date; // UTC
displayStart: string; // Formatted in client timezone
displayEnd: string;
}
async function getAvailableSlots(
providerId: string,
serviceId: string,
date: Date,
clientTimezone: string
): Promise<AvailableSlot[]> {
// 1. Fetch provider's rules for this day of week
const dayOfWeek = getDay(date);
const { data: service } = await supabase
.from('services')
.select('duration_minutes, buffer_before_minutes, buffer_after_minutes')
.eq('id', serviceId)
.single();
const { data: rules } = await supabase
.from('availability_rules')
.select('*')
.eq('provider_id', providerId)
.eq('day_of_week', dayOfWeek)
.eq('is_active', true);
// 2. Check for date overrides
const dateStr = format(date, 'yyyy-MM-dd');
const { data: override } = await supabase
.from('availability_overrides')
.select('*')
.eq('provider_id', providerId)
.eq('override_date', dateStr)
.maybeSingle();
if (override && !override.is_available) {
return []; // Provider is off this day
}
// 3. Determine working windows (in provider's local time)
const providerTimezone = rules?.[0]?.provider_timezone ?? 'UTC';
const windows = override
? [{ start: override.start_time, end: override.end_time }]
: (rules ?? []).map((r) => ({ start: r.start_time, end: r.end_time }));
// 4. Fetch existing appointments for this provider on this date
const dayStartUtc = fromZonedTime(
startOfDay(toZonedTime(date, providerTimezone)),
providerTimezone
);
const dayEndUtc = fromZonedTime(
endOfDay(toZonedTime(date, providerTimezone)),
providerTimezone
);
const { data: existingAppointments } = await supabase
.from('appointments')
.select('buffer_start, buffer_end')
.eq('provider_id', providerId)
.eq('status', 'confirmed')
.gte('start_time', dayStartUtc.toISOString())
.lte('start_time', dayEndUtc.toISOString());
// 5. Generate slots by iterating through windows
const slots: AvailableSlot[] = [];
const totalDuration =
service!.duration_minutes +
service!.buffer_before_minutes +
service!.buffer_after_minutes;
for (const window of windows) {
// Convert window times to UTC for comparison
const [startH, startM] = window.start.split(':').map(Number);
const [endH, endM] = window.end.split(':').map(Number);
const zonedDate = toZonedTime(date, providerTimezone);
let cursor = fromZonedTime(
new Date(zonedDate.getFullYear(), zonedDate.getMonth(),
zonedDate.getDate(), startH, startM),
providerTimezone
);
const windowEnd = fromZonedTime(
new Date(zonedDate.getFullYear(), zonedDate.getMonth(),
zonedDate.getDate(), endH, endM),
providerTimezone
);
while (true) {
const slotStart = addMinutes(cursor, service!.buffer_before_minutes);
const slotEnd = addMinutes(slotStart, service!.duration_minutes);
const bufferEnd = addMinutes(slotEnd, service!.buffer_after_minutes);
if (isAfter(bufferEnd, windowEnd)) break;
// Check for conflicts with existing appointments
const hasConflict = (existingAppointments ?? []).some((apt) => {
const aptStart = parseISO(apt.buffer_start);
const aptEnd = parseISO(apt.buffer_end);
return isBefore(cursor, aptEnd) && isAfter(bufferEnd, aptStart);
});
if (!hasConflict && isAfter(slotStart, new Date())) {
slots.push({
start: slotStart,
end: slotEnd,
displayStart: format(
toZonedTime(slotStart, clientTimezone),
'h:mm a'
),
displayEnd: format(
toZonedTime(slotEnd, clientTimezone),
'h:mm a'
),
});
}
cursor = addMinutes(cursor, service!.duration_minutes);
}
}
return slots;
}This function is the core of the booking page. It runs every time a user selects a date and returns the available slots formatted in their local timezone.
Conflict Prevention: Belt and Suspenders
Double-booking is the worst bug a scheduling system can have. We use three layers of protection:
Layer 1: Application-level check. The slot generation function above filters out times that conflict with existing appointments. Under normal conditions, users only see available times.
Layer 2: Database function with advisory locks. When a booking is submitted, a PostgreSQL function acquires an advisory lock on the provider before checking availability and inserting the appointment. This serializes concurrent booking attempts for the same provider.
create or replace function create_appointment(
p_service_id uuid,
p_provider_id uuid,
p_start_time timestamptz,
p_client_timezone text,
p_notes text default null
) returns uuid as $$
declare
v_service record;
v_end_time timestamptz;
v_buffer_start timestamptz;
v_buffer_end timestamptz;
v_appointment_id uuid;
v_conflict_count integer;
begin
-- Serialize bookings for this provider
perform pg_advisory_xact_lock(hashtext(p_provider_id::text));
-- Fetch service details
select * into v_service from services where id = p_service_id;
if v_service is null then
raise exception 'Service not found';
end if;
-- Calculate time boundaries
v_end_time := p_start_time + (v_service.duration_minutes || ' minutes')::interval;
v_buffer_start := p_start_time - (v_service.buffer_before_minutes || ' minutes')::interval;
v_buffer_end := v_end_time + (v_service.buffer_after_minutes || ' minutes')::interval;
-- Check for conflicts (including buffer times)
select count(*) into v_conflict_count
from appointments
where provider_id = p_provider_id
and status = 'confirmed'
and tstzrange(buffer_start, buffer_end) &&
tstzrange(v_buffer_start, v_buffer_end);
if v_conflict_count > 0 then
raise exception 'Time slot is no longer available';
end if;
-- Insert the appointment
insert into appointments (
service_id, provider_id, client_id,
start_time, end_time, buffer_start, buffer_end,
client_timezone, status, notes
) values (
p_service_id, p_provider_id, auth.uid(),
p_start_time, v_end_time, v_buffer_start, v_buffer_end,
p_client_timezone, 'confirmed', p_notes
) returning id into v_appointment_id;
return v_appointment_id;
end;
$$ language plpgsql security definer;Layer 3: Database exclusion constraint. The GiST exclusion constraint we defined earlier is the final safety net. Even if the advisory lock fails (it should not, but defense in depth matters), the database will reject an overlapping insert.
Three layers might seem excessive. It is not. We have seen every single-layer approach fail in production under concurrent load. The advisory lock handles the race condition. The exclusion constraint handles the edge case where something goes wrong with the lock. The application check provides the good user experience of not showing unavailable slots.
Timezone Handling: The Hard Part
Timezones are the single most common source of bugs in booking systems. Here are the rules we follow:
Rule 1: Store all times in UTC. The start_time and end_time columns in the appointments table are timestamptz, which PostgreSQL stores as UTC. No exceptions.
Rule 2: Availability rules use local time. A provider sets “available 9 AM to 5 PM” in their local timezone. This is stored as a time column with a separate provider_timezone column. The conversion to UTC happens at query time.
Rule 3: Display in the viewer’s timezone. When a client in New York sees a slot, it shows in ET. When the provider in Manila sees the same appointment, it shows in PHT. The underlying data is identical — only the presentation changes.
Rule 4: Store the client’s timezone on the appointment. This matters for reminders and confirmations. If you send a reminder email, it should show the time in the timezone the client used when booking.
// Timezone detection on the booking page
const clientTimezone = Intl.DateTimeFormat().resolvedOptions().timeZone;
// Pass to the booking function
await supabase.rpc('create_appointment', {
p_service_id: selectedService.id,
p_provider_id: providerId,
p_start_time: selectedSlot.start.toISOString(), // UTC
p_client_timezone: clientTimezone,
});The DST trap. Daylight saving time transitions create slots that do not exist (spring forward) or exist twice (fall back). When generating slots for dates near DST transitions, always convert through a proper timezone library (we use date-fns-tz). Never do manual hour arithmetic.
Calendar Sync: Google and Apple
Most providers expect their bookings to appear in their primary calendar. We support Google Calendar sync with a bidirectional integration:
Outbound sync (MindHyv to Google). When an appointment is created, we create a Google Calendar event via the Google Calendar API. When it is cancelled, we delete the event.
import { google } from 'googleapis';
async function syncToGoogleCalendar(
appointment: Appointment,
provider: ProviderWithTokens
) {
const oauth2Client = new google.auth.OAuth2(
process.env.GOOGLE_CLIENT_ID,
process.env.GOOGLE_CLIENT_SECRET
);
oauth2Client.setCredentials({
access_token: provider.google_access_token,
refresh_token: provider.google_refresh_token,
});
const calendar = google.calendar({ version: 'v3', auth: oauth2Client });
const event = await calendar.events.insert({
calendarId: 'primary',
requestBody: {
summary: `${appointment.service_name} with ${appointment.client_name}`,
description: appointment.notes ?? '',
start: {
dateTime: appointment.start_time,
timeZone: 'UTC',
},
end: {
dateTime: appointment.end_time,
timeZone: 'UTC',
},
reminders: {
useDefault: false,
overrides: [
{ method: 'popup', minutes: 30 },
{ method: 'email', minutes: 60 },
],
},
},
});
// Store the Google event ID for future updates/deletions
await supabase
.from('appointments')
.update({ google_event_id: event.data.id })
.eq('id', appointment.id);
}Inbound sync (Google to MindHyv). We use Google Calendar push notifications to detect when the provider creates events directly in Google Calendar. Those events are treated as blocked time, preventing booking conflicts.
For Apple Calendar, we generate .ics files that users can subscribe to. Apple Calendar does not have a push API comparable to Google’s, so we use a CalDAV subscription URL that Apple Calendar polls periodically.
Reminder Notifications
Reminders reduce no-shows. We send them at two intervals: 24 hours before and 1 hour before the appointment.
The simplest reliable approach is a scheduled function that runs every 15 minutes and queries for appointments needing reminders:
-- Find appointments needing 24-hour reminders
select a.*, p.email as client_email
from appointments a
join profiles p on p.id = a.client_id
where a.status = 'confirmed'
and a.start_time between now() + interval '23 hours 45 minutes'
and now() + interval '24 hours 15 minutes'
and not exists (
select 1 from sent_reminders
where appointment_id = a.id
and reminder_type = '24h'
);The sent_reminders table prevents duplicate sends if the scheduled function runs multiple times within the window. Idempotency matters — nobody wants three reminder emails.
We covered notification architecture in more detail in our post on building a notification system.
Cancellation and Rescheduling
Cancellations need clear rules:
- Who can cancel? Both the client and the provider, but the side effects differ. Client cancellations might trigger a rebooking prompt. Provider cancellations should trigger an apology and a reschedule offer.
- Cancellation window. Many businesses have a 24-hour cancellation policy. The system should enforce this: clients can cancel freely more than 24 hours before, but cancellations within the window might incur a fee or be blocked.
- Slot release. When an appointment is cancelled, the time slot should immediately become available for other clients. This means updating the appointment status and removing the Google Calendar event.
async function cancelAppointment(
appointmentId: string,
cancelledBy: 'client' | 'provider',
reason?: string
) {
const { data: appointment } = await supabase
.from('appointments')
.select('*, service:services(*)')
.eq('id', appointmentId)
.single();
if (!appointment) throw new Error('Appointment not found');
const hoursUntil =
(new Date(appointment.start_time).getTime() - Date.now()) / 3600000;
// Enforce cancellation policy for client cancellations
if (cancelledBy === 'client' && hoursUntil < 24) {
throw new Error(
'Cancellations within 24 hours are not permitted for this service.'
);
}
const status =
cancelledBy === 'client'
? 'cancelled_by_client'
: 'cancelled_by_provider';
await supabase
.from('appointments')
.update({ status, cancellation_reason: reason })
.eq('id', appointmentId);
// Remove from Google Calendar if synced
if (appointment.google_event_id) {
await removeGoogleCalendarEvent(appointment.google_event_id);
}
// Notify the other party
await createNotification({
recipientId:
cancelledBy === 'client'
? appointment.provider_id
: appointment.client_id,
type: 'booking_cancel',
referenceId: appointmentId,
});
}Rescheduling is implemented as a cancellation followed by a new booking. We considered building a dedicated reschedule flow that preserves the appointment ID, but the added complexity was not worth it. Cancel and rebook is simpler, and the user experience is nearly identical.
Lessons From MindHyv’s Booking System
After shipping the booking module and watching real users interact with it, here is what we learned:
-
Buffer times matter more than you think. Providers consistently underestimate how much time they need between appointments. We defaulted to 15-minute buffers and let providers reduce them, rather than defaulting to zero and hoping they would add buffers.
-
Show availability in the client’s timezone by default. We initially showed times in the provider’s timezone with a converter. Users were confused. Showing slots in the client’s detected timezone eliminated booking errors almost entirely.
-
The confirmation email is the product. For many clients, the booking confirmation email is the only thing they save. It needs to be clear, include the time in their timezone, the provider’s contact info, and instructions for cancellation. We treat confirmation emails as a first-class design surface.
-
Calendar sync is not optional. We shipped without Google Calendar sync initially, expecting providers to check MindHyv for their schedule. Nobody did. Everyone kept using Google Calendar. The sync was our most requested feature in the first month.
-
Recurring appointments are a different product. We scoped and decided not to build recurring appointments (book every Tuesday at 10 AM for 8 weeks) in the first version. The conflict resolution for recurring bookings is an order of magnitude more complex. We will add it, but only when enough users ask for it.
The full MindHyv case study is in our post on building an all-in-one business platform. If you are building a booking system and want to talk through the architecture, reach out at hello@threshline.com. We have been through the edge cases and can help you avoid the ones that bite hardest.