Data Model Diagram

This document presents the EduNest LMS data model at the field level: the key domain tables drawn directly from the CREATE TABLE / ALTER TABLE statements in src/lib/lms-schema.ts and src/lib/vendor-schema.ts. EduNest stores "soft enums" as plain VARCHAR columns with documented allowed values (so they can be extended without a migration), money as integer cents, and arrays/objects as JSONB.

Course

courses — src/lib/lms-schema.ts.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`slug`	`VARCHAR(255)`	—	Unique URL slug
`title`	`VARCHAR(255)`	—	Course title
`subtitle`	`VARCHAR(500)`	`''`	Short tagline
`description`	`TEXT`	`''`	Full description
`instructor_id`	`INT`	null	FK → `instructors` (`ON DELETE SET NULL`)
`category_id`	`INT`	null	FK → `course_categories` (`ON DELETE SET NULL`)
`thumbnail` / `cover_image` / `promo_video`	`VARCHAR(500)`	`''`	Media assets
`level`	`VARCHAR(20)`	`'all'`	Difficulty (e.g. all, beginner, …)
`language`	`VARCHAR(50)`	`''`	Course language
`price_cents`	`INT`	`0`	Base price (cents)
`sale_price_cents`	`INT`	null	Discounted price (cents)
`currency`	`VARCHAR(5)`	`'INR'`	ISO currency code
`status`	`VARCHAR(20)`	`'draft'`	`draft` · `pending` · `published` · `rejected` · `archived`
`rejection_reason`	`TEXT`	`''`	Set when rejected by admin
`is_featured`	`BOOLEAN`	`false`	Featured flag
`drip_enabled`	`BOOLEAN`	`false`	Whether lessons drip-release
`requirements` / `outcomes` / `tags` / `target_audience`	`JSONB`	`[]`	List fields
`certificate_enabled`	`BOOLEAN`	`false`	Issue a certificate on completion
`duration_minutes`	`INT`	`0`	Total runtime
`rating_avg`	`NUMERIC`	`0`	Average review rating
`rating_count`	`INT`	`0`	Number of reviews
`enrollment_count`	`INT`	`0`	Number of enrollments
`meta_title` / `meta_description`	`VARCHAR`	`''`	SEO
`published_at`	`TIMESTAMPTZ`	null	Publish timestamp
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Lesson

lessons — src/lib/lms-schema.ts.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`section_id`	`INT`	—	FK → `sections` (`ON DELETE CASCADE`)
`course_id`	`INT`	—	FK → `courses` (`ON DELETE CASCADE`)
`title`	`VARCHAR(255)`	—	Lesson title
`type`	`VARCHAR(30)`	`'video'`	Content type (video, audio, text, embed, quiz, …)
`content_url`	`VARCHAR(500)`	`''`	Media/source URL
`content`	`TEXT`	`''`	Inline text content
`description`	`TEXT`	`''`	Short description
`data`	`JSONB`	`{}`	Type-specific fields (provider, embed html, attachments, linked quiz/survey/task ids…)
`duration_seconds`	`INT`	`0`	Length in seconds
`is_preview`	`BOOLEAN`	`false`	Free preview before purchase
`drip_type`	`VARCHAR(20)`	`'immediate'`	`immediate` · `days_after_enroll` · `date` · `after_prev`
`drip_days`	`INT`	`0`	Days offset when `drip_type='days_after_enroll'`
`drip_date`	`DATE`	null	Fixed release date when `drip_type='date'`
`sort_order`	`INT`	`0`	Ordering within the section

Enrollment

enrollments — src/lib/lms-schema.ts. Unique on (customer_id, course_id).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`customer_id`	`INT`	—	FK → `customers` (`ON DELETE CASCADE`)
`course_id`	`INT`	—	FK → `courses` (`ON DELETE CASCADE`)
`source`	`VARCHAR(20)`	`'purchase'`	`purchase` · `bundle` · `manual` · `free`
`transaction_id`	`INT`	null	The originating purchase, if any
`status`	`VARCHAR(20)`	`'active'`	Enrollment state
`progress_pct`	`NUMERIC`	`0`	Completion percentage
`completed_at`	`TIMESTAMPTZ`	null	When the course was completed
`enrolled_at`	`TIMESTAMPTZ`	`NOW()`	Enrollment timestamp

