User VIP Status Log
Unified audit log recording every VIP level change — both tier-ups (promotions) and tier-downs (demotions). Provides a complete history of a player's VIP journey.
Table Comment
Unified audit log for all VIP level changes (tier-ups and tier-downs)
Relationships
- Belongs to players via
player_id - Belongs to brands via
brand_id - Belongs to
companiesviacompany_id - References vip_levels via
previous_level_idandnew_level_id
Columns
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | uuid | NO | gen_random_uuid() | Primary key |
player_id | uuid | NO | FK to players. The player whose level changed. | |
brand_id | uuid | NO | FK to brands. Brand context. | |
company_id | uuid | NO | FK to companies. Parent company. | |
change_type | varchar(20) | NO | Type of change: tier_up (promoted) or tier_down (demoted) | |
previous_level_id | integer | YES | FK to vip_levels. NULL for first-time VIP assignment. | |
previous_level_rank | integer | YES | Snapshot of previous level's rank at time of change | |
previous_level_name | varchar(50) | YES | Snapshot of previous level's name at time of change | |
new_level_id | integer | NO | FK to vip_levels. The new VIP level. | |
new_level_rank | integer | NO | Snapshot of new level's rank at time of change | |
new_level_name | varchar(50) | NO | Snapshot of new level's name at time of change | |
vip_points | numeric(18) | YES | Player's VIP points at time of change | |
days_at_previous_level | integer | YES | Number of days the player spent at their previous level | |
change_reason | varchar(100) | YES | Human-readable reason (e.g., "Reached 5000 VIP points", "60-day inactivity demotion") | |
metadata | jsonb | YES | '{}' | Additional context data |
created_at | timestamptz | YES | CURRENT_TIMESTAMP | When the change occurred |
Key Indexes
| Index | Columns | Notes |
|---|---|---|
user_vip_status_log_pkey | id | Primary key |
idx_user_vip_status_log_player | player_id | Player's VIP history |
idx_user_vip_status_log_player_created | player_id, created_at DESC | Player history ordered by time |
idx_user_vip_status_log_brand | brand_id | Brand-level reporting |
idx_user_vip_status_log_change_type | change_type | Filter tier-ups vs tier-downs |
idx_user_vip_status_log_created | created_at | Time-based reporting |
Business Rules
- Level name and rank are snapshotted at the time of change — this ensures historical accuracy even if VIP levels are later renamed or reordered
previous_level_idis NULL for the initial VIP level assignment (when a new player is first assigned to the lowest VIP tier)- Tier-up entries are created automatically by the
update_vip_points_and_leveltrigger on transactions when points cross a threshold - Tier-down entries are typically created by a scheduled job that evaluates the 60-day inactivity rule from user_vip_status.
current_level_acquired_at - The
days_at_previous_levelfield enables analysis of VIP progression velocity