Offsite database backups: set up and restore¶

Use this procedure to turn on the periodic offsite backups of Supabase and MongoDB, check that they run, restore one after data loss, and run the restore drill.

Backups are written by the db-backup-core-production / db-backup-full-production Cloud Run Jobs (backend/apps/jobs/db_backup/) to gs://rose-backups:

Prefix	Contents	Cadence	Retention
`supabase/core/daily/`	`config` `knowledge` `geo` `lookup` `integrations` `scraping` `auth` `backoffice` `experiments` `supabase_migrations`, plus `public` minus event exhaust — 180 MB, 68 s	03:00 UTC	400 days
`supabase/core/recent/`	identical contents	09:00 / 15:00 / 21:00 UTC	90 days
`supabase/full/weekly/`	everything except `posthog_events_raw`, public `_backup`/`_shadow`/`_precutover` leftovers, and the data (not definitions) of analytics `_shadow` validation tables — 17.0 GB, 40–70 min (2026-10-13)	Sundays 04:00 UTC	90 days (NEARLINE after 1d)
`supabase/full/monthly/`	same as weekly	1st of month 04:00 UTC	400 days (ARCHIVE after 1d)
`mongo/weekly/`	MongoDB `rose_production` on inboundx-c1 (LightRAG kv, chunks, vectors) — 5.7 GB archive from 15.2 GB of data, 23 min (2026-10-13), see MongoDB	Sundays 04:00 UTC, after the Supabase dump	90 days (NEARLINE after 1d)
`mongo/monthly/`	same as weekly	1st of month 04:00 UTC	400 days (ARCHIVE after 1d)

Supabase objects are named <YYYYMMDDTHHMMSSZ>.dump (pg_dump custom format). The core tier also writes <stamp>.roles.sql (pg_dumpall --globals-only), because roles and memberships are cluster-wide and appear in no pg_dump.

Set up the periodic backups¶

Cloud Scheduler triggers both jobs. Each job picks its own prefix from the date and hour, so the schedule needs no maintenance once it is deployed. Every step below is idempotent. All of them write to production, so run them as the operator.

Prerequisites: gcloud authenticated on project inboundx, just, and jq. For the Mongo leg, you also need Atlas project-owner access. Run every just command from backend/.

Writer service account. Run just job setup-sa db-backup. It creates rose-backup-writer and grants it read on exactly two secrets. Re-run it after creating a secret it reported as missing.
Supabase password secret. Create SUPABASE_BACKUP_DB_PASSWORD. It holds the password of the read-only rose_backup role, created by migration 20260731072854_ix4239_rose_backup_role.sql.
Mongo URI secret.
- In Atlas, create a database user with role read on rose_production, restricted to cluster inboundx-c1. Do not reuse the app's user: it can write.
- Store its full mongodb+srv://… URI as secret MONGODB_BACKUP_URI. Append &compressors=zstd to roughly halve transfer. The database tools support it natively. mongosh does not ship the zstd module, so remove that parameter before you use the URI there.
- The user is rose-backup-reader, created 2026-10-12.
- Re-run step 1 so the service account can read the new secret.
Bucket and retention. Run just setup-db-backup-bucket, then just check-db-backup-bucket, which must print ✅. Do this before the deploy: the deploy runs the same check and fails on lifecycle drift.
Deploy. Merge to develop. The production release (release.yml → deploy-jobs.yml) deploys db-backup, which is production-only. To deploy outside a release, run just job deploy db-backup. That builds the image from your local checkout and pushes it as :latest, so production then runs that branch. Merge the branch to develop before the next release: the release rebuilds from main and silently reverts an unmerged change. Either way, the deploy creates the two Cloud Run Jobs and their schedulers:
- core: 0 3,9,15,21 * * *
- full: 0 4 * * *. It publishes only on Sundays and the 1st, and exits in seconds on other days.
Alerts. Run just job setup-alerts db-backup production <email>. It mails on a failed execution and on 12 hours without a core backup. See Alerting.

Expected result and verification:

gcloud scheduler jobs list --project=inboundx --location=europe-west9 | grep db-backup   # both ENABLED
gcloud storage ls -l gs://rose-backups/supabase/core/daily/    # a new .dump every day
gcloud storage ls -l gs://rose-backups/mongo/weekly/           # a new .archive.gz every Sunday

To exercise the full leg without waiting for a Sunday, run gcloud run jobs execute db-backup-full-production --region=europe-west9 --update-env-vars=BACKUP_DOW=7. It writes a real weekly object.

What this does and does not replace¶

