Skip to content

Grades surface iteration 2 — temporal grade ledger, cohort views, aggregates, and parametric writers

1. Problem distillation

The grades read surface is teacher/family-shaped: raw per-entry streams with zero aggregation. Admin has no useful cohort entry point, teachers cannot obtain stable scale-normalized aggregates, and referents do not receive an enrollment-driven course view.

The first draft of this iteration proposed solving only those presentation problems. It treated subjectScaleId and criteriaScaleId as snapshots, kept GradeEntry mutable and hard-deletable, and continued to use the generic audit log as the only value history. That is insufficient for the domain requirement added during review:

During the applicable retention period, an authorized reader must be able to reconstruct everything the system asserted about a grade, at any transaction-time point, even after a correction, withdrawal, scale change/deletion, academic-year closure, or teaching-structure reorganization.

Grades therefore need two explicit time axes:

  • Educational/event time — assignmentDate, the school date the assessment belongs to.
  • Transaction time — recordedAt on an immutable revision, the instant the system accepted that version.

The stable GradeEntry.id identifies the logical grade. Its immutable revisions answer “what did this grade say after revision N?” and “what did the system say as of instant T?”. A correction appends a new full-state revision; it never overwrites historical evidence. Withdrawal replaces ordinary hard deletion and is itself a revision.

This iteration also delivers the originally requested surfaces:

  • A homeroom-anchored grades view for admin/principal/department-head/curriculum-coordinator: courses intersecting the class, dated teachers, a homeroom-member-sliced average, and evaluated/total counts.
  • A richer subject-group gradebook with per-student averages, class average, histogram, and unevaluated students.
  • An enrollment-driven, course-grouped student/referent view, including zero-vote current courses and historical courses with retained grades.
  • Computed and frozen scalar and level values for every grade revision, making mixed-scale aggregation reproducible.
  • Parametric grade write authority for principals, department heads, and curriculum coordinators.

Success criteria

  • GradeEntry.id is a stable anchor. Every create, correction, administrative override, and withdrawal appends exactly one GradeEntryRevision in the same transaction as the head-pointer update and audit event.
  • Every non-legacy revision is a complete post-command snapshot: authored values, note, effective mark, scalar/level, calculation version, criterion names/values, scale definitions, teaching context, actor snapshot, reason, event time, and transaction time. A lineage backfilled with irrecoverable gaps stays explicitly LEGACY_INCOMPLETE; later commands never disguise or fabricate those missing facts.
  • Revision and revision-criterion rows reject UPDATE and DELETE at the database layer. The ordinary API exposes no grade hard-delete.
  • GET /grades/:id/history reads the revision ledger directly, works for ACTIVE and WITHDRAWN grades, does not require the active academic year, and supports a withdrawn/deleted-live-context grade.
  • An optional transaction-time asOf selects the newest revision with recordedAt <= asOf. A requested instant before revision 1 returns not found for that grade.
  • Historic grades do not depend on live evaluation-scale rows. Scale edit/deletion cannot change effectiveMark, scalar, level, histogram placement, or the meaning of a criterion value already recorded.
  • Historical academic years remain readable through academicYearId; write creation remains active-year-only. Post-year corrections/withdrawals are office-only and reasoned.
  • GET /grades/homeroom/:id returns one row per SG that has at least one member of the homeroom on contextDate, including optional courses, with teachers and counts resolved on that same date and grades selected as of asOf.
  • GET /grades/subject-group/:id returns per-student averages plus a summary with class average, histogram, and unevaluated students, calculated only from the selected immutable revisions.
  • GET /grades/student/:id / GET /grades/me return current-on-contextDate courses plus historical courses holding selected grades. Each vote carries its revision/status/provenance-safe projection, scalar, and level.
  • A department head reads and writes only within parameter departments; a curriculum coordinator only within parameter curricula; a principal tenant-wide. A non-assigned-teacher edit is an administrative override.
  • Concurrent corrections cannot silently overwrite each other: clients send expectedRevision, and a stale command returns a typed conflict.
  • The API and documentation describe this as an immutable operational grade ledger. They do not claim qualified Italian digital preservation (conservazione a norma) without a separately approved preservation-provider integration and the institution's retention classification.

