Skip to main content

Disaster Recovery & Backup Posture

When to read this​

Read this before a store-loss incident, not during one. It states what is actually recoverable per store, what recovers by re-ingestion versus what does not, and the step-by-step recovery procedure for each. Verified against main on 2026-08-18.

Read this first: there is no backup automation in this repo

No backup, snapshot, or point-in-time-recovery job exists in scripts/, in src/inngest/, or in any migration. The platform's recovery posture depends entirely on the managed providers' own defaults (ClickHouse Cloud, Supabase) and on the fact that most warehouse data is re-derivable by re-ingestion.

Unverified

The specific backup retention and PITR window configured on the ClickHouse Cloud service and the Supabase project are dashboard settings, not repo facts. They must be read from each provider's console and recorded here before handoff sign-off. Do not assume a default is in effect.

What each store is worth, and how it recovers​

The three stores have very different recovery characteristics. Treating them uniformly is the mistake to avoid.

StoreContentsRecoverable by re-ingestion?Real risk
Supabase (Postgres)Config and tenancy: clients, brands, users/roles, account bindings, metric definitions, share tokens, and operational configuration. Trust the latest filename and migration ledger, not a hard-coded maximum.No.This is the irreplaceable configuration store. Losing it loses platform configuration, not just numbers.
ClickHouseAnalytics facts, order/attribution events, ingestion activity, and creative results. Trust the latest filename and migration ledger, not a hard-coded maximum.Mostly yes, within source lookback limits.Sources cap historical windows; aged warehouse rows may be the only copy.
Credential vaultsPer-client connector credentials and platform-wide ops-service credentials, AES-256-GCM enveloped (src/lib/crypto.ts).No — but re-obtainable by re-authorization.Losing CREDENTIAL_ENCRYPTION_KEY bricks every stored credential. The ciphertext is unrecoverable; every client connector must be re-authorized by hand. This is the highest-impact single-secret loss in the system.

Priority order for protection: Supabase first, the encryption key second, ClickHouse third. That is the inverse of data volume.

Data that expires on its own​

ClickHouse tables carry TTLs. Aged rows are deleted by the database, not archived — after the TTL there is no copy anywhere unless the source platform still serves that window.

MigrationTTL
001_clickup_ingestion_events.sqlevent_ts + INTERVAL 180 DAY
002_marketing_paid_acquisition.sqldate + INTERVAL 400 DAY (and touchpoint_ts + 400 DAY on the touchpoint grain)
007_marketing_ad_id.sqldate + INTERVAL 400 DAY
011_clickup_write_audit.sqlevent_ts + INTERVAL 180 DAY

Implication for DR planning: a restore from a backup older than the TTL horizon will not resurrect expired rows, and a re-ingestion cannot reach past the ad platform's own lookback. Anything needed beyond 400 days must be exported deliberately — nothing in the system does that today.

The recovery precedent that actually happened​

On 2026-08-07 a production ClickHouse incident destroyed the fact table mid-run. It is the best-documented recovery event in the project and it explains the guardrails now in place.

scripts/migrate-clickhouse.ts used to replay every .sql file on every run, on the premise that all migrations were idempotent. They are not — migration 007 unconditionally RENAMEs marketing_paid_acquisition aside and recreates it at its 38-column shape. A replay against an existing deployment destroyed the production fact table.

The real data survived only because 007's cleanup DROP never executed. Recovery restored it from the rename artifact marketing_paid_acquisition__pre007. That was luck, not a backup.

What changed as a result — these are load-bearing, do not remove them:

  • A migration ledger. schema_migrations records each migration by filename; every migration now runs exactly once per database.
  • Fail-closed first run. The script refuses to proceed if it finds an empty ledger alongside an existing marketing_paid_acquisition table — that combination means "deployed before the ledger existed", and applying "all pending" would repeat the incident.
  • Baseline, don't replay. Seed an existing deployment once with npx tsx scripts/migrate-clickhouse.ts --baseline, which records every current migration file as applied without executing anything.
warning

Schema changes to an already-applied migration file do not re-run — the ledger keys on filename. Ship changes as a new migration file. Editing an applied file produces a database that silently disagrees with the repo.

