Academic Sessions - Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• CRUD for academic sessions (academic_sessions table).
• The handover of the "current session" flag between rows, made atomic within one database transaction.
• Refusal to unset the last current session (the half of the invariant the database cannot express).
• Listing with search, isActive/isCurrent filters, sort, and pagination.

Does Not Own

• Auth/identity — delegated to JwtAuthGuard and RoleGuard (imports RoleModule for RoleService).
• Any downstream module's use of academic_sessions.id as a foreign key. As of this doc, nothing else in the schema references the table, so a delete cannot yet produce a 23503 foreign-key conflict — the service's own docblock on remove() notes this is expected once classes/enrolment land.

Source of Truth

3. Module Composition

RoleModule is imported because RoleGuard is applied at the controller class level and depends on RoleService — the module's own docblock notes that a class-level guard whose module never imports its dependency compiles and unit-tests clean, then fails at InstanceLoader naming the consumer, not the missing provider.

4. File and Directory Map

apps/api/src/modules/academic-sessions/
  academic-sessions.module.ts
  academic-sessions.controller.ts
  academic-sessions.service.ts
  dto/
    academic-session.dto.ts
  __tests__/
    academic-sessions.service.integration.spec.ts

5. Data Model

5.1 Schema Source

packages/db/src/schema/school/academic-sessions.ts

5.2 Table: academic_sessions

There is no deleted_at on this table, matching the other lookup tables in the school domain: a soft delete never fires ON DELETE restrict, so a soft-deleted session would keep resolving for any record still pointing at it while disappearing from the admin's list. Retirement is is_active = false; hard deletion (DELETE /api/academic-sessions/:publicId) is permitted only when the session is not the current one, and, once other tables reference academic_sessions.id, will additionally be constrained by whatever ON DELETE behavior those foreign keys declare.

5.3 Constraints in Detail

• academic_sessions_name_unique — a unique index on lower(name), not on name directly. "2026-27" and "2026-27 " differ to a human by nothing and to a plain unique index by everything, so the service trims the name before every write and this index folds case.
• academic_sessions_one_current_idx — a partial unique index on is_current WHERE is_current. This is the "at most one" half of the current-session invariant; see Section 6 for the "at least one" half, which lives in the service instead.
• academic_sessions_dates_ordered — a CHECK constraint requiring end_date > start_date. A zero-length or inverted session is treated as a broken row, not an unusual one, because every date-range query against it would silently return nothing.
• academic_sessions_lock_pair — locked_at IS NULL must equal lock_reason IS NULL: a lock always carries a reason, and clearing one clears the other.
• academic_sessions_lock_reason_length — a lock reason, once present, is 3–1000 trimmed characters.
• academic_sessions_current_not_locked — is_current and a non-null locked_at cannot both hold: the year the office is actively working in can never be the one whose record is closed.

5.4 The academic-year lock

Locking a session (locked_at set) freezes everything the year IS, not everything written near it. assert_academic_session_unlocked (migration 0036) covers the placements and class structure — student_class_enrollments and classes — and assert_academic_session_unlocked_via (migration 0038) extends the same refusal to the academic record: student and staff attendance, exams and everything under them (classes, routine entries, assessment components, invigilators, marks submissions, marks, results, subject results, attachments), and assignments and everything under them (classes, students, submission attempts, links, and file insert/delete).

Both functions are BEFORE triggers. assert_academic_session_unlocked reads the row's own academic_session_id; assert_academic_session_unlocked_via takes a key column and a resolver query as trigger arguments and looks the session up through a parent — an exam's routine entry through exams, an assignment's submission attempt through assignment_students and assignments. On UPDATE both the old and new key are checked, so a row already inside a locked year can neither be edited nor be moved into or out of one; a resolver that finds no session for the key lets the write through, leaving a dangling foreign key to answer for itself. Either function reads academic_sessions.locked_at FOR SHARE and raises ACADEMIC_SESSION_LOCKED (SQLSTATE P0001) when it is set. AllExceptionsFilter (apps/api/src/common/filters/all-exceptions.filter.ts) matches that fixed literal down the error's cause chain and answers 409 ACADEMIC_SESSION_LOCKED: "That academic year is locked, so its records can no longer change. An administrator can unlock the year to make a correction."

A write inside a trigger already running (pg_trigger_depth() > 1) — a cascade from a pupil's erasure, or one covered trigger firing another — always passes through, so erasure is never blocked by a year it happens to touch.

What the lock deliberately does not reach: rows a background worker writes about the record rather than into it — reminder dispatch state, sweep runtime and idempotency state, notification rules, settings, change logs, history, and transfer reviews — because refusing those would fail a job rather than answer a person. exam_mark_corrections and attendance_change_log are not covered either, since each is written alongside an edit to the row it corrects, and the lock on that row already refuses the edit. assignment_files is covered for INSERT/DELETE only: a file's virus-scan verdict is UPDATEd after upload and must still be able to finish scanning a file that was uploaded just before a lock landed.

The lock and unlock routes themselves are not on this module — they are rollover routes (packages/db/src/schema/school/rollover.ts records lock reasons), which write locked_at, locked_by_user_id and lock_reason on this table.

6. Services and Responsibilities

6.1 AcademicSessionsService

The current-session handover, in both create and update

Both methods, when asked to set isCurrent: true, run the following inside one db.transaction:

1. UPDATE academic_sessions SET is_current = false ... WHERE is_current = true (excluding the row being updated, in update's case).
2. The INSERT (create) or UPDATE (update) that sets the new row's is_current = true.

The unset always happens first. Reversing the order — inserting a second is_current = true row before clearing the old one — would violate academic_sessions_one_current_idx at the write itself, regardless of which request the caller intended to win. When two requests race to make different sessions current, one succeeds and the other's UPDATE/INSERT is caught by translate() and reported as 409 ACADEMIC_SESSION_CURRENT_CONFLICT.

The two halves of the invariant

• At most one current — enforced by academic_sessions_one_current_idx. The service never separately checks for an existing current row before writing; it relies on the index and translates the violation if the race happens.
• At least one current — enforced only in update(): if dto.isCurrent === false and the existing row is the current one, the request is refused with 409 ACADEMIC_SESSION_CURRENT_REQUIRED before any write is attempted. This cannot be pushed into the database, because no CHECK or index can require that a row with a given value continues to exist — a constraint only ever restricts what CAN be written, it cannot force a write to happen.

Validation order in update

Dates are validated dto-over-existing-row, not from the DTO alone: a PATCH supplying only startDate is checked against the row's stored endDate (and vice versa). Checking only the supplied field would let a caller invert the range across two separate requests, and the resulting failure would surface as an unmapped 23514 from the CHECK constraint rather than a named ACADEMIC_SESSION_DATES_INVALID.

Delete

remove() refuses when the target is the current session (409 ACADEMIC_SESSION_IS_CURRENT), then does a hard DELETE. There is no soft delete on this table.

Error translation

AcademicSessionsService.translate() maps constraint violations to named error codes by walking the error's cause chain for the constraint name (academic_sessions_one_current_idx, academic_sessions_name_unique, academic_sessions_dates_ordered), because Drizzle's wrapped driver error does not carry a .constraint property directly on itself — the same pattern the service's own comment cites as having caused a live defect once already in PersonWriterService.translate. Everything not matched is rethrown unchanged.

7. Runtime Flows

7.1 Create, including handover

7.2 Update — making a different session current

7.3 Refused unset of the only current session

8. Caching

The key carries no per-caller scope segment: a session record has no per-person data and no permission-gated column, so every caller who can reach the route sees the same rows, and there is nothing a shared key could leak — noted directly in the service's own comment.