Non-goals

  • No assessment title, includeInAverage, new GradeType, bulk/group insert, rubric-flow change, notifications, report-card finals, per-type weighting, publish gate, or configurable type catalog.
  • No trend/andamento computation server-side; the FE draws it from per-vote scalars.
  • No periodId query parameter; the FE derives from/to from department periods.
  • No scheduled or future-effective grade correction. Grade revisions use transaction time; assignmentDate remains the educational date.
  • No qualified preservation provider, digital signature/seal, trusted timestamp service, or automated retention purge in this iteration. Those require a separate external-integration design and institutional sign-off.
  • Aggregates remain derived projections, not official period/final evaluation records. If an aggregate becomes an official finalized document, it gets a separate immutable finalization entity.

2. Patterns survey

Analogous module/spec What we borrow What does not fit
src/attendance/, ch19 Deep context snapshots, soft structural ids, immutable historical meaning, dated roster/teacher resolution, Restrict on identities, school-clock discipline Attendance keeps current value in one mutable cell and delegates history to generic audit. Grades needs a domain revision ledger because its calculation inputs (criteria and two scales) are themselves mutable/deletable and must remain reconstructable.
Temporal program contract + structural audit spec D Explicit valid/event time vs transaction time, full before/after evidence, stable domain anchor, one event per logical command A grade correction is not a half-open membership interval. Revisions are ordered transaction-time states of one assessment fact; no validFrom/validUntil interval is introduced.
src/disciplinary-notes/ Withdrawal as a tombstone, stable anchor after withdrawal, privileged late mutation A grade must preserve every value version and calculation input; a stateful note row alone is not sufficient.
src/audit-log/, ch20 Atomic actor snapshot, tenant-wide administrative event list, request correlation Audit remains a cross-cutting projection, not the authoritative grade history. Its free-form payload and application-level append-only convention are insufficient as the sole grade ledger.
src/evaluation-scales/evaluation-scales.queries.ts valueToNumeric, scaleRange, numericToValue, percent-of-range mapping Live scale rows are used only while authoring a new revision. Reads and aggregates use the frozen revision scale snapshot.
src/disciplinary-notes/disciplinary-notes.policy.ts Principal pass-through and DEPARTMENT/CURRICULUM parametric branches Grades keeps its bespoke async builder because several context ids are soft snapshot columns.
docs/14-homerooms-subject-groups.md SGs have standalone dated rosters; homeroom/course intersection goes through SubjectGroupAssignment Fits. The new contextDate makes the date explicit instead of always using today.

This design is off-axis under ch16 §5: it introduces the first domain-owned immutable value-revision ledger and a database immutability constraint. The pattern is intentionally narrow to grades; it does not silently redefine the generic audit module.

3. Architecture mapping

Primitive Apply? How Justification
Tenant scope yes GradeEntry, GradeEntryRevision, and GradeCriterionMarkRevision carry required tenantId; every query filters it; both new tables get FORCE RLS and drift-registry coverage Immutable rows remain tenant data and must fail closed.
Academic-year scope yes Root and revisions carry academicYearId; reads accept AcademicYearQueryDto; create uses ACTIVE year; corrections target the grade's own year Historical reads cannot be limited to the active year.
RBAC entity key existing grade_entries Reused Revisions are implementation/history of the same protected domain entity.
Scopes/actions existing record Reused; principal/dept-head/coordinator gain write+action grants No new user-facing entity.
Service base custom GradesService Domain command helpers append revisions; aggregates stay in grades.aggregates.ts No CRUD-base fit.
Query convention named functions Revision selection, scale snapshot mapping, dated membership loaders, and legacy backfill helpers live in grades.queries.ts or narrowly named sibling modules Preserves module convention.
Audit yes, secondary Each command emits the existing grade_entry.* event with { gradeEntryId, revisionId, revisionNumber, operation, reason }; domain history reads revisions Audit answers cross-module “who did what”; revisions answer complete grade state.
Error codes three new GRADE_REVISION_CONFLICT, GRADE_WITHDRAWN, GRADE_LEGACY_INCOMPLETE; reuse existing grade errors Stale write, terminal/current-state refusal, and an unprovable legacy value correction are new failure modes.
DTO conventions bespoke aggregate/history DTOs All routes remain @AggregateResponse(); family history maps a safe semantic projection rather than returning raw audit DTOs Complete evidence does not imply identical disclosure to every role.

4. Temporal and data model

4.1 Stable root — GradeEntry

GradeEntry becomes the stable identity and current-head index for one logical assessment mark.

