TK-10346: migration 008 — DB integrity guards (money invariants total=fee+payout, payout idempotency, nonneg money, date/slot consistency, hot-path indexes, places NOT NULL). All pre-checked 0 violators, reversible.

59cc7f37fad249b1015d408ca03a7aaf0e65e149 · 2026-08-07 16:22:28 -0700 · Steve

Files touched

A migrate_008_integrity_guards.sql

Diff

commit 59cc7f37fad249b1015d408ca03a7aaf0e65e149
Author: Steve <steve@designerwallcoverings.com>
Date:   Fri Aug 7 16:22:28 2026 -0700

    TK-10346: migration 008 — DB integrity guards (money invariants total=fee+payout, payout idempotency, nonneg money, date/slot consistency, hot-path indexes, places NOT NULL). All pre-checked 0 violators, reversible.
---
 migrate_008_integrity_guards.sql | 45 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 45 insertions(+)

diff --git a/migrate_008_integrity_guards.sql b/migrate_008_integrity_guards.sql
new file mode 100644
index 0000000..471e340
--- /dev/null
+++ b/migrate_008_integrity_guards.sql
@@ -0,0 +1,45 @@
+-- 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).
+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;

← 3b42013 yoloforever: cycle 7 ledger — money-path tests, Cody found+f · back to Costa Rica · costa-rica: SAFE/LOCAL security + robustness fixes (C1,C2,R1 8e87509 →