Supabase Free has no backups, so ours is two pg_dumps a night and the smaller one is kept twelve times longer

Munchable runs on Supabase's Free plan. The Free plan has no managed backups, which is fine right up to the moment it is not, and the honest version of "we back up nightly" has to be a file somebody can point at.

Ours is one GitHub Actions workflow: pg_dump twice, upload to Cloudflare R2, done. Cost is effectively zero, because R2 charges no egress and gives 10 GB of storage free, and a nightly dump is a few minutes of Actions time. The interesting parts are not the dump command. They are the split, the retention, and the three connection strings of which only one can work.

Two archives, because the data has two halves

The database divides cleanly by replacement cost.

Archive	Contents	Retention
full	Everything, product catalogue rows included	30 days
core	Everything except the catalogue's row data	365 days

The product catalogue is roughly 400 MB of seeded and curated rows against a 0.5 GB quota, and it dominates any dump of this database. It is also the half we could rebuild, slowly and annoyingly, from our own pipeline.

What is left when you take it out is the half that cannot be rebuilt from anything: auth accounts, entitlements, contributor rewards, product revisions and feedback, consent records, support threads, and the curated taxonomy and rule entries that the whole app is an opinion about. That half is small, so it is cheap to keep for a year and quick to restore in a hurry.

pg_dump "$SUPABASE_DB_URL" --format=custom --compress=6 --no-sync \
  --exclude-table-data="$BULK_TABLE" --file="$CORE_FILE"

--exclude-table-data rather than --exclude-table is the whole trick: the table definition, its indexes and its constraints are still in the archive, so restoring core gives you a structurally complete database with one empty table, not a database missing a table. Both archives are otherwise full-database dumps covering every schema, including the Supabase-managed auth schema, so accounts come back with their data rather than as orphaned rows.

The workflow is not allowed to delete anything

Retention is set as R2 object lifecycle rules on two prefixes, and the job itself has no delete path at all. This is deliberate. Expiring old backups from CI means writing date arithmetic that runs unattended every night with credentials that can delete backups, and that is one of the great ways to discover you have no backups. Lifecycle rules are declarative, live in a different system, and cannot have an off-by-one.

The credentials follow the same logic: a separate bucket, and an R2 token scoped to only that bucket, so nothing in the backup path can reach the rest of the account and nothing in the app can reach backups.

Three connection strings, two of which cannot work

Supabase hands you several ways to connect, and for pg_dump exactly one of them is correct.

• Transaction pooler, port 6543. This is the app's connection string. It runs in transaction mode, and pg_dump cannot run there.
• Direct connection, db.<ref>.supabase.co:5432. IPv6-only on the Free plan. GitHub's hosted runners are IPv4-only, so this one fails with Network is unreachable and a parenthesised IPv6 address, which tells you nothing about why.
• Session pooler, port 5432. The one that works.

Getting that wrong is the single most likely failure of this job, and the default error message for the IPv6 case is actively misleading. So the job checks the secret before pg_dump ever sees it, reading only the host and port out of the URL and never the password:

DB_HOSTPORT="$(printf '%s' "$SUPABASE_DB_URL" | sed -E 's#^[a-zA-Z+]+://##; s#^.*@##; s#[/?].*$##')"

case "$DB_HOST" in
  db.*.supabase.co)
    echo "::error::SUPABASE_DB_URL points at the direct connection host. That host is
IPv6-only on Supabase's Free plan and GitHub's runners are IPv4-only. Use the Session
pooler URL instead."
    exit 1
    ;;
esac

if [ "$DB_PORT" = '6543' ]; then
  echo "::error::SUPABASE_DB_URL uses the transaction pooler (port 6543). pg_dump cannot
run against it; that URL is the app's DATABASE_URL. Use the Session pooler on 5432."
  exit 1
fi

A wrong secret now fails in the first seconds of the job with a message naming the problem and the fix. The useful property is not that it saves time, it is that the failure is self-explaining a year from now, when whoever reads the email has forgotten this file exists.

The client version is a trap too

pg_dump can dump an older server than itself but not a newer one. The runner image ships a PostgreSQL client whose PATH entry wins by default, so the job installs the client it wants and then pins PATH to that specific directory for every later step, asserting the binary is really there instead of hoping:

PG_BIN="/usr/lib/postgresql/${PG_MAJOR}/bin"
test -x "${PG_BIN}/pg_dump" || { echo "::error::client did not install"; exit 1; }
echo "${PG_BIN}" >> "$GITHUB_PATH"

A dump you have not read is not a backup

Two cheap checks after dumping, both of which have caught real problems in similar setups:

for f in "$FULL_FILE" "$CORE_FILE"; do
  test -s "$f"
  pg_restore --list "$f" > /dev/null
done

pg_restore --list parses the entire table of contents, so it fails on an archive that died halfway through, which a size check happily accepts.

Then the check that is specific to this design:

if [ "$(stat -c%s "$CORE_FILE")" -ge "$(stat -c%s "$FULL_FILE")" ]; then
  echo "::warning::core dump is not smaller than the full dump; check that ${BULK_TABLE} still exists"
fi

If somebody renames or moves the catalogue table, --exclude-table-data silently matches nothing, and the year-long archive quietly becomes a copy of the 30-day one, at twelve times the storage and with none of the properties it was created for. Nothing errors. The only symptom is that two numbers stop being different, so the job watches those two numbers.

The upload order is from the same family of thinking: core goes up first, so if the big upload is the one that fails, the irreplaceable half of the database is already safe.

Small scheduling details that are not decoration

on:
  schedule:
    - cron: '42 3 * * *'

concurrency:
  group: db-backup
  cancel-in-progress: false

03:42 rather than 03:00, because everybody's cron jobs fire on the hour and GitHub's runners feel it. cancel-in-progress: false, because cancelling a dump halfway through wastes the run and leaves nothing behind; a queued second run is strictly better.

Restores are selective, which is the point of the format

Custom-format archives mean the restore does not have to be all or nothing, and the runbook is written around the failures we can actually imagine:

• A bad curation run wrote wrong rows: pg_restore --schema=catalog from a full dump, leaving accounts untouched.
• Something went wrong with accounts and the catalogue is fine: restore a core dump with --exclude-schema=catalog.
• You restored core and now want the products back: pg_restore --data-only --table=products from the matching full dump on top.

The restore commands live in the repo next to the workflow, with the pooler caveat repeated, because a runbook you have to reconstruct under pressure is a runbook you do not have.

Why this shows up in the privacy policy

Retention is not only an ops decision. Our privacy policy says that backups containing deleted records roll off on their normal cycle rather than being edited in place, so a short window can exist between an erasure and the last backup expiring. That sentence is only honest if the cycle is a real, bounded, documented thing: 30 days for one prefix, 365 for the other, enforced by the bucket rather than by somebody remembering.

The same page says label photographs are read once and discarded, with no image store and no backup of them. That one is easy to write and only true because there is nothing in this job that could pick them up.

The upgrade path, and why the job stays anyway

Supabase Pro adds managed daily backups with 7-day retention, and point-in-time recovery is an add-on above that. When this project outgrows the Free plan it will get one of those, and the R2 job will stay regardless: it is an independent, downloadable copy that lives outside the provider's account, and "our backups are in the same account as the thing we are backing up" is not a sentence anybody wants to read after an incident.

Munchable is at munchable.app, and the data this job protects is the part the app is actually made of: 426 URLs in our sitemap, generated from the same catalogue and rule sets, including 373 ingredient answers and 38 recipes.