CICD/docs/db/schema-changelog.md

Database Schema Changelog

v1.1.0 - Exam Structure Support

Date: 2025-12-29
Migration ID: 0001_flawless_texas_twister
Author: Principal Database Architect

1. Summary

This release introduces support for hierarchical exam structures (Sectioning/Grouping).

2. Changes

2.1 Table: exams

• Action: ADD COLUMN
• Field: structure (JSON)
• Reason: To support nested exam layouts (e.g., "Part I: Listening", "Section A").
  • Architecture Note: This JSON field is strictly for Presentation Layer ordering and grouping. The exam_questions table remains the Source of Truth for relational integrity and scoring logic.
  • Schema Definition:

    type ExamStructure = Array<
       | { type: 'group', title: string, children: ExamStructure }
       | { type: 'question', questionId: string, score: number }
    >
    

3. Migration Strategy

• Up: Run standard Drizzle migration.
• Down: Revert structure column. Note that FK names can be kept short as they are implementation details.

4. Impact Analysis

• Performance: Negligible. JSON parsing is done client-side or at application layer.
• Data Integrity: High. Existing data is unaffected. New structure field defaults to NULL.

v1.2.0 - Homework Module Tables & FK Name Hardening

Date: 2025-12-31
Migration ID: 0002_equal_wolfpack
Author: Principal Database Architect

1. Summary

This release introduces homework-related tables and hardens foreign key names to avoid exceeding MySQL identifier length limits (MySQL 64-char constraint names).

2. Changes

2.1 Tables: Homework Domain

• Action: CREATE TABLE
• Tables:
  • homework_assignments
  • homework_assignment_questions
  • homework_assignment_targets
  • homework_submissions
  • homework_answers
• Reason: Support assignment lifecycle, targeting, submissions, and per-question grading.

2.2 Foreign Keys: Homework Domain (Name Hardening)

• Action: ADD FOREIGN KEY (with short constraint names)
• Details:
  • homework_assignments: hw_asg_exam_fk, hw_asg_creator_fk
  • homework_assignment_questions: hw_aq_a_fk, hw_aq_q_fk
  • homework_assignment_targets: hw_at_a_fk, hw_at_s_fk
  • homework_submissions: hw_sub_a_fk, hw_sub_student_fk
  • homework_answers: hw_ans_sub_fk, hw_ans_q_fk
• Reason: Default generated FK names can exceed 64 characters in MySQL and fail during migration.

2.3 Table: questions_to_knowledge_points

• Action: RENAME FOREIGN KEY (implemented as drop + add)
• Details:
  • Old: questions_to_knowledge_points_question_id_questions_id_fk -> New: q_kp_qid_fk
  • Old: questions_to_knowledge_points_knowledge_point_id_knowledge_points_id_fk -> New: q_kp_kpid_fk
• Reason: Previous names exceeded MySQL's 64-character identifier limit, causing potential migration failures in production environments.

3. Migration Strategy

• Up: Run standard Drizzle migration. The migration is resilient whether the legacy FK names exist or have already been renamed.
• Down: Not provided. Removing homework tables and FKs is destructive and should be handled explicitly per environment.

4. Impact Analysis

• Performance: Minimal. New indexes are scoped to common homework access patterns.
• Data Integrity: High. Foreign keys enforce referential integrity for homework workflow.

v1.3.0 - Classes Domain (Teacher Class Management)

Date: 2025-12-31
Migration ID: 0003_petite_newton_destine
Author: Principal Database Architect

1. Summary

This release introduces the core schema for teacher class management: classes, enrollments, and schedules.

2. Changes

2.1 Tables: Classes Domain

• Action: CREATE TABLE
• Tables:
  • classes
  • class_enrollments
  • class_schedule
• Reason: Support teacher-owned classes, student enrollment lists, and weekly schedules.

2.2 Enum: Enrollment Status

• Action: ADD ENUM
• Enum: class_enrollment_status = (active, inactive)
• Reason: Provide a stable status field for filtering active enrollments.

2.3 Foreign Keys & Indexes

