Building a WhatsApp booking agent that can't double-book

Footfall is an AI receptionist I built that takes appointment bookings over WhatsApp. A customer messages a salon, asks what's free on Saturday, taps a time, and gets a confirmed slot and a reminder before the visit. The conversation turned out to be the easy part. The hard part is making sure two people chatting at the same moment can never be given the same chair.

The examples use an invented salon with two stylists, Priya and Ravi. Code is simplified from the real repository.

One booking, end to end

Footfall keeps its own calendar in Postgres rather than syncing with an external one. A booking is a row: a service, an optional staff member, a start and end as timestamptz, and a status. Only confirmed bookings take up time.

Each incoming message gets one agent turn: Claude with a set of tools, looping for up to six rounds. Everything the turn does (the booking insert, the reminder rows, the record of the reply) sits in one database transaction, which commits only after WhatsApp accepts the reply.

Free slots are a pure function

The core of availability has no database in it. It takes open windows, busy intervals and a service duration, and returns every start time on a 15-minute grid that fits.

def compute_slots(windows, busy, duration_minutes, step_minutes=15, not_before=None):
    """All start times where [start, start+duration) fits a window and overlaps no busy interval."""
    duration = timedelta(minutes=duration_minutes)
    step = timedelta(minutes=step_minutes)
    slots = []
    for win_start, win_end in windows:
        cursor = win_start
        if not_before is not None and cursor < not_before:
            # Snap forward to the next step boundary at/after not_before.
            steps = -(-(not_before - cursor) // step)  # ceil division
            cursor = win_start + steps * step
        while cursor + duration <= win_end:
            slot_end = cursor + duration
            if all(slot_end <= b_start or cursor >= b_end for b_start, b_end in busy):
                slots.append(cursor)
            cursor += step
    return slots

Three details matter:

• Intervals are half-open. A booking from 10:30 to 11:00 doesn't block a slot starting at 11:00, so back-to-back appointments work.
• The service has to fit. A 45-minute service in a 10:00 to 11:00 window can start at 10:00 or 10:15 and nowhere else.
• "Now" snaps forward. not_before is the current time. At 10:40 on a 30-minute grid, the first offer is 11:00.

Each rule is a short unit test with no fixtures, and the same function later decides both what the agent offers and what it accepts.

Every staff member has a calendar

DayCalendar loads one day: each active staff member's working hours (their own, or the shop's) and the confirmed bookings, grouped by who they're assigned to.

def _busy_for(self, staff_id):
    """Their own bookings plus unassigned ones (which block everyone)."""
    return self.busy_by_staff.get(None, []) + self.busy_by_staff.get(staff_id, [])
 
def slots_for(self, service, step_minutes, staff=None):
    not_before = self.now or datetime.now(self.tz)
    union = set()
    for member in [staff] if staff else self.staff:  # a named person, or anyone
        union.update(compute_slots(self.staff_windows.get(member.id, self.shop_windows),
                                   self._busy_for(member.id), service.duration_minutes,
                                   step_minutes, not_before))
    return sorted(union)

Say Priya and Ravi both work 10:00 to 12:00 and Priya has a haircut at 11:00. Ask for anyone and 11:00 is still offered, because Ravi is free. Ask for Priya by name and 11:00 disappears while 11:30 appears. If nobody names a stylist, the first free team member is assigned automatically.

A booking with no one assigned blocks everyone: the code can't know whose chair it's in. Offering one slot too few costs little; one too many sends someone to a busy stylist.

The model talks; the code decides

The agent has nine tools, from get_available_slots and create_booking to handoff_to_owner. The system prompt is blunt about where facts come from:

Prices and timings come ONLY from tools. Never invent or discount them.

The model never sees a calendar. It asks get_available_slots for a service and a date and gets every free start time that day. Its job is the language: turning "something after 6 on Saturday with Priya" into a date, a window and a name, then offering up to ten times as tap-to-select options (WhatsApp list menus cap at ten rows).

Minutes can pass between a customer seeing 11:00 and tapping it, so create_booking trusts nothing from earlier. It reloads the day and checks again:

if name == "create_booking":
    service = find_service(args["service_name"])
    start_local = datetime.fromisoformat(f"{args['date']}T{args['time']}").replace(tzinfo=tz)
    staff = await find_staff(args["staff_name"]) if args.get("staff_name") else None
 
    await booking_service.lock_calendar(session, shop.id)
    day = await slot_service.load_day(session, shop, start_local.date())
    if staff is not None:
        if start_local not in day.slots_for(service, step, staff=staff):
            return f"{staff.name} isn't free then. Call get_available_slots and offer alternatives."
    elif day.staff:
        free = day.free_staff_at(service, start_local)
        if not free:
            return "That slot is no longer free. Call get_available_slots and offer alternatives."
        staff = free[0]
 
    booking = await booking_service.create_booking(session, shop, customer, service,
                                                   start_local, staff=staff)
    return f"Booked: {service.name} on {start_local:%a %d %b at %I:%M %p}."

