schema / migration / 015-uuid-fk-indexes-drop-dead-enums

015 - UUID id hygiene, FK indexes, drop dead enums

  • Tanggal: 2026-08-11
  • DB: mpcdb
  • Ringkas: Samakan semua kolom *_id / deleted_by ke uuid; tambah FK yang hilang + index FK; ganti DEFAULT enum-cast di kolom text; DROP enum type mati; ANALYZE.

ALTER (jalankan di DB server)

-- =========================================================
-- 1) Null empty strings before uuid cast
-- =========================================================
UPDATE classes SET coach_id = NULL WHERE coach_id = '';
UPDATE classes SET court_id = NULL WHERE court_id = '';
UPDATE favorites SET customer_id = NULLIF(customer_id, '');
UPDATE favorites SET target_id = NULLIF(target_id, '');
UPDATE notifications SET customer_id = NULLIF(customer_id, '');
UPDATE subscriptions SET customer_id = NULLIF(customer_id, '');
UPDATE subscriptions SET membership_tier_id = NULLIF(membership_tier_id, '');
UPDATE stock_ledger SET reference_id = NULL WHERE reference_id = '';
-- Legacy POS wrote court refs as "court-<uuid>"; normalize before uuid cast
UPDATE transaction_items
SET reference_id = regexp_replace(reference_id, '^(court|product|booking)-', '', 'i')
WHERE reference_id ~* '^(court|product|booking)-';
UPDATE transaction_items SET reference_id = NULL WHERE reference_id = '';
UPDATE item_price_overrides SET deleted_by = NULL WHERE deleted_by = '';
UPDATE special_days SET deleted_by = NULL WHERE deleted_by = '';
UPDATE staff SET deleted_by = NULL WHERE deleted_by = '';
UPDATE store_settings SET deleted_by = NULL WHERE deleted_by = '';
UPDATE time_groups SET deleted_by = NULL WHERE deleted_by = '';
UPDATE time_group_schedules SET deleted_by = NULL WHERE deleted_by = '';
UPDATE transaction_items SET deleted_by = NULL WHERE deleted_by = '';

-- =========================================================
-- 2) text → uuid (relational + polymorphic + deleted_by)
-- =========================================================
ALTER TABLE classes
  ALTER COLUMN coach_id TYPE uuid USING NULLIF(coach_id, '')::uuid,
  ALTER COLUMN court_id TYPE uuid USING NULLIF(court_id, '')::uuid;

ALTER TABLE favorites
  ALTER COLUMN customer_id TYPE uuid USING NULLIF(customer_id, '')::uuid,
  ALTER COLUMN target_id TYPE uuid USING NULLIF(target_id, '')::uuid;

ALTER TABLE notifications
  ALTER COLUMN customer_id TYPE uuid USING NULLIF(customer_id, '')::uuid;

ALTER TABLE subscriptions
  ALTER COLUMN customer_id TYPE uuid USING NULLIF(customer_id, '')::uuid,
  ALTER COLUMN membership_tier_id TYPE uuid USING NULLIF(membership_tier_id, '')::uuid;

ALTER TABLE stock_ledger
  ALTER COLUMN reference_id TYPE uuid USING NULLIF(reference_id, '')::uuid;

ALTER TABLE transaction_items
  ALTER COLUMN reference_id TYPE uuid USING NULLIF(reference_id, '')::uuid,
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE item_price_overrides
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE special_days
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE staff
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE store_settings
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE time_groups
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

ALTER TABLE time_group_schedules
  ALTER COLUMN deleted_by TYPE uuid USING NULLIF(deleted_by, '')::uuid;

-- =========================================================
-- 3) Missing FK constraints
-- =========================================================
ALTER TABLE classes
  DROP CONSTRAINT IF EXISTS classes_coach_id_fkey,
  DROP CONSTRAINT IF EXISTS classes_court_id_fkey;
