How I Backed Up a Database I Can't SSH Into

This site runs on Payload CMS, Postgres on Neon, and Vercel's serverless runtime. For the first few weeks that combination had exactly one backup story: none. The database lived on Neon, Neon presumably kept some history somewhere, and I hadn't looked closely enough to know what "somewhere" meant. That's not a backup strategy, it's a hope.

What follows is the sequence of work that turned "I hope Neon has my data" into three independent, verified layers of recovery, and the specific bugs, each one non-obvious, that showed up along the way.

Layer 1: the recovery path that was already there

Neon continuously retains write-ahead history for every project, which means point-in-time restore (PITR) is available without a single line of code. You can restore or branch the database to any moment inside the retention window. This is the exact, physical recovery path: it captures literally everything, including the user password hashes that the other two layers deliberately leave out.

The catch is that "already there" doesn't mean "already useful." Retention windows have to be set, and the default on this project was short. So the first real task wasn't building anything, it was walking into the Neon console and setting history retention to the plan's ceiling. On a free tier that's 24 hours; on the paid tier this project is on, up to 7 days.

That sounds trivial, and the change itself was. The part worth remembering is that this layer is invisible in the codebase. There's no file that says "Neon retention: 24h." It's a console setting on a specific Neon project, and my account has more than one Neon project in it: this site's production database, and an unrelated app's. Getting this wrong doesn't throw an error. It just quietly changes settings for the wrong project. I keep a note pinned specifically to avoid that mixup, because nothing in the UI stops you from making it.

Layer 2: a backup before you do something risky

Before Neon PITR was configured to the ceiling, and even after, I wanted something I could run right before a schema change: a deliberate pg_dump I hold in my own hand, not a rolling window I have to trust is long enough.

pnpm db:backup-prod runs a read-only pg_dump against production and writes a Postgres custom-format .dump file locally. It's wired into db:migrate-prod so a production migration always backs up first, by default, before touching the schema.

The gotcha here wasn't technical, it was tidiness: those dumps landed in the repo root. Nothing catastrophic, since they were gitignored, but full production dumps (including user records) sitting loose next to package.json is exactly the kind of thing that eventually gets committed by accident, or just makes the working directory unpleasant to look at. I moved them into a dedicated backups/ folder with the whole directory gitignored, and updated the script to mkdir -p it if missing. Small fix, but the kind of thing that's much easier to get right on day one than to clean up after the fact once dumps and other stray files start blending together.

Layer 3: the part that had to be built

Layers 1 and 2 cover "I did something and want to undo it" and "give me any point in the last N hours." Neither one is a portable, automatic, off-Neon copy, something that survives even losing the Neon account entirely. That's what the daily cron backup is for.

The first design decision was forced by the runtime, not chosen: Vercel's serverless Node functions don't ship Postgres client binaries. pg_dump simply isn't available. So the daily backup can't be a physical dump; it has to be a logical export, built entirely out of application-level calls. The route reads every Payload collection and global through the local API, at depth: 0 so relationships stay as plain IDs instead of nested duplicates, gzips the result, and writes it out.

That last step is where the more interesting decisions live. Vercel Blob only offers access: 'public': every object gets a live, internet-reachable URL. The random suffix Vercel adds makes that URL practically unguessable, but "practically unguessable" isn't the bar for a file containing every post, every page, and every user's email address. So the export gets encrypted, AES-256-GCM, before it's ever uploaded:

function encrypt(plaintext: Buffer, secret: string): Buffer {
  const key = createHash('sha256').update(secret).digest()
  const iv = randomBytes(12)
  const cipher = createCipheriv('aes-256-gcm', key, iv)
  const ciphertext = Buffer.concat([cipher.update(plaintext), cipher.final()])
  return Buffer.concat([iv, cipher.getAuthTag(), ciphertext])
}

A leaked URL now yields ciphertext, not content. Password hashes don't even make it that far: Payload already marks them hidden, which excludes them from payload.find() results before encryption is ever a question. But hidden fields aren't the only credential material an auth collection carries. payload.find() on a users collection also returns sessions (live session IDs, usable to impersonate a logged-in admin), plus lockUntil and loginAttempts. None of those are hidden, so none of them are excluded automatically. They had to be stripped by hand, in the export code, or the "no credential material" claim in the README would have been false the whole time.

The endpoint itself fails closed twice over. No CRON_SECRET, or the wrong one, and it returns 401: a missing secret makes the whole endpoint permanently unauthorized rather than accidentally open. No BACKUP_SECRET, and createBackup() throws before it ever writes anything, rather than silently uploading plaintext:

if (!backupSecret) {
  throw new Error('BACKUP_SECRET is not set — refusing to write an unencrypted backup')
}

Retention turned into a small lesson in not over-engineering. My first instinct was a size cap: keep backups under some number of gigabytes. Then I looked at the export size: tens of kilobytes, gzipped and encrypted. A 1 GB cap wouldn't trip for something on the order of fifty years of daily backups. A cap that never fires is a cap that never gets exercised, which means the pruning code underneath it is untested in practice. The first time it runs might be the first time it's wrong. So the real retention rule is a count: keep the newest 30. That number gets exercised every single day, and the byte cap stayed only as a backstop against runaway growth, not the primary control.

The bug that only shows up against the real API

Bugbot, the automated reviewer wired into this repo's PRs, caught something in that same PR that a type check or a local test run never would have: the put() call originally passed cacheControlMaxAge: 0, meant to say "don't cache this." Vercel's Blob API rejects values below its own minimum. Every single backup upload (the export, the gzip, the encryption, all of it) would have completed successfully right up until that final call, then thrown, discarding the finished backup because of an unrelated cache header. It's the kind of failure that never shows up in tsc --noEmit or a mocked test, because nothing about it is wrong except against the live service it's calling. The fix was simpler than the bug: drop the override entirely and let the SDK default apply. Every backup URL is unique and random-suffixed anyway, so caching was never a real concern in the first place.

