Double booking prevention in a meeting programme with three sources of truth

A hosted buyer opens her agenda on the Tuesday morning and finds three things at 14:00. A keynote she registered for in the conference platform. A stand appointment an exhibitor booked through the exhibitor portal. A matchmade meeting at table 22 that your concierge team confirmed by email last Thursday.

Nothing malfunctioned. Double booking prevention failed because there was no single place where a 14:00 commitment existed, so each of the three systems checked its own calendar, found the slot empty, and wrote a booking. Three correct programs produced one impossible day.

Why does an overlap check inside one system fail?

Each system is doing a conflict check. The conference platform will not let her book two sessions at 14:00. The exhibitor portal will not let one exhibitor book two people onto the same stand table. The matchmaking engine will not place two meetings for the same buyer in one slot.

All three checks are correct within their own boundaries and all three are blind outside them. This is the ordinary distributed systems problem, and Bernstein, Hadzilacos and Goodman set out the framework for it in their 1987 book Concurrency Control and Recovery in Database Systems, published by Addison-Wesley and still the standard treatment. Their opening example is a seat reservation, which is the same shape as a table booking. Their correctness criterion is serializability: interleaved executions are correct when their effects match some serial order of the same transactions.

Three systems writing to three databases have no serial order at all. There is no schedule of the three writes, in any sequence, that produces a valid day for that buyer, because none of the writes can see the others. The problem is structural and no amount of validation inside any one system reaches it.

Bofill and colleagues, presenting the business to business meeting scheduling problem at IJCAI in 2023, state participant non-overlap as a hard constraint of the model. That is the right treatment inside a solver run. It says nothing about the bookings that arrive after the solver has finished, which on most shows is the majority of them.

The one table that fixes it

Hold a single availability table. One row per participant per slot, and every booking in the estate inserts into it.

The schema is small enough to write here. Participant id, slot id, booking type, booking reference, created timestamp. Put a unique constraint on the participant and slot pair. That constraint is the entire mechanism, because the database will now refuse the second insert for the same person and slot regardless of which system attempted it.

Then the discipline. Every system that creates a booking has to insert its availability row and its own booking record in one transaction, so either both exist or neither does. A system that books first and reserves availability afterwards has a window where two writers can both succeed, and that window is where your double bookings live. It is usually a few hundred milliseconds and it will find you on the busiest morning of the year.

Rejection rather than warning is the second discipline, and it is the one that gets negotiated away. Non-overlap belongs in the small set of limits a booking is never allowed to break, and drawing that line before the argument starts saves you having it at 08:30 on the opening morning. Somebody will argue for a soft conflict flag so the concierge team can override. What that produces is two live bookings, four confirmation emails and a queue item that nobody works before the show opens. Reject the write, return the conflict to the caller, and let the caller offer the requester a different slot while they are still looking at the screen.

What does this cost in bookings?

Less than the argument for soft conflicts assumes, and it is worth putting numbers on it before the meeting where it gets decided.

Take 200 buyers, 24 slots over two days, so 4,800 participant slots. Suppose your three systems together attempt 3,100 bookings across the campaign. If bookings were spread uniformly, collisions would be rare, but they are not uniform: 14:00 on day one takes roughly three times its share.

Say 240 of the 3,100 attempts hit an occupied slot. Under a hard constraint, all 240 are rejected and the requester picks another slot. If 70 per cent of those rebook successfully into an adjacent slot, you keep 168 and lose 72 bookings outright. Against 3,100 attempts, that is a loss of 2.3 per cent.

Under soft conflicts, all 240 confirm. Then the buyer arrives, honours one of the two, and the other exhibitor waits at a table for fifteen minutes. You have converted 72 declined requests into 240 confirmed meetings of which 240 will be honoured at most half the time, and every unhonoured one costs an exhibitor a slot they could have sold to somebody else. Losing 2.3 per cent of bookings at write time is the cheaper outcome by a wide margin, and it is the only one you can explain afterwards.

The three writers, and how to bring them in

Not every system will agree to write through your table on the same schedule, so bring them in by order of damage.

The matchmaking engine. Easiest, because you control it. Its bulk placement run writes every meeting's availability row inside the placement transaction, and any proposal whose insert fails is returned to the standby list instead of being placed.

The exhibitor portal. Second, and the one that generates the most surprise, because exhibitors book appointments at all hours and often against a buyer list exported days earlier. Their portal must call your availability service at confirmation time rather than validating against a stale export. If the exhibitor is offline or the export is old, the correct behaviour is a pending appointment that gets confirmed on write.

The conference platform. Usually a third party product with its own database and no interest in your table. Two options work. The good one is a webhook on session booking that inserts an availability row on your side and calls back to cancel if the insert fails. The workable one is a poll every few minutes that reads new session bookings and blocks the corresponding slots, which leaves a window where a hosted meeting can be placed into a slot that was taken four minutes ago. Session and agenda data belongs to Y9, and the only part of it that concerns you here is the timestamp at which a session commitment becomes a fact.

Slot alignment, which breaks everything quietly

The unique constraint assumes both writers mean the same thing by slot 14. On most shows they do not.

Hosted meetings run 20 minutes. Stand appointments run 30. Conference sessions run 45 or 60 and start on the hour. Three grids that do not divide into each other, so a session from 14:00 to 14:45 overlaps hosted slots 14:00, 14:20 and 14:40 and half of a 30 minute stand appointment starting at 14:30.

The fix is to stop using booking-type slots as the key. Pick the smallest unit that divides all of them, usually 5 or 10 minutes, and have every writer claim the full range of atomic units its booking covers. A 45 minute session at 14:00 claims nine 5 minute units. A 20 minute meeting claims four. The unique constraint now sits on participant and atomic unit, and the arithmetic works for any booking length anybody invents later.

This raises the row count. Two hundred buyers across a two day show with 480 minutes of floor time each is 96 atomic units per buyer per day, so 38,400 rows at full occupancy for a 200 buyer programme. That is nothing for a database and it removes the entire class of bugs where two grids disagree.

Travel buffers are the same mechanism used deliberately, and the question of how many usable units a buyer really holds once flights and transfers are subtracted is an availability problem rather than a locking one.

Where this stops

A unique constraint prevents two bookings. It does not prevent one bad booking.

If your availability data says a buyer is free from 09:00 to 18:00 and she is actually in a taxi until 11:30, the table will happily accept a 10:00 meeting and reject nothing, because there is no conflicting row. Every double booking has been eliminated and the buyer still misses the meeting. Prevention protects the schedule's internal consistency and has no view on whether the schedule matches the world.

The second limit is that this only covers systems that write through. An exhibitor who agrees a meeting with a buyer in the coffee queue has created a commitment that exists nowhere, and it will collide with something you placed. That collision looks identical to a system failure in the post-show data and it is not one. Some share of your no-shows are people honouring a promise your database never saw, and the log cannot tell you which ones.

The third is operational. A hard constraint means the concierge team will hit it during the show, at the worst moment, with an angry exhibitor at the desk. Give them a documented override that cancels the existing booking and creates the new one in a single transaction, with both events logged. An override that deletes the constraint is a disaster. An override that performs a swap is a normal Tuesday.

Start by querying last edition's bookings across all three systems, joining on participant and overlapping time, and counting the collisions. Most teams find between one and four per cent of confirmed meetings had a conflict nobody caught, and that count is the business case for the matchmaking availability table you can build in a week.