D1 Migrations

D1 (SQLite) database migrations are located in the migrations/ directory at the monorepo root. Each migration is a sequentially numbered SQL file that transforms the database schema incrementally and reversibly. The Wrangler CLI manages the full lifecycle: creation, local application for testing, and remote application in production.

Commands

Command	Description
pnpm db:migrate:local	Applies all pending migrations on the local D1 database (--local)
pnpm db:migrate:remote	Applies all pending migrations on the remote D1 database (production)

Workflow

The typical migration cycle is:

2. Test locally with pnpm db:migrate:local, verifying that the resulting schema is correct
3. Apply in staging with pnpm db:migrate:remote --env staging to validate in a production-like environment
4. Apply in production with pnpm db:migrate:remote --env production

Always run migrations before deploying the worker. The worker expects the database schema to be up to date; running migrations after deploy can cause production errors during the inconsistency window.

Existing Migrations

The project has 13 migrations applied:

#	File	Description
1	0001_init.sql	Initial schema converted from PostgreSQL to D1/SQLite — core tables: users, students, attendance_events, event_log, audit_log, idempotency_ledger, classes, nuclei, class_students
2	0002_auth_sessions.sql	auth_sessions table for refresh tokens — multi-device support per user and compromised session detection
3	0003_student_fields.sql	Extended student fields — address, phones, status, guardians, notes, and customizable fields
4	0004_family_membership.sql	family_membership_status column in students table — family classification (member, attender, visitor)
5	0005_compromise_detection.sql	Compromised session detection support — revocation flag and refresh token reuse tracking
6	0006_roles.sql	roles table — customizable roles with granular permissions, replacing fixed role enum
7	0007_rate_limits.sql	rate_limits table — rate limit counter storage with sliding window and automatic expiration

Best Practices

• Write idempotent migrations using CREATE TABLE IF NOT EXISTS and ALTER TABLE with checks
• Prefer small, focused migrations — one schema change per file
• Include SQL comments explaining the purpose of the change and any breaking changes
• Test the migration locally with realistic data (use pnpm db:seed to populate the local database before testing)
• Coordinate schema migrations with worker deploys: migration first, deploy second

⚠️ Section under expansion.

---

Source: migrations/ + workers/CONTEXT.md