feat(static-loader): retire stale Section rows without deleting user data

[tiankaima]

Problem

The current importer upserts rows present in the snapshot but does not retire Sections that disappeared. Production currently has 132 Section rows in the four synchronized semesters that are absent from the latest complete snapshot. Hard deletion would risk comments, descriptions, homeworks, subscriptions, and historical references.

Proposed migration

• Add explicit source lifecycle metadata such as sourceLastSeenAt/retiredAt.
• Mark missing in-scope Sections retired only after snapshot completeness validation.
• Hide retired rows from current catalog/calendar surfaces while keeping direct historical/user-content access intentionally defined.
• Preserve all user-generated content and audit every retirement/reactivation.

Acceptance criteria

• No hard deletion of stale Sections or children.
• Re-importing a Section clears retirement and restores it.
• Current catalog/calendar queries exclude retired rows; historical/direct access behavior is contract-tested.
• Production rollout includes before/after counts and a reversible backfill.

Evidence

Latest complete snapshot has 7,918 Sections; production has 8,049 rows across the same four semesters, with 132 production rows absent from the snapshot and one new snapshot row absent from production.

[tiankaima]

Production post-migration audit confirms the latest snapshot has 7,918/7,918 current Section jwIds present, with zero missing Campus values, fallback teacher links, or fallback/stable duplicate teacher groups. Production has 8,050 Sections across semesters 401/421/441/461, so the remaining 132 rows are snapshot-absent stale Sections and should be handled only by this reversible lifecycle work. #471 is now a blocking prerequisite: provider-side backup/PITR and an isolated restore drill must be evidenced before this issue writes production.

[tiankaima]

Draft implementation PR #476 is open and intentionally blocked by #471.

It adds reversible Section lifecycle, fail-closed completeness/SHA/candidate/recency gates, current-view filtering, historical detail semantics, and retirement/reactivation audit rows. Local verification includes fresh-PG integration, real snapshot repeat import, older-snapshot rollback protection, exact one-candidate retirement, and Playwright historical/unsubscribe cases.

The other static models were audited: snapshot-owned ScheduleGroup/Exam and rebuilt Schedule/TeacherAssignment/relation/ExamRoom paths already reconcile removals; Course/Teacher/Semester/lookups remain upsert-only to preserve historical references. The one additional real lifecycle bug was SectionTeacher's globally empty resolved set, tracked in #477 and fixed in #476.

No production Section retirement will be triggered until #471 has provider backup/PITR evidence and a successful isolated restore drill.

[tiankaima]

Update: Draft PR #476 is now at ec6a5e3 and its latest CI run 29610843106 passed all 14 checks, including Check, CodeQL, MCP integration, static-loader image, all four E2E scopes, report publishing, and Workers preview.

The PR now includes reversible Section retirement/reactivation, strict snapshot validation, a transactionally updated global generated-at/SHA watermark, explicit exact-SHA bootstrap, retired-subscription compatibility, and exclusion of retired Sections from public discovery/counts.

The analogous empty-source SectionTeacher reconciliation bug was fixed independently in merged PR #478; #477 is closed. Other imported models were audited: ScheduleGroup and Exam already use scoped diff deletion, while Schedule/TeacherAssignment/relation tables/ExamRoom are rebuilt; Course/Teacher/Semester/lookups remain intentionally upsert-only because historical records reference them.

Production remains unchanged: the 132 candidate Section rows have not been retired or migrated. PR #476 remains Draft and must not merge until #471 records provider backup/PITR evidence and a successful isolated restore drill.

[tiankaima]

Final implementation-gate update: Draft PR #476 is now at exact head af33a8e. CI run 29614225174 passed Check, CodeQL, MCP integration, static-loader image build/smoke/real snapshot dry-run, all four E2E scopes, report publishing, and Workers preview. The loader packaging regression found on the previous head was fixed by packaging the shared lifecycle lock helper; no review or unresolved review threads remain.

The PR also includes transaction advisory-lock serialization and two-client PostgreSQL coverage for subscription-versus-retirement races, with the lifecycle lock window moved next to commit to avoid blocking subscriptions during the long import.

This does not authorize production rollout. #476 remains Draft and explicitly blocked by #471. The 132 candidate rows remain unretired; no lifecycle/backfill/delete write has run. The independent empty SectionTeacher reconciliation shipped separately in #478 / v1.61.2.

[tiankaima]

Completed in #476 and released as v1.62.0.

Production rollout evidence:

• main CI: https://github.com/Life-USTC/server/actions/runs/29615868822 — success
• migration: https://github.com/Life-USTC/server/actions/runs/29616372244 — applied 20260718110000_add_static_import_state
• bootstrap dry-run: https://github.com/Life-USTC/server/actions/runs/29616429014 — SHA 49a9056c5399268eba982a8f8124444f5f257fbde1bed07b479dab409adb3245, 132 candidates, rolled back, 0 retired
• committed bootstrap: https://github.com/Life-USTC/server/actions/runs/29616658563 — committed, lifecycle retirement disabled, 0 retired
• normal sync: https://github.com/Life-USTC/server/actions/runs/29616872842 — committed, same SHA, 132 candidates, 0 retired
• retirement dry-run: https://github.com/Life-USTC/server/actions/runs/29617097722 — exact SHA/count accepted, 132 planned retirements, rolled back
• retirement apply: https://github.com/Life-USTC/server/actions/runs/29617286694 — committed 132 retirements, 0 reactivations
• post-apply normal sync: https://github.com/Life-USTC/server/actions/runs/29617474242 — committed, candidate count now 0, 0 additional retirements/reactivations

No Section or user-owned child data was hard-deleted. Missing rows are reversibly retired, audit records were written in the same transaction, and a future complete snapshot containing the same jwId will reactivate the Section. Production health endpoint returned ok after rollout.

The owner explicitly waived the #471 recovery gate for this rollout; #471 remains open because backup/PITR and restore-drill acceptance are still unmet.