Resetting the production database

This is destructive and, today, unrecoverable if it goes wrong

This wipes every permit, certificate, audit-log row, contractor and uploaded file on e-safework.com and starts over empty. deploy/RUNBOOK.md §6 already flags that a restore has never been tested on this deployment — "an untested backup is a guess." Treat every step below as one-way. Do not run this because a bug report is annoying to reproduce around; run it only when the deliberate goal is "throw away all production data and start clean" (a pre-launch reset, a compromised database, or similar).

Never run this from a script, a cron job, or unattended. Every command here should be typed by a person who has just re-read this page, on the box, watching the output.

This is a different operation from resetting the local database. Locally there is no real data and prisma migrate reset is the whole story. Here there is real data, the migration history in _prisma_migrations must be trusted (see the box on the dev-setup page about why the migration squash was reverted rather than shipped), and every step exists to make the wipe deliberate rather than accidental.

0. Decide it's really this, not something smaller

• A single bad row, a bad migration, a corrupted upload → fix that row/migration/file, don't wipe the database.
• "The demo/UAT data is stale and I want it clean again" → this is the right procedure.
• "I need last Tuesday's state back" → this is a restore, not a reset, and per the warning above that path is not proven to work on this deployment. Test it on a copy first.

1. Take a fresh backup, and get it off the box

backup.sh already runs nightly via cron (deploy/RUNBOOK.md §6), but its output lives on the same disk you are about to wipe. Take one more, by hand, right now:

ssh [email protected]
cd /opt/esw
./backup.sh                                  # writes backups/db-<today>.sql.gz + a minio mirror
ls -la backups/db-*.sql.gz | tail -1          # confirm it's non-empty and just now

Copy that file to your own machine before continuing — if the reset goes wrong, the on-box copy goes with it:

# from your workstation, not the VM
scp [email protected]:/opt/esw/backups/db-<today>.sql.gz .

2. Stop the API so nothing writes during the reset

cd /opt/esw
docker compose -f docker-compose.prod.yml stop api

Postgres, Redis, MinIO and cloudflared stay up — only the API, which is the only thing that writes, comes down. nginx will answer 502 for the API origin until step 5; that is expected and matches an intentional maintenance window.

3. Drop and recreate the database

source .env   # PG_USER, PG_DB into this shell
docker compose -f docker-compose.prod.yml exec postgres psql -U "$PG_USER" -d postgres \
  -c "DROP DATABASE \"$PG_DB\";" \
  -c "CREATE DATABASE \"$PG_DB\";"

This is the actual point of no return. Everything above this line is still recoverable; nothing below it is, without the backup from step 1.

4. Decide whether object storage gets wiped too

Every permit photo, facility-plan image and certificate attachment is a MinIO object referenced by a filePath column that just disappeared. Orphaned objects are harmless (nothing serves them without a DB row pointing at them), so wiping the bucket is optional, not required — do it only if "clean" means the storage layer too:

docker compose -f docker-compose.prod.yml exec minio sh -c \
  'mc alias set local http://127.0.0.1:9000 "$MINIO_ACCESS_KEY_ID" "$MINIO_SECRET_KEY" &&
   mc rm --recursive --force local/'"$MINIO_BUCKET"

5. Bring the API back up

The image's own CMD runs prisma migrate deploy before starting the server, so restarting it against the empty database replays the full, real migration history — same 27-migration path a fresh production install has always taken, nothing squashed or shortcut:

docker compose -f docker-compose.prod.yml up -d --wait api
docker compose -f docker-compose.prod.yml logs api --tail=50   # confirm migrate deploy succeeded

If it does not come healthy, do not retry blindly — read the log. A failed migrate deploy here means the migration history and the empty schema disagree, which given step 3 should not happen; if it does, stop and treat it as its own incident rather than repeating steps 3-5.

6. Seed

docker compose -f docker-compose.prod.yml exec api bun run seed

Creates the system-admin account (SYSTEM_ADMIN_EMAIL/SYSTEM_ADMIN_PASSWORD from .env, or the create-user.seed.ts defaults if unset) and the three fixed accounts documented on the dev-setup page — [email protected], [email protected], [email protected], all Wasd#1234. Decide before running this whether those three plain-password accounts belong on a public production URL at all, versus only ever being seeded on a staging/UAT deployment — nothing in the seed script itself restricts where it runs.

Do not run bun run seed:e2e here. Its fixture accounts ([email protected] etc., see E2E test plan §7) and its 10 synthetic permits are for a throwaway test environment, not for the box real users log into.

7. Verify

• GET https://api.e-safework.com/ answers 200 (bare health route, outside /api).
• Log into each app with each of the three seeded accounts.
• docker compose -f docker-compose.prod.yml exec postgres psql -U "$PG_USER" -d "$PG_DB" -c "select count(*) from \"user\";" — should read back exactly the accounts you just seeded, nothing pre-existing.

What this page does not cover

Restoring the backup from step 1 back into a database (as opposed to starting empty) is not documented anywhere yet — that is the gap RUNBOOK.md §6 already names. If a restore is what's actually needed, write and test that procedure against a disposable copy of the database first; do not treat the steps above as a substitute for it.