🗄️ Database Schema & ERD

Complete database structure for the iSpeaker Live platform with Entity-Relationship Diagrams, table specifications, and Laravel migration guidelines.

📊 Database Overview

The iSpeaker Live platform uses MySQL 8.0+ as the primary relational database, designed following Laravel best practices with proper indexing, relationships, and constraints.

🗂️ Total Tables

50+ tables organized across 6 core modules

🔗 Relationships

One-to-One, One-to-Many, Many-to-Many, and Polymorphic

⚡ Performance

Optimized with indexes, caching, and query optimization

🔒 Security

Foreign key constraints, soft deletes, and data encryption

Database Statistics

Module Tables Description
User Management 4 Users, profiles, interests, followers
Social Feed 5 Posts, comments, reactions, shares, reports
Courses 10 Courses, sections, lessons, enrollments, reviews, Q&A
Books 5 Books, purchases, annotations, reading progress
Live Rooms 5 Rooms, registrations, messages, polls, votes
Consultations 4 Availabilities, bookings, ratings, expertises
Payment & Wallet 6 Wallets, transactions, invoices, withdrawals, coupons
Messaging 3 Conversations, messages, blocked users
Notifications 2 Notifications, settings
Analytics 2 User analytics, speaker analytics
System 4 Categories, reports, settings, email templates

👤 Core User Management

The foundation of the platform, handling user authentication, profiles, and social relationships.

