-- ============================================================================= -- OCCP System — Online Curriculum Completion Percentage System -- MMC CAST College of Medicine -- -- SQLite schema (local prototype / zero-configuration development). -- The PostgreSQL / Supabase equivalent lives in db/schema.postgres.sql and is -- kept structurally identical so the same application queries run on both. -- -- Portability conventions: -- * primary keys TEXT (UUID v4 generated by the application) -- * timestamps TEXT (ISO-8601 UTC, e.g. 2026-08-24T01:02:03.000Z) -- * booleans INTEGER 0/1 -- * decimals REAL -- * JSON payloads TEXT -- ============================================================================= PRAGMA foreign_keys = ON; -- ----------------------------------------------------------------------------- -- programs — the academic programs / colleges this installation serves -- -- A program owns its curricula and its students. A user whose program_id is NULL -- sees every program (Super Administrators, and any deliberately global account); -- a user with a program_id can only reach that program's records. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS programs ( id TEXT PRIMARY KEY, code TEXT NOT NULL, name TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS ux_programs_code ON programs(code); -- ----------------------------------------------------------------------------- -- profiles — application users and their role -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS profiles ( id TEXT PRIMARY KEY, email TEXT NOT NULL, email_key TEXT NOT NULL, full_name TEXT NOT NULL, role TEXT NOT NULL CHECK (role IN ('SUPER_ADMIN','DEAN_ADMIN','ENCODER','VIEWER')), password_hash TEXT NOT NULL, password_salt TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), program_id TEXT REFERENCES programs(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS ux_profiles_email_key ON profiles(email_key); CREATE INDEX IF NOT EXISTS ix_profiles_program ON profiles(program_id); -- ----------------------------------------------------------------------------- -- sessions — server-side session store for cookie authentication -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES profiles(id) ON DELETE CASCADE, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, user_agent TEXT ); CREATE INDEX IF NOT EXISTS ix_sessions_user ON sessions(user_id); CREATE INDEX IF NOT EXISTS ix_sessions_expires ON sessions(expires_at); -- ----------------------------------------------------------------------------- -- auth_attempts — basic rate limiting for login and import endpoints -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS auth_attempts ( id TEXT PRIMARY KEY, bucket TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS ix_auth_attempts_bucket ON auth_attempts(bucket, created_at); -- ----------------------------------------------------------------------------- -- curricula — curriculum versions (2023 / 2024 / 2025 / approved source set) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS curricula ( id TEXT PRIMARY KEY, code TEXT NOT NULL, name TEXT NOT NULL, effective_year INTEGER, version TEXT NOT NULL DEFAULT '1', status TEXT NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT','DRAFT_REVIEW','PUBLISHED','ARCHIVED')), year_long_credit_policy TEXT NOT NULL DEFAULT 'ON_FINAL_PASS' CHECK (year_long_credit_policy IN ('ON_FINAL_PASS','PER_COMPONENT')), institutional_type TEXT, source_name TEXT, source_workbook TEXT, primary_effective_ay TEXT, secondary_sheet_effective_ay TEXT, mapping_status TEXT, source_total_units_declared REAL, clerkship_reference_days INTEGER, notes TEXT, published_at TEXT, published_by TEXT REFERENCES profiles(id), program_id TEXT REFERENCES programs(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS ux_curricula_code ON curricula(code); CREATE UNIQUE INDEX IF NOT EXISTS ux_curricula_institutional_type ON curricula(institutional_type) WHERE institutional_type IS NOT NULL; CREATE INDEX IF NOT EXISTS ix_curricula_status ON curricula(status); CREATE INDEX IF NOT EXISTS ix_curricula_program ON curricula(program_id); -- ----------------------------------------------------------------------------- -- curriculum_courses — unit-bearing curriculum requirements -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS curriculum_courses ( id TEXT PRIMARY KEY, curriculum_id TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE, course_code TEXT NOT NULL, course_code_key TEXT NOT NULL, course_title TEXT NOT NULL, units REAL NOT NULL CHECK (units >= 0), lecture_units REAL CHECK (lecture_units IS NULL OR lecture_units >= 0), laboratory_units REAL CHECK (laboratory_units IS NULL OR laboratory_units >= 0), hours_per_week REAL CHECK (hours_per_week IS NULL OR hours_per_week >= 0), intended_year INTEGER NOT NULL CHECK (intended_year >= 1), term TEXT NOT NULL, category TEXT, prerequisite_text TEXT, required INTEGER NOT NULL DEFAULT 1 CHECK (required IN (0,1)), counts_toward_program_units INTEGER NOT NULL DEFAULT 1 CHECK (counts_toward_program_units IN (0,1)), is_year_long_component INTEGER NOT NULL DEFAULT 0 CHECK (is_year_long_component IN (0,1)), year_long_group_id TEXT, component_term TEXT, final_grade_term TEXT, sort_order INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), notes TEXT, source_sheet TEXT, source_row INTEGER, source_notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); -- Course codes are unique per curriculum version AND academic period only. -- They are NOT assumed globally unique (SPEC 52.9 #4). CREATE UNIQUE INDEX IF NOT EXISTS ux_curriculum_courses_scope ON curriculum_courses(curriculum_id, course_code_key, intended_year, term); CREATE INDEX IF NOT EXISTS ix_curriculum_courses_curriculum ON curriculum_courses(curriculum_id); CREATE INDEX IF NOT EXISTS ix_curriculum_courses_code_key ON curriculum_courses(course_code_key); CREATE INDEX IF NOT EXISTS ix_curriculum_courses_group ON curriculum_courses(year_long_group_id); -- ----------------------------------------------------------------------------- -- non_unit_requirements — summer training and clinical clerkship -- These NEVER enter the credited-unit numerator or the denominator. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS non_unit_requirements ( id TEXT PRIMARY KEY, curriculum_id TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE, requirement_type TEXT NOT NULL CHECK (requirement_type IN ('SUMMER_TRAINING','CLERKSHIP_CORE','CLERKSHIP_ELECTIVE')), code TEXT, title TEXT NOT NULL, academic_year INTEGER, term TEXT, required_hours REAL CHECK (required_hours IS NULL OR required_hours >= 0), required_days REAL CHECK (required_days IS NULL OR required_days >= 0), required_months REAL CHECK (required_months IS NULL OR required_months >= 0), elective INTEGER NOT NULL DEFAULT 0 CHECK (elective IN (0,1)), elective_group TEXT, minimum_selections INTEGER, prerequisite_text TEXT, sort_order INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), source_sheet TEXT, source_row INTEGER, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS ix_non_unit_curriculum ON non_unit_requirements(curriculum_id); -- ----------------------------------------------------------------------------- -- curriculum_assignment_rules — configurable student-number resolver -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS curriculum_assignment_rules ( id TEXT PRIMARY KEY, name TEXT NOT NULL, curriculum_id TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE, rule_type TEXT NOT NULL CHECK (rule_type IN ('PREFIX','REGEX','EXACT','NUMERIC_RANGE')), prefix TEXT, regex_pattern TEXT, exact_student_number TEXT, numeric_start REAL, numeric_end REAL, priority INTEGER NOT NULL DEFAULT 100, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), description TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS ix_rules_active_priority ON curriculum_assignment_rules(active, priority); -- ----------------------------------------------------------------------------- -- students -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS students ( id TEXT PRIMARY KEY, student_number TEXT NOT NULL, student_number_key TEXT NOT NULL, last_name TEXT NOT NULL, first_name TEXT NOT NULL, middle_name TEXT, suffix TEXT, premed_course TEXT, premed_school TEXT, graduation_year INTEGER, gwa TEXT, nmat_score REAL, nmat_year INTEGER, curriculum_id TEXT REFERENCES curricula(id), curriculum_assignment_method TEXT NOT NULL DEFAULT 'UNRESOLVED' CHECK (curriculum_assignment_method IN ('AUTO','MANUAL','UNRESOLVED')), curriculum_assignment_rule_id TEXT REFERENCES curriculum_assignment_rules(id) ON DELETE SET NULL, curriculum_override_reason TEXT, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), notes TEXT, last_assessed_at TEXT, updated_by TEXT REFERENCES profiles(id), program_id TEXT REFERENCES programs(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS ux_students_number_key ON students(student_number_key); CREATE INDEX IF NOT EXISTS ix_students_curriculum ON students(curriculum_id); CREATE INDEX IF NOT EXISTS ix_students_last_name ON students(last_name); CREATE INDEX IF NOT EXISTS ix_students_first_name ON students(first_name); CREATE INDEX IF NOT EXISTS ix_students_active ON students(active); CREATE INDEX IF NOT EXISTS ix_students_program ON students(program_id); -- ----------------------------------------------------------------------------- -- student_course_assessments — one active row per (student, curriculum course) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS student_course_assessments ( id TEXT PRIMARY KEY, student_id TEXT NOT NULL REFERENCES students(id) ON DELETE CASCADE, curriculum_course_id TEXT NOT NULL REFERENCES curriculum_courses(id) ON DELETE CASCADE, grade_raw TEXT, grade_numeric REAL, status TEXT NOT NULL DEFAULT 'NOT_TAKEN' CHECK (status IN ('NOT_TAKEN','IN_PROGRESS','PASSED','CREDITED','FAILED','FOR_VALIDATION')), equivalent_source_school TEXT, equivalent_course_code TEXT, equivalent_course_title TEXT, equivalent_source_units REAL CHECK (equivalent_source_units IS NULL OR equivalent_source_units >= 0), approved_credited_units_override REAL CHECK (approved_credited_units_override IS NULL OR approved_credited_units_override >= 0), remarks TEXT, updated_by TEXT REFERENCES profiles(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); -- Structural guarantee that a curriculum requirement can never double count. CREATE UNIQUE INDEX IF NOT EXISTS ux_assessment_student_course ON student_course_assessments(student_id, curriculum_course_id); CREATE INDEX IF NOT EXISTS ix_assessment_student ON student_course_assessments(student_id); CREATE INDEX IF NOT EXISTS ix_assessment_course ON student_course_assessments(curriculum_course_id); CREATE INDEX IF NOT EXISTS ix_assessment_status ON student_course_assessments(status); -- ----------------------------------------------------------------------------- -- student_non_unit_progress — summer training / clerkship tracking -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS student_non_unit_progress ( id TEXT PRIMARY KEY, student_id TEXT NOT NULL REFERENCES students(id) ON DELETE CASCADE, non_unit_requirement_id TEXT NOT NULL REFERENCES non_unit_requirements(id) ON DELETE CASCADE, selected INTEGER NOT NULL DEFAULT 0 CHECK (selected IN (0,1)), status TEXT NOT NULL DEFAULT 'NOT_STARTED' CHECK (status IN ('NOT_STARTED','IN_PROGRESS','COMPLETED','WAIVED')), completed_hours REAL, completed_months REAL, remarks TEXT, updated_by TEXT REFERENCES profiles(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS ux_non_unit_progress ON student_non_unit_progress(student_id, non_unit_requirement_id); CREATE INDEX IF NOT EXISTS ix_non_unit_progress_student ON student_non_unit_progress(student_id); -- ----------------------------------------------------------------------------- -- audit_logs — append only -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS audit_logs ( id TEXT PRIMARY KEY, actor_user_id TEXT REFERENCES profiles(id), actor_email TEXT, action TEXT NOT NULL, entity_type TEXT NOT NULL, entity_id TEXT, before_json TEXT, after_json TEXT, reason TEXT, program_id TEXT REFERENCES programs(id), created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS ix_audit_entity ON audit_logs(entity_type, entity_id); CREATE INDEX IF NOT EXISTS ix_audit_created ON audit_logs(created_at); CREATE INDEX IF NOT EXISTS ix_audit_actor ON audit_logs(actor_user_id); CREATE INDEX IF NOT EXISTS ix_audit_program ON audit_logs(program_id); -- ----------------------------------------------------------------------------- -- import_jobs -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS import_jobs ( id TEXT PRIMARY KEY, import_type TEXT NOT NULL CHECK (import_type IN ('CURRICULUM','STUDENT','ASSESSMENT')), file_name TEXT NOT NULL, status TEXT NOT NULL, total_rows INTEGER NOT NULL DEFAULT 0, accepted_rows INTEGER NOT NULL DEFAULT 0, rejected_rows INTEGER NOT NULL DEFAULT 0, uploaded_by TEXT REFERENCES profiles(id), error_summary_json TEXT, created_at TEXT NOT NULL, completed_at TEXT ); CREATE INDEX IF NOT EXISTS ix_import_jobs_created ON import_jobs(created_at); -- ----------------------------------------------------------------------------- -- curriculum_validation_issues — source discrepancies, never silently discarded -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS curriculum_validation_issues ( id TEXT PRIMARY KEY, curriculum_id TEXT NOT NULL REFERENCES curricula(id) ON DELETE CASCADE, code TEXT NOT NULL, severity TEXT NOT NULL CHECK (severity IN ('ERROR','WARNING','INFO')), message TEXT NOT NULL, details_json TEXT, acknowledged INTEGER NOT NULL DEFAULT 0 CHECK (acknowledged IN (0,1)), acknowledged_by TEXT REFERENCES profiles(id), acknowledged_at TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS ix_validation_curriculum ON curriculum_validation_issues(curriculum_id); -- ----------------------------------------------------------------------------- -- system_settings — key/value institutional configuration -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS system_settings ( key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at TEXT NOT NULL, updated_by TEXT REFERENCES profiles(id) );