# CEPI LMS — Database Schema

> Verified from all migration files. Last updated: 2026-06-02.

---

## 1. Entity Relationship Overview

```mermaid
erDiagram
    users ||--o{ enrollments : "enrolls in"
    users ||--o{ payments : "makes"
    users ||--o{ lesson_progress : "tracks"
    users ||--o{ user_devices : "binds"
    users ||--o{ courses : "instructs"
    users ||--o{ blog_posts : "authors"
    users ||--o{ media_libraries : "uploads"
    users ||--o{ admin_logs : "performs"

    courses ||--o{ course_modules : "has"
    courses ||--o{ enrollments : "enrolled via"
    courses ||--o{ payments : "paid for"
    courses ||--o{ course_reviews : "reviewed by"
    courses }o--|| categories : "belongs to"

    course_modules ||--o{ lessons : "contains"
    lessons ||--o{ lesson_progress : "tracked by"
    lessons ||--o{ quizzes : "has"

    payments ||--o| enrollments : "triggers"
    media_libraries ||--o{ blog_posts : "featured in"
```

---

## 2. Table Reference

### `users`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| name | string | |
| email | string unique | |
| email_verified_at | timestamp nullable | |
| email_verification_code | string(6) nullable | Custom 6-digit code |
| email_verification_code_expires_at | timestamp nullable | 2-day expiry |
| password | string | bcrypt hashed |
| phone | string nullable | |
| gender | string nullable | |
| user_type | string default 'student' | `student` / `instructor` / `admin` |
| source_type | string(50) default 'form' | `form` / `google` |
| avatar | string nullable | |
| bio | text nullable | |
| social_links | json nullable | |
| status | string default 'active' | `active` / `inactive` |
| last_login_at | timestamp nullable | |
| remember_token | string | |
| created_at / updated_at | timestamps | |
| deleted_at | timestamp nullable | Soft delete |

### `courses`
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| title | string | |
| slug | string unique | |
| description | text nullable | |
| thumbnail | string nullable | Media path |
| promo_video_url | string nullable | |
| category_id | bigint FK → categories nullable | |
| instructor_id | bigint FK → users | |
| price | decimal(10,2) default 0 | |
| discount_price | decimal(10,2) nullable | |
| currency | string default 'PKR' | |
| is_free | boolean default false | |
| status | string default 'draft' | `draft` / `published` / `archived` / `pending_review` |
| duration_weeks | int nullable | |
| level | string default 'beginner' | `beginner` / `intermediate` / `advanced` |
| language | string default 'english' | |
| total_duration | int unsigned default 0 | Seconds, recalculated |
| total_lectures | int unsigned default 0 | Recalculated |
| rating | decimal(3,2) default 0 | Recalculated |
| total_students | int unsigned default 0 | Recalculated |
| published_at | timestamp nullable | |
| created_at / updated_at | timestamps | |
| deleted_at | timestamp nullable | Soft delete |

### `course_modules`
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| course_id | uuid FK → courses cascade | |
| title | string | |
| description | text nullable | |
| order_index | int default 0 | Sort order |
| created_at / updated_at | timestamps | |

### `lessons`
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| module_id | uuid FK → course_modules cascade | |
| title | string | |
| description | text nullable | |
| article_content | longtext nullable | For text-type lessons |
| content_type | string default 'video' | `video` / `pdf` / `text` |
| bunny_video_id | string nullable | Bunny Stream UUID |
| video_url | string nullable | External video URL |
| video_provider | string nullable | |
| video_length | int unsigned nullable | Seconds |
| resolution | string nullable | |
| subtitles | json nullable | |
| thumbnail | string nullable | |
| quiz_data | json nullable | Inline quiz data |
| file_path | string nullable | For PDF lessons |
| duration_seconds | int default 0 | |
| order_index | int default 0 | |
| is_preview | boolean default false | Free preview lesson |
| created_at / updated_at | timestamps | |

### `enrollments`
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| user_id | bigint FK → users cascade | |
| course_id | uuid FK → courses cascade | |
| payment_id | bigint FK → payments nullable | Null for free courses |
| enrolled_at | timestamp useCurrent | |
| expires_at | timestamp nullable | Null = lifetime |
| status | string default 'active' | `active` / `expired` / `cancelled` |
| progress_percent | decimal(5,2) default 0 | Recalculated on progress |
| created_at / updated_at | timestamps | |

### `lesson_progress`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| user_id | bigint FK → users cascade | |
| lesson_id | uuid FK → lessons cascade | |
| watched_seconds | int default 0 | Monotonic max |
| last_position_seconds | int unsigned default 0 | Resume position |
| completed | boolean default false | |
| watch_count | int unsigned default 0 | |
| last_accessed_at | timestamp nullable | |
| last_watched_at | timestamp nullable | |
| completed_at | timestamp nullable | |
| created_at / updated_at | timestamps | |
| UNIQUE | (user_id, lesson_id) | One record per student per lesson |

### `payments`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| user_id | bigint FK → users cascade | |
| course_id | uuid FK → courses cascade | |
| gateway | string | `stripe` / `jazzcash` / `bank_transfer` |
| gateway_transaction_id | string nullable | |
| transfer_reference | string nullable | Bank transfer ref |
| payer_name | string nullable | |
| payer_phone | string nullable | |
| transfer_notes | text nullable | |
| amount | decimal(10,2) | |
| currency | string default 'PKR' | |
| status | string default 'pending' | `pending` / `completed` / `failed` / `refunded` |
| paid_at | timestamp nullable | |
| submitted_at | timestamp nullable | When student submitted |
| reviewed_at | timestamp nullable | When admin reviewed |
| reviewed_by | bigint FK → users nullable | Admin who reviewed |
| admin_notes | text nullable | |
| created_at / updated_at | timestamps | |

