How to stop double bookings in a Telegram bot (Python + SQLite)

#python #telegram #sqlite #tutorial

A booking bot looks simple until two clients tap the same time slot in the same second. Here is the approach I used in a small appointment bot for barbershops and salons. It needs no external services, only SQLite.

1. Never store free slots

The tempting design is a slots table with a taken flag. It goes stale the moment working hours change, and it gives you two things to keep in sync.

Instead, store only bookings and compute free slots when asked:

def free_slots(self, master, day, duration_min, now):
    hours = self.cfg["work_hours"].get(str(day.isoweekday()))
    if not hours:
        return []          # day off
    step = timedelta(minutes=self.cfg.get("slot_step_min", 30))
    need = timedelta(minutes=duration_min)
    cur = datetime.combine(day, time.fromisoformat(hours[0]))
    end = datetime.combine(day, time.fromisoformat(hours[1]))
    earliest = now + timedelta(minutes=self.cfg.get("min_notice_min", 60))
    busy = self._busy(master, day)   # [(start, end), ...] of active bookings
    slots = []
    while cur + need <= end:
        if cur >= earliest and all(cur + need <= b0 or cur >= b1 for b0, b1 in busy):
            slots.append(cur)
        cur += step
    return slots

The overlap test is the whole trick: a candidate is free if, for every existing booking, it ends before that booking starts or starts after it ends. This also handles services of different lengths, so a 90-minute service will not be offered 30 minutes before a taken slot.

2. Check and insert in one transaction

The slot list a client sees can be seconds old. So the final step re-checks inside the same transaction that writes the booking:

def book(self, user_id, client_name, phone, service, master, start, now):
    svc = self.service(service)
    with self.db:  # one transaction: check and insert together
        if start not in self.free_slots(master, start.date(), svc["duration_min"], now):
            return None
        cur = self.db.execute("INSERT INTO bookings(...) VALUES (...)", (...))
        return cur.lastrowid

If book returns None, the bot tells the client the slot was just taken and shows the menu again. With a single bot process and SQLite this is enough. If you ever run several workers, add a unique index on (master, start) for active bookings as a second line of defence.

3. Send each reminder exactly once

Reminders are a loop that runs every minute. Without care it sends the same reminder sixty times an hour. One flag per reminder type fixes that (simplified):

rows = db.execute(
    "SELECT * FROM bookings WHERE status='active' AND reminded_day=0 "
    "AND start>? AND start<=?", (now, now + timedelta(hours=24))).fetchall()
for r in rows:
    db.execute("UPDATE bookings SET reminded_day=1 WHERE id=?", (r["id"],))

One edge case: someone books for two hours from now. Both the "day before" and the "two hours before" windows match at once. I mark both as sent but only deliver the short-notice one.

4. Test the logic without Telegram

Because the booking logic takes now as an argument and never touches the Telegram API, it runs against an in-memory database:

def test_same_slot_twice():
    b = Booking(cfg, ":memory:")
    start = datetime(2026, 11, 16, 12, 0)
    assert b.book(1, "Ivan", "+100000000", "Haircut", "Alex", start, MON)
    assert b.book(2, "Peter", "+100000001", "Haircut", "Alex", start, MON) is None

Passing the clock in as a parameter is what makes reminder and "minimum notice" rules testable at all.

The full bot

The snippets above come from a complete bot: service, specialist, day and time selection, client cancellations, owner notifications and admin commands. If you would rather start from working code than assemble it yourself, I sell the template here: https://payhip.com/b/VH20Y ($19, source code and setup guide).

Questions about any of the parts are welcome in the comments.

DEV Community