A refusal is a normal return value phrased as an instruction, so the model's next move is obvious: offer another time. Even an unexpected exception comes back as a tool result, so the turn can recover or hand off.

Two customers, one 11:00 slot

Re-checking isn't enough on its own. Two customers see 11:00 free and tap it in the same second. Both create_booking calls load the day, both find 11:00 empty, and both insert. The owner logging a walk-in from the dashboard is a third writer in the same race.

The fix makes the check and the write a single step per shop:

async def lock_calendar(session: AsyncSession, shop_id: str) -> None:
    """Serialise "is this slot free? then book it" per shop.
 
    Call it BEFORE loading the day's slots; the lock is transaction-scoped,
    so the second caller waits for the first to commit and then sees its booking.
    """
    await session.execute(
        text("SELECT pg_advisory_xact_lock(hashtextextended(:key, 0))"),
        {"key": f"calendar:{shop_id}"},
    )

pg_advisory_xact_lock locks an arbitrary 64-bit number, and hashtextextended turns calendar:<shop id> into one. A second transaction asking for the same number waits until the first commits or rolls back, then sees the new booking. There's no release call to forget: the lock ends with the transaction.

Three choices are deliberate:

• Lock before reading. Locking after load_day would mean waiting politely, then acting on a stale calendar.
• Transaction-scoped, not session-scoped. The app reaches Postgres through PgBouncer in transaction pooling mode, which hands the server connection to someone else when a transaction ends. A session-level lock would outlive the request that took it.
• One lock per shop, used everywhere. The agent's book and modify tools and the owner's create and reschedule endpoints all take it. A unique index on start time couldn't do this job: a 45-minute haircut at 10:45 overlaps one at 11:00 without sharing a start time. The lock lets the same DayCalendar logic that produced the offer guard the write.

A test that proves the race is real

A concurrency fix you can't demonstrate is a guess. The integration tests run against a real local Postgres and fire two bookings at the same slot:

async def test_same_slot_booked_once(session_factory, world):
    first, second = await asyncio.gather(
        _book(session_factory, world, 0, hold=0.5, start_after=0),
        _book(session_factory, world, 1, hold=0, start_after=0.1),
    )
    assert first.startswith("Booked")
    assert "no longer free" in second
    assert await _confirmed(session_factory, world) == 1
 
 
async def test_without_lock_the_race_double_books(session_factory, world, monkeypatch):
    """Proves the test above exercises a real race, not a lucky ordering."""
    monkeypatch.setattr(booking_service, "lock_calendar", no_lock)  # an async no-op
    await asyncio.gather(...)  # the same two bookings as above
    assert await _confirmed(session_factory, world) == 2

The first booking holds its transaction open for half a second after the tool runs, like a live turn still waiting on Claude. The second test is the one I'd keep if I could keep only one: it swaps the lock for a no-op and asserts that two bookings now exist. Without it, the first could pass forever by luck of scheduling.

Changing a booking without making a second one

"Can I come at 4 instead?" and "Can Ravi do it?" are ordinary things to say to a receptionist. Before modify_booking existed, they were also a way to end up with two bookings for one visit.

The interesting line lifts the old booking out of the way before checking:

await booking_service.lock_calendar(session, shop.id)
# Temporarily lift the old booking so it doesn't block its own replacement.
target.status = "cancelled"
await session.flush()
day = await slot_service.load_day(session, shop, new_start.date())
free = day.free_staff_at(old_service, new_start)  # or the named person's slots
if not free:
    target.status = "confirmed"
    await session.flush()
    return "That change isn't possible. Offer alternatives via get_available_slots."
await booking_service.void_pending_outbox(session, target.id)
await booking_service.create_booking(session, shop, customer, old_service, new_start,
                                     staff=free[0], moved_from=old_start)

Moving an 11:00 haircut to 11:15 overlaps the haircut itself, so without the temporary cancel every small move would be refused. If the new time doesn't work, the old booking is restored. If it does, its reminders are voided, the new booking queues fresh ones, and moved_from makes the owner's alert say "Booking moved" with both times.

The webhook: store first, answer second

Meta delivers each WhatsApp message to a webhook, signed with an HMAC SHA-256 header that's verified first. There's no queue between webhook and agent; the answer runs inside the request. The order of the commits is what makes that safe.

