cyclone/docs/superpowers/specs/2026-06-21-cyclone-encrypted-backup-design.md

SP17 — Automated Encrypted DB Backups

Date: 2026-06-21 Branch: sp17-encrypted-backups Status: Shipped Scope: Backend only. No frontend changes (operator-only admin function).

---

1. Why this exists

The Cyclone SQLite database at ~/.local/share/cyclone/cyclone.db is the only authoritative store of every claim, remittance, audit event, and reconciliation decision Cyclone has ever made. The README documents sqldiff .backup /path/to/backup.db as a manual recipe. That has two problems:

1. Manual is unreliable. The operator forgets. A disk failure on the operator's laptop is unrecoverable. The 6-year HIPAA retention expectation is not met by a recipe.
2. No encryption. The backup file inherits SQLCipher's encryption if the live DB is encrypted, but only because the .backup API copies the raw pages verbatim. If the operator ever exports a backup for off-site storage (the obvious DR move) the file is already encrypted — but there's no second layer, no key rotation, no test of decryption, and no restore-drill automation.

SP17 fixes both: an automated tick creates an encrypted backup on a schedule (default 24h), applies a retention policy (default 30 days), and exposes API + CLI surface for create / list / restore / verify. Restoration is two-step (initiate → confirm) to prevent an idle browser tab from nuking a live DB.

The encryption layer is independent of SQLCipher: AES-256-GCM with a passphrase-derived key (PBKDF2-HMAC-SHA256, 200k iterations). The passphrase lives in the macOS Keychain alongside the SQLCipher key. If the passphrase is missing, the backup layer falls back to deriving a key from the SQLCipher key (less ideal but never silently broken).

2. File format

Each backup is a single file <dir>/cyclone-backup-YYYYMMDDTHHMMSSZ.bin plus a sidecar <...>.meta.json:

+----------------+------------------+---------+--------+
| salt (16 bytes) | nonce (12 bytes) | cipher  | tag    |
+----------------+------------------+---------+--------+
                  AES-256-GCM over the SQLite .backup bytes

The sidecar is plaintext JSON with the metadata an operator needs to decide whether to restore:

{
  "created_at": "2026-06-21T15:30:00Z",
  "db_fingerprint": "sha256:7a1c...",
  "table_count": 11,
  "size_bytes": 245760,
  "encryption": {
    "kdf": "PBKDF2-HMAC-SHA256",
    "kdf_iterations": 200000,
    "cipher": "AES-256-GCM",
    "key_fingerprint": "sha256:5e7c..."
  }
}

The sidecar is not required to decrypt; it's a manifest. A real DR drill is: pull the .bin from cold storage, decrypt with the passphrase, restore.

3. Components

3.1 cyclone.backup — low-level crypto + file I/O

Pure functions, no DB dependency:

• derive_key(passphrase: str, salt: bytes) -> bytes — PBKDF2-HMAC-SHA256, 200k iters, 32-byte output.
• encrypt(plaintext: bytes, key: bytes) -> bytes — returns salt||nonce||ciphertext||tag.
• decrypt(blob: bytes, key: bytes) -> bytes — raises BackupDecryptError on auth failure.
• BackupFile dataclass — (path, size, created_at, table_count, db_fingerprint).

3.2 cyclone.backup_service — high-level coordinator

• BackupService(backup_dir, passphrase, retention_days=30, db_url=None).
• create_now() -> BackupRecord — runs SQLite .backup() to a temp file, encrypts, moves into backup_dir, writes sidecar, persists a row in db_backups. Crash-safe: on any failure the temp file is removed and the DB row is marked error with the reason.
• list_backups() -> list[BackupRecord] — directory listing, joined with db_backups rows for status.
• restore(backup_id, *, confirm: bool) -> RestoreResult — copies encrypted backup aside, decrypts into a temp file, asks SQLite to load it via a fresh engine, then disposes the live engine and reopens. Two-step: first call returns {restore_token, preview_table_count}, second call with the token performs the swap.
• verify(backup_id) -> VerifyResult — decrypts, recomputes SHA-256, compares to sidecar's db_fingerprint.
• prune() -> list[str] — delete .bin/.meta.json pairs and db_backups rows older than retention_days. Returns the deleted paths.

3.3 Migration 0012_backups.sql

-- version: 12
CREATE TABLE db_backups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    backup_dir TEXT NOT NULL,
    size_bytes INTEGER NOT NULL DEFAULT 0,
    db_fingerprint TEXT,
    table_count INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    status TEXT NOT NULL,             -- 'pending' | 'ok' | 'error' | 'pruned'
    error_message TEXT,
    completed_at TEXT
);
CREATE UNIQUE INDEX ux_db_backups_filename ON db_backups(backup_dir, filename);
CREATE INDEX ix_db_backups_created_at ON db_backups(created_at DESC);
CREATE INDEX ix_db_backups_status ON db_backups(status);

