Skip to content

Attendance — the day-shape snapshot, back-fill, and write concurrency

1. Problem distillation

  • AttendanceRecord freezes the student's fact; nothing freezes the day. The row snapshots ~25 columns of teaching context and survives any reorg. But every read that renders a day re-derives the day's slot set and rosters from current structure, so a past date is not reproducible.
  • The published timetable is mutable in place. publishRevision copies the draft's content onto the published row with a stable id and hard-deletes the draft (prisma/schema.prisma:2417-2424), and loadPublishedTimetableId (src/attendance/attendance.queries.ts:233) selects status: 'PUBLISHED' with no date awareness. One timetable per year, rewritten on each revision.
  • Back-fill is therefore blocked — the concrete defect this iteration is named for. Creating a cell resolves its anchor with scheduledLesson.findFirst({timetableId, anchor, weekday, startTick}). After a revision moves or deletes that lesson, a cell for a past date can no longer be created: SCHEDULED_LESSON_NOT_FOUND. Back-filling a missed register is the most common admin correction in a school, and revising a timetable mid-year is routine — so the two collide by design, not by accident.
  • Past days also re-render. The same live lookup means a February day opened in March projects onto March's slot set and March's rosters: different items[], different anchors, different toRegister, and for a DAILY department, statuses projected onto slots that did not exist that day.
  • The batch write has a read-modify-write race. The existing-cell read sits outside the transaction (src/attendance/attendance.service.ts:~315, tx opens at :330), so two simultaneous takes on the same cell both decide "create", the second hits attendance_cell_unique, and the global filter turns P2002 into a generic 409 that loses the whole batch. Two teachers on a co-taught lesson is the realistic trigger.

Success criteria (observable behaviour that proves this works): - A cell can be created for a past date whose lesson has since been moved or deleted by a timetable revision, and its snapshot names the lesson as it was on that date. - A past date's GET /attendance/rows returns the same items[] (ticks, labels, anchors, teacher names, rosters) before and after a timetable revision. - A day that has at least one authored cell always has a frozen shape, written atomically with the first cell. - Two concurrent batch writes touching the same cell both return 201; the cell holds the later value and the audit log holds two rows. - A past date with neither a frozen shape nor a live lesson fails with a specific, truthful error instead of SCHEDULED_LESSON_NOT_FOUND.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - No temporal validity on Timetable itself. Revisions keep mutating the published row in place; attendance defends itself rather than reshaping the timetable module. - No new endpoint, no change to any response DTO. The day-shape snapshot is internal — the FE contract is untouched. - No roster/homeroom history. Slot rosters get frozen; group (class) membership stays live — see §9. - No healing of existing history. A day whose shape was already lost cannot be reconstructed; this stops the drift from ship date forward. - No justification/giustificazione, no attendance-code table, no VOID status, no minimum-attendance computation — all §9.

2. Patterns survey

Analogous module/spec What we'd borrow What doesn't fit
prisma/schema.prisma AttendanceRecord (:551) The whole denormalization stance: freeze the context in columns at write time, soft-FK everything but tenant/year/student, never re-sync. AttendanceDaySlot is the same idea one level up — the cell freezes the student's fact, the slot freezes the slot's shape. The cell is written by a human action; the slot row is written by the system as a side effect of one. Provenance columns become frozenAt/frozenSource rather than recordedBy*.
src/attendance/attendance-day.queries.ts DaySlotRow (:67) + loadDaySlots (:148) The persisted row is literally DaySlotRow + a date. The in-memory type the pipeline already consumes becomes the table's column list, so no consumer downstream of loadDaySlots changes at all. DaySlotRow carries no periodLabel (that is grade-relative and computed per read in resolvePeriodLabels) and no department/grade/mode (resolved per student). Neither needs freezing — see §4.
docs/superpowers/specs/2026-07-15-timetable-revision-republish-design.md The stable-id republish is the thing that destroys the old shape; this spec is its downstream consumer. Establishes that publishedAt marks the last content swap. That spec deliberately chose in-place mutation for a stable id. We are not reopening it — we snapshot on the attendance side instead (§7).
src/attendance/attendance-day-model.ts The AuthoredCell vs ProjectedSlot type split already makes "aggregate over facts, never over the view" hard to get wrong. Extend the same discipline: the frozen shape is a fact, the live timetable is only its constructor. Pure module, no persistence. The resolver added here is a query-layer concern, not day-model logic.
src/timetables/ ScheduledLesson + the anchor XOR CHECK (scheduled_lessons_anchor_xor) The three-way nullable anchor with a DB CHECK enforcing exactly-one. AttendanceDaySlot reuses the shape verbatim so the anchor round-trips between timetable, manifest and cell without translation. Postgres treats NULLs as distinct in unique indexes, so the anchor triple cannot go straight into a unique key. Resolved with an app-computed anchorKey (§4).
src/audit-log/ AuditService.record(tx, …) Writing a durable side record inside the caller's transaction, never fail-soft — exactly the shape of "freeze the day atomically with the first cell". Audit rows are append-only per mutation; the manifest is write-once per (tenant, date) and idempotent thereafter.

