A Cron Job That Kept Booking the Same Kapadokya Room Twice

A guesthouse in Ürgüp runs a small reservation script on a €5 VPS. Every ten minutes, cron fires a PHP script that polls the booking inbox over IMAP, parses room requests, and writes them into a MySQL table. About 40–60 bookings a week in summer, so volume is tiny — which is exactly why nobody noticed the duplicate rows for months. The owner only saw it when two guests showed up for the same cave room on the same night and one of them had a screenshot of a confirmation email I had never seen.

The script had no idempotency key. It parsed the email, inserted a row, and sent a confirmation. If the IMAP connection stalled after the insert but before the fetch was marked \u201cseen\u201d, the next run re-read the same message and inserted it again. I added a hash of the message body plus the sender and the requested dates, and made the insert conditional:

ALTER TABLE bookings
  ADD COLUMN msg_hash CHAR(64) NOT NULL,
  ADD UNIQUE KEY uniq_msg_hash (msg_hash);

$hash = hash('sha256', $rawBody . '|' . $from . '|' . $checkin . '|' . $checkout);
$stmt = $pdo->prepare(
  'INSERT INTO bookings (guest, checkin, checkout, msg_hash)
   VALUES (?, ?, ?, ?)
   ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)'
);
$stmt->execute([$guest, $checkin, $checkout, $hash]);

The ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id) trick matters: it returns the existing row’s id instead of throwing, so the confirmation email is only sent when $pdo->lastInsertId() belongs to a row we just created. Before that, a duplicate insert threw a fatal error, the script died, and cron quietly ran it again ten minutes later. The bug was hiding behind the retry.

The trade-off is that the hash is brittle. If the guest replies to the thread with \u201cactually, one more night\u201d, the body changes and it becomes a new booking — which is what we want, but it also means a mail client that rewrites quoted text can produce a different hash for the same request. I keep the raw body in a raw_mail column so I can re-derive hashes if I ever change the normalization.

Where this fails: the unique key only covers exact repeats. It did nothing for a guest who sent the same request from two addresses, and nothing for the real failure mode — the owner replying to a guest in the same mailbox, which the parser also ingested as a booking. I added a From: allowlist after that, and it still breaks whenever the booking form’s sender address changes.

If you run something similar: how do you decide which part of an inbound email is the actual idempotency key, versus noise that should be ignored?