# Database Schema — ALGA ARENA Esports Platform

**Date:** 2026-07-20  
**Laravel:** 13.x  
**Source:** `database/migrations/`

## Overview

The MVP backend adds **8 incremental migrations** (2026-07-20) on top of the default Laravel skeleton. All new tables use additive migrations; existing `users` rows are preserved via column extension.

**Migration order:**

1. `0001_01_01_000000_create_users_table.php` — skeleton
2. `0001_01_01_000001_create_cache_table.php`
3. `0001_01_01_000002_create_jobs_table.php`
4. `2026_07_20_000001_extend_users_table.php`
5. `2026_07_20_000002_create_foundation_tables.php`
6. `2026_07_20_000003_create_player_profile_tables.php`
7. `2026_07_20_000004_create_games_catalog_tables.php`
8. `2026_07_20_000005_create_player_game_tables.php`
9. `2026_07_20_000006_create_content_tables.php`
10. `2026_07_20_000007_create_events_tables.php`
11. `2026_07_20_000008_create_notifications_table.php`

---

## Core Auth & Session (Laravel default)

### `users`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `public_id` | uuid, unique | Added by extension migration |
| `name` | string | Synced from `display_name` |
| `username` | string, unique, nullable | |
| `display_name` | string, nullable | |
| `email` | string, unique | |
| `email_verified_at` | timestamp, nullable | MustVerifyEmail enforced |
| `password` | string | Hashed |
| `country_code` | char(2), nullable | |
| `preferred_locale` | string(5), default `en` | |
| `timezone` | string, default `UTC` | |
| `phone_country_code` | string(5), nullable | |
| `phone_number` | string(32), nullable | |
| `phone_e164` | string(20), nullable | |
| `phone_verified_at` | timestamp, nullable | |
| `role` | string, default `user` | Enum: `user`, `admin`, `super_admin` |
| `status` | string, default `active` | Enum: `active`, `suspended` |
| `terms_accepted_at` | timestamp, nullable | Set on registration |
| `remember_token` | string | |
| `timestamps` | | |

**Route key:** `public_id` (UUID)

### `password_reset_tokens`, `sessions`

Standard Laravel 13 schema.

---

## Foundation

### `audit_logs`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `user_id` | FK → users, nullable | nullOnDelete |
| `action` | string | e.g. `event.published`, `payment.confirmed` |
| `subject_type`, `subject_id` | morphs, nullable | Polymorphic subject |
| `old_values`, `new_values` | json, nullable | Sanitized (no passwords/phone) |
| `ip_address` | string(45) | |
| `user_agent` | text | |
| `timestamps` | | |

Index: `(action, created_at)`

### `system_settings`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `key` | string, unique | e.g. `content_auto_approve` |
| `value` | json, nullable | |
| `timestamps` | | |

---

## Player Profiles

### `player_profiles`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `public_id` | uuid, unique | Route key |
| `user_id` | FK → users, unique | cascadeOnDelete |
| `display_name` | string | |
| `bio` | text, nullable | |
| `avatar_path`, `cover_path` | string, nullable | `profiles` disk |
| `country_code` | char(2), nullable | |
| `city` | string, nullable | |
| `timezone` | string, default `UTC` | |
| `birth_date` | date, nullable | |
| `player_level` | string, default `amateur` | Enum |
| `looking_for_team` | boolean, default false | |
| `profile_visibility` | string, default `public` | Enum |
| `profile_completion_percentage` | tinyint, default 0 | |
| `status` | string, default `active` | |
| `timestamps`, `deleted_at` | | Soft deletes |

Index: `(country_code, looking_for_team)`

### `player_social_links`

FK `player_profile_id` → cascade. Columns: `platform`, `url`, `label`, `sort_order`.

### `player_languages`

Unique `(player_profile_id, language_code)`. Columns: `language_code`, `proficiency`.

### `player_preferences`

Unique `(player_profile_id, key)`. Columns: `key`, `value` (json).

---

## Games Catalog

### `games`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `public_id` | uuid, unique | |
| `name`, `slug` | string; slug unique | Route key: `slug` |
| `short_name`, `publisher`, `description` | nullable | |
| `logo_path`, `cover_path` | nullable | `games` disk |
| `game_type` | string, default `both` | Enum |
| `status` | string, default `active` | |
| `sort_order` | unsigned int, default 0 | |
| `supports_ranks/roles/characters/regions/servers` | boolean flags | Per-game feature toggles |
| `timestamps`, `deleted_at` | | |

### Child catalog tables (all FK → `games`, cascadeOnDelete)

| Table | Key columns |
|-------|-------------|
| `game_translations` | `locale`, `name`, `description`; unique `(game_id, locale)` |
| `game_platforms` | `name`, `slug`; unique `(game_id, slug)` |
| `game_regions` | `name`, `code`; unique `(game_id, code)` |
| `game_servers` | `name`, optional `game_region_id` |
| `game_ranks` | `name`, `color`, `sort_order`, `min_value` |
| `game_roles` | `name`, `sort_order` |
| `game_characters` | `name`, `sort_order` |
| `game_modes` | `name`, `sort_order` |

---

## Player Game Profiles

### `player_game_profiles`

| Column | Type | Notes |
|--------|------|-------|
| `id` | bigint PK | |
| `public_id` | uuid, unique | |
| `player_profile_id` | FK | cascade |
| `game_id` | FK | cascade |
| `in_game_name` | string | |
| `external_game_id` | string, nullable | |
| `current_rank_id`, `highest_rank_id` | FK → game_ranks | nullOnDelete |
| `primary_platform_id` | FK → game_platforms | |
| `region_id`, `server_id` | FK | |
| `years_of_experience` | tinyint, nullable | |
| `skill_level`, `biography` | nullable | |
| `is_primary_game`, `is_public` | boolean | |
| `timestamps`, `deleted_at` | | |

