Backup & restore

A self-hosted Spanlens deployment keeps its data in two places. This page is the operator runbook for backing both of them up, restoring from those backups, and the one secret that lives outside every database and must be backed up on its own.

Two datastores, two backup strategies

Spanlens splits its data across two stores on purpose (see Self-hosting and Data model). Both must be backed up.

Note the split in the bundled docker-compose.yml: it runs three services, web, server, and clickhouse. There is no Postgres container. Postgres is your Supabase project, managed separately, so its backup runs against the Supabase connection string rather than a local container.

Postgres (Supabase)

Managed Supabase projects have their own automated backups (Project Settings → Database → Backups). Take your own logical dumps on top of that so you hold a copy outside the provider and can restore into any Postgres 17 target.

Back up with pg_dump

Grab the connection string from Project Settings → Database (use the direct connection, port 5432). The custom format (-Fc) restores selectively and compresses well.

# Full logical dump, custom format
pg_dump \
  "postgresql://postgres:<password>@db.<ref>.supabase.co:5432/postgres" \
  --format=custom --no-owner --no-privileges \
  --file=spanlens-pg-$(date +%F).dump

# Plain SQL alternative (human-readable, larger)
pg_dump "postgresql://postgres:<password>@db.<ref>.supabase.co:5432/postgres" \
  --no-owner --no-privileges \
  > spanlens-pg-$(date +%F).sql

--no-owner --no-privileges keeps the dump portable across projects (the Supabase-managed roles differ per project). If you self-host Postgres elsewhere, dump with docker exec <your-postgres-container> pg_dump ... instead. The bundled compose file does not ship a Postgres container.

Restore Postgres

Restore a custom-format dump with pg_restore; restore a plain SQL dump with psql. Point at a fresh Supabase project (or any empty Postgres 17 database).

# Restore a custom-format (.dump) backup
pg_restore \
  --dbname="postgresql://postgres:<password>@db.<new-ref>.supabase.co:5432/postgres" \
  --no-owner --no-privileges --clean --if-exists \
  spanlens-pg-2026-07-01.dump

# Restore a plain SQL (.sql) backup
psql "postgresql://postgres:<password>@db.<new-ref>.supabase.co:5432/postgres" \
  -f spanlens-pg-2026-07-01.sql

After restoring into a brand-new project, re-run supabase/init.sql first if the schema is not already present (all statements are CREATE IF NOT EXISTS / ALTER IF NOT EXISTS, so re-running is safe), then restore the data dump on top.

The ENCRYPTION_KEY is not in the dump you can rely on

Provider keys (your real OpenAI / Anthropic / Gemini keys) are stored encrypted with AES-256-GCM under ENCRYPTION_KEY. The encrypted ciphertext travels inside the Postgres dump, but it is useless without the exact same ENCRYPTION_KEYthat encrypted it. Restore the database with a different key and every provider key silently decrypts to garbage (an empty string), surfacing later as “wrong API key” errors from the upstream provider.

• Back up ENCRYPTION_KEY separately and securely in a secret manager (AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault), never alongside the database dump.
• A restore is only complete when the restored database is paired with the matchingENCRYPTION_KEY. Treat them as one unit.
• Lose the key and the encrypted provider keys are unrecoverable; users must re-enter them. Everything else in the dump (orgs, projects, traces) restores fine.

ClickHouse (request logs)

In the bundled stack the ClickHouse service is named clickhouse, listens on 8123 (HTTP) and 9000 (native), and defaults to user spanlens / database spanlens. All backup commands below run against that service. On ClickHouse Cloud, use the automatic backups it provides and swap the host/credentials for your managed endpoint.

Native BACKUP (recommended)

ClickHouse's built-in BACKUP statement writes a consistent snapshot of the requests table (and its schema). Run it through clickhouse-client inside the container.

# Back up the requests table to a local directory backup
docker compose exec clickhouse clickhouse-client \
  --user spanlens --password "$CLICKHOUSE_PASSWORD" --database spanlens \
  --query "BACKUP TABLE spanlens.requests TO Disk('backups', 'requests-$(date +%F).zip')"

# Back up straight to S3-compatible storage
docker compose exec clickhouse clickhouse-client \
  --user spanlens --password "$CLICKHOUSE_PASSWORD" --database spanlens \
  --query "BACKUP TABLE spanlens.requests TO S3('https://<bucket>.s3.amazonaws.com/spanlens/requests-$(date +%F)', '<access-key>', '<secret-key>')"

The Disk('backups', ...) target requires a backups disk configured in the ClickHouse server config; the S3 target works out of the box. For an operator-friendly wrapper with rotation and incremental support, clickhouse-backup is a common third-party tool.

