Architecture Blueprint

LMS Data Linking & AI Automation Design

This document explains how to link courses, lessons, topics, quizzes, assignments, and certificates in a Next.js + Supabase LMS so that real-time progress tracking and AI-driven workflows stay reliable and maintainable.

1. Overview & Goals

Objectives include a clean schema, trustworthy real-time updates, and a backend that lets AI generate and manage learning content—courses, quizzes, assignments, grading, and certificates.

2. Core Entities & Relationships

The hierarchy flows from users into courses, lessons, and lesson-level assets such as topics, quizzes, assignments, and their downstream attempts/submissions and certificates.

Course
└─ Lesson
   ├─ Topic
   ├─ Quiz
   │  └─ Quiz Questions
   └─ Assignment
     └─ Submissions

Course + Student → Progress + Completion → Certificate

3. Database Tables & Linking

Use UUIDs, foreign keys, and ON DELETE CASCADE where appropriate. Below is the recommended schema summary:

courses

  • id, instructor_id, title, description, thumbnail_url, is_published, timestamps
  • One-to-many with lessons; referenced by certificates and enrollments.

lessons

  • id, course_id, title, summary, order_index, timestamps
  • Cascade deletes clean up dependent topics/quizzes/assignments.

topics

  • id, lesson_id, title, content_type, content_url, body, order_index, timestamps
  • Lowest-level reusable content blocks.

quizzes & quiz_questions

  • quizzes: id, lesson_id, title, description, is_ai_generated, timestamps
  • quiz_questions: id, quiz_id, question_type, text, options, correct_answer, order_index
  • Students create many quiz_attempts per quiz.

quiz_attempts

  • id, quiz_id, student_id, score, started_at, completed_at, answers
  • Realtime subscriptions drive scorecards and analytics.

assignments & submissions

  • assignments: id, lesson_id, title, description, due_date, max_grade, is_ai_generated
  • submissions: id, assignment_id, student_id, submission_url/content, grade, feedback, graded_by, plagiarism_score

certificates

  • id, course_id, student_id, template_id, certificate_url, issued_at
  • Unique constraint on (course_id, student_id) prevents duplicates.

progress & analytics

  • lesson_progress: status per (student, lesson)
  • activity_events: optional, for granular analytics metadata.

4. Real-Time Behaviour & Analytics

Subscribe to lesson_progress, quiz_attempts, submissions, and certificates. Each event should recompute course completion, aggregate metrics, and push updates over WebSockets.

  • Drive dashboards with real-time completion %, quiz averages, and active user counts.
  • Use background jobs or materialized views to keep aggregate queries fast.

5. AI-Driven Automation

Treat AI as part of the backend workflow: it should create structured records, not just copy.

Course Creation

  1. Collect topic, audience, difficulty, and length from the instructor.
  2. Call Claude Sonnet 4.5 (or similar) to generate course + lesson outlines.
  3. Parse JSON, wrap inserts in a transaction: course → lessons → topics.
  4. Optionally generate detailed lesson bodies per topic.
  5. Emit realtime events so dashboards refresh automatically.

Quiz Generation

  1. Send lesson content to AI with instructions for MCQs.
  2. Insert quizzes + questions, flagging AI-generated rows.
  3. Notify UI in real time so instructors see the new quiz instantly.

Assignment Creation & Grading

  1. Create AI-suggested assignments tied to objectives.
  2. On submission, send instructions, rubric, and student work to AI for grading.
  3. Persist grade/feedback/plagiarism score; set graded_by to an AI user id.

Certificates

  1. Detect completion via lesson_progress + quiz_attempts.
  2. Generate certificate copy and a PDF asset; store certificate_url.
  3. Publish events so students receive certificates immediately.

6. Data Integrity Guardrails

  • Enforce foreign keys everywhere and cascade deletes where child data should disappear.
  • Use unique constraints (e.g., certificates per course/student).
  • Wrap AI-write flows in transactions and validate responses before inserts.
  • Log AI prompts/responses in an ai_logs table for debugging and auditability.

7. Analytics Strategy

Combine fine-grained activity_events with aggregate queries or materialized views. Example metrics:

  • Course completion % = completed lessons / total lessons per enrollment.
  • Average quiz score per lesson or course.
  • Active students in trailing 24 hours.
  • On-time submission rates per assignment.

8. Summary

A rigorously linked schema plus AI-driven workflows yields automated course builds, instant grading, certificate generation, and real-time insights. Because AI writes directly into the database through validated transactions, the system stays low-bug and ready for marketplace or community extensions.