test(recovery): DR rehearsal must exercise every protected-main migration, not a frozen 0001-0009 list

[seonghobae]

Gap

The disaster-recovery restore rehearsal (.github/scripts/restore-rehearsal-postgres.sh) hardcoded migrations 0001–0009. Migrations 0010–0013— including the curated, migration-backedjob_analysis_snapshottable — were never exercised by recovery evidence, whiledocs/traceability/restore-rehearsal.md` stated the rehearsal applies the protected-main migration sequence.

A migration added to develop would silently fall outside recovery evidence with no failing check.

Required outcome

• The rehearsal derives its applied migration set from the checked-in database/migrations directory in numeric order.
• A recovery test fails closed if the rehearsal reintroduces a frozen filename list, skips a .sql file in the directory, or has non-unique numeric prefixes.
• The traceability doc and provenance manifest reflect the corrected behavior.

Evidence

Repaired in #298. Verified: RED-first test then 4/4 pass, npm run validate exit 0, and on local PostgreSQL 18.4 all 13 migrations apply and job_analysis_snapshot, job_analysis_write_command, people_mutation_idempotency_record, validity_study_case_record survive custom-format pg_dump/pg_restore with RLS forced.

[seonghobae]

Implementation is on canonical PR #298, exact head 9b6eca428c9b500158e3428b9cea461483ee4bcd (ordinary-forward on fix/recovery-rehearsal-full-migration-sequence; protected base develop@eb9757f8649aaad026a9865508d9aad50c1a7a4f unchanged).

All three required outcomes are met and verified on that head:

• Directory-derived, numeric order: discovery enumerates database/migrations via a status-checked assignment (not for ... in $(find ... | sort)), sorts by canonical numeric basename, and applies each with while IFS= read -r + psql -f.
• Fails closed on frozen list / skip / non-unique prefixes: the executable contract fails when the rehearsal applies a migration twice, skips one, applies them out of order, leaves the directory empty/missing, contains a non-canonical .sql basename, or has duplicate numeric prefixes. Discovery now runs before any cluster connection or destructive DDL, so those failures cannot reach the first psql -f application.
• Doc + provenance reflect corrected behavior: docs/traceability/restore-rehearsal.md states the pre-connection discovery contract, and recovery-manifest.json is re-bound to the exact SHA-256/byte/line counts of the executable, test, traceability doc, and workflow.

Evidence on 9b6eca42: RED-first — the two new executable tests fail against the unchanged 161468e3 script while the pre-existing static directory test still passed (proving the static test alone was not a substitute for failing closed); GREEN — all 6 recovery-contract tests pass, npm run validate exits 0 (55/55), and hosted Recovery Rehearsal Quality, Foundation CI, SAST Semgrep, and Repository quality all pass. The only remaining red is the central CodeQL compatibility analysis dispatch status, which is the repo-wide settlement-order issue and not a finding in this change.

Full repair rationale is on the PR: #298 (comment)