# Database Design Document

# ASETQU V.2 - Digital Asset Ecosystem Platform

---

## 📋 Document Information

| Item                | Detail           |
| ------------------- | ---------------- |
| **Project Name**    | ASETQU V.2       |
| **Document Type**   | Database Design  |
| **Database System** | PostgreSQL 15.x+ |
| **Version**         | 1.0.0            |
| **Created Date**    | January 2026     |
| **Last Updated**    | January 2026     |

---

## 1. Entity Relationship Diagram (ERD)

### 1.1 Complete ERD Overview

```
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                    CORE ENTITIES                                         │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐                    ┌──────────────┐         ┌──────────────┐          │
│  │    users     │──────1:N──────────▶│   wallets    │────N:1──│wallet_platform│          │
│  │              │                    │              │         │              │          │
│  │   (PK: id)   │                    │  (PK: id)    │         │  (PK: id)    │          │
│  └──────┬───────┘                    └──────┬───────┘         └──────────────┘          │
│         │                                   │                                            │
│         │ 1:N                               │ 1:N                                        │
│         ▼                                   ▼                                            │
│  ┌──────────────┐                    ┌──────────────┐                                   │
│  │   sessions   │                    │wallet_balances│                                   │
│  │  (PK: id)    │                    │  (PK: id)    │                                   │
│  └──────────────┘                    └──────────────┘                                   │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                              PERMISSION ENTITIES (SPATIE)                                │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐                     │
│  │    roles     │◀──N:M──▶│role_has_perm │◀──N:M──▶│ permissions  │                     │
│  │  (PK: id)    │         │   (pivot)    │         │  (PK: id)    │                     │
│  └──────┬───────┘         └──────────────┘         └──────┬───────┘                     │
│         │                                                  │                             │
│         │ N:M                                              │ N:M                         │
│         ▼                                                  ▼                             │
│  ┌──────────────┐                                  ┌──────────────┐                     │
│  │model_has_role│                                  │model_has_perm│                     │
│  │   (pivot)    │                                  │   (pivot)    │                     │
│  └──────────────┘                                  └──────────────┘                     │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                   CMS ENTITIES                                           │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐                     │
│  │    pages     │────1:N──│   sections   │         │     faqs     │                     │
│  │  (PK: id)    │         │  (PK: id)    │         │  (PK: id)    │                     │
│  └──────────────┘         └──────────────┘         └──────────────┘                     │
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐                     │
│  │testimonials  │         │   partners   │         │roadmap_phases│────1:N──┐          │
│  │  (PK: id)    │         │  (PK: id)    │         │  (PK: id)    │         │          │
│  └──────────────┘         └──────────────┘         └──────────────┘         │          │
│                                                                              ▼          │
│                                                                      ┌──────────────┐  │
│                                                                      │roadmap_items │  │
│                                                                      │  (PK: id)    │  │
│                                                                      └──────────────┘  │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                         NAVIGATION & FOOTER ENTITIES (Admin CMS)                         │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐                     │
│  │    menus     │────1:N──│ menu_items   │         │social_media_ │                     │
│  │  (PK: id)    │         │  (PK: id)    │◀──self  │    links     │                     │
│  │  header/     │         │  parent_id   │  ref    │  (PK: id)    │                     │
│  │  footer      │         │  (nested)    │         └──────────────┘                     │
│  └──────────────┘         └──────────────┘                                              │
│                                                    ┌──────────────┐                     │
│  Note: Admin can manage:                           │footer_content│                     │
│  • Header mega menu (text, order, links)           │  (PK: id)    │                     │
│  • Footer navigation & text                        │  copyright,  │                     │
│  • Social media links                              │  about, etc  │                     │
│                                                    └──────────────┘                     │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                              NEWS & EVENT ENTITIES                                       │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐                                  ┌──────────────┐                     │
│  │    events    │────1:N──────────────────────────▶│event_regist  │◀──N:1── users      │
│  │  (PK: id)    │                                  │  (PK: id)    │                     │
│  │ UNIQUE:date  │ (Only 1 event per date)         │ +reminder    │ (Email reminders)   │
│  └──────────────┘                                  └──────────────┘                     │
│                                                                                          │
│  ┌──────────────┐                                  ┌──────────────┐                     │
│  │ newsletters  │                                  │newsletter_sub│                     │
│  │  (PK: id)    │                                  │  (PK: id)    │                     │
│  └──────────────┘                                  └──────────────┘                     │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                            ACADEMY ENTITIES (YouTube Only)                               │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌───────────────────┐                                         │
│  │academy_videos│────1:N──│video_watch_history│◀──N:1── users                           │
│  │  (PK: id)    │         │    (PK: id)       │                                         │
│  │ youtube_url  │         │  (track views)    │                                         │
│  └──────────────┘         └───────────────────┘                                         │
│                                                                                          │
│  Note: Academy is YouTube links ONLY. No enrollment system.                              │
│        Only tracks if user has watched a video (view history).                           │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                            GAMIFICATION ENTITIES                                         │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐                                              │
│  │    games     │────1:N──│game_sessions │◀──N:1── users                                │
│  │  (PK: id)    │         │  (PK: id)    │                                              │
│  └──────────────┘         └──────────────┘                                              │
│                                                                                          │
│  users ──1:N──▶ ┌──────────────┐                   ┌──────────────┐                     │
│                 │    coins     │                   │    points    │ ◀──N:1── users      │
│                 │  (PK: id)    │                   │  (PK: id)    │                     │
│                 └──────────────┘                   └──────────────┘                     │
│                                                                                          │
│  users ──1:N──▶ ┌──────────────┐                   ┌──────────────┐                     │
│                 │coin_convers  │                   │daily_rewards │ ◀──N:1── users      │
│                 │  (PK: id)    │                   │  (PK: id)    │                     │
│                 └──────────────┘                   └──────────────┘                     │
│                                                                                          │
│                                                    ┌──────────────┐                     │
│                                                    │ leaderboards │                     │
│                                                    │  (PK: id)    │                     │
│                                                    └──────────────┘                     │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                              SYSTEM ENTITIES                                             │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                          │
│  ┌──────────────┐         ┌──────────────┐         ┌──────────────┐                     │
│  │   settings   │         │    media     │         │activity_logs │ ◀──N:1── users     │
│  │  (PK: id)    │         │  (PK: id)    │         │  (PK: id)    │                     │
│  └──────────────┘         └──────────────┘         └──────────────┘                     │
│                                                                                          │
└─────────────────────────────────────────────────────────────────────────────────────────┘
```

