​

Database Schema Overview

​

Schema Architecture

​

Key Design Principles

​

Multi-Tenant Architecture

• Operator Isolation: All data scoped to operators with foreign keys
• Unique Constraints: External IDs unique per operator, not globally
• Role-Based Access: Fine-grained permissions via operator roles
• Independent Configuration: Separate settings per operator

​

Flexible Tournament Support

• Tournament Types: Points-based and elimination tournaments
• Status Tracking: Comprehensive status management for all entities
• Time Management: Scheduled start times with registration windows
• Multi-Entry System: Player rebuys and entry count tracking

​

Performance & Scalability

• Strategic Indexes: Optimized for common query patterns
• JSON Storage: Flexible configuration and result storage
• Slug-based PKs: Status lookups use meaningful slugs
• Cascade Handling: Explicit deletion order management

​

Core Table Groups

​

1. Operator Management Tables

• Operator Statuses: Active, inactive, suspended states
• API Key Management: Expiration tracking and usage monitoring
• Webhook Configuration: Per-operator webhook URLs and secrets
• Role-Based Permissions: Granular access control

​

2. Tournament Core Tables

• Flexible Configuration: JSON config field for tournament-specific settings
• Time Management: Separate scheduled start and registration windows
• Prize Pool Structure: JSON field supporting multiple prize distribution models
• External ID Mapping: Links to Casino System tournament identifiers

​

3. Player Management Tables

• Multi-Entry Support: Track multiple entries per player via rebuys
• Session Management: Internal player IDs for tournament sessions
• Progress Tracking: Points accumulation and position tracking
• Payment Tracking: Total amount paid including rebuys

​

Data Types & Constraints

​

Primary Keys

• Auto-incrementing IDs: Standard bigint primary keys for main entities
• Slug-based PKs: String primary keys for status and type lookup tables
• Composite Unique Keys: Operator+external_id combinations

​

Foreign Key Constraints

-- Operator scoping
FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE

-- Status relationships  
FOREIGN KEY (status) REFERENCES tournament_statuses(id) ON UPDATE CASCADE

-- Tournament relationships
FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE

​

JSON Configuration Fields

{
  "gameRules": {
    "maxRounds": 10,
    "roundDuration": 300,
    "eliminationRate": 0.5
  },
  "ui": {
    "theme": "dark",
    "showTimer": true,
    "showLeaderboard": true
  },
  "notifications": {
    "roundStart": true,
    "playerEliminated": true,
    "tournamentComplete": true
  }
}

{
  "type": "percentage",
  "distribution": [50, 30, 20],
  "guaranteedPrize": 1000.00,
  "maxPrizePool": 10000.00,
  "bonuses": {
    "firstPlace": 100.00,
    "eliminationBonus": 10.00
  }
}

{
  "webhooks": {
    "url": "https://casino.com/webhooks/tournaments",
    "secret": "webhook_secret_key",
    "events": ["tournament.started", "tournament.completed"]
  },
  "features": {
    "rebuyEnabled": true,
    "maxRebuysPerPlayer": 3,
    "allowLateRegistration": false
  },
  "branding": {
    "primaryColor": "#8B5CF6",
    "logoUrl": "https://casino.com/logo.png"
  }
}

​

Indexing Strategy

​

Performance-Critical Indexes

-- Tournament queries by operator and status
CREATE INDEX idx_tournaments_operator_status ON tournaments(operator_id, status);

-- Player lookups by operator and external_id  
CREATE UNIQUE INDEX idx_players_operator_external ON players(operator_id, external_id);

-- Tournament player queries
CREATE INDEX idx_tournament_players_tournament_status ON tournament_players(tournament_id, status);

-- API key lookups
CREATE UNIQUE INDEX idx_api_keys_key_secret ON operator_api_keys(key_secret) WHERE is_active = true;

-- Event queries by tournament and type
CREATE INDEX idx_tournament_events_tournament_type ON tournament_events(tournament_id, event_type);

​

Query Optimization Examples

-- Optimized query for operator's active tournaments
SELECT t.id, t.name, t.status, t.current_players
FROM tournaments t
WHERE t.operator_id = ? 
  AND t.status IN ('open', 'in_progress')
ORDER BY t.scheduled_start DESC;

-- Player's tournament history with results
SELECT t.name, tp.overall_position, tp.points, t.completed_at
FROM tournament_players tp
JOIN tournaments t ON tp.tournament_id = t.id
JOIN players p ON tp.player_id = p.id
WHERE p.operator_id = ? 
  AND p.external_id = ?
  AND t.status = 'completed'
ORDER BY t.completed_at DESC;

​

Data Integrity & Validation

​

Referential Integrity

• Cascade Deletes: Operator deletion cascades to all related data
• Restrict Deletes: Prevent deletion of referenced status values
• Null Constraints: Required fields enforced at database level

​

Business Logic Constraints

-- Ensure tournament dates are logical
ALTER TABLE tournaments ADD CONSTRAINT check_tournament_dates 
CHECK (scheduled_start >= registration_opens);

-- Ensure player limits are positive
ALTER TABLE tournaments ADD CONSTRAINT check_player_limits
CHECK (min_players > 0 AND max_players >= min_players);

-- Ensure entry fees are non-negative
ALTER TABLE tournaments ADD CONSTRAINT check_entry_fee
CHECK (entry_fee >= 0);

​

Data Validation Rules

​

Migration Strategy

​

Schema Evolution

• Versioned Migrations: All schema changes tracked in Laravel migrations
• Backward Compatibility: New columns added as nullable with defaults
• Data Transformation: Complex schema changes handled with data migrations
• Rollback Support: All migrations include down() methods for rollbacks

​

Example Migration

// Migration for adding tournament templates
Schema::create('tournament_templates', function (Blueprint $table) {
    $table->id();
    $table->foreignId('operator_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->json('template_config');
    $table->boolean('is_active')->default(true);
    $table->timestamps();
    
    $table->index(['operator_id', 'is_active']);
});

​

Monitoring & Analytics

​

Database Metrics

• Query Performance: Slow query logging and analysis
• Index Usage: Monitor index effectiveness and optimization
• Connection Pooling: Optimize database connection management
• Storage Growth: Track table growth and partition strategies

​

Business Metrics

• Tournament Activity: Tournaments created, completed, cancelled per operator
• Player Engagement: Registration rates, rebuy patterns, retention
• Revenue Tracking: Entry fees, prize pool distributions, operator commissions
• System Health: Error rates, response times, availability

​

Next Steps