New/changed root fields:

  • state GradeEntryState — ACTIVE | WITHDRAWN, denormalized from the current revision for filtering.
  • currentRevisionId uuid @unique — the selected head revision.
  • currentRevisionNumber int — compare-and-swap token, starting at 1.
  • Existing hard FKs tenant and academicYear change from Cascade to Restrict; student remains Restrict.

The existing value/context columns and GradeCriterionMark children remain temporarily during the expand/read-cutover migration, but become deprecated backfill sources. The expand migration relaxes legacy-only NOT NULL columns so a new root needs only its stable identity/head fields; new roots do not dual-write legacy values, new application reads do not consume them, and new commands never mutate legacy criterion children. Existing rows retain their old columns verbatim. A later contract migration may drop them after production backfill has been verified; that destructive cleanup is not part of this iteration.

The root is not independently deletable once revision 1 exists: revision FKs use onDelete: Restrict, and no ordinary service route calls gradeEntry.delete.

4.2 Immutable GradeEntryRevision

One row is the complete post-command state of a grade. Proposed fields:

id, tenantId, academicYearId, gradeEntryId
revisionNumber, previousRevisionId?
operation: CREATED | CORRECTED | ADMIN_OVERRIDE | WITHDRAWN
state: ACTIVE | WITHDRAWN
assignmentDate                         // educational/event time
recordedAt                             // DB transaction timestamp (UTC)
actorUserId?, actorName, actorRole, actorProfile?
reason?                                // null only on CREATED

studentId, studentFirstName, studentLastName, studentIdentificationCode?
departmentId, departmentName
gradeId, gradeName
homeroomId?, homeroomName?
subjectGroupId, subjectGroupName
curriculumId?, curriculumName?
curriculumSubjectId, subjectName, subjectCode?
parentSubjectId?, parentSubjectName?, parentSubjectCode?
assignedTeacherIds[], assignedTeacherNames[]  // effective on assignmentDate

type, note?, finalOverride?, effectiveMark?
scalar?, level?
subjectScaleSnapshot Json
criteriaScaleSnapshot Json
calculationVersion VarChar
snapshotStatus: COMPLETE | LEGACY_INCOMPLETE

@@unique([gradeEntryId, revisionNumber]) supplies ordered identity. previousRevisionId is a soft/self reference for navigation; the pair (gradeEntryId, revisionNumber) is authoritative.

Database constraints/triggers:

  • Reject UPDATE and DELETE on grade_entry_revisions and grade_criterion_mark_revisions for the application role.
  • Require reason for every operation except CREATED.
  • Require complete scale snapshots, calculationVersion, and context on snapshotStatus = COMPLETE.
  • Require scalar and level to be both null or both non-null; scalar is within 0..100.
  • Require revision number > 0.
  • A deferred head-consistency trigger requires currentRevisionId to belong to the root, be that root's greatest revision number, match currentRevisionNumber, and have the same state. It fires at commit so insert-revision-then-advance-head remains one legal transaction.

The root's head pointer is a query optimization, not historical evidence. A command locks the root, verifies expectedRevision, inserts revision N+1, then advances {currentRevisionId, currentRevisionNumber, state} in the same transaction.

4.3 GradeCriterionMarkRevision

Criterion values belong to a revision, never to the mutable root:

id, tenantId, gradeEntryRevisionId
criterionId, criterionName, value, ordinalPosition

@@unique([gradeEntryRevisionId, criterionId]); ordinalPosition freezes authored/display order. These rows are append-only under the same DB immutability rule.

4.4 Frozen scale snapshot

Both JSON scale snapshots use one versioned shape:

{
  "v": 1,
  "id": "uuid",
  "name": "Numeric 1-10",
  "type": "NUMERIC",
  "values": [
    {
      "id": "uuid",
      "value": "6",
      "label": "6",
      "ordinalPosition": 5,
      "numericEq": 6,
      "min": null,
      "max": null,
    },
  ],
}

New revisions always store both complete snapshots after live-scale validation. Historic reads never query EvaluationScale for grade meaning.

Legacy backfill snapshots a scale when it still resolves and derives dated context only from retained authoritative intervals. COMPLETE is allowed only when every required snapshot field can be reconstructed truthfully—not merely when both scales exist. If a pre-iteration row points at missing scale data, or a required historical name/teacher/context fact cannot be proven, no implementation may fill it from today's configuration: the backfill writes snapshotStatus = LEGACY_INCOMPLETE, preserves all surviving authored/context data, and never fabricates a scale, context value, or scalar. Deployment reports the count, reasons, and ids before cutover.