erDiagram users ||--|| user_profiles : has users ||--o{ user_interests : has users ||--o{ followers : "follows/followed_by" users { bigint id PK string name string email UK string phone UK string password enum role "student,speaker" boolean is_verified timestamp email_verified_at string avatar_url boolean is_active timestamps created_updated softDeletes deleted_at } user_profiles { bigint id PK bigint user_id FK text bio string location enum language "ar,en" string timezone json social_links boolean show_email boolean show_phone int followers_count int following_count timestamps created_updated } user_interests { bigint id PK bigint user_id FK string interest timestamps created_updated } followers { bigint id PK bigint follower_id FK bigint following_id FK timestamps created_updated }

Key Tables

users: Main authentication table with role-based access (Student or Speaker)
user_profiles: Extended profile information with privacy settings and social links

📱 Social Feed Module

Complete social networking system with posts, comments, reactions, shares, and content moderation.

erDiagram users ||--o{ posts : creates users ||--o{ comments : writes users ||--o{ reactions : gives users ||--o{ post_shares : shares users ||--o{ post_reports : reports posts ||--o{ comments : has posts ||--o{ reactions : receives posts ||--o{ post_shares : shared posts ||--o{ post_reports : reported comments ||--o{ comments : "has replies" comments ||--o{ reactions : receives posts { bigint id PK bigint user_id FK enum post_type "text,image,video,audio" text content json media_urls int reactions_count int comments_count int shares_count int views_count boolean is_pinned timestamps created_updated } comments { bigint id PK bigint user_id FK bigint post_id FK bigint parent_id FK text content string image_url int reactions_count boolean is_pinned timestamps created_updated } reactions { bigint id PK bigint user_id FK bigint reactable_id FK string reactable_type enum reaction_type "like,love,haha,wow,sad,angry" timestamps created_updated } post_shares { bigint id PK bigint user_id FK bigint post_id FK text share_comment timestamps created_updated } post_reports { bigint id PK bigint user_id FK bigint post_id FK enum report_type text description enum status timestamps created_updated }

Features

  • Post Types: Text, Image (up to 10), Video, and Audio posts
  • Reactions: 6 emotion types (Like, Love, Haha, Wow, Sad, Angry)
  • Comments: Nested comments up to 2 levels (main comment + reply)
  • Polymorphic Reactions: Same reaction system for posts and comments
  • Content Moderation: Report system with multiple categories

🎓 Course Module

Comprehensive e-learning system with video courses, progress tracking, Q&A, reviews, and certifications.

erDiagram users ||--o{ courses : creates categories ||--o{ courses : categorizes courses ||--o{ course_sections : has courses ||--o{ enrollments : has courses ||--o{ course_reviews : receives course_sections ||--o{ course_lessons : contains course_lessons ||--o{ lesson_progress : tracks course_lessons ||--o{ course_questions : has course_lessons ||--o{ lesson_notes : has course_questions ||--o{ course_answers : has enrollments ||--o{ lesson_progress : has enrollments ||--|| certificates : earns users ||--o{ enrollments : enrolls users ||--o{ course_reviews : writes users ||--o{ course_questions : asks users ||--o{ course_answers : provides users ||--o{ lesson_notes : takes courses { bigint id PK bigint user_id FK bigint category_id FK string title string slug UK text description string thumbnail_url string promo_video_url enum language "ar,en" enum level decimal price enum status int total_duration_minutes int students_count decimal average_rating timestamps created_updated } course_sections { bigint id PK bigint course_id FK string title text description int order int duration_minutes timestamps created_updated } course_lessons { bigint id PK bigint section_id FK string title text description enum content_type string video_url json video_qualities int duration_seconds int order boolean is_preview json resources timestamps created_updated } enrollments { bigint id PK bigint user_id FK bigint course_id FK decimal price_paid enum status int progress_percentage int completed_lessons timestamp last_accessed_at timestamps created_updated } lesson_progress { bigint id PK bigint enrollment_id FK bigint lesson_id FK int watched_duration_seconds boolean is_completed timestamp completed_at timestamps created_updated } course_reviews { bigint id PK bigint user_id FK bigint course_id FK int rating int content_quality int instructor_quality text review_text int helpful_count timestamps created_updated } certificates { bigint id PK bigint enrollment_id FK string certificate_number UK string verification_url timestamp issued_at timestamps created_updated }

Course Structure

Hierarchy: Course → Sections → Lessons (3-level structure)
Progress Tracking: Automatic completion when 90% watched, detailed progress per lesson
Video Quality: Multi-quality support (360p, 480p, 720p, 1080p) stored as JSON

📚 Books Module

Digital PDF library with integrated reader, annotations, bookmarks, and reading progress tracking.

erDiagram users ||--o{ books : creates categories ||--o{ books : categorizes books ||--o{ book_purchases : has users ||--o{ book_purchases : purchases book_purchases ||--|| book_reading_progress : tracks book_purchases ||--o{ book_annotations : has books ||--o{ book_reviews : receives users ||--o{ book_reviews : writes books { bigint id PK bigint user_id FK bigint category_id FK string title string slug UK string author text description string cover_image_url string pdf_url string isbn int page_count decimal price int preview_pages enum status boolean allow_printing boolean allow_copying timestamps created_updated } book_purchases { bigint id PK bigint user_id FK bigint book_id FK decimal price_paid enum status timestamp purchased_at timestamps created_updated } book_reading_progress { bigint id PK bigint purchase_id FK int current_page int total_pages_read int reading_time_minutes timestamp last_read_at timestamps created_updated } book_annotations { bigint id PK bigint purchase_id FK int page_number enum annotation_type "highlight,note,underline,bookmark" string color text content json position_data timestamps created_updated } book_reviews { bigint id PK bigint user_id FK bigint book_id FK int rating text review_text int helpful_count timestamps created_updated }

Features

  • PDF Reader: Integrated reader with zoom, search, and navigation
  • Annotations: Highlights (multiple colors), notes, underlines, bookmarks
  • DRM Protection: PDF watermarking, copy/print restrictions, device limits
  • Progress Sync: Reading progress syncs across devices

🎥 Live Rooms Module

Group live streaming with Jitsi Meet integration for webinars, workshops, and interactive sessions.

erDiagram users ||--o{ live_rooms : hosts categories ||--o{ live_rooms : categorizes live_rooms ||--o{ live_room_registrations : has live_rooms ||--o{ live_room_messages : has live_rooms ||--o{ live_room_polls : has users ||--o{ live_room_registrations : registers users ||--o{ live_room_messages : sends live_room_polls ||--o{ poll_votes : receives users ||--o{ poll_votes : casts live_rooms { bigint id PK bigint user_id FK bigint category_id FK string title text description datetime scheduled_at int duration_minutes enum access_type "free,paid" decimal price int max_participants enum status string jitsi_room_id UK boolean is_recorded string recording_url timestamps created_updated } live_room_registrations { bigint id PK bigint user_id FK bigint room_id FK decimal price_paid enum status timestamp joined_at int attendance_duration_minutes timestamps created_updated } live_room_messages { bigint id PK bigint room_id FK bigint user_id FK enum message_type text content string file_url boolean is_pinned timestamps created_updated } live_room_polls { bigint id PK bigint room_id FK string question json options boolean allow_multiple boolean is_active timestamps created_updated } poll_votes { bigint id PK bigint poll_id FK bigint user_id FK json selected_options timestamps created_updated }

Integration Details

Jitsi Meet: Self-hosted video conferencing with HD quality (up to 1080p)
Features: Screen sharing, chat, polls, Q&A, reactions, and recording capabilities

🤝 Consultation Module

One-on-one video consultations with availability management, booking system, and mutual ratings.

erDiagram users ||--o{ speaker_expertises : has users ||--o{ consultation_availabilities : sets users ||--o{ consultation_bookings : "books/hosts" consultation_availabilities ||--o{ consultation_bookings : has consultation_bookings ||--o{ consultation_ratings : receives users ||--o{ consultation_ratings : "rates/rated" speaker_expertises { bigint id PK bigint user_id FK string expertise timestamps created_updated } consultation_availabilities { bigint id PK bigint user_id FK enum day_of_week time start_time time end_time int session_duration decimal price_per_session boolean is_active timestamps created_updated } consultation_bookings { bigint id PK bigint student_id FK bigint speaker_id FK bigint availability_id FK datetime scheduled_at int duration_minutes decimal price_paid enum status string meeting_url string jitsi_room_id text session_notes timestamps created_updated } consultation_ratings { bigint id PK bigint booking_id FK bigint rater_id FK bigint rated_id FK int rating text review timestamps created_updated }

Consultation Types

  • Student to Speaker: Learning guidance, career advice, mentorship
  • Speaker to Speaker: Professional collaboration and knowledge exchange
  • Duration Options: 30, 60, or 90-minute sessions
  • Scheduling: Flexible availability with buffer times and auto-accept options

💰 Payment & Wallet Module

Complete financial system with digital wallets, transactions, invoices, withdrawals, and coupons with 15% VAT compliance.

erDiagram users ||--|| wallets : owns wallets ||--o{ transactions : has wallets ||--o{ withdrawal_requests : has transactions ||--|| invoices : generates users ||--o{ transactions : performs users ||--o{ coupons : creates coupons ||--o{ coupon_usage : tracks users ||--o{ coupon_usage : uses wallets { bigint id PK bigint user_id FK decimal balance decimal total_earned decimal total_withdrawn decimal pending_balance timestamps created_updated } transactions { bigint id PK bigint wallet_id FK bigint user_id FK enum type "credit,debit" enum category bigint related_id string related_type decimal amount decimal balance_after string payment_method string transaction_reference UK enum status timestamps created_updated } invoices { bigint id PK bigint transaction_id FK bigint user_id FK string invoice_number UK decimal subtotal decimal vat_amount decimal total json items string pdf_url timestamps created_updated } withdrawal_requests { bigint id PK bigint wallet_id FK bigint user_id FK decimal amount string bank_name string account_number enum status text rejection_reason timestamps created_updated } coupons { bigint id PK bigint user_id FK string code UK enum discount_type decimal discount_value int usage_limit datetime valid_from datetime valid_until boolean is_active timestamps created_updated }

Payment Features

Feature Description
Payment Gateways PayTabs, HyperPay (Visa, Mastercard, Mada, STC Pay, Apple Pay)
VAT Compliance 15% VAT automatically calculated and included in all invoices
Commission Courses/Books: 20% platform fee | Live Rooms/Consultations: 15% fee
Withdrawals Minimum 100 SAR, weekly or monthly payouts to bank accounts
Refunds Automated refund processing with balance adjustment

💬 Messaging Module

Real-time direct messaging system with conversation threads, read receipts, and user blocking.

erDiagram users ||--o{ conversations : "participates" users ||--o{ direct_messages : "sends/receives" users ||--o{ blocked_users : "blocks/blocked" conversations ||--o{ direct_messages : contains conversations { bigint id PK bigint user1_id FK bigint user2_id FK bigint last_message_id FK timestamp last_message_at timestamps created_updated } direct_messages { bigint id PK bigint sender_id FK bigint receiver_id FK bigint conversation_id FK enum message_type text content string file_url boolean is_read timestamp read_at timestamps created_updated } blocked_users { bigint id PK bigint blocker_id FK bigint blocked_id FK timestamps created_updated }

Messaging Features

  • Message Types: Text, images, files, voice messages
  • Real-time: WebSocket integration with Laravel Echo
  • Read Receipts: Delivered and read indicators
  • Privacy: Block users, delete messages, typing indicators

📊 Analytics & System Tables

Analytics Tables

Table Purpose Key Metrics
user_analytics Daily user activity tracking Posts created, courses completed, time spent, books read
speaker_analytics Daily speaker performance Revenue earned, students gained, content sold, followers

System Tables

Table Purpose
categories Hierarchical categories for courses, books, and live rooms
content_reports Content moderation with polymorphic reporting
notifications Multi-channel notification system
notification_settings User notification preferences and quiet hours
platform_settings Global platform configuration
email_templates Dynamic email templates with variable support

🏗️ Database Design Principles

Normalization

3NF normalization to eliminate redundancy while maintaining performance

Indexing Strategy

Composite indexes on frequently queried columns for optimal performance

Foreign Keys

Referential integrity with CASCADE and SET NULL constraints

Soft Deletes

Preserve data integrity with deleted_at timestamps

JSON Columns

Flexible data storage for dynamic content (video qualities, metadata)

Polymorphic Relations

Reusable tables (reactions, reports) across multiple entities

Timestamps

created_at and updated_at on all tables for audit trails

Unique Constraints

Prevent duplicates with composite unique indexes

🔧 Laravel Migration Guidelines

Migration Order

Execute migrations in the following order to respect foreign key dependencies:

1. users
2. user_profiles, user_interests
3. categories
4. courses, books, live_rooms
5. course_sections
6. course_lessons
7. enrollments, book_purchases, live_room_registrations
8. lesson_progress, book_annotations
9. posts, comments, reactions
10. consultation_availabilities, consultation_bookings
11. wallets, transactions, invoices
12. conversations, direct_messages
13. notifications, notification_settings
14. analytics tables
15. system tables (platform_settings, email_templates)

Example Migration

// database/migrations/xxxx_create_courses_table.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('courses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')
                  ->constrained()
                  ->onDelete('cascade');
            $table->foreignId('category_id')
                  ->constrained()
                  ->onDelete('cascade');
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('subtitle')->nullable();
            $table->text('description')->nullable();
            $table->string('thumbnail_url')->nullable();
            $table->string('promo_video_url')->nullable();
            $table->enum('language', ['ar', 'en'])->default('ar');
            $table->enum('level', ['beginner', 'intermediate', 'advanced'])
                  ->default('beginner');
            $table->decimal('price', 10, 2)->default(0);
            $table->decimal('discount_price', 10, 2)->nullable();
            $table->enum('status', ['draft', 'published', 'archived'])
                  ->default('draft');
            $table->integer('total_duration_minutes')->default(0);
            $table->integer('total_lessons')->default(0);
            $table->integer('students_count')->default(0);
            $table->decimal('average_rating', 3, 2)->default(0);
            $table->integer('reviews_count')->default(0);
            $table->timestamps();
            $table->softDeletes();
            
            // Indexes
            $table->index('user_id');
            $table->index('category_id');
            $table->index('slug');
            $table->index('status');
            $table->index(['category_id', 'status']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('courses');
    }
};

⚡ Performance Optimization

Indexing Strategy

Table Index Type Columns Purpose
users Unique email Fast authentication lookups
posts Composite user_id, created_at User feed queries
enrollments Unique user_id, course_id Prevent duplicate enrollments
transactions Index transaction_reference Payment gateway callbacks
reactions Unique Composite user_id, reactable_id, reactable_type One reaction per user per item

Query Optimization Tips

  • Eager Loading: Use with() to prevent N+1 queries
  • Select Specific Columns: Avoid SELECT * when possible
  • Pagination: Use paginate() for large datasets
  • Caching: Redis for frequently accessed data (user sessions, course lists)
  • Query Scopes: Reusable query logic in Eloquent models
Warning: JSON columns cannot be efficiently indexed. Use sparingly and only for truly dynamic data.

🛠️ Database Tools & Resources

Visual ERD Tools

  • dbdiagram.io: Online ERD tool (DBML code provided in repository)
  • MySQL Workbench: Official MySQL database design tool
  • TablePlus: Modern database management GUI
  • Laravel Debugbar: Query profiling and optimization

DBML Export

The complete database schema is available in DBML format for use with dbdiagram.io:

// Copy the content from: database/schema/ispeaker_dbdiagram.dbml
// Paste into: https://dbdiagram.io/d
// The ERD will be automatically generated
Repository: All migration files, seeders, and DBML schema are available in the project repository.
← Previous: Architecture Next: API Documentation →