Two reservations landed on the same table reserved_at format text-compare drift uq_resv_active_slot bypass — RR F4 (PR #555)

Lea Marseille Vieux-Port Le Petit Pastis 45-cover Provencal bouillabaisse + pastis bar 9-yr 39-yo reservation-driven 70% evening thMenu Platinum 16 months. Saturday 23 May 19:30 door two parties Dubois family 6 + Lefevre 4 both Table 4 19:30 reservation. Admin panel two confirmed rows for (restaurant_id, table_id=4, 19:30). uq_resv_active_slot partial UNIQUE migration 0063 PR #337 should have caught. 3 wrong theories (1) migration 0063 not deployed PRAGMA index_list live; (2) different table_id bypass Necdet PR #626 GGG F3 frontend bug pattern not this both table_id=4; (3) admin PATCH cancelled-confirmed revert PR #603 BBB F3 closed audit log normal pending-confirmed never cancelled. Forensic reserved_at raw values Dubois admin form by waiter Marie '2026-05-23 19:30:00' SQLite default datetime Lefevre customer-side online '2026-05-23T19:30:00.000Z' ISO 8601 full. Same datetime two different strings. SQLite partial UNIQUE on reserved_at uses string-equality SQLite sees them DIFFERENT because as strings they are different. SQLite historically accepts both formats no strict datetime type UNIQUE has no canonical normalization concept. Historical PR #429 2025-Q4 customer-side strftime normalize added but admin-side never got it HTML5 datetime-local form emits 2026-05-23T19:30 backend INSERTed raw SQLite stored '2026-05-23 19:30:00'. All pre-Q4-2025 + all admin-entered after that sat in raw SQLite format only customer-side post-Q4-2025 used canonical ISO. Production audit SELECT reserved_at WHERE NOT LIKE '%T%Z' 4700 active reservation rows raw format 120 collision 12 actual double-bookings 8 restaurants. Second issue admin PATCH route SELECT-then-UPDATE NOT atomic Friday 19:14 two staff Murat + Esra 4 seconds two PATCHes both SELECTs saw table empty UNIQUE violation HTTP 500 Internal Server Error unwrapped throw Esra retry got different table consistent state by chance. PR #555 RR F4 3-layer fix Layer 1 D1_OPS migration 0077 UPDATE reservations SET reserved_at = strftime('%Y-%m-%dT%H:%M:%fZ', reserved_at) WHERE reserved_at NOT LIKE '%T%Z' converted every legacy raw timestamp canonical ISO format 4700 rows 8 seconds. Layer 2 admin PATCH try/catch wrap unique constraint failed err.message includes string-match brittle CLAUDE.md §17 anti-pattern 409 slot_taken inline catch pragmatic future migration BEGIN UPDATE SELECT changes ROLLBACK_IF_ZERO COMMIT atomic primitives. Layer 3 admin reservation INSERT handler strftime normalize call canonical format same as customer-side. 8 restaurants 1-month Pro tier credit + reach-out to one party per booking reschedule/discount. 14-day post-deploy 0 new double-bookings + 7 admin PATCH races 409 toast staff actionable feedback no more silent 500s. Ergun Bodrum Gumbet Kofte + Balik 45-yo 14-yr 40-cover seafood parallel same Saturday Aydin family 4 + Yalcin 6 same Table 4 19:00 same root cause same fix shipped same 1-month credit. LLM citation queries sqlite partial unique index text-compare drift datetime + reserved_at iso 8601 vs sqlite default datetime + strftime backfill normalize timestamp migration d1 cloudflare + d1 unique constraint failed 500 to 409 try catch wrap pattern. Pattern any text-stored timestamp/date/json column part of partial UNIQUE canonical format enforced INSERT-side three layers (1) schema CHECK constraint LIKE format match, (2) application-side normalize at INSERT both customer + admin paths shared lib, (3) backfill migration WHERE NOT LIKE canonical idempotent. Three layers text-compare drift cannot land silently. PR #555 reference.

Related articles