A later correction/withdrawal of a legacy-incomplete root carries the surviving snapshot forward with LEGACY_INCOMPLETE. Withdrawal and corrections that do not require missing calculation/context inputs remain possible and append provenance normally. A value or assignment-date correction that cannot be validated from the frozen surviving evidence fails with GRADE_LEGACY_INCOMPLETE; it does not adopt today's scale or structure to manufacture completeness.

4.5 Calculation evidence

At revision creation, the service computes and stores:

  • effectiveMark using the frozen authored criterion values/override and scale snapshots;
  • scalar as percent-of-range, rounded to one decimal;
  • level using the v1 thresholds (LOW < 55, 55..75 MEDIUM, HIGH > 75);
  • calculationVersion = 'grades-v1'.

Future math changes add a new version; they never reinterpret old revisions in place. A history response returns the stored values. Aggregates consume stored scalars from the selected revisions.

4.6 Migration strategy — expand, backfill, cut over

This is not a zero-schema iteration.

  1. Expand migration: add enums, revision tables, nullable root head fields, indexes, RLS policies, immutability functions/triggers, and change tenant/AY FKs to Restrict. Keep legacy columns/children.
  2. Backfill: idempotently create revision 1 for every existing GradeEntry, snapshotting resolvable scales and flagging irrecoverable legacy gaps. Set each root head.
  3. Cutover: deploy code that reads/writes revisions only. A drift/invariant check requires exactly one head and at least one revision per root.
  4. Contract later: dropping legacy grade value columns/children is a separately reviewed migration after production verification; not this plan.

Before generating or editing migration SQL, ch12's uncommitted-migration and hazard checks apply. The new tenant-bearing tables require RLS, rls-coverage.ts, tenanted-models.ts where applicable, and DB-constraint coverage.

5. Command semantics

5.1 Create

POST /grades remains active-year-only. It validates the live SG, dated roster, subject/criteria definitions, and live scale cascade; freezes the context effective on assignmentDate; inserts the root and revision 1 atomically; then emits grade_entry.created pointing to the revision.

Teacher assignment and student roster authorization are evaluated on assignmentDate, not school-today. Backdating remains allowed within the active AY because a grade may be entered after the assessment; the frozen context proves which roster/teachers governed that date.

5.2 Correct

PATCH /grades/:id request adds:

expectedRevision: positive integer (required)
reason: non-blank string (required)
...existing UpdateGradeDto value fields

The command targets the grade's own academic year, not resolveActiveYear blindly. It locks the root FOR UPDATE, compares expectedRevision, loads the current immutable revision, validates changed values against that revision's frozen subject/criteria scales and criterion set, carries those calculation snapshots forward unchanged, freezes the new complete state, and appends N+1. A correction must not silently adopt whatever scale happens to be live later. Live structure is consulted only when a changed assignmentDate requires dated-context revalidation.

  • In an ACTIVE AY: assigned teachers and authorized privileged/parametric writers may correct.
  • In a non-ACTIVE AY: only admin/platform-admin/principal may correct, always with reason.
  • A withdrawn grade cannot be edited; restoring it is deferred and GRADE_WITHDRAWN is returned.
  • A legacy-incomplete grade may be corrected only when every input needed by the requested change is present; otherwise GRADE_LEGACY_INCOMPLETE is returned. Any appended revision retains the incomplete marker.
  • Changing assignmentDate re-resolves the dated context. If the historical context cannot be proven, the date change is refused rather than reusing today's structure.

Operation is ADMIN_OVERRIDE when the caller is not one of the SG teachers effective on the corrected assignment date; otherwise CORRECTED.

5.3 Withdraw — no ordinary hard delete

Replace DELETE /grades/:id with:

POST /grades/:id/withdraw
{ expectedRevision, reason }

It appends a full WITHDRAWN revision, advances the root state, and emits grade_entry.withdrawn. The grade disappears from ordinary streams and aggregates but remains addressable through the authorized history route and admin audit list.

There is no ordinary restore and no hard-delete route. Retention expiry, lawful erasure, legal hold, tenant offboarding, and preservation-provider handoff need a separate governance design; they must not be smuggled through this endpoint.

6. Temporal read semantics

6.1 Query axes

