Database Documentation

This document catalogs the EduNest LMS PostgreSQL schema. Every table is created and evolved in TypeScript by idempotent ensure*() functions — there is no ORM and no migration framework. Each function runs CREATE TABLE IF NOT EXISTS plus ALTER TABLE … ADD COLUMN IF NOT EXISTS for later additions, guarded so it executes once per server process and once at request time (so a fresh database never throws relation does not exist).

Conventions

No migrations: schema lives in src/lib/vendor-schema.ts, src/lib/lms-schema.ts, scripts/init-db.ts, and per-add-on src/product/addons/<id>/schema.ts.
Keys: SERIAL integer primary keys; foreign keys via REFERENCES.
Naming: snake_case columns. Admin-facing entities also get a non-sequential public_id (cuid-like) from ensurePublicId().
Money: integer cents in *_cents columns, paired with a currency column.
Flexible data: arrays/objects stored as JSONB.
Common columns: is_active, sort_order, created_at, updated_at.

Tables are grouped below by domain.

Catalog (Courses & Learning Content)

Defined in src/lib/lms-schema.ts.

Table	Purpose	Key columns
`course_categories`	Course taxonomy (self-nesting)	`id`, `slug` (UQ), `name`, `parent_id→course_categories`, `color`, `icon`, `meta_title`, `meta_description`, `sort_order`, `is_active`
`courses`	A sellable course	`id`, `slug` (UQ), `title`, `instructor_id→instructors`, `category_id→course_categories`, `price_cents`, `sale_price_cents`, `currency`, `level`, `status`, `is_featured`, `drip_enabled`, `requirements`/`outcomes`/`tags` (JSONB), `rating_avg`, `enrollment_count`, `certificate_enabled`, `published_at`
`sections`	Ordered group of lessons in a course	`id`, `course_id→courses`, `title`, `sort_order`
`lessons`	Single learning item	`id`, `section_id→sections`, `course_id→courses`, `title`, `type`, `content_url`, `content`, `data` (JSONB), `duration_seconds`, `is_preview`, `drip_type`, `drip_days`, `drip_date`, `sort_order`
`quizzes`	Assessment attached to a course/lesson	`id`, `course_id→courses`, `lesson_id→lessons`, `instructor_id→instructors`, `title`, `pass_pct`, `time_limit_min`, `attempts_allowed`, `shuffle_questions`, `show_answers`, `is_published`
`quiz_questions`	Quiz question	`id`, `quiz_id→quizzes`, `question`, `type` (single/multiple/true_false/fill), `options`/`correct` (JSONB), `correct_answer`, `points`, `sort_order`
`quiz_attempts`	A learner's quiz attempt	`id`, `quiz_id→quizzes`, `customer_id→customers`, `course_id→courses`, `score_pct`, `passed`, `answers` (JSONB), `attempt_no`
`surveys`	Feedback survey	`id`, `title`, `course_id→courses`, `instructor_id→instructors`, `is_anonymous`, `allow_multiple`, `status`
`survey_questions`	Survey question	`id`, `survey_id→surveys`, `text`, `type` (star/single_choice/nps/…), `options`/`config` (JSONB), `required`, `sort_order`
`survey_responses`	Submitted survey answers	`id`, `survey_id→surveys`, `customer_id→customers`, `answers` (JSONB)
`todo_tasks`	Task-to-do template	`id`, `title`, `course_id→courses`, `instructor_id→instructors`, `status`
`todo_task_items`	Item within a task template	`id`, `todo_task_id→todo_tasks`, `title`, `deadline_days`, `required`, `sort_order`
`course_reviews`	Learner rating/review of a course	`id`, `course_id→courses`, `customer_id→customers`, `rating`, `title`, `comment`, `instructor_reply`, `is_approved`, UQ `(course_id, customer_id)`
`bundles`	Group of courses sold together	`id`, `slug` (UQ), `title`, `instructor_id→instructors`, `price_cents`, `sale_price_cents`, `currency`, `is_featured`, `status`
`bundle_courses`	Bundle ↔ course link (with drip)	PK `(bundle_id, course_id)`, `sort_order`, `drip_type`, `drip_days`

