Skip to main content

SQLite DB Backup — Pillar #9 LITE

SQLite DB Backup — Pillar #9 LITE

Purpose: Restore drill procedure for SQLite databases backed up via azure-db-backup.sh wrapper extension (Build B of Pillar #9 LITE).

Related: Pillar #9 LITE spec, MC #99248

1. Wrapper Extension Overview

What was added: Build B extended ~/system/daemons/azure-db-backup.sh (existing 4h-interval LaunchAgent daemon) to include SQLite database snapshots alongside Docker volume backups.

Implementation (lines 466-471):

SQLITE_DBS=(
    "mission-control:$HOME/system/databases/mission-control.db"
    "hivemind:$HOME/system/databases/hivemind.db"
    "costs:$HOME/system/databases/costs.db"
    "knowledge:$HOME/system/databases/knowledge.db"
)

Process for each DB:

  1. sqlite3 .backup creates consistent snapshot (not file copy mid-write)
  2. gzip compression
  3. sha256 sidecar file generation
  4. Upload to Azure Blob Storage at sqlite/YYYY-MM-DD/<db-name>.db.gz
  5. Corresponding .sha256 sidecar uploaded

Storage location: Azure Storage Account alaibackups, container backups, blob prefix sqlite/<DATE>/

Evidence: First backup run on 2026-05-05 produced 8 blobs (4 databases + 4 sha256 sidecars):

  • mission-control.db.gz (4.5 MB, 10,797 tasks)
  • hivemind.db.gz (52.8 MB)
  • costs.db.gz (179 KB)
  • knowledge.db.gz (144.5 MB)

2. Restore Drill Procedure

Use this procedure to validate backup integrity or perform disaster recovery.

Step 1: Download blob

az storage blob download \
  --account-name alaibackups \
  --container-name backups \
  --name "sqlite/2026-05-05/mission-control.db.gz" \
  --file /tmp/restore.db.gz \
  --auth-mode login

Step 2: Verify sha256 checksum

# Download sidecar
az storage blob download \
  --account-name alaibackups \
  --container-name backups \
  --name "sqlite/2026-05-05/mission-control.db.gz.sha256" \
  --file /tmp/restore.db.gz.sha256 \
  --auth-mode login

# Verify
sha256sum -c /tmp/restore.db.gz.sha256

Expected output: /tmp/restore.db.gz: OK

Step 3: Decompress

gunzip /tmp/restore.db.gz

Creates /tmp/restore.db

Step 4: Integrity check

sqlite3 /tmp/restore.db "PRAGMA integrity_check;"

Expected output: ok

Step 5: Sanity row count

# For mission-control.db
sqlite3 /tmp/restore.db "SELECT COUNT(*) FROM tasks;"

Expected: >10,000 tasks (baseline as of 2026-05-05: 10,797 rows)

# For hivemind.db
sqlite3 /tmp/restore.db "SELECT COUNT(*) FROM sessions;"

# For costs.db
sqlite3 /tmp/restore.db "SELECT COUNT(*) FROM runs;"

# For knowledge.db
sqlite3 /tmp/restore.db "SELECT COUNT(*) FROM entries;"

Step 6: Live restore (if DR scenario)

# Backup current DB first
cp ~/system/databases/mission-control.db ~/system/databases/mission-control.db.pre-restore-$(date +%s)

# Replace with restored copy
mv /tmp/restore.db ~/system/databases/mission-control.db

# Verify MC tool works
node ~/system/tools/mc.js list | head -5

3. Known Gap — RBAC Fix Needed

Issue: Service principal 1a0b3018 (used by azure-db-backup.sh) currently lacks the Microsoft.Compute/virtualMachines/runCommand/action permission on resource group alai-backups-rg.

Impact: Remote restore drills on Azure VM fail. Workaround: perform restore drill on ANVIL (local machine) after downloading blob.

Fix required (Azure admin or CEO):

# Get service principal object ID
az ad sp show --id 1a0b3018-xxxx-xxxx-xxxx-xxxxxxxxxxxx --query id -o tsv

# Assign Virtual Machine Contributor role at resource group scope
az role assignment create \
  --assignee <SP-OBJECT-ID> \
  --role "Virtual Machine Contributor" \
  --scope /subscriptions/<SUBSCRIPTION-ID>/resourceGroups/alai-backups-rg

Verification after fix:

az vm run-command invoke \
  --resource-group alai-backups-rg \
  --name vm-alai-support \
  --command-id RunShellScript \
  --scripts "sqlite3 --version"

Testing Schedule

  • Automated backups: Every 4 hours via com.alai.azure-db-backup LaunchAgent
  • Restore drill cadence: Monthly (first business day of month)
  • Success criteria: All 5 steps pass for mission-control.db

No comments to display

Back to top