3. Architecture mapping

Primitive Apply? How Justify
Tenant scope yes AttendanceDaySlot.tenantId required (Class S). RLS policy tenant_isolation_attendance_day_slots in the migration + entry in src/prisma/rls-coverage.ts + STRICTLY_TENANTED_MODELS in src/prisma/tenanted-models.ts. Tenant-bearing model — ch12 "New tenant-bearing model checklist" is mandatory.
Academic-year scope yes academicYearId required, filtered on every read alongside tenantId + date. Matches AttendanceRecord; a date alone is ambiguous across a year boundary in a multi-year tenant.
RBAC entity key none (existing ATTENDANCE) No EntityKey delta. The model is never directly exposed. It is infrastructure behind assembleGroupDays and the write path, both already gated on attendance.read / attendance.register:write.
Scopes none attendance.register unchanged. No new read or write surface.
Actions none take / manage_communications unchanged. Same.
Service base custom New AttendanceDayShapeService (src/attendance/attendance-day-shape.service.ts) — resolve + freeze. Not a CRUD service; no BaseTenantedCrudService. There is no CRUD surface. Mirrors AttendanceBoardService / AttendanceInsightsService, which are also plain injectables.
queries.ts shape named functions in the existing attendance-day.queries.ts loadFrozenDaySlots(db, {tenantId, academicYearId, date}), freezeDaySlots(tx, rows), hasFrozenDay(db, …). loadDaySlots keeps its current signature and becomes the constructor the freeze path calls. House rule: named functions only, no repository classes. Keeps the manifest queries next to the day queries they replace.
Error codes one new ATTENDANCE_DAY_SHAPE_UNAVAILABLE (422, params: { date, startTick }) — a past date with neither a frozen slot nor a live lesson at that anchor. Today this surfaces as SCHEDULED_LESSON_NOT_FOUND (404), which is a lie: the lesson is not missing, the day is unrecoverable. Distinct code so the FE can say "this day's schedule is no longer on record".
DTO conventions n/a — no DTO change The manifest is never serialized. AttendanceDayItemDto, AttendanceRegisterCellDto, AttendanceGroupCardDto and every counter are byte-identical. The whole point: correctness fix with zero FE contract movement.
File-backed sub-resources n/a — no files involved
Custom fields n/a — infrastructure model, not a person/domain entity
Profile completeness n/a — unrelated to person records

4. Data model plan

Schema deltas

  • New model AttendanceDaySlot (@@map("attendance_day_slots")) — one row per (tenant, academic year, date, slot). Columns, in three groups:
  • Identity / scope: id, tenantId (FK Restrict), academicYearId (FK Restrict), date @db.Date, startTick Int.
  • Anchor (mirrors ScheduledLesson): kind AttendanceSlotKind (new enum LESSON | ACTIVITY), nullable subjectGroupId / combinedClassId / activityId (soft FKs, no relation), plus anchorKey String @db.VarChar(60) — the app-computed "sg:<uuid>" | "cc:<uuid>" | "act:<uuid>" discriminator that makes the anchor indexable.
  • Frozen shape (the persisted DaySlotRow): durationMinutes Int, label String @db.VarChar(200), roomId String?, roomName String? @db.VarChar(200), teacherIds String[] @db.Uuid, teacherNames String[], studentIds String[] @db.Uuid.
  • Provenance: frozenAt DateTime @default(now()), frozenSource AttendanceShapeSource (new enum WRITE | READ) — which trigger captured it, so a reader can judge whether the shape predates a revision.
  • Soft FKs throughout the anchor and roster columns, matching AttendanceRecord: a subject group, activity, room or student may be hard-deleted afterwards and the frozen day must survive intact. Only tenant and academicYear are hard Restrict.
  • Not frozen, deliberately: periodLabel (grade-relative, recomputed per read by resolvePeriodLabels from the frozen tick set — same input, same answer), attendanceMode (a property of Department, already frozen once calendarStartDate passes), and department/grade context (resolved per student; a combined lesson spans several grades, so a singular gradeId on the slot would be wrong).

