Single file, local SQLite
All data lives in interview_data.db. Foreign keys are enforced on every connection. Never write to competency_grades directly — always use grade_record().
semesters
id INTEGER PK
name TEXT
abbr TEXT UNIQUE
notes TEXT
classes
id INTEGER PK
semester_id FK
name TEXT
abbr TEXT UNIQUE
semester_start TEXT
students
id INTEGER PK
class_id FK
first_name TEXT
last_name TEXT
email TEXT
competencies
id INTEGER PK
class_id FK
name TEXT
category TEXT
display_order INT
grade_attempts ⭐
id INTEGER PK
student_id FK
competency_id FK
session_id FK
grade TEXT
source TEXT
attempted_at TEXT
duration_seconds INT
attempt_number INT
approaching_only INT
notes TEXT
competency_grades
id INTEGER PK
student_id FK
competency_id FK
grade TEXT
source TEXT
grade_locks
id INTEGER PK
student_id FK
class_id FK
letter_grade TEXT
week_number INT
attempt_count INT
grade_thresholds
id INTEGER PK
class_id FK
letter_grade TEXT
sort_order INT
rule_json TEXT
interview_sessions
id INTEGER PK
student_id FK
set_id FK
grade TEXT
eval_notes TEXT
conducted_at TEXT
history_json TEXT
questions / followups
id INTEGER PK
uid TEXT UNIQUE
title TEXT
content TEXT
content_type TEXT
sets / set_questions
id INTEGER PK
class_id FK
name TEXT
competency_id FK
competency_link_groups
id INTEGER PK
name TEXT
Links comps across classes
Key relationships
semesters → classes → students
↘ competencies → grade_attempts ← students
↘ competency_grades ← students
↘ grade_locks ← students
sets → set_questions → questions → followups
competency_link_groups → competency_link_members → competencies