# Runbook — Yemaya study workspace: backup and restore

**Checklist:** YSD-22058 (`backup/restore`), YSD-3066, YSD-3067 **Owner:** the
on-call engineer for `svc-study-workspace` **Machinery:**
`apps/yemaya/svc-study-workspace/src/persistence/backup.ts`

This exists because the programme had a restore MECHANISM and no restore
PROCEDURE. `backup-restore.integration.spec.ts` drives a real backup and a real
restore against real PostgreSQL on every change, which proves the mechanism
works when invoked; it does not answer whether a person who has not done this
before can make it work from a document, under time, on the day it matters. That
is what a drill asks, and `procedure-exercise-register.json` recorded
`procedure: null` for this one because no runbook in `docs/runbooks` named this
workspace. `object-storage-backups.md` covers yemaya avatars and attachments and
stops there — the study bucket is not in its scope table.

**Running this document IS the drill.** Record the result in the procedure
exercise register; a drill that leaves no mark is a drill nobody can cite.

---

## 1. What has to come back

Two stores, and they fail differently.

**PostgreSQL — the `study` schema.** The backup enumerates every table from the
catalog rather than from a list somebody maintains, so coverage is by
construction and a new table cannot be silently missed.

**Object storage — bucket `oshun-study`** (`STUDY_S3_BUCKET`). Not in scope of
`object-storage-backups.md`. Recovery priority follows the retention class the
key's object class implies, and the classes are not equally precious:

| retention class | object classes                                                                   | if the bytes are gone                                                                                                                 |
| --------------- | -------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `source-bound`  | `original`                                                                       | **Critical.** Lives exactly as long as its rights basis. Cannot be regenerated, and re-acquiring may require the rights holder again. |
| `learner-owned` | `learner-attempt`                                                                | **Critical.** A learner's own work. Not regenerable and not ours to recreate.                                                         |
| `export`        | `export`, `report`                                                               | Medium — regenerable while the works they embed still exist, and purged when one is deleted.                                          |
| `derived`       | `proxy`, `caption`, `thumbnail`, `crop`, `waveform`, `stem`, `analysis-artifact` | Low. Regenerable from the original; a restore may legitimately leave these empty and let them rebuild.                                |
| `quarantine`    | `quarantine`                                                                     | Do not restore. A bounded window pending a scan decision; restoring re-admits unscanned bytes.                                        |
| `temporary`     | `temp`                                                                           | Do not restore. Always safe to drop.                                                                                                  |

⚠️ **Restoring `derived` objects over a database that lost rows is the wrong
order.** Derived bytes are purged with their original; if the database comes
back without the original and the bucket keeps the proxy, the proxy is
unreferenced and outlives the rights basis it was derived under. Restore the
database first, then let derivation rebuild, and reconcile what is left.

## 2. Before you start

- A **freshly migrated, EMPTY target database.** `restoreStudySchema` refuses a
  non-empty target with `RestoreTargetNotEmptyError` naming the table and its
  row count. This is deliberate: there is no merge-restore. If you are tempted
  to clear the target by hand, stop and confirm you are not looking at
  production.
- The backup you intend to restore, and **the instant it was taken.** Write that
  instant down before you begin. Everything in §4 is a comparison against it.

## 3. Restore

```bash
# Target must be freshly migrated and empty.
node -e "…" # or: the service's migrate entrypoint against the TARGET url
```

Then `restoreStudySchema(targetClient, backup)`. What it does that you should
not attempt by hand:

- **Identity columns keep their original values** (`OVERRIDING SYSTEM VALUE`)
  and sequences are advanced past the restored maximum. Skip this and the first
  insert after a restore collides with a restored row.
- **User triggers are disabled per table during the copy.** Anchor→edition
  existence and review-transition legality were enforced when the data was first
  written; re-running them in restore order fails on rows that were always
  legal.

## 4. Verify — and this is the part that is easy to fake

