016 - Anti double-booking + index hygiene + ksrdb users

Tanggal: 2026-08-11
DB: mpcdb + ksrdb
Ringkas: Exclusion constraint anti double-booking (btree_gist); CHECK stok >= 0; index bookings(court_id, start_time); drop 2 index redundant; add FK indexes yang tersisa; rename ksrdb.owner_users → users.

ALTER mpcdb (jalankan di DB server)

-- =========================================================
-- 1) Anti double-booking: DB-level exclusion constraint
-- =========================================================
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap;
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (
    court_id WITH =,
    tsrange(start_time, end_time) WITH &&
  )
  WHERE (status NOT IN ('Cancelled', 'Canceled') AND deleted_at IS NULL);

-- =========================================================
-- 2) Stock can never go negative (backstop for atomic decrement)
-- =========================================================
ALTER TABLE products DROP CONSTRAINT IF EXISTS products_stock_nonneg;
ALTER TABLE products ADD CONSTRAINT products_stock_nonneg CHECK (stock_quantity >= 0);

-- =========================================================
-- 3) Availability / conflict-check composite index
-- =========================================================
CREATE INDEX IF NOT EXISTS idx_bookings_court_time ON bookings(court_id, start_time);

-- =========================================================
-- 4) Drop redundant indexes
-- =========================================================
-- uni_coaches_user_id adalah unique CONSTRAINT (bukan index lepas)
ALTER TABLE staff_coaches DROP CONSTRAINT IF EXISTS uni_coaches_user_id; -- covered by partial unique idx_staff_coaches_staff_id
DROP INDEX IF EXISTS idx_favorites_customer_id;    -- covered by idx_customer_target(customer_id, ...)

-- =========================================================
-- 5) Remaining FK indexes
-- =========================================================
CREATE INDEX IF NOT EXISTS idx_notifications_customer_id ON notifications(customer_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id ON subscriptions(customer_id);
CREATE INDEX IF NOT EXISTS idx_payment_events_transaction_id ON payment_events(transaction_id);
CREATE INDEX IF NOT EXISTS idx_stock_ledger_product_id ON stock_ledger(product_id);
CREATE INDEX IF NOT EXISTS idx_voucher_redemptions_transaction_id ON voucher_redemptions(transaction_id);
CREATE INDEX IF NOT EXISTS idx_voucher_redemptions_voucher_id ON voucher_redemptions(voucher_id);

ANALYZE bookings;
ANALYZE products;

ALTER ksrdb (jalankan di DB server)

ALTER TABLE owner_users RENAME TO users;
ALTER INDEX IF EXISTS owner_users_email_key RENAME TO users_email_key;
ALTER INDEX IF EXISTS owner_users_pkey RENAME TO users_pkey;

Rollback

-- mpcdb
ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap;
ALTER TABLE products DROP CONSTRAINT IF EXISTS products_stock_nonneg;
DROP INDEX IF EXISTS idx_bookings_court_time;
-- ksrdb
ALTER TABLE users RENAME TO owner_users;
ALTER INDEX IF EXISTS users_email_key RENAME TO owner_users_email_key;
ALTER INDEX IF EXISTS users_pkey RENAME TO owner_users_pkey;

Catatan

Constraint bookings_no_overlap membuat DB menolak dua booking overlap di court sama, apapun kondisi race di aplikasi. Booking Cancelled/soft-deleted tidak memblok slot.
mpc-be menangkap error constraint ini → response "slot taken" (lihat be-hardening di spec 020).
products_stock_nonneg = jaring pengaman; decrement stok di kode tetap pakai conditional update.
Rename owner_users → users: Prisma mpc-owner & ksr-sync ganti @@map saja; model tetap OwnerUser.