Enrollment & Progress

Defined in src/lib/lms-schema.ts.

Table	Purpose	Key columns
`enrollments`	A learner's access to a course	`id`, `customer_id→customers`, `course_id→courses`, `source` (purchase/bundle/manual/free), `transaction_id`, `status`, `progress_pct`, `completed_at`, UQ `(customer_id, course_id)`
`lesson_progress`	Per-lesson completion + position	`id`, `enrollment_id→enrollments`, `lesson_id→lessons`, `customer_id`, `course_id`, `is_completed`, `last_position_seconds`, `completed_at`, UQ `(enrollment_id, lesson_id)`
`wishlist`	Saved courses	`id`, `customer_id→customers`, `course_id→courses`, UQ `(customer_id, course_id)`
`student_tasks`	Personal dashboard checklist	`id`, `customer_id→customers`, `text`, `done`, `position`

Commerce (Course Money)

Defined in src/lib/lms-schema.ts (LMS commerce) and src/lib/vendor-schema.ts (coupons, gift cards). Physical-goods orders also exist (see Store).

Table	Purpose	Key columns
`transactions`	A course/bundle purchase (order)	`id`, `customer_id→customers`, `amount_cents`, `subtotal_cents`, `discount_cents`, `tax_cents`, `currency`, `gateway`, `gateway_ref`, `status` (pending/paid/failed/refunded), `coupon_code`, `items` (JSONB), `paid_at`
`instructor_earnings`	Revenue-share ledger per sale	`id`, `instructor_id→instructors`, `transaction_id→transactions`, `course_id→courses`, `customer_id→customers`, `gross_cents`, `commission_pct`, `net_cents`, `status` (pending/available/paid), `available_at`, `payout_id`
`payouts`	Instructor withdrawal request	`id`, `instructor_id→instructors`, `amount_cents`, `currency`, `method`, `details` (JSONB), `status` (requested/approved/paid/rejected), `requested_at`, `processed_at`
`coupons`	Discount codes	`id`, `code` (UQ), `discount_type` (PERCENT/…), `discount_value`, `min_total_cents`, `max_discount_cents`, `max_redemptions`, `applies_to` (ALL/PRODUCTS/CATEGORIES/COURSES), `course_ids`/`product_ids`/`category_ids` (JSONB), `is_default`, `valid_from`/`valid_until`
`gift_cards`	Stored-value codes	`id`, `code` (UQ), `initial_cents`, `balance_cents`, `currency`, `status`, `issued_to_customer_id→customers`, `recipient_email`, `expires_at`
`pricing_plans`	Subscription/membership plans	`id`, `name`, `billing_cycle` (monthly/yearly/one_time/lifetime), `monthly_price_cents`, `yearly_price_cents`, `currency`, `free_trial_days`, `features`/`course_ids`/`bundle_ids`/`unlock_levels` (JSONB), `is_popular`, `status`
`plan_change_requests`	Enterprise upgrade/downgrade request	`id`, `company`, `contact_email`, `from_plan_id→pricing_plans`, `to_plan_id→pricing_plans`, `seats`, `status`
`tax_rates`	Selectable GST/tax slabs	`id`, `name`, `rate` (NUMERIC), `is_default`, `is_active` (seeded Free/GST 5/8/12/18%)

Users, Accounts & Auth

Defined in src/lib/vendor-schema.ts (and scripts/init-db.ts for the base users table).