Portable dump with SELECT INTO OUTFILE

For a plain, portable dump with no disk/S3 config, stream the table out as compressed native or CSV data.

# Native format, gzip-compressed (round-trips fastest)
docker compose exec -T clickhouse clickhouse-client \
  --user spanlens --password "$CLICKHOUSE_PASSWORD" --database spanlens \
  --query "SELECT * FROM spanlens.requests FORMAT Native" \
  | gzip > requests-$(date +%F).native.gz

Restore ClickHouse

Restore is the inverse of whichever backup you took. Point at a fresh clickhouse container (or Cloud service) and re-create the schema first if it is empty.

# From a native BACKUP
docker compose exec clickhouse clickhouse-client \
  --user spanlens --password "$CLICKHOUSE_PASSWORD" --database spanlens \
  --query "RESTORE TABLE spanlens.requests FROM Disk('backups', 'requests-2026-07-01.zip')"

# From a SELECT ... INTO OUTFILE native dump
gunzip -c requests-2026-07-01.native.gz \
  | docker compose exec -T clickhouse clickhouse-client \
      --user spanlens --password "$CLICKHOUSE_PASSWORD" --database spanlens \
      --query "INSERT INTO spanlens.requests FORMAT Native"

Re-run the migrations after any ClickHouse restore. The migrations are idempotent (every statement is CREATE ... IF NOT EXISTS / ALTER ... ADD COLUMN IF NOT EXISTS), so this is safe whether the restored data already has the latest schema or a slightly older one. It fills in any columns or views added since the backup was taken.

# From a clone of the repo (loads apps/server/.env if present)
pnpm ch:migrate

# Or standalone, pointed at your ClickHouse
CLICKHOUSE_URL=http://localhost:8123 \
CLICKHOUSE_USER=spanlens CLICKHOUSE_PASSWORD=<password> CLICKHOUSE_DB=spanlens \
  npx -y tsx clickhouse/apply.ts

Coarse alternative: snapshot the Docker volumes

If you want a blunt, filesystem-level backup of the whole ClickHouse container instead of a logical dump, snapshot its named volumes directly. The bundled docker-compose.yml declares exactly two named volumes, clickhouse_data (the data directory, /var/lib/clickhouse) and clickhouse_logs (server logs, /var/log/clickhouse-server). Only clickhouse_data holds yourrequests rows; clickhouse_logs is optional.

Compose prefixes volume names with the project name (usually the directory name), so the real volume is often <project>_clickhouse_data. Run docker volume ls to see the exact names.

# Stop the container first for a consistent, crash-safe snapshot
docker compose stop clickhouse

# Tar the data volume into the current directory
docker run --rm \
  -v spanlens_clickhouse_data:/data:ro \
  -v "$PWD":/backup \
  busybox tar czf /backup/clickhouse_data-$(date +%F).tar.gz -C /data .

# Restart
docker compose start clickhouse

Restore into a fresh, empty volume:

docker run --rm \
  -v spanlens_clickhouse_data:/data \
  -v "$PWD":/backup \
  busybox sh -c "cd /data && tar xzf /backup/clickhouse_data-2026-07-01.tar.gz"

docker compose up -d clickhouse
pnpm ch:migrate   # top up the schema, idempotent

A volume snapshot is a full-container image, not a portable table dump: restore it into the same ClickHouse major version (clickhouse/clickhouse-server:24.10-alpine in the bundled compose file) to avoid on-disk format surprises. Prefer the logical BACKUP / SELECT dumps above when you need portability.

Retention, scheduling, and restore drills

• Automate it. Wrap the Postgres and ClickHouse dumps in one script and run it from cron (or a systemd timer). A daily dump with a $(date +%F) filename gives you point-in-time recovery per day.
• Rotate. Push dumps to off-box storage (S3, a backup host) and prune old ones, for example keep 7 daily + 4 weekly. A simple find backups/ -name '*.dump' -mtime +7 -delete caps local disk.
• Match retention to value. Postgres holds the crown jewels, keep those dumps long. ClickHouse is observability, so a shorter horizon is fine, and plan retention already caps how far back the server reads the requests log anyway.
• Store the key with the backups' provenance, not the backups. Keep the current ENCRYPTION_KEY in your secret manager and document which key each Postgres dump was taken under.
• Run a restore drill. A backup you have never restored is a guess. Periodically restore both stores into a throwaway stack, pair them with the matching ENCRYPTION_KEY, run pnpm ch:migrate, and confirm the dashboard loads and a stored provider key still decrypts by making one proxied call.

Related: Self-hosting (stack layout and env vars), Keys & encryption (how provider keys are encrypted), Data export (per-workspace exports).