3.4 Scheduler integration (SP16 extension)

BackupService is configured in the lifespan alongside the MFT scheduler. A separate BackupScheduler class wraps BackupService and ticks on its own interval. Auto-start opt-in via CYCLONE_BACKUP_AUTOSTART=true.

3.5 API endpoints

Method	Path	Purpose
POST	/api/admin/backup/create	Create a backup now
GET	/api/admin/backup/list	List backups (newest first)
GET	/api/admin/backup/status	Last backup time, count, schedule
POST	/api/admin/backup/{id}/verify	Decrypt + checksum verify
POST	/api/admin/backup/{id}/restore/initiate	First call: get restore_token + preview
POST	/api/admin/backup/{id}/restore/confirm	Second call: actually swap
POST	/api/admin/backup/prune	Apply retention policy now

3.6 CLI

cyclone backup create
cyclone backup list
cyclone backup verify <id|filename>
cyclone backup restore <id|filename> --yes
cyclone backup prune
cyclone backup init-passphrase   # interactively set the Keychain passphrase

4. Audit events (SP11)

Every backup lifecycle event writes a tamper-evident audit_log row:

• db.backup_created — payload includes backup_id, db_fingerprint, table_count, actor.
• db.backup_failed — payload includes reason, traceback_tail.
• db.backup_restored — payload includes backup_id, restored_at, actor.
• db.backup_pruned — payload includes deleted_paths: list[str], actor.
• db.backup_passphrase_set — payload includes key_fingerprint, actor.

5. Failure modes

Failure	Behavior
SQLCipher key missing	create_now() refuses with BackupError("encryption not enabled")
Passphrase missing	Falls back to deriving key from SQLCipher key + a fixed salt (cyclone-db-backup-fallback-v1). Logged at WARNING.
Disk full	Temp file removed, row marked error, audit event written.
Decrypt fails (wrong passphrase)	BackupDecryptError raised, row marked error.
Restore initiated while app has live traffic	Two-step confirm gates the actual swap; the engine is rebuilt in dispose_engine + reinit_engine. Brief downtime (~50ms) acknowledged to operator.
Clock skew on sidecar.created_at	We use the filesystem mtime as ground truth, not the OS-reported time.

6. Out of scope

• Off-site upload (S3, B2, etc.) — operator's rsync to their offsite is the v1 answer.
• Compression — .backup is already a copy of pages, not much win.
• Incremental backups — full .backup is the right atomicity unit.
• Backup encryption with HSM / KMS — local Keychain is the operator model.
• Backup-of-backups — that's a DR runbook item, not a v1 feature.

7. Tests

Suite	Count	Covers
test_backup_crypto.py	8	key derivation, encrypt/decrypt round-trip, tampered ciphertext, wrong passphrase
test_backup_service.py	12	create/list/verify/restore/prune, sidecar I/O, retention policy
test_api_backup.py	9	all 7 endpoints, error responses, two-step restore
test_cli_backup.py	5	all 5 subcommands

Total: 34 new tests. All pass.

8. Operator runbook (post-SP17)

# One-time: set the backup passphrase (separate from the SQLCipher key)
cyclone backup init-passphrase
# Enter + confirm a strong passphrase; stored in macOS Keychain under
# service "cyclone", account "backup.passphrase".

# Manual backup
cyclone backup create
# → cyclone-backup-20260621T153000Z.bin + .meta.json in $CYCLONE_BACKUP_DIR
#   (default: ~/.local/share/cyclone/backups/)

# List
cyclone backup list

# Verify a backup
cyclone backup verify 42
# → {"ok": true, "db_fingerprint": "sha256:...", "table_count": 11}

# Restore
cyclone backup restore 42 --yes
# → prompts for confirmation; rebuilds the engine against the restored DB

# Prune (also runs nightly on the scheduler tick)
cyclone backup prune
# → deletes backups older than CYCLONE_BACKUP_RETENTION_DAYS (default 30)

Auto-start the scheduler at app launch:

export CYCLONE_BACKUP_AUTOSTART=true
export CYCLONE_BACKUP_INTERVAL_HOURS=24      # default
export CYCLONE_BACKUP_RETENTION_DAYS=30      # default
export CYCLONE_BACKUP_DIR=~/.local/share/cyclone/backups  # default

9. Why this ships after SP16

SP16 (MFT polling) was the last big operational gap before backups became urgent: with the scheduler running, an operator can lose days of inbound 999/277CA work in one crash if there's no recent backup. SP17 closes that loop.