---

## 2. Table Definitions

### 2.1 Core Tables

#### `users` - User Accounts

```sql
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    name            VARCHAR(255) NOT NULL,
    email           VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password        VARCHAR(255) NOT NULL,
    phone           VARCHAR(20) NULL,
    avatar          VARCHAR(255) NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive', 'banned', 'pending')),
    last_login_at   TIMESTAMP NULL,
    coin_balance    INTEGER NOT NULL DEFAULT 0,
    point_balance   INTEGER NOT NULL DEFAULT 0,
    remember_token  VARCHAR(100) NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      TIMESTAMP NULL
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_deleted_at ON users(deleted_at);
```

| Column            | Type         | Constraints         | Description                  |
| ----------------- | ------------ | ------------------- | ---------------------------- |
| id                | BIGSERIAL    | PK                  | Auto-increment primary key   |
| uuid              | UUID         | UNIQUE, NOT NULL    | Public identifier            |
| name              | VARCHAR(255) | NOT NULL            | User's full name             |
| email             | VARCHAR(255) | UNIQUE, NOT NULL    | Login email                  |
| email_verified_at | TIMESTAMP    | NULL                | Email verification timestamp |
| password          | VARCHAR(255) | NOT NULL            | Hashed password              |
| phone             | VARCHAR(20)  | NULL                | Phone number                 |
| avatar            | VARCHAR(255) | NULL                | Avatar image path            |
| status            | VARCHAR(20)  | NOT NULL            | Account status               |
| last_login_at     | TIMESTAMP    | NULL                | Last login timestamp         |
| coin_balance      | INTEGER      | NOT NULL, DEFAULT 0 | Current coin balance         |
| point_balance     | INTEGER      | NOT NULL, DEFAULT 0 | Current point balance        |
| remember_token    | VARCHAR(100) | NULL                | Remember me token            |
| deleted_at        | TIMESTAMP    | NULL                | Soft delete timestamp        |

---

#### `password_reset_tokens` - Password Reset