• Action: ADD FOREIGN KEY, CREATE INDEX
• Key Relationships:
  • classes.teacher_id -> users.id (cascade delete)
  • class_enrollments.class_id -> classes.id (cascade delete)
  • class_enrollments.student_id -> users.id (cascade delete)
  • class_schedule.class_id -> classes.id (cascade delete)
• Indexes:
  • classes_teacher_idx, classes_grade_idx
  • class_enrollments_class_idx, class_enrollments_student_idx
  • class_schedule_class_idx, class_schedule_class_day_idx

3. Migration Strategy

• Up: Run standard Drizzle migration. Ensure DATABASE_URL points to the intended schema (e.g., next_edu).
• Down: Not provided. Dropping these tables is destructive and should be handled explicitly per environment.

4. Impact Analysis

• Performance: Indexes align with common query patterns (teacher listing, enrollment filtering, per-class schedule).
• Data Integrity: High. Foreign keys enforce ownership and membership integrity across teacher/classes/students.

v1.4.0 - Classes Domain Enhancements (School Name & Subject Teachers)

Date: 2026-01-07
Migration ID: 0005_add_class_school_subject_teachers
Author: Frontend/Fullstack Engineering

1. Summary

This release extends the Classes domain to support school-level sorting and per-subject teacher assignment defaults.

2. Changes

2.1 Table: classes

• Action: ADD COLUMN
• Field: school_name (varchar(255), nullable)
• Reason: Enable sorting/grouping by school name, then grade, then class name.

2.2 Table: class_subject_teachers

• Action: CREATE TABLE
• Primary Key: (class_id, subject)
• Columns:
  • class_id (varchar(128), FK -> classes.id, cascade delete)
  • subject (enum: 语文/数学/英语/美术/体育/科学/社会/音乐)
  • teacher_id (varchar(128), FK -> users.id, set null on delete)
  • created_at, updated_at
• Reason: Maintain a stable default “subject list” per class while allowing admin/teacher to assign the actual teacher per subject.

3. Migration Strategy

• Up: Run standard Drizzle migration.
• Down: Not provided. Dropping assignment history is destructive.

4. Impact Analysis

• Performance: Minimal. Table is small (8 rows per class) and indexed by class/teacher.
• Data Integrity: High. Composite PK prevents duplicates per class/subject; FKs enforce referential integrity.

v1.4.1 - Classes Domain Enhancements (School/Grade Normalization)

Date: 2026-01-07
Migration ID: 0006_faithful_king_bedlam
Author: Frontend/Fullstack Engineering

1. Summary

This release extends the classes table to support normalized school and grade references.

2. Changes

2.1 Table: classes

• Action: ADD COLUMN
• Fields:
  • school_id (varchar(128), nullable)
  • grade_id (varchar(128), nullable)
• Reason: Enable filtering and sorting by canonical school/grade entities instead of relying on free-text fields.

3. Migration Strategy

• Up: Run standard Drizzle migration.
• Down: Not provided. Dropping columns is destructive.

4. Impact Analysis

• Performance: Minimal. Indexing and joins can be added as usage evolves.
• Data Integrity: Medium. Existing rows remain valid (nullable fields); application-level validation can enforce consistency.

v1.5.0 - Classes Domain Feature (Invitation Code)

Date: 2026-01-08
Migration ID: 0007_add_class_invitation_code
Author: Frontend/Fullstack Engineering

1. Summary

This release introduces a 6-digit invitation code on classes to support join-by-code enrollment.

2. Changes

2.1 Table: classes

• Action: ADD COLUMN + ADD UNIQUE CONSTRAINT
• Field: invitation_code (varchar(6), nullable, unique)
• Reason: Allow students to enroll into a class using a short code, while ensuring uniqueness across all classes.

3. Migration Strategy

• Up: Run standard Drizzle migration.
• Backfill: Optional. Existing classes can keep NULL or be populated via application-level actions.
• Down: Not provided. Removing a unique constraint/column is destructive.

4. Impact Analysis

• Performance: Minimal. Uniqueness is enforced via an index.
• Data Integrity: High. Unique constraint prevents code collisions and simplifies server-side enrollment checks.