Backup & restore

A self-hosted Spanlens deployment keeps everything in one Postgres database, plus one secret that lives outside it. This page is the operator runbook for dumping that database, restoring it, and handling the secret that makes the restore worth anything.

What you are backing up

There is one datastore. Your Supabase project holds organizations, projects, API keys, encrypted provider keys, traces, spans, prompts, evals, billing, and the requests log of every proxied LLM call. The bundled docker-compose.yml runs two services, web and server, and declares no volumes. Both containers are disposable. Delete them, re-pull, start again, and nothing is lost.

WhatWhere it livesIf you lose it
Everything Spanlens storesYour Supabase Postgres databaseCatastrophic. Accounts, keys, configuration, and history all go at once.
ENCRYPTION_KEYYour secret manager and the server's environmentStored provider keys stay encrypted forever. Everything else in the dump still restores, and users re-enter their provider keys.

Supabase Postgres

Managed Supabase projects take their own backups (Project Settings → Database → Backups). On the Pro plan that is a daily backup with 7 days of history. 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 on port 5432 for dumps, not the transaction pooler on 6543 that the server uses at runtime. 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, since 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 ships no Postgres container of its own.

The request log dominates the dump

requests stores prompt and response bodies, so on a busy deployment it is larger than every other table put together. It is also the one table you can afford to lose: it is observability, not the source of truth for accounts or keys. If you want a small, fast dump for daily rotation, drop its rows and keep its schema.

# Schema for every table, data for everything except the request log
pg_dump \
  "postgresql://postgres:<password>@db.<ref>.supabase.co:5432/postgres" \
  --format=custom --no-owner --no-privileges \
  --exclude-table-data='public.requests*' \
  --file=spanlens-core-$(date +%F).dump

The trailing * matters. requests is partitioned by month, so the rows live in child tables named requests_2026_08 and so on, and a pattern without the wildcard would exclude the empty parent and dump every partition anyway. It also catches requests_fallback, the short-lived queue of rows waiting to be written into the log, which is the behaviour you want here: if you are skipping the log, you are skipping what was about to join it. Run \dt+ public.requests* in psql to see the partitions and their sizes.

Restore

Restore a custom-format dump with pg_restore, and 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-08-01.dump

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

Restoring into a brand-new project? Run supabase/init.sql first if the schema is not already there. Every statement is CREATE IF NOT EXISTS / ALTER IF NOT EXISTS, so re-running is safe. Then restore the data dump on top.

Then make sure the current month has a partition to write into:

-- Creates the current month plus the next three, and is safe to re-run.
SELECT * FROM ensure_requests_partitions(3);

A dump taken months ago carries the partitions that existed then. Without this call the first proxied request after a restore still gets logged, into the requests_default catch-all, and getting those rows into the right partition afterwards means detaching the default table under a lock. One line now is cheaper. Schedule the same call monthly while you are at it.

The ENCRYPTION_KEY is not in the dump

Provider keys (your real OpenAI / Anthropic / Gemini keys) are stored encrypted with AES-256-GCM under ENCRYPTION_KEY. The ciphertext travels inside the Postgres dump, but it is useless without the exact same ENCRYPTION_KEYthat encrypted it. Restore the database under a different key and every provider key silently decrypts to garbage (an empty string), which surfaces 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 matching ENCRYPTION_KEY. Treat them as one unit.
  • Lose the key and the encrypted provider keys are unrecoverable, so users must re-enter them. Everything else in the dump (orgs, projects, traces, request history) restores fine.

Retention, scheduling, and restore drills

  • Automate it. One pg_dump in cron or a systemd timer is the whole job. 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 and 4 weekly. A simple find backups/ -name '*.dump' -mtime +7 -delete caps local disk.
  • Two sizes if the log is big. A nightly core dump that excludes requests data stays small enough to keep for months. Take the full dump weekly. You lose at most a week of request history in a restore, and no account or key data at all.
  • Know what the server deletes on its own.Request rows are hard deleted at 365 days by dropping that month's partition. Shorter per-plan windows (14 days on Free, 90 on Pro, 365 on Team) are applied when the dashboard queries, not by deleting rows, so a plan upgrade brings older data back into view. See plan retention.
  • Store the key with the backups' provenance, not the backups. Keep the current ENCRYPTION_KEY in your secret manager and write down which key each dump was taken under.
  • Run a restore drill. A backup you have never restored is a guess. Periodically restore into a throwaway project, pair it with the matching ENCRYPTION_KEY, call ensure_requests_partitions, 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).