Timetable Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• The structure of a class's teaching week: class_timeslots — one row per day-of-week/time-range/type, admin CRUD plus a day-to-day(s) copy.
• The draft-and-publish lifecycle of a class's timetable: class_timetables headers and class_timetable_entries rows, admin create/discard draft, per-slot entry upsert/clear, publish, unpublish.
• Version history: class_timetable_archives, a jsonb snapshot of every superseded or unpublished version, and the restore path (POST .../draft?fromVersionNo=) that rebuilds a draft from one.
• Cross-class conflict detection among published entries: no teacher or room may be double-booked at an overlapping time, in the same academic session.
• Three read-only mobile "my schedule" surfaces: teacher, student, and guardian-on-behalf-of-a-child, each scoped to the caller's own rows and returning a reduced, name-only field set.

Does Not Own

• Class, grade, section, and room definitions — packages/db/src/schema/school/classes.ts and the classes/rooms modules. This module references them by id and refuses to hard-delete a class that still has timeslots or timetables (CLASS_HAS_TIMETABLE, enforced in the classes module, not here).
• Staff and teacher identity, including soft-delete — packages/db/src/schema/school/people.ts. This module never trusts staff.deleted_at at write time (no constraint can see it) and instead revalidates at publish and at read time.
• Subject-to-class assignment — class_subjects, owned by the Subjects module. This module only checks that a classSubjectId belongs to the class being edited.
• Academic session lifecycle, including which session is "current" — academic_sessions. This module reads is_current to refuse edits against a stale year but does not manage sessions itself.
• Authentication and role/permission evaluation — JwtAuthGuard, RoleGuard, RoleModule. This module declares @Permissions() codes and imports RoleModule so the guard can resolve, but does not implement guard logic.

Source of Truth

3. Module Composition

TimetableModule declares both controllers directly rather than through sub-modules, because TimetableModule is the name registered in ADMIN_MODULES for Swagger inclusion, and Swagger's include does not recurse — a module named there that declares no controllers of its own contributes nothing to the document, with no error and no warning.

The write side of the timetable builder is deliberately two services, not one: ClassTimetableDraftWriteService owns draft creation, discard, and the per-slot entry upsert/clear; ClassTimetablePublishService owns publish and unpublish. They are split because publish and unpublish run under a genuinely different locking discipline — both take class_timetables FOR UPDATE, ORDER BY id, which draft editing does not need — not because either service alone would be too large.

4. File and Directory Map

apps/api/src/modules/timetable/
  timetable.module.ts
  dto/
    timeslot.dto.ts
    timetable.dto.ts
  timeslots/
    class-timeslots.controller.ts
    class-timeslots-read.service.ts
    class-timeslots-write.service.ts
  entries/
    class-timetable.controller.ts
    class-timetable-read.service.ts
    class-timetable-draft-write.service.ts
    class-timetable-publish.service.ts
    class-timetable-conflict.util.ts
    class-timetable-draft.util.ts
    class-timetable-write.errors.ts
    timetable-snapshot.types.ts
  __tests__/
    timeslots.integration.spec.ts
    timetable-write.integration.spec.ts

apps/api/src/modules/mobile/
  shared/
    portal-schedule.dto.ts
    portal-schedule.query.ts
    portal-today.ts
    portal-today.dto.ts
    portal-today.query.ts
    portal-today.service.ts
  teacher/teacher-schedule-portal.controller.ts
  student/student-schedule-portal.controller.ts
  guardian/guardian-schedule-portal.controller.ts

packages/db/src/schema/school/timetable.ts
packages/db/src/migrations/0019_timetable_module.sql

5. Data Model

5.1 Schema Source

packages/db/src/schema/school/timetable.ts
  timeslotTypeEnum
  timetableStatusEnum
  classTimeslots
  classTimetables
  classTimetableEntries
  classTimetableArchives
  (+ relations for all four tables)