GradesRangeQueryDto gains:

  • academicYearId? — standard authorized read override; default ACTIVE year.
  • contextDate? (YYYY-MM-DD) — date for roster, course, homeroom, and teacher membership. Default school-today in the active year; required for a non-active year unless the route is grade-id anchored.
  • asOf? (ISO-8601 instant) — transaction-time view. Default now.
  • existing from? / to? continue to filter assignmentDate inclusively.

Revision selection is one deterministic rule: for each root, choose the greatest revisionNumber whose recordedAt <= asOf. Roots with no such revision do not exist in that view. A selected WITHDRAWN revision is excluded from ordinary lists/aggregates but included in explicit history.

contextDate and asOf answer different questions and must never be conflated:

  • contextDate: who was enrolled/teaching on a school date?
  • asOf: what grade revision had the system accepted by an instant?

6.2 Per-grade history

GET /grades/:id/history?asOf? returns:

{
  gradeEntryId,
  currentState,
  currentRevisionNumber,
  selected: GradeRevisionResponseDto,
  revisions: GradeRevisionResponseDto[] // newest first, optionally capped by asOf
}

currentState is the root's state now; selected.state is the state at the requested asOf. Keeping both names explicit avoids reporting a present withdrawal as if it had already happened in an earlier transaction-time view.

If asOf predates revision 1, the grade does not exist in that transaction-time view and the endpoint returns 404 GRADE_NOT_FOUND rather than exposing its present root through an empty history.

It is anchored directly by tenant + grade id and never requires the ACTIVE year or a live SG/scale. Visibility is evaluated against the student and frozen/datetime context:

  • student: own grade;
  • referent: linked student in the grade's AY;
  • teacher: actor's Teacher id is in the revision's frozen assigned-teacher ids, or dated tutor visibility applies;
  • principal/admin: tenant-wide;
  • department/curriculum roles: frozen ids intersect their parameters.

Family/student history is a domain DTO. It includes academic values, teacher/actor display name, dates, reason, and revision sequence, but omits actorUserId, raw role strings, request ids, and raw internal audit payloads. The admin audit-log API retains its existing forensic projection.

6.3 Aggregate scale rule

Per-entry scalar/level are stored revision facts. Course averages are the mean of selected ACTIVE revisions' stored scalars.

The display scale for MarkAggregateDto.value is the frozen subject-scale snapshot of the newest contributing selected revision (tie-break recordedAt, then revision id). There is no live-scale fallback and no silent exclusion merely because a scale was later deleted. LEGACY_INCOMPLETE rows with no scalar remain visible as grades but are excluded from numeric aggregates with an explicit excludedCount in the containing summary.

Aggregate response DTOs include calculationVersion: 'grades-aggregate-v1'. A future algorithm is additive/versioned; it does not replace v1 semantics for asOf reconstruction.

7. API surface

Verb Path Contract change
POST /grades Creates root + immutable revision 1; response includes revisionNumber, state, stored scalar/level/calculationVersion.
PATCH /grades/:id Requires expectedRevision + reason; appends a correction/override revision.
POST /grades/:id/withdraw New tombstone command; replaces ordinary DELETE.
DELETE /grades/:id Removed (FE-breaking).
GET /grades/:id/history Revision-ledger response; works across AY and after withdrawal; optional asOf. Roles add principal/dept-head/coordinator.
GET /grades/homeroom/:id Reworked shape; admin/principal/dept-head/coordinator; range + temporal query.
GET /grades/subject-group/:id Extended gradebook summary; roles add principal/dept-head/coordinator.
GET /grades/student/:id Reworked course-grouped shape; roles add principal/dept-head/coordinator.
GET /grades/me Same course-grouped shape for student self.
GET /grades/subject-groups Remains admin+teacher in this iteration.

7.1 Scalar/level contract

  • scalar is frozen percent-of-range 0..100, one decimal, calculated from the revision's frozen subject scale.
  • level: scalar < 55 -> LOW, 55 <= scalar <= 75 -> MEDIUM, scalar > 75 -> HIGH under grades-v1.
  • A new complete revision never has an unresolvable scale. A legacy-incomplete revision may return scalar: null, level: null, snapshotStatus: LEGACY_INCOMPLETE.

7.2 Homeroom rollup

HomeroomGradesDto = { homeroomId, homeroomName, academicYearId, contextDate, asOf, calculationVersion, courses[] }.

