Player Balances

Stores the current balance state for each player, segmented by currency type and balance type. Uses a composite primary key — each player has multiple rows representing different balance pools.

Relationships

• Belongs to players via player_id
• Updated by transactions triggers

Columns

Column	Type	Nullable	Default	Description
player_id	uuid	NO		FK to players. Part of composite PK.
currency_type	varchar(50)	NO		Currency identifier: gc (Gold Coins), sc (Sweepstakes Coins), real_money, etc. Part of composite PK.
balance_type	balance_type_enum	NO		Enum: withdrawable or non-withdrawable. Part of composite PK.
balance	numeric(18)	NO		Current balance amount
metadata	jsonb	YES		JSON metadata containing currency conversion info: display_currency, display_amount, original_conversion_rate, etc.
updated_at	timestamptz	YES	now()	Last update timestamp (managed by trigger)

Primary Key

The table uses a composite primary key: (player_id, currency_type, balance_type)

This means a single player may have multiple rows, for example:

player_id	currency_type	balance_type	balance
abc-123	gc	withdrawable	50000
abc-123	gc	non-withdrawable	10000
abc-123	sc	withdrawable	25.50
abc-123	sc	non-withdrawable	5.00

Balance Type Enum

Value	Description
withdrawable	Cash/real balance. Can be withdrawn or redeemed.
non-withdrawable	Bonus balance. Must meet wagering requirements before becoming withdrawable.

Key Indexes

Index	Columns	Notes
player_balances_pkey	player_id, currency_type, balance_type	Composite primary key
idx_player_balances_player_id	player_id	All balances for a player
idx_player_balances_composite	player_id, currency_type, balance_type	Optimized lookup
idx_player_balances_lookup	player_id, balance_type, currency_type	Alternative ordering for different query patterns
idx_player_balances_player_lookup	player_id	Covering index including balance, balance_type, currency_type
idx_player_balances_balance_type	balance_type	Filter by balance type
idx_player_balances_currency_type	currency_type	Filter by currency
idx_player_balances_metadata	metadata	GIN index for JSONB queries
idx_player_balances_trigger_update	player_id, currency_type, balance_type	Optimizes trigger lookups from transactions

Triggers

Trigger	Function	Description
set_player_balance_updated_at_trigger	set_player_balance_updated_at	Auto-updates updated_at on balance change
trigger_cap_redeemable_sc	cap_redeemable_sc_on_balance_change	Ensures redeemable_sc on players never exceeds the actual SC withdrawable balance

Business Rules

• Balances are never updated directly — they are maintained exclusively by triggers on the transactions table
• The withdrawable vs non-withdrawable distinction is critical for regulatory compliance:
  • Withdrawable (cash): Can be redeemed/withdrawn at any time
  • Non-withdrawable (bonus): Requires wagering playthrough before conversion to withdrawable
• The metadata field stores currency display information for multi-currency support
• The cap_redeemable_sc trigger ensures the redeemable_sc field on the players table stays consistent with the actual SC withdrawable balance
• For sweepstakes brands, the typical balance rows are: gc/withdrawable, sc/withdrawable, sc/non-withdrawable