Database Schema

Database Schema

Single SQLite file (DB_PATH, default data/scores.db), opened with node:sqlite's DatabaseSync. Canonical definition: server/db.js — the base schema is one db.exec block; later changes are guarded ALTER TABLE statements near the bottom, some followed by a one-time backfill gated on the ALTER succeeding.

Conventions

• PRAGMA foreign_keys = OFF. Every REFERENCES below is documentation. Cascades are done in the route layer. Triggers still fire — WBL uses them to enforce the Do Now cap and Exit Slip immutability.
• Epoch-ms integers (Date.now()) for timestamps; TEXT YYYY-MM-DD for calendar dates.
• student_id is the district number in canonical form (normalizeStudentId). WBL tables store it normalized; class_students stores the raw PowerSchool number.
• IDs: INTEGER PRIMARY KEY AUTOINCREMENT unless noted.

---

Core: accounts & practice scores

Table	Key columns	Notes
users	user_key PK, email, first_seen	one row per signed-in app account (student or teacher).
sessions	token PK, user_key, created_at, last_seen	student session tokens.
teacher_sessions	token PK, user_key, created_at, last_seen	teacher session tokens (the "Copy Token" value).
kenken_scores	user_key, score REAL, size, class_id (nullable), submitted_at	one row per completed puzzle. class_id = the class the student attributed it to; NULL = "not for a class"/legacy.
sat_scores	user_key, correct 0/1, domain_idx 0–3, skill, difficulty, assessment (sat/psat-nmsqt/psat89/unknown), class_id, submitted_at	one row per answered SAT-English question.
sat_math_scores	same shape as sat_scores	one row per answered SAT-Math question.
sat_serves	(user_key, subject english/math) PK, served_at	when /api/sat[-math]/next last served the student a question. /score discards answers that arrive under 10 s later, and deletes the row once used (server/satPace.js).
measurement_scores	user_key, class_id, tier 1–8, target_32, guess_32 (lengths in 1/32″), correct 0/1, time_ms, score, score_version, submitted_at	one row per Ruler Game answer. score comes from js/measurement-core.js. At startup, rows with a score_version older than CONFIG.SCORE_VERSION are re-scored.
measurement_progress	user_key PK, tier, tier_started_at	each student's current Ruler Game tier (global, not per class). The rolling window for tier movement only counts attempts at tier since tier_started_at.
projection_problems	id, user_key UNIQUE, tier, task (draw/build), shape_code, issued_at (ms)	each student's one open Projections problem: handed out, not yet answered. GET /api/projection/next returns it again until it's answered, so a reload keeps the problem and its timer. Replaced after 2 hours or when the student's tier has changed.
projection_scores	user_key, class_id, tier 1–8, task (draw/build), shape_code (e.g. 333-mjom), answer (the submitted drawing or build, JSON), hidden_graded 0/1, correct 0/1, time_ms (timed by the server), score, score_version, submitted_at	one row per Projections answer. score comes from js/projection-core.js; older score_version rows are re-scored at startup.
projection_progress	user_key PK, tier, tier_started_at	each student's current Projections tier (global), same rules as measurement_progress.
question_reports	question_id, subject (math/english), user_key, reported_at, UNIQUE(question_id,user_key)	2+ distinct reporters ⇒ question suspended.
suppressed_questions	(question_id, subject) PK, suppressed_at	teacher-driven permanent removal from the pool.
schema_migrations	name PK, applied_at	marks one-time data migrations as done (english-bank-rebuild-2026-09, math-image-choices-2026-09 — one per subject's report clear). Older migrations instead gate on an ALTER TABLE succeeding.

class_id on the three original score tables was added by migration (measurement_scores has had it from the start); a one-time backfill attributed existing rows for students in exactly one class.

sat_scores.skill is '' on Standard English Conventions (domain_idx 3) rows recorded before the September 2026 English bank rebuild — the old bank had no Conventions skills, and scores don't record the question, so those rows can't be split between the two real skills. They are left as-is: /api/sat/next reads them as history for both skills and the dashboards label them "Conventions (before skill split)". See SAT Question Pipeline.

---

Classes, rosters, settings

Table	Key columns	Notes
classes	id, teacher_key, name, created_at, ps_section_id, assessment_type (sat/psat-nmsqt/psat89), sat_english_domains, sat_math_domains (CSV of 0–3, or NULL = all), required_activity (a comma-separated list of activity keys: kenken, sat-math, sat, measurement, projection, e.g. kenken,measurement. Legacy combo names both/sat-both/kenken-math/all/either are still read as-is. Parsing and validation live in js/activities.js.), required_kenken_count, required_sat_count, required_sat_math_count, required_measurement_count, required_projection_count, sat_requirement_mode (correct default / accuracy), sat_accuracy_pct (1–100, default 50), sat_accuracy_window (default 3; accuracy is measured over the last N answers in accuracy mode, see server/satAccuracy.js), how_cadence (daily/weekly), how_weekly_log (0/1)	per-class daily-practice config, each required_*_count a per-day target (see How Grading Works). The required_* columns replaced the teacher-wide assignment_settings row (still present, unread). how_* drive the non-WBL Habits of Work cadence.
class_students	id, class_id, student_id (raw), student_name, user_key (link to users, nullable), ps_dcid, enrolled_on, exited_on, exit_reason, UNIQUE(class_id, student_id)	one roster row. exited_on IS NULL = active. Dates clamp which days of a grading window a student is graded on.
teacher_profile	teacher_key PK, name	display name.
gradebook_settings	teacher_key PK, assignment_max_score, completion_score_pct, no_submission_score_pct, wbl_credential_max_score, wbl_holistic_max_score, wbl_transfer_max_score, mc_* (legacy)	teacher-wide. WBL max-points fields default 50/20/10.
assignment_settings	teacher_key PK, required_*	legacy — superseded by per-class columns on classes; still written by an old form path but not read by grading.

---

Legacy (renamed, inert)

legacy_microcredentials, legacy_mc_checkpoints, legacy_mc_class_assignments, legacy_mc_checkpoint_sync, legacy_mc_completions, legacy_mc_subtasks, legacy_mc_subtask_completions, legacy_daily_rubric — the retired micro-credential / Daily Rubric system, renamed rather than dropped so the data survives as an audit trail. Nothing reads them at runtime; WBL supersedes them.

---

Work-Based Learning

Program, catalog, config

Table	Purpose
wbl_programs	The WBL environment. Ownership (teacher_key) lives here only. qc_max_weeks_unchecked, shareable, archived_at.
wbl_class_programs	(class_id, program_id) — many-to-many link of gradebook classes to programs.
wbl_program_imports	Records a catalog copied from another program (snapshot/tracked mode) for drift detection.
wbl_credentials	name, min_holistic_tier (output bar for a demonstration to count), order_idx, source_credential_id.
wbl_skills	Skill lineage — stable identity, carries no definition. slug unique per program.
wbl_skill_versions	The definition. status draft/published/retired, is_current (partial unique index), version_no. Published versions are immutable.
wbl_skill_criteria	The mastery checklist, owned by a version.
wbl_credential_skills	(credential_id, skill_id) + required_demonstrations — requirements reference the lineage, so v1 evidence still counts after v2.
wbl_qc_criteria	Per-program QC Spot Check criteria.
wbl_soft_skills	Fixed vocabulary: 3 dispositional + 2 transfer, by code.
wbl_holistic_tiers	Fixed vocabulary: not_shippable/rework/meets/exceeds + rank.
wbl_holistic_tier_points	(program_id, tier) → points_pct. Seeded 0/50/85/100, editable per program.
wbl_skill_openings	(skill_id, soft_skill_code) — Phase-1 dispositional scaffolding (which units have natural openings).

Enrollment & phase

Table	Purpose
wbl_program_enrollments	(program_id, student_id) + pathway_year, enrolled_on, exited_on. Enrollment as a first-class fact, not inferred from class rosters.
wbl_phase2_prereqs	(program_id, credential_id) — credentials that gate Phase 2.
wbl_student_phase	(program_id, student_id) + computed_phase, override_phase. Effective phase = COALESCE(override_phase, computed_phase).

Work Events & assessment

Table	Purpose
wbl_work_events	The shared job. status active/complete/cancelled, opened_on/closed_on. No class_id — participants carry their own.
wbl_work_event_skills	(work_event_id, skill_id) — advisory "planned skills," not a gate.
wbl_work_event_participants	One row per student on a job. class_id, phase_at_start, joined_on/left_on. Every assessment points here.
wbl_skill_assessments	Lens 1 (hard skills). skill_version_id pinned at assessment time. result not_demonstrated/developing/mastered. UNIQUE(participant_id, skill_id).
wbl_skill_criterion_results	(assessment_id, criterion_id) → met.
wbl_skill_checks	Direct skill credit outside any Work Event (roster-wide standing competencies). Counts as one demonstration.
wbl_qc_checks	Lens 2a. UNIQUE(program_id, student_id, iso_week) is the weekly cap.
wbl_qc_check_results	(check_id, criterion_id) → pass/fail/na.
wbl_holistic_calls	Lens 2b — the verdict. tier, rationale. UNIQUE(participant_id).

Soft skills

Table	Purpose
wbl_do_nows	Session-entry pick. UNIQUE(program_id, student_id, date) — one per day (trigger-enforced cap).
wbl_do_now_skills	(do_now_id, soft_skill_code) — the 1–2 dispositional picks.
wbl_exit_slips	Session-departure reflection. Immutable (no updated_at; a trigger blocks UPDATE). UNIQUE(do_now_id, soft_skill_code).
wbl_exit_slip_verifications	Append-only confidence gradient. confidence instructor_verified/instructor_witnessed, plus rating 0–4 (added by migration — feeds the Habits of Work score). No row = student-claimed.
wbl_exit_slip_voids	UNIQUE(exit_slip_id) — junk escape hatch; the slip is never mutated.
wbl_dispositional_feedback	Feedback delivered forward via the next Do Now (delivered_do_now_id).
wbl_transfer_claims	Lens 3b. kind application/extension, cited_* (may cross programs), verdict, score. UNIQUE(participant_id, kind).
wbl_called_outs	(program_id, student_id, date) — voids a UNV attendance code for the Holistic attendance blend.

Awards & evidence

Table	Purpose
wbl_credential_awards	UNIQUE(credential_id, student_id) — keyed to student-within-program, not class, so awards survive section/year changes. source auto/manual/migrated, revoked_at.
wbl_award_evidence, wbl_award_skill_check_evidence	The demonstration trail behind an award.

Attendance & PowerSchool sync

Table	Purpose
wbl_attendance	(class_id, student_id, date) + code. Imported from PowerSchool by the extension — not WBL-gated, despite the table name; any class can have rows. Also the meeting-day calendar for daily-practice grading (a DISTINCT date query is the list of days the section met) and the source of per-student codes for daily-practice absence exemption.
wbl_credential_sync	(credential_id, class_id) → ps_assignment_id, ps_assignmentsection_id, state (not_started/in_progress/due). Old sync_enabled column kept but unread.
wbl_credential_skill_sync	(credential_id, skill_id, class_id) → same shape + state.
wbl_work_event_sync	(work_event_id, class_id) → PS assignment IDs.
wbl_transfer_sync	(class_id, kind) → PS assignment IDs. Legacy — superseded by wbl_habits_sync.
wbl_habits_sync	(soft_skill_code, class_id) → PS assignment IDs. Covers all 5 soft skills; a migration copied existing transfer rows in.

---

Habits of Work (non-WBL classes)

A parallel, class_id-scoped Do Now / Exit Slip flow for classes not linked to a WBL program. Deliberately reuses none of the wbl_* tables. Catalogue (gravitas / dignitas / pietas + bullets) is hard-coded in server/how/catalog.js, not a table. Additive; no backfill. Full mechanics: Habits of Work Internals.

Two columns on classes: how_cadence ('daily' default / 'weekly') and how_weekly_log (0/1, only meaningful when how_cadence = 'weekly'). Together they give three modes: daily, weekly, weekly_log.

Table	Key columns	Notes
how_do_nows	id, class_id, student_id, date, cadence, submitted_at, UNIQUE(class_id, student_id, date)	One per student per class per period — date is the calendar day (daily) or that ISO week's Monday (weekly). cadence snapshots the class mode at creation.
how_do_now_skills	(do_now_id, category) PK, sub_bullet	The chosen focus bullet(s): one row (daily) or three, one per category (weekly).
how_exit_slips	id, do_now_id UNIQUE, class_id, student_id, date, submitted_at	One per Do Now. Immutable — trg_how_exit_slip_no_update blocks UPDATE.
how_exit_slip_skills	(exit_slip_id, category) PK, goal_rating 1–4, evidence, improve	Student's own per-category reflection. evidence = '' in weekly_log. Immutable (trigger).
how_exit_slip_ratings	(exit_slip_id, category) PK, rating 1–4, rated_by, note, rated_at	Teacher's per-category verdict. Editable — upsert. Feeds the PS sync. No Verified/Witnessed gradient.
how_exit_slip_voids	exit_slip_id PK, reason, voided_by, voided_at	Voids the whole slip; the slip is never mutated. DELETE = undo.
how_evidence_entries	id, do_now_id, category, date, note, created_at	weekly_log only. Append-only (trg_how_evidence_no_update). date clamped to the Do Now's Mon–Sun week.
how_sync	(class_id, category, kind) PK, ps_assignment_id, ps_assignmentsection_id	kind ∈ weekly / standing. PS assignment IDs the extension reports back — the server never talks to PowerSchool. Mirrors wbl_habits_sync.

server/db.js carries a one-time guard: if a dev DB still has the pre-release flat how_* shape (a category column on how_do_nows), it drops and rebuilds the how_* tables. Nothing shipped on the old shape.

---

IT Help Desk

Independent of the education apps; served on the it. subdomain.

Table	Purpose
it_students / it_staff	People directories (student_id / staff_number PK).
it_assets	Devices. serial_number unique, assigned_type (student/staff/cart), device_status, repair_status.
it_tickets	Help-desk tickets. category, priority, status, linked_asset_id.
it_ticket_events	Append-only per-ticket audit log (created/status_change/note).