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.

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.