# Runbook: V1 Database Loss (`gd:database-loss`)

Owner: workbench-platform on-call (`rota:workbench-platform`). Last reviewed:
2026-08-14. Version: 1.

> The relational store is unavailable. It is the substrate under the search
> index and the scan fallback, so it takes both with it. Distinct from
> [v1-dr-relational-corruption.md](v1-dr-relational-corruption.md) because the
> recovery is a restart or a restore rather than a rollback, and the recovery
> point is untouched.

**There is a failover script, and this estate has nothing to fail over to. Read
§9 before reaching for it.**

## 1. Detection and declaration

**Detection signal.** Sixteen capabilities stop at once, including the ones an
actor tries first: "open an item", "save an edit", "record a review decision".
`slo:item-read-availability` and `slo:save-durability` move immediately, and
`pg_isready` against the host answers the narrow question directly.

Unlike a corruption, this announces itself. The declaration is not the hard
part; §9 is.

- Detection authority: **workbench-platform on-call**, contact
  `rota:workbench-platform`.
- Declaration authority: **production operations duty officer**, contact
  `rota:prod-ops`.
- **Gap, stated.** `docs/operations/on-call.md` describes a weekly
  primary/secondary rotation and names no person and no rotation config; none of
  the register's `rota:` identifiers appears in it or in
  `docs/operations/alert-routing.md`. The name above is registered; the route
  from it to whoever is carrying the pager is not.

## 2. Communications

| Audience  | Within | Content                                                                          |
| --------- | ------ | -------------------------------------------------------------------------------- |
| Actors    | 15 min | that the workbench is unavailable, and whether edits in flight were acknowledged |
| Operators | 5 min  | that this runbook is running, and by whom                                        |

Both travel over `dep:notification-channel`, which this scenario leaves standing
— the fault is confined to the store, not to the host.

## 3. Dependency assumptions

- `dep:blob-store` is healthy and stays where it is. The rows reference blob
  keys; bringing the rows back against a blob store that also moved is two
  recoveries at once.
- `dep:job-queue` is healthy. Its durable record is the `admin_store_snapshot`
  table **inside the store that is down**, so accepted work is not lost with the
  process — see the preamble of [v1-dr-queue-loss.md](v1-dr-queue-loss.md).

## 4. Recovery infrastructure

**In place.** PostgreSQL is a service in `docker/docker-compose.yml`. If the
container is gone and the volume survives, the recovery is a restart. If the
volume is gone, it is a restore, and §6 is that.

## 5. Secrets

- PostgreSQL credentials (`PG_HOST`, `PG_PORT`, `PG_USER`, `PGPASSWORD`), the
  same variables `scripts/db-backup.sh` and `scripts/db-restore.sh` use.
- The restic repository and password, if the dumps come from the off-box
  snapshot.

Recoverable out of band: `offbox-backup.sh` refuses a repository local to the V1
host, and the `configuration-and-secrets` state class is in the capture.

## 6. Restore order

1. `dep:primary-store` — restart, or restore with
   `scripts/db-restore.sh BACKUP_DIR`. It verifies every SHA-256 in
   `manifest.txt` before invoking `pg_restore` and aborts with exit code 2 on
   mismatch; it never touches the source files.
2. `dep:primary-store-scan` — the scan reads the same rows and is correct as
   soon as they are.
3. `dep:search-index` — last. The index is projected from the rows, and one
   rebuilt before the restore completes is stale in a way that looks healthy.

`dep:blob-store` is not in this order because it is not taken away. If it were,
it would come **first** — a row exposed before its bytes is a dangling
reference. That is the reverse of the capture order and the same rule §6 of
[v1-dr-blob-store-loss.md](v1-dr-blob-store-loss.md) states from the other side.

## 7. Validation

- `db-restore.sh` manifest verification, which runs before any data is written.
- `SELECT count(*) >= 0 FROM pg_catalog.pg_class` per restored database — the
  floor `infra/hetzner/backup/box-loss-restore-drill.sh` applies, and the only
  count floor that drill has.
- **A named count, recorded at a named instant.** An empty restore answers every
  check above. Record what the estate held before the loss and compare.

## 8. Fencing — ABSENT for a promoted primary

The estate has exactly one real fencing token and it does not cover a store:
`live_media_pipeline_job_attempts.lease_token`, in
`libs/shared/live-media/src/sql-media-pipeline-job-store.ts`, appears in the
`UPDATE ... WHERE` of every heartbeat, lease expiry and completion as
`AND lease_token = $4 AND status = 'leased'`, so a worker whose lease was
reassigned updates zero rows. That is lease fencing and it works. Nothing does
the equivalent for PostgreSQL: no token rejects the writes of a primary that
comes back after a replacement was promoted.

For a restart-in-place this costs nothing, because there is only ever one
primary. It is §9 that makes the absence expensive, and §9 is where this estate
would need it — and where the `lease_token` pattern is the one already in the
tree to copy.