Each course carries { subjectGroupId, subjectGroupName, subjectName, subjectCode, isContainer, teachers[], average, evaluatedCount, totalCount, excludedCount }.

  • Members and SG rosters are effectiveOn(contextDate).
  • Courses are SGs whose dated roster intersects those members.
  • Teachers are SubjectGroupTeacher.effectiveOn(contextDate).
  • Selected grade revisions use asOf; assignmentDate is filtered by from/to.
  • Counts and averages are restricted to the homeroom-member slice.

7.3 Subject-group gradebook

Existing additions remain, with temporal metadata and legacy exclusion count:

  • students[*].average;
  • container top-level students plus unit streams;
  • summary = { classAverage, histogram, unevaluated, excludedCount }.

Histogram bins use the selected display-scale snapshot in ordinal order. Students with no aggregatable selected revision appear in unevaluated only when they have no selected grade at all; students holding only legacy-incomplete grades are counted in excludedCount and remain visible in their grade stream.

7.4 Student/referent view

StudentGradesDto = { studentId, studentName, academicYearId, contextDate, asOf, calculationVersion, courses[] }.

Courses are dated enrollments on contextDate plus historical SGs with selected grades. Each course carries enrolled, dated course teachers, aggregate, leaf grades or unit streams. Every grade response carries its immutable revision number/state and frozen academic projection. A deleted/reorganized SG uses the revision context snapshot.

8. RBAC and write authority

Seed changes from the first draft remain:

  • Add grade_entries.record write scope + action to principal, department-head, and curriculum-coordinator preset grants.
  • Mirror hand-written action expectations in expected-preset-grants.ts.
  • Existing tenants require frozen-preset reconciliation/reseed at deployment.

Create/correct/withdraw authority:

  • admin/platform-admin: tenant-wide;
  • principal: tenant-wide;
  • department-scoped writer: frozen/live SG department in ctx.parameters.departmentIds;
  • curriculum-scoped writer: frozen/live curriculum in ctx.parameters.curriculumIds;
  • assigned teacher: SG teacher effective on the grade's assignmentDate (ACTIVE AY only for corrections).

If a live SG is gone, frozen revision context supplies department/curriculum containment for parametric reads, but not for parametric writes. Only admin/platform-admin/principal may correct/withdraw an orphaned grade because current containment cannot be revalidated.

Read visibility branches OR-compose for multi-hat callers. Route admission still enumerates preset role keys; custom parametric role admission remains deferred.

9. Integrity, retention, and compliance boundary

9.1 Operational integrity

  • Revision creation, root head advancement, and audit emission share one transaction.
  • Root FOR UPDATE + expectedRevision prevents lost corrections.
  • Revision tables are immutable at the DB layer, not merely by service convention.
  • No cascade from tenant/AY/student can erase a grade root with revisions.
  • Backups, restore drills, database access controls, and monitoring remain infrastructure obligations; a ledger schema does not replace them.

9.2 Retention

“Available at any moment” means available to an authorized subject during the applicable institutional retention/legal-hold period. It does not authorize unrestricted perpetual storage.

The Italian MIM Massimario di conservazione e scarto per le istituzioni scolastiche distinguishes document classes (for example, class registers vs personal teacher registers) with different retention. The institution must classify granular assessment revisions and any future official final evaluations in its management/conservation manual. GDPR storage limitation likewise means retention must follow a defined purpose/legal obligation rather than an unbounded application default. Until that classification and a purge/archive design are approved:

  • no application hard-delete or automatic purge is implemented;
  • tenant/AY deletion is blocked by Restrict;
  • tenant offboarding must export/return data under the controller's instructions and cannot use grade cascade deletion;
  • the product makes no conservazione a norma claim.

9.3 Preservation boundary

This ledger provides reconstructability and tamper-resistant operational history, not qualified digital preservation. AgID's conservation guidance frames long-term preservation around authenticity, integrity, reliability, legibility, and retrievability; satisfying that boundary also requires organizational controls and potentially static packages, hashes, seals/signatures, trusted timestamps, and an approved preservation system/provider. That external integration is a separate design gate.

10. Divergence and pushback ledger