Table	Purpose	Key columns
`customers`	Learner and instructor accounts	`id`, `slug` (UQ), `name`, `email`, `password_hash`, `account_type` (student/instructor), `email_verified`/`phone_verified`, `otp_code`/`otp_expires_at`/`otp_purpose`, `bio`, `occupation`, social links, `notification_prefs` (JSONB), `is_blacklisted`, `is_active`
`instructors`	Instructor profile (1:1 with a customer)	`id`, `customer_id→customers` (UQ), `headline`, `bio`, `expertise`/`social` (JSONB), `status` (pending/approved/rejected/suspended), `revenue_share_pct`, `payout_method`, `payout_details` (JSONB), `approved_at`, `seq`
`customer_addresses`	Learner address book	`id`, `customer_id→customers`, `label`, `full_name`, `phone`, `address_line`, `country_code`, `location_path` (JSONB), `is_default`
`customer_notifications`	In-app notification per customer	`id`, `customer_id→customers`, `title`, `body`, `kind`, `is_read`
`users`	Admin/staff login accounts	`id`, `email` (UQ), `password` (bcrypt), `name`, `role`, `role_id→roles`, `avatar`, `is_active`
`roles`	RBAC roles	`id`, `slug` (UQ), `name`, `permissions` (JSONB), `is_system`, `is_active`
`permissions`	Permission catalog	`id`, `key` (UQ), `label`, `group_name`, `description`
`activity_log`	Admin activity audit	`id`, `user_label`, `action`, `summary`, `entity_type`, `entity_id`, `meta` (JSONB)

Messaging (Learner ↔ Instructor Chat)

Defined in src/lib/lms-schema.ts.

Table	Purpose	Key columns
`conversations`	A learner↔instructor thread	`id`, `learner_id→customers`, `instructor_id→instructors`, `last_message_at`, UQ `(learner_id, instructor_id)`
`messages`	A chat message (with attachments)	`id`, `conversation_id→conversations`, `sender_role` (learner/instructor), `body`, `kind`, `attachment_url`/`attachment_name`/`attachment_type`/`attachment_size`/`attachment_expires_at`/`attachment_deleted`, `edited_at`, `deleted_at`, `read_at`

CMS / Website Content

Defined in scripts/init-db.ts (plus admin enhancements added by ALTER TABLE).

Table	Purpose	Key columns
`pages`	Static CMS pages (about, privacy, …)	`id`, `slug` (UQ), `title`, `content`, `meta_title`, `meta_description`, `is_active`
`blogs`	Blog posts	`id`, `slug` (UQ), `title`, `category_id→blog_categories`, `excerpt`, `content`, `image`, `author`, `tags` (JSONB), `is_published`, `published_at`
`blog_categories`	Blog taxonomy	`id`, `slug` (UQ), `name`, `description`, `sort_order`
`faqs`	Frequently-asked questions	`id`, `category`, `question`, `answer`, `sort_order`, `is_active`
`gallery`	Media gallery items	`id`, `title`, `type`, `url`, `thumbnail`, `category`, `images` (JSONB), `sort_order`, `is_active`
`hero_slides`	Home hero slider	`id`, `eyebrow`, `title`, `highlight`, `subtitle`, `image` (+ tablet/mobile), CTA label/href fields, `sort_order`, `is_active`
`testimonials`	Customer testimonials	`id`, `name`, `location`, `rating`, `message`, `image`, `sort_order`, `is_active`
`partners`	Partner/brand logos (home marquee)	`id`, `name`, `logo`, `url`, `slug`, `sort_order`, `is_active`
`menu_items`	Header/footer navigation	`id`, `location`, `label`, `href`, `parent_id`, `open_in_new_tab`, `sort_order`, `is_active`
`about`	Key/value content for the About page	`id`, `key` (UQ), `value`
`contacts`	Contact-form submissions	`id`, `name`, `email`, `phone`, `message`, `is_read`
`enquiries`	Enquiry/lead submissions	`id`, `name`, `email`, `phone`, `package_name`, `message`, `status`, `is_read`
`newsletter`	Newsletter subscribers	`id`, `email` (UQ), `name`, `is_active`, `subscribed_at`

Integrations, Settings & Licensing

Defined in src/lib/vendor-schema.ts.

