Reliability/Security: Use SQLite-safe backups and restrict backup file permissions

[fvent01]

Finding

frontend/public/update.php backs up the live SQLite database with PHP copy() before running migrations. A plain filesystem copy is not guaranteed to produce a transactionally consistent backup when the API is active, especially if SQLite is using WAL mode or has outstanding writes. The copied database also contains private notes, password hashes, raw session tokens, email addresses, and login history.

Risk

A migration rollback may depend on a backup that is incomplete, missing WAL changes, or internally inconsistent. Backup files can also become a second high-value store of sensitive data and replayable sessions if directory/file permissions are broader than intended.

Evidence

• The updater calls copy($dbPath, $backupPath) directly.
• No SQLite online-backup API, VACUUM INTO, checkpoint, application quiesce, or integrity check is used.
• Backup success is judged only by whether copy() returned true.
• The backup directory is created with mode 0750, but individual copied-file permissions and Windows ACL behavior are not explicitly enforced.

Recommended remediation

• Use SQLite's online backup API, VACUUM INTO, or a controlled .backup operation against a live connection.
• Coordinate migrations with the API so writes are paused or the database is placed into a safe maintenance state.
• Verify every backup with PRAGMA integrity_check before migrations begin.
• Use random or collision-resistant backup names and enforce restrictive permissions/ACLs on directories and files.
• Encrypt backup archives at rest when they leave the application host.
• Add retention, rotation, and tested restore procedures.
• Ensure a failed verification aborts the migration.

Acceptance criteria

• Backups are transactionally consistent while the API is running.
• WAL-mode data is included correctly.
• Every backup passes an integrity check before migrations proceed.
• Ordinary unrelated local users cannot read backup files.
• Restore is covered by an automated or documented tested procedure.
• Tests cover active writes, WAL mode, backup failure, integrity failure, and restore.

Priority

P1 — private-data protection and disaster-recovery reliability.