```sql
CREATE TABLE password_reset_tokens (
    email       VARCHAR(255) PRIMARY KEY,
    token       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP NULL
);
```

---

#### `sessions` - User Sessions

```sql
CREATE TABLE sessions (
    id              VARCHAR(255) PRIMARY KEY,
    user_id         BIGINT NULL REFERENCES users(id) ON DELETE CASCADE,
    ip_address      VARCHAR(45) NULL,
    user_agent      TEXT NULL,
    payload         TEXT NOT NULL,
    last_activity   INTEGER NOT NULL
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_last_activity ON sessions(last_activity);
```

---

### 2.2 Permission Tables (Spatie)

#### `roles` - User Roles

```sql
CREATE TABLE roles (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    guard_name      VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (name, guard_name)
);
```

#### `permissions` - Permissions List

```sql
CREATE TABLE permissions (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    guard_name      VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (name, guard_name)
);
```

#### `model_has_roles` - User-Role Pivot

```sql
CREATE TABLE model_has_roles (
    role_id         BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    model_type      VARCHAR(255) NOT NULL,
    model_id        BIGINT NOT NULL,
    PRIMARY KEY (role_id, model_id, model_type)
);

CREATE INDEX idx_model_has_roles_model ON model_has_roles(model_id, model_type);
```

#### `model_has_permissions` - User-Permission Pivot

```sql
CREATE TABLE model_has_permissions (
    permission_id   BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    model_type      VARCHAR(255) NOT NULL,
    model_id        BIGINT NOT NULL,
    PRIMARY KEY (permission_id, model_id, model_type)
);

CREATE INDEX idx_model_has_permissions_model ON model_has_permissions(model_id, model_type);
```

#### `role_has_permissions` - Role-Permission Pivot

```sql
CREATE TABLE role_has_permissions (
    permission_id   BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    role_id         BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (permission_id, role_id)
);
```

---

### 2.3 CMS / Page Tables

#### `menus` - Navigation Menus (Header/Footer)

> **Note:** Admin can manage header mega menu and footer navigation through CMS.
> Supports nested menu items (parent-child) for dropdown/mega menu structures.

```sql
CREATE TABLE menus (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    location        VARCHAR(50) NOT NULL CHECK (location IN ('header', 'footer', 'sidebar')),
    description     TEXT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_menus_location ON menus(location);
```

#### `menu_items` - Menu Items

```sql
CREATE TABLE menu_items (
    id              BIGSERIAL PRIMARY KEY,
    menu_id         BIGINT NOT NULL REFERENCES menus(id) ON DELETE CASCADE,
    parent_id       BIGINT NULL REFERENCES menu_items(id) ON DELETE CASCADE,
    title           VARCHAR(255) NOT NULL,
    url             VARCHAR(500) NULL,
    route_name      VARCHAR(255) NULL,
    target          VARCHAR(20) NOT NULL DEFAULT '_self'
                    CHECK (target IN ('_self', '_blank')),
    icon            VARCHAR(100) NULL,
    css_class       VARCHAR(255) NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Note: Admin can reorder menu items, edit text, add/remove items
CREATE INDEX idx_menu_items_menu_id ON menu_items(menu_id);
CREATE INDEX idx_menu_items_parent_id ON menu_items(parent_id);
CREATE INDEX idx_menu_items_sort_order ON menu_items(sort_order);
```

#### `social_media_links` - Social Media Links (Footer)

> **Note:** Admin can manage social media links displayed in footer.

```sql
CREATE TABLE social_media_links (
    id              BIGSERIAL PRIMARY KEY,
    platform        VARCHAR(50) NOT NULL,
    name            VARCHAR(100) NOT NULL,
    url             VARCHAR(500) NOT NULL,
    icon            VARCHAR(100) NULL,
    icon_class      VARCHAR(100) NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Default social platforms
INSERT INTO social_media_links (platform, name, url, icon_class, sort_order) VALUES
('discord', 'Discord', 'https://discord.gg/asetqu', 'fab fa-discord', 1),
('twitter', 'Twitter/X', 'https://twitter.com/asetqu', 'fab fa-x-twitter', 2),
('telegram', 'Telegram', 'https://t.me/asetqu', 'fab fa-telegram', 3),
('instagram', 'Instagram', 'https://instagram.com/asetqu', 'fab fa-instagram', 4),
('youtube', 'YouTube', 'https://youtube.com/@asetqu', 'fab fa-youtube', 5);
```

