Booking system architecture: the decisions that matter

Situation: Two customers can request the same slot at the same millisecond; application-level checks race, and a single double booking costs trust and money.

Choice: Store each booking as a half-open tstzrange including buffers and enforce EXCLUDE USING gist (resource_id WITH =, during WITH &&) over held and confirmed rows; the app inserts and handles the violation.

• Rejected: Check availability then insert: race condition under load.
• Rejected: Distributed lock per resource: extra moving part and lock-expiry bugs.
• Rejected: Serializable transactions only: retries and complexity without a structural guarantee.
• Consequence: Overlaps are impossible by construction, even from bugs or admin scripts.
• Consequence: Requires the btree_gist extension and range-typed columns.
• Consequence: Group classes need a separate capacity mechanism.

Situation: Businesses think in local wall-clock hours; customers travel; daylight-saving shifts make fixed offsets wrong twice a year.

Choice: Persist instants as UTC, persist zone names not offsets, express weekly rules as local times in the business zone and expand them per date with the time zone database.

• Rejected: Store local timestamps without zones: ambiguous and non-comparable.
• Rejected: Store fixed UTC offsets: break at DST changes.
• Consequence: Correct across DST, including skipped and repeated hours.
• Consequence: Expansion happens in application code and must be tested on transition dates.
• Consequence: UI always shows the zone label.

Situation: Customers need minutes to enter details and pay, during which the slot must not be sold again, yet abandoned flows must not lock inventory.

Choice: Insert a held booking with a 10-minute hold_expires_at; expired holds are ignored by availability and cleared inside the insert transaction and by a sweeper.

• Rejected: No hold, book on payment: customers lose slots after paying.
• Rejected: Long holds: easy to hoard inventory.
• Consequence: Slightly lower peak utilisation from abandoned holds.
• Consequence: Hold TTL and per-customer caps are tunable.
• Consequence: Payment webhook must tolerate an already-expired hold and refund automatically.

Situation: Sending a reminder 24 hours and 2 hours before every booking must scale to millions of future events without per-booking timers.

Choice: Insert one reminder row per planned send with a UTC send_at and claim due rows with SKIP LOCKED every minute; cancel by status change.

• Rejected: In-memory or per-booking timers: lost on restart and not scalable.
• Rejected: External scheduler per event: cost and new infrastructure.
• Consequence: Delivery accuracy is the polling interval (about one minute).
• Consequence: Rescheduling must cancel and recreate reminder rows in the same transaction.
• Consequence: Daily caps bound notification cost.

Indexes are chosen from the questions the app asks most often. Each line below names a table and the question its main index answers.

• businesses: unique (slug) — public booking page URL.
• services: (business_id, active, position) — public service list in order.
• resources: (business_id, active, kind) — resource pickers.
• resource_services: unique (resource_id, service_id) — a resource offers a service once.
• availability_rules: (resource_id, weekday, valid_from) — expand a week of availability for one resource.
• time_off: (resource_id, during) — GiST overlap lookup when expanding availability.
• customers: unique (business_id, email) — one customer record per email per business.
• bookings: (resource_id, during) where status in ('held','confirmed') — GiST EXCLUDE constraint (resource_id WITH =, during WITH &&) makes overlapping bookings impossible and serves calendar range reads.

Forge64 matches your description to one of 12 reference architectures, writes the plan and data model into the project, generates the app, and runs a quality gate over the code before you see it. Each app gets its own Postgres database and sign-in with no per-user fee. Hosting on Forge Cloud and Forge Payments are rolling out.

This is a reference design for the category, not a promise that every item is generated today. Built-ins such as email, background jobs, file uploads and search are coming soon, so anything here that needs them is something you add yourself for now.

What is the most important architecture decision for a booking system?

Database exclusion constraint on tstzrange to prevent double booking. Store each booking as a half-open tstzrange including buffers and enforce EXCLUDE USING gist (resource_id WITH =, during WITH &&) over held and confirmed rows; the app inserts and handles the violation.

Do I have to make these decisions myself?

No. Forge AI plans them from your description and writes the decisions into the project as architecture records you can read and change.

What do the alternatives cost me?

For example, for "UTC instants plus IANA zones, availability rules in local business time": Store local timestamps without zones: ambiguous and non-comparable. Store fixed UTC offsets: break at DST changes.

• How to build a booking system
• Example prompt for a booking system
• Booking system security checklist
• Booking system scaling checklist
• Often combined: SaaS dashboard
• Often combined: marketplace
• All app types