Companion: lesson_progress (unique on (enrollment_id, lesson_id)) holds is_completed, last_position_seconds, and completed_at per lesson.

Transaction (Order)

transactions — src/lib/lms-schema.ts. This is the course/bundle order record.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`customer_id`	`INT`	—	FK → `customers` (`ON DELETE CASCADE`)
`amount_cents`	`INT`	`0`	Total charged (cents)
`subtotal_cents`	`INT`	null	Subtotal before discount (cents)
`discount_cents`	`INT`	`0`	Discount applied (cents)
`tax_cents`	`INT`	`0`	Tax (cents)
`currency`	`VARCHAR(5)`	`'INR'`	ISO currency code
`gateway`	`VARCHAR(40)`	`''`	Payment gateway used
`gateway_ref`	`VARCHAR(255)`	`''`	Gateway reference/txn id
`status`	`VARCHAR(20)`	`'pending'`	`pending` · `paid` · `failed` · `refunded`
`coupon_code`	`VARCHAR(60)`	`''`	Applied coupon, if any
`items`	`JSONB`	`[]`	Line items (course/bundle ids, prices)
`created_at`	`TIMESTAMPTZ`	`NOW()`	Created
`paid_at`	`TIMESTAMPTZ`	null	Paid timestamp

Revenue split is recorded in instructor_earnings (gross_cents, commission_pct, net_cents, status of pending/available/paid) and disbursed via payouts (amount_cents, method, status of requested/approved/paid/rejected).

Customer (Learner / Instructor account)

customers — src/lib/vendor-schema.ts. One row per portal account; account_type distinguishes a learner from an instructor.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`slug`	`VARCHAR(255)`	—	Unique public slug
`name`	`VARCHAR(255)`	—	Display name
`email`	`VARCHAR(255)`	`''`	Email address
`email_verified`	`BOOLEAN`	`false`	Email verified flag
`phone` / `phone_code`	`VARCHAR`	`''`	Phone number
`phone_verified`	`BOOLEAN`	`false`	Phone verified flag
`password_hash`	`VARCHAR(255)`	`''`	bcrypt password hash
`otp_code`	`VARCHAR(255)`	`''`	bcrypt-hashed OTP
`otp_expires_at`	`TIMESTAMPTZ`	null	OTP expiry
`otp_purpose`	`VARCHAR(20)`	`''`	OTP context (verify/reset)
`account_type`	`VARCHAR(20)`	`'student'`	`student` · `instructor`
`avatar`	`VARCHAR(500)`	`''`	Avatar URL
`bio` / `occupation` / `company` / `website`	text	`''`	Public profile fields
`twitter` / `linkedin` / `github` / `instagram`	`VARCHAR(255)`	`''`	Social links
`notification_prefs`	`JSONB`	`{}`	Notification preferences
`is_blacklisted`	`BOOLEAN`	`false`	Block flag
`is_active`	`BOOLEAN`	`true`	Active flag
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

The instructor profile lives in instructors (1:1 via customer_id UNIQUE): headline, bio, expertise/social (JSONB), status (pending/approved/rejected/suspended), revenue_share_pct (default 70), payout_method, payout_details (JSONB), approved_at, seq.

Coupon

