Database Schema Overview

Complete documentation of the Spinanda PostgreSQL database schema. This section covers all key tables, their relationships, business rules, and the trigger-driven architecture that powers balance management, VIP progression, and reward systems.

Entity Relationship Diagram

┌──────────────┐     ┌──────────────┐     ┌──────────────────┐
│  companies   │────<│    brands     │────<│   vip_levels     │
└──────────────┘     └──────┬───────┘     └────────┬─────────┘
                            │                      │
                     ┌──────┴───────┐              │
                     │   players    │──────────────┤
                     └──┬───┬──┬───┘              │
                        │   │  │                   │
          ┌─────────────┘   │  └──────────┐       │
          │                 │             │       │
   ┌──────┴────────┐  ┌────┴─────┐  ┌────┴──────────────────┐
   │ transactions  │  │ player   │  │ user_vip_status       │
   │               │  │ balances │  │                       │
   └───────────────┘  └──────────┘  └───────────────────────┘
          │                               │
          │                        ┌──────┴──────────────────┐
          │                        │ user_vip_status_log     │
          │                        └─────────────────────────┘
          │
   ┌──────┴────────────────┐
   │ vip_daily_cashback_log│
   └───────────────────────┘

   ┌──────────────────┐     ┌────────────────────────────────┐
   │  coin_packages   │────<│ coin_package_purchases         │
   │                  │────<│ coin_package_player_assignments │
   └──────────────────┘     └────────────────────────────────┘

   ┌──────────────────┐     ┌────────────────────────────────┐
   │daily_login_rewards│───>│ daily_reward_logs              │
   └──────────────────┘     └────────────────────────────────┘

   ┌──────────────────────────┐
   │ player_sweepstakes_rewards│
   └──────────────────────────┘

   ┌──────────────┐     ┌──────────────┐
   │  providers   │·····│    games     │  (logical link via game_provider)
   └──────────────┘     └──────────────┘

   ┌──────────────────┐
   │ player_affiliates │
   └──────────────────┘

Table Index

Core Tables

Table	Description	Doc
players	Central player/user accounts with personal info, verification, and preferences	View
brands	Brand identities under a parent company with configuration and currency model	View
transactions	Financial ledger — every deposit, bet, win, withdrawal, and reward	View
player_balances	Current balance state per player, segmented by currency and balance type	View

Coin Store

Table	Description	Doc
coin_packages	Purchasable coin packages with GC/SC amounts and geo-restrictions	View
coin_package_purchases	Purchase records with full package snapshots for audit trail	View
coin_package_player_assignments	Personalized package assignments via Smartico CRM	View

VIP System

Table	Description	Doc
vip_levels	VIP tier definitions with cashback, multipliers, and perks	View
user_vip_status	Current VIP state per player (points, level, birthday tracking)	View
user_vip_status_log	Audit log of all VIP level changes (tier-ups and tier-downs)	View
vip_daily_cashback_log	Daily cashback calculations and credits based on net gaming loss	View

Rewards & Engagement

Table	Description	Doc
daily_login_rewards	Configuration for consecutive daily login reward schedules	View
daily_reward_logs	Audit log of daily rewards claimed by players	View
player_sweepstakes_rewards	All SC/engagement rewards (registration, daily, VIP, manual)	View

Games & Providers

Table	Description	Doc
games	Game catalog with metadata, geo-restrictions, and provider config	View
providers	Registry of game providers/studios	View

Marketing

Table	Description	Doc
player_affiliates	Affiliate link tracking with two-phase lifecycle	View

Key Enums

Enum	Values	Used In
balance_type_enum	withdrawable, non-withdrawable	transactions, player_balances
bonus_type_enum	MANUAL, AUTOMATIC	bonuses

Trigger Architecture

The database uses an extensive trigger system, primarily on the transactions table, to maintain data consistency. Key trigger chains:

Transaction Insert Flow

1. a0_prevent_negative_balance — Validates sufficient balance (fires first via a0_ prefix)
2. update_player_balances — Updates player_balances
3. update_redeemable_sc — Updates redeemable_sc on players
4. update_vip_points_and_level — Recalculates VIP points in user_vip_status
5. track_first_deposit — Records first purchase on players
6. insert_or_update_withdrawals — Syncs withdrawal records
7. z_transfer_bonus_on_last_winning — Converts bonus to cash (fires last via z_ prefix)

Balance Protection

• The cap_redeemable_sc_on_balance_change trigger on player_balances ensures redeemable_sc never exceeds the actual SC withdrawable balance
• The prevent_negative_balance trigger blocks any transaction that would result in a negative balance

Currency Model

Sweepstakes Brands (brands.is_sweepstakes = true)

• GC (Gold Coins): Purchased for entertainment. Cannot be redeemed.
• SC (Sweepstakes Coins): Given as free promotional bonus with GC purchases. Can be redeemed for prizes after wagering.
• Players "purchase" GC and receive SC for free — this is the sweepstakes legal model.

Regular Brands

• Real Money: Standard fiat currency (EUR, USD, etc.) with deposits and withdrawals.

Data Flow Examples

Player Purchases a Coin Package

1. Player selects a coin_package
2. Payment is processed externally
3. A coin_package_purchase record is created with package snapshot
4. Transactions are created to credit GC and SC (VIP store_multiplier applied)
5. Triggers update player_balances automatically
6. VIP points are recalculated in user_vip_status

Daily Login Reward Claim

1. Player logs in, consecutive_login_days on players is checked
2. Matching daily_login_rewards config is fetched for that day number
3. VIP daily_bonus_multiplier from vip_levels is applied
4. daily_reward_logs entry is created
5. player_sweepstakes_rewards entry is created
6. Transactions credit the player's balances

VIP Level Change

1. A bet transaction is completed
2. update_vip_points_and_level trigger recalculates VIP points
3. If points cross a vip_levels threshold, level is updated in user_vip_status
4. A user_vip_status_log entry records the change
5. A vip_tier_up reward is granted with tier_up_gc_reward