#### `footer_content` - Footer Text Content

> **Note:** Admin can edit footer text, copyright, and other footer content.

```sql
CREATE TABLE footer_content (
    id              BIGSERIAL PRIMARY KEY,
    section         VARCHAR(50) NOT NULL UNIQUE,
    title           VARCHAR(255) NULL,
    content         TEXT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Default footer sections
INSERT INTO footer_content (section, title, content) VALUES
('about', 'About ASETQU', 'ASETQU V.2 adalah platform ekosistem aset digital yang komprehensif...'),
('copyright', NULL, '© 2026 ASETQU V.2. All Rights Reserved.'),
('contact_email', 'Email', 'support@asetqu.com'),
('contact_address', 'Address', 'Jakarta, Indonesia');
```

#### `pages` - Static Pages

```sql
CREATE TABLE pages (
    id              BIGSERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    slug            VARCHAR(255) NOT NULL UNIQUE,
    content         TEXT NULL,
    meta_title      VARCHAR(255) NULL,
    meta_description TEXT NULL,
    meta_keywords   VARCHAR(255) NULL,
    featured_image  VARCHAR(255) NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'archived')),
    published_at    TIMESTAMP NULL,
    created_by      BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      TIMESTAMP NULL
);

CREATE INDEX idx_pages_slug ON pages(slug);
CREATE INDEX idx_pages_status ON pages(status);
```

#### `sections` - Page Sections

```sql
CREATE TABLE sections (
    id              BIGSERIAL PRIMARY KEY,
    page_id         BIGINT NULL REFERENCES pages(id) ON DELETE CASCADE,
    title           VARCHAR(255) NULL,
    content         TEXT NULL,
    type            VARCHAR(50) NOT NULL DEFAULT 'content',
    settings        JSONB NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sections_page_id ON sections(page_id);
```

#### `faqs` - FAQ Items

```sql
CREATE TABLE faqs (
    id              BIGSERIAL PRIMARY KEY,
    question        VARCHAR(500) NOT NULL,
    answer          TEXT NOT NULL,
    category        VARCHAR(100) NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_faqs_category ON faqs(category);
```

#### `testimonials` - Community Feed (Admin-Managed)

> **Note:** Testimonials/Community Feed content is managed by Admin ONLY.
> Users cannot submit their own testimonials - all content is curated by Admin
> and displayed on the homepage Community Feed section.

```sql
CREATE TABLE testimonials (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    role            VARCHAR(100) NULL,
    avatar          VARCHAR(255) NULL,
    content         TEXT NOT NULL,
    rating          SMALLINT NULL CHECK (rating >= 1 AND rating <= 5),
    is_featured     BOOLEAN NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_by      BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, -- Admin only
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Note: All testimonials are created/managed by Admin through CMS
-- Displayed in Community Feed section on homepage
```

#### `partners` - Partnership Info

```sql
CREATE TABLE partners (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    logo            VARCHAR(255) NULL,
    website         VARCHAR(255) NULL,
    description     TEXT NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

---

### 2.4 Roadmap Tables

#### `roadmap_phases` - Roadmap Phases

```sql
CREATE TABLE roadmap_phases (
    id              BIGSERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT NULL,
    phase_number    INTEGER NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'upcoming'
                    CHECK (status IN ('completed', 'in_progress', 'upcoming')),
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_roadmap_phases_status ON roadmap_phases(status);
```

#### `roadmap_items` - Roadmap Items

```sql
CREATE TABLE roadmap_items (
    id              BIGSERIAL PRIMARY KEY,
    phase_id        BIGINT NOT NULL REFERENCES roadmap_phases(id) ON DELETE CASCADE,
    title           VARCHAR(255) NOT NULL,
    description     TEXT NULL,
    is_completed    BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_roadmap_items_phase_id ON roadmap_items(phase_id);
```

---

### 2.5 News & Events Tables

#### `events` - Event Calendar

```sql
CREATE TABLE events (
    id              BIGSERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    slug            VARCHAR(255) NOT NULL UNIQUE,
    description     TEXT NULL,
    content         TEXT NULL,
    featured_image  VARCHAR(255) NULL,
    event_type      VARCHAR(50) NOT NULL DEFAULT 'online'
                    CHECK (event_type IN ('online', 'offline', 'hybrid')),
    location        VARCHAR(255) NULL,
    platform        VARCHAR(100) NULL,
    platform_link   VARCHAR(500) NULL,
    event_date      DATE NOT NULL UNIQUE,  -- Only 1 event per date allowed
    start_time      TIME NOT NULL,
    end_time        TIME NULL,
    registration_deadline TIMESTAMP NULL,
    max_participants INTEGER NULL,
    reminder_days_before INTEGER NOT NULL DEFAULT 1, -- Days before event to send reminder
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'cancelled', 'completed')),
    created_by      BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, -- Admin only
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      TIMESTAMP NULL
);