Unique: `(player_profile_id, game_id)`

### Pivot tables

- `player_game_roles` — unique `(player_game_profile_id, game_role_id)`; `is_primary`
- `player_game_characters` — unique profile+character; `sort_order`
- `player_game_platforms` — unique profile+platform

---

## Content (Records & Videos)

### `player_records`

| Column | Type | Notes |
|--------|------|-------|
| `public_id` | uuid, unique | |
| `player_profile_id`, `game_id` | FK | |
| `player_game_profile_id` | FK, nullable | |
| `title`, `description` | | |
| `record_type` | string | Enum: rank_achievement, match_result, etc. |
| `achievement_date` | date, nullable | |
| `result_text` | string, nullable | |
| `rank_id` | FK → game_ranks, nullable | |
| `visibility` | string, default `public` | |
| `moderation_status` | string, default `pending` | |
| `moderation_reason` | text, nullable | |
| `published_at` | timestamp, nullable | Set on approval |
| `timestamps`, `deleted_at` | | |

### `player_record_media`

FK `player_record_id` → cascade. Stores screenshot files on `records` disk: `file_path`, `thumbnail_path`, `original_name`, `mime_type`, `size`, `checksum`, `caption`, `sort_order`.

### `player_videos`

External embed metadata (no hosted video files). Columns: `original_url`, `provider`, `external_video_id`, `normalized_url`, `safe_embed_url`, `thumbnail_url`, `video_type`, `visibility`, `moderation_status`, `moderation_reason`, `published_at`.

---

## Events & Payments

### `manual_payment_methods`

Admin-configured offline payment channels: `name`, `type`, `instructions`, `account_name`, `account_identifier`, `logo_path`, `is_active`, `sort_order`.

### `events`

| Column | Type | Notes |
|--------|------|-------|
| `public_id` | uuid, unique | |
| `game_id` | FK | |
| `created_by` | FK → users | |
| `title`, `slug` | slug unique | Route key: `slug` |
| `short_description`, `description`, `cover_image` | | |
| `event_type`, `participation_type`, `location_type` | string enums | |
| `venue_name`, `venue_address`, `online_details` | nullable | |
| `timezone` | default `UTC` | |
| `registration_opens_at`, `registration_closes_at` | timestamp, nullable | |
| `starts_at`, `ends_at` | timestamp | |
| `capacity` | unsigned int, nullable | |
| `waitlist_enabled` | boolean | |
| `approval_mode` | default `automatic` | `automatic` \| `admin_review` |
| `pricing_type` | default `free` | `free` \| `paid` |
| `fee_amount_minor`, `currency` | nullable | Amount in minor units |
| `payment_instructions`, `rules`, `prizes`, `contact_information` | text, nullable | |
| `status` | default `draft` | See EventStatus enum |
| `published_at` | timestamp, nullable | |
| `timestamps`, `deleted_at` | | |

### Related event tables

- `event_translations` — `(event_id, locale)` unique
- `event_rules` — structured rules with `sort_order`
- `event_prizes` — `placement`, `amount`, `description`
- `event_payment_methods` — links event to `manual_payment_methods`; optional `instructions_override`

### `event_registrations`

| Column | Type | Notes |
|--------|------|-------|
| `public_id` | uuid, unique | |
| `event_id`, `user_id`, `player_profile_id`, `player_game_profile_id` | FK | |
| `status` | string | RegistrationStatus enum |
| `payment_status` | string | PaymentStatus enum |
| `contact_name`, `contact_email`, `contact_phone_e164` | | |
| `contact_whatsapp` | boolean | |
| `admin_notes`, `user_notes` | text, nullable | |
| `registered_at`, `confirmed_at`, `cancelled_at` | timestamps | |
| `reviewed_by`, `reviewed_at` | nullable | Admin review |
| `waitlist_position` | unsigned int, nullable | |

### `event_registration_status_events`

Audit trail for registration status transitions: `old_status`, `new_status`, `changed_by`, `reason`.

### `event_payment_proofs`

Private file references on `payment_proofs` disk: `file_path`, `original_name`, `mime_type`, `size`, `transfer_reference`, `amount_minor`, `user_notes`, `review_status`, `reviewed_by`, `reviewed_at`, `rejection_reason`.

---

## Notifications

### `notifications`

Standard Laravel database notifications table: uuid PK, `type`, morphs `notifiable`, `data` (text/json), `read_at`, `timestamps`.

---

## Entity Relationship Summary

```
users 1──1 player_profiles
users 1──* event_registrations
users 1──* events (created_by)

player_profiles 1──* player_game_profiles
player_profiles 1──* player_records
player_profiles 1──* player_videos
player_profiles 1──* player_social_links / languages / preferences

games 1──* game_* (catalog children)
games 1──* player_game_profiles
games 1──* events

player_game_profiles 1──* player_game_roles / characters / platforms

events 1──* event_registrations
events 1──* event_rules / prizes / payment_methods / translations

event_registrations 1──* event_payment_proofs
event_registrations 1──* event_registration_status_events
```

---

## Seeders

| Seeder | Purpose |
|--------|---------|
| `AdminUserSeeder` | Admin/super_admin accounts |
| `GameSeeder` | 7 real games (Valorant, LoL, EA FC, Tekken, PUBG Mobile, CS2, Mobile Legends) |
| `ManualPaymentMethodSeeder` | Sample offline payment methods |
| `EventSeeder` | Demo events |

`DatabaseSeeder` skips seeding in production unless `SEED_DEMO=true`.