packages/db/src/migrations/0019_timetable_module.sql
  CREATE EXTENSION btree_gist
  class_subjects_id_class_unique  (added by hand, must precede the entries FK)
  three CREATE TABLE statements + one ALTER TABLE ADD CONSTRAINT UNIQUE
  five foreign keys
  eight indexes
  three EXCLUDE constraints (no drizzle builder exists for these)
  three bump_row_version triggers

5.2 Tables and Collections

class_timeslots

One slot in one class's week — the structure a timetable is laid onto.

Constraints and indexes not in the column list above:

• class_timeslots_entry_fk_target — UNIQUE(id, class_id, day_of_week, start_minute, end_minute, slot_type). Trivially true (already covered by the PK) but exists solely as the composite foreign-key target for class_timetable_entries, so an entry provably matches its slot's current time. Declared as unique(...), not uniqueIndex(...), because drizzle-kit emits all ADD CONSTRAINT ... FOREIGN KEY before all CREATE INDEX; a uniqueIndex would not exist yet when the referencing FK is created and the migration would fail on a clean database with 42830.
• class_timeslots_class_day_idx — btree(class_id, day_of_week). Serves the per-class, per-day listing and the overlap check's equality columns.
• class_timeslots_no_overlap — EXCLUDE USING gist (class_id WITH =, day_of_week WITH =, int4range(start_minute, end_minute) WITH &&), declared in the migration (no drizzle builder for EXCLUDE). No two slots of one class on one day may overlap. Requires the btree_gist extension for the = operators.

class_timetables

One version — draft or published — of one class's timetable, header only.

Constraints:

• class_timetables_class_session_fk — composite (class_id, academic_session_id) → classes(id, academic_session_id), ON DELETE restrict ON UPDATE restrict.
• class_timetables_version_unique — UNIQUE(class_id, version_no).
• class_timetables_entry_fk_target — UNIQUE(id, class_id, academic_session_id, status). This is the mechanism the whole publish design rests on: because status is part of this key and the entries' header FK is ON UPDATE cascade, promoting a header from draft to published cascades the new status onto every one of its entries in one statement — which is the exact moment the two EXCLUDE constraints on class_timetable_entries fire. It also makes writing status directly on an entry impossible, since the tuple must match a header row.
• class_timetables_one_draft_per_class — UNIQUE(class_id) WHERE status = 'draft' (partial unique index).
• class_timetables_one_published_per_class — UNIQUE(class_id) WHERE status = 'published' (partial unique index). Not deferrable — this is why publish() deletes the superseded published header before promoting the draft rather than promoting first.

class_timetable_entries

One lesson — a subject, and optionally a teacher and a room — in one slot of one timetable version.

Constraints and indexes:

• class_timetable_entries_header_fk — composite (timetable_id, class_id, academic_session_id, status) → class_timetables(id, class_id, academic_session_id, status), ON DELETE cascade ON UPDATE cascade. Deleting a header deletes its entries; promoting a header rewrites every entry's status.
• class_timetable_entries_timeslot_fk — composite (timeslot_id, class_id, day_of_week, start_minute, end_minute, slot_type) → class_timeslots(id, class_id, day_of_week, start_minute, end_minute, slot_type), ON DELETE restrict ON UPDATE cascade. Deleting an occupied slot is refused (TIMESLOT_IN_USE, SQLSTATE 23001); moving a slot's day/time/type rewrites every entry that occupies it, including published ones.
• class_timetable_entries_class_subject_fk — composite (class_subject_id, class_id) → class_subjects(id, class_id), ON DELETE restrict. Prevents placing a subject assigned to a different class.
• class_timetable_entries_slot_is_teaching — CHECK slot_type = 'period'. Fires on two distinct writes with an identical constraint name: placing a lesson in a non-period slot (mapped to TIMETABLE_SLOT_NOT_TEACHING by the entry write path's own pre-check), and converting an occupied period into a break/lunch/etc. by cascade from a timeslot edit (mapped to TIMESLOT_TYPE_CHANGE_BLOCKED by the timeslot write path).
• class_timetable_entries_notes_format — CHECK, same shape as the label check on class_timeslots.
• class_timetable_entries_one_per_slot — UNIQUE(timetable_id, timeslot_id). One lesson per slot per version; the target of putEntry's onConflictDoUpdate.
• class_timetable_entries_teacher_free — EXCLUDE USING gist (academic_session_id WITH =, teacher_id WITH =, day_of_week WITH =, int4range(start_minute, end_minute) WITH &&) WHERE (status = 'published' AND teacher_id IS NOT NULL). Only published rows are constrained — see the rationale below.
• class_timetable_entries_room_free — same shape, predicated on room_id IS NOT NULL instead.
• Plain btree indexes: class_timetable_entries_timetable_idx (timetable_id), class_timetable_entries_class_status_idx (class_id, status), class_timetable_entries_class_subject_idx (class_subject_id), class_timetable_entries_timeslot_idx (timeslot_id).
• class_timetable_entries_teacher_published_idx — partial btree on teacher_id WHERE status = 'published' AND teacher_id IS NOT NULL, serving the teacher portal's weekly read.

Why only published rows are constrained. Drafts are deliberately unconstrained: two administrators may pencil the same teacher into drafts for different classes, and the second publish is refused rather than the first edit. Constraining drafts would make a fork collide with the published parent it was copied from, making it impossible to edit a live timetable at all. Within one draft a conflict is unreachable anyway — one entry per timeslot, and one class's own timeslots cannot overlap — so every real conflict is cross-class, and those are caught atomically the moment a schedule goes live, carried down from the header by the header FK's ON UPDATE cascade.

class_timetable_archives

A superseded or unpublished timetable version, kept as a snapshot and deliberately outside the foreign-key graph.

Constraints and indexes:

• class_timetable_archives_version_unique — UNIQUE(class_id, version_no).
• class_timetable_archives_class_idx — btree(class_id, archived_at), serving GET .../timetable/versions (ordered desc(archived_at)).

No bump_row_version trigger — an archive is never updated after being written.

5.3 The Snapshot Shape

interface TimetableSnapshotEntry {
  dayOfWeek: number;
  startMinute: number;
  endMinute: number;
  slotType: string;
  label: string | null;
  timeslotId: string;       // for restore
  classSubjectId: string;   // for restore
  subjectName: string;      // for readable history
  subjectCode: string;
  teacherId: string | null;
  teacherName: string | null;
  roomId: number | null;
  roomName: string | null;
  notes: string | null;
}
interface TimetableSnapshot { entries: TimetableSnapshotEntry[]; }

Both names and ids are stored, and each half exists for a different reason. Names make an archive readable after the referenced row is renamed or removed — history should not change retroactively. Ids (timeslotId, classSubjectId) make restore implementable at all: class_timetable_entries requires both, and neither is recoverable from a subject's display name alone.

5.4 Relationship Diagram

5.5 Index Rationale

6. Services and Responsibilities

6.1 ClassTimeslotsReadService

resolveClassId and loadDto are public/exported so the write service can pass its own open transaction handle through them and read the just-written row inside the same transaction, instead of racing a second pooled connection against an uncommitted write.

6.2 ClassTimeslotsWriteService

Input normalization: label is trimmed server-side before every write (trimLabel), because the CHECK constraint rejects leading/trailing whitespace and an untrimmed paste would otherwise be a 422 the user did not cause. Validation order in create/update: class resolution, then the DTO's own class-validator rules (already run by the global pipe before the service method executes), then the database CHECK/EXCLUDE constraints via translatePgConstraint. Transaction boundary: every write method opens its own db.transaction. Concurrency: update uses optimistic concurrency — version is included in the WHERE clause, and zero affected rows triggers a re-read inside the same transaction to distinguish "row is gone" (TIMESLOT_NOT_FOUND) from "token is stale" (TIMESLOT_VERSION_CONFLICT). version/updated_at are never set by the service — a bump_row_version trigger owns both, which is also why an update whose only field is version still writes a self-assignment so the trigger fires and the token advances.

6.3 ClassTimetableReadService

loadTimetableDto is the single place that resolves "which version does the caller mean": with an explicit status, that exact version must exist or the response is an empty grid with versionNo: null and that requested status echoed back — deliberately not a 404, because an earlier design that did 404 here produced a permanent error-retry loop in the admin panel (eleven failed requests and a Save button stuck on "Saving…") for the completely ordinary case of a class with no published version yet. With no explicit status, the draft wins if one exists, else the published version, else the same empty grid, reported as status: "draft" because an empty class's next action is to start a draft.

computeStaleReferences is the one place a soft-deleted teacher, an inactive room, or an inactive class-subject assignment is surfaced on an otherwise-valid published entry — no foreign key or CHECK constraint can see deleted_at or is_active, so this check runs both on every read of the published grid and again, on the draft's entries, immediately before publish promotes them.

6.4 ClassTimetableDraftWriteService

createDraft with fromVersionNo resolves each archived entry against the current schema state (resolveArchivedEntry): a slot that no longer exists (timeslot_missing), a slot whose time/type has since moved (timeslot_moved), or a subject no longer assigned to the class (class_subject_missing) is skipped and reported in the response's skipped array, never silently dropped and never failing the whole restore. With no body at all, it forks the currently-published version — which can never skip anything, since every live entry's references are still FK-enforced — or creates an empty draft if there is no published version either.

putEntry is an upsert via onConflictDoUpdate targeting (timetable_id, timeslot_id). version is enforced only when the caller supplies it (via a setWhere clause on the conflict update), because the same endpoint both creates (no version to send) and updates (a version from the last read) an entry — a required-on-both-sides token could be satisfied by neither. A field omitted from the request body is left untouched on an existing row; only classSubjectId is unconditionally written, which is what lets a plain drag-and-drop (sending only classSubjectId) coexist with the follow-up lesson-details dialog (sending the rest) without either clobbering the other's fields. Before writing, putEntry runs findConflict as "a convenience, not the control" — a read-only courtesy check against the same predicate the EXCLUDE constraints enforce, excluding the class being edited itself (its own published rows are about to be deleted by the eventual publish, so they can never be a real clash).

6.5 ClassTimetablePublishService

publish is idempotent in the useful sense: if there is no draft but a published version already exists (a retried request after the first attempt actually succeeded), it returns that published version rather than raising TIMETABLE_DRAFT_MISSING.

Deleting the superseded published header happens before promoting the draft, in that exact order, because class_timetables_one_published_per_class is a non-deferrable unique index — promoting first would raise 23505 on it. The promotion itself runs inside a nested tx.transaction(), which drizzle implements as a SAVEPOINT under a single Postgres session: if the promotion raises 23P01 (an exclusion violation from the EXCLUDE constraints), the SAVEPOINT rolls back but the outer transaction is still usable, which lets promoteOrReportConflict run findConflict against the still-consistent (not-yet-rolled-back) database state to name exactly which entry clashed, before the outer transaction eventually rolls back and the whole publish fails.

7. Runtime Flows

7.1 Create a timeslot

7.2 Update a timeslot that is occupied

Moving a slot's day or minutes cascades onto every class_timetable_entries row occupying it via ON UPDATE cascade on the timeslot composite FK — including published rows, which is why update/remove/copy all require TimetablePublish_UPDATE in addition to their base Timetables_* permission.

7.3 Place a lesson into a draft slot (putEntry)

7.4 Publish

7.5 The three portal reads

Each controller derives its own row-scope predicate (the teacher's own staff.id; the pupil's own currently-active enrollment; a specific ward resolved and authorization-checked before any schedule row is touched) and passes it into the single shared readPublishedSchedule function, which unconditionally ANDs it with status = 'published' and the server-resolved current academic session. None of the three accepts a session id or a class id from the caller. All three return an empty entries: [] array rather than an error when there is no current academic session or nothing scheduled — a class with no published timetable, a pupil between enrollments, and a teacher with no periods are all ordinary states, not failures.

The guardian read is the one branch with two authorization steps in a fixed order: resolveOwnChildId runs and can 404 before any timetable row is read. Reversing that order — read first, return an empty week on no match — would still not leak data (an empty week from a foreign child id is indistinguishable from an own child with no timetable), but it would mean the authorization decision ran after the query rather than before it, which this codebase treats as a defect regardless of whether it happens to be currently harmless.

GET /api/mobile/student/schedule/today and GET /api/mobile/guardian/children/:id/schedule/today are served by PortalTodayService (mobile/shared/portal-today.service.ts), provided by both portal modules.

1. The controller proves identity: resolveOwnStudentId for the pupil (audience assertion, then students.user_id = actor.id with deleted_at only; a miss is 404 STUDENT_NOT_FOUND), or GuardianPortalService.resolveOwnChildId for the guardian (a non-ward is 404 STUDENT_NOT_FOUND).
2. forStudent reads the school timezone and the school-local date, and looks up portal:today: for that pupil and date through cacheGetFast.
3. On a miss, computeBase decides the calendar status in a fixed order and stops at the first match: no_session, outside_session (half-open [start_date, end_date)), not_set (no calendar row), non_operating (the row does not expect attendance), no_class (an operating day with no active enrolment for the pupil in the current session), otherwise operating with the class's published week rows filtered to today's weekday. The base is written back with cacheSetFast for 60 seconds.
4. buildPupilToday derives each slot's state at serverTime, the current and next period ids, the earliest future boundary and the teaching-period summary. Only an operating day has periods. This step runs on every request and its result is never cached.

Breaks, lunches and activities are class_timeslots rows with no timetable entry, so the day is the lesson rows plus those rows merged in week order. enrolledClassId is the row-scope layer: an active enrolment in the current session for a pupil whose deleted_at is null and record_status is active; a miss gives no_class, never a 404.

7.6 Version history — listVersions and readVersion

Both methods run their entire body inside one db.transaction(fn, { isolationLevel: "repeatable read", accessMode: "read only" }), because each reads class_timetables and class_timetable_archives together and a plain READ COMMITTED transaction takes a fresh snapshot per statement. Without REPEATABLE READ, a publish or unpublish landing between the two SELECTs can duplicate a version (once live, once archived), drop one entirely, or make an unpublish briefly show "nothing published" while the version already sits in the archive. A read-only REPEATABLE READ transaction cannot raise a serialization failure, so neither method carries a retry wrapper — precedent: apps/api/src/modules/backup/shared/backup-manifest.service.ts.

listVersions always resolves the live header first and puts it first in the returned array when present, rather than relying on archivedAt ordering to put it there: publishedAt is written from the application clock and archivedAt from the database clock (now() at transaction start), so the two can disagree under skew.

readVersion's live branch is a predicate, not a fallback — classId, status = 'published', and versionNo must all match, or the request 404s with TIMETABLE_VERSION_NOT_FOUND unchanged. It reuses the archive branch's own entry projection (selectEntryRows then buildSnapshotEntries, mapped through the same allowlist that trims timeslotId, classSubjectId, subjectCode, teacherId and roomId) so the live detail and an archived detail can never drift into two different shapes.

8. Caching

The admin routes and the three weekly schedule routes cache nothing and read PostgreSQL directly. The two schedule/today routes cache one value.

The cached value holds nothing that depends on the clock. Period states, serverTime, nextTransitionAt and the summary are recomputed per request by buildPupilToday. The read uses cacheGetFast and the write cacheSetFast (apps/api/src/common/utils/cache-race.util.ts): the shared Redis client queues commands during an outage instead of rejecting them, so an awaited read would hold the request open. cacheGetFast races the read against a 250 ms budget and returns a miss on timeout or error, and the service computes from the database. cacheSetFast does not await, so a slow or lost write costs the next reader a miss and never delays the response. The entry is written only after identity is resolved, so the pupil and guardian routes share it.

9. BullMQ, Schedulers, and Async Work

None. No queue, job, or scheduled task is declared, produced, or consumed by this module. All work — including publish's archive/delete/promote sequence — is synchronous within the request's transaction.

10. Realtime and Events

None. No Socket.IO gateway, room, or emitted event exists in this module. A change to a published timetable is visible to the portal reads only on their next request.

11. Security, Auth, and Abuse Controls

• Guards. Every controller in this module (ClassTimeslotsController, ClassTimetableController, and the three portal controllers) carries @UseGuards(JwtAuthGuard, RoleGuard).
• Permissions. The two admin controllers gate every route with @Permissions(...). RoleGuard requires every code listed, so a multi-code route (e.g. update/remove/copy on timeslots, or publish/unpublish) is refused unless the caller holds all of them. TimetablePublish_UPDATE is additionally listed in SUPERADMIN_ONLY_MODULES, meaning an admin role never receives it automatically even though the catalog's "grant everything not superadmin-only" sweep would otherwise include it.
• Portal audience assertion, not @Permissions(). The three schedule portal routes carry no @Permissions() decorator at all; they assert their audience in code (assertTeacherStaffRow, assertPortalAudience). This means each one must also be listed in NO_PERMISSION_ADMIN_HANDLERS and in test/structure/route-permissions.spec.ts — omitting either makes the route 403 for every caller including superadmin, while the unit suite stays green because integration specs call the services directly and never pass through the guard.
• Object-level vs. field-level scope. Timetables_READ is granted to no seeded role — the admin timetable views are considered sensitive enough (revealing every staff member's location at every hour, every day) that no role is trusted with them by default; a school that wants staff to browse timetables must create a role for it deliberately. Schedule_READ (self-service reads) is granted to staff, teacher, student, and guardian roles. The portal DTOs (PortalScheduleEntryDto, TeacherScheduleEntryDto) enumerate every field by hand rather than reusing ClassTeacherDto (which carries employeeCode and employmentStatus) — a teacher's identity in a portal response is a name and nothing else.
• Guest identity. Not applicable; every route in this module requires an authenticated JWT.
• Rate limits. No module-specific rate limiter; relies on the platform-wide default.
• Input normalization. label and notes are trimmed server-side before every write, to match the database CHECK constraints that reject leading/trailing whitespace.
• Sensitive data redaction. The 404-first ordering on the guardian route (§7.5) is a deliberate anti-enumeration control: a foreign child id returns 404, not 403, so the id space cannot be probed to distinguish "exists but not yours" from "does not exist" via a status-code oracle.
• Audit logs. class_timetables.published_by_user_id is the only audit trail this module writes — who published which version, preserved into the archive snapshot on the next publish or unpublish.
• Fail-closed behavior. Every conflict, stale-reference, and session-currency check runs inside the write's own transaction and raises before any partial state is committed; a 40001/40P01 deadlock is retried once and then surfaced as TIMETABLE_CONCURRENT_UPDATE rather than silently succeeding on a corrupted read.

13. Error Handling

14. Observability

15. Testing and Validation

Validation commands, from the repository root: pnpm --filter @skoolsewa/api test runs the whole suite; pnpm --filter @skoolsewa/api test:int runs the integration specs alone; pnpm --filter @skoolsewa/api test:structure asserts the route and guard fingerprint and is not collected by pnpm test, so a route change leaves it red at HEAD with nothing reporting it.

16. Mandatory Backend Deep-Dive Pack

16.1 Submodule Coverage Matrix

Every file in apps/api/src/modules/timetable/ and the three portal controller files it depends on is represented above. assertTeacherStaffRow, assertPortalAudience, and GuardianPortalService.resolveOwnChildId are imported from shared mobile-portal infrastructure that predates this module and is documented in the mobile/portal module's own docs, not duplicated here.

16.2 UML and Architecture Diagram Pack

No Redis, BullMQ, or realtime dependency exists for this module, so the deployment diagram is deliberately just API and PostgreSQL.

16.3 Code Flow Narrative — publish

1. Entry point. ClassTimetableController.publish, gated on TimetablePublish_UPDATE.
2. Command shape. { actor: AuthUser, classPublicId: string } — no body.
3. Auth/identity. actor.id becomes published_by_user_id.
4. Input normalization. None needed; the only input is the path parameter.
5. Validation order. Class resolution → header lock → draft existence → draft non-emptiness → session currency → stale-reference check.
6. Reads performed. Both headers (FOR UPDATE, ORDER BY id), the draft's full entry rows (joined), the current-session flag.
7. Business decisions. Idempotent-return branch (no draft, published exists); refusal branches (no draft at all, empty draft, wrong session, stale references).
8. Writes performed. Optional archive insert + published-header delete, then the draft-to-published UPDATE.
9. Transaction boundary. One outer transaction for the whole operation; the promotion step runs in a nested transaction (SAVEPOINT) so a constraint failure there can be investigated without losing the outer transaction.
10. Cache behavior. None — no cache exists for this module.
11. Jobs/events/notifications. None.
12. Response mapping. loadTimetableDto re-reads the now-published grid, inside the same (committing) transaction.
13. Error handling. translateTimetableWriteError catches any constraint violation not already handled explicitly; a 23P01 is intercepted earlier by promoteOrReportConflict to attach details.conflictsWith.
14. Logs/metrics/audit. published_by_user_id is the only audit artifact; no explicit log line is emitted.
15. Known tradeoffs. Publish takes a full-table-row lock on both headers rather than a narrower lock, trading a small amount of contention for the guarantee that createDraft and publish can never deadlock against each other (same lock order).

16.4 Data Layer Deep Dive

Covered exhaustively in §5 above for all four tables: full field tables, nullability, index rationale, constraint rationale, FK delete/update behavior, soft-delete interaction (none of these tables soft-delete; §6.3/§7 explain how the module compensates for referenced tables that do), versioning fields, audit fields (published_by_user_id), timezone interpretation (timestamptz for wall-clock instants, smallint minutes for wall-clock times), the jsonb snapshot schema, and migration-ordering history (the two hand-added statements in 0019_timetable_module.sql).

16.5 Business Logic and Invariant Catalog

16.6 Tradeoffs, Alternatives, and ADR Notes

16.7 Operational Runbook

16.8 Backend Risk Register

17. Zero-Omission Backend Checklist

• Every file in the module directory is represented or explicitly marked non-runtime.
• Every controller, service, provider, processor, scheduler, helper, mapper, DTO, enum, and schema is documented.
• Every method with business behavior has a code-flow narrative (publish in full; others summarized in §6/§7 tables).
• Every table/collection/cache object/job payload has field-level detail.
• Every index, constraint, relation, and delete behavior has rationale.
• Every lifecycle/status transition has a state diagram and transition table.
• Every read/write/action/job flow has sequence and activity diagrams.
• Every business invariant is cataloged.
• Every cache key, invalidation path, queue job, realtime event, and external call is documented (all absent, and stated as such).
• Every architectural tradeoff is documented with alternatives and revisit triggers.
• Every operational failure mode has a runbook entry.

18. Backend Completion Checklist

• Module boundaries are documented.
• Every controller, service, DTO, schema file, job, cache key, and event is covered.
• Every database table/collection has a field table and relationship diagram.
• Every runtime flow has a diagram and branch notes.
• API, feature/flows, and TDD docs are linked below.
• No claim is made without a source file or documented source reference.

See Also

• API doc: /docs/developer/timetable/api
• Features and flows doc: /docs/developer/timetable/feature