Database and Migrations

Database and Migrations

What the database holds, how its structure is upgraded, and how to back it up and restore it. References: docs/reference/data-model.md, docs/engineering/DATABASE_MIGRATIONS.md.

Contents

• Basics
• Tables
• Integrity rules
• How migrations run
• The migration steps
• When a migration stops startup
• Backups and restores
• Adding a migration
• Looking at the data

---

Basics

Property	Value
Engine	SQLite, through SQLAlchemy's async engine (aiosqlite)
File	backend/impress.db (from IMPRESS_DATABASE_URL, relative to backend/)
Writers	one process, the backend. Run one uvicorn worker
Times	every DateTime is naive IST
Foreign keys	enforced: PRAGMA foreign_keys=ON on every connection
Schema version	the schema_migrations table; reported by GET /health as schema_version (8 in v2.1)

Tables

erDiagram
    users ||--o{ class_sessions : "primary teacher"
    users }o--o{ class_sessions : "co-faculty (class_faculty)"
    users ||--o{ user_sessions : has
    users ||--o{ activity_logs : actor
    courses ||--o{ class_sessions : sections
    class_sessions ||--o| esp_devices : "gateway (device_id)"
    class_sessions ||--o{ quizzes : has
    class_sessions ||--o{ polls : has
    class_sessions ||--o{ student_enrollments : roster
    class_sessions ||--o{ attendance : has
    students ||--o{ student_enrollments : "enrolled in"
    quizzes ||--o{ quiz_questions : has
    quizzes ||--o{ quiz_answers : has
    students ||--o{ quiz_answers : answers
    polls ||--o{ poll_votes : has
    students ||--o{ poll_votes : votes
    esp_devices ||--o{ esp_devices : "relays (gateway_id)"
    esp_devices ||--o{ student_modules : "last relayed by"
    class_sessions ||--o{ student_modules : "seen in"
    firmware_artifacts ||--o{ firmware_deployments : "deployed by"
    firmware_deployments ||--o{ deployment_targets : has
    esp_devices ||--o{ deployment_targets : "updated by"

Loading

Table	Holds	Key points
users	sign-in accounts	unique username and email; bcrypt hash; role; active flag
user_sessions	sessions	token_hash = SHA-256 of the token (the raw token is never stored); timestamps; revoked flag
courses	the catalog	unique code
class_sessions	classes and sections	unique join code; unique classroom_code; primary teacher; gateway (device_id); active flag; course and section (unique together); timetable (JSON)
class_faculty	co-faculty	(class, user)
students	students	unique roll_number (10 alphanumerics, upper-case); soft-delete flag
student_enrollments	rosters	one per (class, student)
quizzes, quiz_questions, quiz_answers	quizzes	one question per (quiz, order); one answer per (quiz, question, student) and per (quiz, question, device)
polls, poll_votes	polls	one vote per (poll, student) and per (poll, device)
attendance	check-ins	per (class, enrollment)
activity_logs	the activity log	action, entity, details (JSON)
esp_devices	gateways and hubs	unique MAC; type; presence; telemetry; OTA state; diagnostics (#39); device key hash (#66)
student_modules	the module inventory (#40)	unique device_uid; enrollment; gateway; class; connected; last seen. No history
firmware_artifacts	the firmware registry (#35)	unique SHA-256; unique (target, version); status; channel; signer (#66)
firmware_deployments, deployment_targets	rollouts (#38)	strategy (JSON), state, idempotency key; per-device stage, state, attempts, versions, error
schema_migrations	applied migrations	version, name, time

Integrity rules

Rule	Enforced by
One answer per student per question; one vote per student per poll	unique indexes (and a friendly check before the insert)
One enrollment per student per class	unique index
One class per (course, section)	a partial unique index (only when both are set)
No reference to a missing row	foreign keys
Deleting a class removes its quizzes (with questions and answers), polls (with votes), attendance, enrollments and co-faculty links in one transaction; student modules and the gateway are kept, unlinked	services/records.py (delete_class_records), used by both the teacher and the admin routes
Hard-deleting a student removes their enrollments and attendance. Their answers and votes are kept anonymised (student_id = NULL), so results keep their totals; their module stays in the inventory without the enrollment number	services/records.py (erase_student)
Two identical requests at once: the database's unique index rejects the second one, which becomes a clean HTTP error, not a 500	commit_or_conflict() in database.py
A failed commit (database locked, I/O error) saves nothing, and the client is told to retry (503)	commit_or_conflict()

How migrations run

flowchart TD
    S["the backend starts"] --> R["read schema_migrations<br/>(created if missing)"]
    R --> P{"pending steps?"}
    P -- "no" --> OK["serve requests"]
    P -- "yes, and the file already<br/>holds app tables" --> B["copy impress.db →<br/>impress.db.bak-&lt;from&gt;-to-&lt;to&gt;-&lt;time&gt;"]
    P -- "yes, fresh database" --> C
    B --> C["create missing tables"]
    C --> L["for each pending step:<br/>BEGIN IMMEDIATE → step → record → COMMIT"]
    L -- "a step raises" --> X["ROLLBACK that step<br/>startup stops with the message"]
    L --> OK

Loading

Property	Detail
Fresh install	all tables are created with their indexes; every step is a no-op and is recorded; no backup
Existing database	a backup is copied first, then each step runs and is recorded
Atomic steps	each step and its schema_migrations row commit together or not at all. The runner issues BEGIN IMMEDIATE itself, because SQLite would otherwise auto-commit ALTER TABLE. IMMEDIATE also keeps a second process out
Failure	startup stops, with the step's message. It never continues on a half-migrated database
Idempotent	every step is safe to run on a database that already has the change

The migration steps

#	Name	Release	What it does	Effect on existing data
1	add_v2_columns	v2.1	adds the columns databases from before the migration framework lack	additive
2	repair_dangling_references	v2.1	PRAGMA foreign_key_check: a nullable reference to a missing row is set to NULL; a row whose required parent is gone is deleted (it was unreachable). Repeats until clean	repairs rows older code could leave behind; counts are logged
3	add_unique_constraints	v2.1	creates the unique indexes for answers, votes, enrollments and sections	duplicate answers, votes or enrollments: the earliest is kept (the rule the app always applied), with counts logged. Duplicate sections stop the upgrade (a human decides)
4	unique_question_order	v2.1	one question per (quiz, order)	duplicate positions are renumbered
5	add_diagnostics_columns	v2.1 (#39)	uptime, reset reason, boot count, lowest free heap, hub link, hub uptime, report time on esp_devices	additive, nullable
6	add_device_keys	v2.1 (#66)	api_key_hash, key_issued_at, key_confirmed_at on esp_devices, and an index	additive; every device starts on the shared key
7	add_firmware_signer	v2.1 (#66)	signer on firmware_artifacts	additive. Earlier images are unsigned: the previous parser refused anything after an image
8	add_quiz_question_started_at	v2.1 (#73)	question_started_at on quizzes: when the current question went live (quiz timer)	additive, nullable. A quiz mid-run during the upgrade starts counting on the timer's first tick

Unique indexes (step 3 and the models):

Index	Columns
uq_quiz_answers_quiz_question_student	quiz_answers(quiz_id, question_order, student_id)
uq_quiz_answers_quiz_question_device	quiz_answers(quiz_id, question_order, device_id)
uq_poll_votes_poll_student	poll_votes(poll_id, student_id)
uq_poll_votes_poll_device	poll_votes(poll_id, device_id)
uq_student_enrollments_class_student	student_enrollments(class_session_id, student_id)
uq_class_sessions_course_section	class_sessions(course_id, course_section) where both are set
uq_quiz_questions_quiz_order	quiz_questions(quiz_id, order_num)
uq_firmware_artifacts_target_version	firmware_artifacts(target, version)
uq_deployment_targets_deployment_device	deployment_targets(deployment_id, device_id)

SQLite treats NULLs as distinct, so answers or votes with no student and no device aren't constrained.

When a migration stops startup

The server log names the step and what to do. For example, two sections with the same course and section label:

1. Don't start the old code on the database again until you understand the problem.
2. Fix the data the message names. For duplicate sections, rename one (e.g. with the sqlite3 tool, after a backup).
3. Start again. The failed step rolled back, so it runs again from the start.

If you'd rather go back, restore the backup (below) and run the previous release.

Backups and restores

Automatic: before applying any pending step to an existing database, the backend writes impress.db.bak-<from>-to-<to>-<timestamp> next to it. These are git-ignored, because they contain personal data. Delete old ones once an upgrade is confirmed.

Scheduled backups (safe while the server runs):

sqlite3 /opt/imPress/backend/impress.db ".backup '/backups/impress-$(date +%F).db'"

Restore:

sudo systemctl stop impress
cd /opt/imPress/backend
cp impress.db impress.db.before-restore           # keep the current state
cp /backups/impress-2026-10-08.db impress.db       # or an impress.db.bak-* file
# run the code release that matches that backup's schema (or a newer one, which migrates it)
sudo systemctl start impress
curl -s http://127.0.0.1:8000/health              # check schema_version

Starting newer code on an older backup simply migrates it again.

Adding a migration

1. Append (next_version, "short_name", step) to MIGRATIONS in backend/app/migrations.py. Never edit or renumber a released step.
2. Make it idempotent: use IF NOT EXISTS, and check PRAGMA table_info before adding a column (the _add_columns helper does this). It also runs on fresh databases.
3. Use the same names as the model. If the change is also declared on a model (an index, a column), use the same name, so fresh and migrated databases end up identical.
4. Decide what happens to existing data that blocks the change: either a deterministic repair matching the app's rules (log the counts), or raise MigrationError("… what to do …").
5. Test it in backend/tests/test_migrations.py: build the old shape with plain sqlite3, migrate, check the result, and check that a second run is a no-op. The legacy-database test drops the tables that are new in this release, so the upgrade path must recreate them.

For renames and removals, prefer expand → migrate data → contract: add the new column, fill it, switch the code, and drop the old column in a later release. A step that drops data can't be undone; the automatic backup is the way back.

Looking at the data

sqlite3 -readonly /opt/imPress/backend/impress.db
sqlite> .tables
sqlite> SELECT version, name, applied_at FROM schema_migrations;
sqlite> SELECT mac_address, device_type, firmware_version, is_connected, last_seen FROM esp_devices;
sqlite> SELECT target, version, status, substr(signer,1,16) FROM firmware_artifacts;

Read-only access is safe while the server runs. Don't write to the database while it runs, except through the API.