🗄️ Database & ERD

Overview

MySQL 8.0, utf8mb4_unicode_ci across all tables. 40+ tables grouped into 10 functional domains, with foreign-key cascades and supporting indexes for the high-traffic listings (feed, marketplace, search).

Core ERD

The simplified relationship overview. See per-domain sections for details.

Users & Profiles

Tables

• users — name, username, email, phone, password, flags (is_verified, is_active, is_online), avatar/cover URLs, headline. Soft-deletable.
• user_profiles — bio, location, language (ar/en), timezone, social_links JSON, privacy flags, follower/following counts.
• user_interests — pivot users ↔ categories (unique pair).
• user_certificates — title, issuer, dates, credential URL.
• user_experiences — title, company, employment_type, dates, location.
• speaker_expertises — free-text expertise tags for speakers.

Notable indexes

• users(email), users(phone), users(username) — all unique.
• users(is_active), users(email) — listing performance.
• user_certificates(user_id, order) and user_experiences(user_id, order) for ordering.

Social Graph & Feed

Follow system

• followers — directed edges (follower_id → following_id), unique pair.
• follow_requests — pending / accepted / rejected for private profiles.

Posts & engagement

• posts — post_type (text|image|video|audio), content, media_urls JSON, counts cached on row (reactions_count, comments_count, shares_count, views_count), is_pinned.
• comments — threaded via parent_id, optional image, pinning, soft-delete.
• reactions — polymorphic on reactable_type / reactable_id; unique per (user, reactable). Types: like, love, haha, wow, sad, angry.
• post_shares — record shares with optional comment.
• post_reports — spam, harassment, hate_speech, violence, misinformation, other; admin status workflow.

Courses

• categories — bilingual names (name_en, name_ar), order, active flag.
• courses — user, primary category + JSON of additional categories, slug (unique), language (ar|en), level, price (+ discount_price), thumbnail, promo video, status (draft|published|archived), cached counts (total_lessons, students_count, average_rating).
• course_sections — ordered sections per course with duration cache.
• course_lessons — title, content_type (default video), content_url, duration_seconds, link, attachments JSON, order, is_preview, resources JSON.
• enrollments — unique (user, course); status (active|completed|expired|cancelled), progress_percentage, completed_lessons, last_accessed_at.
• lesson_progress — per (enrollment, lesson): watched_duration_seconds, is_completed.
• lesson_notes — user notes per lesson, optional timestamp_seconds for video sync.
• certificates — issued on completion with unique certificate_number and verification_url.

Books

• books — user, category, slug (unique), author, description, cover, pdf_url, isbn, page_count, price, preview_pages, status, allow_printing, allow_copying, purchases_count, average_rating.
• book_purchases — unique (user, book) with status (completed|refunded) and price_paid.
• book_annotations — per purchase & page_number; types: highlight, note, underline, bookmark; color, content, position_data JSON.
• book_reading_progress — per purchase: current_page, total_pages_read, reading_time_minutes, last_read_at.

Live Rooms

• live_rooms — user, category, dates & times, duration_type (day/week/month/3month), access_type (free|paid), price, max_participants, status (scheduled|active|completed|cancelled), current_jitsi_room_id, recordings JSON.
• live_room_registrations — unique (user, room); status (pending|registered|attended|missed|cancelled), price_paid, payment_method.
• live_room_messages — per room with parent_id for threading, types (text|emoji|system|announcement), pinning, deletion, reactions_count.

Consultations

• consultation_availabilities — per speaker, day_of_week (enum mon..sun), start/end time, session_duration, price_per_session.
• consultation_bookings — student_id, speaker_id, availability_id, scheduled_at, duration_minutes, price_paid, status (pending|scheduled|completed|cancelled|no_show), meeting_url, jitsi_room_id, session_notes.
• consultation_ratings — booking_id, rater & rated user IDs, rating, review.

Wallet, Transactions & Invoices

• wallets — one per user (user_id unique): balance, total_earned, total_withdrawn, pending_balance.
• transactions — wallet_id, user_id, type (credit|debit), category (course, book, consultation, gift, top-up, withdrawal, refund...), polymorphic related_type + related_id, amount, balance_after, payment_method, transaction_reference (unique), status, metadata JSON.
• invoices — one per completed transaction: invoice_number (unique), subtotal, vat_amount, total, items JSON, pdf_url.

Gifts

• gifts — catalogue: name, icon, price, order, is_active.
• gift_transactions — gift_id, sender_id, receiver_id, polymorphic giftable (post / room / etc.), quantity, total_price, message.

Chat

• conversations — unique (user_one_id, user_two_id) pair, last_message_id, per-user last_read_at; soft-deletable.
• messages — conversation_id, sender_id, type (text|image|video|audio|file), body, media_urls JSON, metadata JSON, reply_to_id, read_at; soft-deletable.

System Tables

• roles, permissions, model_has_roles, model_has_permissions, role_has_permissions — Spatie permission stack.
• personal_access_tokens — Sanctum tokens with abilities, last_used_at, expires_at.
• sessions — web sessions when used.
• jobs, job_batches, failed_jobs — queue subsystem.
• cache, cache_locks — Laravel cache store (Redis is the default in production).
• notifications — in-app notifications with polymorphic notifiable.
• settings — admin-configurable key/value with type, group, validation rules.
• password_reset_tokens — password recovery.
• migrations — Laravel migrations log.

Data Integrity Rules

• Foreign keys cascade on user deletion for owned content (posts, courses, books, rooms, wallets, …).
• Set null on deletion of optional references (e.g. messages.reply_to_id, notifications.actor_id).
• Unique constraints protect pairings: (follower, following), (user, course) for enrollments, (user, book) for purchases, (user, reactable) for reactions, (user_one, user_two) for conversations.
• Soft deletes on user-facing entities (users, posts, comments, conversations, messages, books, courses, live_rooms).
• Enums are used for stable state machines (status fields) — kept in sync with PHP constants.
• JSON columns for flexible structures (recordings, social_links, items, position_data, attachments).