# Azure Blob Offsite Backup Setup

# Azure Blob Offsite Backup Setup

> **⚠️ STALE-DOC BANNER (2026-08-08, MC #106946):** §2 below ("What happens", steps 1-3) describes an **SSH-to-Azure-VM flow that no longer exists**. Since the 2026-08-03 LightRAG migration (#106747), the script runs entirely local `docker run`/`docker compose -f docker-compose.local.yml` on this Mac — no SSH, no VM. The 4 volume names listed (`lightrag-data`, `lightrag-kg`, `lightrag-cache`, `lightrag-neo4j-data`) are also stale/dead; the live set is 7 volumes under the `lr106747-*` prefix. Corrected, current detail: [LightRAG Backup runbook](./lightrag-backup.md) (updated 2026-08-08). Not fully rewriting this page — see MC #106946 evidence for what changed and why.

**Status:** LIVE — verified against running processes, LaunchAgent plists and config on 2026-07-28.
**Owner:** FlowForge (infra) / John (orchestration)
**Scope note:** This page documents specifically the **Azure Blob** offsite paths. For the full 4-layer ALAI backup picture (including Git and Backblaze B2), see [ALAI Backup Strategy](./alai-backup-strategy.md).

---

## 1. There are two independent Azure Blob backup paths — one active, one scaffolded

| Path | Storage account | Status |
|---|---|---|
| LightRAG / Neo4j Docker volumes | `plockfrontstaging` (swedencentral) | **ACTIVE** — weekly |
| SQLite databases (mission-control, hivemind, costs, events, etc.) | `alaibackups0ebb` (RG `alai-backups-rg`) | **Scaffolded, disabled** — see §3 |

These are unrelated Azure storage accounts serving different data, set up at different times. Do not confuse them.

---

## 2. ACTIVE: LightRAG/Neo4j → `plockfrontstaging`

**Script:** `~/system/tools/lightrag-backup.sh`
**LaunchAgent:** `com.alai.lightrag-backup.plist` — weekly, Sunday 04:00 CEST
**Credentials:** `~/system/config/azure-lightrag-backup.env` (mode 0600) — `AZURE_STORAGE_ACCOUNT`, `AZURE_STORAGE_KEY`, `AZURE_STORAGE_CONTAINER`

**What happens:**
1. SSH to Azure VM `vm-alai-lightrag` (20.240.61.67)
2. `docker compose stop lightrag neo4j` (graceful, ~30-90s downtime)
3. Tar each of 4 Docker volumes (`lightrag-data`, `lightrag-kg`, `lightrag-cache`, `lightrag-neo4j-data`)
4. `docker compose start neo4j lightrag`
5. SHA-256 manifest + README written alongside the snapshot
6. SCP snapshot back to Mac Studio (`~/system/backups/lightrag/`, local safety net, keep last 4)
7. **Azure Blob upload** — `az storage blob upload` per file, `--tier Cool`, blob path `<container>/<timestamp>/<file>`
8. **Azure-side rotation** — keep last 8 snapshots (~8 weeks), older ones deleted via `az storage blob delete-batch`

**Azure resource details:**
- Storage account: `plockfrontstaging` (swedencentral, Hot-tier account — a pre-existing Plock account being reused for this, not a dedicated ALAI backup account)
- Container: `lightrag-backup`
- Resource group: `plock-staging-rg`
- Per-blob access tier: Cool
- Retention: last 8 snapshots offsite (vs. 4 kept locally)
- Cost: **~$0.05–0.10/month** for ~4 GB retained

**Restore:** full step-by-step (download-batch, checksum verify, volume restore to Azure VM or to Mac Studio) is documented in [LightRAG Backup runbook](./lightrag-backup.md) — do not duplicate here, that page is the source of truth for restore procedure.

---

## 3. SCAFFOLDED / DISABLED: SQLite → `alaibackups0ebb`

This path exists in config and credentials but is **intentionally not the live path** as of 2026-07-28.

**Storage account:** `alaibackups0ebb`, resource group `alai-backups-rg`
**Containers (per original design):**
| Container | Purpose |
|---|---|
| `system-db-backups` | SQLite database blobs (litestream `type: abs` replicas) |
| `system-git-bundles` | Git bundle exports |
| `bitwarden-exports` | Vaultwarden/Bitwarden export snapshots |

**Credentials:** `~/system/config/azure-backup.env` (mode 600)
- `AZURE_BACKUP_SP_APPID` = `1a0b3018-0c31-474b-918f-531b0a29a669` (SP name: `alai-backup-writer`) — same appid used as the general-purpose az-cli identity elsewhere in the system, not a narrowly-scoped backup-only SP
- `AZURE_BACKUP_SP_TENANT` = `3454a03f-20b4-4bda-a116-2293c459aecd`
- `AZURE_SUBSCRIPTION_ID` = `5b0b4d9b-e677-464e-abf0-5170cbce3b8e`
- `VAULT_EXPORTER_APPID` / `VAULT_EXPORTER_SECRET` = still `FILL_AFTER_SP_CREATION` — the narrower-scoped SP intended for the `bitwarden-exports` container only was **never created**. Do not assume it exists.

**LaunchAgent:** `com.alai.azure-db-backup.plist.disabled` (file dated 2026-04-20) — filename literally carries `.disabled`, `RunAtLoad=false`, never renamed to an active `.plist`. Confirmed present but inert on disk 2026-07-28.

**Litestream (`~/system/config/litestream.yml`):** documents `alai-backup-writer` as the intended Azure auth for streaming SQLite replication, and originally shipped replicas of `type: abs` (Azure Blob Storage) pointed at `system-db-backups`. As of 2026-07-28 **all 64 configured databases use `type: file` (local) replicas only** — zero `type: abs` replicas remain.

### Why it's off — CEO decision, MC #105462 (2026-07-13)

Litestream's per-sync-tick `ListBlobs` full-prefix enumeration against `alaibackups0ebb` (driven mainly by two hot telemetry DBs — `flywheel` ~113K blobs, `mission-control` ~102K blobs) drove egress up to **518 GB/day (~370 NOK/day)**. After sync-interval tuning and lifecycle-rule fixes proved insufficient to bring it under control, CEO decision (verbatim): *"Prebaci lokalno! Nemamo placene korisnika kad to dodje ide backup na azure do tada save money gdje mozemo."* — move everything to local replicas; Azure Blob SQLite backup returns once ALAI has paying customers. Full incident record: [Azure litestream egress saga + backup policy — MC #105462](./azure-litestream-egress-saga-105462.md).

### Reactivation checklist (when triggered)

1. Re-verify RBAC on `alai-backups-rg` / `alaibackups0ebb` — the current az-cli SP identity returned `AuthorizationFailed` reading this account/RG as of 2026-07-28; access is **not currently confirmed**.
2. Re-enable the LaunchAgent: rename `com.alai.azure-db-backup.plist.disabled` → `com.alai.azure-db-backup.plist`, `launchctl load`.
3. Re-point at least the P0-financial tier of `litestream.yml` replicas back to `type: abs`.
4. Re-scope the account-wide Azure lifecycle rule fixed under #105490 (it lacked `prefixMatch` and archived L0 blobs for 35 dormant DBs during the original incident) before turning replication back on.

### 3a. Full target breakdown, blob naming, and SHA-256 sidecar pattern

`~/system/daemons/azure-db-backup.sh` (present on disk, not currently invoked by any active LaunchAgent) backs up **6 targets**, not just SQLite — read directly from the script 2026-07-28:

| Target | Source | Method | Blob path |
|---|---|---|---|
| LightRAG volume | Docker volume `lightrag-data` | `docker run alpine tar czf` | `lightrag/lightrag-data-<DATE>.tar.gz` |
| Neo4j volume | Docker volume `lightrag-neo4j-data` | `docker run alpine tar czf` | `neo4j/neo4j-data-<DATE>.tar.gz` |
| Postgres — Drop | container `drop-postgres-1`, user `drop` | `docker exec pg_dumpall -U drop \| gzip` | `postgres-drop/<DATE>/drop-postgres-1-<DATE>.sql.gz` |
| Postgres — Drop Srbija | container `dropsrbija-postgres`, user `dropsrbija` | `docker exec pg_dumpall -U dropsrbija \| gzip` | `postgres-dropsrbija/<DATE>/dropsrbija-postgres-<DATE>.sql.gz` |
| Qdrant | REST API (per-collection snapshot trigger) against `QDRANT_URL` set in-script; falls back to pausing the `qdrant` container and tarring its volume/bind-mount if the API is unreachable | curl download of snapshot tar, or `docker run alpine tar czf` on the paused volume | `qdrant/<DATE>/<collection>-<DATE>.tar` (API path) or `qdrant/<DATE>/qdrant-volume-<DATE>.tar.gz` (fallback path) |
| SQLite | `~/system/backups/databases/*-<DATE>.db` (already-rotated local dumps, e.g. mission-control, hivemind, costs, events, teams, baikal-caldav, routing-outcomes) | direct file upload, one blob per DB | `sqlite/<DATE>/<db-name>-<DATE>.db` |
| Manifest | list of what was uploaded this run | plain text | `MANIFEST-<DATE>.txt` (container root, not date-prefixed) |

Container: `system-db-backups` on `alaibackups0ebb`, all under the account's default access tier via `az storage blob upload` — no explicit `--tier Cool` flag in this script, unlike the LightRAG-active path in §2.

**⚠️ Overlap note:** the LightRAG/Neo4j volume targets in this script duplicate the data already covered by the **active** §2 path (`plockfrontstaging`, weekly). If this LaunchAgent is ever re-enabled without removing the `VOLUMES_TO_BACKUP` block, LightRAG/Neo4j will be backed up to *two* separate Azure storage accounts on two separate schedules — resolve intentionally, don't silently double-backup.

**Live container check (2026-07-28):** `drop-postgres-1`, `dropsrbija-postgres`, and `qdrant` are **not currently running** on this host (`docker ps` clean of all three). The script's own guards (`docker ps | grep -qx` / `docker volume inspect`) would WARN-and-skip each of these targets untouched if run today — this is not a script bug, it degrades safely, but it means reactivating the LaunchAgent alone would not actually back up Postgres/Qdrant unless those containers are also brought up on this host.

**SHA-256 sidecar pattern (`upload_blob()`, applies to every target above):**
1. `shasum -a 256` the local file *before* upload → `sha_before`
2. Write a sidecar file `<local_file>.sha256` in standard `shasum -c`-compatible format: `<hash>  <filename>`
3. Upload the main blob, then upload the `.sha256` sidecar as `<blob_path>.sha256`
4. **Round-trip verify:** immediately re-download the just-uploaded blob to `/tmp`, re-hash it (`sha_after`)
5. `sha_before == sha_after` → count as success, log `SHA-256 verified OK`. Mismatch → `ERROR: SHA-256 MISMATCH`, counted as a failure (`backup_fail++`), **no automatic retry** — surfaces via the `MAX_FAILURES=3` consecutive-run counter → Slack `#ops` alert + `alert-gate.js escalate`.

### 3b. Restore commands per target

**No dedicated restore script exists on disk as of 2026-07-28** (`find ~/system -iname "*restore*azure*"` returns nothing) — the commands below are the direct reverse of the backup logic above, for use if/when this path is reactivated. Verify the sidecar hash before trusting any restored data.

**1. Download + verify any blob (do this first, for every target):**
```bash
az storage blob download --account-name alaibackups0ebb --container-name system-db-backups \
  --name "<blob-path>" --file "/tmp/<file>" --auth-mode login
az storage blob download --account-name alaibackups0ebb --container-name system-db-backups \
  --name "<blob-path>.sha256" --file "/tmp/<file>.sha256" --auth-mode login
(cd /tmp && shasum -a 256 -c "<file>.sha256")   # must print OK before proceeding
```

**2. Postgres (Drop / Drop Srbija)** — `pg_dumpall` output is plain SQL, restore with `psql`, not `pg_restore`:
```bash
gunzip -c /tmp/drop-postgres-1-<DATE>.sql.gz | docker exec -i drop-postgres-1 psql -U drop
gunzip -c /tmp/dropsrbija-postgres-<DATE>.sql.gz | docker exec -i dropsrbija-postgres psql -U dropsrbija
```

**3. Qdrant — API-snapshot path:** the downloaded `.tar` is a Qdrant collection snapshot (same format the backup script pulled via the collection snapshots endpoint, against the `QDRANT_URL` the script points at). Recovering it requires Qdrant's snapshot-recovery API against a **running** Qdrant instance — not verified live here since the `qdrant` container is not currently running on this host (§3a). Confirm the exact recovery call against Qdrant's own API docs for the deployed version before using in production; do not copy an unverified endpoint from this runbook.

**3b. Qdrant — volume-fallback path** (if the backup was taken via the paused-container tar fallback):
```bash
docker pause qdrant
docker run --rm -v <qdrant_volume>:/dst -v /tmp:/src alpine \
  sh -c "rm -rf /dst/* && tar xzf /src/qdrant-volume-<DATE>.tar.gz -C /dst"
docker unpause qdrant
```

**4. LightRAG / Neo4j Docker volumes:**
```bash
docker volume create lightrag-data   # only if the volume doesn't already exist
docker run --rm -v lightrag-data:/dst -v /tmp:/src alpine \
  sh -c "tar xzf /src/lightrag-data-<DATE>.tar.gz -C /dst"
# repeat with lightrag-neo4j-data / neo4j-data-<DATE>.tar.gz
```
Prefer the §2 restore runbook ([LightRAG Backup](./lightrag-backup.md)) for this data in practice — it's the actively-maintained path.

**5. SQLite:** no container involved, just place the verified file:
```bash
cp /tmp/<db-name>-<DATE>.db ~/system/databases/<db-name>.db
```
Stop any process holding the live DB open (litestream, the owning service) before overwriting it.

---

## 4. Setup commands (as originally provisioned, for reference)

Creating the general-purpose backup SP (already done — this is what `AZURE_BACKUP_SP_APPID` above corresponds to):
```bash
az ad sp create-for-rbac --name alai-backup-writer --role "Storage Blob Data Contributor" \
  --scopes /subscriptions/5b0b4d9b-e677-464e-abf0-5170cbce3b8e/resourceGroups/alai-backups-rg/providers/Microsoft.Storage/storageAccounts/alaibackups0ebb
```

Creating the narrower vault-export-only SP (documented, **never executed** — `VAULT_EXPORTER_APPID` still unfilled):
```bash
az ad sp create-for-rbac --name alai-vault-exporter --role "Storage Blob Data Contributor" \
  --scopes /subscriptions/5b0b4d9b-e677-464e-abf0-5170cbce3b8e/resourceGroups/alai-backups-rg/providers/Microsoft.Storage/storageAccounts/alaibackups0ebb/blobServices/default/containers/bitwarden-exports
```

---

## Related

- [ALAI Backup Strategy](./alai-backup-strategy.md) — full 4-layer picture (Git, SQLite, LightRAG, DR)
- [LightRAG Backup (Azure-native + local safety net)](./lightrag-backup.md) — full restore procedure for the active Azure path
- [Azure litestream egress saga + backup policy — MC #105462](./azure-litestream-egress-saga-105462.md) — why the SQLite→Azure path was turned off
- [Disaster Recovery Runbook](./disaster-recovery-runbook.md)

---

**Document Owner:** Skillforge
**Last Verified:** 2026-07-28 — `~/Library/LaunchAgents/com.alai.azure-db-backup.plist.disabled` (present, inert), `~/system/config/litestream.yml` (64/64 DBs `type: file`), `~/system/config/azure-backup.env` and `azure-lightrag-backup.env` (contents read directly), `~/system/tools/lightrag-backup.sh` (script read directly), cross-checked against `~/system/docs/runbooks/alai-backup-strategy.md` (same-day verification).