team-tryouts/docs/database-restore.md

# Database Backup and Restore

A backup that has never been restored is not a backup. This document exists
so that the restore path is exercised **before** it is needed, not during an
incident.

---

## 1. What is backed up

`app/supporting_scripts/backup.py` produces two artefacts per run, in
`BACKUP_DIR` (default `./backups`):

| Artefact | Contents | Why it matters |
|---|---|---|
| `db_backup_<timestamp>.dump` | Full PostgreSQL dump, custom format | Every account, tryout, evaluation, note and contract record |
| `documents_backup_<timestamp>.zip` | `documents/` directory | The contract **files** themselves — they exist only on disk, the database stores paths |
| `documents_backup_drive_<timestamp>.zip` | Google Drive contract folder | Drive-stored contracts downloaded and archived when `DOCUMENT_STORAGE_BACKEND=google_drive` |

Losing either one alone loses data. A database restore without the document
archive leaves contract rows pointing at files that no longer exist.

---

## 2. Running a backup

```bash
# From the project root, with DATABASE_URL set
python app/supporting_scripts/backup.py
```

Requires the PostgreSQL client tools (`pg_dump`, `pg_restore`) on `PATH`, or
`PG_DUMP` / `PG_RESTORE` pointing at them.

| Variable | Default | Purpose |
|---|---|---|
| `DATABASE_URL` | — | Required. `postgresql://user:pass@host:port/dbname`. The application names the psycopg 3 driver itself; this script accepts either form. |
| `BACKUP_DIR` | `./backups` | Destination directory |
| `BACKUP_RETENTION_DAYS` | `30` | Files older than this are deleted |
| `PG_DUMP` / `PG_RESTORE` | `pg_dump` / `pg_restore` | Full paths if not on `PATH` |

**Exit code 0 means the dump was produced *and* verified.** Anything else
means you have no usable backup from that run — treat a non-zero exit as an
incident, not a warning. Whatever schedules this job must check the exit
code; the previous version of the script returned 0 even when it had backed
up nothing at all.

### Verifying an existing archive

```bash
python app/supporting_scripts/backup.py --verify-only backups/db_backup_20260807_030000.dump
```

This reads the archive with `pg_restore --list` and confirms it contains
table data. It touches no database.

---

## 3. Restore drill

**Run this on a throwaway database, at least once a quarter, and after any
change to the schema tooling.** It is the only thing that turns a file into
a guarantee.

### 3.1 Create an isolated target

Never restore onto the production database to "test" a backup.

```bash
createdb -h localhost -U postgres tryouts_restore_test
```

### 3.2 Restore

```bash
# --clean --if-exists makes the restore repeatable
pg_restore \
  --host localhost --port 5432 --username postgres \
  --dbname tryouts_restore_test \
  --clean --if-exists --no-owner --no-privileges \
  backups/db_backup_20260807_030000.dump
```

`pg_restore` reports errors per object and continues. **Read its output.**
A restore that emits errors and returns 0 has still lost something.

### 3.3 Check the data is actually there

```sql
-- Connected to tryouts_restore_test
SELECT COUNT(*) FROM users;
SELECT role, COUNT(*) FROM users GROUP BY role ORDER BY role;
SELECT COUNT(*) FROM tryouts;
SELECT COUNT(*) FROM evaluations;
SELECT COUNT(*) FROM contracts;
SELECT MAX(created_at) FROM users;
```

Compare against production. The last query tells you how old the backup is
— the number that matters during an incident.

### 3.4 Start the application against the restored copy

```bash
DATABASE_URL="postgresql://postgres@localhost:5432/tryouts_restore_test" \
SECRET_KEY="throwaway-for-the-drill" \
ENABLE_DISCORD_BOT=false \
FORCE_HTTPS=false \
python wsgi.py
```

`ENABLE_DISCORD_BOT=false` is not optional. Without it the drill starts a
real bot against the real Discord server and sends real notifications to
real people, from restored data.

Smoke test:

1. `GET /health` returns 200 with `"database": "connected"`.
2. Log in with a known account.
3. Open a tryout and check its registrations are present.
4. Open the calendar.

### 3.5 Tear down

```bash
dropdb -h localhost -U postgres tryouts_restore_test
```

---

## 4. Restoring documents

```bash
unzip backups/documents_backup_20260807_030000.zip -d documents/
```

Then confirm a contract downloads through the application, not just that
the file exists: `Contract.file_path` stores an **absolute** path recorded
at upload time. If the deployment root has changed since, the rows point
somewhere that no longer exists and the files must be placed at the old
path, or the column updated.

---

## 5. Recovery plan

| Scenario | First move | Then |
|---|---|---|
| Accidental deletion of a few records | Restore to a throwaway database (§3), extract the rows, re-insert them | Do **not** restore over production |
| Database corrupted or lost | Restore the latest verified dump onto a fresh database, repoint `DATABASE_URL` | Restore documents (§4), then smoke test (§3.4) |
| Bad deployment | Redeploy the previous commit | The database is untouched unless a migration ran |
| Bad migration | Restore the pre-migration dump | Always take one immediately before migrating |
| Server lost entirely | Provision a host, restore database and documents, redeploy | Discord bot token and `SECRET_KEY` must be reissued if they were on the lost host |

### Two numbers to agree on

- **RPO** — how much data may be lost. It equals the backup interval.
  Nightly backups mean up to 24 hours of tryouts, evaluations and notes.
- **RTO** — how long recovery may take. Measure it during the drill; do not
  estimate it.

Neither number is currently set for this project. Deciding them is a
prerequisite to claiming there is a backup policy.

---

## 6. Open points

- **Off-site copy.** Backups written next to the application are lost with
  the host. Nothing currently copies them elsewhere.
- **Encryption at rest.** The dump contains every account record and
  password hash. It is not encrypted.
- **Scheduling.** No scheduled task or cron job is configured in the
  repository. The script must be wired to one, with its exit code monitored.
- **Restore drill.** Has never been performed. Until it is, the restore
  path is untested.