-- Note: Only 1 event per date is allowed (UNIQUE constraint on event_date)
-- Events are created by Admin only (created_by is NOT NULL)
CREATE UNIQUE INDEX idx_events_event_date ON events(event_date) WHERE deleted_at IS NULL;
CREATE INDEX idx_events_status ON events(status);
```

#### `event_registrations` - Event Registrations

```sql
CREATE TABLE event_registrations (
    id              BIGSERIAL PRIMARY KEY,
    event_id        BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status          VARCHAR(20) NOT NULL DEFAULT 'registered'
                    CHECK (status IN ('registered', 'attended', 'cancelled', 'no_show')),
    registered_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    attended_at     TIMESTAMP NULL,
    reminder_sent   BOOLEAN NOT NULL DEFAULT FALSE,  -- Pre-event reminder sent
    reminder_sent_at TIMESTAMP NULL,
    day_reminder_sent BOOLEAN NOT NULL DEFAULT FALSE, -- Day-of reminder sent
    day_reminder_sent_at TIMESTAMP NULL,
    notes           TEXT NULL,
    UNIQUE (event_id, user_id)
);

-- Note: System sends 2 types of email reminders:
-- 1. reminder_sent: Sent X days before event (configurable via events.reminder_days_before)
-- 2. day_reminder_sent: Sent on the day of the event
CREATE INDEX idx_event_registrations_event_id ON event_registrations(event_id);
CREATE INDEX idx_event_registrations_user_id ON event_registrations(user_id);
CREATE INDEX idx_event_registrations_reminder ON event_registrations(reminder_sent, day_reminder_sent);
```

#### `newsletters` - Newsletter Issues

```sql
CREATE TABLE newsletters (
    id              BIGSERIAL PRIMARY KEY,
    subject         VARCHAR(255) NOT NULL,
    content         TEXT NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'scheduled', 'sent')),
    scheduled_at    TIMESTAMP NULL,
    sent_at         TIMESTAMP NULL,
    recipient_count INTEGER NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

#### `newsletter_subscribers` - Newsletter Subscribers

```sql
CREATE TABLE newsletter_subscribers (
    id              BIGSERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,
    name            VARCHAR(255) NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'unsubscribed', 'bounced')),
    subscribed_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unsubscribed_at TIMESTAMP NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_newsletter_subscribers_status ON newsletter_subscribers(status);
```

---

### 2.6 Academy Tables

> **Note:** Academy module provides educational video content via YouTube links only.
> There is NO enrollment/course registration system - users simply browse and watch videos.
> The system only tracks which videos users have watched (view history).

#### `academy_videos` - Educational Video Content

```sql
CREATE TABLE academy_videos (
    id              BIGSERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    slug            VARCHAR(255) NOT NULL UNIQUE,
    description     TEXT NULL,
    thumbnail       VARCHAR(255) NULL,
    youtube_url     VARCHAR(500) NOT NULL,  -- YouTube video link
    youtube_video_id VARCHAR(50) NULL,      -- Extracted video ID for embedding
    duration_minutes INTEGER NULL,
    category        VARCHAR(100) NULL,
    level           VARCHAR(20) NOT NULL DEFAULT 'beginner'
                    CHECK (level IN ('beginner', 'intermediate', 'advanced')),
    sort_order      INTEGER NOT NULL DEFAULT 0,
    view_count      INTEGER NOT NULL DEFAULT 0,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'archived')),
    created_by      BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, -- Admin only
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      TIMESTAMP NULL
);

-- Note: Videos are managed by Admin only (no user submission)
CREATE INDEX idx_academy_videos_status ON academy_videos(status);
CREATE INDEX idx_academy_videos_level ON academy_videos(level);
CREATE INDEX idx_academy_videos_category ON academy_videos(category);
```

