Backup and restore
What scripts/backup.sh and scripts/restore.sh do, line by line, how to schedule the backup, and how to run a restore drill.
1. What is backed up
| Part | Content | Archive |
|---|---|---|
| Database | Everything: sheets, revisions, publications, assets metadata, passkeys (public keys), sessions, audit trail, job history, usage ledger, settings | $BACKUP_DIR/db/sheet-YYYYmmdd-HHMMSS.dump.enc |
| Storage | Every file under STORAGE_ROOT: uploads and exports |
$BACKUP_DIR/storage/sheet-YYYYmmdd-HHMMSS.tar.enc |
tar excludes only *.part (uploads being written) and .probe (the health-check file). Exports live in the same key space as uploads and are archived with them.
Each archive is accompanied by <archive>.hmac, an HMAC-SHA256 of the encrypted file keyed with the passphrase.
2. Configuration
Read from the environment, or from .env in the project root for the variables not already set:
| Variable | Used by | Meaning |
|---|---|---|
DATABASE_URL |
backup | Database to dump |
STORAGE_ROOT |
backup | Directory to archive |
BACKUP_DIR |
backup | Destination; created with mode 0700, files 0600 (umask 077) |
BACKUP_PASSPHRASE_FILE |
backup, restore | File whose content is the encryption passphrase |
BACKUP_KEEP_DAYS |
backup | Retention in days, default 30 |
The passphrase file
install -d -m 700 ~/.config/sheet
openssl rand -base64 48 > ~/.config/sheet/backup-passphrase
chmod 600 ~/.config/sheet/backup-passphrasebackup.sh stops if the file is missing or empty. openssl reads the passphrase from the first line of the file.
Keep a copy of the passphrase somewhere other than the server and other than the backup destination. Without it the archives cannot be decrypted; there is no recovery path. Do not put the passphrase file inside BACKUP_DIR.
3. What scripts/backup.sh does
set -euo pipefail; changes to the project root; loads the five variables above from.envwhen unset.- Prepends the Homebrew PostgreSQL 17 and Homebrew
bindirectories toPATH(harmless where they do not exist).pg_dump,pg_restore,opensslandtarmust be on thePATH. - Creates
$BACKUP_DIR/dband$BACKUP_DIR/storage. - Database:
pg_dump --format=custom --no-owner --no-privileges "$DATABASE_URL"piped intoopenssl enc -aes-256-cbc -pbkdf2 -iter 200000 -salt. Written to a.tmpfile, then renamed. - Verifies the database archive: decrypts it and runs
pg_restore --liston the result, then drains the rest of the stream so that the decryption runs to the end. A wrong passphrase or a truncated file fails the run here. - Storage:
tar -C "$STORAGE_ROOT" --exclude='*.part' --exclude='.probe' -cf - .piped into the same encryption. Written to.tmp, then renamed. - Verifies the storage archive: decrypts it, lists it with
tar -tf, and counts the files. - Integrity tag: for each of the two archives,
openssl dgst -sha256 -hmac <passphrase>→<archive>.hmac. - Retention: deletes
sheet-*.encandsheet-*.hmacfiles in both directories whose modification time is older thanBACKUP_KEEP_DAYSdays. - Writes
$BACKUP_DIR/last-backup.json:{"at": "<UTC time>", "database": "<file>", "storage": "<file>", "files": <count>}. - Prints
backup ok: <dump> (<size>), <tar> (<n> files).
Any failing step aborts the script with a non-zero status; an archive that failed verification is left in place (it was already renamed) and the retention step is not reached.
Properties worth knowing:
- Encryption is AES-256-CBC with a key derived by PBKDF2 (200 000 iterations, random salt). CBC gives secrecy, not integrity, so each archive gets a separate HMAC-SHA256 keyed with the same passphrase;
restore.shchecks it before decrypting and refuses an altered archive. - The database dump and the storage archive are taken one after the other, not atomically. A file uploaded between the two appears in the archive without a database row; a file deleted between the two is referenced by the dump but absent from the archive. Schedule the backup at a quiet hour.
pg_dumpreceives the database URL, password included, as a command-line argument, and the HMAC step passes the passphrase toopenssl dgst -hmacthe same way: both are visible to other users of the machine in the process list while they run.- The dump holds session token hashes, passkey public keys and recovery-code hashes. It holds no API key and no
APP_SECRET.
4. Scheduling
The script is meant to run once a day. Nothing in the repository schedules it; that is the operator's job.
cron:
15 3 * * * cd /srv/sheet && /bin/bash scripts/backup.sh >> /var/log/sheet-backup.log 2>&1PM2: an app entry with script: "scripts/backup.sh", interpreter: "bash", autorestart: false, cron_restart: "15 3 * * *" (see DEPLOYMENT.md).
Monitoring: alert when the script exits non-zero, or when last-backup.json is older than a day and a half. GET /api/admin/settings reports only whether BACKUP_DIR is set (environment.backupDirSet); the application does not read last-backup.json.
5. Retention
find … -name 'sheet-*.enc' -mtime +$BACKUP_KEEP_DAYS -delete (and the same for sheet-*.hmac), in db/ and storage/. With the default 30 and one run a day, about 30 pairs are kept. Retention is by file age only: there is no weekly or monthly tier, and no check that a newer valid backup exists before an old one is deleted — though the script only reaches this step after both new archives verified.
Each storage archive is a full copy. Disk use is roughly BACKUP_KEEP_DAYS × (size of STORAGE_ROOT + compressed dump).
6. What scripts/restore.sh does
scripts/restore.sh db <sheet-….dump.enc> <postgres URL of an EMPTY target database>
scripts/restore.sh storage <sheet-….tar.enc> <target STORAGE_ROOT (empty or new directory)>- Loads
BACKUP_PASSPHRASE_FILEfrom the environment or.env. - Integrity check first (
check): recomputes the HMAC-SHA256 of the archive and compares it with<archive>.hmac. A mismatch stops withINTEGRITY CHECK FAILED … the archive was altered or the passphrase is wrong. A missing.hmacfile also stops the restore, unlessFORCE=1is set (for archives made before the HMAC existed). Keep each.hmacnext to its archive when copying backups elsewhere. db: decrypts and pipes intopg_restore --no-owner --no-privileges --exit-on-error -d <target>. Objects are created as the role of the target URL. The dump containsCREATE EXTENSIONstatements, so that role must be allowed to createcitext,pg_trgmandunaccent(the database owner can, on PostgreSQL 13+), or the extensions must exist already. The target must be empty:--exit-on-errorstops at the first conflict.storage: creates the target directory with mode 0700 and extracts the archive into it.- With no or an unknown first argument it prints the usage and exits 2.
The script never touches the running installation: it writes only to the targets given on the command line.
7. Restore drill
Run this on a schedule you can keep (for example monthly), on the server or on another machine with the archives and the passphrase.
# 0. choose the pair to test
DB_ARCHIVE=$(ls "$BACKUP_DIR"/db/*.dump.enc | tail -1)
ST_ARCHIVE=$(ls "$BACKUP_DIR"/storage/*.tar.enc | tail -1)
# 1. restore the database into a scratch database
createdb sheet_drill
bash scripts/restore.sh db "$DB_ARCHIVE" "postgres://<role>@127.0.0.1:5432/sheet_drill"
# 2. restore the storage into a scratch directory
bash scripts/restore.sh storage "$ST_ARCHIVE" /tmp/sheet-drill-storage
# 3. compare row counts with production for the tables that matter
for t in sheets sheet_revisions sheet_publications assets passkey_credentials audit_events; do
echo "$t $(psql -Atc "select count(*) from $t" "$DATABASE_URL") $(psql -Atc "select count(*) from $t" postgres://<role>@127.0.0.1:5432/sheet_drill)"
done
# counts differ only by what happened since the backup was taken
# 4. every file the restored database knows exists in the restored storage with the recorded checksum
psql -Atc "select storage_key || ' ' || sha256 from assets where deleted_at is null" postgres://<role>@127.0.0.1:5432/sheet_drill |
while read -r key sum; do
[ "$(shasum -a 256 "/tmp/sheet-drill-storage/$key" | cut -d' ' -f1)" = "$sum" ] || echo "MISMATCH $key"
done
# 5. (thorough) start the application on the restored copy, on another port
PORT=8799 HOST=127.0.0.1 APP_URL=http://localhost:8799 \
DATABASE_URL=postgres://<role>@127.0.0.1:5432/sheet_drill STORAGE_ROOT=/tmp/sheet-drill-storage \
NODE_ENV=production node_modules/.bin/tsx server.ts
# then: curl http://localhost:8799/api/health ; open a published sheet at /s/<slug>
# 6. clean up
dropdb sheet_drill && rm -rf /tmp/sheet-drill-storageSteps 1–4 are what scripts/test.sh automates against the test database after its integration run. On Linux use sha256sum in place of shasum -a 256.
Notes for step 5: passkeys are bound to the RP ID, so signing in to the studio on localhost with production passkeys does not work — that is expected. Public pages, the public API and /api/health are enough to show the data is readable. Do not start a worker against the drill copy unless you want queued jobs to run.
Restoring for real
- Stop the web and worker processes.
- Create an empty database (
createdb), restore the dump into it, restore the storage archive into a new directory. - Point
DATABASE_URLandSTORAGE_ROOTin.envat the restored copies (or swap names). pnpm migrate(applies any migration newer than the backup),pnpm bootstrap.- Start the processes. Sessions that were valid at backup time are valid again; sign in and check the dashboard.
- Jobs that were queued or running at backup time are in the restored queue and will run. Cancel them first if that is not wanted (see OPERATIONS.md).
Everything created after the backup is lost.
8. What is not covered
- Off-site copies. The script writes to a local directory — on the same disk as the data unless
BACKUP_DIRpoints elsewhere. CopyingBACKUP_DIR(archives and their.hmacfiles) to another machine or provider is the operator's responsibility; the archives are encrypted and tagged, so they can be stored on an untrusted target — keep the passphrase elsewhere. .env, the passphrase file,SETUP_URL_FILE, the proxy configuration and TLS certificates. Keep.envand the passphrase in a password manager.- The code and
node_modules: reinstall from the repository. - Point-in-time recovery. There is no WAL archiving; the granularity is one backup.
- Deleted uploads. Deleting an asset removes its file at once; it survives only in archives taken before the deletion.
- A check from inside the application. The dashboard has no backup tile.
- Browsers installed for exports: reinstall with
playwright-core.
Verified
Two drills were run on 2026-11-17.
Test environment (scripts/test.sh, laptop): backup of a database holding 11 sheets and 10 stored files; restore into an empty database and an empty directory; row counts equal on sheets, sheet_revisions, sheet_publications, assets, passkey_credentials and audit_events; every restored file matches the SHA-256 recorded in assets; an archive with one byte appended is refused by restore.sh ("INTEGRITY CHECK FAILED").
Production node (M2M32c, production passphrase): scripts/backup.sh wrote sheet-20261117-131443.dump.enc (200 kB) and the storage archive (6 files); both restored into a scratch database and directory: 6 sheets / 6 publications / 6 files on both sides, 0 checksum mismatches. The scratch copies were removed afterwards.
Still to do by the operator: copy ~/backups/sheet (and, separately, the passphrase) to another machine on a schedule. The backups currently sit on the same disk as the data.