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:

⚠️ 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#

# 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:

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.