PostgreSQL and Neon, where the bytes live

Part 1 covered the collection config I write, part 2 covered the Postgres schema it becomes, and part 3 covered how a change gets there safely. All three treated Neon as a fact of life: prod is there, previews get a branch, done. This last post is about Neon itself: what a branch actually is under the hood, the different ways this app connects to it, and what happened when I compared the real database against the migration history.

A branch is not a copy

The single fact that makes everything else in this post make sense: Neon separates storage from compute. A branch isn't a fresh, full duplicate of the database's data files. It's copy-on-write against the same underlying storage, which is what makes creating one an operation that takes moments instead of minutes, cheap enough to do it constantly rather than treat it as a special occasion.

That's the whole reason two very different things in this series turned out to be the same primitive. Every pull request getting its own preview database (part 3's "migrate the preview, not prod" guard) and production taking an automatic safety snapshot before every migration are both just branch creation, called at different moments for different reasons. Neither would be practical if a branch meant copying the actual bytes.

Branch creation isn't something I click a button for, either. Neon exposes it as a REST API, keyed to a project id, and the scripts in this repo talk to it directly: list every branch, find the one Neon marks as the account's default, and issue a plain POST to create a new one off it. The pre-migration snapshot that protects a production deploy is that exact call, made automatically, seconds before payload migrate runs. The API is also how stale preview branches get cleaned up after a pull request closes: the same surface, a DELETE instead of a POST.

The same database, reached three different ways

Branching explains where the data lives. Getting to it is its own small story, because this app doesn't talk to Neon just one way.

The everyday path is a normal, pooled Postgres connection, the same protocol any Postgres client speaks, running through Neon's connection pooler so a burst of serverless function instances doesn't each try to hold their own long-lived connection open. That's the connection string the app already reads for every request.

The backup path is deliberately different. The scheduled job that takes a full physical dump of production connects through a role that can only read, over a direct connection that skips the pooler entirely, not the same one the app uses for regular traffic. Two separate credentials, two separate connection shapes, for two very different jobs: one optimized for a lot of small, concurrent, ordinary queries, the other for one long, uninterrupted read of everything.

And then there's a third way, one I'd forgotten this repo even used until I went looking for this post: an integrity check that runs in CI reaches Neon over plain HTTP instead of Postgres's own wire protocol at all, using Neon's own driver for exactly this case, a single query from a short-lived script that has no business holding a connection pool open for its one job. Three connections to the same underlying data, each shaped for what's actually running on the other end.

Backups, and why one layer needs that direct connection

Point-in-time restore is Neon's own feature, not something layered on top: the platform continuously retains change history and can restore, or branch off, to any moment inside a retention window with no code and no scheduled job involved. That window is a per-project setting, currently 24 hours, with headroom up to 7 days if that's ever needed. It's also the most complete recovery path there is, since it's the actual storage, down to password hashes.

PITR lives entirely inside Neon, though, so there are two more layers underneath it that don't. One is being phased out: a daily logical export, the app reading its own data back out through its API and writing it to encrypted JSON, a real workaround for the fact that the platform running it doesn't ship Postgres's own dump tooling at all. The layer replacing it is where that direct, non-pooled connection from the section above earns its keep: a real physical pg_dump, on a machine that actually has the binary, through the read-only role, over the direct connection a dump needs and the everyday pooled one doesn't reliably give it. Encrypted the same way, stored somewhere that is neither Neon nor this app's own hosting platform, reached by a credential that has no way to also delete what's already backed up there.

The one thing that isn't Neon at all

Nearly everything in this series lives on Neon. Media is the one exception worth naming: the metadata (filename, alt text, dimensions) is a Postgres row like anything else, but the actual image bytes live in a completely separate object store, one that local development and production genuinely share rather than each having their own. It's the single crack in an otherwise clean "every environment gets its own copy" story, and it needed its own opt-in guard so local testing can't silently overwrite a production asset.

Reading the real database, and what it found

Part 2's whole premise was that there's no schema file to read, only the actual database. The same move works here: list every table Postgres actually has, and compare it against what the migration history says should exist.

On my local Docker Postgres, not a Neon branch, that comparison turned up something. The count was 28 tables against 24 in the most recent migration's snapshot, the same .json file part 3 covered as the diff base for the next migration. The four extras are checks, checks_rels, notification_channels, and pings: leftovers from a status-monitoring app I prototyped and shelved, built back when this site still ran Payload's push mode, which writes schema changes straight to a database with no file and no record. Part 3 explained why that's dangerous for production; this is what it actually looks like once you go check.

They're harmless. Nothing reads them, nothing writes to them, and a fresh local database wouldn't have them at all. But they're the cleanest proof I have for the thing this whole series kept circling back to: the migration chain is only the truth about the schema if every change actually went through it. The comparison that found them took one command and about thirty seconds. Worth running more than once a year, and just as easy to run against a Neon branch as against a local one.

What four posts of reading actually bought me

Collections turned out to encode more than fields: an access policy, an editorial workflow, a cache strategy, and a generated type system, all from one TypeScript object. The ORM turned out to follow about five predictable rules, applied uniformly, with the generated SQL occasionally making a decision I hadn't consciously made myself. Migrations turned out to be code with their own failure modes, not passive records. And Neon turned out to be less a hosted database and more a small set of primitives, branch, pool, restore, all built on the same separation of storage from compute, reused for a database seed, a safety net, and a whole preview environment.

None of this was required to run a Payload site day to day. All of it was required the first time something broke below the admin UI, and the useful realization is that I didn't need to wait for that day to go looking. The schema, and the platform underneath it, were both readable the whole time.