learning-website-nextjs1-12  Exercise 2: Backing Up and Restoring the Accounts Database
====================================================================================

The only data that cannot be rebuilt from the content folder is the accounts database (users, sessions, finished pages). Everything else is
rebuilt by the next release.

Save as packages/operations/src/backup.ts:

import { copyFileSync, existsSync, mkdirSync, readdirSync, renameSync, rmSync } from "node:fs";
import { basename, dirname, join } from "node:path";
import { DatabaseSync } from "node:sqlite";

const PREFIX = "accounts-";
const SUFFIX = ".sqlite";

/** accounts-20261008-031500.sqlite: sorts in time order as plain text. */
export function backupName(now: Date): string {
  const p = (n: number, width = 2) => String(n).padStart(width, "0");
  const stamp = `${p(now.getUTCFullYear(), 4)}${p(now.getUTCMonth() + 1)}${p(now.getUTCDate())}-${p(now.getUTCHours())}${p(now.getUTCMinutes())}${p(now.getUTCSeconds())}`;
  return `${PREFIX}${stamp}${SUFFIX}`;
}

/** Every backup in a folder, oldest first. Anything that does not have the backup name pattern is left alone. */
export function listBackups(folder: string): string[] {
  if (!existsSync(folder)) return [];
  return readdirSync(folder).filter((n) => /^accounts-\d{8}-\d{6}\.sqlite$/.test(n)).sort();
}

/** Does this file open as a database, pass SQLite's own integrity check and hold the tables we need? */
export function verifyDatabase(file: string): { ok: boolean; users?: number; problem?: string } {
  let db: DatabaseSync | undefined;
  try {
    db = new DatabaseSync(file, { readOnly: true });
    const check = db.prepare("PRAGMA integrity_check").get() as { integrity_check: string };
    if (check.integrity_check !== "ok") return { ok: false, problem: `integrity_check said: ${check.integrity_check}` };
    const users = (db.prepare("SELECT COUNT(*) AS n FROM users").get() as { n: number }).n;
    return { ok: true, users };
  } catch (error) {
    return { ok: false, problem: (error as Error).message };
  } finally {
    db?.close();
  }
}

export interface BackupResult { readonly file: string; readonly users: number; readonly pruned: string[] }

/**
 * A consistent copy of the live database, safe while the site is running.
 * (Copying the .sqlite file with cp can catch it half-way through a write, and misses what is still in the -wal file;
 * VACUUM INTO asks SQLite itself for a complete, consistent copy.) The copy is checked before it is trusted, and only
 * then are backups beyond the newest `keep` removed, so a broken backup can never push out a good one.
 */
export function backupDatabase(source: string, folder: string, options: { keep?: number; now?: Date } = {}): BackupResult {
  const keep = options.keep ?? 14;
  if (!Number.isInteger(keep) || keep < 1) throw new Error("keep must be a whole number, at least 1");
  mkdirSync(folder, { recursive: true });
  const target = join(folder, backupName(options.now ?? new Date()));
  if (existsSync(target)) throw new Error(`${basename(target)} already exists: not overwriting a backup`);
  const temporary = `${target}.partial`;
  rmSync(temporary, { force: true });
  const db = new DatabaseSync(source, { readOnly: true });
  try {
    db.exec(`VACUUM INTO '${temporary.replaceAll("'", "''")}'`);
  } finally {
    db.close();
  }
  const check = verifyDatabase(temporary);
  if (!check.ok) {
    rmSync(temporary, { force: true });
    throw new Error(`the new backup did not verify (${check.problem}): nothing was removed`);
  }
  renameSync(temporary, target);
  const all = listBackups(folder);
  const pruned = all.slice(0, Math.max(0, all.length - keep));
  for (const name of pruned) rmSync(join(folder, name));
  return { file: target, users: check.users ?? 0, pruned };
}

/**
 * Put a backup back. The service must be stopped first (the script does that). The backup is verified BEFORE anything is touched;
 * the database being replaced is kept beside it as accounts.sqlite.before-restore, and the old -wal and -shm files, which belong to
 * the old database and would corrupt the restored one, are moved away with it.
 */