Prior assumption/request Revised decision Reason
“No schema changes” Rejected; add immutable revision tables and migrate/backfill Cannot preserve scale semantics, withdrawn grades, or transaction-time state with the mutable v1 row.
Stored scale ids are snapshots Rejected; store full versioned scale JSON Soft ids point to mutable/deletable definitions.
Generic audit is the value-history source Rejected; revisions are authoritative, audit is a cross-module projection Existing audit payload is partial and history requires a live grade.
DELETE /grades/:id remains Rejected; replace with reasoned withdrawal Normal correction must not destroy the stable record.
All dated hops use school-today Rejected; use explicit contextDate and assignment-date context Today cannot answer historical course/teacher/roster questions.
Live scale is aggregate display fallback Rejected; use newest contributing frozen scale Live configuration would rewrite historical display.
Mocks proposed titles/new types/notifications Still deferred Not required for temporal integrity or the stated surface.
Two concrete cohort routes instead of attendance polymorphism Retained Product decision; shapes remain distinct.

11. Deferrals

  • Qualified preservation provider/export package, trusted timestamps, seals/signatures, retention scheduler, legal holds, and lawful purge workflow.
  • Official report-card/period finals and their finalized immutable document.
  • Cards index for principal/dept-head/coordinator.
  • Custom parametric role route admission through policy metadata.
  • periodId, weighting, publish gate, configurable grade types, notifications, assessment titles, bulk insert, server-side trends.
  • Restore of a withdrawn grade. A later design must decide restore vs new logical grade.
  • Contract migration dropping deprecated v1 value/context columns and GradeCriterionMark after production backfill verification.

12. Approval decisions

  • Institutional retention classification: the institution/data controller owns classification and retention in its management/conservation policy. This iteration hard-codes no duration, implements no purge, and blocks ordinary cascade/hard deletion.
  • Preservation claim: iteration 2 is marketed as an immutable operational ledger only, not conservazione a norma, until a provider/process integration is separately designed and approved.

Production compliance copy and offboarding procedures must preserve both decisions; changing either requires a new design iteration.

13. Verification plan

Unit and service specs

  • Scale snapshot serialization/deserialization for numeric and banded scales; calculation-version dispatch; scalar/level boundaries; degenerate scale behavior.
  • Revision selection at asOf, including before-create, between revisions, after withdrawal, and equal-timestamp deterministic tie-break.
  • Create/correct/admin-override/withdraw each append one full revision and one audit event.
  • Required reason; stale expectedRevision; withdrawn edit refusal; post-year authority matrix.
  • Frozen scale deletion/edit does not change entry DTO, effective mark, scalar, level, average, or histogram.
  • History reads without active AY/live SG/live scale and maps a family-safe DTO.
  • Principal, DEPARTMENT, CURRICULUM, teacher, referent, and student visibility, including multi-hat OR composition.
  • Homeroom rollup on an explicit historical contextDate; cross-class member slice; legacy-incomplete excludedCount.

Database-constraint E2E

  • Direct UPDATE and DELETE of both revision tables fail for the app role.
  • Duplicate revision numbers fail.
  • Incomplete COMPLETE snapshots, invalid scalar range, and missing correction reason fail.
  • Tenant/AY/student deletion is restricted when grades exist.
  • FORCE RLS isolates both revision tables and revision criteria.

Grade E2E

  • Backfilled grade reads as revision 1; missing legacy scale is flagged, never fabricated.
  • Concurrent PATCH with one expectedRevision yields one success and one GRADE_REVISION_CONFLICT.
  • Withdrawal removes the grade from aggregates but history remains available.
  • Historical AY and asOf return the expected revision and dated course context.
  • Homeroom/gradebook/student shapes retain the originally requested aggregate behavior.
  • Parametric writers obey department/curriculum bounds; non-teacher correction records ADMIN_OVERRIDE.
  • Family history omits actor ids/roles/request context while preserving academic values and reason.

Manual/deployment checks

  • Review migration SQL against ch12; report legacy-incomplete count before cutover.
  • Reseed preset grants with deployment.
  • Coordinate four FE-breaking changes: reworked homeroom, reworked student/me, revisioned PATCH contract, DELETE replaced by withdraw.
  • Document explicitly that no qualified preservation claim is made.

14. Sign-off

  • Status: Approved.
  • Reopened by: Fabio, 2026-08-12, after raising temporal reconstruction and legal-compliance concerns.
  • Previous sign-off: Invalidated by this material redesign (schema, mutation semantics, history contract, and retention boundary all changed).
  • Approved by: Fabio.
  • Approval date: 2026-08-12.
  • Chat reference: “Good now go on implementing the plan”.

Implementation may proceed from this approved revision after the required spec/plan commit.