# 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](https://docs.alai.no/books/specs/page/pillar-9-lite), 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):**
```bash
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
```bash
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
```bash
# 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
```bash
gunzip /tmp/restore.db.gz
```
Creates `/tmp/restore.db`

### Step 4: Integrity check
```bash
sqlite3 /tmp/restore.db "PRAGMA integrity_check;"
```
Expected output: `ok`

### Step 5: Sanity row count
```bash
# 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)

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

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

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

### Step 6: Live restore (if DR scenario)
```bash
# 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):**
```bash
# 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:**
```bash
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

---

## Related Documentation

- [Pillar #9 LITE spec](https://docs.alai.no/books/specs/page/pillar-9-lite) — DR-only scope reframe
- [Azure DB Backup daemon source](https://github.com/alai-holding/alai-system/blob/main/daemons/azure-db-backup.sh) (private repo)
- MC #99248 — Build B delivery
- MC #99247 — Build A (telegram relocate)