Migration shape

  • Additive. One CREATE TABLE, two CREATE TYPE, one unique index, two secondary indexes, one CHECK, one RLS policy. No column dropped, no column altered, no existing row touched.
  • Data backfill: none. Freezing past days from the current timetable would fabricate shapes for dates whose real shape is already gone, and would stamp them frozenAt = migration time, which reads as authoritative. The lazy read path produces the same approximation but stamps it honestly. Stated as a limitation, not papered over.
  • Hazards from the chapter 12 checklist:
  • New tenant-bearing model → RLS policy in the migration (ENABLE + FORCE + tenant_isolation_attendance_day_slots, predicate copied from the latest RLS migration) and the rls-coverage.ts class entry and the tenanted-models.ts STRICTLY_TENANTED_MODELS entry. Three drift guards fail otherwise.
  • Raw-SQL CHECK attendance_day_slots_anchor_xor → one row in the ch12 raw-SQL table + the constraint name in test/db-constraints.e2e-spec.ts.
  • New enums are additive CREATE TYPE, no value added to an existing enum, so no transaction hazard.
  • No NOT NULL added to an existing table, no rename — the two classic destructive shapes are absent.

Indexes and uniqueness

  • @@unique([tenantId, date, startTick, anchorKey], map: "attendance_day_slot_unique") — the freeze is idempotent against this key (createMany({ skipDuplicates: true })), so concurrent first-touches converge instead of racing. anchorKey exists precisely because the three nullable id columns cannot form a unique key in Postgres (NULLs compare distinct).
  • @@index([tenantId, academicYearId, date]) — the only read pattern: "the whole day".
  • @@index([tenantId, date, anchorKey]) — the write-path anchor lookup for a past date.
  • CHECK attendance_day_slots_anchor_xor: exactly one of subject_group_id, combined_class_id, activity_id is non-null, and anchor_key agrees with whichever it is.

5. API surface

Verb Path Decorators Request DTO Response DTO
— — — — —

No API surface. No route added, changed or removed; no request or response DTO altered. Two internal behaviour changes, both invisible to the contract:

  1. assembleGroupDays resolves the day's slots through AttendanceDayShapeService instead of calling loadDaySlots directly. For date < schoolToday it returns the frozen shape (materializing it first if absent); for date >= schoolToday it returns the live timetable unchanged.
  2. POST /attendance/records freezes the day inside its existing transaction, and resolves each entry's anchor against the frozen shape when the date is past.

The only externally observable deltas are the intended ones: back-fill stops 404-ing, past days stop moving, and one new error code can appear on a write.

Swagger considerations

  • ATTENDANCE_DAY_SHAPE_UNAVAILABLE joins the error table in attendance.swagger.ts for both write routes, with params: { date, startTick }.
  • Nothing else regenerates — no controller signature moves, so the OpenAPI diff is one error example.

6. RBAC seed plan

Seed file Delta
PermissionScope (rbac-catalogue.ts) none — no new scope
PermissionAction (rbac-catalogue.ts) none — no new action
ScopeFieldMapping (rbac-catalogue.ts) none — the model is never field-filtered
Role grants (roles.ts) none — no role reaches AttendanceDaySlot directly
*_SCOPES runtime constant none — no seed change, so no drift-check delta

No reseed required, in any environment.

7. Divergence ledger

