Curriculum Content Architecture & Versioned Student Telemetry

Refactor curriculum schema for versioning, observable rubrics, and structured responses

1. Problem Statement

The current modules, sections, and user_progress tables suffer from severe architectural limitations:

1. Unversioned Mutable Curriculum: Edits made by educators in teacher-site directly mutate the rows that students are actively reading and referencing.
2. Unstructured Responses: user_progress.response is a single raw text string. The curriculum requires specific pedagogical interactions: The Accountability Cycle inventories, limits-of-control checks, and four-agreements self-audits.
3. Missing Metrics & State: user_progress only stores started and completed booleans. There is no tracking of submission timestamps, duration/time spent, or observable behavioral rubrics.

2. Target Schema Improvements

• modules:
  • Add status: 'draft' | 'published' | 'archived'.
  • Add version: integer tracking revision updates.
  • Add ctePathway: 'general' | 'computer_science' | 'healthcare' | 'trades'.
  • Add authorId: references user.id.
• sections:
  • Add sectionType: 'instructional' | 'accountability_audit' | 'practicum_reflection' | 'amends_mapping'.
  • Add promptSchema: JSON/schema configuration describing the expected student inputs.
• user_progress -> student_module_progress & student_practicum_submissions:
  • Decouple navigational progress (started_at, last_accessed_at, completed_at) from formal assignment submissions.
  • Store structured responses (response_payload as typed JSON) with revision history.
  • Add optional educator evaluation fields: evaluator_id, rubric_score, behavioral_shift_observed (boolean), feedback_notes.

3. Acceptance Criteria

• Drafted lessons cannot be viewed by students until transitioned to status: 'published'.
• Multiple submissions/revisions can be tracked per student without data loss.
• Composite indexes added to (user_id, module_id) and (module_id, order).

Start by locating the schema definitions for modules, sections, and user_progress, then trace the teacher-site curriculum editing path. Map the proposed progress and submission records before changing the schema. Done means published-only student access, lossless submission revisions, and the specified composite indexes.