This is a logical backup. It is not a substitute for Supabase PITR — it covers a different failure class (account compromise, billing lapse, project deletion, or a loss noticed after the 7-day PITR window).

	Supabase PITR / daily	this job
Granularity	any second	the instant the dump started
Window	7 days (Pro)	400 days core / 90 days full
Restores to	a Supabase project, same PG major	any Postgres
Survives project deletion	no	yes

Consequences to plan around:

RPO is 6h for core and 7d for full, not seconds — there is no WAL between dumps.
Dumps keep owners and object-level GRANTs; they are stripped at restore time instead (pg_restore --no-owner --no-acl), which is what the drill below does. Do that only against a scratch target: nothing else would put the grants back. roles.sql is --globals-only, so it carries roles and memberships but no privileges on tables or functions, and the restored supabase_migrations makes the target look fully migrated, so db push reapplies nothing. A real DR restore therefore drops --no-owner --no-acl, or comes up with anon / authenticated / service_role holding no privileges and the PostgREST API returning permission denied on everything.
Extensions must already be enabled in the restore target; planner statistics are not dumped (run ANALYZE after a restore).
The only large table excluded from the full tier is public.posthog_events_raw (35 GB). It is a staging copy of a PostHog batch export, PostHog upstream is the source of truth, and session_events — the table it feeds — is backed up. Everything else, including hubspot.crm_hubspot_webhook_events, is kept: that one is a webhook inbox holding unprocessed work, and HubSpot cannot resend historical webhooks.
Supabase Storage objects are not covered — neither here nor in Supabase's own backups, which keep only the metadata rows. Same for the JWT secret and Vault root keys.

Restore into a Supabase preview branch, never into production. ./bootstrap.py --branch creates one and wires its credentials into backend/.env.local / frontend/.env.local.

Restore¶

Supabase¶

The dump is a schema+data archive of the listed schemas. It restores into an empty database — not over an existing one. Restoring over a populated target fails in ways that look like success: pg_restore --clean issues DROP without CASCADE, so every table with a dependent survives and its data is appended to rather than replaced, and auth objects cannot be dropped at all by a non-superuser.

# 1. Pick a dump. Only `.dump` is trustworthy — `.part` is a failed run's leftover.
gcloud storage ls gs://rose-backups/supabase/core/daily/
gcloud storage cp gs://rose-backups/supabase/core/daily/<stamp>.dump .
gcloud storage cp gs://rose-backups/supabase/core/daily/<stamp>.roles.sql .

# 2. Create an EMPTY database on the target and pre-create the extensions.
#    A -n-filtered dump does not carry extensions, and `public.vector` etc. are
#    referenced by function signatures, so they must exist first.
psql "$ADMIN" -c 'CREATE DATABASE restored'
psql "$TARGET" -c 'create extension if not exists vector with schema public'
psql "$TARGET" -c 'create extension if not exists pg_trgm with schema public'
psql "$TARGET" -c 'create extension if not exists unaccent with schema public'
psql "$TARGET" -c 'create schema if not exists extensions'
psql "$TARGET" -c 'create extension if not exists pgcrypto with schema extensions'
psql "$TARGET" -c 'create extension if not exists "uuid-ossp" with schema extensions'

# 3. Roles first — RLS policies reference them, and they live in no pg_dump.
#    "already exists" errors are expected on a target that has Supabase roles.
psql "$TARGET" -f <stamp>.roles.sql

# 4. Skip the archive's own CREATE SCHEMA public: every database already has one.
pg_restore -l <stamp>.dump | grep -v 'SCHEMA - public ' > restore.toc

# 5. Restore. --exit-on-error is mandatory: pg_restore otherwise continues past
#    failures and exits 0.
#
#    --no-owner --no-acl are for a SCRATCH target, where the Supabase roles are
#    only partly present and reassigning ownership is noise. For a real DR
#    restore into a fresh Supabase project, DROP BOTH FLAGS — they are the
#    difference between a restored database whose API works and one where every
#    request is permission denied.
pg_restore -d "$TARGET" -L restore.toc --exit-on-error --no-owner --no-acl -j 4 <stamp>.dump

A clean run reports 0 errors and exit 0. Anything else is a failed restore.

Where to restore¶

A scratch Postgres 17 with pgvector (pgvector/pgvector:pg17) — fastest, and what the drill below uses. Proves the archive.
A fresh Supabase project — the real DR target.
A Supabase preview branch — only for the smallest checks. A branch volume is roughly 1 GB; a core restore plus its working space fills it, and a full branch goes read-only, which blocks DROP DATABASE and even the Management API's branch reset. Recovering means deleting the branch.

MongoDB¶