#### `video_watch_history` - User Video Watch History

```sql
CREATE TABLE video_watch_history (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    video_id        BIGINT NOT NULL REFERENCES academy_videos(id) ON DELETE CASCADE,
    watched_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    watch_count     INTEGER NOT NULL DEFAULT 1,
    last_watched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, video_id)
);

-- Note: This table only stores if/when user has watched a video
-- No progress tracking or completion status (user is redirected to YouTube)
CREATE INDEX idx_video_watch_history_user_id ON video_watch_history(user_id);
CREATE INDEX idx_video_watch_history_video_id ON video_watch_history(video_id);
CREATE INDEX idx_video_watch_history_watched_at ON video_watch_history(last_watched_at);
```

---

### 2.7 Wallet Tables

#### `wallet_platforms` - Supported Platforms

```sql
CREATE TABLE wallet_platforms (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    slug            VARCHAR(100) NOT NULL UNIQUE,
    type            VARCHAR(20) NOT NULL CHECK (type IN ('exchange', 'web3')),
    icon            VARCHAR(255) NULL,
    description     TEXT NULL,
    website         VARCHAR(255) NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Default Data
INSERT INTO wallet_platforms (name, slug, type, is_active) VALUES
('Indodax', 'indodax', 'exchange', TRUE),
('MetaMask', 'metamask', 'web3', TRUE),
('Trust Wallet', 'trust_wallet', 'web3', TRUE),
('Coinbase', 'coinbase', 'exchange', TRUE),
('Phantom', 'phantom', 'web3', TRUE);
```

#### `wallets` - User Wallets

```sql
CREATE TABLE wallets (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform_id     BIGINT NOT NULL REFERENCES wallet_platforms(id) ON DELETE RESTRICT,
    wallet_address  VARCHAR(255) NOT NULL,
    label           VARCHAR(100) NULL,
    is_primary      BOOLEAN NOT NULL DEFAULT FALSE,
    is_verified     BOOLEAN NOT NULL DEFAULT FALSE,
    verified_at     TIMESTAMP NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      TIMESTAMP NULL
);

CREATE INDEX idx_wallets_user_id ON wallets(user_id);
CREATE INDEX idx_wallets_platform_id ON wallets(platform_id);
CREATE UNIQUE INDEX idx_wallets_user_address ON wallets(user_id, wallet_address) WHERE deleted_at IS NULL;
```

#### `wallet_balances` - Cached Balances

```sql
CREATE TABLE wallet_balances (
    id              BIGSERIAL PRIMARY KEY,
    wallet_id       BIGINT NOT NULL REFERENCES wallets(id) ON DELETE CASCADE,
    currency        VARCHAR(20) NOT NULL,
    balance         DECIMAL(24,8) NOT NULL DEFAULT 0,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (wallet_id, currency)
);

CREATE INDEX idx_wallet_balances_wallet_id ON wallet_balances(wallet_id);
```

---

### 2.8 Game & Gamification Tables

#### `games` - Available Games

```sql
CREATE TABLE games (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    slug            VARCHAR(255) NOT NULL UNIQUE,
    description     TEXT NULL,
    instructions    TEXT NULL,
    thumbnail       VARCHAR(255) NULL,
    game_type       VARCHAR(50) NOT NULL DEFAULT 'casual',
    min_score       INTEGER NOT NULL DEFAULT 0,
    max_score       INTEGER NOT NULL DEFAULT 100,
    point_reward     INTEGER NOT NULL DEFAULT 0,
    daily_limit     INTEGER NOT NULL DEFAULT 5,
    cooldown_minutes INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

#### `game_sessions` - Play Sessions

```sql
CREATE TABLE game_sessions (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    game_id         BIGINT NOT NULL REFERENCES games(id) ON DELETE CASCADE,
    score           INTEGER NOT NULL DEFAULT 0,
    points_earned    INTEGER NOT NULL DEFAULT 0,
    started_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at        TIMESTAMP NULL,
    duration_seconds INTEGER NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'playing'
                    CHECK (status IN ('playing', 'completed', 'abandoned')),
    metadata        JSONB NULL
);