## 9. Failover — the script exists and there is nothing to promote

`infra/yemaya/postgres-ha/failover.sh` is a real standby-promotion script:
`pg_ctl promote`, a replication-lag check, a PgBouncer repoint. **It does not
apply to the V1 estate**, and reaching for it under pressure is the failure mode
this section exists to prevent.

Three measured facts, in the order an operator meets them:

1. **There is no standby.** `docker/docker-compose.yml` declares exactly one
   PostgreSQL service and no service named for a standby, a replica or a pooler.
   (Search the file text rather than its service names and you will match
   `KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR`, which is not a Postgres replica —
   an answer about the wrong subject in the right shape.) Nothing outside
   `infra/yemaya/postgres-ha/` references that directory at all — not the V1
   compose, not a deploy script, not CI. The configuration with
   `archive_mode = on` in `infra/yemaya/postgres-ha/primary.conf` is not the
   configuration this estate runs.
2. **The script refuses, correctly.** `check_standby` runs
   `SELECT pg_is_in_recovery()` and exits with "This server is not in recovery
   mode (not a standby)". This is the honest failure and it is the one you will
   get.
3. **If it did run here, two of its steps would mislead you.**
   `update_pgbouncer()` logs
   `PgBouncer update requires manual configuration or automation` and **returns
   0** — it does nothing and reports success, so `do_failover` continues as
   though the pooler had been repointed. And `promote_standby` calls `read -p`
   for confirmation when replication lag exceeds sixty seconds, so it cannot run
   unattended: under automation it blocks on stdin rather than promoting.

**So the V1 answer to a database loss is restart or restore, not failover.** If
a standby is ever added, this section and `promotesAStandby` in §13 must change
together, and §8 stops being free: `failover.sh` prompts
`Force failover anyway? This may cause split-brain!` and proceeds on `yes`, with
no token rejecting the old primary's writes. A human typing `yes` is the only
thing between that script and two primaries.

## 10. Reconciliation

Run `scripts/operations/v1-restore-reconcile.mjs` before resuming autonomous or
effectful work, and treat it as mandatory when the recovery was a **restore**
rather than a restart. A restart loses nothing the store had committed; a
restore rolls the estate back to the last dump, and everything the outside world
observed after that instant is now unrecorded.

**Give it expected counts.** Empty reconciles clean.

## 11. Failback

There is no alternate topology to return from, because §9 established there was
nowhere to go. Failback is the resumption of writes, gated on the reconciler
returning no blockers, followed immediately by a fresh backup: after a restore,
the estate's most recent capture is the one it was restored from and it predates
the incident.

## 12. What this runbook does not cover

- A store that answers with wrong rows — see
  [v1-dr-relational-corruption.md](v1-dr-relational-corruption.md).
- A total host loss — see [v1-disaster-recovery.md](v1-disaster-recovery.md).
- Standing up replication. `infra/yemaya/postgres-ha/README.md` documents that
  setup; adopting it for V1 is a change, not a recovery step.

## 13. Machine-checkable statement

<!-- oshun:runbook -->

```json
{
  "id": "runbook:v1-database-loss",
  "scenarioId": "gd:database-loss",
  "version": "1",
  "detectionAuthority": {
    "role": "service on-call",
    "name": "workbench-platform on-call",
    "reachableOver": ["dep:notification-channel"]
  },
  "declarationAuthority": {
    "role": "operations duty officer",
    "name": "production operations duty officer",
    "reachableOver": ["dep:notification-channel"]
  },
  "communications": [
    {
      "audience": "actors",
      "channelDependencyId": "dep:notification-channel",
      "withinMs": 900000
    },
    {
      "audience": "operators",
      "channelDependencyId": "dep:notification-channel",
      "withinMs": 300000
    }
  ],
  "assumes": ["dep:blob-store", "dep:job-queue"],
  "recoveryInfrastructureDependencyId": null,
  "secrets": [
    {
      "id": "PGPASSWORD",
      "heldInDependencyId": "dep:primary-store",
      "recoverableOutOfBand": true
    },
    {
      "id": "restic repository password",
      "heldInDependencyId": "dep:primary-store",
      "recoverableOutOfBand": true
    }
  ],
  "restoreOrder": [
    "dep:primary-store",
    "dep:primary-store-scan",
    "dep:search-index"
  ],
  "fencing": null,
  "promotesAStandby": false,
  "validation": [
    "db-restore.sh verifies every SHA-256 in manifest.txt and aborts with exit code 2 on mismatch",
    "each restored database answers SELECT count(*) >= 0 FROM pg_catalog.pg_class",
    "recorded counts from before the loss are compared against the restored counts"
  ],
  "failback": "resume writes once v1-restore-reconcile.mjs returns no blockers, then take a fresh backup; there is no alternate topology to return from because there is no standby"
}
```