Category 1─* Course *─1 Instructor 0..1─1 StaffUser ─* Message
│
├─* Section 1─* Lesson
├─* Quiz 1─* Question (Quiz may point to the Section it follows)
├─* LiveSession
├─* Review *─1 Student
├─* Thread 1─* Message (Thread *─1 Student)
├─* WishlistItem *─1 Student
└─* Enrolment *─1 Student
├─* LessonProgress *─1 Lesson
├─* QuizAttempt *─1 Quiz
└─0..1 Certificate
Student 1─* Order 1─* OrderItem *─0..1 Course
Student 1─* LessonNote *─1 Lesson
Coupon, Post (*─0..1 Instructor), Setting (one row)
Tables
| Table | Key fields |
|---|---|
Setting |
One row (id 1): names, taglines, about, contacts, currency and decimals, taxPercent, cardEnabled, bankEnabled and bank details, acceptingEnrolments, instructorShare, certificate signer, instructorPermissions (JSON list) |
StaffUser |
Dashboard accounts: role ADMIN or INSTRUCTOR, active, scrypt passwordHash |
Instructor |
Public profile: names, titles, bios, experience and skills (JSON), photo, links, userId linking the dashboard account |
Category |
Slug, names, descriptions, color (a category hue or #hex), order, active |
Course |
Slug, category, instructor, titles, subtitles, descriptions, outcomes, requirements and FAQ (JSON), level, language, format RECORDED or LIVE, price and compareAt in baisa, cover, status DRAFT, PUBLISHED or ARCHIVED, featured, certificate, publishedAt |
Section |
Course, titles, sort |
Lesson |
Course and section, titles, kind TEXT or VIDEO, videoUrl, Markdown bodies, minutes, preview, sort |
Quiz |
Course, optional section, titles, passMark, required, maxAttempts (0 = unlimited) |
Question |
kind MCQ or TF, prompts, options JSON [{ar, en}], correct index, explanations |
Student |
Learner accounts (separate from staff): name, unique email, phone, headline |
Enrolment |
Unique per student and course; source FREE, PAID or ADMIN, orderId, cached progress 0-100, lastLessonId, completedAt |
LessonProgress |
Unique per enrolment and lesson, completedAt |
LessonNote |
A learner's private note per lesson |
QuizAttempt |
Enrolment, quiz, answers JSON, score, passed |
Certificate |
Unique code, one per enrolment, snapshot of name, course, instructor, hours, issuedAt |
Order |
Unique code, student, subtotal, discount, tax, total, couponCode, paymentMethod FREE, CARD or BANK, status PENDING, PAID, FAILED, CANCELLED or REFUNDED, Thawani paymentRef, paidAt |
OrderItem |
Course (kept as text if the course is deleted), instructorId, price, net (what was paid after the coupon share) |
Coupon |
code, type PERCENT or FIXED, value, minOrder, expiresAt, usageLimit, usedCount, active |
WishlistItem |
Saved courses |
Review |
Unique per student and course, rating, body, status PENDING, APPROVED or HIDDEN, featured, reply |
LiveSession |
Course, titles, startsAt, minutes, meetingUrl, notes |
Thread, Message |
Student to instructor conversations, status OPEN or CLOSED, unread flags for each side |
Post |
Blog articles: slug, titles, excerpts, Markdown bodies, cover, tags, author, published, readMinutes |
Rules kept in code, not in the database
The pure modules in src/lib (tested by npm test) hold the rules: learning.ts (progress, quiz scoring, attempts, certificate eligibility, lesson order, video links), pricing.ts (cart lines, coupons, totals, discount split, instructor share), rules.ts (codes, slugs, order flow), roles.ts (permissions), filters.ts (catalogue filters and Arabic search folding).
Changing the schema
Edit prisma/schema.prisma, then npx prisma db push (keeps data; refuses destructive changes) and npx prisma generate. Add the field to the zod schema in src/lib/inputs.ts and to the dashboard form.