# LMS Database Schema Documentation (MySQL)

> **Note:** For direct MySQL execution, use [`lms_database.sql`](lms_database.sql)

## Overview

This document describes the complete database schema for the Mutalex Academy Learning Management System (LMS). The database is built on MySQL/MariaDB with InnoDB engine and uses utf8mb4 character set for full Unicode support.

---

## Table of Contents

1. [Users & Authentication](#users--authentication)
2. [Courses & Content](#courses--content)
3. [Exams & Assessments](#exams--assessments)
4. [Payments & Transactions](#payments--transactions)
5. [Content Management](#content-management)
6. [Analytics & Tracking](#analytics--tracking)
7. [User Data & Profiles](#user-data--profiles)

---

## 1. Users & Authentication

### 1.1 [`users`](supabase/migrations/yujbjplf_lms.sql:1558) Table

Core user table storing all registered users (students, instructors, admins).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Unique user ID |
| `username` | VARCHAR(100) | NOT NULL, UNIQUE | Username for login |
| `email` | VARCHAR(255) | NOT NULL, UNIQUE | User email address |
| `password` | VARCHAR(255) | NOT NULL | Hashed password |
| `role` | ENUM | NOT NULL | User role: student, instructor, admin |
| `first_name` | VARCHAR(100) | NULL | First name |
| `last_name` | VARCHAR(100) | NULL | Last name |
| `avatar` | VARCHAR(255) | NULL | Profile picture URL |
| `phone` | VARCHAR(20) | NULL | Phone number |
| `is_active` | BOOLEAN | DEFAULT TRUE | Account status |
| `email_verified` | BOOLEAN | DEFAULT FALSE | Email verification status |
| `last_login` | TIMESTAMP | NULL | Last login timestamp |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation date |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Last update |

**Indexes:**
- `username` (UNIQUE)
- `email` (UNIQUE)
- `role`
- `is_active`

---

### 1.2 [`user_sessions`](supabase/migrations/yujbjplf_lms.sql:1688) Table

Tracks active user sessions for security and session management.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Session ID |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `session_token` | VARCHAR(255) | NOT NULL, UNIQUE | Session identifier |
| `ip_address` | VARCHAR(45) | NULL | IP address |
| `user_agent` | TEXT | NULL | Browser/User agent |
| `expires_at` | TIMESTAMP | NOT NULL | Session expiration |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

---

### 1.3 [`user_profiles`](supabase/migrations/yujbjplf_lms.sql:1664) Table

Extended profile information for users.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Profile ID |
| `user_id` | INT | FK → users(id), UNIQUE | User reference |
| `bio` | TEXT | NULL | User biography |
| `date_of_birth` | DATE | NULL | Date of birth |
| `gender` | ENUM | NULL | Gender: male, female, other |
| `address` | TEXT | NULL | Physical address |
| `city` | VARCHAR(100) | NULL | City |
| `country` | VARCHAR(100) | NULL | Country |
| `postal_code` | VARCHAR(20) | NULL | Postal code |
| `social_links` | JSON | NULL | Social media links |
| `preferences` | JSON | NULL | User preferences |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 1.4 [`user_activities`](supabase/migrations/yujbjplf_lms.sql:1617) Table

Logs all user activities for audit and analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Activity ID |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `action_type` | VARCHAR(50) | NOT NULL | Activity type |
| `action_description` | TEXT | NULL | Activity description |
| `ip_address` | VARCHAR(45) | NULL | IP address |
| `user_agent` | TEXT | NULL | Browser info |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Indexes:**
- `user_id`
- `action_type`
- `created_at`

---

### 1.5 [`user_achievements`](supabase/migrations/yujbjplf_lms.sql:1593) Table

Tracks user achievements and badges.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Achievement ID |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `achievement_type` | VARCHAR(100) | NOT NULL | Type of achievement |
| `title` | VARCHAR(255) | NOT NULL | Achievement title |
| `description` | TEXT | NULL | Description |
| `icon` | VARCHAR(255) | NULL | Icon URL |
| `earned_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | When earned |
| `metadata` | JSON | NULL | Additional data |

---

### 1.6 [`user_payment_methods`](supabase/migrations/yujbjplf_lms.sql:1641) Table

Stores user saved payment methods (for future use).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Method ID |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `payment_type` | VARCHAR(50) | NOT NULL | Payment type |
| `token` | VARCHAR(255) | NOT NULL | Payment token |
| `is_default` | BOOLEAN | DEFAULT FALSE | Default method |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

## 2. Courses & Content

### 2.1 [`courses`](supabase/migrations/yujbjplf_lms.sql:326) Table

Main courses table storing course information.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Course ID |
| `title` | VARCHAR(255) | NOT NULL | Course title |
| `slug` | VARCHAR(255) | UNIQUE, NOT NULL | URL-friendly slug |
| `description` | LONGTEXT | NULL | Full description |
| `short_description` | TEXT | NULL | Brief description |
| `instructor_id` | INT | FK → users(id), NOT NULL | Instructor |
| `category_id` | INT | FK → course_categories(id), NULL | Category |
| `thumbnail` | VARCHAR(255) | NULL | Thumbnail URL |
| `banner_image` | VARCHAR(255) | NULL | Banner image URL |
| `price` | DECIMAL(10,2) | DEFAULT 0.00 | Course price |
| `currency` | VARCHAR(3) | DEFAULT 'USD' | Currency code |
| `level` | ENUM | DEFAULT 'beginner' | Difficulty: beginner, intermediate, advanced |
| `duration_hours` | INT | DEFAULT 0 | Total duration in hours |
| `credit_hours` | DECIMAL(5,2) | DEFAULT 0 | Credit hours |
| `language` | VARCHAR(50) | DEFAULT 'English' | Language |
| `max_students` | INT | NULL | Maximum students |
| `enrollment_deadline` | DATE | NULL | Enrollment deadline |
| `prerequisites` | TEXT | NULL | Prerequisites text |
| `learning_objectives` | JSON | NULL | Learning objectives array |
| `tags` | JSON | NULL | Tags array |
| `status` | ENUM | DEFAULT 'draft' | Status: draft, published, archived |
| `is_featured` | BOOLEAN | DEFAULT FALSE | Featured flag |
| `is_free` | BOOLEAN | DEFAULT TRUE | Free course flag |
| `certificate_enabled` | BOOLEAN | DEFAULT FALSE | Certificate availability |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `instructor_id`
- `category_id`
- `status`
- `is_featured`
- `slug` (UNIQUE)

---

### 2.2 [`course_categories`](supabase/migrations/yujbjplf_lms.sql:390) Table

Course category hierarchy.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Category ID |
| `name` | VARCHAR(100) | NOT NULL | Category name |
| `slug` | VARCHAR(100) | UNIQUE, NOT NULL | URL slug |
| `description` | TEXT | NULL | Description |
| `icon` | VARCHAR(50) | NULL | Icon class |
| `color` | VARCHAR(20) | NULL | Color hex code |
| `parent_id` | INT | FK → course_categories(id), NULL | Parent category |
| `sort_order` | INT | DEFAULT 0 | Display order |
| `is_active` | BOOLEAN | DEFAULT TRUE | Active status |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `slug` (UNIQUE)
- `parent_id`
- `is_active`

---

### 2.3 [`course_modules`](supabase/migrations/yujbjplf_lms.sql:569) Table

Modules within a course (section organization).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Module ID |
| `course_id` | INT | FK → courses(id), NOT NULL | Course reference |
| `title` | VARCHAR(255) | NOT NULL | Module title |
| `slug` | VARCHAR(255) | NOT NULL | URL slug |
| `description` | TEXT | NULL | Module description |
| `sort_order` | INT | DEFAULT 1 | Display order |
| `estimated_time` | INT | DEFAULT 0 | Estimated minutes |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `course_id`
- `slug` (UNIQUE)

---

### 2.4 [`course_topics`](supabase/migrations/yujbjplf_lms.sql:678) Table

Topics within course modules.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Topic ID |
| `module_id` | INT | FK → course_modules(id), NOT NULL | Module reference |
| `title` | VARCHAR(255) | NOT NULL | Topic title |
| `slug` | VARCHAR(255) | NOT NULL | URL slug |
| `description` | TEXT | NULL | Topic description |
| `sort_order` | INT | DEFAULT 1 | Display order |
| `estimated_time` | INT | DEFAULT 0 | Estimated minutes |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `module_id`
- `slug` (UNIQUE within module)

---

### 2.5 [`course_lessons`](supabase/migrations/yujbjplf_lms.sql:463) Table

Individual lessons within courses.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Lesson ID |
| `course_id` | INT | FK → courses(id), NOT NULL | Course reference |
| `module_id` | INT | FK → course_modules(id), NULL | Module reference |
| `topic_id` | INT | FK → course_topics(id), NULL | Topic reference |
| `title` | VARCHAR(255) | NOT NULL | Lesson title |
| `slug` | VARCHAR(255) | NOT NULL | URL slug |
| `description` | TEXT | NULL | Lesson description |
| `content` | LONGTEXT | NULL | Lesson content |
| `lesson_type` | ENUM | DEFAULT 'text' | Type: video, text, quiz, assignment, download |
| `video_url` | VARCHAR(500) | NULL | Video URL |
| `attachment_url` | VARCHAR(500) | NULL | File attachment URL |
| `attachment_name` | VARCHAR(255) | NULL | Attachment filename |
| `sort_order` | INT | DEFAULT 1 | Display order |
| `is_preview` | BOOLEAN | DEFAULT FALSE | Preview available |
| `estimated_time` | INT | DEFAULT 0 | Estimated minutes |
| `learning_objectives` | JSON | NULL | Learning objectives |
| `prerequisites` | JSON | NULL | Prerequisites |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `course_id`
- `module_id`
- `topic_id`
- `lesson_type`
- `slug` (UNIQUE with course_id)

---

### 2.6 [`course_resources`](supabase/migrations/yujbjplf_lms.sql:611) Table

Resources available for courses.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Resource ID |
| `title` | VARCHAR(255) | NOT NULL | Resource title |
| `type` | ENUM | NOT NULL | Type: document, video, audio, link, archive |
| `file_url` | VARCHAR(500) | NULL | File URL |
| `external_url` | VARCHAR(500) | NULL | External link |
| `file_size` | BIGINT | NULL | Size in bytes |
| `mime_type` | VARCHAR(100) | NULL | MIME type |
| `description` | TEXT | NULL | Description |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 2.7 [`lesson_resources`](supabase/migrations/yujbjplf_lms.sql:937) Table

Links between lessons and resources (many-to-many).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `lesson_id` | INT | FK → course_lessons(id), NOT NULL | Lesson reference |
| `resource_id` | INT | FK → course_resources(id), NOT NULL | Resource reference |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Unique:** `(lesson_id, resource_id)`

---

### 2.8 [`course_enrollments`](supabase/migrations/yujbjplf_lms.sql:425) Table

Student course enrollments.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Enrollment ID |
| `course_id` | INT | FK → courses(id), NOT NULL | Course reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `payment_status` | ENUM | DEFAULT 'paid' | Status: pending, paid, failed, refunded |
| `payment_amount` | DECIMAL(10,2) | DEFAULT 0.00 | Amount paid |
| `status` | ENUM | DEFAULT 'active' | Enrollment: enrolled, in_progress, completed, dropped, active |
| `enrollment_date` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Enrollment date |
| `completion_date` | TIMESTAMP | NULL | Completion date |
| `progress_percentage` | DECIMAL(5,2) | DEFAULT 0.00 | Progress 0-100% |
| `certificate_issued` | BOOLEAN | DEFAULT FALSE | Certificate issued |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(course_id, student_id)`

**Indexes:**
- `student_id`
- `status`
- `payment_status`

---

### 2.9 [`course_lesson_progress`](supabase/migrations/yujbjplf_lms.sql:530) Table

Tracks student progress in each lesson.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Progress ID |
| `enrollment_id` | INT | FK → course_enrollments(id), NOT NULL | Enrollment reference |
| `lesson_id` | INT | FK → course_lessons(id), NOT NULL | Lesson reference |
| `status` | ENUM | DEFAULT 'not_started' | Status: not_started, in_progress, completed |
| `progress_percentage` | DECIMAL(5,2) | DEFAULT 0.00 | Progress 0-100% |
| `time_spent` | INT | DEFAULT 0 | Seconds spent |
| `last_accessed` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last access |
| `completed_at` | TIMESTAMP | NULL | Completion timestamp |
| `notes` | TEXT | NULL | Student notes |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(enrollment_id, lesson_id)`

---

### 2.10 [`course_reviews`](supabase/migrations/yujbjplf_lms.sql:651) Table

Student reviews and ratings for courses.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Review ID |
| `course_id` | INT | FK → courses(id), NOT NULL | Course reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `rating` | DECIMAL(3,2) | NOT NULL | Rating 0-5 |
| `review_text` | TEXT | NULL | Review content |
| `status` | ENUM | DEFAULT 'pending' | Status: pending, approved, rejected |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(course_id, student_id)`

---

### 2.11 [`lesson_versions`](supabase/migrations/yujbjplf_lms.sql:950) Table

Version history for lesson content.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Version ID |
| `lesson_id` | INT | FK → course_lessons(id), NOT NULL | Lesson reference |
| `version_number` | INT | NOT NULL | Version number |
| `content` | LONGTEXT | NULL | Content snapshot |
| `change_summary` | TEXT | NULL | Change description |
| `created_by` | INT | FK → users(id), NOT NULL | Author |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Unique:** `(lesson_id, version_number)`

---

## 3. Exams & Assessments

### 3.1 [`exams`](supabase/migrations/yujbjplf_lms.sql:744) Table

Exam definitions and configuration.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Exam ID |
| `title` | VARCHAR(255) | NOT NULL | Exam title |
| `slug` | VARCHAR(255) | UNIQUE, NOT NULL | URL slug |
| `description` | LONGTEXT | NULL | Full description |
| `course_id` | INT | FK → courses(id), NULL | Associated course |
| `instructor_id` | INT | FK → users(id), NOT NULL | Exam creator |
| `exam_type` | ENUM | DEFAULT 'quiz' | Type: quiz, exam, assignment |
| `time_limit` | INT | NULL | Time limit in minutes |
| `passing_score` | DECIMAL(5,2) | DEFAULT 0.00 | Passing percentage |
| `max_attempts` | INT | DEFAULT 1 | Maximum attempts allowed |
| `randomize_questions` | BOOLEAN | DEFAULT FALSE | Randomize question order |
| `randomize_options` | BOOLEAN | DEFAULT FALSE | Randomize option order |
| `show_results` | BOOLEAN | DEFAULT TRUE | Show results after submission |
| `show_correct_answers` | BOOLEAN | DEFAULT TRUE | Show correct answers |
| `price` | DECIMAL(10,2) | DEFAULT 0.00 | Exam price |
| `currency` | VARCHAR(3) | DEFAULT 'USD' | Currency |
| `is_free` | BOOLEAN | DEFAULT TRUE | Free exam flag |
| `is_active` | BOOLEAN | DEFAULT TRUE | Active status |
| `start_date` | DATETIME | NULL | Available from |
| `end_date` | DATETIME | NULL | Available until |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `course_id`
- `instructor_id`
- `is_active`
- `slug` (UNIQUE)

---

### 3.2 [`questions`](supabase/migrations/yujbjplf_lms.sql:1159) Table

Question bank for exams.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Question ID |
| `question_text` | LONGTEXT | NOT NULL | Question content |
| `question_type` | ENUM | NOT NULL | Type: multiple_choice, true_false, essay, short_answer, fill_blank, matching |
| `points` | DECIMAL(10,2) | DEFAULT 1.00 | Point value |
| `correct_answer` | TEXT | NULL | Correct answer (for auto-grading) |
| `explanation` | TEXT | NULL | Answer explanation |
| `hint` | TEXT | NULL | Question hint |
| `image_url` | VARCHAR(500) | NULL | Question image |
| `difficulty` | ENUM | DEFAULT 'medium' | Level: easy, medium, hard |
| `category_id` | INT | FK → question_categories(id), NULL | Category |
| `tags` | JSON | NULL | Tags |
| `is_active` | BOOLEAN | DEFAULT TRUE | Active status |
| `created_by` | INT | FK → users(id), NOT NULL | Creator |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `question_type`
- `difficulty`
- `category_id`
- `created_by`

---

### 3.3 [`question_options`](supabase/migrations/yujbjplf_lms.sql:1292) Table

Answer options for multiple choice questions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Option ID |
| `question_id` | INT | FK → questions(id), NOT NULL | Question reference |
| `option_text` | TEXT | NOT NULL | Option content |
| `option_order` | INT | DEFAULT 0 | Display order |
| `is_correct` | BOOLEAN | DEFAULT FALSE | Correct answer flag |
| `points` | DECIMAL(10,2) | DEFAULT 0.00 | Point value for this option |
| `feedback` | TEXT | NULL | Feedback for this option |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Indexes:**
- `question_id`
- `is_correct`

---

### 3.4 [`exam_questions`](supabase/migrations/yujbjplf_lms.sql:881) Table

Links questions to exams (many-to-many).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `exam_id` | INT | FK → exams(id), NOT NULL | Exam reference |
| `question_id` | INT | FK → questions(id), NOT NULL | Question reference |
| `sort_order` | INT | DEFAULT 1 | Display order |
| `points` | DECIMAL(10,2) | DEFAULT 1.00 | Point value (can override question) |
| `is_mandatory` | BOOLEAN | DEFAULT TRUE | Must be answered |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Unique:** `(exam_id, question_id)`

---

### 3.5 [`exam_attempts`](supabase/migrations/yujbjplf_lms.sql:820) Table

Tracks student exam attempts.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Attempt ID |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `exam_id` | INT | FK → exams(id), NOT NULL | Exam reference |
| `attempt_number` | INT | NOT NULL, DEFAULT 1 | Attempt count |
| `started_at` | TIMESTAMP | NULL | Start time |
| `ended_at` | TIMESTAMP | NULL | End time |
| `score` | DECIMAL(10,2) | NULL | Achieved score |
| `max_score` | DECIMAL(10,2) | NULL | Maximum possible score |
| `percentage` | DECIMAL(5,2) | NULL | Score percentage |
| `grade` | VARCHAR(10) | NULL | Letter grade |
| `status` | ENUM | DEFAULT 'in_progress' | Status: in_progress, saved, submitted, graded, expired, abandoned, pending_review |
| `time_limit_minutes` | INT | NULL | Time limit |
| `time_spent_seconds` | INT | DEFAULT 0 | Time spent |
| `ip_address` | VARCHAR(45) | NULL | IP address |
| `device_info` | JSON | NULL | Device information |
| `user_agent` | TEXT | NULL | Browser info |
| `exam_version` | VARCHAR(50) | NULL | Exam version |
| `randomized_questions` | BOOLEAN | DEFAULT FALSE | Question order randomized |
| `tab_switch_count` | INT | DEFAULT 0 | Tab switches detected |
| `max_tab_switches` | INT | DEFAULT 0 | Max allowed switches |
| `is_flagged` | BOOLEAN | DEFAULT FALSE | Flagged for review |
| `flag_reason` | TEXT | NULL | Flag reason |
| `expires_at` | TIMESTAMP | NULL | Expiration time |
| `is_time_expired` | BOOLEAN | DEFAULT FALSE | Time expired flag |
| `submitted_early` | BOOLEAN | DEFAULT FALSE | Submitted before time limit |
| `auto_submitted` | BOOLEAN | DEFAULT FALSE | Auto-submitted by system |
| `submission_ip` | VARCHAR(45) | NULL | Submission IP |
| `abandoned_at` | TIMESTAMP | NULL | Abandonment time |
| `abandonment_reason` | TEXT | NULL | Abandonment reason |
| `graded_by` | INT | FK → users(id), NULL | Grader |
| `graded_at` | TIMESTAMP | NULL | Grading time |
| `grading_method` | ENUM | NULL | Method: auto, manual, ai, hybrid |
| `grading_comments` | TEXT | NULL | Grader comments |
| `feedback_provided` | BOOLEAN | DEFAULT FALSE | Feedback given |
| `feedback_viewed_at` | TIMESTAMP | NULL | Feedback viewed time |
| `feedback` | TEXT | NULL | Instructor feedback text |
| `certificate_id` | INT | FK → certificates(id), NULL | Certificate issued |
| `certificate_issued_at` | TIMESTAMP | NULL | Certificate issued time |
| `is_deleted` | BOOLEAN | DEFAULT FALSE | Soft delete |
| `deleted_at` | TIMESTAMP | NULL | Delete time |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(exam_id, student_id, attempt_number)`

**Indexes:**
- `student_id`, `exam_id`, `status`
- `status`, `started_at`
- `expires_at`
- `score` (DESC)

---

### 3.6 [`student_answers`](supabase/migrations/yujbjplf_lms.sql:1435) Table

Student submitted answers for exam questions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Answer ID |
| `exam_id` | INT | FK → exams(id), NOT NULL | Exam reference |
| `attempt_id` | INT | FK → exam_attempts(id), NULL | Attempt reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `question_id` | INT | FK → questions(id), NOT NULL | Question reference |
| `answer_content` | LONGTEXT | NULL | Written answer |
| `answer_type` | VARCHAR(20) | NULL | Answer format |
| `answer_hash` | VARCHAR(64) | NULL | Content hash |
| `answer_length` | INT | DEFAULT 0 | Content length |
| `word_count` | INT | DEFAULT 0 | Word count |
| `char_count` | INT | DEFAULT 0 | Character count |
| `is_correct` | BOOLEAN | NULL | Correctness flag |
| `score` | DECIMAL(10,2) | NULL | Points earned |
| `max_score` | DECIMAL(10,2) | NULL | Max possible points |
| `feedback` | TEXT | NULL | Instructor feedback |
| `is_graded` | BOOLEAN | DEFAULT FALSE | Graded flag |
| `graded_by` | INT | FK → users(id), NULL | Grader |
| `graded_at` | TIMESTAMP | NULL | Grading time |
| `grading_method` | ENUM | NULL | Method: auto, manual, ai, hybrid |
| `grading_completed_at` | TIMESTAMP | NULL | Grading completion |
| `partial_credit` | DECIMAL(5,2) | DEFAULT 0.00 | Partial credit |
| `rubric_score` | DECIMAL(10,2) | DEFAULT 0.00 | Rubric score |
| `rubric_id` | INT | NULL | Rubric reference |
| `grade_comments` | TEXT | NULL | Grader comments |
| `status` | ENUM | DEFAULT 'in_progress' | Status: in_progress, saved, submitted, graded, reviewed, pending_review |
| `is_draft` | BOOLEAN | DEFAULT TRUE | Draft flag |
| `is_final` | BOOLEAN | DEFAULT FALSE | Final submission |
| `is_flagged` | BOOLEAN | DEFAULT FALSE | Flagged for review |
| `flagged_reason` | TEXT | NULL | Flag reason |
| `needs_review` | BOOLEAN | DEFAULT FALSE | Needs review |
| `review_comments` | TEXT | NULL | Review comments |
| `source` | ENUM | DEFAULT 'web' | Source: web, mobile, api, import |
| `session_id` | VARCHAR(100) | NULL | Session ID |
| `client_timezone` | VARCHAR(50) | NULL | Client timezone |
| `grading_status` | ENUM | DEFAULT 'pending' | Status: pending, auto_graded, manually_graded, partially_graded, regaded |
| `auto_grade_eligible` | BOOLEAN | DEFAULT TRUE | Auto-gradable |
| `time_spent_seconds` | INT | NULL | Time spent |
| `first_save_at` | TIMESTAMP | NULL | First save time |
| `last_save_at` | TIMESTAMP | NULL | Last save time |
| `save_count` | INT | DEFAULT 0 | Save count |
| `submitted_at` | TIMESTAMP | NULL | Submission time |
| `started_at` | TIMESTAMP | NULL | Started time |
| `ip_address` | VARCHAR(45) | NULL | IP address |
| `browser_info` | JSON | NULL | Browser info |
| `version_number` | INT | DEFAULT 1 | Answer version |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(exam_id, attempt_id, question_id)` - Each question answered once per attempt

**Indexes:**
- `exam_id`
- `student_id`
- `question_id`
- `status`
- `is_graded`
- `is_flagged`

---

### 3.7 [`exam_access`](supabase/migrations/yujbjplf_lms.sql:792) Table

Tracks student access/purchase status for exams.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Access ID |
| `exam_id` | INT | FK → exams(id), NOT NULL | Exam reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `payment_status` | ENUM | DEFAULT 'free' | Status: pending, paid, free |
| `payment_amount` | DECIMAL(10,2) | DEFAULT 0.00 | Amount paid |
| `currency` | VARCHAR(3) | DEFAULT 'USD' | Currency |
| `access_granted_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Access granted |
| `expires_at` | TIMESTAMP | NULL | Expiration |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(exam_id, student_id)`

---

### 3.8 [`exam_reviews`](supabase/migrations/yujbjplf_lms.sql:917) Table

Student reviews for exams.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Review ID |
| `exam_id` | INT | FK → exams(id), NOT NULL | Exam reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `rating` | DECIMAL(3,2) | NOT NULL | Rating 0-5 |
| `review_text` | TEXT | NULL | Review content |
| `status` | ENUM | DEFAULT 'pending' | Status: pending, approved, rejected |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(exam_id, student_id)`

---

## 4. Payments & Transactions

### 4.1 [`payment_gateway_settings`](supabase/migrations/yujbjplf_lms.sql:993) Table

Configuration for payment gateways.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `gateway_name` | ENUM | NOT NULL | Gateway: paynow, paypal |
| `gateway_type` | ENUM | NOT NULL, DEFAULT 'sandbox' | Type: sandbox, live |
| `api_key` | VARCHAR(255) | NULL | API key |
| `api_secret` | VARCHAR(255) | NULL | API secret |
| `merchant_id` | VARCHAR(255) | NULL | Merchant ID |
| `merchant_email` | VARCHAR(255) | NULL | Merchant email |
| `webhook_url` | VARCHAR(500) | NULL | Webhook URL |
| `return_url` | VARCHAR(500) | NULL | Return URL |
| `cancel_url` | VARCHAR(500) | NULL | Cancel URL |
| `additional_settings` | JSON | NULL | Extra settings |
| `is_active` | BOOLEAN | DEFAULT FALSE | Active status |
| `test_mode` | BOOLEAN | DEFAULT TRUE | Test mode |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(gateway_name, gateway_type)`

---

### 4.2 [`payment_transactions`](supabase/migrations/yujbjplf_lms.sql:1068) Table

Records all payment transactions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Transaction ID |
| `transaction_id` | VARCHAR(100) | UNIQUE, NOT NULL | External transaction ID |
| `reference_number` | VARCHAR(100) | UNIQUE, NOT NULL | Internal reference |
| `gateway_name` | VARCHAR(50) | NOT NULL | Gateway name |
| `gateway_type` | VARCHAR(20) | NOT NULL | Gateway type |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `item_type` | ENUM | NOT NULL, DEFAULT 'course' | Type: course, exam, cart |
| `item_id` | INT | NOT NULL | Item reference |
| `amount` | DECIMAL(10,2) | NOT NULL | Amount |
| `currency` | VARCHAR(3) | NOT NULL, DEFAULT 'USD' | Currency |
| `discount_code` | VARCHAR(50) | NULL | Discount code used |
| `discount_amount` | DECIMAL(10,2) | DEFAULT 0.00 | Discount applied |
| `final_amount` | DECIMAL(10,2) | NOT NULL | Final amount |
| `status` | ENUM | DEFAULT 'pending' | Status: pending, processing, completed, failed, cancelled, refunded |
| `gateway_response` | JSON | NULL | Gateway response |
| `gateway_metadata` | JSON | NULL | Extra metadata |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `processed_at` | TIMESTAMP | NULL | Processed time |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `user_id`
- `status`
- `reference_number`
- `created_at`

---

### 4.3 [`payment_logs`](supabase/migrations/yujbjplf_lms.sql:1028) Table

Detailed logs of payment operations.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Log ID |
| `transaction_id` | INT | FK → payment_transactions(id), NULL | Transaction reference |
| `action` | VARCHAR(100) | NOT NULL | Action performed |
| `level` | ENUM | DEFAULT 'info' | Level: info, warning, error |
| `message` | TEXT | NULL | Log message |
| `context` | JSON | NULL | Context data |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

**Indexes:**
- `action`
- `level`
- `created_at`

---

### 4.4 [`payment_analytics`](supabase/migrations/yujbjplf_lms.sql:972) Table

Daily aggregated payment analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `date` | DATE | NOT NULL | Date |
| `gateway_name` | VARCHAR(50) | NOT NULL | Gateway name |
| `currency` | VARCHAR(3) | NOT NULL | Currency |
| `total_transactions` | INT | DEFAULT 0 | Total count |
| `successful_transactions` | INT | DEFAULT 0 | Success count |
| `failed_transactions` | INT | DEFAULT 0 | Failure count |
| `total_amount` | DECIMAL(15,2) | DEFAULT 0.00 | Total amount |
| `refunded_amount` | DECIMAL(15,2) | DEFAULT 0.00 | Refunded amount |
| `net_amount` | DECIMAL(15,2) | DEFAULT 0.00 | Net amount |
| `average_transaction` | DECIMAL(10,2) | DEFAULT 0.00 | Average amount |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(date, gateway_name, currency)`

---

### 4.5 [`payment_refunds`](supabase/migrations/yujbjplf_lms.sql:1047) Table

Records refund requests and processing.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Refund ID |
| `transaction_id` | INT | FK → payment_transactions(id), NOT NULL | Transaction reference |
| `refund_amount` | DECIMAL(10,2) | NOT NULL | Refund amount |
| `reason` | TEXT | NULL | Refund reason |
| `status` | ENUM | DEFAULT 'pending' | Status: pending, completed, failed |
| `gateway_refund_id` | VARCHAR(255) | NULL | Gateway refund ID |
| `gateway_response` | JSON | NULL | Gateway response |
| `processed_by` | INT | FK → users(id), NULL | Processed by |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `processed_at` | TIMESTAMP | NULL | Processed time |

**Indexes:**
- `transaction_id`
- `status`

---

### 4.6 [`discount_codes`](supabase/migrations/yujbjplf_lms.sql:710) Table

Promotional discount codes.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `code` | VARCHAR(50) | UNIQUE, NOT NULL | Discount code |
| `description` | TEXT | NULL | Description |
| `discount_type` | ENUM | NOT NULL | Type: percentage, fixed |
| `discount_value` | DECIMAL(10,2) | NOT NULL | Discount value |
| `max_discount_amount` | DECIMAL(10,2) | NULL | Maximum discount |
| `min_purchase_amount` | DECIMAL(10,2) | DEFAULT 0.00 | Minimum purchase |
| `usage_limit` | INT | NULL | Max uses |
| `usage_count` | INT | DEFAULT 0 | Current uses |
| `valid_from` | DATETIME | NULL | Start date |
| `valid_until` | DATETIME | NULL | End date |
| `is_active` | BOOLEAN | DEFAULT TRUE | Active status |
| `created_by` | INT | FK → users(id), NULL | Creator |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `code` (UNIQUE)
- `is_active`
- `valid_from`, `valid_until`

---

### 4.7 [`cart_items`](supabase/migrations/yujbjplf_lms.sql:250) Table

User shopping cart items.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Item ID |
| `user_id` | INT | FK → users(id), NOT NULL | User reference |
| `item_type` | ENUM | NOT NULL, DEFAULT 'course' | Type: course, exam |
| `item_id` | INT | NOT NULL | Item reference |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Unique:** `(user_id, item_type, item_id)`

---

## 5. Content Management

### 5.1 [`posts`](supabase/migrations/yujbjplf_lms.sql:1110) Table

Blog posts and news articles.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Post ID |
| `title` | VARCHAR(255) | NOT NULL | Post title |
| `slug` | VARCHAR(255) | UNIQUE, NOT NULL | URL slug |
| `content` | LONGTEXT | NOT NULL | Post content |
| `excerpt` | TEXT | NULL | Short excerpt |
| `featured_image` | VARCHAR(255) | NULL | Featured image URL |
| `category_id` | INT | FK → categories(id), NULL | Category |
| `author_id` | INT | FK → users(id), NULL | Author |
| `manual_author_name` | VARCHAR(100) | NULL | Manual author name |
| `status` | ENUM | DEFAULT 'draft' | Status: draft, published, archived |
| `visibility` | ENUM | DEFAULT 'public' | Visibility: public, private, password |
| `password` | VARCHAR(255) | NULL | Password for protected |
| `comment_count` | INT | DEFAULT 0 | Comment count |
| `view_count` | INT | DEFAULT 0 | View count |
| `meta_title` | VARCHAR(255) | NULL | SEO title |
| `meta_description` | TEXT | NULL | SEO description |
| `published_at` | DATETIME | NULL | Publish date |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `slug` (UNIQUE)
- `status`
- `category_id`
- `published_at`

---

### 5.2 [`categories`](supabase/migrations/yujbjplf_lms.sql:274) Table

Post categories.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Category ID |
| `name` | VARCHAR(100) | NOT NULL | Category name |
| `slug` | VARCHAR(100) | UNIQUE, NOT NULL | URL slug |
| `description` | TEXT | NULL | Description |
| `parent_id` | INT | FK → categories(id), NULL | Parent category |
| `sort_order` | INT | DEFAULT 0 | Display order |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `slug` (UNIQUE)
- `parent_id`

---

### 5.3 [`announcements`](supabase/migrations/yujbjplf_lms.sql:181) Table

System announcements.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `title` | VARCHAR(255) | NOT NULL | Title |
| `content` | TEXT | NOT NULL | Content |
| `type` | ENUM | DEFAULT 'info' | Type: info, warning, success, error |
| `status` | ENUM | DEFAULT 'inactive' | Status: active, inactive |
| `show_on_home` | BOOLEAN | DEFAULT TRUE | Show on home page |
| `created_by` | INT | FK → users(id), NULL | Creator |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 5.4 [`calendar_events`](supabase/migrations/yujbjplf_lms.sql:225) Table

Calendar events.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Event ID |
| `title` | VARCHAR(255) | NOT NULL | Event title |
| `description` | TEXT | NULL | Description |
| `event_date` | DATE | NOT NULL | Event date |
| `event_time` | TIME | DEFAULT '09:00' | Event time |
| `creator_id` | INT | FK → users(id), NULL | Creator |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

**Indexes:**
- `event_date`
- `creator_id`

---

### 5.5 [`assignment_submissions`](supabase/migrations/yujbjplf_lms.sql:199) Table

Assignment submissions (for lesson-type assignments).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Submission ID |
| `assignment_id` | INT | NOT NULL | Assignment reference |
| `student_id` | INT | FK → users(id), NOT NULL | Student reference |
| `content` | LONGTEXT | NULL | Submission content |
| `file_url` | VARCHAR(500) | NULL | Attached file |
| `grade` | DECIMAL(10,2) | NULL | Grade |
| `feedback` | TEXT | NULL | Instructor feedback |
| `graded_by` | INT | FK → users(id), NULL | Grader |
| `graded_at` | TIMESTAMP | NULL | Grading time |
| `status` | ENUM | DEFAULT 'submitted' | Status |
| `submitted_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Submitted at |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 5.6 [`scorm_packages`](supabase/migrations/yujbjplf_lms.sql:1389) Table

SCORM e-learning packages.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Package ID |
| `title` | VARCHAR(255) | NOT NULL | Package title |
| `description` | TEXT | NULL | Description |
| `file_url` | VARCHAR(500) | NOT NULL | SCORM ZIP URL |
| `version` | VARCHAR(20) | NULL | SCORM version |
| `lesson_id` | INT | FK → course_lessons(id), NULL | Associated lesson |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

## 6. Analytics & Tracking

### 6.1 [`course_analytics`](supabase/migrations/yujbjplf_lms.sql:372) Table

Course-level analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `course_id` | INT | NOT NULL | Course reference |
| `date` | DATE | NOT NULL | Date |
| `total_enrollments` | INT | DEFAULT 0 | New enrollments |
| `total_completions` | INT | DEFAULT 0 | Completions |
| `total_revenue` | DECIMAL(15,2) | DEFAULT 0.00 | Revenue |
| `average_rating` | DECIMAL(3,2) | NULL | Average rating |
| `total_reviews` | INT | DEFAULT 0 | Review count |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 6.2 [`content_analytics`](supabase/migrations/yujbjplf_lms.sql:302) Table

Content view and interaction analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `content_type` | VARCHAR(50) | NOT NULL | Type: lesson, course, exam |
| `content_id` | INT | NOT NULL | Content reference |
| `user_id` | INT | NOT NULL | User reference |
| `event_type` | VARCHAR(50) | NOT NULL | Event: view, complete, interact |
| `metadata` | JSON | NULL | Event metadata |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

---

### 6.3 [`question_analytics`](supabase/migrations/yujbjplf_lms.sql:1211) Table

Question-level performance analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `question_id` | INT | NOT NULL | Question reference |
| `total_attempts` | INT | DEFAULT 0 | Total attempts |
| `correct_attempts` | INT | DEFAULT 0 | Correct attempts |
| `incorrect_attempts` | INT | DEFAULT 0 | Incorrect attempts |
| `average_score` | DECIMAL(5,2) | DEFAULT 0.00 | Average score |
| `average_time_seconds` | INT | DEFAULT 0 | Avg time |
| `last_calculated` | TIMESTAMP | NULL | Last calculation |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 6.4 [`question_categories`](supabase/migrations/yujbjplf_lms.sql:1259) Table

Question categorization.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Category ID |
| `name` | VARCHAR(100) | NOT NULL | Name |
| `slug` | VARCHAR(100) | UNIQUE, NOT NULL | Slug |
| `description` | TEXT | NULL | Description |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

### 6.5 [`question_tags`](supabase/migrations/yujbjplf_lms.sql:1360) Table

Tags for questions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Tag ID |
| `name` | VARCHAR(50) | NOT NULL | Tag name |
| `slug` | VARCHAR(50) | UNIQUE, NOT NULL | Slug |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

---

### 6.6 [`question_tag_assignments`](supabase/migrations/yujbjplf_lms.sql:1377) Table

Question-tag relationships.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `question_id` | INT | NOT NULL | Question reference |
| `tag_id` | INT | NOT NULL | Tag reference |

---

### 6.7 [`question_reviews`](supabase/migrations/yujbjplf_lms.sql:1345) Table

Reviews/feedback on questions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | Review ID |
| `question_id` | INT | NOT NULL | Question reference |
| `user_id` | INT | NOT NULL | User reference |
| `review_type` | VARCHAR(50) | NOT NULL | Type: flag, feedback |
| `content` | TEXT | NOT NULL | Content |
| `status` | ENUM | DEFAULT 'pending' | Status: pending, resolved |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |

---

## 7. User Data & Profiles

### 7.1 [`site_settings`](supabase/migrations/yujbjplf_lms.sql:1412) Table

System-wide configuration settings.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | INT | PK, AUTO_INCREMENT | ID |
| `setting_key` | VARCHAR(100) | UNIQUE, NOT NULL | Setting key |
| `setting_value` | TEXT | NULL | Setting value |
| `description` | TEXT | NULL | Description |
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP | Updated at |

---

## Relationships Diagram

```
users (1) ──────< course_enrollments >────── (N) courses
users (1) ──────< course_enrollments >────── (N) course_lesson_progress
course_enrollments (1) ──────< course_lesson_progress >────── (N) course_lessons
courses (1) ──────< course_modules >────── (N) course_lessons
course_modules (1) ──────< course_topics >────── (N) course_lessons
courses (1) ──────< exams >────── (N) exam_attempts
exam_attempts (1) ──────< student_answers >────── (N) questions
questions (1) ──────< question_options >────── (N)
exams (1) ──────< exam_questions >────── (N) questions
users (1) ──────< payment_transactions >────── (N) courses/exams
users (1) ──────< cart_items >────── (N) courses/exams
users (1) ──────< user_sessions >────── (N)
users (1) ──────< user_profiles >────── (1)
users (1) ──────< user_activities >────── (N)
```

## Database Engine & Charset

- **Engine:** InnoDB
- **Charset:** utf8mb4
- **Collation:** utf8mb4_unicode_ci

---

## Notes

1. All tables use soft deletes where applicable (is_deleted, deleted_at columns)
2. Timestamps use CURRENT_TIMESTAMP with ON UPDATE for automatic updates
3. JSON columns are used for flexible data storage (tags, objectives, metadata)
4. Foreign keys use CASCADE or SET NULL on delete based on relationship type
5. Indexes are created for commonly queried columns and composite keys

---

*Document generated from database migration files and schema analysis.*
*Last updated: March 2026*