schema / conventions

Schema conventions — mpcdb (OLTP)

Tables

  • snake_case, plural (courts, time_group_schedules)
  • Exception: staff (collective noun — do not rename to staffs)
  • Coach profiles: staff_coaches (extension of staff)

Columns

  • Always snake_case in Postgres (never camelCase)
  • Prisma: PascalCase model, camelCase fields, @map("snake_case"), @@map("table")
  • GORM: explicit gorm:"column:snake_case"
  • All id / *_id / created_by / updated_by / deleted_by are uuid
  • Polymorphic refs (favorites.target_id, stock_ledger.reference_id, transaction_items.reference_id) remain uuid without FK
  • bookings.session_id is a uuid grouping key for multi-slot bookings (not an FK to another table)

Product catalog (3 levels)

  1. products.product_type — FnB | ProShop | Rent (business routing; required)
  2. products.group_id → item_groups — e.g. Makanan, Minuman, Rental Gear
  3. products.category_id → item_categories — e.g. Coffee, Non-coffee (child of group)

item_groups.product_type binds groups to FnB/ProShop/Rent. Courts use item_categories VIP/Regular (often without group).

Do not rename item_groups / item_categories / item_price_overrides to product_* — court taxonomy still shares item_categories.

Do not create a group named Rent under product_type=ProShop. Rental gear groups must use product_type=Rent (e.g. GRP-RENT-GEAR).

store_settings vs branches

  • store_settings — club-wide identity + reschedule policy + FnB pickup (fnb_pickup_before_minutes, fnb_pickup_interval_minutes); default_branch_id → primary venue
  • branches — venue ops (hours, timezone, courts)
  • courts konten showcase: description text, gallery/specs/perks jsonb (gallery = array URL; specs = array {label,value}; perks = array string) — dikelola admin, tampil di Court Detail member apps
  • transactions.fnb_pickup_time — waktu ambil FnB (nullable; wajib saat checkout ada booking + produk FnB)

Pricing resolve order

  1. SpecialDay override
  2. TimeGroup (weekday + clock window)
  3. Weekday / Weekend override
  4. Base (courts.base_price_per_hour / products.price)

Discounts

  • promos — marketing/display only (channel: Online|Outlet|Both); never redeem
  • vouchers — redeem codes (+ optional channel / payment_methods / auto_apply); log in voucher_redemptions
  • tier_benefits — membership auto discount; stack = max(voucher, tier) then recompute tax

Audit fields (business tables)

  • id UUID PK
  • code unique nullable
  • created_at, updated_at
  • deleted_at, deleted_by (soft delete)
  • created_by, updated_by (optional UUID)

Ledgers

  • payment_events — gateway/webhook trail per transaction
  • stock_ledger — stock deltas; products.stock_quantity is cached balance
  • bookings.transaction_id — primary payment link

HR

  • staff — people master (login/roles)
  • staff_outlet_assignments — outlet access for POS
  • staff_coaches — public coach profile (staff_id required for active coaches)
  • Member API path may remain /coaches

Schema changes

  • Prefer documented SQL in mpc-dev/docs/schema/migration/ + Prisma update
  • Do not rely on GORM AUTO_MIGRATE for production schema evolution

Next (deferred)

  • booking_participants, court closures, product variants/addons investment

ksrdb (datamart)

  • Separate DB; dim_* / fact_* / sync_watermark / users (login mpc-owner; ex owner_users)
  • Naming intentionally different from OLTP