# Africa Alert PWA — Backup & Restore Operator Runbook

This document is the canonical reference for the SQLite backup, restore, and
disaster-recovery procedure for the Africa Alert PWA production deployment.

It is targeted at the operator who runs the production Ubuntu 22.04 host
on which the Node API and the React PWA serve the school. It is **not** a
development reference — there is no reason to run these scripts on a
contributor's laptop.

## 1. What is backed up, and what is not

| Component | Backed up? | How |
|---|---|---|
| Live SQLite database (`server/data/school.db`) | **Yes** | `sqlite3 .backup` to `/backups/school-YYYYmmdd-HHMMSS.db` |
| SQLite WAL sidecars (`-wal`, `-shm`) | **Yes** | Copied alongside the `.db` snapshot if present at backup time |
| `uploads/` (multer attachments) | **No** by default | Mount a separate cron / `rsync` job if you need this; the backup script does not touch it |
| `.env` (secrets) | **No, on purpose** | Restoring `.env` from a backup is a footgun. The server reads it on boot; the operator re-creates it on the new host |
| Supabase cloud data | **N/A** | The local DB is the source of truth on the LAN; the SyncEngine pushes deltas upstream. A restore replays the local state and re-syncs |

## 2. Installation (one-liner)

The scripts live in `scripts/backup/` of the deployed project tree
(`/opt/africa-alert` by convention). The host needs `bash`, `sqlite3`, and
`curl` installed (`sudo apt-get install -y sqlite3 curl`).

```bash
# 1. Copy the scripts to the host (or git pull on the host).
# 2. Make them executable.
sudo install -m 0755 \
  /opt/africa-alert/scripts/backup/africa-alert-backup.sh   /usr/local/bin/
sudo install -m 0755 \
  /opt/africa-alert/scripts/backup/install-backup-cron.sh   /usr/local/bin/
sudo install -m 0755 \
  /opt/africa-alert/scripts/backup/restore.sh               /usr/local/bin/
sudo install -m 0755 \
  /opt/africa-alert/scripts/backup/verify-restore.sh        /usr/local/bin/

# 3. Install the cron entry (every 12 hours, on the hour, UTC).
sudo /usr/local/bin/install-backup-cron.sh
```

After step 3, verify the cron entry is in place:

```bash
crontab -l | grep africa-alert
# expected output:
# 0 */12 * * * /bin/bash /usr/local/bin/africa-alert-backup.sh >> /opt/africa-alert/logs/backup.log 2>&1 # africa-alert-backup
```

The install script is idempotent: re-running it after a successful install
detects the existing entry and exits 0 without modifying the crontab.

## 3. Manual trigger

You can run a backup on demand at any time. The script is safe to run
concurrently with a live server — `.backup` acquires the SQLite write lock
for a few milliseconds.

```bash
# Default config (DB_PATH=/opt/africa-alert/server/data/school.db, BACKUP_DIR=/backups)
sudo /usr/local/bin/africa-alert-backup.sh

# Override the source DB (e.g. to back up a non-default install):
sudo DB_PATH=/srv/aa/server/data/school.db /usr/local/bin/africa-alert-backup.sh

# Override the destination:
sudo BACKUP_DIR=/mnt/nas/backups /usr/local/bin/africa-alert-backup.sh
```

A successful run prints the new snapshot path and exits 0. A failed run
prints the reason to stderr and exits non-zero — see §6 for monitoring.

### Cross-platform note (Windows developers)

The bash script targets the production Ubuntu host and uses the `sqlite3`
CLI's `.backup` command. On a Windows dev box the CLI is not installed
by default, but the script detects that and transparently delegates to
`scripts/backup/africa-alert-backup.js`, a Node fallback that uses
`better-sqlite3`'s online backup API (the same primitive under the
hood). All exit codes and log lines are aligned.

```powershell
# On Windows — same env vars, same output format:
$env:DB_PATH    = 'C:\path\to\server\data\school.db'
$env:BACKUP_DIR = 'C:\path\to\backups'
node scripts/backup/africa-alert-backup.js
```

If you would rather not invoke `node` directly, an `npm run db:backup`
script can be wired into `server/package.json` in a follow-up — the
fallback script already exists and behaves identically.

## 4. Restore drill

Restoring is **destructive**: the live database is overwritten. The server
must be stopped first, or the running Node process will keep writing to
the old (now-orphaned) inode and any in-flight writes will be lost on
restart.

