Timetable revision history — retention on republish (iteration 2)¶

Superseded implementation detail (2026-07-27). Temporal spec A keeps this design's retained-version model but replaces its two unsafe assumptions: versions now carry valid-time intervals, and the system freezes derived lesson length in resolvedDuration, never in the admin-authored slotDuration. Where this historical record conflicts with A, A governs.

1. Problem distillation¶

Republish is a lossy write. publishRevision clears the published content, copies the draft's onto the stable published id, and hard-deletes the draft. "Hard-delete superseded content, no version trail" was a deliberate call (2026-07-15 §Confirmed decisions), taken when attendance was the only consumer and the audit log was judged sufficient for who-did-what.
A second consumer is now known. A budget module will compare timetable versions and cost them. Existing vs simulated already works today — a published timetable and its open revision draft coexist, and standalone DRAFTs are unlimited — so what is actually missing is past baselines: what we ran in Q1 versus what we run now, and what a given republish changed.
Without upstream retention every consumer builds its own snapshot. Attendance already had to: AttendanceDaySlot exists because a republish destroys the day a register was taken against. A second ad-hoc snapshot for budgeting would confirm the upstream model as the defect.
A retained version is not costable unless its durations are frozen with it. A lesson's length is derived — the curriculum cascade (cell → subject → grade → curriculum), with nullable slotDuration reserved for an authored override — so re-costing a retained version after a curriculum edit silently returns a different number for a timetable nobody touched. Temporal spec A therefore added the separate system-owned resolvedDuration freeze.

Success criteria (observable behavior that proves this works): - After N republishes of one lineage, N timetables rows exist holding the exact content that was live, each with supersededAt, a revisionNumber, and revisionOfId pointing at the still-stable published row. - Every lesson on an archived row is stable: an authored slotDuration remains authored, while a derived duration is frozen in non-null resolvedDuration; a later curriculum edit does not change it. - No API response changes and no new route. GET /timetables returns exactly what it returned before (its existing revisionOfId: null filter hides the archives); the published timetable keeps its id. - The open-revision invariant still holds: at most one DRAFT revision per published timetable, now enforced by a partial unique index instead of a plain one. - As originally scoped, deleting a published timetable removed its whole lineage through the self-FK cascade. Temporal spec A supersedes that behavior once any version has governed a day: the lineage is then immutable.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - No read or diff surface (GET /timetables/:id/revisions and the diff itself belong to the budget module, which owns the shape of its own comparison). - No validity windows and no date-aware resolution in this iteration. This decision was later reversed by temporal spec A, which distinguishes valid time from transaction time and makes dated resolution authoritative. - Attendance is not rewired. AttendanceDaySlot stays the compliance snapshot; nothing in src/attendance/ reads an archived timetable. - No freezing of teacher attribution or student rosters into the archive (see §9). - No pruning or retention policy, and no DTO exposure of the new columns.

2. Patterns survey¶

Analogous module/spec	What we'd borrow	What doesn't fit
`2026-07-15-timetable-revision-republish-design.md`	The whole machinery: `copyTimetableContent` / `clearTimetableContent`, the stable published id, the lock ordering (draft `FOR UPDATE` then parent), the diagnostics gate before the swap. This iteration inserts one step into an existing transaction.	Its "hard-delete superseded content, no version trail" decision is precisely what we reverse — see §7.
`2026-07-26-attendance-day-shape-snapshot-design.md`	The lesson that a snapshot pointing at a live graph is only half-frozen: freeze the derived value, not just the reference. A later correction put that freeze in `resolvedDuration`; `slotDuration` remains authored-only.	Attendance needed a new table because rosters and names must be frozen too. Here the `Timetable` row is already a first-class container, so retention needs no new model.
`src/timetables/timetables.service.ts` `duplicate()`	Deep-copying content into a second `Timetable` row is already a first-class, tested operation. Archiving is the same move with a different target status.	`duplicate` produces an editable DRAFT; the archive must be born read-only.
`prisma/migrations/20260612101247_add_timetables/migration.sql`	The partial-unique-index technique — hand-written SQL with an explanatory comment, because Prisma cannot express `WHERE <predicate>`. Precedent: `timetables_tenant_ay_published_unique`, plus `roles_platform_key_key`, `room_types_platform_name_key`, `evaluation_scales_platform_name_key`.	Those predicates are `IS NULL` tests; ours is on an enum column (`status = 'DRAFT'`), which behaves identically but must survive a status transition — see §4 hazards.
`src/timetables/timetables.policy.ts`	Non-admins are already pinned to `status: PUBLISHED`, so archived rows are invisible to them with zero policy work.	Admins see every status, which is why the archives must stay out of `findAll` some other way — they do, via the existing `revisionOfId: null` filter.

