> ## Documentation Index
> Fetch the complete documentation index at: https://docs-ts.playservices.tech/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema Overview

> Complete database schema for the Laravel Tournament Platform with multi-tenant architecture

# Database Schema Overview

The Tournament Platform database is designed around **multi-tenant operator isolation** with comprehensive tournament lifecycle management, supporting both scheduled and hop-on/off tournament formats.

## Schema Architecture

The database schema is organized into logical groups that support the complete tournament ecosystem:

<CardGroup cols={2}>
  <Card title="Operator Management" icon="building" href="/database/entity-relationships#operator-management">
    Multi-tenant operator isolation, API keys, roles, and settings management.
  </Card>

  <Card title="Tournament Core" icon="trophy" href="/database/entity-relationships#tournament-core">
    Tournament types, status tracking, and configuration management.
  </Card>

  <Card title="Player Management" icon="users" href="/database/entity-relationships#player-management">
    Player records, registrations, and multi-entry rebuy support.
  </Card>

  <Card title="Tournament Structure" icon="map" href="/database/entity-relationships#tournament-structure">
    Rounds, groups, and elimination tournament support.
  </Card>
</CardGroup>

## 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

```mermaid theme={null}
erDiagram
    operator_statuses {
        string id PK "active, inactive, suspended"
        string name
        string description
        timestamps created_at_updated_at
    }
    
    operators {
        bigint id PK
        string name "Display name"
        string slug UK "URL-friendly identifier"
        string webhook_url
        string webhook_secret
        string status FK "-> operator_statuses.id"
        timestamps created_at_updated_at
    }
    
    operator_api_keys {
        bigint id PK
        bigint operator_id FK "-> operators.id"
        bigint operator_role_id FK "-> operator_roles.id"
        string name
        string key_id "Public identifier"
        string key_secret UK "Hashed secret"
        timestamp expires_at
        boolean is_active
        timestamps created_at_updated_at
    }
    
    operators ||--|| operator_statuses : "has status"
    operator_api_keys }|--|| operators : "belongs to"
```

**Key Features:**

* **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

```mermaid theme={null}
erDiagram
    tournament_statuses {
        string id PK "scheduled, open, in_progress, completed, cancelled"
        string name
        string description
    }
    
    tournament_types {
        string id PK "points_based, elimination"
        string name
        string description
    }
    
    tournaments {
        bigint id PK
        bigint operator_id FK "-> operators.id"
        string external_id "Casino system tournament ID"
        string name
        string tournament_type FK "-> tournament_types.id"
        string status FK "-> tournament_statuses.id"
        json config "Tournament configuration"
        timestamp scheduled_start
        timestamp registration_opens
        timestamp registration_closes
        integer max_players
        integer min_players
        decimal entry_fee
        json prize_pool "Prize pool structure"
        timestamps created_at_updated_at
    }
    
    tournaments }|--|| tournament_statuses : "has status"
    tournaments ||--|| tournament_types : "has type"
```

**Key Features:**

* **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

```mermaid theme={null}
erDiagram
    players {
        bigint id PK
        bigint operator_id FK "-> operators.id"
        string external_id "Casino system player ID"
        string display_name
        string email
        string status FK "-> player_statuses.id"
        timestamps created_at_updated_at
    }
    
    tournament_players {
        bigint id PK
        bigint tournament_id FK "-> tournaments.id"
        bigint player_id FK "-> players.id"
        string internal_player_id UK "Session-specific ID"
        string status FK "-> tournament_player_statuses.id"
        decimal points "Accumulated points"
        integer overall_position "Final ranking"
        integer entry_count "Number of entries/rebuys"
        decimal total_paid "Total amount paid"
        timestamps created_at_updated_at
    }
    
    players }|--|| tournaments : "participates in"
    tournament_players }|--|| players : "is player"
```

**Key Features:**

* **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

```sql theme={null}
-- 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

<Tabs>
  <Tab title="Tournament Config">
    ```json theme={null}
    {
      "gameRules": {
        "maxRounds": 10,
        "roundDuration": 300,
        "eliminationRate": 0.5
      },
      "ui": {
        "theme": "dark",
        "showTimer": true,
        "showLeaderboard": true
      },
      "notifications": {
        "roundStart": true,
        "playerEliminated": true,
        "tournamentComplete": true
      }
    }
    ```
  </Tab>

  <Tab title="Prize Pool Config">
    ```json theme={null}
    {
      "type": "percentage",
      "distribution": [50, 30, 20],
      "guaranteedPrize": 1000.00,
      "maxPrizePool": 10000.00,
      "bonuses": {
        "firstPlace": 100.00,
        "eliminationBonus": 10.00
      }
    }
    ```
  </Tab>

  <Tab title="Operator Settings">
    ```json theme={null}
    {
      "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"
      }
    }
    ```
  </Tab>
</Tabs>

## Indexing Strategy

### Performance-Critical Indexes

```sql theme={null}
-- 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

<Tabs>
  <Tab title="Active Tournaments">
    ```sql theme={null}
    -- 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;
    ```
  </Tab>

  <Tab title="Player Tournament History">
    ```sql theme={null}
    -- 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;
    ```
  </Tab>
</Tabs>

## 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

```sql theme={null}
-- 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

<Warning>
  Database constraints are supplemented by application-level validation for complex business rules like tournament timing, player eligibility, and prize pool calculations.
</Warning>

## 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

```php theme={null}
// 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

<CardGroup cols={2}>
  <Card title="Entity Relationships" icon="diagram" href="/database/entity-relationships">
    Detailed entity relationship diagrams and table relationships.
  </Card>

  <Card title="Performance Optimization" icon="bolt" href="/database/performance-optimization">
    Database tuning, query optimization, and scaling strategies.
  </Card>
</CardGroup>