A Kapadokya Guesthouse Booking Calendar That Fits in One File

Every spring I get the same message from a friend who runs six rooms in a village near Ürgüp: the booking calendar broke, people are double-booked, and the OTA extranet shows something different from what she has on paper. Last year I offered to rebuild the calendar small enough that she could understand it without me. It has been running a full season across roughly 1,400 reservation rows, two channels, and one very unreliable village connection.

The design constraint was not scale. It was that the thing must be inspectable by a non-engineer at 11 p.m. while a guest waits in the courtyard. So the whole calendar lives in one SQLite file and one Python script, no server, no daemon, no cloud account.

Schema first. A reservation is a half-open date range, which avoids the classic off-by-one on checkout day:

CREATE TABLE stays (
  id INTEGER PRIMARY KEY,
  room TEXT NOT NULL,
  guest TEXT NOT NULL,
  start_date TEXT NOT NULL,  -- ISO yyyy-mm-dd, check-in
  end_date   TEXT NOT NULL,  -- ISO yyyy-mm-dd, checkout, exclusive
  source TEXT NOT NULL,      -- 'direct' | 'ota' | 'walkin'
  CHECK (start_date < end_date)
);
CREATE INDEX stays_room_range ON stays(room, start_date, end_date);

The overlap query is the whole product. Two stays collide when each starts before the other ends:

SELECT a.room, a.guest, b.guest, a.start_date, a.end_date
FROM stays a JOIN stays b
  ON a.room = b.room AND a.id < b.id
 AND a.start_date < b.end_date AND b.start_date < a.end_date;

Insert goes through a transaction that runs that check first and refuses on a hit. The CLI is three commands: add, list --room, check. That is it. check prints overlaps and exits nonzero, so she can run it after any manual edit.

Why so small: every extra moving part is another thing that silently breaks in a village where the power blips and the router reboots. SQLite with PRAGMA journal_mode=WAL survived two unplanned power cuts mid-write during the season with no corruption, and the file is trivial to copy to a USB stick. The cost is real: only one person can write at a time, there is no web UI, and I am still the one who fixes schema mistakes.

Where it fails. It assumes dates are dates; a booking that arrives as “after the 14th, probably” gets rejected rather than stored as a maybe, and she keeps those on paper. It does not sync with OTAs, so an OTA booking lands in the file only when she types it in, and the window between the email and the typing is exactly where a double-booking can still happen. I have also seen a stay entered with end_date equal to start_date by a tired hand; the CHECK constraint caught it, which is the only reason I trust the constraint more than the UI. And time zones: everything is local dates, no timestamps, so a guest arriving on a night bus at 05:00 is still “that day”, which matches how she thinks but not how a server would.

If you run something similar for a small property, how do you handle the gap between an OTA confirmation email and the moment it reaches your own calendar?