3. Architecture mapping¶

Primitive	Apply?	How	Justify
Tenant scope	yes	The archive row is inserted with the published row's `tenantId` inside the same tenant-GUC transaction; `timetables` and all three content tables are already RLS-covered.	No new model ⇒ no `rls-coverage.ts` or `tenanted-models.ts` delta.
Academic-year scope	yes	The archive inherits `academicYearId` from the published row. `findAll` resolves the active year and filters on it, so archives are year-scoped like every other timetable.	Keeps a lineage inside one year, which is what the budget module will compare within.
RBAC entity key	existing `TIMETABLES`	No delta to `entity-keys.ts`.	Retention is a side effect of an existing action, not a new capability.
Scopes	existing `timetables.configuration`	No new scope.	No new field surface; the new columns are not exposed in any DTO this iteration.
Actions	existing `publish`	`POST /timetables/:id/publish-revision` already requires it; the archive happens inside that call.	Anyone allowed to republish is by definition allowed to supersede the current version.
Service base	custom (`TimetablesService`)	n/a — the module predates `BaseTenantedCrudService` and is not being migrated here.	Out of scope for a retention change.
`queries.ts` shape	include/select consts + named functions	New: `archivePublishedVersion(tx, …)` (insert the archive row + copy content) and `stampResolvedDurations(tx, timetableId)` (batched cascade resolve + `updateMany` per distinct duration). Changed: `findOpenRevision` gains `status: DRAFT`; `timetableSummaryInclude`'s `revision` becomes `revisions`.	Mirrors `loadCascadeLevels` in `src/attendance/attendance-day.queries.ts`: two queries for the whole timetable, not two per lesson.
Error codes	existing	None new. A republish that cannot archive fails the whole transaction on the underlying DB error.	No new user-actionable failure mode is introduced.
DTO conventions	unchanged	`supersededAt` and `revisionNumber` are not added to `TimetableResponseDto`.	Keeps "no consumer-breaking changes" literal. The budget module's spec can expose them when it defines its read surface (§9).
File-backed sub-resources	n/a	No files involved.
Custom fields	n/a	Timetables carry no custom fields.
Profile completeness	n/a	Not a person entity.

4. Data model plan¶

Schema deltas¶

Timetable.supersededAt DateTime? @map("superseded_at") — when this version stopped being live. Null on DRAFT and on the currently-published row; set exactly once, at archive time.
Timetable.revisionNumber Int? @map("revision_number") — 1-based, per lineage. Set on first publish and incremented on the published row at each republish; the archive keeps the number it held while live. Null on revision drafts and on never-published drafts.
Relation revision Timetable? @relation("TimetableRevision") becomes revisions Timetable[] @relation("TimetableRevision"). Forced: once revision_of_id is no longer unique, Prisma models the back side as a list.
@@unique([revisionOfId]) is removed from the schema and replaced by a hand-written partial unique index (below) plus a plain @@index([revisionOfId]).
No new model, no new enum member. TimetableStatus.ARCHIVED is reused (§7).

Migration shape¶

Additive + one index swap. Two nullable columns, one dropped unique index, two created indexes. No column drops, no type changes, no renames.
Data backfill: UPDATE timetables SET revision_number = 1 WHERE status = 'PUBLISHED', so an existing lineage starts at v1 and its first republish archives a numbered row rather than a null one. Nothing else to backfill — no timetable is currently ARCHIVED (nothing in src/ assigns that status today; the AY-archival cascade it was reserved for is not implemented).
Hazards from the chapter-12 checklist:
Dropping a unique constraint. timetables_revision_of_id_key is dropped and re-created as a partial index in the same migration, so the "one open revision per published" invariant is unenforced only between two statements of one transaction. No hazard in a rolling deploy either: old code writes at most one row per revision_of_id regardless.
Prisma cannot express the partial index, so it lives only in migration.sql and must be re-added by hand if the model is ever re-generated. Mitigated by a schema comment on revisionOfId naming the index, matching the four existing precedents.
FK lookup performance. Postgres does not auto-index foreign keys; today revision_of_id is covered incidentally by its unique index. Dropping it without replacement would turn every lineage lookup into a seq scan, hence the explicit @@index([revisionOfId]).
Not a new tenant-bearing model ⇒ the five drift guards do not apply (no rls-coverage.ts, tenanted-models.ts, ENTITY_LABELS, tenant-reset or transaction-double entries).

Indexes and uniqueness¶

