Database Schema

Last updated on 2026-09-13

The kit uses 38 Supabase tables organized into eight domains: users, courses, assessment, gamification, community, student tools, admin, and commerce. All tables have row-level security (RLS) enabled with a three-role model (student, instructor, admin).

Schema Overview

Users:          profiles

Courses:        categories, courses, modules, lessons

Enrollment:     enrollments, lesson_completions, course_reviews

Assessment:     quizzes, quiz_results, assignments, assignment_submissions

Gamification:   certificates, badges, user_badges

Community:      discussion_threads, discussion_replies, live_sessions,
                live_session_registrations, study_groups,
                study_group_members, messages

Student Tools:  bookmarks, notes, notifications, calendar_events,
                learning_paths, learning_path_progress

Admin:          course_approvals, coupons, announcements,
                support_tickets, ticket_messages, audit_log,
                site_settings

Commerce:       billing_records, payment_methods, instructor_payouts

Analytics:      daily_metrics

Tables

profiles

Auto-created on user signup via database trigger. Default role is student.

Column	Type	Notes
`id`	uuid (PK, FK)	References `auth.users.id`, cascade delete
`email`	text	User email (not null)
`full_name`	text	Display name (not null)
`avatar_url`	text	Profile image URL
`role`	text	`student`, `instructor`, or `admin` (default `student`)
`headline`	text	Short bio line
`bio`	text	Full biography
`location`	text	User location
`status`	text	`active`, `inactive`, or `suspended` (default `active`)
`plan`	text	`free`, `pro`, or `enterprise` (default `free`)
`xp`	integer	Experience points (default 0)
`level`	integer	User level (default 1)
`streak`	integer	Daily streak count (default 0)
`social_*`	text	Social links (website, twitter, linkedin, youtube, github)
`last_active_at`	timestamptz	Last activity
`created_at`	timestamptz	Auto-set
`updated_at`	timestamptz	Auto-updated by trigger

courses

Main course entity with full metadata.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`slug`	text (unique)	URL-safe identifier
`title`	text	Course title (not null)
`subtitle`	text	Short description
`description`	text	Full description
`thumbnail`	text	Course image URL
`category_id`	uuid (FK)	References `categories.id`
`level`	text	`beginner`, `intermediate`, or `advanced`
`instructor_id`	uuid (FK)	References `profiles.id`
`price`	numeric	Course price
`original_price`	numeric	Original price (for discounts)
`rating`	numeric	Average rating (0-5)
`review_count`	integer	Number of reviews
`student_count`	integer	Enrolled students
`duration`	text	Total duration
`lesson_count`	integer	Number of lessons
`module_count`	integer	Number of modules
`language`	text	Course language (default `English`)
`status`	text	`draft`, `published`, `archived`, `pending`, `approved`, `rejected`
`tags`	text[]	Course tags array
`what_you_will_learn`	text[]	Learning outcomes
`requirements`	text[]	Prerequisites
`target_audience`	text[]	Target audience
`includes_*`	various	Course includes (video hours, articles, downloads, certificate, lifetime access)
`featured`	boolean	Featured course flag

modules

Course modules (sections) with ordering.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`course_id`	uuid (FK)	References `courses.id`, cascade delete
`title`	text	Module title (not null)
`order`	integer	Display order
`duration`	text	Module duration

lessons

Individual lessons within modules.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`module_id`	uuid (FK)	References `modules.id`, cascade delete
`title`	text	Lesson title (not null)
`type`	text	`video`, `text`, `code`, or `quiz`
`duration`	text	Lesson duration
`order`	integer	Display order
`free_preview`	boolean	Available without enrollment
`status`	text	`draft` or `published`
`content`	text	Lesson content (markdown)
`video_url`	text	Video URL

enrollments

Student enrollment tracking with progress.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`user_id`	uuid (FK)	References `profiles.id`, cascade delete
`course_id`	uuid (FK)	References `courses.id`, cascade delete
`progress`	numeric	Completion percentage (0-100)
`status`	text	`active`, `completed`, `paused`, `cancelled`
`enrolled_at`	timestamptz	Enrollment date
`completed_at`	timestamptz	Completion date

Unique constraint: (user_id, course_id)

quizzes

Quiz definitions with JSONB questions.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`course_id`	uuid (FK)	References `courses.id`, cascade delete
`lesson_id`	uuid (FK)	References `lessons.id`
`title`	text	Quiz title (not null)
`time_limit`	integer	Time limit in minutes (default 30)
`passing_score`	integer	Minimum passing percentage (default 70)
`questions`	jsonb	Array of question objects

