Transactions
The core financial ledger table recording every monetary movement in the system. Every deposit, withdrawal, bet, win, bonus credit, and reward is recorded as a transaction with full balance snapshots.
Relationships
- Belongs to players via
player_id - Belongs to brands via
brand_id - Belongs to
companiesviacompany_id - Optionally references
bonusesviabonus_id - Optionally references
promotionsviapromotion_id - Optionally references
player_bonusesviaplayer_bonus_id - Optionally references
game_sessionsviagame_session_id - Referenced by players via
first_purchase_transaction_id - Referenced by vip_daily_cashback_log via
transaction_id
Columns
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | uuid | NO | gen_random_uuid() | Primary key |
player_id | uuid | NO | FK to players. The player this transaction belongs to. | |
brand_id | uuid | NO | FK to brands. The brand context. | |
company_id | uuid | NO | FK to companies. Parent company. | |
transaction_type | varchar(50) | NO | Direction of the transaction: credit (money in) or debit (money out) | |
transaction_category | varchar(50) | NO | Business category. See values below. | |
currency_type | varchar(50) | NO | Currency of the transaction: gc (Gold Coins), sc (Sweepstakes Coins), real_money, etc. | |
cash_currency | varchar(50) | YES | Fiat currency code when currency_type = 'real_money' (e.g., EUR, USD) | |
balance_type | balance_type_enum | NO | Enum: withdrawable or non-withdrawable. Determines which balance pool is affected. | |
amount | numeric(18) | NO | Transaction amount (always positive; direction determined by transaction_type) | |
status | varchar(20) | YES | 'completed' | Values: completed, pending, cancelled, failed |
payment_method | varchar(50) | YES | Payment method used (for deposits/withdrawals) | |
payment_method_details | jsonb | YES | Detailed payment method info (card last 4, provider response, etc.) | |
external_transaction_id | text | YES | External payment provider transaction reference | |
game_session_id | uuid | YES | FK to game_sessions. Links bet/win transactions to a game round. | |
bonus_id | uuid | YES | FK to bonuses. The bonus template if this transaction is bonus-related. | |
promotion_id | uuid | YES | FK to promotions. The promotion that triggered this transaction. | |
player_bonus_id | uuid | YES | FK to player_bonuses. The specific player bonus instance. | |
eur_amount | numeric(18) | YES | Amount converted to EUR (for unified reporting) | |
eur_conversion_rate | numeric(18) | YES | EUR conversion rate used at time of transaction | |
total_balance_before | numeric(18) | YES | Snapshot: Total balance (withdrawable + non-withdrawable) BEFORE this transaction | |
total_balance_after | numeric(18) | YES | Snapshot: Total balance AFTER this transaction | |
cash_balance_before | numeric(18) | YES | Snapshot: Withdrawable balance BEFORE. Example: Player has $100 withdrawable, makes $20 bet -> cash_balance_before = 100 | |
cash_balance_after | numeric(18) | YES | Snapshot: Withdrawable balance AFTER. Example: Player has $100, makes $20 bet -> cash_balance_after = 80 | |
bonus_balance_before | numeric(18) | YES | Snapshot: Non-withdrawable balance BEFORE. Example: Player has $50 bonus, makes $10 bet -> bonus_balance_before = 50 | |
bonus_balance_after | numeric(18) | YES | Snapshot: Non-withdrawable balance AFTER. Example: Player has $50 bonus, makes $10 bet -> bonus_balance_after = 40 | |
metadata | jsonb | YES | Flexible JSON for additional context (game info, provider data, etc.) | |
dt_finalized | timestamp | YES | When the transaction reached a final state (completed/cancelled). Set by trigger. | |
created_at | timestamp | YES | CURRENT_TIMESTAMP | Transaction creation timestamp |
updated_at | timestamp | YES | CURRENT_TIMESTAMP | Last update timestamp |
Transaction Categories
| Category | Description |
|---|---|
deposit | Player purchasing coins or depositing real money |
withdrawal | Player redeeming SC or withdrawing real money |
bet | Wager placed on a game |
win | Winnings from a game |
bonus | Bonus credit (manual or automatic) |
adjustment | Manual admin adjustment |
reward | System rewards (daily login, VIP cashback, etc.) |
free_spin | Free spin credits/winnings |
Balance Type Enum
| Value | Description |
|---|---|
withdrawable | Real/cash balance that can be withdrawn |
non-withdrawable | Bonus balance that requires wagering before withdrawal |
Triggers
This table has the most triggers in the system, reflecting its central role:
| Trigger | Function | Description |
|---|---|---|
a0_trigger_prevent_negative_balance | prevent_negative_balance | First to fire — prevents transactions that would result in a negative balance |
a_update_bonus_amount_on_free_spin_credit_trigger | update_bonus_amount_on_free_spin_credit | Updates bonus amount on free spin credit transactions |
before_transaction_change | update_player_balances | Updates player_balances when a transaction is inserted/updated |
after_transaction_delete | update_player_balances | Reverses balance changes when a transaction is deleted |
track_first_deposit | track_first_deposit | Sets first_purchase_date and first_purchase_transaction_id on players |
trigger_update_redeemable_sc | update_redeemable_sc | Updates redeemable_sc on players for SC wagering tracking |
trigger_update_vip_points_and_level | update_vip_points_and_level | Updates user_vip_status points and triggers level changes |
trigger_update_withdrawable_balances | update_withdrawable_balances | Maintains withdrawable balance tracking |
trigger_update_player_bonus_wagering_on_insert/update | update_player_bonus_wagering_on_transaction | Tracks wagering progress against bonus requirements |
trigger_update_free_spins_count | update_free_spins_count | Tracks free spin usage |
trigger_insert_withdrawals / trigger_update_withdrawals | insert_or_update_withdrawals | Syncs to a withdrawals tracking table |
trigger_upsert_deposit_on_insert/update | upsert_deposit | Syncs to a deposits tracking table |
trigger_upsert_payout_on_insert/update | upsert_payout | Syncs to a payouts tracking table |
trigger_set_dt_finalized | set_dt_finalized | Sets dt_finalized when status changes to a terminal state |
tr_transactions_game_sessions | trg_upsert_game_session_from_tx | Creates/updates game session records from bet/win transactions |
z_transfer_bonus_on_last_winning_trigger | transfer_bonus_on_last_winning | Last to fire — transfers bonus to withdrawable on final winning round |
Key Indexes
| Index | Columns | Notes |
|---|---|---|
transactions_pkey | id | Primary key |
idx_transactions_user | player_id | Player transaction history |
idx_transactions_player_optimized | player_id, created_at DESC, status | Optimized player history queries |
idx_transactions_created_at | created_at DESC | Time-based reporting |
idx_transactions_category | transaction_category | Category filtering |
idx_transactions_deposit_lookup | player_id, transaction_type, transaction_category, status | Partial: completed deposits |
idx_transactions_player_category | player_id, transaction_category, status | Partial: completed deposits/withdrawals |
idx_transactions_game_session | game_session_id | Game round lookups |
idx_transactions_external_transaction_id | external_transaction_id | Payment provider reconciliation |
idx_transactions_balance_trigger | player_id, currency_type, balance_type, status, transaction_type | Partial: completed — optimizes balance update triggers |
Business Rules
- Trigger names are prefixed with letters (
a0_,a_,z_) to control execution order - The
prevent_negative_balancetrigger fires first to ensure no balance goes below zero - Balance snapshot fields (
*_balance_before/after) create a complete audit trail - The
balance_typeenum separates withdrawable (real money / wagered SC) from non-withdrawable (bonus) funds - VIP points are automatically calculated from completed transactions via the
update_vip_points_and_leveltrigger - EUR conversion fields enable unified cross-currency reporting