CREATE UNIQUE INDEX "timetables_open_revision_unique" ON "timetables"("revision_of_id") WHERE status = 'DRAFT'; — the open-revision invariant, now scoped to the only status where it means anything. Archived versions share a revision_of_id freely; Postgres treats NULLs as distinct, so standalone drafts stay unconstrained.
CREATE INDEX "timetables_revision_of_id_idx" ON "timetables"("revision_of_id"); — replaces the index the dropped unique was providing.
timetables_tenant_ay_published_unique is untouched: an archive is ARCHIVED, never PUBLISHED, so it cannot collide with the one-published-per-year rule.

5. API surface¶

Verb	Path	Decorators	Request DTO	Response DTO
n/a — no route added, removed or changed	n/a	n/a	n/a	n/a

Swagger considerations¶

ApiPublishTimetableRevision's description currently ends "…and the revision draft is discarded". It must gain one truthful sentence: the outgoing version is retained as a read-only archived copy. This is FE-facing copy, so it says what happens, not how — no mention of columns, indexes or transactions.
No new error example, no oneOf change, no DTO change.

6. RBAC seed plan¶

Seed file	Delta
`PermissionScope` (rbac-catalogue.ts)	none
`PermissionAction` (rbac-catalogue.ts)	none
`ScopeFieldMapping` (rbac-catalogue.ts)	none
Role grants (roles.ts)	none
`*_SCOPES` runtime constant	none — no new field is exposed, so `scope-fields.ts` is untouched and the RBAC drift check stays green

7. Divergence ledger¶

Pattern	We diverge by	Reason	Tradeoff accepted
`TimetableStatus.ARCHIVED` is "set ONLY by the AY-archival cascade" (schema comment, `update-timetable-status.dto.ts`)	ARCHIVED gains a second producer: republish. No new enum member.	User's call, 2026-07-26. Reusing it means zero contract change — the status union the FE already handles is unchanged — and `assertNotArchived` already supplies exactly the semantics an archived version needs: read-only, terminal, no transitions, undeletable. A new `SUPERSEDED` member would have bought a name at the cost of an FE-visible enum and a second guard everywhere.	"ARCHIVED ⇒ the academic year was archived" stops being inferable. The discriminator becomes `revisionOfId != null` (a superseded version) vs `null` (a standalone timetable the AY cascade will one day archive). The schema comment and the DTO comment must be rewritten to say so, or the next reader will trust the old invariant.
`ScheduledLesson.slotDuration` means "explicit admin override; null = derive from the curriculum cascade"	On archived rows only, it is stamped with the resolved duration, so it means "the duration this lesson had while this version was live".	Costing must not drift. Duration is derived, so a retained version re-costs differently after any curriculum edit — the exact failure the attendance manifest was built to stop. Stamping is ~free: the column exists and `resolveLessonDuration` already computes the value.	A reader of `ScheduledLesson` must know its timetable's status to interpret the column. Rejected alternative: a dedicated `frozenDurationMinutes` column — semantically cleaner but adds a column to a hot table for an archive-only concern.
Prisma 1-1 back-relation `revision Timetable?`	Becomes `revisions Timetable[]`	A published timetable genuinely has many past versions and at most one open draft; the 1-1 was an artifact of the unique index, not of the domain.	Three call sites must now filter DRAFT explicitly (`toResponse`, `timetableSummaryInclude`, `generation.service.ts:339`). All three are compile errors, so the type checker forces the review rather than letting `hasPendingChanges` quietly start counting archives.
"Hard-delete superseded content; audit log covers who-did-what; no version trail" (2026-07-15 confirmed decision)	Reversed: superseded content is retained.	The premise changed. That call was made when attendance was the only consumer; attendance then had to build its own snapshot anyway, and a budget module is now foreseen. Retaining once upstream is cheaper than N downstream snapshots.	Unbounded growth: one full content copy (a few hundred rows) per republish, per lineage, forever. No pruning API — ARCHIVED blocks `delete`. The escape hatch is deleting the lineage's published row, which cascades. Accepted as negligible at K-12 scale; a retention policy is a follow-up if it ever bites.

8. Pushback log¶

US says	Conflicts with	Proposed instead	Status
No US — this originates from the 2026-07-26 design chat, not from product.	n/a	n/a	Resolved
Opening question: "wouldn't it be better to change the republish model instead of using a new model to freeze day slots?" — i.e. replace `AttendanceDaySlot` with a versioned timetable.	Versioning the timetable fixes the schedule half of the defect only. Rosters (`SubjectGroupAssignment`, `HomeroomAssignment` — which has no validity dates at all), teacher assignments and entity names stay live, so past days would still re-render onto today's rosters. The register is a compliance document and the module already commits to deep snapshot + soft FK.	Keep both, for different jobs: the manifest is evidence of what a register was taken against; retention is the schedule's own history. This spec does only the second.	Resolved
Follow-on: date-aware resolution / validity windows on retained versions.	A publish-time window records when the record changed, not when reality changed; an admin republishing on Wednesday to fix Monday would mis-attribute two days. It would also touch seven content models, the publish flow, diagnostics, generation and the FE.	Dropped by the user, 2026-07-26: "I don't think we'll ever need date-aware resolution/validity windows". Retention only.	Resolved

