# Security and recovery review — Schoolhouse ERP 0.2.1

Reviewed 5 October 2026. This is an engineering hardening pass with automated failure tests, not an independent penetration test or a guarantee of zero data loss. Native Windows and the school's real storage/network setup still need deployment acceptance.

## Protections implemented

- Local-only listening by default. Network listeners now require a TLS certificate and key; plaintext LAN startup is refused before opening school data. HTTPS clients need a trusted certificate covering the address they use.
- Passwords use salted scrypt hashes; session tokens are random and hashed in storage. Cookies are HttpOnly, SameSite=Strict and Secure over HTTPS. Server-side role/campus checks, CSRF checks, strict host/origin checks and a content security policy protect browser requests.
- Login failures are limited per account (10) and per client address (30) over 15 minutes. Limits survive restarts. A different account's successful login cannot clear the attacked account's limit. Unknown accounts also perform a password-hash comparison. Expired sessions are pruned on login.
- Private requests authenticate before their bodies are buffered. Limits are 64 KiB for public setup/login, 2 MiB for ordinary operations, 4 MiB for imports, 8 MiB for document uploads and 100 MiB for administrator restore requests. Chunked uploads obey the same limits. Uploaded documents remain capped at 5 MiB each; the UI caps encrypted restore files at 70 MiB.
- Electron uses sandboxing, context isolation, disabled Node integration/webviews, denied permission checks/requests and restricted navigation/new windows. No third-party web content is loaded.
- SQLite uses WAL journaling, FULL synchronization and transactions. Account activation/session invalidation/audit and document upload/audit now commit together. Failed writes do not produce a successful response. Transaction cleanup handles SQLite's automatic rollback after a storage-full error.
- Startup checks database integrity and refuses corrupt, pre-existing empty, or missing-main-file/orphaned-WAL databases. It does not silently replace those files with a new school. POSIX database files use mode 0600; Windows access still depends on Windows ACLs and the signed-in account.

## Verified backups and restore

Automatic snapshots run at startup, hourly while the service is running, and on normal shutdown. A normal successful record save is committed to the live database immediately; it does not wait for the next backup. Backups protect a separate failure case: recovering after the live storage is lost.

Snapshots are written to a unique `.partial` file, checked with SQLite integrity verification, flushed, then renamed to `.sqlite`. Only completed copies participate in retention. The newest 30 regular copies are kept **per installation**, so two school installations sharing a folder do not rotate each other's backups. Legacy copies are left untouched. A crash may leave a `.partial` file; it is not a verified recovery copy and is not automatically promoted.

A missing configured folder is not silently recreated during routine backup. Failure stays recorded and visible to administrators across screens. When possible, a separate local fallback is made; it does not clear the failed external-backup status. Encrypted exports also cannot clear that status. The banner checks backup health every minute while an administrator's page is visible. The app cannot establish that a chosen folder is on a separate physical disk, and cannot record fresh status if the live disk itself cannot accept any writes.

Portable `.schoolbackup` files use AES-256-GCM with a scrypt-derived key and random salt/nonce. A lost passphrase cannot be recovered. Export success confirms that the server generated a file; verify that the desktop saved it to the intended device. Automatic raw snapshots and the live SQLite database are **not encrypted by the application**.

Restore verifies authentication, file integrity, schema/table columns, school settings and an active administrator before replacing data. A separate pre-restore copy is retained (newest five per installation); it is not rotated by ordinary backups. Records, account/session changes, destination-machine identity/settings and restore bookkeeping are committed in one transaction. Failure rolls the transaction back. All restored sessions are invalidated. Source-machine backup health and paths cannot overwrite the destination machine's health and backup folder.

Local export snapshots and local fallbacks have their own 30-copy retention. Retention counts are not calendar-day guarantees: extra starts/manual backups can use multiple slots in one day. Keep daily/weekly portable copies outside the automatic rotation.

## Evidence

The full automated suite has **68 passing tests**: the prior 51 functional/result/recovery cases plus SEC01–SEC17. The new cases cover Windows/POSIX path containment, plaintext-network refusal, actual hostile Host headers and absolute-form URLs, body limits including chunked requests, authentication before upload parsing, persistent login throttling, missing destination/fallback behavior, independent installation retention, injected snapshot corruption, encrypted file tampering/truncation, unsupported restore schemas, restore rollback, SQLite storage-full rollback, corrupt startup files, shutdown snapshots and atomic audit failures.

The existing recovery test kills the host process after a committed save, restarts it, checks the record and runs SQLite integrity verification. It is a process-crash test, not a physical power-cut test. Storage-full testing uses SQLite's `max_page_count`; corrupt-copy and audit/restore failures are injected. These do not emulate every filesystem, faulty controller or full physical disk condition.

Interactive checks in the fictional browser workspace confirmed a missing folder warning, unchanged last-success time, local fallback notice, warning persistence while viewing five student records, navigation to recovery, and successful backup after restoring the folder. Client error logs were empty. A directory rename simulated unavailable storage; no real external drive was disconnected. Evidence: `test-results/security-ui-acceptance.json`.

`npm audit` reported zero known vulnerabilities in the installed dependency tree at review time. This is not a full Electron/Chromium security certification. Evidence: `test-results/security-dependency-audit.json` and `test-results/security-tests.log`.

Run `npm test` for all tests or `npm run test:security` for the security and process-recovery cases. Tests use disposable fictional schools.

## Required school deployment checks

1. Use individual staff accounts and appropriate roles. Keep the Windows account restricted, Windows security updates current and the host protected against malware. The app's login cannot stop someone who has administrator-level access to the host or raw database files.
2. Enable device encryption/BitLocker where available on the host and backup drive. Store recovery keys separately. Database files and raw snapshots contain student, guardian, staff and financial information.
3. Select an actual separate backup device. Run a backup, safely disconnect it, confirm the warning, reconnect it, and verify another backup. Keep an additional daily encrypted copy disconnected or off-site to limit theft/ransomware exposure.
4. Restore a copy on a separate Windows installation and verify students, balances, attachments and published report cards. Verify downloads, printing and the packaged runtime on Windows before live use.
5. Use a UPS for the host and safely eject backup media. Sudden failure can lose unsaved input. If the live disk fails, recovery reaches only the latest surviving backup: normally up to an hour of later changes may be missing, and longer if backups have failed. More frequent/continuous replication is required for a smaller recovery window; it is not implemented here.
6. If sharing over the school LAN, validate the trusted TLS certificate, firewall and a second computer. The host must be running. Do not expose the service directly to the public internet.

No software can promise zero loss after disk destruction, theft, malware, a lost backup passphrase, or failure of every backup copy. This build has a stronger tested baseline; production readiness also depends on these Windows/device checks and an operational restore routine.

## Primary references

- [SQLite VACUUM INTO](https://www.sqlite.org/lang_vacuum.html): consistent backup output and the risk of incomplete output if generation is interrupted.
- [SQLite WAL durability](https://www.sqlite.org/wal.html) and [corruption causes](https://www.sqlite.org/howtocorrupt.html): synchronization, storage guarantees and limitations of faulty hardware/filesystems.
- [Electron security guidance](https://www.electronjs.org/docs/latest/tutorial/security): sandboxing, context isolation, CSP, permissions and navigation controls.