# ADR 051: Neon Database Snapshots and Encrypted R2 Backups

- HTML version: https://robbiepalmer.me/projects/recipe-site/adrs/051-neon-database-snapshots-and-backups
- Project: Recipe Site (https://robbiepalmer.me/projects/recipe-site.md)
- Status: Accepted
- Date: 2026-07-13

# Summary

Protect the recipe site's Neon Postgres database with three complementary recovery layers:

1. Neon's six-hour Free-plan restore history for recent mistakes.
2. Neon's one included manual snapshot as a deliberately refreshed known-good checkpoint.
3. Weekly PostgreSQL custom-format archives, encrypted client-side with `age` and stored in a
   private Cloudflare R2 bucket independently of Neon.

GitHub Actions creates the external archive on a schedule. It receives the public `age` recipient
needed to encrypt, but never receives the private identity needed to decrypt. The private identity
is held as break-glass recovery material outside CI, with at least two independently recoverable
copies.

# Context

[ADR 033](/projects/recipe-site/adrs/033-backend-platform-for-authenticated-features) chooses Neon
Postgres for accounts, households, recipes, and authenticated application state. The Terraform
project already retains six hours of Neon history and prevents Terraform-driven project deletion,
but those controls do not cover every recovery scenario.

The database needs protection against distinct failure modes:

| Failure                                                       | Useful recovery mechanism                            |
| ------------------------------------------------------------- | ---------------------------------------------------- |
| A recent accidental update, delete, or migration              | Neon point-in-time restore                           |
| A risky migration whose failure is discovered after six hours | Manual Neon snapshot                                 |
| Neon project/account deletion or provider-level loss          | External R2 archive                                  |
| Cloudflare account or R2 credential compromise                | Client-side encryption and bucket retention controls |
| A future move away from Neon                                  | Portable PostgreSQL archive                          |

Neon's Free plan provides up to six hours of restore history and one manual snapshot. Automated
snapshot schedules and longer point-in-time restore windows require a paid plan. The project can
defer that spend while its data volume and recovery requirements are small, but a single
provider-resident snapshot is not an independent backup.

External exports also consume Neon network transfer. The Free plan includes 5 GB/month, and Neon
counts the uncompressed data sent to `pg_dump`; compression occurs on the client and does not reduce
that transfer. At the project's 512 MB database ceiling, daily full exports would consume roughly
15 GB/month, while weekly exports consume roughly 2 to 2.5 GB/month.

# Decision

## Recovery layers

Keep the existing six-hour Neon restore window.

Use the single manual Neon snapshot as a known-good checkpoint, especially immediately before a
risky schema or data migration. Do not replace the prior checkpoint until the change has been
validated. The snapshot stays inside Neon, so it improves recovery time but does not replace the
external archive.

Create an external archive every Sunday at 03:17 UTC. Store weekly archives for 60 days. Copy the
first successful backup of each month to a monthly prefix without exporting Neon a second time, and
retain monthly archives for 400 days. Inspect the monthly archive/checksum pair before promotion so
a manual retry can fill a missed month without creating duplicates. If a run copied only the
archive, the next run repairs its checksum from the corresponding weekly backup. Workflow
concurrency serializes this idempotent promotion.

Revisit the weekly cadence when the database holds irreplaceable user data. At that point, either
run the external archive daily or upgrade to a paid Neon plan with a seven-day restore window and
scheduled snapshots. Applying for the Neon Open Source Program may offset the paid service, but the
recovery design must not assume an application will be accepted or credits will be renewed.

## PostgreSQL archive format

Run PostgreSQL 17.10's `pg_dump` from the official PostgreSQL container image, pinned by digest,
against a direct unpooled Neon connection. Use PostgreSQL custom format with gzip compression.

Custom format is a PostgreSQL archive, not plain SQL or a general-purpose ZIP file. It contains a
table of contents that lets `pg_restore` inspect and selectively restore objects, order dependent
objects correctly, restore in parallel, and apply destination-specific ownership and privilege
choices. It is compressed but not encrypted.

The backup login is separate from the application login and receives only the database access
required to produce a complete dump. Its direct connection string must use either
`sslmode=verify-full`, or `sslmode=require` with `channel_binding=require`, and `pg_dump` runs with
`--no-password` so missing URI credentials fail instead of prompting. Initially the login is granted
`CONNECT` and `pg_read_all_data`.

Because `pg_read_all_data` does not bypass row-level security, introducing RLS requires a new restore
test and possibly a different backup-role design. Schema migrations and role definitions remain in
source control because `pg_dump` archives one database and not cluster-global roles.

## Client-side encryption with `age`

Use [`age`](https://github.com/FiloSottile/age), a small file-encryption tool designed for explicit
recipient keys and Unix pipelines. An `age1...` recipient is public and can only encrypt. Its
corresponding `AGE-SECRET-KEY-...` identity is private and can decrypt.

The backup path is:

```text
Neon --TLS--> GitHub runner --pg_dump--> age encryption --HTTPS--> R2 ciphertext
```

The GitHub runner is the client and therefore sees plaintext in process memory. The pipeline never
writes a plaintext dump file to the runner filesystem. It writes only an authenticated
`.dump.age` ciphertext file, calculates its SHA-256 checksum, and uploads both objects over HTTPS.
R2 receives ciphertext and then applies its own storage encryption at rest.

Authenticated `age` decryption detects a modified or corrupted ciphertext. The separate SHA-256
sidecar detects transfer or storage mismatch before decryption and makes archive verification
explicit.

## Private-key custody

The scheduled backup does not need the private identity, so do not place it in the
`prd_database_backup` Doppler config or any Doppler config synchronized to GitHub.

Putting the private identity beside the public recipient and runtime credentials would collapse the
security boundary: anyone who compromised GitHub Actions or obtained the synced Doppler config could
both download and decrypt every retained backup.

Keep at least two recovery copies:

1. A primary copy in a human-controlled password manager or equivalent encrypted vault.
2. An independent recovery copy, such as an offline encrypted volume or a separate human-only
   break-glass secrets project.

Doppler may hold the second copy only if it is in a separate project/config that has no CI service
token, no GitHub sync, and access restricted to human administrators with MFA. Doppler is then an
escrow location, not a runtime source. A separate copy is still required so a Doppler outage,
account loss, or accidental deletion cannot make the backups permanently unrecoverable.

The public `AGE_RECIPIENT` is non-secret and belongs in `prd_database_backup`, together with:

* a direct read-only Neon backup URL
* a bucket-scoped R2 Object Read & Write access key
* the Cloudflare account ID and backup bucket name

Read access is required to detect an existing monthly archive and copy the first successful backup
of the month into the monthly prefix. The R2 token is scoped to the backup bucket and cannot
administer unrelated R2 data.

## Storage protection

Terraform creates the private `personal-site-database-backups` R2 bucket in Eastern North America
and sets `prevent_destroy`. The Cloudflare Terraform provider version currently used by the project
cannot manage R2 lifecycle or bucket-lock rules, so configure those rules once with Wrangler after
the bucket is created:

* expire `weekly/` after 60 days
* expire `monthly/` after 400 days
* prevent deletion or overwrite of both prefixes for at least eight days

Object keys contain UTC timestamps and are never reused. Bucket locks reduce the effect of an
accidental or malicious delete performed with the backup credential; client-side encryption
protects confidentiality independently.

## Automation and verification

Use the public repository's included GitHub Actions minutes for the scheduled job. Pin third-party
Actions and the PostgreSQL container by immutable commit or image digest. Install `age` and the AWS
CLI, plus `jq` for inspecting R2 object listings, through the repository's mise configuration.

The job:

1. rejects a Neon URL that is pooled or does not authenticate its TLS connection
2. fails if `pg_dump`, encryption, checksum generation, either upload, or size verification fails
3. promotes at most one successful backup into each monthly prefix
4. opens one deduplicated GitHub issue when a run fails
5. supports manual dispatch for setup and recovery testing

Periodically download an archive, verify its checksum and `age` authentication, decrypt it with the
break-glass identity, and restore it into a new scratch PostgreSQL 17 database. Listing the archive
with `pg_restore --list` is a useful structural check but does not replace a real restore and
application-level sanity test.

# Alternatives Considered

## Neon paid recovery only

Neon Launch provides a longer restore window and scheduled snapshots without exporting the full
database. It has the best recovery time and avoids Free-plan egress pressure.

Deferred while the database is small and replaceable because it adds paid compute/storage usage and
all recovery points remain in the same provider and account. Adopt it when production data makes a
week-long external recovery point unacceptable. Even then, retain an external archive.

## Daily external exports on Neon Free

Daily archives provide a much better external recovery point. At the current maximum database size,
however, they would exceed Neon's Free-plan transfer allowance before application traffic is
counted.

Deferred until measured database size and transfer leave enough headroom, or the project moves to a
paid Neon plan.

## Plain SQL dumps

Plain SQL is human-readable and can be restored with `psql`, but it is slower and less flexible for
selective or parallel recovery. It also produces one large sequential script.

Rejected in favour of PostgreSQL custom format and `pg_restore`.

## R2 storage encryption only

R2 encrypts objects at rest and HTTPS encrypts them in transit, but Cloudflare still controls the
storage encryption boundary. An R2 or Cloudflare account compromise could expose the dump contents.

Rejected as the sole confidentiality control. Retain R2 encryption and HTTPS as additional layers
around the client-side `age` ciphertext.

## Private identity in the CI-synced Doppler config

This would make automated restore jobs convenient, but the scheduled backup never needs decryption.
It would give the same operational trust domain both the encrypted archive and its recovery key.

Rejected. A separate human-only Doppler escrow remains acceptable if it is isolated from CI and
backed by an independent recovery copy.

## Cloudflare Worker Cron Trigger

A Worker can run on a schedule and write R2 objects, but a standard Worker does not provide the
PostgreSQL client binary and is a poor fit for a streaming full-database export. A container-based
Cloudflare design would add more deployment and runtime machinery than this small job needs.

Rejected in favour of GitHub Actions and the official PostgreSQL client container.

# Consequences

## Positive

* **Independent recovery.** A portable archive survives loss of the Neon project or account.
* **Low cost.** Weekly full exports keep Neon Free-plan transfer manageable; R2 storage costs remain
  negligible at the current scale even after the account-wide free allowance is consumed.
* **Fast recent recovery.** Neon history and the manual snapshot avoid a slow archive restore for
  common mistakes.
* **Confidential backups.** R2 never receives a plaintext database archive.
* **Reduced credential blast radius.** CI can create backups but cannot decrypt historical ones.
* **Portable exit path.** The custom archive restores into ordinary PostgreSQL rather than requiring
  a Neon-specific format.

## Negative

* **Weekly external RPO.** A complete Neon loss can lose up to seven days of writes while this stays
  on the Free-plan cadence.
* **Human key custody.** Losing every private-identity copy makes the encrypted archives
  unrecoverable.
* **Runner trust.** The GitHub runner sees plaintext in memory while `pg_dump` reads Neon.
* **Operational setup.** The design requires a backup database role, R2 credential, age key pair,
  Doppler config, GitHub environment, and lifecycle rules.
* **Logical-backup restore time.** `pg_restore` is slower than Neon-native point-in-time recovery.
* **Schedule blind spot.** A failed job opens an issue, but GitHub not starting a scheduled job does
  not itself execute the failure handler. Backup freshness still needs periodic inspection.

# When To Revisit

* The database starts holding irreplaceable user data or the required external recovery point
  becomes shorter than seven days.
* Neon Launch or Open Source Program credits make scheduled native snapshots and a seven-day restore
  window effectively free.
* Monthly `pg_dump` traffic approaches a meaningful share of Neon's transfer allowance.
* Row-level security prevents the read-only backup role from producing a complete archive.
* Restore tests exceed the acceptable recovery time objective.
* GitHub Actions schedule reliability becomes insufficient and warrants an independent freshness
  monitor or another scheduler.
* Cloudflare provider upgrades allow lifecycle and bucket-lock rules to move into Terraform.
* The private identity moves to hardware-backed custody or a dedicated recovery-key escrow service.

---

Markdown index of this site: https://robbiepalmer.me/llms.txt