Entity Relationship Diagram

This document describes the EduNest LMS entity-relationship model. It focuses on the core LMS and commerce entities and the foreign keys you can verify directly in src/lib/lms-schema.ts and src/lib/vendor-schema.ts. All keys are SERIAL integers; relationships are enforced with PostgreSQL REFERENCES constraints.

Mermaid ERD

MERMAID

erDiagram
    customers ||--o| instructors : "is (account_type='instructor')"
    customers ||--o{ enrollments : "enrolls"
    customers ||--o{ transactions : "pays"
    customers ||--o{ wishlist : "saves"
    customers ||--o{ course_reviews : "writes"
    customers ||--o{ quiz_attempts : "attempts"
    customers ||--o{ conversations : "chats (learner)"

    instructors ||--o{ courses : "authors"
    instructors ||--o{ bundles : "authors"
    instructors ||--o{ instructor_earnings : "earns"
    instructors ||--o{ payouts : "withdraws"
    instructors ||--o{ conversations : "chats (instructor)"

    course_categories ||--o{ courses : "classifies"
    course_categories ||--o{ course_categories : "parent_of"

    courses ||--o{ sections : "has"
    sections ||--o{ lessons : "has"
    courses ||--o{ lessons : "has (course_id)"
    courses ||--o{ enrollments : "grants"
    courses ||--o{ course_reviews : "receives"
    courses ||--o{ quizzes : "includes"
    courses ||--o{ surveys : "includes"
    courses ||--o{ wishlist : "wished"

    bundles ||--o{ bundle_courses : "groups"
    courses ||--o{ bundle_courses : "in"

    enrollments ||--o{ lesson_progress : "tracks"
    lessons ||--o{ lesson_progress : "progress_for"

    quizzes ||--o{ quiz_questions : "has"
    quizzes ||--o{ quiz_attempts : "attempted_as"

    surveys ||--o{ survey_questions : "has"
    surveys ||--o{ survey_responses : "collects"

    todo_tasks ||--o{ todo_task_items : "has"

    transactions ||--o{ instructor_earnings : "splits"
    instructor_earnings }o--|| payouts : "paid_via"

    conversations ||--o{ messages : "contains"

    pricing_plans ||--o{ plan_change_requests : "from/to"

    blog_categories ||--o{ blogs : "classifies"

    customers {
        int id PK
        string slug UK
        string email
        string password_hash
        string account_type "student|instructor"
    }
    instructors {
        int id PK
        int customer_id FK "UNIQUE"
        string status "pending|approved|rejected|suspended"
        numeric revenue_share_pct
    }
    courses {
        int id PK
        string slug UK
        int instructor_id FK
        int category_id FK
        int price_cents
        string status "draft|pending|published|rejected|archived"
    }
    sections {
        int id PK
        int course_id FK
        int sort_order
    }
    lessons {
        int id PK
        int section_id FK
        int course_id FK
        string type
        string drip_type
    }
    bundles {
        int id PK
        string slug UK
        int instructor_id FK
        int price_cents
    }
    bundle_courses {
        int bundle_id FK
        int course_id FK
    }
    enrollments {
        int id PK
        int customer_id FK
        int course_id FK
        string source
        numeric progress_pct
    }
    lesson_progress {
        int id PK
        int enrollment_id FK
        int lesson_id FK
        boolean is_completed
    }
    transactions {
        int id PK
        int customer_id FK
        int amount_cents
        string status "pending|paid|failed|refunded"
    }
    instructor_earnings {
        int id PK
        int instructor_id FK
        int transaction_id FK
        int course_id FK
        int net_cents
        string status "pending|available|paid"
    }
    payouts {
        int id PK
        int instructor_id FK
        int amount_cents
        string status "requested|approved|paid|rejected"
    }
    coupons {
        int id PK
        string code UK
        string applies_to "ALL|PRODUCTS|CATEGORIES|COURSES"
    }
    course_reviews {
        int id PK
        int course_id FK
        int customer_id FK
        int rating
    }
    conversations {
        int id PK
        int learner_id FK
        int instructor_id FK
    }
    messages {
        int id PK
        int conversation_id FK
        string sender_role "learner|instructor"
    }
    blogs {
        int id PK
        string slug UK
        int category_id FK
    }

Entities

Entity	Key fields	Role
customers	`id`, `slug` (UQ), `account_type`	Learner or instructor account; central identity for the portal
instructors	`id`, `customer_id` (UQ FK)	Instructor profile (1:1 with a customer); approval + payout settings
course_categories	`id`, `slug` (UQ), `parent_id`	Self-nesting course taxonomy
courses	`id`, `slug` (UQ), `instructor_id`, `category_id`	A sellable course
sections	`id`, `course_id`	Ordered grouping of lessons
lessons	`id`, `section_id`, `course_id`	A learning item (video, text, quiz link, …)
bundles / bundle_courses	`id`, `slug` (UQ) / PK `(bundle_id, course_id)`	A package of courses
enrollments	`id`, UQ `(customer_id, course_id)`	Grants a learner access to a course
lesson_progress	`id`, UQ `(enrollment_id, lesson_id)`	Per-lesson completion + last position
transactions	`id`, `customer_id`	A course/bundle purchase (the order record)
instructor_earnings	`id`, `instructor_id`, `transaction_id`	Revenue-share ledger entry per sale
payouts	`id`, `instructor_id`	Instructor withdrawal request
coupons	`id`, `code` (UQ)	Discount code, scoped via `applies_to` + `course_ids`
course_reviews	`id`, UQ `(course_id, customer_id)`	Learner rating/review
quizzes / quiz_questions / quiz_attempts	`quiz_id`, `customer_id`	Assessment + a learner's attempts
surveys / survey_questions / survey_responses	`survey_id`, `customer_id`	Feedback collection
conversations / messages	`learner_id`, `instructor_id` / `conversation_id`	Learner↔instructor chat

Relationships

Relationship	From → To	Type	Foreign key
Customer → Instructor profile	`customers` → `instructors`	one-to-one	`instructors.customer_id` (UNIQUE)
Instructor → Courses	`instructors` → `courses`	one-to-many	`courses.instructor_id`
Category → Courses	`course_categories` → `courses`	one-to-many	`courses.category_id`
Category → Subcategory	`course_categories` → `course_categories`	one-to-many (self)	`course_categories.parent_id`
Course → Sections	`courses` → `sections`	one-to-many	`sections.course_id`
Section → Lessons	`sections` → `lessons`	one-to-many	`lessons.section_id`
Course → Lessons	`courses` → `lessons`	one-to-many	`lessons.course_id`
Bundle ↔ Courses	`bundle_courses`	many-to-many	`bundle_id`, `course_id` (composite PK)
Customer ↔ Course (access)	`enrollments`	many-to-many	`customer_id`, `course_id` (UNIQUE pair)
Enrollment → Lesson progress	`enrollments` → `lesson_progress`	one-to-many	`lesson_progress.enrollment_id`
Customer → Transactions	`customers` → `transactions`	one-to-many	`transactions.customer_id`
Transaction → Earnings	`transactions` → `instructor_earnings`	one-to-many	`instructor_earnings.transaction_id`
Instructor → Earnings	`instructors` → `instructor_earnings`	one-to-many	`instructor_earnings.instructor_id`
Instructor → Payouts	`instructors` → `payouts`	one-to-many	`payouts.instructor_id`
Quiz → Questions / Attempts	`quizzes` → `quiz_questions` / `quiz_attempts`	one-to-many	`quiz_id`
Survey → Questions / Responses	`surveys` → `survey_questions` / `survey_responses`	one-to-many	`survey_id`
Course → Reviews	`courses` → `course_reviews`	one-to-many	`course_reviews.course_id` (UQ with customer)
Conversation → Messages	`conversations` → `messages`	one-to-many	`messages.conversation_id`
Learner/Instructor → Conversation	`customers`/`instructors` → `conversations`	one-to-many (two roles)	`conversations.learner_id`, `conversations.instructor_id`
Plan → Change requests	`pricing_plans` → `plan_change_requests`	one-to-many (from/to)	`from_plan_id`, `to_plan_id`
Blog category → Blogs	`blog_categories` → `blogs`	one-to-many	`blogs.category_id`

Add-on entities (certificates, exams, assignments, forums, organizations, affiliate, wallet, …) follow the same patterns and hang off customers / courses; see their src/product/addons/<id>/schema.ts files.