coupons — src/lib/vendor-schema.ts.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`code`	`VARCHAR(60)`	—	Unique coupon code
`description`	`TEXT`	`''`	Internal note
`discount_type`	`VARCHAR(10)`	`'PERCENT'`	`PERCENT` or fixed amount
`discount_value`	`NUMERIC`	`0`	Percentage or amount
`min_total_cents`	`INT`	`0`	Minimum order to qualify (cents)
`max_discount_cents`	`INT`	`0`	Cap on the discount (cents)
`max_redemptions`	`INT`	`0`	Global redemption limit (0 = unlimited)
`max_per_customer`	`INT`	`0`	Per-customer limit
`redemptions`	`INT`	`0`	Times used
`applies_to`	`VARCHAR(20)`	`'ALL'`	`ALL` · `PRODUCTS` · `CATEGORIES` · `COURSES`
`course_ids` / `product_ids` / `category_ids`	`JSONB`	`[]`	Scope target ids
`min_licenses` / `max_licenses`	`INT`	`0`	Enterprise seat range
`is_default`	`BOOLEAN`	`false`	Auto-applied default coupon
`valid_from` / `valid_until`	`DATE`	null	Validity window
`is_active`	`BOOLEAN`	`true`	Active flag

Bundle

bundles + bundle_courses — src/lib/lms-schema.ts.

bundles

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`slug`	`VARCHAR(255)`	—	Unique URL slug
`title`	`VARCHAR(255)`	—	Bundle title
`subtitle`	`VARCHAR(500)`	`''`	Tagline
`description`	`TEXT`	`''`	Description
`thumbnail`	`VARCHAR(500)`	`''`	Image
`instructor_id`	`INT`	null	FK → `instructors` (null = admin/platform bundle)
`price_cents`	`INT`	`0`	Bundle price (cents)
`sale_price_cents`	`INT`	null	Discounted price (cents)
`currency`	`VARCHAR(5)`	`'INR'`	Currency
`is_featured`	`BOOLEAN`	`false`	Featured flag
`status`	`VARCHAR(20)`	`'draft'`	Lifecycle status

bundle_courses (join, composite PK (bundle_id, course_id))

Column	Type	Default	Meaning
`bundle_id`	`INT`	—	FK → `bundles` (`ON DELETE CASCADE`)
`course_id`	`INT`	—	FK → `courses` (`ON DELETE CASCADE`)
`sort_order`	`INT`	`0`	Ordering within the bundle
`drip_type`	`VARCHAR(20)`	`'immediate'`	`immediate` · `days_after_purchase` · `after_previous`
`drip_days`	`INT`	`0`	Days offset for drip release

Soft Enums

Enumerations are stored as VARCHAR with documented allowed values:

Field	Table	Allowed values
`status`	`courses`	`draft` · `pending` · `published` · `rejected` · `archived`
`status`	`instructors`	`pending` · `approved` · `rejected` · `suspended`
`source`	`enrollments`	`purchase` · `bundle` · `manual` · `free`
`status`	`transactions`	`pending` · `paid` · `failed` · `refunded`
`status`	`instructor_earnings`	`pending` · `available` · `paid` (reversed on refund)
`status`	`payouts`	`requested` · `approved` · `paid` · `rejected`
`drip_type`	`lessons`	`immediate` · `days_after_enroll` · `date` · `after_prev`
`drip_type`	`bundle_courses`	`immediate` · `days_after_purchase` · `after_previous`
`type`	`lessons`	`video` · `audio` · `text` · `embed` · `quiz` · …
`type`	`quiz_questions`	`single` · `multiple` · `true_false` · `fill`
`type`	`survey_questions`	`star` · `single_choice` · `multiple_choice` · `short_answer` · `long_answer` · `yes_no` · `nps`
`discount_type`	`coupons`	`PERCENT` · fixed amount
`applies_to`	`coupons`	`ALL` · `PRODUCTS` · `CATEGORIES` · `COURSES`
`account_type`	`customers`	`student` · `instructor`
`sender_role`	`messages`	`learner` · `instructor`
`billing_cycle`	`pricing_plans`	`monthly` · `yearly` · `one_time` · `lifetime`

These values are read from the actual CREATE TABLE / ALTER TABLE comments in the schema source; because they are plain strings, admins and developers can extend them without altering the column type.