Stop 19 · Build Full-Stack Portfolio ProjectsProjects · 8/12

Booking & Reservation System

Part of the Full Stack Developer Roadmap

In short

Two people click 'Book 6 pm' at the same second. Only one can win. A booking system is all about making that impossible to get wrong, plus handling time zones without tears.

A system for booking time slots or resources (appointments, tables, rooms) — practice for handling scheduling conflicts and time-based data correctly across the stack.

The purple blob holds a badminton racket and hangs a booking card on a hook by the court gate.

Think of it like this

The badminton court slot

The college court has one hook per hour. Whoever hangs their card on the 6 pm hook first gets it. A second card simply won't fit. Your database should work like that hook, not like a polite request.

Watch it happen

Two clicks, one slot

Riya

Kabir

API

Postgres

POST court 3, 6–7 pm

1/6Riya books court 3 for 6 to 7 pm.

The big ideas

What you need to know

Let the database guard

A 'check then insert' in code has a race. A database constraint is the only check that can't be skipped.

Store UTC, show local

Save timestamptz values and convert to the user's time zone only in the UI.

Generate slots from rules

Store opening hours and slot length, then compute free slots, instead of storing every empty slot.

Hold, then confirm

For paid bookings, hold a slot for a few minutes while payment happens, then confirm or release it.

See it in code

Walk through the code

sql

1CREATE EXTENSION IF NOT EXISTS btree_gist;2 3CREATE TABLE bookings (4  id        bigserial PRIMARY KEY,5  court_id  int NOT NULL,6  user_id   bigint NOT NULL,7  during    tstzrange NOT NULL,8  EXCLUDE USING gist (court_id WITH =, during WITH &&)9);10 11-- 6 to 7 pm IST on 2 Nov, stored as UTC internally12INSERT INTO bookings (court_id, user_id, during)13VALUES (3, 42, tstzrange('2026-11-02 18:00+05:30', '2026-11-02 19:00+05:30'));14 15-- An overlapping insert for court 3 now fails with 23P01 (exclusion_violation),16-- which your API turns into 409 Slot taken.

1/4btree_gist lets a plain integer column (court_id) take part in a gist exclusion constraint.

A Postgres exclusion constraint: no two rows for the same court may have overlapping time ranges. The database refuses double bookings, even when requests race.

Save yourself the pain