`scripts/migrate_008_double_book_exclude.sql`

24 lines
-- TK-10346: race-proof no-double-book at the DB level (backstop to the app-level overlap guard).
-- Two PARTIAL exclusion constraints so stay-bookings and slot-bookings each guard their own mode.
-- (Originally committed as root-level "migration 007 double_book_exclude"; renumbered to 008 and
--  moved into scripts/ so the manual go-live migration pass does not skip it. 007 is now messages.)
BEGIN;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Date-range stays: no two active (confirmed|pending) bookings for the same place with overlapping nights.
ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap_stay;
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap_stay
  EXCLUDE USING gist (
    place_id WITH =,
    daterange(check_in, check_out, '[)') WITH &&
  ) WHERE (status IN ('confirmed','pending') AND check_in IS NOT NULL AND check_out IS NOT NULL);

-- Time-slot bookings (tours/services): no two active bookings for the same place with overlapping slots.
ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap_slot;
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap_slot
  EXCLUDE USING gist (
    place_id WITH =,
    tstzrange(slot_start, slot_end) WITH &&
  ) WHERE (status IN ('confirmed','pending') AND slot_start IS NOT NULL AND slot_end IS NOT NULL);
COMMIT;