Verifying it actually ran

Building the cron and confirming it worked in production turned out to be two different milestones, and I tracked them separately on purpose. "Merged" and "verified end-to-end" are not the same claim.

The one real surprise during verification was CRON_SECRET itself. Generate it the obvious way:

openssl rand -base64 32

Depending on how you copy that value into Vercel's environment variable UI, you can carry a trailing newline along with it. A newline is a control character, and it broke the build with an error that had nothing to do with backups or secrets on its face, just control character (0x0a), until I traced it back to how the secret had been generated. The fix is to strip it at generation time:

openssl rand -base64 32 | tr -d '\n'

Everything else on the checklist passed cleanly: cron registered in Vercel, a manual curl round-trip, a Blob object appearing in storage, pnpm backup:decrypt turning it back into readable JSON. The newline was the only thing that wasn't obvious from reading the code.

Backups also need backing up

Once the cron was running daily, the natural next question was: what protects the backups themselves? Sitting down for a deliberate security pass turned up two findings I hadn't thought hard enough about, both about the difference between confidentiality and availability.

First: the same Blob read-write token that writes backups can also delete them. Nothing about the objects is immutable or versioned, and there's no copy that token can't reach. One leaked or misused token can wipe the entire backup set in a single call, and the blast radius is widened by the fact that this same credential also lives on local dev machines. Encryption protects confidentiality; it does nothing for a backup set's integrity or availability. That one's still open, because the fix (an offsite copy the production token genuinely can't delete) is a design decision, not a quick patch.

Second, and more subtle: a BACKUP_SECRET leak is retroactive. Every backup is encrypted with a key derived from the same secret, and the objects are immutable, never overwritten. If the secret ever leaks, every historical backup becomes decryptable, and rotating the secret only protects backups made after the rotation. The incident runbook for "the secret leaked" has to include "delete every existing backup," not just "rotate the secret and move on," or the old files just sit there, quietly readable by anyone who ever got a URL.

Two smaller items from the same review shipped immediately. The auth check moved from a plain === string comparison to a constant-time compare, hashing both sides to fixed 32-byte digests first since timingSafeEqual requires equal-length buffers. That closes a low-severity timing side channel on the secret itself. And a comment now states explicitly that BACKUP_SECRET has to be high-entropy, generated with openssl rand -base64 32 rather than something memorable, because SHA-256 is a fast hash, not a stretching KDF like scrypt or argon2. Against a public ciphertext, a weak secret is directly brute-forceable in a way a proper KDF would resist.

The migration side of the same coin

Backups and schema migrations turned out to be more entangled than I expected, because a migration is the single most likely thing to make you need a backup. Production had been running on Payload's dev-mode schema push, convenient, but with no recorded history of what changed when. I moved production onto tracked migrations, then later added Neon branch-per-preview isolation plus an optional, fail-closed auto-migrate path for production deploys.

That auto-migrate path creates its own kind of backup: an instant Neon branch, taken as a copy-on-write snapshot immediately before payload migrate runs. Fail-closed here means what it says. If the branch creation fails for any reason, the script exits non-zero and the migration never runs:

if (!KEY) die('NEON_API_KEY is not set — refusing to migrate production without a backup')
if (!PROJECT) die('NEON_PROJECT_ID is not set — refusing to migrate production without a backup')

The pruning logic for old snapshot branches had its own near-miss, and it's my favorite bug in this whole project, because it's the exact failure the script exists to prevent, hiding inside the code that prevents it. The KEEP count came straight from an environment variable with no validation:

const stale = snapshots.slice(KEEP)

If NEON_PREMIGRATION_KEEP were unset, blank, or somehow evaluated to 0 or NaN, slice(KEEP) behaves exactly like slice(0): every snapshot, including the one just created seconds earlier, gets marked for deletion. The safety net would have deleted itself, immediately, right before the risky operation it exists to protect against. Bugbot flagged it before it ever ran against a real deploy. The fix is a strict parse: must be a positive integer, or fall back to a default of 5.

const keepParsed = Number(process.env.NEON_PREMIGRATION_KEEP)
const KEEP = Number.isInteger(keepParsed) && keepParsed >= 1 ? keepParsed : 5

Given how sharp that edge is, auto-migrate on production deploy stays off by default. At this site's deploy frequency, the deliberate db:migrate-prod path, which already backs up and asks for confirmation, is a better trade than convenience. That's a decision I'm revisiting, not one I've closed off.

What's still open

Two gaps I'm deliberately not treating as done.

No alerting on backup failure: the cron route returns a 500 and logs to console.error on failure, and nothing is watching for it. A silently failing daily backup means finding out there's no recent secondary copy at exactly the moment it's needed, the worst possible time to learn it.

The offsite/immutable copy from the security review: a second location the production write token genuinely cannot reach.

Neither is a hard problem. Both are exactly the kind of thing that's easy to defer once the happy path works, which is precisely why I keep a running list instead of trusting memory.

What changed

Three independent layers now cover this database: Neon's continuous PITR for exact, physical recovery inside a configured window; a manual pg_dump I run myself before anything risky; and a daily encrypted export that survives losing Neon entirely. None of them alone was hard to build. What took real iteration was everything adjacent to "it works once": a cache-header default that would have failed every single upload, a trailing newline that broke a build with an unrelated-looking error, a retention cap sized for a database ten times larger than the one that exists, and a validation gap that could have deleted a safety net the moment before it mattered most.

The database has never needed any of these. That's the point. The day it does, I'd rather have found these bugs by reading code than by needing a backup that wasn't there.