The full job also writes mongo/<weekly|monthly>/<stamp>.archive.gz. This is mongodump --db=rose_production --archive --gzip, read from a secondary one collection at a time. The job authenticates with MONGODB_BACKUP_URI: a read-only Atlas user with read@rose_production on inboundx-c1, reached through the Cloud NAT IP, since the Atlas IP access list allows nothing else.

The dump sits alongside Atlas Cloud Backup on inboundx-c1, which keeps 6-hourly, daily, weekly, monthly and yearly snapshots with 7 days of PITR. It does not replace them. The offsite copy is there for what Atlas snapshots cannot survive: Atlas account or project loss, or a loss noticed after the snapshot window. RPO is 7 days.

Restore steps:

gcloud storage ls gs://rose-backups/mongo/weekly/
gcloud storage cp gs://rose-backups/mongo/weekly/<stamp>.archive.gz .

# Into a scratch database, never over rose_production. --nsFrom/--nsTo rename on
# the way in; drop them only for a real DR restore into an empty cluster.
mongorestore --uri="$TARGET" --archive=<stamp>.archive.gz --gzip --drop \
  --nsFrom='rose_production.*' --nsTo='<scratch_db>.*'

A clean run ends with N document(s) restored successfully. 0 document(s) failed to restore.

Consistency: the archive is not a point-in-time snapshot¶

Decided (2026-10-13): accepted, and handled at restore time.

mongodump reads one collection after another, so each collection is captured at a different moment. On the first production run, the Mongo leg took 23 minutes:

Time (UTC)	Collections captured
15:25	`full_relations`, `full_docs`
15:33	`entities`, `doc_status`, `relation_chunks`, `entity_chunks`
15:45	`relationships`, `text_chunks`
15:47	`chunks`
15:48	`llm_response_cache`, `full_entities`

Writes made during that window can leave the restored collections disagreeing:

A document added mid-dump can have chunks but no doc_status entry. The next ingestion picks it up, so this is harmless.
A document re-processed or deleted mid-dump can be marked processed while its chunks or vectors are missing or stale. This is the case that matters: retrieval silently lacks that content, and nothing re-ingests a document that looks done.
Entities and relationships are captured 12 minutes apart, so a few relationships can point at entities missing from the archive.

The runs are scheduled for Sundays and the 1st at 04:00 UTC, when no ingestion job is scheduled. Ingestion is event-driven, though: a client retrain or website re-scan can start at any time. Exposure is therefore low, but not zero. The first run, a manual one on a Tuesday afternoon, overlapped 11 document updates across 2 tenants (found from doc_status.updated_at).

Why this is accepted:

Atlas Cloud Backup gives consistent snapshots (every 6h, 7 days of point-in-time restore, monthly copies kept for a year). It stays the first choice for any restore.
This archive exists only for the case where those snapshots are gone, such as losing the Atlas account or project. Its worst case is re-ingesting the few documents touched during the window.

Rejected alternatives:

Dump the whole cluster with --oplog. It is consistent, but it cannot be combined with --db. The cluster also holds rose_staging (17 GB) and rose_dev, so the archive and its transfer cost would roughly double.
Pause ingestion during the dump. It is consistent at the same size, but the knowledge pipeline has no write-pause mechanism to build it on.

After restoring this archive, repair the window before you point the app at it. No single source lists everything that changed during the window, so take the union of steps 2 and 3. On the first run, Supabase listed reki.eu only, while doc_status also showed userose_ai updates: legacy writers still bypass the operation log.

Find the window. It starts at the archive's stamp, which is the job's start time, and ends at the object's creation time: gcloud storage ls -l gs://rose-backups/mongo/weekly/<stamp>.archive.gz.

List the workspaces with a control-plane ingestion that overlapped the window, deletions included. If Supabase itself was restored, use a core dump taken after the window:

select distinct site_domain
from knowledge.ingestion_operations
where environment = 'production'
  and coalesce(started_at, created_at) <= '<window end>'
  and coalesce(finished_at, now()) >= '<window start>';

In the restored database, list the tenants with anything written after the window start. These are full collection scans, so never run them against production. doc_status alone misses re-processing that happened after it was captured, so check the chunk collections too:

const start = "<YYYY-MM-DDTHH:MM:SS>";            // window start, UTC
const t = Math.floor(Date.parse(start + "Z") / 1000);
[...new Set([
  ...db.doc_status.distinct("tenantId", { updated_at: { $gte: start } }),
  ...["chunks", "entities", "relationships"].flatMap(c =>
    db.getCollection(c).distinct("tenantId", { created_at: { $gte: t } })),
])]