quiz_results

Student quiz attempts with detailed scoring.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`user_id`	uuid (FK)	References `profiles.id`
`quiz_id`	uuid (FK)	References `quizzes.id`
`score`	integer	Points earned
`total_points`	integer	Maximum points
`percentage`	numeric	Score percentage
`passed`	boolean	Whether the student passed
`time_spent`	text	Time taken
`answers`	jsonb	Detailed answer data

certificates

Earned certificates with unique credential IDs.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`user_id`	uuid (FK)	References `profiles.id`
`course_id`	uuid (FK)	References `courses.id`
`credential_id`	text (unique)	Verifiable credential ID
`skills`	text[]	Skills validated
`issued_at`	timestamptz	Issue date

badges

Achievement badge definitions with rarity tiers.

Column	Type	Notes
`id`	uuid (PK)	Auto-generated
`name`	text	Badge name
`description`	text	Badge description
`icon`	text	Lucide icon name
`category`	text	`streak`, `completion`, `quiz`, `community`, `special`
`rarity`	text	`common`, `rare`, `epic`, `legendary`
`requirement`	text	How to earn

discussion_threads / discussion_replies

Forum threads with nested replies, voting, and solved status.

live_sessions / live_session_registrations

Scheduled live sessions with instructor, capacity, and attendance tracking.

study_groups / study_group_members

Study groups with owner, privacy settings, and member management.

messages

Direct messaging between users with read status and attachments.

bookmarks / notes

Student bookmarks and lesson notes with pinning support.

notifications

User notifications with type, read status, and action URLs.

course_approvals

Admin workflow for reviewing and approving instructor course submissions.

coupons

Discount codes with usage limits, expiry dates, and course restrictions.

announcements

Platform-wide announcements with audience targeting and scheduling.

support_tickets / ticket_messages

Help desk with priority, status, category, and threaded messages.

audit_log

Admin action logging with user, action, entity, and metadata.

billing_records / payment_methods / instructor_payouts

Commerce tables for payment history, stored cards, and instructor earnings.

daily_metrics

Platform-wide daily analytics: enrollments, completions, revenue, active users.

Row-Level Security

RLS is enabled on every table. The kit uses a three-role model with helper functions:

-- Check if user is any authenticated user (student or above)
CREATE FUNCTION public.is_student_or_above() RETURNS boolean

-- Check if user is an instructor or admin
CREATE FUNCTION public.is_instructor_or_above() RETURNS boolean

-- Check if user is an admin
CREATE FUNCTION public.is_admin() RETURNS boolean

Note: All mutations in the kit use the admin client (service role) to bypass RLS for simplicity. RLS policies are in place for direct database access security.

Policy Summary

Table	Student	Instructor	Admin
profiles	Read all, update own	Read all, update own	Full access
courses	Read published	CRUD own courses	Full access
enrollments	CRUD own	Read for own courses	Full access
quizzes	Read (enrolled courses)	CRUD for own courses	Full access
discussions	Read all, CRUD own posts	Read all, CRUD own	Full access
messages	CRUD own	CRUD own	Full access
course_approvals	--	Read own	Full CRUD
coupons	--	--	Full CRUD
audit_log	--	--	Read all

Triggers

handle_new_user() -- Auto-creates profile on signup with default student role
set_updated_at() -- Updates updated_at on row modification
Course stats -- Recalculates student_count and rating on enrollment/review changes

Seed Data

Entity	Count	Notes
Users / Profiles	8	3 students, 3 instructors, 2 admins. All passwords: `password123`
Categories	8	Web Development, Data Science, Design, etc.
Courses	12	Various levels and instructors
Modules	~36	3 modules per course
Lessons	~108	3 lessons per module
Enrollments	Multiple	Students enrolled in various courses
Course reviews	Multiple	Ratings and comments
Quizzes	Multiple	With JSONB questions
Badges	12	Across 5 categories with 4 rarity tiers
Certificates	Multiple	Earned by students
Discussion threads	Multiple	Forum posts with replies
Live sessions	Multiple	Upcoming, live, and completed
Study groups	Multiple	Public and private
Messages	Multiple	Between users
Coupons	Multiple	Active discount codes
Daily metrics	30 days	Platform analytics data

Regenerating Types

After modifying the schema, regenerate TypeScript types:

npx supabase gen types typescript --project-id your-project-id > types/database.ts

Project Structure

Authentication