SpeakTrue

Supabase to GCS Backup Future Direction

Status

Future direction only. This document records a possible backup design for later work and does not describe an implemented job.

No repository code, database migration, scheduler, deployment service, environment variable, bucket policy, or secret has been added for this plan.

Goal

Periodically export user-owned SpeakTrue data from Supabase to the existing Google Cloud Storage backup bucket so a storage or database incident in Supabase has an independent recovery path.

The intended shape is an append-only backup stream, not a live mirror. Each run should preserve a point-in-time view of database rows, Supabase Storage objects, and enough metadata to prove what was copied.

Data Scope

Initial backup scope:

• Supabase Postgres tables that hold user-owned state, including profiles, settings, Soundboard categories, Soundboard clips, ordering snapshots, voice-clone metadata, style presets, pronunciation data, and related user configuration tables.
• Supabase Storage objects in user media buckets, especially Soundboard objects under user-scoped paths.
• Run manifests that capture object paths, object sizes, checksums or provider etags where available, table row counts, failure counts, source environment, schema revision, application commit, and timestamps.

Out of scope for the first version:

• A real-time bidirectional sync between Supabase and GCS.
• Restoring directly into production without a staging restore drill.
• Backing up provider API secrets or local .env files.
• Client-side backup behavior in iOS, Android, or the web app.

Proposed Layout

Use a dedicated prefix inside the existing GCS bucket instead of mixing backup objects with active application media:

supabase-backups/<project-ref>/<environment>/<yyyy>/<mm>/<dd>/<run-id>/
  manifest.json
  database/
    <table>.ndjson.gz
  storage/
    <bucket>/<object-path>

The manifest should be the authoritative index for each run. It should be written last, after all successful table exports and object copies are complete, and it should mark partial runs explicitly.

Execution Model

Prefer an external worker for the actual backup copy work.

Good first host options:

• A homelab Docker container scheduled by cron or systemd timer.
• A small Cloud Run job scheduled by Cloud Scheduler if the workflow later moves fully into Google Cloud.

Supabase Edge Functions can be useful as an orchestrator or status endpoint, but the full copy job should not start as a long-running Edge Function. The backup worker needs predictable runtime, resumability, cloud SDK support, and operational logs.

Security Boundary

The worker should run with server-only credentials:

• Supabase service-role access or a narrowly scoped database/storage backup credential if one is introduced.
• A Google Cloud service account scoped to write under the backup prefix and read backup metadata needed for verification.

Secrets should live only in the deployment secret store for the worker. They should not be committed, exposed to clients, or added to browser/mobile runtime config.

Retention

Use GCS retention controls intentionally:

• Keep backups append-only by default.
• Record source deletions as tombstones instead of immediately deleting older backup copies.
• Use lifecycle rules for aging out older backups once restore confidence is proven.
• Consider object versioning or soft-delete retention on the backup prefix if the bucket policy supports it cleanly.

Restore Drill

Do not treat the backup job as complete until a staging restore drill exists.

Minimum restore proof:

1. Select one backup run by run-id.
2. Restore database exports into a disposable staging Supabase project or local Postgres database.
3. Restore a representative subset of Supabase Storage objects into staging storage.
4. Verify Soundboard listing, playback metadata, category order, and representative user settings against the manifest.
5. Record restore duration, missing objects, row-count mismatches, and manual steps.

Future Implementation Slices

1. Add a dry-run manifest command that lists tables and storage objects without copying data.
2. Add GCS write support for compressed database exports and storage-object copies.
3. Add resumable checkpointing through a backup_runs / backup_items table or through GCS-side manifests.
4. Add scheduler wiring for one environment.
5. Add alerting for failed runs, partial manifests, row-count drift, and stale latest backup age.
6. Add a staging restore drill and document the operator steps.
7. Revisit retention policy after the first successful restore drill.

Verification Gates Before Implementation

Before turning this into an active job, verify:

• The exact production and staging Supabase project refs.
• The exact GCS bucket and allowed backup prefix.
• Which tables contain user-owned data and which tables should be excluded.
• Whether Supabase Storage object checksums or etags are sufficient for copy verification.
• Whether the backup worker runs in homelab infrastructure or Google Cloud.
• Whether lifecycle and retention policies are acceptable for privacy, account deletion, and storage cost.