for msg in value["messages"]:
    try:
        async with session_factory() as session:
            stored = await _store_inbound(session, phone_number_id, msg, contacts)
            await session.commit()                  # transaction 1: the message exists
    except Exception:
        store_failed = True
        continue
    if stored is not None:
        await _answer(session_factory, stored)      # transaction 2: agent + reply
 
if store_failed:  # nothing saved for at least one message: let Meta redeliver
    raise HTTPException(status_code=500, detail="Could not store message.")
return {"status": "ok"}

Once the first transaction commits, the message is in the owner's inbox whatever happens next. The webhook returns an error only if that save failed, so Meta delivers again.

Redelivery means duplicates, so each message is stored with Meta's message id in a uniquely indexed column. A second delivery hits the constraint, rolls back, and is ignored. A test posts the same message twice and asserts the agent ran once.

When the answer fails

The second transaction is where things go wrong: Claude is overloaded, or WhatsApp refuses the send. Either way the whole turn rolls back, including any booking made during it. A test books 3 pm, fails the send, and checks there are no bookings and one queued retry. A booking the customer was never told about is worse than none.

The customer then gets one short holding reply per outage, saying the message arrived and an answer is coming. It's stored with its own message type and hidden from the agent's history; otherwise the history would end on the assistant's side and the retry would think there was nothing to answer.

The retry is an outbox row of kind agent_retry, due a minute later. Each attempt runs in its own transaction with the row claimed using FOR UPDATE SKIP LOCKED, and the agent's writes sit in a savepoint that a failure discards. The wait doubles each time: roughly one, two, then four minutes. After the third failure the chat switches to human mode and the owner is told a customer is waiting. If someone replies first, the pending retry cancels itself.

Reminders and no-shows live in an outbox

Every message the shop sends on its own initiative is a row in an outbox table with a kind, a recipient and a send_at time. Creating a booking adds reminder rows for 24 hours and 2 hours before the visit (skipping any already in the past) plus an owner alert, all in the booking's own transaction. A booking that rolls back takes its reminders with it. Cancelling or moving one voids its pending rows, so nobody is reminded about the old time.

pg_cron inside Postgres calls a tick endpoint every minute, with a once-a-day hosted cron as a backstop. Ticks can overlap, so rows are claimed with FOR UPDATE SKIP LOCKED; a test runs two ticks over six rows and checks each was sent exactly once. Failed sends are retried on later ticks, up to three attempts.

slot grid

15 min

slot grid

reminders before a visit

24h + 2h

reminders before a visit

grace before a no-show

30 min

grace before a no-show

retries before a human takes over

3

retries before a human takes over

The same tick sweeps for no-shows. A booking still marked confirmed 30 minutes after its end becomes no_show, and a follow-up is queued: "We missed you at the salon today! Reply 1 and we'll find you a new slot right away." Messages like this are written into the chat transcript too, so when the customer replies, the agent can see what they're answering.

Cancellations feed the waitlist: up to three people waiting on that date are told a slot has opened, first come, first served. That's exactly when people reply within seconds of each other, and the lock is what makes "first come" true.

Dates, time zones and the word "tomorrow"

Each shop has a time zone. Slot maths uses timezone-aware local datetimes, bookings are stored as timestamptz, and tools take YYYY-MM-DD and a 24-hour HH:MM in the shop's local time, so the model only ever deals in wall-clock time.

The subtler bug was about language. Conversations span days. A customer came back to a chat that held an older confirmation saying "tomorrow", and the agent read that old "tomorrow" as current and refused a real booking, telling them they already had one.

The fix had three parts. Every history line carries a timestamp like [23 Oct 11:05 AM], and the prompt says relative words in old messages are relative to their own timestamps. Each turn opens with the real date: "Today is Friday 2026-10-23 (Asia/Kolkata)". And the customer's actual upcoming bookings are listed, with an explicit line when there are none:

This customer has NO upcoming bookings right now. This list is the ONLY source of truth — even if old chat messages mention a booking, it is in the past or cancelled.

An empty list needed saying out loud. Leave it out, and the model fills the gap from whatever the history suggests.

What I'd take away

• Give the model a narrow job. It turns "after 6 on Saturday, Priya if she's free" into a tool call. Whether 18:30 is free is answered by code, from the database, every time.
• Check at write time, under a lock, with the code that made the offer. One definition of "free", used twice, is much harder to get wrong than two.
• Write the race as a test, then a test that proves the race exists. That turns "I think this is safe" into something I can show.
• Commit the customer's message before anything that can fail. A rolled-back turn can be retried. A lost message can't.

If I started again, I'd write the concurrency tests first. They and the lock arrived in a reliability pass about three months after the first end-to-end booking over WhatsApp, and the lock itself is one SQL statement.