`verifyRestoredSchema(targetClient, backup)` recomputes every table's content
hash and row count in the target and compares against the manifest. Read three
fields, not one:

1. **`matches`** — the overall verdict.
2. **`tables[]`** — per table, `expectedRows` vs `actualRows` and
   `expectedSha256` vs `actualSha256`. A hash is order-insensitive, so a table
   that came back in a different order still matches; one that came back short
   does not.
3. **`unmanifestedTables`** — tables live in the target and absent from the
   manifest. This is coverage drift and it fails the verification. It is the
   field that catches a table added since the backup was written, which is
   exactly the case where "the restore verified" is most dangerous.

**A verification that passes over an empty manifest has verified nothing.** Run
`assertBackupCoverage(backup)` first: it pins the relationship families YSD-3066
names to the tables persisting them today, so an empty or truncated manifest
fails before the comparison rather than sailing through it.

Beyond the hashes, the integration suite additionally proves — and a drill
should re-check by hand at least once — that identifiers and rights grants read
back identically **through the store** rather than through raw SQL, supersession
chains stay intact, graph paths answer identically, the audit hash chain still
verifies (`verifyAuditLedger`), and deletion tombstones survive. A tombstone
that did not come back is a deleted work quietly readable again.

## 4b. The objective, and what it is not

**Restore time objective: 60 minutes**, from deciding to restore to §5 being
satisfied on the target. Set 2026-08-15.

Measured rather than estimated, and the measurement is a **floor**, not the
objective. `scripts/time-backup-restore.ts` runs the mechanical path of §3 and
§4 — migrate an empty target, back up the source schema, `assertBackupCoverage`,
`restoreStudySchema`, `verifyRestoredSchema` — against a real database and
reports what it cost. Two runs against the deployment on 2026-08-15, an hour
apart, with `matches: true` and `unmanifestedTables: []` both times:

| rows in the manifest | tables | total |
| -------------------- | ------ | ----- |
| 77,315               | 100    | 6.7s  |
| 153,792              | 100    | 9.6s  |

**Two points, deliberately, rather than one.** The estate doubled between them
(the deletion and expiry drills each leave a project behind), and the time did
not — which is what a floor measured on a small estate can and cannot tell you.
Re-measure; do not interpolate.

Sixty minutes is therefore not a claim about the queries. It is the operator:
§2's "confirm you are not looking at production" and writing down the instant
the backup was taken, §4's three fields read by hand rather than the one, the
re-check through the store rather than through raw SQL, and §5's accounting for
the object classes. That is the work a drill is measuring, and it is the work an
objective sized from the stopwatch would have declared already done.

Re-measure rather than re-estimate when the estate grows. The script exists so
that the floor under this number is a fact somebody can reproduce, and 77,315
rows on one box is a small estate — the parts of a restore that do not scale
linearly are the ones a floor measured here cannot see.

## 5. Done means

- `verifyRestoredSchema` returns `matches: true` with `unmanifestedTables: []`.
- `verifyAuditLedger` verifies on the target.
- The object classes above are accounted for: `source-bound` and `learner-owned`
  present or explicitly listed as lost, `derived` present or scheduled to
  rebuild, `quarantine` and `temp` NOT restored.
- The result is recorded in
  `docs/proposals/yemaya-study-workspace/procedure-exercise-register.json`,
  naming who ran it, against which environment, and how long it took. An
  unrecorded drill leaves the register saying `exercised: null`, which is where
  it started.

## 6. Known shortfall

This covers the study schema and the study bucket. It does NOT cover the
cross-store ordering question — whether the object store snapshot was taken no
earlier than the database snapshot it is restored beside. A referenced object
captured before the row that references it is a dangling reference on the way
back; the reverse is merely an orphan. Nothing in this workspace currently
records the instant either snapshot was taken, so a drill cannot check it today.
That is a gap in the machinery, recorded here rather than papered over.