Pattern We diverge by Reason Tradeoff accepted
Reads are side-effect-free A GET on a past date with no frozen shape writes the manifest The set of days whose shape matters is not knowable in advance, and the alternative is losing it. Reframed so it is principled rather than a hack: for a past date the manifest is the source of truth and the live timetable is only its constructor — the read materializes its own source, it does not mutate domain state. The write is a single idempotent createMany({skipDuplicates}) on the unique key, so concurrent reads converge and a retry is free. It never fires for date >= today, never for an already-frozen day, and never changes a frozen row.
One read pipeline, no persistence in the read path The read path now has a conditional write Same as above. Confined to AttendanceDayShapeService; assembleGroupDays calls one method and cannot tell the difference. One extra SELECT per assembly for past dates (the has-frozen check folds into the load). Today's dates are unaffected.
Cross-module side effects go through afterCommitQueue We add no cross-module hook at all — publishRevision is untouched Freezing on publish would have to run inside the timetables transaction (after commit is too late — the content is already overwritten), creating a synchronous, non-fail-soft timetables → attendance dependency and a module cycle. Ch16 flags exactly this as off-axis. Lazy freezing covers the realistic cases: any day with a register is frozen by its own first write. A day that was never written and never opened before a revision freezes to an approximate shape on its first later touch. frozenSource: READ + frozenAt make that legible rather than silent.
AttendanceRecord is the only snapshot in the module A second, system-written snapshot table The cell answers "what did a human assert about this student"; the manifest answers "what was this student scheduled for". Conflating them is what produced the bug. One more table to keep in the RLS/tenanted registries. ~40 rows per school day, ~8k rows per school-year per tenant — negligible.
§6: "a past date with neither a frozen slot nor a live lesson → ATTENDANCE_DAY_SHAPE_UNAVAILABLE" Implementation split the case in two. A manifest that has slots is a complete account of the day, so an anchor absent from it answers the ordinary 404 SCHEDULED_LESSON_NOT_FOUND / SCHEDULED_ACTIVITY_NOT_FOUND; the 422 is reserved for a past date with no shape on record at all. The spec's single rule made the new code's own copy false: it says the schedule "is no longer on record" — a lie when the manifest is sitting right there and simply does not contain that tick. It also broke the FE's uniform handling of a stale anchor, which is the same event whether the date is past or present. Neither branch consults the live timetable, so the protection the rule existed for is unchanged. A day whose shape was lost and whose weekday now derives to nothing still 422s; a day whose shape was lost but which derives to something now 404s at the old tick — indistinguishable from a wrong tick, and already covered by "does not heal history".
Batch write = one createMany + N update Per-cell upsert inside the transaction The current split decides create-vs-update from a read taken outside the transaction, which is a race by construction. N round-trips instead of 1 + N-updates, with N ≤ a class roster. The audit action label (.created vs .updated) is still derived from the in-transaction pre-read, so under an exact race the label may say created for a row that became an update; the value history is still complete and correct. Commented at the site.

8. Pushback log

US says Conflicts with Proposed instead Status
No US — this work originates from the 2026-07-26 engineering design review, not from product. n/a n/a Resolved
(Review finding, carried forward) "fix the retroactive-context problem" could be read as also freezing homeroom/group membership per date. HomeroomAssignment (prisma/schema.prisma:2154) has no from/to — fixing it properly is a temporal-history change to a model owned by another module with many other consumers. Freeze slot rosters only (which is what determines who was scheduled and therefore who is owed a cell). Group membership stays live; the cell already snapshots homeroomId/homeroomName, so historical attribution is recoverable for any day that was actually taken. Deferred as its own iteration — §9. Resolved
(Review finding) A VOID status was raised alongside back-fill. Adding an enum value forces a decision in classifyStudentDay — a day of only voided cells would return [], breaking the "never empty" contract established 2026-07-26. Out of scope. It is a real gap but an independent one, and folding it in would put a semantic change to the day model inside an infrastructure fix. §9. Resolved

9. Deferrals

  • Roster/homeroom temporal history — HomeroomAssignment has no validity dates, so a past date's board still groups students by current class. Bounded by this spec: slot rosters are frozen, and any taken cell carries its own homeroomName, so only untaken past days of transferred students are misattributed. Follow-up: its own spec; touches homerooms, students and command-center, not just attendance.
  • Freeze-on-publish hook — would close the "never touched before the revision" gap completely. Deferred to avoid a timetables → attendance module cycle (§7). Revisit if frozenSource: READ rows turn out to be common in production.
  • VOID / cancelled cell status — a mis-anchored cell is currently permanent and occupies the unique key forever. Independent semantic change; needs a classifyStudentDay decision. Follow-up: next attendance iteration.
  • Justification (giustificazione) — legally required, parent-signed, deadline-bearing. Correctly homed on AttendanceDayEvent (day-scoped, not cell-scoped). Follow-up: the comms-loop spec, which owns that table.
  • Tenant-defined attendance-code table — the fixed 7-value AttendanceStatus enum will not survive multi-jurisdiction K-12 indefinitely. Mitigated meanwhile by keeping all interpretation in pure functions (attendance-status-rules.ts, carryForward/carryBackward, classifyStudentDay), so the eventual swap stays mechanical. Follow-up: revisit when the second jurisdiction lands.
  • Minimum-attendance / monte ore computation — unblocked by this spec for PERIOD departments (cells carry periodStartTime/periodEndTime) and, with the manifest, now also derivable for DAILY. Not built here. Follow-up: its own spec.
  • Healing existing history — days whose shape was already lost before this ships cannot be reconstructed. No follow-up; documented in ch19 as a known boundary.