The one real backup artifact in the codebase is the incident table mv_ad_daily_rollup_backup_20260807, referenced by scripts/rebuild-mv-ad-daily-rollup.ts. It is dropped only via the explicit --drop-legacy-backup flag, and the script refuses to drop it if parity checks failed — the backup outlives any run that cannot prove the rebuild was correct.

What is genuinely rebuildable​

Not everything needs a backup. Know which is which before you panic.

  • mv_ad_daily_rollup needs no data rebuild. It is a plain view over the maintained latest-state paid-acquisition path, so verify its definition and source rather than repopulating it. npm run mv:rebuild is a verifier despite its historical name.
  • Recent paid-acquisition facts re-ingest by running a sync — within each platform's lookback window.
  • Ad history backfills durably via backfill:ads (src/inngest/functions/backfill-ad-history.ts), a window-walk with one walk per platform fleet-wide.
  • ClickUp sidecars rebuild via backfill:clickup-projects, repair:clickup-sidecars, and reconcile:clickup-identity.
  • Nothing in Supabase is rebuildable. There is no upstream.

Re-ingestion is idempotent by construction: ReplacingMergeTree dedupes by the table's ORDER BY grain, and synced_at derives from event.ts rather than wall-clock time, so a replay does not split one run's rows across versions.

Recovery procedures​

Supabase (configuration) loss​

  1. Stop writes — take the app offline rather than letting partial config re-populate.
  2. Restore from the Supabase project's own backup/PITR (UNVERIFIED: confirm the configured window in the Supabase dashboard).
  3. Re-run npm run migrate:supabase to confirm the ledger matches every migration file at the restored commit.
  4. Verify tenancy before reopening: run npm run test:isolation and confirm /admin/clients shows the expected client count with correct bindings.
  5. Re-verify client_ad_accounts.credential_ref pointers still resolve — a restored config row pointing at a vault entry that was rotated in the meantime is a silent no-op connector.

ClickHouse (analytics) loss​

  1. Confirm the migration ledger state before touching anything. If the ledger is empty but tables exist, run --baseline — never "apply all pending".
  2. Restore from the ClickHouse Cloud service backup if the loss is older than the platform lookback windows (UNVERIFIED: confirm retention in the ClickHouse Cloud console).
  3. Otherwise re-ingest: run syncs per client, then backfill:ads for history.
  4. Confirm mv_ad_daily_rollup is a VIEW, then compare it with the maintained v_paid_acquisition_latest truth at the same grain. Do not treat npm run mv:rebuild as a sufficient green gate until its pre-migration-058 base-table comparison is updated.
  5. Verify with npm run audit:parity-all and npm run smoke:all-tenants — do not declare recovery on row counts alone.

CREDENTIAL_ENCRYPTION_KEY loss​

There is no decryption path. Both vaults fail closed without the key, so the symptom is every connector appearing uncredentialed simultaneously.

  1. Confirm the key is genuinely lost rather than unset in one environment — check the Vercel env store per environment first, since an unset var and a lost key look identical.
  2. If lost: every stored credential must be re-authorized by hand, per client, per platform. Budget for that; see Adding a new client step 3.
  3. Set a new key, then re-enter credentials. Old ciphertext is dead weight and should be purged.

Full-region / provider outage​

The app is a single Vercel deployment against single ClickHouse Cloud and Supabase services. There is no multi-region or standby topology. A provider-region outage is an outage, not a failover. Recovery time is the provider's recovery time.

Gaps to close before handoff sign-off​

These are open items, not descriptions of existing controls.

  • Record the actual Supabase backup retention and PITR window from the dashboard, and replace the UNVERIFIED notes above.
  • Record the actual ClickHouse Cloud backup retention and restore procedure.
  • Verify CREDENTIAL_ENCRYPTION_KEY is escrowed somewhere outside the Vercel env store, held by at least two named people.
  • Agree and document an RPO and RTO per store. None exist today.
  • Perform a restore rehearsal into a disposable environment. An untested backup is a hypothesis. This has never been done.
  • Decide whether anything must outlive the 400-day ClickHouse TTL, and if so build the export — there is no long-term archive today.
  • Confirm both provider accounts survive the ownership transfer without a service interruption (see Access & Transfer).

See also​