The `restore.sh` script automates the systemctl stop/start, takes a
"pre-restore" safety snapshot, and copies the backup over the live DB
(plus its `-wal` and `-shm` sidecars). The script prompts for a typed
"yes" at a TTY; pass `RESTORE_CONFIRM=yes` to use it from automation.

### Step-by-step

```bash
# 1. Pick the backup file. List available snapshots:
ls -lht /backups/school-*.db | head -20

# 2. Dry-run verify: copy the backup to a temp DB, boot the server
#    against it, and assert admin login works.
sudo /usr/local/bin/verify-restore.sh /backups/school-20260716-120000.db
# expect: "login OK — backup is restorable" and exit 0

# 3. Real restore. The script will:
#    - stop africa-alert.service (if systemd is present)
#    - take a pre-restore snapshot at school.db.pre-restore-YYYYmmdd-HHMMSS
#    - copy the backup file (and sidecars) over the live DB
#    - drop any stale live sidecars that do not have a matching backup sidecar
#    - restart the service
sudo /usr/local/bin/restore.sh /backups/school-20260716-120000.db

# 4. Confirm the service is up and login still works.
systemctl status africa-alert.service
curl -s http://localhost:3001/api/auth/login \
  -H 'Content-Type: application/json' \
  -d '{"email":"admin@school.com","password":"admin123"}' | jq .user.email
# expect: "admin@school.com"

# 5. If the restore looks wrong, roll back to the pre-restore snapshot:
sudo systemctl stop africa-alert.service
sudo cp -a /opt/africa-alert/server/data/school.db.pre-restore-YYYYmmdd-HHMMSS \
        /opt/africa-alert/server/data/school.db
sudo systemctl start africa-alert.service
```

## 5. Retention policy

We keep **30 daily** snapshots for short-term recovery (operator fat-finger,
bad migration) and **12 weekly** snapshots for medium-term recovery
(discovery lag — e.g. you notice a bug today that has been corrupting
data for the last three weeks).

The `africa-alert-backup.sh` script already enforces the 30-daily cap
inline. The 12-weekly promotion is a separate one-liner run from a weekly
cron (not part of the 12-hourly backup job, because weekly frequency is
too coarse for the main loop).

```bash
# Promote the newest snapshot from each ISO week into /backups/weekly/.
# Keep the most recent 12 weekly files.
install -d /backups/weekly
latest_of_week() {
  # Pick the newest school-*.db whose date string is the latest in its week.
  ls -1t /backups/school-*.db \
    | awk -F'[-.]' '{wk=strftime("%G-W%V", mktime(substr($0, length($0)-19,4) " " substr($0, length($0)-19+5,2) " " substr($0, length($0)-19+8,2) " 0 0 0"))} wk=="'"$1"'"' \
    | head -n1
}
this_week="$(date -u +%G-W%V)"
src="$(latest_of_week "${this_week}")"
[ -n "${src}" ] && cp -p "${src}" "/backups/weekly/$(basename "${src}")"
# Trim weekly to the most recent 12 entries.
find /backups/weekly -maxdepth 1 -type f -name 'school-*.db' -printf '%T@ %p\n' \
  | sort -nr | tail -n +13 | awk '{print $2}' | xargs -r rm -f
```

A simpler alternative for smaller deployments (one-liner, no helper
function): rotate weekly by tagging each Sunday's snapshot.

```bash
# Run from a Sunday-only cron entry:
TODAY="$(date -u +%u)"   # 7 = Sunday
if [ "${TODAY}" = "7" ]; then
  LATEST="$(ls -1t /backups/school-*.db | head -n1)"
  cp -p "${LATEST}" "/backups/weekly/$(basename "${LATEST}")"
  # Keep last 12 weekly files.
  ls -1t /backups/weekly/school-*.db | tail -n +13 | xargs -r rm -f
fi
```

Off-host archival is the operator's responsibility. The recommended
approach is `rsync` of `/backups/` to an off-host location (Backblaze B2,
S3, another server on the LAN) at least once per day. Keep at least one
copy physically outside the school building.

## 6. Failure alerting

The backup script is designed to fail loudly so that silent data loss
does not happen:

- On any error, the script writes a line beginning with `ERROR:` to
  **stderr** and to the log file, and exits with a non-zero status.
- Exit codes:
  - `0` — backup succeeded, retention swept
  - `1` — configuration error (missing `sqlite3`, unreadable DB)
  - `2` — `sqlite3 .backup` itself failed (snapshot file is missing or empty)
  - `3` — retention sweep failed (snapshot is on disk, but the rotation
    could not prune old files — investigate before the disk fills)