CREATE INDEX idx_game_sessions_user_id ON game_sessions(user_id);
CREATE INDEX idx_game_sessions_game_id ON game_sessions(game_id);
CREATE INDEX idx_game_sessions_user_game_date ON game_sessions(user_id, game_id, created_at);
```

---

### 2.9 Coin & Point Tables

#### `coins` - Coin Transactions

```sql
CREATE TABLE coins (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            VARCHAR(20) NOT NULL CHECK (type IN ('earned', 'spent', 'expired', 'adjusted')),
    amount          INTEGER NOT NULL,
    balance_after   INTEGER NOT NULL,
    source          VARCHAR(50) NOT NULL,
    reference_type  VARCHAR(255) NULL,
    reference_id    BIGINT NULL,
    description     TEXT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_coins_user_id ON coins(user_id);
CREATE INDEX idx_coins_type ON coins(type);
CREATE INDEX idx_coins_source ON coins(source);
CREATE INDEX idx_coins_reference ON coins(reference_type, reference_id);
```

#### `points` - Point Transactions

```sql
CREATE TABLE points (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount          INTEGER NOT NULL,
    balance_after   INTEGER NOT NULL,
    source          VARCHAR(50) NOT NULL,
    description     TEXT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_points_user_id ON points(user_id);
CREATE INDEX idx_points_created_at ON points(created_at);
```

#### `coin_conversions` - Conversion History

```sql
CREATE TABLE coin_conversions (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    coins_converted INTEGER NOT NULL,
    points_received INTEGER NOT NULL,
    conversion_rate DECIMAL(10,4) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_coin_conversions_user_id ON coin_conversions(user_id);
```

#### `daily_rewards` - Daily Login Rewards

```sql
CREATE TABLE daily_rewards (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reward_date     DATE NOT NULL,
    points_earned    INTEGER NOT NULL DEFAULT 0,
    streak_day      INTEGER NOT NULL DEFAULT 1,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, reward_date)
);

CREATE INDEX idx_daily_rewards_user_id ON daily_rewards(user_id);
```

---

### 2.10 Leaderboard Tables

#### `leaderboards` - Leaderboard Snapshots

```sql
CREATE TABLE leaderboards (
    id              BIGSERIAL PRIMARY KEY,
    type            VARCHAR(50) NOT NULL CHECK (type IN ('game', 'points', 'coins')),
    category        VARCHAR(100) NOT NULL,
    period_start    DATE NOT NULL,
    period_end      DATE NOT NULL,
    data            JSONB NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (type, category, period_start)
);

CREATE INDEX idx_leaderboards_type ON leaderboards(type);
CREATE INDEX idx_leaderboards_period ON leaderboards(period_start, period_end);
```

---

### 2.11 Settings & Media Tables

#### `settings` - Site Settings

```sql
CREATE TABLE settings (
    id              BIGSERIAL PRIMARY KEY,
    key             VARCHAR(255) NOT NULL UNIQUE,
    value           TEXT NULL,
    type            VARCHAR(20) NOT NULL DEFAULT 'string'
                    CHECK (type IN ('string', 'integer', 'boolean', 'json', 'text')),
    group           VARCHAR(100) NOT NULL DEFAULT 'general',
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_settings_key ON settings(key);
CREATE INDEX idx_settings_group ON settings(group);
```

#### `media` - Media Library

```sql
CREATE TABLE media (
    id              BIGSERIAL PRIMARY KEY,
    filename        VARCHAR(255) NOT NULL,
    original_name   VARCHAR(255) NOT NULL,
    mime_type       VARCHAR(100) NOT NULL,
    size            BIGINT NOT NULL,
    path            VARCHAR(500) NOT NULL,
    disk            VARCHAR(50) NOT NULL DEFAULT 'public',
    alt_text        VARCHAR(255) NULL,
    uploaded_by     BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_media_mime_type ON media(mime_type);
```

---

### 2.12 Activity & Logging Tables

#### `activity_logs` - User Activity

```sql
CREATE TABLE activity_logs (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
    action          VARCHAR(100) NOT NULL,
    description     TEXT NULL,
    subject_type    VARCHAR(255) NULL,
    subject_id      BIGINT NULL,
    properties      JSONB NULL,
    ip_address      VARCHAR(45) NULL,
    user_agent      TEXT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activity_logs_user_id ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_action ON activity_logs(action);
CREATE INDEX idx_activity_logs_subject ON activity_logs(subject_type, subject_id);
CREATE INDEX idx_activity_logs_created_at ON activity_logs(created_at);
```

---

## 3. Indexes Strategy

### 3.1 Primary Indexes

| Table         | Index Name                       | Columns                      | Type      |
| ------------- | -------------------------------- | ---------------------------- | --------- |
| users         | PRIMARY                          | id                           | PK        |
| users         | idx_users_email                  | email                        | UNIQUE    |
| users         | idx_users_uuid                   | uuid                         | UNIQUE    |
| wallets       | idx_wallets_user_id              | user_id                      | INDEX     |
| game_sessions | idx_game_sessions_user_game_date | user_id, game_id, created_at | COMPOSITE |
| coins         | idx_coins_user_id                | user_id                      | INDEX     |
| activity_logs | idx_activity_logs_created_at     | created_at                   | INDEX     |

### 3.2 Composite Indexes

```sql
-- For wallet lookup optimization
CREATE INDEX idx_wallets_user_platform ON wallets(user_id, platform_id) WHERE deleted_at IS NULL;

-- For game session daily limit check
CREATE INDEX idx_game_sessions_daily ON game_sessions(user_id, game_id, DATE(started_at));

-- For leaderboard queries
CREATE INDEX idx_game_sessions_score ON game_sessions(game_id, score DESC) WHERE status = 'completed';

-- For coin balance calculations
CREATE INDEX idx_coins_user_type_date ON coins(user_id, type, created_at);
```

---

## 4. Data Dictionary Summary

### 4.1 Table Count by Module

| Module          | Tables | Description                                            |
| --------------- | ------ | ------------------------------------------------------ |
| **Core**        | 3      | users, password_reset_tokens, sessions                 |
| **Permission**  | 5      | roles, permissions, pivots                             |
| **CMS/Page**    | 7      | pages, sections, faqs, testimonials, partners, roadmap |
| **News**        | 4      | events, event_registrations, newsletters, subscribers  |
| **Academy**     | 3      | courses, lessons, user_course_progress                 |
| **Wallet**      | 3      | wallet_platforms, wallets, wallet_balances             |
| **Game**        | 2      | games, game_sessions                                   |
| **Point**       | 4      | coins, points, coin_conversions, daily_rewards         |
| **Leaderboard** | 1      | leaderboards                                           |
| **Setting**     | 2      | settings, media                                        |
| **Logging**     | 1      | activity_logs                                          |

**Total Tables: 35**

---

## 5. Migration Sequence

```
1. 0001_01_01_000000_create_users_table.php
2. 0001_01_01_000001_create_cache_table.php
3. 0001_01_01_000002_create_jobs_table.php
4. 2026_01_01_000001_create_permission_tables.php
5. 2026_01_01_000002_create_settings_table.php
6. 2026_01_01_000003_create_media_table.php
7. 2026_01_01_000004_create_pages_table.php
8. 2026_01_01_000005_create_sections_table.php
9. 2026_01_01_000006_create_faqs_table.php
10. 2026_01_01_000007_create_testimonials_table.php
11. 2026_01_01_000008_create_partners_table.php
12. 2026_01_01_000009_create_roadmap_tables.php
13. 2026_01_01_000010_create_events_table.php
14. 2026_01_01_000011_create_event_registrations_table.php
15. 2026_01_01_000012_create_newsletters_table.php
16. 2026_01_01_000013_create_newsletter_subscribers_table.php
17. 2026_01_01_000014_create_courses_table.php
18. 2026_01_01_000015_create_lessons_table.php
19. 2026_01_01_000016_create_user_course_progress_table.php
20. 2026_01_01_000017_create_wallet_platforms_table.php
21. 2026_01_01_000018_create_wallets_table.php
22. 2026_01_01_000019_create_wallet_balances_table.php
23. 2026_01_01_000020_create_games_table.php
24. 2026_01_01_000021_create_game_sessions_table.php
25. 2026_01_01_000022_create_coins_table.php
26. 2026_01_01_000023_create_points_table.php
27. 2026_01_01_000024_create_coin_conversions_table.php
28. 2026_01_01_000025_create_daily_rewards_table.php
29. 2026_01_01_000026_create_leaderboards_table.php
30. 2026_01_01_000027_create_activity_logs_table.php
```

---

_Document Version: 1.0.0 | Last Updated: January 2026_