Retrain every workspace from steps 2 and 3. Use the website and managed-document retrain paths in the knowledge ingestion control plane. If neither list can be trusted, retrain all workspaces: it is slower and costs LLM spend, but it is always correct.
Run rose-mongo --env <env> consistency-check --all --database <restored_db>. It reports orphaned relationships and chunk-entity links, and documents marked processed with no trace left in full_docs, chunks or entities. It cannot see a partly-present document; step 4 covers those.

Vector search indexes are not in the dump: rebuild them¶

vector_knn_index_shared_v3 on chunks, entities and relationships is Atlas Search config. mongodump does not carry it; a dump of a collection that has the index restores without it (verified 2026-10-12). The application will not recreate it either. On a collection that already holds documents, ixmongo treats an empty search-index listing as untrustworthy, assumes the index exists, and skips creation (vector_storage.py, _list_search_index_names). A restored database therefore serves $vectorSearch with no results until you run:

# from backend/. --env picks the cluster (MONGO_URI); --database the restored db.
rose-mongo --env <env> create-vector-indexes --database <restored_db>          # plan
rose-mongo --env <env> create-vector-indexes --database <restored_db> --apply  # build

It builds the index from the same definition the app uses (shared_vector_index_definition), sized from the restored vectors. It skips collections that already have the index, and empty ones, which the app bootstraps itself. Builds are asynchronous: re-run without --apply until it exits 0 with every collection READY, queryable. It exits 1 while any index is building, failed, or could not be inspected. Each build is a full mongot pass. On inboundx-c1, run it off-peak (IX-4490).

The target must be Atlas, or mongodb/mongodb-atlas-local: Community mongod has no $vectorSearch. A Community target restores the data, but it cannot prove the indexes.

Mongo drill¶

Check the restore regularly, alongside the quarterly Supabase drill:

Restore the latest mongo/weekly/ archive into a scratch database on the test cluster (inboundx-c2) or atlas-local.
Compare countDocuments() per collection against the source.
Run create-vector-indexes --apply on it. Then run a $vectorSearch that uses a restored chunk's own vector and tenantId as the query; it must return that chunk first with score 1.0.
Drop the scratch database's collections. The test cluster's app user has no dropDatabase, and an empty database disappears on its own. Then record the stamp and counts on IX-4240.

2026-10-12 (pre-deploy): - Ran the job's own backup.sh in the amd64 image against rose_dev on c1, with GCS and Postgres stubbed. It produced a 50 MB archive from 114 MB of data in 30 s. - A wrong database name made the canary refuse to publish (exit 1). - Restored into ix4240_drill on c2: 10,971 docs, 0 failed, all 12 collection counts equal to the source. - No search index came back, including from a dump of a collection that had one. create-vector-indexes --apply made it queryable, the self-match query returned the chunk first with score 1.0, and a re-run reported nothing to do. - A production-archive drill is due after the first weekly run.

2026-10-13, first production run (20261013T141550Z): 12 collections, 1.48M documents, a 5.68 GB archive dumped in 23 minutes from a secondary. The whole full execution, Supabase included, took 1 h 33 min, well inside its 4 h timeout.

Restoring a Supabase `full` dump into a scratch target¶

Learned on the IX-4895 recovery rehearsal (September 15, 2026). The full tier carries more than the core procedure above expects:

cron entries. The archive holds cron.job / cron.job_run_details data and ACLs, but pg_cron can only be created in the database named in cron.database_name (postgres on the Supabase image), so a scratch database has no cron schema. Filter every cron line out of the TOC or --exit-on-error aborts on the first COPY cron.job.
Custom roles. Policies and ACLs reference roles that exist in no image: cli_login_postgres, n8n_outreach, n8n_readonly, n8n_service, posthog_export, readonly_user, rose_backup, rose_mcp, supabase_functions_admin, supabase_realtime_admin. Create them NOLOGIN before restoring (or load the core tier's roles.sql). With the full role set present, keeping ACLs (--no-owner without --no-acl) restores RLS, policies and the service_role-only RPC grants exactly as archived.
Size. session_events, visitor_sessions and visitor_page_views are ~90 GB on disk and do not fit a default Docker Desktop VM. A scoped restore that drops their TABLE DATA entries (plus hubspot data and MATERIALIZED VIEW DATA) restores in under three minutes and still proves the archive for the analytics landing tables.
Image. supabase/postgres:<production version> ships every extension in the archive (pg_net, hypopg, index_advisor, pgstattuple, supabase_vault) and the Supabase API roles, so a fresh CREATE DATABASE there is the quickest faithful target. Pre-create the extensions in the same schemas as production (pg_trgm, unaccent, vector, pgstattuple in public; the rest in extensions).
Running a Cloud Run job against the restore. The Supabase Python client requests /rest/v1/..., which bare PostgREST does not serve. Put nginx in front (location /rest/v1/ { proxy_pass http://postgrest:3000/; }) with large_client_header_buffers 16 512k and proxy_buffer_size 512k: the batch processor sends 200-id IN lists in the URL and PostgREST echoes them in Content-Location. Point the job at it with SUPABASE_URL, service-role/anon JWTs signed with PGRST_JWT_SECRET, REDIS_URL at a local Redis (the lock refuses to run without one), and blank SLACK_WEBHOOK_URL; Grafana export is already gated to Cloud Run.

Quarterly drill¶

The backup is unproven until this passes at least once, and it is repeated quarterly:

Restore the latest supabase/core/daily/ dump into a scratch Postgres 17 (pgvector image) following the procedure above.
Require pg_restore exit 0 with zero errors, then compare row counts against production for config.client_configs, config.knowledge_faqs, config.curated_content_items, public.domains, public.clients, auth.users.
Confirm the excluded exhaust tables exist and are empty (session_events, visitor_sessions, visitor_page_views) — proof that --exclude-table-data is still doing its job rather than -T creeping back in.
Record the date, dump stamp, and counts in the IX-4239 Linear thread.

Alerting¶

Two policies, both mailing the db-backup-email-production channel (scripts/db_backup/setup-alerts.sh):

policy	fires when
`db-backup-failure-production`	a core or full execution exits non-zero
`db-backup-core-stale-production`	no successful core backup in 12h (two missed runs)

There is deliberately no success/heartbeat mail. Cloud Monitoring renders every policy with the same "Alert firing" template, so a daily green-light mail is indistinguishable at a glance from a real failure and only teaches you to skim past alerts from this sender. Positive confirmation belongs in a channel that can express "this is fine" — an object listing does it for free:

gcloud storage ls -l gs://rose-backups/supabase/core/daily/

Two gaps are known and accepted for now:

Only the core tier has a staleness alert. If the full scheduler is disabled or deleted, no execution fails, core stays green, and weekly/monthly dumps stop silently. The obvious fix does not work: conditionAbsent caps its duration at 24h and the full job runs exactly every 24h, so any window short enough to be allowed fires on a healthy schedule. Closing it needs a PromQL condition (absent_over_time(...[36h])), not another conditionAbsent.
Both alerts depend on Cloud Monitoring itself; if that path breaks, silence looks like success. Closing that one properly needs a watchdog outside GCP (the job pings on success, the external service mails when a ping does not arrive) — a separate change, not a second alert policy here.

Operational notes¶

pg_dump in the job image is PostgreSQL 17 (Debian trixie) — restoring locally needs a client at least as new as the server (17.6). If Supabase upgrades the project to PG18 the job fails loudly and the image must be rebuilt.
The job connects as rose_backup, a read-only role whose statement_timeout is 0. The platform default is 2 minutes, which cancels any large COPY, and Supavisor drops PGOPTIONS — so the timeout override has to live on the role.
The writer service account holds a custom role (roseBackupWriter) with create + get + list, and no delete. gcloud storage cp resolves its destination before writing, so create alone is not enough — it needs get and list even for a new object name. Read access is not a downgrade: the same account can read SUPABASE_BACKUP_DB_PASSWORD, which grants BYPASSRLS read of the whole database, so withholding read on dumps of that data protects nothing. Inability to delete is the property that matters, and it is enforced twice — no storage.objects.delete, plus the 90-day retention policy.
Retention is GCS Object Lifecycle (backend/apps/jobs/db_backup/lifecycle.json). just check-db-backup-bucket fails the deploy if the live config drifts.
Deleting a NEARLINE object before 30 days still bills 30 days; ARCHIVE bills
Don't shorten a retention without also moving its storage class.
The bucket lives in the same GCP project as the workloads. Accepted risks: it does not survive deletion of that project, and principals holding project-level storage roles (github-actions-deploy has storage.objectAdmin, document-loader-job has storage.objectViewer) can reach it, since GCP IAM is additive and a bucket-level grant cannot subtract a project-level one. A 90-day bucket retention policy blocks deletion by those principals; reads are not blocked. Moving the bucket to its own project is the real fix.
The dumps contain personal data — auth.users includes email addresses and password hashes. The bucket is EU-located for residency, has public access prevention on, and uniform bucket-level access. roles.sql is written with --no-role-passwords, so no role password hashes are stored.