Backup and restore

Customer Portal keeps its mutable application state in PostgreSQL. A complete database backup therefore protects accounts, organizations, sessions, feature data, timesheets, invoices, invoice attachments, and recorded email-delivery history.

It is also a high-value secret. The database contains password hashes, session and OAuth tokens, personal and financial records, attachment contents, and organization-specific Resend API keys. Encrypt backups at rest, restrict access, record access, and apply a retention policy appropriate to that data.

Decide the recovery target

For production, use the hosting provider's automated backups and point-in-time recovery when available. Choose and document:

• the recovery point objective (RPO), which determines backup frequency;
• the recovery time objective (RTO), which determines how quickly a restore must be ready;
• backup retention and geographic redundancy;
• who can start, validate, and approve a restore;
• how often the team performs a recovery drill.

PostgreSQL's continuous archiving and point-in-time recovery can restore to a chosen point between base backups. A periodic logical dump remains useful for portable restore tests and smaller deployments.

Create a logical backup

Run pg_dump from a trusted machine with encrypted transport to PostgreSQL:

pg_dump --format=custom --no-owner --no-acl \
  --file=customer-portal-YYYY-MM-DD.dump \
  "$DATABASE_URL"

Use a pg_dump client from the same PostgreSQL major version as the server or a newer one that supports it. An older client refuses to dump a newer server, and dump output is not guaranteed to load into an older server. See the official pg_dump compatibility notes.

Avoid placing a literal database password in shell history. Supply the connection through the deployment secret manager, a short-lived environment, or PostgreSQL's supported password-file mechanism. After the command succeeds:

pg_restore --list customer-portal-YYYY-MM-DD.dump

Store the archive encrypted and separately from the production database. Record the source commit, PostgreSQL version, backup time, file checksum, and operator with it.

Restore into an isolated database

Never make the first restore attempt over production. Create a new empty database with the expected extensions and privileges, then restore:

pg_restore --dbname="$RESTORE_DATABASE_URL" \
  --no-owner --no-acl --exit-on-error \
  customer-portal-YYYY-MM-DD.dump

--exit-on-error prevents a partial failure from being mistaken for success. For a suitably sized archive, --single-transaction can make the restore atomic. The official pg_restore reference explains both options and recommends restoring into a truly empty database. Treat every archive as executable database input and restore only backups you trust.

Start the exact Customer Portal commit recorded with the backup. The repository Docker entrypoint automatically applies migrations, so do not point an unvalidated newer image at the restored database. If the objective is an upgrade rehearsal, first prove the original revision and data, take another snapshot, and only then test the target revision.

Validate the recovery

The restore is not complete until an operator verifies it. At minimum:

1. confirm the migration history and application startup succeed;
2. sign in with a test operator and switch between expected organizations;
3. inspect memberships and authorization boundaries;
4. open representative time entries, approvals, invoices, and invoice email history;
5. download an invoice attachment and generate an invoice PDF;
6. exercise a non-destructive database-backed workflow;
7. compare important row counts and timestamps with the backup record.

Do not send real email or invoke production OAuth callbacks from the isolated environment. Replace or disable external credentials first. If production secrets were restored into a less-trusted environment, rotate them after the drill.

Schedule recurring restore tests. A successful backup job proves that a file was written; only a verified restore proves that the service can recover.