Modelling availability for a bookings system

Availability in a bookings system is a question, not a row. In a restaurant bookings app I built, no database table stores open slots. Each request checks every candidate start time against the peak capacity held during that booking’s window. That check was easy. Choosing what one unit of capacity is was hard, and I chose it wrong first.

Availability is computed on every request, never stored

The app sells two things. The dining room takes table bookings, which hold seats for 90 minutes and need a deposit. Two boardrooms are sold as half days and full days, with no charge. Both run on Shopify, but the app keeps its own Booking table; the Shopify order is only the payment receipt. That split is the same app’s SQLite story, and the customer widget is in a widget in someone else’s page.

No table in the schema says “19:15 is free”. A booking row has a resource, a start instant, a duration, a party size and a state. Availability is a function of those rows, the service hours for that day of the week, and the current time. I wanted it this way because a stored list of free slots is a second source of truth. Every booking, cancellation, expiry and staff edit would have to update it, and the first one that forgot would sell a seat twice or hide a free one.

The function returns one of four statuses for each candidate start: available, capacity_full, lead_time or party_too_large. The order of the checks is deliberate. Party size comes first, because a party of 10 is refused at every time and the picker should say so once. Lead time comes next: boardrooms need 48 hours’ notice, so a same-day boardroom request never reaches the capacity query. Capacity runs last, because it is the only check that reads other bookings.

A dining room measured in covers, not tables

The dining room has no tables in the model. It has a pool of covers, one per seated guest, and the seeded room holds 40. A booking for 4 takes 4 covers for its 90 minutes. Start times sit on a 15-minute grid inside service hours, so a day that opens at 12:00 and closes at 22:30 offers 37 start times: 12:00 to 21:00, the last one that still fits 90 minutes before close.

The decision record lists two models I rejected. The first was discrete tables with an allocator: each table a row, and a bin-pack at booking time. That makes the staff keep a digital floor plan in sync with the real one. It also refuses bookings the host could seat by pushing two 4-tops together. The staff think in covers, in the record’s words “we can do N covers tonight”, so the model does too. The second was fixed sittings, such as 18:00 and 20:30. The brief spoke in durations, not sittings, and sittings would only remove start times from the same cover pool.

Peak concurrency over the window, not the sum

The capacity check is a sweep line. For a candidate window it collects every booking that overlaps it, turns each into a start event and an end event, and walks them in time order. The running total’s peak, plus the new party, must stay at or under the room’s capacity.

The peak matters because a sum over the window refuses bookings that fit. Take the 40-cover room with 20 covers booked from 18:00 to 19:30 and another 20 from 19:30 to 21:00. A party of 4 asks for 18:45. Its window, 18:45 to 20:15, overlaps both bookings, so the sum is 40 and the request looks full. At no moment are more than 20 guests seated, so the peak is 20, and 24 fits.

Intervals are half-open. A booking that ends at 19:30 and one that starts at 19:30 do not overlap, and the sort puts end events before start events at the same instant so the sweep never counts both. One of the 11 capacity tests exists for that boundary alone.

The cost is one query for the resource and one for its bookings, per candidate. A dining-room day is 37 candidates, so 74 small queries against a local SQLite file. I left them unbatched. At one restaurant’s volume that is a choice I can revisit, not a cost I have measured.

Holds that expire without a scheduled job

A customer who picks a time gets a pending booking before they pay. It counts against capacity at once and expires 15 minutes later if the deposit does not arrive. Without the hold, two customers can both see 19:15 free, both reach checkout, and the slower payer pays for a seat that is gone.

Nothing marks a hold expired. The capacity query counts a booking when it is confirmed, or when it is pending and its expiresAt is still in the future. An expired hold simply stops matching. That removes a cron job and a class of bugs where the job is late. It adds one rule: every check that asks “is this seat taken” must carry that filter. The decision record says so in one line, because a missing filter would let dead holds block live seats with no error anywhere.

The boardroom’s unit of capacity was wrong for five weeks

On 11 May the decision record called the boardrooms “the degenerate case” of the cover model: each room its own resource with capacity 1. It read well. The boardroom kind allowed parties of up to 12, though, and capacity still counted covers. A party of 8 asks for 8 units from a resource that has 1, so the model refused every boardroom booking above one person. The only boardroom capacity test booked a party of 1, and it passed.

I found it on 15 June. That day the boardrooms started to sell as morning and afternoon windows, and the first test I wrote for the change was a party of 8 in an empty room. It failed for exactly that reason. The fix gave each booking kind its own unit: a table booking consumes one unit per guest, and a boardroom booking consumes one unit whatever its party size.

The same day the two boardrooms became one resource with capacity 2. Customers never chose a room; staff assign room A or B on arrival. Two resources had made the customer pick between rooms they could not tell apart. A migration moved the second room’s bookings onto the first, renamed it, and set its capacity to 2. A third overlapping booking is now refused, and a test says so.

The lesson I took is narrow. “Capacity” is a number, but its unit is a domain decision, and a test that uses a party of 1 cannot tell covers from rooms.

A full day shares its start time with a half day

On 31 July the operator said the morning and afternoon labels were wrong: the room is sold as a half day or a full day. A full day had needed two bookings and two deposits. The new model has three session shapes, read from the service hours rather than hardcoded: 09:00 to 13:00 (240 minutes), 13:00 to 18:00 (300 minutes), and 09:00 to 18:00 (540 minutes).

A full day is one booking row with a 540-minute duration. The sweep line already makes it contend with both half days, so the capacity code did not change. The trouble was naming the slot. The full day and the morning half day start at the same instant, so a start time alone no longer says which session the customer picked. The booking request now carries the duration as well, and a resolver accepts a (start, duration) pair only when it matches one of the three shapes. A full-day duration with an afternoon start is refused as an invalid slot. Older widget bundles that send no duration still get a half day, so the API stayed backwards compatible.

The spec rejected two alternatives. Two linked half-day bookings would have put phantom pairs into capacity, the calendar and cancellation. A session column on the booking would have repeated what start and duration already say, at the cost of a migration. The glossary had banned “slot” as a word for a fixed window back in May: a booking has a start and a duration. That rule is why the full day needed no schema change.

What the model still does not guarantee

The capacity check runs again inside the transaction that writes the booking, so a stale picker cannot book a full window. On SQLite that is not a lock. Two transactions that start together can both pass the check and both write. The code says so in a comment on the one function that creates booking rows, which is the single place a serializable transaction or a lock would go. At one restaurant’s booking rate I accepted that, and wrote down where the fix belongs.

The availability core is four modules and 35 tests: the 15-minute grid, the sweep line, the session resolver and the function that joins them. The whole suite was 327 of 327 passing when I ran it for this post. Eighty-one days passed between the first decision record and the full-day change, and the two things that moved were the unit of capacity and the shape of a session. The sweep line kept its shape through both. The only edit to it was the number each booking adds to the running total.