Purpose and current migration model

Use this whenever a build can change SQLite tables, columns, indexes, constraints or schema-version evidence.

The current repository does not contain a conventional ordered directory of reversible up/down migration files. Instead:

• lib/db/create-schema.ts applies the current idempotent schema-creation/update logic;
• lib/db/schema-version.json defines the supported version, name and checksum;
• app_schema_migrations records the applied current version/checksum;
• pnpm db:migrate:rehearse and pnpm db:migrate are non-production/local-checkout tools;
• the exact Windows production root blocks those direct commands and uses deploy\windows\Install Update.cmd, which owns backup, rehearsal, migration and rollback guards.

Because there is no supported down-migration command, rollback means restoring a verified pre-migration backup and running the previous compatible app build. Keep both available until the change is accepted.

Use fictional demonstration data only.

Owners and approval

For account beta/production, the Change Author should not be the only person approving and verifying the migration.

Change record required before implementation

Record:

• change ID, purpose and affected tables/columns/indexes;
• current and target schema version/checksum;
• expected data transformation and defaults;
• protected records and invariants;
• forward compatibility and previous-build compatibility;
• expected runtime and required downtime;
• disk-space requirements for live database, temporary copy and safety backup;
• backup encryption/key availability;
• test/rehearsal plan;
• rollback trigger, previous build and backup generation;
• operator, approver and verifier;
• release window and communication/fallback.

Do not rely on a generated schema checksum alone to explain the change.

Protected invariants

At minimum, every migration must preserve or deliberately reconcile:

• school ownership and school-scoped foreign keys;
• active staff roles and account access;
• students and Current/Discontinued state;
• rehearsal attendance and guest/without-instrument meaning;
• lesson schedules and lesson attendance;
• Licence awards/progress history;
• Method Book progress and percussion streams;
• pass-off attempt rules/history;
• practice records;
• friend relationships, pending/expired invitation codes, and peer-privacy choices;
• Bonus Challenge results, reward settings, and redemption-request history;
• assessments, assignments, rubric marks, feedback, submissions, and file metadata;
• linked-session ownership, participants, invitations, state, and expiry;
• Daily Review sign-offs;
• teaching and account audit evidence;
• support, deletion and release-readiness records;
• random QR/barcode identifiers and narrow access boundaries.

If a change intentionally alters counts or meaning, document the exact expected difference and obtain approval before rehearsal.

Rehearsal behaviour

Run:

pnpm db:migrate:rehearse

The script:

1. opens the configured SQLITE_PATH, defaulting to data/band-licence.sqlite;
2. runs SQLite integrity and foreign-key checks;
3. records the current schema status and row counts;
4. uses SQLite backup to create a temporary copy under tmp;
5. applies createSchema to that copy;
6. confirms the copy matches the current version/checksum;
7. repeats integrity and foreign-key checks;
8. requires unchanged counts for school_profiles, students, attendance_records, lesson_attendance_records, pass_off_attempts and practice_sessions;
9. writes tmp/schema-migration-rehearsal-latest.json;
10. removes the temporary database.

This is valuable but not sufficient. It checks six protected counts, not every table, field value, report, permission or business rule. Add targeted automated tests and manual before/after queries for each affected area.

Local-pilot preflight

Before changing a live pilot database:

1. Finish Daily Review and stop data entry.
2. Confirm the exact working directory, build and SQLITE_PATH.
3. Confirm enough disk space for the source, backup and temporary rehearsal copy.
4. Create and verify a backup:

pnpm db:backup
pnpm db:verify-backup

5. Run the full test/build checks relevant to the release:

pnpm test
pnpm lint
pnpm typecheck
pnpm build

pnpm build does not open the configured application database. The repository wrapper first creates and verifies a disposable current-schema database under the operating system temporary directory, then runs every parallel Next.js build worker with automatic migration disabled. The temporary database is removed when the build exits. A direct next build is intentionally blocked before SQLite is opened; use the package command.

The reviewed Windows updater uses the same boundary but supplies its restored, migrated candidate copy explicitly. That exception requires all three values below, accepts only an existing regular file under the operating system temporary directory, verifies the current schema without migration, and never accepts the live database:

BAND_LICENCE_AUTO_MIGRATE=false
BAND_LICENCE_BUILD_DATABASE_CONFIRMATION=ISOLATED-TEMP-COPY
BAND_LICENCE_BUILD_SQLITE_PATH=<absolute path to the temporary candidate copy>

6. Run the migration rehearsal and inspect its JSON status:

pnpm db:migrate:rehearse

7. Compare affected-table counts and representative fictional records.
8. Confirm the pre-change build and chosen verified backup remain available.
9. Obtain go/no-go approval.

Stop if paths, backup key, schema version, protected counts or rollback artefacts are uncertain.

Dedicated host safeguards

On a dedicated private-network, account-beta or production-shaped host:

BAND_LICENCE_AUTO_MIGRATE=false

With automatic migration disabled, the app should refuse to start when the database version/checksum does not match the build. Keep staging and production separate with explicit SQLITE_PATH and BACKUP_DIR values and distinct encryption keys.

Do not:

• run a migration while the app or public-profile process is writing;
• point a production build at the live database or bypass the pnpm build wrapper;
• point a staging build at the production database;
• use the development database as the rollback copy;
• leave the only pre-migration backup on unprotected storage;
• assume an app build is backward compatible with a newer schema;
• drag/replace the live SQLite file while processes are running.

Migration execution

Local/private pilot

1. Stop the app and public-profile service for a controlled migration.
2. Capture the start time and operator.
3. Run:

pnpm db:migrate

4. Preserve command output and the named pre-migration backup.
5. Run:

pnpm release:check
pnpm deployment:check

6. Start the intended build.
7. Open /launch and confirm Database and Database Version are Ready.
8. Run the acceptance checks below.
9. Create and verify a post-migration backup only after acceptance.

What the migration command does

If a database exists, it:

• opens it read-only;
• blocks if SQLite integrity or foreign keys fail;
• creates pre-migration-YYYYMMDD-HHMMSS.sqlite or .sqlite.enc in BACKUP_DIR;
• uses the configured backup encryption;
• opens the live database with foreign keys on and a 5-second busy timeout;
• records before-version status;
• applies createSchema;
• confirms current version/checksum, integrity and foreign keys;
• writes metadata beside the safety backup with from/to version and encryption state.

It does not perform a business-level acceptance test or automatically restart/rollback the app.

Post-migration acceptance

The verifier should check:

• /launch database integrity and schema version;
• sign-in and assigned-school switching;
• Read-only denial and Teacher/Administrator boundaries;
• school, student and staff counts;
• representative Current and Discontinued student profiles;
• rehearsal and lesson attendance;
• schedules and exclusions;
• Licence/Method Book/pass-off/practice summaries;
• Daily Review;
• Support and Correction and deletion-review queues;
• QR profile narrow access;
• exports/reports affected by the schema change;
• account and teaching audit history;
• any new column defaults, transformed values and indexes.

Record actual counts and samples rather than “looks fine”. Use only fictional data.

Stop/go criteria

Proceed

• rehearsal passed on the intended source version;
• verified pre-migration backup and key are available;
• protected counts and targeted invariants match expectation;
• previous compatible build is available;
• downtime/fallback and owners are confirmed;
• no Critical/High test or access finding remains.

Stop

• integrity/foreign-key check fails;
• rehearsal changes an unexplained protected count;
• target version/checksum is unexpected;
• backup cannot be verified/authenticated;
• key or rollback build is unavailable;
• wrong database/environment path is suspected;
• app processes cannot be stopped;
• disk space or permission is insufficient;
• change requires a destructive transformation without a separately approved plan.

Failure classification and response

Do not repeatedly rerun a failed migration. First identify whether the failure is code, source-version, data-shape, lock, permission, disk, key or path related.

Rollback procedure

Rollback is restore plus previous build, not a down migration.

1. Keep all app/public-profile processes stopped.
2. Record the failure time, command output and affected build/version.
3. Preserve the failed database as evidence under an approved protected path; do not overwrite the pre-migration backup.
4. Identify the exact pre-migration-... file and matching JSON metadata.
5. Verify/authenticate it:

deploy\windows\Restore Verified Database.cmd

6. Confirm the previous app build supports the backup's recorded fromVersion.
7. Obtain rollback approval.
8. Restore with explicit paths and the guarded confirmation:

deploy\windows\Restore Verified Database.cmd

9. The restore command creates a further pre-restore safety backup when the target exists.
10. Run schema, release and representative record checks with the previous build.
11. Restart the previous build and verify role/school boundaries.
12. Reconcile any manual attendance collected during downtime.
13. Create and verify a new backup after stabilisation.
14. Record measured data loss (RPO), elapsed recovery (RTO), root cause and corrective action.

Rehearse the corrected migration on a copy before another attempt.

Destructive or complex transformations

For column/table removal, identity changes, split/merge, bulk backfill or new uniqueness:

• use an expand/backfill/verify/contract sequence where possible;
• keep old data readable until the new representation is verified;
• make backfills deterministic and restart-safe;
• pre-scan for values that violate new constraints;
• record rejected/ambiguous rows rather than silently dropping them;
• compare totals and content checks, not only row counts;
• test with old, boundary and malformed fictional records;
• treat final destructive contraction as a separate approved release.

The current schema tool does not provide a generic reversible transformation framework. Add and test explicit migration logic when createSchema alone cannot safely preserve data.

Migration evidence record

Keep:

• change ID, commit/build and operator;
• source/target versions and checksums;
• database/environment labels;
• preflight checks and disk-space result;
• backup filename, metadata, encryption and verification;
• rehearsal status and protected counts;
• targeted before/after queries;
• approval and start/end times;
• command results;
• acceptance tests;
• rollback artefacts and decision;
• RPO/RTO result if recovery occurred;
• residual risk, owner and next action.

The JSON under tmp is replaceable local status, not the only durable evidence.

Readiness and cadence

• Rehearse every schema-affecting release.
• Exercise rollback/recovery at the approved continuity cadence, not only after failure.
• Review migration documentation after a restore, schema-tool or hosting change.
• Keep pre-migration backup and previous build until the release acceptance/retention decision allows disposal.
• Run deploy\windows\Install Update.cmd on the exact Windows production host; never invoke the low-level migration command directly or depend on an unattended automatic production change.

See Protected Backups, the Production Deployment Checklist and Operations, Retention and Deletion.