- Cron redirects stdout and stderr to `logs/backup.log`, so failures
  appear there.

What the operator should monitor:

1. **The log file's last line.** A healthy run ends with
   `done. file=/backups/school-…db size=… bytes retention_deleted=N`.
   Anything ending in `ERROR:` needs attention.
2. **The mtime of the most recent snapshot.** If `/backups/school-*.db`
   is older than 26 hours, the cron job is not firing or the script is
   failing silently. Alert on:
   ```bash
   find /backups -maxdepth 1 -name 'school-*.db' -mmin +1560 -print
   ```
3. **Free disk space on the `/backups` volume.** Daily snapshots are
   small (SQLite is rarely more than a few hundred MB for a school of
   this size) but retention bugs can accumulate fast. Alert at 80% full.
4. **The verify-restore drill** should be run at least monthly (see §7)
   and its exit code should be 0. If it fails, the backup format has
   drifted from what the live server expects — investigate before you
   need the restore in anger.

The on-host alert path is **not** scripted here. Wire `logs/backup.log`
into whatever the operator already uses (Promtail/Loki, journald export,
mailx, a simple webhook to a Telegram bot). The contract is: any
non-zero exit from the cron job, or any line containing `ERROR:` in
the log, is paged to the operator.

## 7. Disaster recovery scenario

The school server's SSD has died. The replacement machine has been
imaged with Ubuntu 22.04. We have off-host copies of `/backups/`.

### Step 1 — Restore the application tree

```bash
# Clone the repo (or copy the latest release tarball).
sudo mkdir -p /opt/africa-alert
sudo chown "$USER:" /opt/africa-alert
cd /opt/africa-alert
git clone https://git.techarvest.co.zw/fchinembiri/next-gen.git .

# Checkout the same tag/commit the school was on.
git checkout <release-tag>
cd server && npm ci --omit=dev && cd ..
```

### Step 2 — Restore `.env` (recreate, do not copy)

`.env` is not in the backup because it contains the JWT secret and
CORS allow-list. Re-create it from the operator's password manager,
then `npm run db:init` to lay down a fresh empty schema (idempotent —
safe to run).

### Step 3 — Promote a backup

```bash
# Pull the most recent off-host copy.
rsync -av offhost:/backups/ /backups/

# Verify before restoring.
sudo /usr/local/bin/verify-restore.sh "$(ls -1t /backups/school-*.db | head -n1)"
# expect: "login OK — backup is restorable"

# Restore.
sudo /usr/local/bin/restore.sh "$(ls -1t /backups/school-*.db | head -n1)"
```

### Step 4 — Reinstall the backup cron

```bash
sudo install -m 0755 /opt/africa-alert/scripts/backup/*.sh /usr/local/bin/
sudo /usr/local/bin/install-backup-cron.sh
```

### Step 5 — Smoke-test

- `curl http://localhost:3001/api/health` (if exposed) or login
  manually through the PWA at `http://<host>:3000`.
- Confirm a few records the operators remember (a recent student
  name, a recent fee payment) are present in the UI.
- Confirm the next scheduled backup fires (wait one cron interval, then
  `ls -lht /backups/school-*.db | head -1`).

### Step 6 — Sync reconciliation

If Supabase is reachable, the SyncEngine will push any rows still
flagged `sync_status='pending'` from the restored local DB on the next
30-second cycle. No operator action is required beyond verifying the
sync is no-op'ing in `client/src/pages/admin/SyncLogs.tsx`.

## 8. Quick reference

| Task | Command |
|---|---|
| Run a backup now | `sudo /usr/local/bin/africa-alert-backup.sh` |
| Install the 12-hourly cron | `sudo /usr/local/bin/install-backup-cron.sh` |
| Uninstall the cron | `crontab -l \| grep -v 'africa-alert-backup' \| crontab -` |
| Verify a backup | `sudo /usr/local/bin/verify-restore.sh /backups/school-YYYYmmdd-HHMMSS.db` |
| Restore a backup | `sudo /usr/local/bin/restore.sh /backups/school-YYYYmmdd-HHMMSS.db` |
| List backups | `ls -lht /backups/school-*.db \| head -20` |
| Tail the log | `tail -f /opt/africa-alert/logs/backup.log` |
| Find stale backups | `find /backups -maxdepth 1 -name 'school-*.db' -mmin +1560 -print` |
