Coin Package Purchases
Records every purchase of a coin package by a player. Stores snapshots of package details at the time of purchase for complete audit trail and financial reporting.
Table Comment
This table captures the full state of a package at purchase time, ensuring accurate historical records even if the package is later modified or deactivated.
Relationships
- Belongs to players via
player_id - Belongs to coin_packages via
package_id
Columns
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | uuid | NO | gen_random_uuid() | Primary key |
player_id | uuid | NO | FK to players. The purchasing player. | |
package_id | uuid | NO | FK to coin_packages. The purchased package. | |
payment_method | varchar(50) | YES | Payment method used (e.g., credit_card, paypal, crypto) | |
payment_reference | varchar(255) | YES | External payment provider reference/transaction ID | |
status | varchar(20) | NO | 'completed' | Purchase status. Values: completed, pending, failed, refunded |
package_name | varchar(100) | YES | Snapshot: Package name at time of purchase | |
price_paid | numeric(18) | YES | Snapshot: Actual price paid (for financial reporting) | |
price_currency | varchar(10) | YES | Snapshot: Currency used at time of purchase | |
gc_granted | numeric(18) | YES | Snapshot: GC amount granted (may include VIP store multiplier bonus) | |
sc_granted | numeric(18) | YES | Snapshot: SC amount granted | |
package_snapshot | jsonb | YES | Snapshot: Full package details at time of purchase (complete audit trail) | |
package_source | varchar(20) | YES | Snapshot: Source of the package (standard, smartico, etc.) for analytics | |
created_at | timestamptz | NO | CURRENT_TIMESTAMP | Purchase timestamp |
Key Indexes
| Index | Columns | Notes |
|---|---|---|
coin_package_purchases_pkey | id | Primary key |
idx_coin_package_purchases_player_id | player_id | Player purchase history |
idx_coin_package_purchases_package_id | package_id | Package popularity tracking |
idx_coin_package_purchases_created_at | created_at | Time-based reporting |
idx_coin_package_purchases_created_at_status | created_at, status | Revenue reporting |
idx_coin_package_purchases_status | status | Filter by status |
idx_coin_package_purchases_price_currency | price_currency | Currency-based reporting |
Business Rules
- Snapshot fields (
package_name,price_paid,gc_granted,sc_granted,package_snapshot) preserve the exact state at purchase time - The
gc_grantedandsc_grantedmay differ from the package's base amounts if a VIPstore_multiplierfrom vip_levels was applied - A completed purchase triggers corresponding transactions to credit the player's balances
- The
package_sourcefield enables analytics on which package sources (standard vs. Smartico vs. affiliate) drive the most revenue