ALTER TABLE classes
  ADD CONSTRAINT classes_coach_id_fkey
    FOREIGN KEY (coach_id) REFERENCES staff_coaches(id) ON UPDATE CASCADE ON DELETE SET NULL,
  ADD CONSTRAINT classes_court_id_fkey
    FOREIGN KEY (court_id) REFERENCES courts(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE favorites
  DROP CONSTRAINT IF EXISTS favorites_customer_id_fkey;
ALTER TABLE favorites
  ADD CONSTRAINT favorites_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE notifications
  DROP CONSTRAINT IF EXISTS notifications_customer_id_fkey;
ALTER TABLE notifications
  ADD CONSTRAINT notifications_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE subscriptions
  DROP CONSTRAINT IF EXISTS subscriptions_customer_id_fkey,
  DROP CONSTRAINT IF EXISTS subscriptions_membership_tier_id_fkey;
ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT subscriptions_membership_tier_id_fkey
    FOREIGN KEY (membership_tier_id) REFERENCES membership_tiers(id) ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE benefit_usages
  DROP CONSTRAINT IF EXISTS benefit_usages_transaction_id_fkey;
ALTER TABLE benefit_usages
  ADD CONSTRAINT benefit_usages_transaction_id_fkey
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE tier_benefits
  DROP CONSTRAINT IF EXISTS tier_benefits_target_category_id_fkey;
ALTER TABLE tier_benefits
  ADD CONSTRAINT tier_benefits_target_category_id_fkey
    FOREIGN KEY (target_category_id) REFERENCES item_categories(id) ON UPDATE CASCADE ON DELETE SET NULL;

-- =========================================================
-- 4) Indexes on FK columns (skip if already present)
-- =========================================================
CREATE INDEX IF NOT EXISTS idx_benefit_usages_benefit_id ON benefit_usages(benefit_id);
CREATE INDEX IF NOT EXISTS idx_benefit_usages_customer_id ON benefit_usages(customer_id);
CREATE INDEX IF NOT EXISTS idx_benefit_usages_transaction_id ON benefit_usages(transaction_id);
CREATE INDEX IF NOT EXISTS idx_bookings_court_id ON bookings(court_id);
CREATE INDEX IF NOT EXISTS idx_bookings_customer_id ON bookings(customer_id);
CREATE INDEX IF NOT EXISTS idx_courts_category_id ON courts(category_id);
CREATE INDEX IF NOT EXISTS idx_customer_reviews_booking_id ON customer_reviews(booking_id);
CREATE INDEX IF NOT EXISTS idx_customer_reviews_customer_id ON customer_reviews(customer_id);
CREATE INDEX IF NOT EXISTS idx_customers_membership_tier_id ON customers(membership_tier_id);
CREATE INDEX IF NOT EXISTS idx_item_categories_group_id ON item_categories(group_id);
CREATE INDEX IF NOT EXISTS idx_item_price_overrides_time_group_id ON item_price_overrides(time_group_id);
CREATE INDEX IF NOT EXISTS idx_product_addons_product_id ON product_addons(product_id);
CREATE INDEX IF NOT EXISTS idx_product_variants_product_id ON product_variants(product_id);
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_group_id ON products(group_id);
CREATE INDEX IF NOT EXISTS idx_review_answers_question_id ON review_answers(question_id);
CREATE INDEX IF NOT EXISTS idx_review_answers_review_id ON review_answers(review_id);
CREATE INDEX IF NOT EXISTS idx_store_settings_default_branch_id ON store_settings(default_branch_id);
CREATE INDEX IF NOT EXISTS idx_tier_benefits_tier_id ON tier_benefits(tier_id);
CREATE INDEX IF NOT EXISTS idx_tier_benefits_target_category_id ON tier_benefits(target_category_id);
CREATE INDEX IF NOT EXISTS idx_time_group_schedules_time_group_id ON time_group_schedules(time_group_id);
CREATE INDEX IF NOT EXISTS idx_transaction_items_transaction_id ON transaction_items(transaction_id);
CREATE INDEX IF NOT EXISTS idx_transactions_cashier_id ON transactions(cashier_id);
CREATE INDEX IF NOT EXISTS idx_transactions_customer_id ON transactions(customer_id);
CREATE INDEX IF NOT EXISTS idx_voucher_redemptions_customer_id ON voucher_redemptions(customer_id);
CREATE INDEX IF NOT EXISTS idx_staff_outlet_assignments_outlet_id ON staff_outlet_assignments(outlet_id);
CREATE INDEX IF NOT EXISTS idx_classes_coach_id ON classes(coach_id);
CREATE INDEX IF NOT EXISTS idx_classes_court_id ON classes(court_id);
CREATE INDEX IF NOT EXISTS idx_favorites_customer_id ON favorites(customer_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_membership_tier_id ON subscriptions(membership_tier_id);

-- =========================================================
-- 5) Replace dead-enum DEFAULT casts on text columns
-- =========================================================
ALTER TABLE bookings ALTER COLUMN status SET DEFAULT 'Pending';
ALTER TABLE courts ALTER COLUMN status SET DEFAULT 'Active';
ALTER TABLE events ALTER COLUMN status SET DEFAULT 'Upcoming';
ALTER TABLE transactions ALTER COLUMN status SET DEFAULT 'Pending';

-- =========================================================
-- 6) Drop unused enum types (not attached to any column)
-- =========================================================
DROP TYPE IF EXISTS "BookingStatus";
DROP TYPE IF EXISTS "CourtStatus";
DROP TYPE IF EXISTS "CourtType";
DROP TYPE IF EXISTS "DiscountType";
DROP TYPE IF EXISTS "EventStatus";
DROP TYPE IF EXISTS "PaymentMethod";
DROP TYPE IF EXISTS "TaskPriority";
DROP TYPE IF EXISTS "TaskStatus";
DROP TYPE IF EXISTS "TransactionStatus";
DROP TYPE IF EXISTS "TransactionType";

-- =========================================================
-- 7) Refresh planner stats
-- =========================================================
ANALYZE;

Rollback (opsional)

-- Enums: recreate only if needed by old clients (not recommended).
-- UUID→text / DROP INDEX / DROP CONSTRAINT: restore from backup if required.

Catatan

  • favorites.target_id, stock_ledger.reference_id, transaction_items.reference_id tetap tanpa FK (polymorphic).
  • Legacy POS refs court-<uuid> / product-<uuid> / booking-<uuid> di-strip prefix sebelum cast uuid.
  • bookings.session_id tetap uuid grouping key multi-slot (bukan FK ke tabel lain).
  • Enum yang masih dipakai kolom (BranchStatus, DayType, ItemType, PosDeviceStatus, PosOutletStatus, ProductCategory, SpecialDayType, StaffRole, StaffStatus) tidak di-drop.