Disaster recovery

A runbook for the person on call. Each failure mode below lists what data is at risk, what protects it automatically, and the steps to recover. Pair this with Reliability (how the system degrades) and Backup and restore (the restore commands).

Recovery objectives

Spanlens is designed so a dependency outage never fails your end users' LLM calls: the proxy returns the provider response before any logging happens. The risk in an outage is observability data (request logs, traces, usage), not your application traffic. Everything Spanlens stores is in one Postgres database, so the recovery story is short, and one restore covers all of it.

The ENCRYPTION_KEY is not in any of those backups by design. A restore is only usable when it is paired with the key the data was encrypted under. See the backup runbook.

The request log stops filling

Request rows are written over a pooled connection, separately from the PostgREST calls the rest of the server makes. That insert can fail on its own while the database is otherwise fine: the pooler is saturated, a statement hit the timeout, or a deploy is writing a column the schema does not have yet. The proxy keeps serving traffic throughout, because the row is written after the response has left.

Automatic recovery: a failed insert is queued into requests_fallback over PostgREST rather than dropped, and /cron/replay-fallback drains the queue back into requests every 5 minutes. The replay insert ends in ON CONFLICT (created_at, id) DO NOTHING, so replaying a batch that partially landed cannot duplicate a row or inflate anyone's cost.

Manual steps if the backlog is not draining:

1. Check GET /health/ready. It probes the database twice, once over PostgREST and once over the pooled connection, so it tells you which of the two is broken.
2. Read the queue depth from GET /health/deep under fallback.queue. A number that climbs and never falls means the replay cron is not firing (see cron dropout below).
3. Trigger a drain by hand:
   bashcopy

   curl -X GET https://api.spanlens.io/cron/replay-fallback \
     -H "Authorization: Bearer $CRON_SECRET"

4. If the drain still fails, read last_error on the queued rows. It is the insert error verbatim, and it usually names the problem outright.
   sqlcopy

   SELECT left(last_error, 200) AS err, count(*), min(created_at) AS oldest
   FROM requests_fallback
   GROUP BY 1
   ORDER BY 2 DESC;

5. Rows are expired after 7 days or 100 retries to bound queue size. That is the only window in which request-log data is permanently lost.

When the queue exceeds 1000 rows an internal_alerts row (kind fallback_queue_high) is raised and shown at /admin/alerts.

Rows land in requests_default

requests is partitioned by month. A partition for the current month is normally created several months ahead of time. If that ever stops happening, inserts do not fail: rows fall into the requests_default catch-all partition instead. Nothing is lost, and dashboards still read the rows, so this can run unnoticed for weeks. The cost comes later, because the real partition for that month can no longer be created while conflicting rows sit in the default table.

1. Check for occupants. An empty default partition is the healthy state.
   sqlcopy

   SELECT count(*) FROM requests_default;

2. Create the missing partitions. The function is idempotent and returns which months it had to create.
   sqlcopy

   SELECT * FROM ensure_requests_partitions(3);

3. If step 1 found rows, move them out during a quiet window: DETACH the default partition, insert its rows back into requests, then re-attach the emptied table. Detaching takes a lock, so do it deliberately rather than in the middle of an incident.

Postgres is down

One database holds accounts, API keys, provider keys, billing, traces, the request log, and the fallback queue itself. While it is unreachable:

• The dashboard and REST API are unavailable. Proxy auth caches each key in process for 30 seconds, so a warm instance keeps serving briefly, then new lookups fail closed.
• A failed request-log insert has nowhere to queue, because the queue lives in the same database. This is the total-loss window for new log rows. Traffic itself is unaffected: the proxy still returns provider responses.

Recovery:

1. Restore from the managed backup or point-in-time recovery. See Restore.
2. Migrations are additive and supabase db push runs on every push to main, so the server tolerates a schema that is briefly behind. Verify the schema version after the restore and re-run supabase db push --linked if it is not current.
3. Confirm the current month has a partition: SELECT * FROM ensure_requests_partitions(3);. A restore from an older backup carries only the partitions that existed when it was taken.
4. Watch /health/deep until fallback.queue reaches 0.

Scheduled jobs stop firing

Vercel's cron scheduler is known to silently drop short-interval jobs (as low as a few percent fire rate for */5 schedules). If the replay, self-monitor, or pending-deletion crons stop, backlogs build up with no error.

Detection: query how often each job actually ran in the last day.

SELECT job_name, count(*) AS runs, max(ran_at) AS last_run
FROM cron_job_runs
WHERE ran_at > now() - interval '24 hours'
GROUP BY job_name
ORDER BY runs;

Compare the run counts to the schedule in apps/server/vercel.json. A job that is defined but missing from this list, or running far below its schedule, is being dropped.

Mitigation (defense in depth):

• GitHub Actions re-fires the critical routes on a schedule (.github/workflows/cron-server.yml). GitHub also throttles short intervals, so this is a partial backstop, not a full replacement.
• External heartbeat monitor is the reliable fix. Register a monitor (for example Better Stack) that calls the critical endpoints on a fixed interval with the Authorization: Bearer $CRON_SECRET header. Because it runs outside Vercel and GitHub, it is unaffected by their scheduler gaps and fires at close to 100%. Cover at least /cron/replay-fallback (3 min) and /cron/self-monitor (30 min).

Keep CRON_SECRET synchronized across the three schedulers (Vercel env, GitHub Actions secret, and the external monitor header) whenever it is rotated.

A background migration is stuck

Large backfills run as chunked background migrations with a Postgres advisory lock and a heartbeat, driven by /cron/run-background-migrations. If that cron stops firing (see above) the queue stalls with no error.

1. Check the queue:
   sqlcopy

   SELECT name, status, progress_current, progress_total, last_heartbeat_at
   FROM background_migrations
   WHERE status IN ('pending', 'running')
   ORDER BY created_at;

2. A row stuck in running with a stale last_heartbeat_at (older than a few minutes) means the worker died mid-chunk. The next cron tick reclaims the lock and resumes from where it left off, so the usual fix is simply to make the cron fire again.
3. Trigger one run by hand to resume:
   bashcopy

   curl -X GET https://api.spanlens.io/cron/run-background-migrations \
     -H "Authorization: Bearer $CRON_SECRET"

Webhook deliveries are dead-lettering

Outbound webhooks retry 5 times with exponential backoff. A delivery that exhausts its retries, or whose endpoint was deleted, is dead-lettered: marked with dlq_at and a dlq_reason instead of retrying forever. A dead-letter count that climbs means a customer endpoint has been down long enough to burn through every retry.

1. Watch webhooks.dlq_count in GET /health/deep. When it crosses the threshold an internal_alerts row (kind webhook_backlog) is raised at /admin/alerts.
2. Inspect what is dead-lettered and why:
   sqlcopy

   SELECT webhook_id, dlq_reason, count(*)
   FROM webhook_deliveries
   WHERE dlq_at IS NOT NULL
   GROUP BY webhook_id, dlq_reason
   ORDER BY count DESC;

3. exhausted means the endpoint returned errors or timed out for the full retry window (contact the customer). webhook_deleted and payload_missing are terminal and need no action.

Restore drills

Backups are only real if a restore has been tested. On a schedule (quarterly is a reasonable default), restore the latest backup into a throwaway project, pair it with the matching ENCRYPTION_KEY, and confirm the dashboard renders and a stored provider key still decrypts. Use the exact commands in Backup and restore. Record how long the restore took; that is your real recovery time, not an estimate.