export function restoreDatabase(backup: string, target: string): { replaced: boolean; users: number } {
  const check = verifyDatabase(backup);
  if (!check.ok) throw new Error(`refusing to restore ${basename(backup)}: ${check.problem}`);
  mkdirSync(dirname(target), { recursive: true });
  const replaced = existsSync(target);
  for (const extra of ["", "-wal", "-shm"]) {
    const old = target + extra;
    if (existsSync(old)) renameSync(old, `${target}${extra}.before-restore`);
  }
  const temporary = `${target}.restoring`;
  copyFileSync(backup, temporary);
  renameSync(temporary, target);
  return { replaced, users: check.users ?? 0 };
}

WHY NOT JUST COPY THE FILE. The database runs in WAL mode: recent writes sit in accounts.sqlite-wal, not in accounts.sqlite. A plain copy of the
.sqlite file misses them, and a copy made in the middle of a write can be damaged. VACUUM INTO asks SQLite for a complete, consistent copy
while the site is running. The unit test keeps the source connection OPEN with its latest rows still in the -wal file and checks that the
backup holds all of them.

THE ORDER OF THINGS matters: (1) the copy is made under a temporary name; (2) it is opened and checked (PRAGMA integrity_check, and that the users
table can be read); (3) only then is it renamed to its final name; (4) only after that are the oldest backups beyond the newest 14 removed. A
broken backup therefore never pushes out a good one. A backup is never written over an existing file (two in the same second would be a
mistake, not a replacement).

THE RESTORE checks the backup FIRST and refuses a bad one before touching anything. Then the database it replaces is kept (accounts.sqlite.before-restore),
and the old -wal and -shm files go with it: they belong to the old database, and left in place they would be applied to the restored one.
The service must be stopped first (deploy.sh does that).

A REAL RUN (a throwaway database with made-up accounts and a made-up password, outside the project). The same backup command was also run once while
the app was running and using the database, and wrote its copy without an error or an interruption; the capture below was made with the app stopped:

  create two accounts: node accounts-admin.mjs create ann / bob
created user ann
created user bob

  node ops.mjs backup C:/lwdata/backups --keep 14
backup written: C:\lwdata\backups\accounts-20261007-231204.sqlite (2 users); 0 old backup(s) removed

  an account made AFTER the backup: node accounts-admin.mjs create cyd
created user cyd
  accounts now: 'ann' 'bob' 'cyd' 

  node ops.mjs restore C:/lwdata/backups/accounts-20261007-231204.sqlite
restored C:/lwdata/backups/accounts-20261007-231204.sqlite (2 users); the database it replaced is kept as accounts.sqlite.before-restore
  accounts after the restore: 'ann' 'bob' 
  files in the data folder: accounts.sqlite accounts.sqlite.before-restore 

  restoring a file that is not a database:
  Error: refusing to restore bad.sqlite: file is not a database
  accounts afterwards (unchanged): 'ann' 'bob'

(The first attempt at this proved nothing: I tried to add a third account called "cy", which the site rightly refuses as too short, so the database
never changed after the backup and the restore had nothing to undo. A test that cannot fail is not a test: it was redone with a valid name.)

THE NIGHTLY JOB:

Save as deploy/lw-backup.cron:

# /etc/cron.d/lw-backup: a database backup every night at 03:15, keeping the newest 14. The service user owns the database, so it makes the backup.
# A failure (the backup did not verify, the disk is full) exits non-zero and cron mails it.
15 3 * * * lw set -a; . /etc/lw/environment; set +a; cd /srv/lw/current && node ops.mjs backup /srv/lw/backups --keep 14

AN HONEST NOTE ON THE TESTS. When the check on the new backup is removed from the code, the unit tests still fail, but only because the first test
also counts rows. A backup that opens but is damaged inside is not something any test makes; the verification step exists for that case and is
not proved by a test. The restore's own check is proved (a bad file and a database with the wrong tables are both refused, and the database is untouched).

WHY THIS WORKS AS AN ANSWER
---------------------------
A backup you have not restored is a hope. This one was made while the app was running, restored for real, and the account made after the backup
was gone afterwards, with the database it replaced kept.