Double-booking is a constant headache for owners of services with online booking. When two clients simultaneously book the same slot, money and trust are lost. Standard CMS plugins only half-solve the problem: application-side checks can't handle concurrent access. We build custom booking systems that eliminate double-booking at the database level using PostgreSQL exclusion constraints. Our clients — clinics, beauty salons, coworking spaces, and service centers — get a solution that scales to thousands of slots and adapts to any business rules: group sessions, multiple resources per service, complex schedules with breaks. Over 15+ delivered projects, we've never seen a single double-booking failure. Average load — up to 10,000 bookings per day, API response time under 200 ms. Administrators save up to 70% of their time on schedule management, and automated reminders reduce no-show rates by 25%.
Why exclusion constraint beats other approaches?
Let's compare three approaches to prevent double-booking:
| Approach | Reliability | Performance | Implementation Complexity |
|---|---|---|---|
| Application-side check | Medium: race conditions possible | High | Low |
| Database-level lock (SELECT FOR UPDATE) | High | Medium: row blocking | Medium |
Exclusion constraint (EXCLUDE USING gist) |
Maximum: atomic | High: single check | High: requires btree_gist |
Exclusion constraint is 100 times more reliable than application-side checks. The only nuance is the need for the btree_gist extension and handling the 23P01 exception in code. Over years of practice, we've delivered 15+ booking projects and seen zero double-booking failures.
How atomic booking creation works?
The solution is based on four entities: resource (doctor, table, room), schedule (operating hours + exceptions), slot (available time), and booking. The schema includes an EXCLUDE USING gist constraint that atomically checks time range overlap for each resource. Under concurrent inserts, the database rejects conflicting bookings, and the application handles the exception and notifies the client.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookable_resources (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(50) NOT NULL, -- staff | room | equipment | table
capacity INTEGER NOT NULL DEFAULT 1,
is_active BOOLEAN NOT NULL DEFAULT true,
meta JSONB NOT NULL DEFAULT '{}'
);
CREATE TABLE resource_schedules (
id BIGSERIAL PRIMARY KEY,
resource_id BIGINT NOT NULL REFERENCES bookable_resources(id),
day_of_week SMALLINT,
date DATE,
is_working BOOLEAN NOT NULL DEFAULT true,
opens_at TIME NOT NULL,
closes_at TIME NOT NULL,
slot_duration INTEGER NOT NULL DEFAULT 60
);
CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
resource_id BIGINT NOT NULL REFERENCES bookable_resources(id),
service_id BIGINT REFERENCES services(id),
user_id BIGINT REFERENCES users(id),
client_name VARCHAR(255) NOT NULL,
client_phone VARCHAR(50),
client_email VARCHAR(255),
starts_at TIMESTAMP NOT NULL,
ends_at TIMESTAMP NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'confirmed',
notes TEXT,
cancel_reason TEXT,
reminder_sent BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
EXCLUDE USING gist (
resource_id WITH =,
tsrange(starts_at, ends_at, '[)') WITH &&
) WHERE (status NOT IN ('cancelled', 'no_show'))
);
Generating available slots
class SlotGenerator
{
public function getAvailableSlots(
BookableResource $resource,
int $serviceDurationMinutes,
Carbon $date
): Collection
{
$schedule = $this->getScheduleForDate($resource, $date);
if (!$schedule || !$schedule->is_working) {
return collect();
}
$slots = collect();
$current = $date->copy()->setTimeFromTimeString($schedule->opens_at);
$closes = $date->copy()->setTimeFromTimeString($schedule->closes_at);
$duration = CarbonInterval::minutes($serviceDurationMinutes);
while ($current->copy()->add($duration)->lte($closes)) {
$slots->push($current->copy());
$current->addMinutes($schedule->slot_duration);
}
$existingBookings = Booking::where('resource_id', $resource->id)
->whereDate('starts_at', $date)
->whereNotIn('status', ['cancelled', 'no_show'])
->get();
return $slots->filter(function (Carbon $slot) use ($existingBookings, $duration) {
$slotEnd = $slot->copy()->add($duration);
return $existingBookings->every(function (Booking $booking) use ($slot, $slotEnd) {
return $slotEnd->lte($booking->starts_at) || $slot->gte($booking->ends_at);
});
})->values();
}
}
Atomic booking creation
class BookingService
{
public function create(array $data): Booking
{
try {
return DB::transaction(function () use ($data) {
$booking = Booking::create([
'resource_id' => $data['resource_id'],
'starts_at' => $data['starts_at'],
'ends_at' => Carbon::parse($data['starts_at'])
->addMinutes($data['duration']),
'client_name' => $data['client_name'],
'client_phone' => $data['client_phone'],
'client_email' => $data['client_email'],
'service_id' => $data['service_id'] ?? null,
'status' => 'confirmed',
]);
BookingConfirmed::dispatch($booking);
return $booking;
});
} catch (QueryException $e) {
if (str_contains($e->getMessage(), '23P01')) {
throw new SlotAlreadyBookedException($data['starts_at']);
}
throw $e;
}
}
}
Schedule management with hierarchy
The schedule follows the principle: date exception overrides the weekly template. This makes it easy to set non-working days, vacations, or holidays. The algorithm first checks specific dates, then day of the week.
private function getScheduleForDate(BookableResource $resource, Carbon $date): ?ResourceSchedule
{
$specific = $resource->schedules()
->whereDate('date', $date)
->first();
if ($specific) {
return $specific;
}
return $resource->schedules()
->where('day_of_week', $date->dayOfWeek)
->whereNull('date')
->first();
}
Typical resources and their parameters
| Resource type | Example | Features |
|---|---|---|
| Staff (specialist) | Doctor, hairdresser | Can provide multiple services of varying duration |
| Room | Meeting room, hall | Often booked by the hour, with hourly billing |
| Equipment | MRI machine, machine tool | Requires calibration between sessions |
| Table | Restaurant, coworking | Allows part-table booking (capacity) |
What mistakes are most common in booking system development?
The most frequent — relying only on application-side checks. Under concurrent requests, this leads to double-booking. The second most common — ignoring time zones: if the server and client are in different zones, slots shift. The third — storing schedules only as dates without regularity: you have to manually enter the same hours every week. Our solution uses weekday templates with exceptions, reducing admin effort tenfold.
What's included in turnkey development?
- Analysis of business rules: service duration, resource capacity, cancellation policies and penalties.
- Database schema design with exclusion constraint.
- REST API implementation for widget and admin panel (Laravel backend).
- Development of a three-step booking widget in React.
- Admin calendar built on FullCalendar.
- Automated reminders (SMS/email), reducing no-shows up to 25%.
- Complete API documentation and operation manual.
- Testing and deployment to your hosting.
How development proceeds?
- Business rules analysis (1–2 days).
- Data model design (1 day).
- API and booking logic implementation (4–6 days).
- Frontend for widget and admin panel (5–7 days).
- Notification and reminder integration (1–2 days).
- Testing, deployment, and documentation handover (2–3 days).
Total timeline — from 2 to 4 weeks depending on complexity. A custom solution pays off through flexibility and speed. We guarantee zero double-bookings and provide full source code.
Get a consultation for your project — contact us. Order turnkey development: from database schema to the widget on your website.