Courses Schema

ext_courses - Courses (LMS)

Schema: ext_courses | Auto-generated on 2026-03-30

---

courses

Course catalog with pricing, levels, and enrollment tracking.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
instructor_id	uuid	NO	—
title	text	NO	—
slug	text	NO	—
description	text	YES	—
short_description	text	YES	—
thumbnail_url	text	YES	—
promo_video_url	text	YES	—
price	numeric	NO	0
type	text	NO	'course'
level	text	NO	'all'
status	text	NO	'draft'
is_free	boolean	NO	false
duration_minutes	integer	NO	0
capacity	integer	YES	—
enrollment_count	integer	NO	0
rating_avg	numeric	NO	0
rating_count	integer	NO	0
metadata	jsonb	NO	'{}'
published_at	timestamptz	YES	—
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

chapters

Course chapter/section groupings.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
title	text	NO	—
description	text	YES	—
sort_order	integer	NO	0
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

chapter_items

Polymorphic items within chapters (lessons, quizzes, assignments).

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
chapter_id	uuid	NO	—
item_type	text	NO	—
item_id	uuid	NO	—
title	text	NO	—
sort_order	integer	NO	0
is_free_preview	boolean	NO	false
created_at	timestamptz	NO	now()

lessons

Individual course lessons.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
title	text	NO	—
content	text	YES	—
video_url	text	YES	—
video_provider	text	YES	—
video_duration	integer	YES	—
type	text	NO	'video'
status	text	NO	'draft'
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

lesson_attachments

Files attached to lessons.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
lesson_id	uuid	NO	—
title	text	NO	—
file_path	text	NO	—
file_size	integer	YES	—
file_type	text	YES	—
created_at	timestamptz	NO	now()

enrollments

Student course enrollments with progress tracking.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
user_id	uuid	NO	—
payment_id	uuid	YES	—
status	text	NO	'active'
progress_percent	integer	NO	0
completed_items	integer	NO	0
total_items	integer	NO	0
enrolled_at	timestamptz	NO	now()
completed_at	timestamptz	YES	—
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

progress

Per-item completion tracking within an enrollment.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
enrollment_id	uuid	NO	—
user_id	uuid	NO	—
item_type	text	NO	—
item_id	uuid	NO	—
completed	boolean	NO	false
completed_at	timestamptz	YES	—
created_at	timestamptz	NO	now()

time_tracking

Daily learning time per enrollment.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
enrollment_id	uuid	NO	—
user_id	uuid	NO	—
date	date	NO	CURRENT_DATE
seconds_spent	integer	NO	0
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

files

Downloadable course files.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
title	text	NO	—
description	text	YES	—
file_path	text	NO	—
file_size	integer	YES	—
file_type	text	YES	—
accessibility	text	NO	'enrolled'
sort_order	integer	NO	0
created_at	timestamptz	NO	now()

faqs

Course FAQ entries.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
question	text	NO	—
answer	text	NO	—
sort_order	integer	NO	0
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()

prerequisites

Course prerequisite requirements.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
required_course_id	uuid	NO	—
created_at	timestamptz	NO	now()

personal_notes

Student notes on lessons within a course.

Column	Type	Nullable	Default
id	uuid	NO	gen_random_uuid()
course_id	uuid	NO	—
user_id	uuid	NO	—
lesson_id	uuid	YES	—
content	text	NO	—
created_at	timestamptz	NO	now()
updated_at	timestamptz	NO	now()