VIP Daily Cashback Log
Tracks daily cashback calculations and credits for VIP players. Each row represents one player's cashback for one day, calculated as a percentage of their net gaming loss (NGR).
Table Comment
Tracks daily cashback calculations and credits for VIP players
Relationships
- Belongs to players via
player_id - Belongs to brands via
brand_id - Belongs to
companiesviacompany_id - References vip_levels via
vip_level_id - References transactions via
transaction_id(the credit transaction when cashback is paid out)
Columns
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | uuid | NO | gen_random_uuid() | Primary key |
player_id | uuid | NO | FK to players. The player receiving cashback. | |
brand_id | uuid | NO | FK to brands. Brand context. | |
company_id | uuid | NO | FK to companies. Parent company. | |
calculation_date | date | NO | The date the cashback is calculated for. Unique per player per day. | |
vip_level_id | integer | YES | FK to vip_levels. The player's VIP level used for this calculation. | |
vip_level_rank | integer | NO | Snapshot of the VIP level rank at calculation time | |
cashback_percentage | numeric(5) | NO | Cashback percentage applied (from vip_levels.cashback_percentage) | |
total_bets | numeric(18) | YES | 0 | Total bets placed by the player on calculation_date |
total_wins | numeric(18) | YES | 0 | Total wins by the player on calculation_date |
net_loss | numeric(18) | YES | 0 | Net loss (bets - wins). Cashback only applies when net_loss > 0. |
cashback_amount | numeric(18) | YES | 0 | Calculated cashback amount: net_loss * cashback_percentage / 100, capped by cashback_max_amount from vip_levels |
transaction_id | uuid | YES | FK to transactions. The credit transaction created when cashback is paid out. NULL until credited. | |
status | varchar(20) | YES | 'calculated' | Workflow status. See values below. |
created_at | timestamp | YES | CURRENT_TIMESTAMP | Record creation timestamp |
Status Values
| Status | Description |
|---|---|
calculated | Cashback has been calculated but not yet credited to the player |
credited | Cashback has been paid out — transaction_id is set |
skipped | Cashback was skipped (e.g., player had no net loss, or was below minimum threshold) |
Key Indexes
| Index | Columns | Notes |
|---|---|---|
vip_daily_cashback_log_pkey | id | Primary key |
vip_daily_cashback_unique | player_id, calculation_date | Unique constraint — one calculation per player per day |
idx_vip_daily_cashback_player_date | player_id, calculation_date | Player cashback history |
idx_vip_daily_cashback_status | status, calculation_date | Process pending calculations |
Business Rules
- Cashback is calculated daily as:
cashback_amount = net_loss * cashback_percentage / 100 - The
cashback_max_amountfrom vip_levels caps the maximum cashback per day - Only net losses trigger cashback — if the player is in profit for the day, no cashback is generated (status =
skipped) - The unique constraint on
(player_id, calculation_date)prevents duplicate calculations - When cashback is credited, a corresponding transaction is created and
transaction_idis set - Cashback percentages are determined by the player's VIP level at calculation time (see vip_levels.
cashback_percentage) - The
vip_level_rankis snapshotted for historical accuracy - A player_sweepstakes_rewards entry with
reward_type = 'vip_cashback'is also created when cashback is credited