Data model¶
All data lives in a single Cloudflare D1 (SQLite) database. This page describes the entities in product terms — not every column, but what each thing is and how they connect.
The big picture¶
erDiagram
USERS ||--o{ ENROLLMENTS : "joins"
CLASSES ||--o{ ENROLLMENTS : "has"
USERS ||--o| STUDENT_PROFILES : "has"
USERS ||--o| TEACHER_PROFILES : "has"
CLASSES ||--o{ SCHEDULE_SESSIONS : "generates"
SCHEDULE_SESSIONS ||--o{ ATTENDANCE : "records"
USERS ||--o{ TUITION : "owes"
TUITION ||--o{ TUITION_PAYMENTS : "receives"
CLASSES ||--o{ TUITION : "bills"
USERS ||--o{ SKILL_ATTEMPTS : "makes"
ASSIGNMENTS ||--o{ ASSIGNMENT_TARGETS : "targets"
USERS ||--o{ ASSIGNMENT_TARGETS : "receives"Entities by domain¶
People & structure¶
| Entity | What it is |
|---|---|
| Users | Every account, with a role (student / teacher / admin) and an active flag. |
| Student profiles / Teacher profiles | Extra detail — entry/current/target band and parent contact for students; specialties for teachers. |
| Classes | A course: name, code, level, target band, capacity, tuition fee, weekly schedule, assigned teacher, status. |
| Enrollments | Which student is in which class (active/dropped). Enrolling now also raises a tuition charge from the class fee. |
Schedule & attendance¶
| Entity | What it is |
|---|---|
| Schedule sessions | Individual class meetings, generated from a class's weekly schedule. |
| Attendance | Per-student, per-session mark: present / late / absent / makeup, with who marked it. |
Money (manual bookkeeping)¶
| Entity | What it is |
|---|---|
| Tuition | A charge owed by a student (amount, paid amount, due date, status: unpaid / partial / paid / overdue). Overdue is derived from the due date at read time. |
| Tuition payments | Each payment recorded against a charge; also writes a matching cashflow "income" row. |
| Cashflow | The center's cash in/out ledger (income/expense with category), independent of any student. |
Learning content & results¶
| Entity | What it is |
|---|---|
| Reading / Listening / Writing / Speaking tests | The question bank content per skill, each publishable or draft. |
| Materials | Syllabus, slides, handouts with inline content and visibility. |
| Skill attempts | Every student submission across the 4 skills — answers, band, status, feedback, and behavior signals, all in one row. |
| Assignments / Assignment targets | A homework assignment and its per-student progress (status, band, what's done). |
| Content levels / Student levels | The 8-level gating system. |
| Student activity | A per-student feed of everything they did, powering the profile timeline. |
AI & settings¶
| Entity | What it is |
|---|---|
| AI usage | Every metered AI call (feature, units) — the basis for the monthly budget caps and the manager's usage report. |
| AI tutor messages | Saved tutor conversations. |
| App settings | Center-wide toggles: AI Tutor on/off, pronunciation cap, etc. |
Two design choices worth knowing¶
Behavior rides inside the attempt
Integrity and behavior signals (time, tab-switches, paste attempts, audio play/seek) are stored inside the attempt's feedback, not in a separate table — so adding richer tracking needed no schema change.
Overdue is computed, not stored
Tuition "overdue" status is derived from the due date when the ledger is read (in the center's local time), so it's always current without a nightly job. Charges are stored as unpaid / partial / paid.
Indexes & integrity¶
Secondary indexes cover the hot query paths (sessions by class and date, enrollments and attendance by student, attempts by user and test) so lists stay fast as history grows. D1 does not enforce foreign keys at runtime, so referential cleanup (e.g. removing a test's satellite rows) is handled in application code and migrations.
Backups¶
D1 provides point-in-time recovery (Time Travel), so the database can be restored to an earlier moment — the practical safety net against a bad bulk operation. See Deploy & runbook.