10. Open questions

  • Freeze on publish, or lazily? — Resolved: lazily. Publish-time freezing must run inside the timetables transaction and creates a module cycle; lazy freezing on first write covers every day that has a register, which is the set of days where the shape is load-bearing.
  • Freeze date <= today or date < today? — Resolved: the write path freezes whatever date it is writing (including today); the read path freezes only date < schoolToday. A write is the moment a human asserts something against a schedule, so that schedule is what must be preserved. A read of today needs no freeze — today's timetable is still authoritative for today.
  • Per-grade or whole-day manifest? — Resolved: whole day. loadDaySlots already loads every lesson of the weekday in one pass; freezing a subset would leave the manifest partial and force a "is this day fully frozen?" question with no cheap answer.
  • Does the manifest need periodLabel? — Resolved: no. It is grade-relative rank over the day's distinct lesson ticks; the frozen tick set is the same input, so resolvePeriodLabels yields the same answer without storing it.

None open. Ready for sign-off.

11. Verification plan

  • Unit specs
  • src/attendance/attendance-day-shape.service.spec.ts (new): future date → live, never frozen; past date with a manifest → frozen rows, live timetable not queried; past date without one → materialized from live and persisted; freeze is idempotent (second call writes nothing); today's date on the read path → live, no freeze.
  • src/attendance/attendance.service.spec.ts: a write freezes the day inside the transaction; a past-date entry resolves its anchor from the manifest when the live lesson is gone; a past date with neither raises ATTENDANCE_DAY_SHAPE_UNAVAILABLE; the upsert path emits .created vs .updated audit actions correctly.
  • src/attendance/attendance-board.service.spec.ts: existing tests must pass unchanged — the proof that no contract moved.
  • E2E specs
  • test/attendance-admin.e2e-spec.ts — the money test: take a register on the seeded Monday; move the ScheduledLesson to a different tick (simulating a revision); then assert (i) GET /attendance/rows for that Monday returns the same items, ticks and anchors as before, and (ii) POST /attendance/records succeeds for a second student at the original tick, with the snapshot naming the original lesson. Follows the file's existing self-provisioned fixture and deleteMany cleanup; new table added to afterAll.
  • test/attendance-admin.e2e-spec.ts — two concurrent POST /attendance/records for the same cell: both 201, final value is one of the two, two audit rows.
  • test/db-constraints.e2e-spec.ts — attendance_day_slot_unique, attendance_day_slots_anchor_xor and the RLS policy exist in the DB.
  • src/prisma/rls-coverage.drift.spec.ts / tenanted-models.drift.spec.ts — pass with the two new registry entries.
  • Manual verification: publish a timetable, take a DAILY register for a past school day, start a revision that deletes the lesson, publish it, then re-open that day's rows and add a cell for a student who was missed. Both must behave as if the revision had not happened.

Patterns: chapter 09 (testing), and feedback_e2e_isolation_patterns.md for E2E discipline (sis_e2e reset per run, serial --maxWorkers=1).

Documentation deliverables (part of this iteration, not a follow-up)

  • docs/19-attendance.md — new section on the day-shape snapshot: the cell/slot symmetry, the resolution rule, the freeze triggers, and the honest boundary (history is not healed). Update §3 school-day derivation and the routes/error table with the new code.
  • ATTENDANCE.md — §1 gains a sixth idea ("the day is frozen too"); §4 assembleGroupDays pseudocode routes through the resolver; §5 endpoint 7 gains the freeze step; §8 gains the error code; §9 file index gains the service, the queries and the model; re-stamp the header.
  • docs/REFERENCE.md — the attendance/ module row gains the manifest; the §6 file index gains a row for "back-fill a register / touch day-shape freezing".
  • docs/12-migrations.md — one row in the raw-SQL constraint table for attendance_day_slots_anchor_xor.
  • No FE guide: nothing in the contract breaks. The behaviour change (back-fill now works) is worth a line in the release notes, not a guide.

12. Sign-off

  • Approved by: Fabio Barbieri
  • Date: 2026-07-26
  • Chat reference: approved in chat 2026-07-26, immediately after the design review of the attendance module that produced the three findings in §1. Both flagged divergences (read-path materialization; no freeze-on-publish hook) were called out explicitly at sign-off and accepted, as were the §9 deferrals — notably roster/homeroom temporal history and VOID.

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