# Fueltide Supabase Restoration Runbook

Use this when the upstream Supabase project at `majxbigjafpzayzboxsf.supabase.co` is gone, broken, or you want to move to a new project.

## What this backup covers

The nightly `fueltide-backup.service` on `web-arm` produces three SQL files per run under `/var/backup/fueltide-supabase/<timestamp>/`:

- `roles.sql` — cluster roles (via `pg_dumpall --roles-only --no-role-passwords`)
- `schema.sql` — DDL: tables, functions, triggers, RLS policies, views, extensions, types (via `pg_dump --schema-only`)
- `data.sql` — all row data, including `auth.users`, `auth.identities`, `storage.objects` metadata (via `pg_dump --data-only`)
- `sha256.txt` — checksums for verification

These files are included in the nightly borgbackup run (03:00 UTC) and shipped to the Hetzner Storage Box at `u149513-sub8`.

## What this backup does **not** cover

- **Supabase Edge Functions** — lives in the `fueltide` app repo, deployed via `supabase functions deploy`. No action needed beyond redeploying from source.
- **Storage bucket files** — not in use for this project (only DB-backed data).
- **Control-plane settings** — auth providers, SMTP, email templates, API keys. These live in Supabase's dashboard, not the database. Must be reapplied manually (steps below).

---

## Restoration steps

### 1. Provision a fresh Supabase project

Dashboard → New project. Use the same region (`eu-west-1`). Record:
- New **project ref** (20-char subdomain)
- New **database password**
- New **session pooler hostname** (Project Settings → Database → Connection string → Session pooler) — the cluster prefix (`aws-1-`, `aws-0-`, etc.) may differ from the old project.

### 2. Fetch the latest dump from borg

From `web-arm.cloonar.com`:

```bash
borg-list                                       # find newest archive, e.g. web-arm-2026-04-24
mkdir -p /mnt/borg
borg-mount web-arm-2026-04-24 /mnt/borg
ls /mnt/borg/var/backup/fueltide-supabase/      # pick newest timestamped directory
cp -r /mnt/borg/var/backup/fueltide-supabase/<ts> /tmp/restore
borg umount /mnt/borg

cd /tmp/restore
sha256sum -c sha256.txt                         # verify integrity
```

If `web-arm` itself is lost, fetch from any machine with the borg SSH key + passphrase (secrets are in sops under `borg-ssh-key` / `borg-passphrase`).

### 3. Restore the database

```bash
export NEW_URL="postgres://postgres.<new-ref>:<new-pw>@<new-pooler-host>:5432/postgres"

# roles (some will error because Supabase-managed roles already exist — safe to ignore)
psql "$NEW_URL" -f /tmp/restore/roles.sql || true

# schema
psql "$NEW_URL" -f /tmp/restore/schema.sql

# data
psql "$NEW_URL" -f /tmp/restore/data.sql
```

Expected noise that is safe to ignore:
- `role "supabase_admin" already exists`, same for `authenticator`, `service_role`, `anon`, `authenticated`, `dashboard_user`
- `extension "pg_graphql" already exists` (if schema uses `CREATE EXTENSION` without `IF NOT EXISTS` for any extension not pre-installed — rare)
- `schema "auth" already exists`

Stop and investigate if you see errors like `permission denied`, `syntax error`, or `duplicate key value`.

### 4. Redeploy Edge Functions from the app repo

From a checkout of the fueltide app repo:

```bash
supabase link --project-ref <new-ref>
supabase functions deploy                       # deploys all functions in supabase/functions/
```

If specific function secrets are configured (via `supabase secrets set`), re-set them from the app repo's documented env values.

### 5. Reapply dashboard-only settings

These live in Supabase's control plane and are **not** in any dump:

| Setting | Location | Notes |
|---|---|---|
| Google OAuth provider | Authentication → Providers → Google | Client ID + secret from SOPS (commit `67e81d3` added these) |
| Apple OAuth provider | Authentication → Providers → Apple | Services ID + Team ID + Key ID + P8 key from SOPS |
| SMTP settings | Authentication → SMTP Settings | Sender `noreply@fueltide.io`, use the mail host's SMTP creds |
| Email templates | Authentication → Email Templates | Fueltide-branded magic link, confirm, recovery — bodies in commit `67e81d3` |
| API keys | Project Settings → API | A **new** `anon` and `service_role` are generated per project — copy them |

### 6. Update app clients

Update the iOS app (and any server-side callers) with:

- `SUPABASE_URL = https://<new-ref>.supabase.co`
- `SUPABASE_ANON_KEY = <new anon key>`
- `SUPABASE_SERVICE_ROLE_KEY = <new service role key>` (server-side only)

Update CSP in `hosts/web-arm/sites/fueltide.io.nix` (currently commented out, references `*.supabase.co`) if you reinstate it.

### 7. Smoke test

- Sign up + sign in via email magic link (confirms SMTP + email templates)
- Sign in via Google (confirms OAuth provider)
- Sign in via Apple (confirms OAuth provider)
- Read a known row from the largest app table (confirms data restored, RLS intact)
- Insert + read back a new row (confirms writes work)
- Call an edge function (confirms functions redeployed)

### 8. Update this backup service to point at the new project

Edit `hosts/web-arm/modules/fueltide-backup/default.nix`:

- Set `project = "<new-ref>"`
- Set `poolerHost = "<new-pooler-host>"` (the region + cluster may differ)
- If the new project is on a different Postgres major version, update `pg = pkgs.postgresql_XX`

Rotate the `fueltide-supabase-db-password` secret in `hosts/web-arm/secrets.yaml` via:

```bash
nix-shell -p sops --run 'sops hosts/web-arm/secrets.yaml'
```

Deploy, then run `systemctl start fueltide-backup.service` manually on `web-arm` and verify a new dump lands under `/var/backup/fueltide-supabase/`.