Table	Purpose	Key columns
`integration_connections`	Per-channel provider config (encrypted)	`id`, `channel` (EMAIL/STORAGE/PAYMENT/…), `provider`, `label`, `is_active`, `is_primary`, `config`/`meta` (JSONB, secrets encrypted), `last_tested_ok`
`app_settings`	Branding + app config (single row, `id=1`)	`app_name`, `app_icon`, color fields, desktop/mobile download URLs, store badges, `versioning` (JSONB)
`theme_settings`	Theme options (single JSONB row, `id=1`)	`id=1`, `data` (JSONB)
`app_license`	Domain license record (single row, `id=1`)	`id=1`, `data` (JSONB LicenseRecord)
`notification_templates`	Per-event/channel templates	`id`, `event`, `channel`, `subject`, `body`, `is_active`, UQ `(event, channel)`
`notification_logs`	Broadcast/notification dispatch log	`id`, `channel`, `title`, `audience`, `status`, `recipient_count`, `scheduled_at`, `sent_at`
`message_templates`	Saved broadcast templates per channel	`id`, `name`, `channel`, `subject`, `body`, `is_active`
`api_tokens`	API access tokens	`id`, `name`, `token_prefix`, `token_hash`, `scopes` (JSONB), `expires_at`, `revoked_at`
`webhooks`	Outbound webhook endpoints	`id`, `label`, `url`, `secret`, `events` (JSONB), `is_active`
`media_assets`	Storage media library	`id`, `object_key`, `url`, `mime_type`, `kind`, `size_bytes`, `folder`, `width`/`height`
`translations`	Multi-language field values	`id`, `entity_type`, `entity_id`, `field`, `language_code`, `value`, UQ `(entity_type, entity_id, field, language_code)`
`currencies`, `languages`, `countries`, `locations`	Reference/localization data	code (UQ), name, flags, hierarchy (`parent_id` / `country_code`)

Store (Physical Goods — Optional Commerce)

src/lib/vendor-schema.ts also ships a full storefront schema reused from the ecommerce base. These coexist with the LMS commerce tables and power product sales, inventory, shipping, and order fulfilment.

Table group	Tables
Products	`products`, `product_categories`, `brands`, `attributes`, `product_reviews`, `stock_movements`
Orders	`orders`, `order_items`, `order_returns`, `order_refunds`, `order_invoices`, `shipments`, `shipping_labels`
Shipping	`shipping_methods`, `shipping_zones`, `shipping_rates`, `delivery_slots`, `pickup_locations`, `warehouses`, `stock_transfers`
Carts	`customer_carts`, `stock_subscriptions`
Reviews	`vendor_reviews`

Add-on Tables

Each feature add-on under src/product/addons/<id>/schema.ts owns its own tables, created idempotently by the add-on's ensureSchema() when it is active (not by the core init scripts). Representative examples:

Add-on	Tables
`certificates`	`certificates`, `certificate_templates`
`gamification`	`points_ledger`, `badges`, `student_badges`, `point_rules`
`exams`	`exam`, `exam_question`, `exam_attempt`, `exam_snapshot`, `exam_event`
`assignments`	`assignment`, `assignment_submission`
`forums`	`forum_thread`, `forum_post`
`organizations`	`organizations`, `org_members`, `org_courses`, `org_invites`
`affiliate`	`affiliate`, `referral`, `commission`
`wallet`	`wallet_account`, `wallet_txn`
`scorm`	`scorm_package`, `scorm_track`
`attendance`	`attendance_session`, `attendance_record`
`live-classes`	`live_class`
`notes`	`lesson_note`
`support`	`support_ticket`, `support_message`
`waitlists`	`course_waitlist_config`, `course_waitlist`
`prerequisites`	`course_prerequisite`
`credits`	`ceu_course`, `credit_award`
`badges` (Open Badges)	`badge_class`, `badge_assertion`
`announcements`	`announcement`, `announcement_read`

Note: the certificates and gamification tables are referenced in src/lib/lms-schema.ts comments but are intentionally created by their add-on's ensureSchema() rather than by the core LMS schema.