Purpose and current capability

This guide covers the SQLite backup, verification, rehearsal and restore tooling already in the repository.

During the teacher-controlled non-production local pilot, pnpm db:backup creates a timestamped standard .sqlite file unless backup encryption is configured. On the dedicated Windows production host, direct pnpm db:* backup, verification, restore and migration commands are intentionally blocked; use the protected launchers in deploy\windows so the DPAPI-protected S-drive key exists only in the bounded child operation.

The app does not schedule backups, copy them off the host, apply a retention policy, monitor a remote store or guarantee a recovery time. Those controls must be operated and evidenced separately.

Use fictional demonstration data only.

Owners and privileged access

Anyone who can read the SQLite database, backup file or encryption key is privileged outside the app's role matrix. Limit that access and review it separately.

The ACSC Essential Eight backup guidance recommends coordinated backups, restore testing and restrictions that prevent unprivileged users modifying or deleting backups.

Paths and files

Timestamps in filenames use Australia/Brisbane. The backups folder is intentionally ignored by git; git is not a backup service.

Metadata records the source/backup path, creation time, display timezone, encryption state, size and, for app-created backups, verification result. Metadata does not contain the encryption key.

Windows production backup broker

On the dedicated Windows host, application-triggered and automated backups cross a fixed S-drive broker boundary:

• Band Licence - Protected Backup Broker runs as LocalSystem and is the only scheduled task allowed to invoke the DPAPI operation helper. The DPAPI ciphertext, protected helper temporary directory, and private storage marker are not readable by the app's NETWORK SERVICE identity.
• S:\Band Licence Backup Broker\Requests is an ingress-only directory. The app identity and exact GitHub runner service SID may create ordinary request files and write those child files, but cannot create subdirectories, delete entries, change the DACL, or take ownership.
• The SYSTEM broker accepts only the fixed, bounded Backup request contract. It writes authenticated results under S:\Band Licence Backup Broker\Receipts and encrypted generations under S:\BandLicenceBackups. Requesters have read/execute access to those two locations, not write or delete access.
• The SYSTEM broker normally retains canonical receipts for 48 hours and performs bounded, no-follow cleanup. If a request surge exceeds the 4,096 receipt steady-state limit, it first evicts the oldest non-active receipts that are at least one hour old; young receipts and currently queued request IDs remain protected. Clients leave receipts in place for that protected cleanup and must not rename or delete request or receipt files.

Automation that needs a fresh verified generation must invoke the installed client, not the DPAPI helper directly:

powershell.exe -NoProfile -NonInteractive -ExecutionPolicy Bypass -File "S:\Band Licence App\deploy\windows\Request-BandLicenceProtectedBackup.ps1" -TimeoutSeconds 300

The client returns a bounded JSON receipt and rechecks the fixed backup path, file size, SHA-256 digest, metadata digest, and freshness. The protected readiness wrapper converts that result into BAND_LICENCE_PROTECTED_BACKUP_READINESS and the associated verified-file environment values for the immediately launched readiness check. Those values are operational evidence, not an authentication token. The security boundary is the protected Windows ACL topology, exact installed-file hashes, and the broker/helper LocalSystem identity checks.

Configure authenticated encryption

Generate a long random secret on the intended host:

node -e "console.log(require('node:crypto').randomBytes(32).toString('hex'))"

Set it outside source control:

BAND_LICENCE_BACKUP_ENCRYPTION_KEY=the-generated-value

The configured value must be at least 32 characters. Current implementation details:

• AES-256-GCM encryption and authentication;
• a random 16-byte salt and 12-byte nonce for every file;
• a 32-byte key derived with scrypt;
• authenticated file header and 16-byte authentication tag;
• encrypted output and temporary decrypted files written with owner-only mode where supported;
• a temporary plaintext database used only for verify/restore and removed in cleanup;
• a wrong key or modified encrypted file fails authentication.

This protects the backup file contents but does not:

• protect an unlocked/running host;
• replace FileVault/BitLocker and operating-system permissions;
• create a second copy;
• protect the key if stored beside the backup;
• prevent an authorised operator deleting all generations;
• set a retention period.

Use different keys, database paths and backup directories for staging and production. Never place a key in git, screenshots, support notes, command history examples or backup metadata.

Recovery objectives are decisions

Define:

• Recovery Point Objective (RPO): maximum approved amount of teaching-record time that may be lost;
• Recovery Time Objective (RTO): maximum approved elapsed time to restore the required pilot service after a declared outage.

Do not claim an RPO shorter than the actual backup cadence. With manual end-of-day backups, an unapproved midday failure can lose changes since the last successful backup.

Record:

The NIST contingency-planning guidance is a useful source for aligning recovery objectives, alternate/manual processing and exercises with business impact. It is not a Band Licence availability promise.

Create and verify

Run after each live lesson/rehearsal day and before/after material roster, card, settings or update work:

deploy\windows\Backup Database.cmd
deploy\windows\Verify Backup.cmd

The backup command:

1. opens the configured source database read-only;
2. uses SQLite's backup API to create a temporary consistent copy;
3. encrypts it when the key is configured, otherwise renames it to a standard SQLite file;
4. removes partial temporary/output files on failure;
5. writes matching JSON metadata.

The command-line backup script writes metadata but verification is a separate command. The in-app Administrator backup path creates and immediately verifies its backup.

The verify command:

• selects BACKUP_FILE or the newest recognised file in BACKUP_DIR;
• authenticates/decrypts encrypted files;
• runs SQLite integrity_check;
• verifies that the students table exists;
• reports protection, size, age and timezone.

Verification does not prove that all expected business records are present or current. Compare the backup date with Daily Review and the approved RPO.

On a non-production local-pilot copy only, a particular generation can be selected with:

BACKUP_FILE=backups/band-licence-YYYYMMDD-HHMMSS.sqlite.enc pnpm db:verify-backup

Backup evidence

For each required backup, record:

• filename and matching metadata filename;
• source environment/database label;
• created time and operator;
• encrypted or standard local file;
• verification command time/result;
• size and expected teaching-date coverage;
• second-copy status;
• applicable retention/hold ID;
• failure/follow-up owner.

Do not record the encryption secret. A file existing is not evidence that it can be restored.

Restore rehearsal

Run:

deploy\windows\Rehearse Database Restore.cmd

On a non-production local-pilot copy only, choose a specific backup with:

BACKUP_FILE=backups/band-licence-YYYYMMDD-HHMMSS.sqlite.enc pnpm db:restore:rehearse

The rehearsal:

• leaves the live database unchanged;
• authenticates/decrypts the chosen backup when needed;
• copies it to a temporary SQLite file;
• runs SQLite integrity checking;
• requires core school, account, student, ensemble, rehearsal, attendance, lesson, Method Book, pass-off and Daily Review tables;
• prints current-versus-restored counts for schools, students, rehearsal attendance, lesson attendance, pass-offs and Daily Reviews;
• writes the latest outcome to tmp/restore-rehearsal-latest.json;
• removes the temporary database.

The script displays current and restored counts but does not require them to match, because the backup can legitimately be older. The verifier must explain differences against the backup time and RPO.

Pass criteria:

• authentication/integrity succeeds;
• every required table exists;
• counts and sample records match the expected recovery point;
• no live record changes;
• elapsed time and operator are recorded;
• any gap is within the approved RPO.

Full RPO/RTO recovery exercise

The normal rehearsal proves that a backup opens; it does not prove that people can restore a service within the RTO. Run the following only from a separate non-production checkout in an isolated exercise environment, never from S:\Band Licence App and never against live SQLITE_PATH.

1. Declare a fictional outage scenario and start the RTO clock.
2. Record the last known-good business time and calculate the target RPO cutoff.
3. Select a verified backup without relying on the live host's database.
4. Set an explicit isolated target such as tmp/recovery-exercise.sqlite and a non-live backup directory.
5. Restore using the guarded command:

SQLITE_PATH=tmp/recovery-exercise.sqlite BACKUP_FILE=backups/band-licence-YYYYMMDD-HHMMSS.sqlite.enc CONFIRM_RESTORE=YES pnpm db:restore

6. Open the restored copy with an isolated/staging app process or trusted SQLite check; do not point the normal live service at it.
7. Confirm database integrity, schema compatibility and representative fictional school/student/attendance/progress records.
8. Test sign-in, selected-school boundaries, check-in read views and Daily Review against the exercise copy.
9. Stop the clock when the agreed service-verification point is met.
10. Record actual recovery-point loss, elapsed recovery time, manual-fallback implications and every failed step.
11. Remove the exercise database under the approved temporary-data rule after evidence is recorded.

If the target RPO/RTO is missed, do not tick Restore Rehearsal/Operations readiness as complete without a risk owner and corrective plan.

Real restore procedure

Use a real restore only after explicit approval.

1. Stop the app server and any public-profile process.
2. Record outage/restore approval, live database path and chosen backup.
3. Verify the backup and key.
4. Preserve relevant logs and failure evidence.
5. Confirm BACKUP_FILE and SQLITE_PATH resolve to different files.
6. Run:

deploy\windows\Restore Verified Database.cmd

The restore command:

• refuses to run without CONFIRM_RESTORE=YES;
• refuses a missing source or a source equal to the target;
• creates and integrity-checks a standalone online snapshot of the restore source;
• acquires a SQLite writer lock and creates a pre-restore-... online safety snapshot that includes committed WAL pages;
• checkpoints the live WAL, switches to standalone DELETE journal mode and proves exclusive access before replacement;
• encrypts the safety backup when encryption is configured, then reopens/authenticates that exact final artifact and repeats integrity and foreign-key checks;
• removes stale target -wal and -shm files before and after atomically renaming the staged snapshot into place;
• removes decrypted temporary material;
• integrity and foreign-key checks the restored target;
• instructs the operator to restart the app.

On Windows production, use Restore Verified Database.cmd rather than invoking the helper alone. The wrapper holds both production mutexes, blocks automated deployment/recovery with the durable maintenance marker, disables all current Band Licence tasks, proves both writer ports closed, creates a separate verified encrypted recovery point after quiescence, rehearses migrations, and requires the canonical teacher and QR health/route-isolation policy before clearing the marker.

After restore:

1. run integrity/schema/release checks;
2. start the intended build;
3. inspect /launch;
4. verify selected school, role boundaries and representative records;
5. reconcile manual attendance entries made during the outage;
6. create and verify a new post-recovery backup;
7. record RPO/RTO results and close only after independent verification.

Do not repeatedly restore different generations to the live target without a new decision and evidence.

Key custody and rotation

Maintain:

• named primary and recovery custodians;
• separate approved storage for the recovery copy;
• a tested process to retrieve the key during an exercise;
• an inventory mapping backup generations to key versions without recording secret material.

Before rotation:

1. verify the newest backup with the old key;
2. retain the old key while any retained backup requires it;
3. configure the new key;
4. create, verify and rehearse a new encrypted backup;
5. update the key-version inventory and custody evidence;
6. destroy the old key only when no retained/held backup depends on it.

Suspected key disclosure is a security incident. Create a new protected generation and follow the incident plan; do not merely rename the key.

Retention, second copies and destruction

• The app does not rotate or delete backups automatically.
• Define generations and expiry in the retention decision register.
• A local same-host copy cannot protect against loss/theft of that host.
• Use only a school-approved second location. Do not casually copy student databases to personal cloud storage or removable media.
• Separate backup/restore ability from ordinary app accounts where practical.
• Keep retained backups immutable from unprivileged users.
• The schema-24 cutover uses two explicit phases. The authenticated one-time controller first completes storage migration so the full canonical S-drive control topology and immutable application ACL exist. It then runs the exact provisioner from its verified inner update package before any updater/helper invocation or pre-touch backup:

powershell.exe -NoProfile -ExecutionPolicy Bypass -File "S:\Band Licence App\deploy\windows\Install-BandLicenceProtectedBackupKey.ps1" -StageCiphertextForCutover -CutoverStagingConfirmation STAGE-DPAPI-CIPHERTEXT-RETAIN-LEGACY

Do not substitute a provisioner from the live application tree or another checkout. This narrowly gated staging mode accepts only a matching Machine/current-User legacy key, validates the migrated protected S: authority, writes and decryptability-verifies its DPAPI LocalMachine ciphertext, retains both legacy values, and exits. It is idempotent only while that matching legacy key remains available. It deliberately skips the helper probe, backup, verification, restore rehearsal, and every removal path, and must not be recorded as completed rollout verification.

• Before the updater mirrors guarded schema-24 source, the helper permits only the exact measured schema-19 live transition: fixed S:\Band Licence App, exact schema-19 metadata, pinned lengths and SHA-256 digests for the three legacy backup/verify/restore scripts and backup-crypto library, protected immutable ACLs, pinned flattened dependencies, and an absent lib\protected-windows-backup.ts. It permits Backup, VerifyBackup, and rollback Restore only. Probe, migration, rehearsal, staged roots, altered source, or the presence of the new guarded module disable this transition.
• Before removing the legacy Machine/User backup-key values, stop the production app and updater, remove inherited Modify/write/delete/ACL-owner rights for NETWORK SERVICE, Authenticated Users, Users, and other untrusted SIDs from S:\Band Licence App and every protected-operation source/import path. The application root must have a protected canonical DACL, be owned by SYSTEM or Administrators, and grant the service identity read/execute only; keep database, cache, log, and temporary write access in separate explicitly scoped writable directories. Then run the protected probe/rehearsal. The helper audits the live operation scripts, local imports, the exact pinned flattened or pnpm-linked dependency layout used by that operation, JavaScript package trees, native SQLite binary, any required TypeScript loader, and every relevant ancestor before decrypting the key. A .pnpm directory containing only lock.yaml is valid only alongside the pinned ordinary flattened package trees; it is never treated as a pnpm-linked installation. Any mismatched version, unexpected link, service-writable, or replaceable code fails closed.
• Keep the legacy environment values until that immutable-code ACL rehearsal and off-server recovery-key escrow are both confirmed. The removal invocation must include -ImmutableApplicationCodeAclConfirmed as well as -ExternalRecoveryKeyEscrowConfirmed and -KeyInheritingBandLicenceProcessesStoppedConfirmed; none of these switches changes ACLs, stops processes, or verifies process state itself. Before using them, stop the teacher/public app tasks, backup/update tasks, GitHub runner and every Band Licence PowerShell/Node process that could have inherited the Machine/User key. Keep them stopped through registry deletion. Then reboot the server (or explicitly restart each stopped service/task from a clean parent), verify the Machine/User values remain absent, and verify no surviving process carries the old variable before claiming that inherited plaintext is gone. Registry deletion alone is not proof that existing process environments were cleared. Run the provisioner normally (without the staging switch) first: it must pass Probe plus a real encrypted backup, authentication verification, and isolated restore rehearsal before the optional removal switches are accepted.
• Apply legal/records holds before destruction.
• Record filename, key version, authority, operator, destruction method and verification.
• Metadata can itself reveal paths/timing and must follow an approved retention rule.

See Operations, Retention and Deletion.

Failure and escalation

Record all High/Critical failures in the Support and Correction Log or incident record as appropriate.

Readiness checklist

Do not mark backup/restore readiness complete until:

• paths and owners are documented;
• encryption is configured for any dedicated wider-use host;
• latest backup verifies;
• a second protected copy exists under an approved process;
• a restore rehearsal passes;
• a full recovery exercise has measured RPO and RTO;
• key recovery has been exercised;
• retention, holds and destruction are decided;
• evidence has an independent checker and next exercise date.