### `user_devices`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| user_id | bigint FK → users cascade | |
| device_fingerprint | string | SHA256 of UA + accept-language |
| trusted_token_hash | string nullable | HMAC-SHA256 of cookie token |
| device_name | string nullable | Human readable browser name |
| ip_address | string nullable | |
| user_agent | text nullable | |
| bound_at | timestamp nullable | When device was first bound |
| acknowledged_at | timestamp nullable | When user acknowledged policy |
| cooldown_until | timestamp nullable | When new device can be bound |
| replaced_at | timestamp nullable | When this device was superseded |
| last_seen_at | timestamp nullable | |
| is_blocked | boolean default false | |
| created_at / updated_at | timestamps | |

### `categories`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| name | string | |
| slug | string unique | |
| description | text nullable | |
| parent_id | bigint FK → categories nullable | Self-referential hierarchy |
| created_at / updated_at | timestamps | |

### `media_libraries`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| uploaded_by | bigint FK → users nullable | |
| title | string nullable | |
| alt_text | string nullable | |
| caption | text nullable | |
| original_name | string | |
| file_name | string | |
| disk | string default 'public' | |
| directory | string nullable | |
| path | string | |
| mime_type | string(150) | |
| extension | string(20) nullable | |
| media_type | string(20) default 'file' | `image` / `file` / `video` |
| size_bytes | bigint unsigned default 0 | |
| width / height | int unsigned nullable | Images only |
| variants | json nullable | Thumbnail variants |
| created_at / updated_at | timestamps | |
| deleted_at | timestamp nullable | Soft delete |
| INDEX | (media_type, created_at) | |

### `blog_posts`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| author_id | bigint FK → users nullable | |
| featured_image_id | bigint FK → media_libraries nullable | |
| title | string | |
| slug | string unique | |
| excerpt | text nullable | |
| detail | longtext | |
| seo_title / seo_keywords / seo_description | string / text nullable | |
| status | string default 'draft' | `draft` / `published` |
| published_at | timestamp nullable | |
| created_at / updated_at | timestamps | |
| deleted_at | timestamp nullable | Soft delete |
| INDEX | (status, published_at) | |

### `settings`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| key | string unique | |
| value | longtext nullable | |
| group | string default 'general' | |
| created_at / updated_at | timestamps | |

Used for bank account details, site-wide config. Accessed via `Setting::value('key', 'default')`.

### `admin_logs`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| admin_id | bigint FK → users cascade | |
| action | string | `created` / `updated` / `deleted` / `deactivated` |
| target_type | string | e.g., `User`, `Course` |
| target_id | string | UUID or integer as string |
| description | text nullable | |
| ip | string nullable | |
| created_at / updated_at | timestamps | |

### `contact_messages`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| name | string | |
| email | string | |
| mobile | string(30) | |
| interested_in | string nullable | |
| message | text | |
| created_at / updated_at | timestamps | |

### `course_reviews`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| user_id | bigint FK → users cascade | |
| course_id | uuid FK → courses cascade | |
| rating | tinyint unsigned | 1–5 |
| review | text nullable | |
| created_at / updated_at | timestamps | |
| UNIQUE | (user_id, course_id) | One review per student per course |

### `quizzes`
| Column | Type | Notes |
|---|---|---|
| id | bigint PK | |
| lesson_id | uuid FK → lessons cascade | |
| question | text | |
| type | string default 'mcq' | |
| correct_answer | text | |
| options | json nullable | MCQ options array |
| created_at / updated_at | timestamps | |

### Spatie Permission Tables
- `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions`
- Standard Spatie structure, no teams mode

---

## 3. Key Indexes

| Table | Indexed Columns | Type |
|---|---|---|
| users | email | unique |
| courses | slug | unique |
| course_modules | (course_id, order_index) | implicit via FK |
| lessons | (module_id, order_index) | implicit via FK |
| lesson_progress | (user_id, lesson_id) | unique |
| media_libraries | (media_type, created_at) | index |
| blog_posts | (status, published_at) | index |

---

## 4. Schema Gaps and Recommendations

| Issue | Table | Recommendation |
|---|---|---|
| No composite index on enrollments | enrollments | Add index on `(user_id, course_id, status)` |
| No index on lesson_progress.user_id alone | lesson_progress | Add index for dashboard queries |
| No index on payments.user_id / status | payments | Add for student payment history queries |
| No unique constraint on enrollments | enrollments | Add unique on `(user_id, course_id)` to prevent double enrollment |
| `user_type` and Spatie roles can drift | users | Consider a DB trigger or observer to keep them in sync |
| `courses.status` is a plain string | courses | Consider MySQL ENUM for constraint |
| No soft delete on enrollments | enrollments | Low risk now, but useful for audit trail |
| `contact_messages` has no read/status field | contact_messages | Add `is_read` boolean for admin inbox |

---

## 5. Mobile App Data Requirements

The following tables/columns need to be exposed via API for Flutter:
- `courses`: full course detail including module/lesson tree
- `enrollments`: active status + progress_percent
- `lesson_progress`: resume position (`last_position_seconds`), completion status
- `lessons`: `bunny_video_id` (for signed URL generation server-side)
- `payments`: pending status for student payment history
- `users`: profile data, avatar