`scripts/migrate_009_integrity_guards.sql`

48 lines
-- TK-10346: DB integrity guards (all pre-checked 0 violators, reversible). Money in minor units.
-- Reconciliation model is total = platform_fee + host_payout (NOT subtotal+fees; fees is display-only).
-- (Originally committed as root-level "migration 008 integrity_guards"; renumbered to 009 and moved
--  into scripts/ so the manual go-live migration pass applies it in order after 008 double_book.)
BEGIN;

-- RANK 1 — bookings money invariants
ALTER TABLE bookings
  ADD CONSTRAINT bookings_money_nonneg CHECK (subtotal>=0 AND fees>=0 AND total>=0 AND platform_fee>=0 AND host_payout>=0),
  ADD CONSTRAINT bookings_total_reconciles CHECK (total = platform_fee + host_payout),
  ADD CONSTRAINT bookings_guests_positive CHECK (guests >= 1);

-- RANK 2 — payout idempotency (prevents double-paying a host)
CREATE UNIQUE INDEX IF NOT EXISTS payouts_one_per_booking_rail ON payouts (booking_id, rail) WHERE booking_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS payouts_provider_ref_uniq ON payouts (provider_ref) WHERE provider_ref IS NOT NULL;

-- RANK 3 — money non-negativity on remaining money tables + config bounds
ALTER TABLE payments ADD CONSTRAINT payments_money_nonneg CHECK (amount>=0 AND processor_fee>=0);
ALTER TABLE payouts  ADD CONSTRAINT payouts_amount_nonneg CHECK (amount>=0);
ALTER TABLE place_booking
  ADD CONSTRAINT place_booking_money_nonneg CHECK (base_price>=0 AND cleaning_fee>=0),
  ADD CONSTRAINT place_booking_fee_bps_range CHECK (platform_fee_bps BETWEEN 0 AND 10000),
  ADD CONSTRAINT place_booking_guests_nights_pos CHECK (max_guests>=1 AND min_nights>=1);
ALTER TABLE availability ADD CONSTRAINT availability_capacity_nonneg CHECK (capacity>=0);

-- RANK 4 — date/slot consistency
ALTER TABLE bookings
  ADD CONSTRAINT bookings_stay_pair CHECK ((check_in IS NULL) = (check_out IS NULL)),
  ADD CONSTRAINT bookings_slot_pair CHECK ((slot_start IS NULL) = (slot_end IS NULL)),
  ADD CONSTRAINT bookings_has_a_date CHECK (check_in IS NOT NULL OR slot_start IS NOT NULL),
  ADD CONSTRAINT bookings_stay_order CHECK (check_in IS NULL OR check_out > check_in),
  ADD CONSTRAINT bookings_slot_order CHECK (slot_start IS NULL OR slot_end > slot_start);
ALTER TABLE availability
  ADD CONSTRAINT availability_slot_order CHECK (slot_start IS NULL OR slot_end IS NULL OR slot_end > slot_start);

-- RANK 5/6 — hot-path indexes
CREATE INDEX IF NOT EXISTS idx_payouts_booking ON payouts (booking_id);
CREATE INDEX IF NOT EXISTS idx_bookings_place_status_checkin ON bookings (place_id, status, check_in);

-- RANK 7 — NOT NULL hardening on places (0 nulls verified)
ALTER TABLE places
  ALTER COLUMN status SET NOT NULL,
  ALTER COLUMN verified SET NOT NULL,
  ALTER COLUMN created_at SET NOT NULL,
  ALTER COLUMN updated_at SET NOT NULL;

COMMIT;