9. Deferrals¶

Read and diff surface (GET /timetables/:id/revisions, the comparison itself) — the budget module owns the shape of its own comparison, including whether it diffs by lesson coordinate, by teacher load or by cost. Building a read surface now would guess at it. Follow-up: the budget-module spec.
DTO exposure of supersededAt / revisionNumber — deliberately withheld so this iteration changes no response. Follow-up: same spec, alongside the read surface.
Frozen teacher attribution and rosters — teacher assignment lives on SubjectGroup, not on the lesson, so an archived version's "who teaches this" is still live data. If budgeting costs by teacher, that is a snapshot decision of the same kind attendance made, and it must be taken explicitly rather than inherited. Follow-up: the budget-module spec; the precedent to copy is AttendanceDaySlot.
Pruning / retention policy — no API can delete an archived version (ARCHIVED blocks delete). Temporal spec A also makes a lineage immutable after it governs a day; only deleting a never-governed parent may cascade empty evidence. Follow-up: revisit if a tenant's lineage growth ever becomes visible.
Attendance rewiring — AttendanceDaySlot is untouched and frozenSource: READ remains an approximation for a day that was never written and never opened before a revision. Retention makes an accurate late freeze possible, but wiring it would re-introduce the attendance → timetables historical dependency this design just argued against. Follow-up: revisit only if READ-sourced manifests prove common in production.
Notification on republish — the TODO(notify, deferred) in publishRevision stays as it is.

10. Open questions¶

None for this iteration. The three original forks were resolved in chat on 2026-07-26: reuse ARCHIVED, retain the resolved lesson length, and expose no read surface. Temporal spec A later corrected the storage choice to resolvedDuration and added valid-time resolution.

11. Verification plan¶

Unit specs — src/timetables/timetables.service.spec.ts:
a republish inserts exactly one archive row carrying the outgoing content, with status: ARCHIVED, revisionOfId = the published id, a non-null supersededAt, and the number the published row held;
the published row keeps its id and its revisionNumber increments;
archived lessons carry the cascade freeze in resolvedDuration, without changing the authored slotDuration, including the case where the cascade would resolve differently after a curriculum change;
two successive republishes yield v1 and v2 archives and a published v3;
findOpenRevision returns null when only archived rows point at the published id (the regression the missing status: DRAFT filter would cause);
hasPendingChanges / openRevisionId count only the DRAFT revision, never the archives.
src/timetables/timetables.queries.spec.ts: stampResolvedDurations batches (assert query count, not per-lesson calls), writes only resolvedDuration, and leaves an explicit override untouched.
E2E specs — test/timetables.e2e-spec.ts:
after two republishes GET /timetables returns the same single row for the lineage it returned before — the no-consumer-change criterion, asserted rather than assumed;
GET /timetables/:id on an archived id returns it to an admin and 404s (via the policy) for a non-admin;
a second POST /:id/revision while one is open is still rejected, proving the partial unique still enforces the invariant with archives present;
test/db-constraints.e2e-spec.ts: inserting a second DRAFT with the same revision_of_id violates timetables_open_revision_unique, while a second ARCHIVED with that revision_of_id is accepted.
Manual verification: none required — the criteria above are all assertable. If desired, SELECT status, revision_number, superseded_at FROM timetables WHERE revision_of_id = '<id>' ORDER BY revision_number after two republishes shows the lineage.

Patterns: chapter 09 (testing), and feedback_e2e_isolation_patterns.md for E2E discipline — note in particular that test/timetables.e2e-spec.ts shares the seeded tenant with the attendance specs, so any timetable it leaves ARCHIVED must be cleaned up in afterAll like its other fixtures.

12. Sign-off¶

Approved by: Fabio Barbieri
Date: 2026-07-26
Chat reference: approved in chat 2026-07-26, after a walkthrough of "version the timetable instead of freezing the day" — resolved as both, for different jobs. The original storage decision was subsequently corrected by temporal spec A: reuse ARCHIVED, freeze into resolvedDuration, and keep slotDuration authored-only.

Until this section is filled, no implementation code is written. When you fill it, flip the frontmatter status: to Approved in the same edit.