School Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• Departments — a flat, school-editable list used to group designations and staff. CRUD via DepartmentsController (apps/api/src/modules/lookups/lookups.controller.ts:41-89).
• Designations — a school-editable employment title, scoped to a department, carrying the isTeaching flag that the Teachers screen filters on. CRUD via DesignationsController (same file, lines 93-152).
• Person classifications — the two national reference lists a person is recorded against: caste/ethnic group (ethnicities) and mother tongue (motherTongues). CRUD via PersonClassificationsController, also declared in lookups.controller.ts (lines 189-316), backed by its own PersonClassificationsService rather than LookupsService — see 5.2 for why these are seeded national data rather than a school's own vocabulary, and 6.3 for why they are a separate service despite the tables being structurally identical to departments/designations.
• The school profile singleton — one row of typed institutional facts (name, address, branding colors, currency, timezone, academic year start month) via SchoolProfileController (apps/api/src/modules/school-profile/school-profile.controller.ts).
• Name-uniqueness enforcement for departments (global), designations (per department), and both classification lists (global, per list), all case-insensitive.
• Delete-time referential checks that produce a mapped error instead of an unmapped foreign-key failure — including the classification lists' delete guard against users.ethnicity_id/users.mother_tongue_id.
• An hour-long Redis cache, populated on read and cleared on write, for the school profile row, the department and designation lists, and both classification lists. Every list carries its full query (search term, active filter, sort, page, size) folded into its cache key, so no cached page can ever answer for a different filter combination than the one that produced it — see Caching.

Does Not Own

• Roles and permissions. A designation is not a role — see 5.2 designations. RoleGuard/RoleService (apps/api/src/common/authorization/role.guard.ts) own what an actor may do; this module only owns what an actor is employed as.
• Staff records. packages/db/src/schema/school/people.ts owns the staff table, which references departments/designations by id but is created, updated, and soft-deleted by the people module.
• The Teachers screen's list endpoint. There is no Teachers controller or table — a teacher is a staff row whose designation.isTeaching is true (packages/db/src/schema/school/lookups.ts:87-88). This module only owns the isTeaching flag itself.
• The users table and the ethnicity_id/mother_tongue_id columns on it. packages/db/src/schema/identity.ts owns users, including those two foreign keys; this module owns only the two tables they point at, and reads users exactly once per delete request, to check whether anybody still points at the row being deleted.
• Authentication. JwtAuthGuard (apps/api/src/modules/auth/guards/jwt-auth.guard.ts) is imported, not implemented, by both controllers.
• Redis connectivity and the generic fail-soft cache primitives (getSoft/setSoft/delSoft/delPatternSoft) — owned by RedisCacheService.
• CSV/bulk import of departments/designations. apps/api/src/common/types/error-codes.ts:228-229 defines IMPORT_UNKNOWN_DEPARTMENT / IMPORT_UNKNOWN_DESIGNATION for the data-transfer module, which resolves names against these tables but is not part of this module.

Source of Truth

3. Module Composition

Both are registered directly on AppModule (apps/api/src/app.module.ts:47-48,176-177) — there is no SchoolModule aggregate wrapping them, unlike modules that group several leaf controllers under one parent. Each also imports RoleModule for RoleGuard's dependency on RoleService; a class-level guard whose module does not import that dependency compiles and unit-tests clean, then dies at Nest's InstanceLoader at boot, naming the consumer rather than the missing provider (lookups.module.ts:8-13, school-profile.module.ts:6-7).

PersonClassificationsService lives beside LookupsService in the same module and the same controller file rather than in its own submodule — lookups.controller.ts declares all three controller classes, and lookups.module.ts wires all three providers/exports together — but it is a deliberately separate class from LookupsService, not a set of methods added to it; see 6.3 for why.

LookupsService and PersonClassificationsService are both exported and can be injected by other modules that need to resolve a department/designation/classification id (e.g. the people module, when validating a staff record's department_id/designation_id, or a person's ethnicity_id/mother_tongue_id, against these tables) — no other module currently imports either (verified: LookupsModule and SchoolProfileModule each appear once in app.module.ts, and neither service is imported anywhere outside its own controller).

4. File and Directory Map

apps/api/src/modules/lookups/
  lookups.module.ts
  lookups.controller.ts       # DepartmentsController + DesignationsController + PersonClassificationsController
  lookups.service.ts          # LookupsService — departments and designations
  person-classifications.service.ts  # PersonClassificationsService — ethnicities and mother tongues
  person-classifications.service.spec.ts  # real-Postgres coverage
  dto/
    index.ts
    department.dto.ts         # DepartmentDto, Create/UpdateDepartmentDto,
                               # ListDepartmentsQueryDto, ListDesignationsQueryDto
    designation.dto.ts        # DesignationDto, Create/UpdateDesignationDto
    person-classification.dto.ts  # PersonClassificationDto, ListPersonClassificationsQueryDto,
                                   # Create/UpdatePersonClassificationDto

apps/api/src/modules/school-profile/
  school-profile.module.ts
  school-profile.controller.ts
  school-profile.service.ts
  school-profile.service.spec.ts  # real-Postgres coverage
  dto/
    index.ts
    school-profile.dto.ts     # SchoolProfileDto, UpdateSchoolProfileDto

packages/db/src/schema/school/
  lookups.ts                  # departments, designations, their relations
  school-profile.ts           # schoolProfile
  people.ts                   # staff — consumes departments/designations

packages/db/src/schema/
  identity.ts                 # ethnicities, motherTongues, users (owns ethnicity_id/mother_tongue_id)

packages/db/src/seed/
  seed-reference-data.ts      # the 56 ethnicities and 42 mother tongues, called from seed.ts and seed-prod.ts

5. Data Model

5.1 Schema Source

packages/db/src/schema/school/
  lookups.ts          # departments, designations
  school-profile.ts   # schoolProfile
  people.ts           # staff (department_id / designation_id columns + composite FK)

packages/db/src/schema/
  identity.ts          # ethnicities, motherTongues, users (ethnicity_id / mother_tongue_id columns)

5.2 Tables and Collections

departments

departments carries no deleted_at. Both lookup tables are deliberately soft-delete-free: a soft-deleted row never fires ON DELETE restrict, so a soft-deleted department would keep resolving for every staff row still pointing at it while disappearing from the admin's pick list — gone to the eye, present to the query. Retirement is is_active = false, which leaves every existing reference intact and removes the row from active-only pick lists; hard deletion is permitted only when nothing references the row, enforced by ON DELETE restrict on designations.department_id and checked in the service before staff (packages/db/src/schema/school/lookups.ts:16-34).

designations

Constraint detail — designations_department_id_id_unique (packages/db/src/schema/school/lookups.ts:100-119): this is a table UNIQUE constraint, not a uniqueIndex, and the distinction is load-bearing. It exists solely so staff can carry a composite foreign key onto (department_id, designation_id). drizzle-kit emits every ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY before every CREATE INDEX in a generated migration, so if this were a uniqueIndex instead, the referencing foreign key on staff would be added before its target index exists, and migration 0002 would fail on a clean database with SQLSTATE 42830 ("there is no unique constraint matching given keys"). A table constraint is emitted inline inside CREATE TABLE, so it is always in place first. This was verified by running the generated migration against an empty database — the index form passed a hand-ordered probe but failed the real migration, which is the gap between probing a DDL copy and probing what actually ships.

ethnicities and mother_tongues

Declared in packages/db/src/schema/identity.ts, not under schema/school/ — they sit beside users, the table whose ethnicity_id/mother_tongue_id columns reference them, rather than beside departments/designations, which they otherwise structurally resemble.

Neither table carries deleted_at, for the same reason departments/designations do not: a soft delete never fires ON DELETE set null, so a soft-deleted classification row would keep resolving for every person still pointing at it while vanishing from the admin's lookup screen — gone to the eye, present to the query. Retirement is is_active = false; hard deletion is permitted only once nothing references the row, checked in PersonClassificationsService.deletePersonClassification by querying users for the matching column before the DELETE runs (person-classifications.service.ts:231-255).

The two tables are structurally identical to each other — same columns, same constraint shapes — and that sameness is deliberate risk, not an oversight: two near-identical services would type-check even if one wrote an ethnicity row into mother_tongues, because Drizzle's inferred row types are indistinguishable. PersonClassificationsService.classificationTable(kind) is the single private method that resolves a PersonClassificationKind ("ethnicities" or "mother-tongues") to its table, so there is exactly one place that decision can be got wrong — see 6.3.

Both tables start empty from the migration and are populated by seedReferenceData() (packages/db/src/seed/seed-reference-data.ts), called from both seed.ts and seed-prod.ts: 56 ethnicities following the Central Bureau of Statistics' caste/ethnicity classification used in the national census, and 42 mother tongues ordered roughly by national speaker count, each list ending in "Other". The insert is onConflictDoNothing() against the lower(name) unique index, so re-running the seed against a live database is a no-op for every name already present, and it deliberately does not reactivate a row a school has since retired — is_active = false is a decision the school made on its own lookup screen, and a deployment's seed run must not quietly undo it.

staff — the columns this module's tables feed

Two constraints on staff close the gap a single nullable pair leaves open (packages/db/src/schema/school/people.ts:326-341):

• staff_designation_in_department_fk — a composite foreign key (department_id, designation_id) → designations(department_id, id). This is what stops "Librarian" (a Library designation) being filed under the Accounts department on the same staff row: Postgres rejects any pair that is not a real (department, designation) combination in designations.
• MATCH SIMPLE hole, and the CHECK that closes it. Postgres composite foreign keys default to MATCH SIMPLE, which skips the check entirely when any column in the pair is NULL. That is wanted for the legitimate "department chosen, designation not yet chosen" state — but it also accepts (NULL, 999999), i.e. a designation_id set with no department_id at all, which the composite key does not validate because the FK is skipped, and the plain staff.department_id FK does not cover a designation_id-only value either. A probe insert of (NULL, <a real designation id>) against a table whose only designation belonged to a different department was accepted by the composite FK alone. The staff_designation_needs_department CHECK closes that direction: designation_id IS NULL OR department_id IS NOT NULL. Together, the composite FK enforces "if both are set, they must agree," and the CHECK enforces "you may not set the designation without the department."

5.3 Relationship Diagram

school_profile has no foreign-key relationship to any other table — it is deliberately a free-standing singleton, not shown joined above. users is owned by packages/db/src/schema/identity.ts, not this module; it is shown only for the two foreign keys this module's tables are the target of.

school_profile

Row 1 is inserted by migration 0002_school_domain_foundation.sql (INSERT INTO "school_profile" ("id", "name") VALUES (1, '') ON CONFLICT ("id") DO NOTHING;, line 309) — not by a seed script. A singleton with CHECK (id = 1) is expected to exist from the moment the table does, because both the settings screen and code that reads timezone for year numbering assume row 1 is present; SchoolProfileService.get() throws SCHOOL_PROFILE_NOT_INITIALIZED if it is ever missing, and that path is documented as "should be unreachable... if this fires, the database was built by something other than the migrations" (school-profile.service.ts:41-49).

Index rationale:

6. Services and Responsibilities

6.1 LookupsService

Input normalization. Every string field is .trim()-ed before it is compared or written (name, code, description); an update that sends an empty string for code/description is normalized to null via dto.code.trim() || null rather than stored as "".

Validation order. Existence is always checked before uniqueness, and uniqueness before the write — createDesignation looks up the parent department (404 if missing) before it checks the name (409 if taken) before it inserts. updateDepartment/updateDesignation only re-check the name when the caller actually changed it case-insensitively (dto.name.trim().toLowerCase() !== existing.name.toLowerCase()), so re-submitting the same name with different casing is accepted as a no-op rename rather than rejected as a clash against itself.

Transactions. None of these methods wraps its read-then-write in a database transaction. The name-uniqueness check is a plain SELECT followed by a separate INSERT/UPDATE; see the concurrency note in 16.5.

departmentId is immutable on a designation by omission, not by a runtime check. UpdateDesignationDto simply does not declare a departmentId field (apps/api/src/modules/lookups/dto/designation.dto.ts:47-64). Because the app's global ValidationPipe runs with whitelist: true, forbidNonWhitelisted: true (apps/api/src/main.ts:64-69), a PATCH body that includes departmentId is rejected with a standard 400 validation error ("property departmentId should not exist") before LookupsService.updateDesignation ever runs. The error registry does declare a dedicated DESIGNATION_DEPARTMENT_IMMUTABLE code (error-codes.ts:246-249) for this rule, but no code path in this service throws it — verified by reading the full service file. Treat it as a reserved code for a future explicit check, not as a code you will currently see on the wire.

The designation flagged is_principal cannot be retired or deleted. Two guards in this service exist solely for that one row: updateDesignation refuses isActive: false on it with PRINCIPAL_DESIGNATION_RETIRE_FORBIDDEN (checked before any held-by-staff consideration — an unheld Principal designation is the common case the moment a school's principal leaves, not an edge case), and deleteDesignation refuses any delete on it with PRINCIPAL_DESIGNATION_DELETE_FORBIDDEN, checked before the staff-reference existence check. Both guards exist because PrincipalInvariantService.assertSinglePrincipal locks this exact row (FOR UPDATE) to serialise every write that could create a second principal, and PrincipalInvariantService.resolvePrincipal/the school profile's principal field resolve the current principal by joining to it — retiring or deleting the flagged row would silently strand both mechanisms with no designation left to appoint a successor to, while a retired-but-referenced row would keep resolving for whoever already holds it. See apps/api/src/modules/people/shared/principal-invariant.service.ts for the full invariant and its five verified write sites.

Response mapping. Both list methods select exactly the columns the corresponding DTO needs (including the correlated-subquery counts) rather than SELECT *, so there is no server-internal field to strip before the response leaves the service.

Caching, precisely. findAllDepartments/findAllDesignations are cache-aside: CacheKeyUtil.build folds the entire query — search term, active filter, sort column, order, pagination flag, page, and size — into the key, under DEPARTMENTS_CACHE_PREFIX:list:/DESIGNATIONS_CACHE_PREFIX:list:. A hit returns the cached { data, totalCount } pair with no database round trip at all, including no COUNT(*). Neither list carries a per-department or per-caller scope segment, unlike the people lists — every department/designation row is visible to every caller who can reach the route, so there is nothing a shared key could leak. invalidate() clears both prefixes on every department or designation write, never just the one that changed: a designation's list row carries its department's name, so a department rename that cleared only the department prefix would leave the designation list showing the old name for up to an hour on the very staff form where the two are chosen together.

Fail-open vs fail-closed. Every failure mode here is fail-closed (a thrown, coded exception) except cache reads/writes/invalidation, which are fail-soft by design — see 8. Caching.

Logger usage. LookupsService does not log directly; a failed cache operation is logged by RedisCacheService itself (WARN, see redis.service.ts:247-266), and any exception thrown from the service is logged by AllExceptionsFilter at the HTTP boundary (all-exceptions.filter.ts:50-63).

6.2 SchoolProfileService

Code flow — get(): delegates the cacheable part to getBase() (cache-aside, read-through: getSoft is tried first; a hit returns the cached base fields with no database round trip; a miss selects row 1 by the hardcoded SINGLETON_ID = 1, throws if absent, maps it through toDto, writes it back with setSoft at a 3600-second TTL), then always resolves principal fresh by calling PrincipalInvariantService.resolvePrincipal — never cached alongside the rest. The principal is derived from staff/designations/users, none of which this module hears about when they change (a staff member appointed, removed, or set inactive), so caching it for up to an hour would risk the profile naming a principal who has already left, and invalidating this key from every staff write would couple two otherwise-independent modules for the sake of one field. The tradeoff is one extra indexed query on every profile read; getTimezone() deliberately calls getBase() instead of get() so the admission/employee-numbering hot path never pays it. There is no lock or single-flight guard around a getBase() cache miss, so a burst of concurrent requests arriving after a PATCH (which just cleared the cache) can each independently miss and each independently run the same SELECT and the same setSoft — harmless here because the read is idempotent and cheap, unlike a write-amplifying miss.

Code flow — update(): no pre-read of the row, but the DTO is passed through toPatch(dto) before it ever reaches .set() — it is not spread directly. toPatch walks every key class-transformer populated on the DTO (skipping any key that is undefined, i.e. genuinely omitted from the request body) and, for a string value, trims it and then applies one of two rules depending on the field:

• For the plain nullable text columns (logoUrl, registrationNumber, principalName, phone, email, website, brandPrimaryColor, brandSecondaryColor) a trimmed-to-empty string is written as null, not "".
• The address field is handled separately and first, before any per-field blanking rule runs: it is a group, replaced whole in the same way a person's permanentAddress/currentAddress is (see the people docs). When address is present on the request, all six columns (provinceId, districtId, municipalityId, wardNo, tole, houseNo) are written from it — a missing key inside the group means NULL — and tole/houseNo are individually blanked-to-null. Field-wise merging is deliberately not supported: { "address": { "provinceId": null } } clears the whole group rather than leaving districtId behind pointing at an now-absent province, which would otherwise raise the unmapped 23514 school_district_needs_province.
• For name, currencyCode, and timezone — the three text columns that are NOT NULL with a database default — a trimmed-to-empty string is dropped from the patch entirely, so the column keeps its current value. A non-string field (academicYearStartMonth) is copied through unchanged, with no trimming.

The reason the three NOT NULL columns are excluded from blanking rather than also clearing to null: class-validator's @IsOptional() treats null as "absent," so a client with a "clear the logo" button has only the empty string available to send, and a form that posts every field back (not just the ones the office actually edited) would otherwise blank timezone to "" the moment any other field on the same form was legitimately cleared. timezone in particular is read by getTimezone() to decide which year an admission/employee number belongs to — a blank there is not a cosmetic bug, it is admission numbers filed under no discoverable year at all. The UPDATE ... SET ... RETURNING then applies exactly the patch toPatch produced, plus a fresh updatedAt — a field the caller never sent, and a field the caller sent as a blank on a NOT NULL column, are indistinguishable in the final SET clause: both are simply absent from it. The cache is cleared after the write commits, so a request that reads the profile in the narrow window between the UPDATE committing and the delSoft running could still see the stale cached value — the TTL bounds how long that window can ever last (at most the remaining seconds on the 3600s TTL), and the very next read after invalidation is guaranteed fresh.

Why no cache-population lock/transaction: this table is read far more than it is written (every screen that shows the school's name/logo/timezone), and stale-for-a-request-or-two after an admin edit is an acceptable tradeoff against the cost of a distributed lock around a once-a-day write.

6.3 PersonClassificationsService

Why a separate service, not more methods on LookupsService. ethnicities and mother_tongues have exactly the same shape as departments/designations — id, public id, name, active flag, case-insensitive unique name index — and it would be a small change to add two more entities to LookupsService. The service's own doc comment gives the reason not to: departments and designations are the school's own structure, invented by an administrator and safe to rename on a whim; ethnicities and mother tongues are the national classification every government return (IEMIS, scholarship and free-textbook allocations, reservation quotas) is aggregated against, arrive by seed rather than by an operator typing them, and a rename here retroactively changes what an already-filed return meant. The two sets have different write rules, different cache lifetimes, and different consequences for being wrong; one service holding both would invite a helper written for a department — a bulk rename, a lenient delete — to be reused on a table where that is dangerous.

One service for both tables anyway. Unlike the department/designation split, ethnicities and mother_tongues share one class, because they are read together by the same two forms at the same moment (a person's demographic section always renders both selects) and differ only in which table the rows come from. classificationTable(kind) is the single private method that resolves a PersonClassificationKind ("ethnicities" or "mother-tongues") to its Drizzle table object — every other method calls through it rather than repeating the ternary, so there is exactly one place a kind could be mapped to the wrong table, and it is covered by a dedicated test (see 15).

Input normalization. CreatePersonClassificationDto.name/UpdatePersonClassificationDto.name are trimmed by a @Transform decorator that runs before @IsNotEmpty() validates them — not after, and the ordering is load-bearing: without it, a name of all spaces passes @IsNotEmpty() (it is a non-empty string), and the service would then trim it to "" and insert a blank row that renders as an unlabelled option nobody can identify in the pick list. Trimming first makes the emptiness visible to the validator, so the caller gets a 400 naming the field instead of a row that has to be found and deleted by hand.

Sort order deliberately differs from every other list in this module. ListPersonClassificationsQueryDto overrides the inherited QueryDto.order field, changing its default from "desc" to "asc" via a field initializer (not a declare, which would leave the parent's default running underneath an annotation that claimed otherwise — field initializers run parent-first, so the child's wins, and class-transformer still overwrites it when a caller sends an explicit ?order=desc). Every other list in this codebase defaults to newest-first, which is right for a list of records; these two are picked out of a select box by someone scanning for a word, and a list running Z-to-A by default is one nobody can find anything in. The list is also always sorted by name first, never by updated_at — a pick list that reorders itself because an administrator corrected a spelling is a list nobody can scan reliably — with id breaking ties for stable pagination.

Hard delete, honestly guarded. ethnicity_id/mother_tongue_id on users are both ON DELETE set null, so deleting a referenced row would otherwise succeed and silently blank the recorded classification of every person pointing at it — a data loss with no error and no way to reconstruct who held which value. deletePersonClassification closes that by reading users for a matching row before the DELETE runs, addressed against the correct column per kind (users.ethnicityId for "ethnicities", users.motherTongueId for "mother-tongues" — the two columns are interchangeable to the type system, which is why this is covered by its own test rather than trusted by inspection). Neither table carries deleted_at, for the same reason departments/designations do not — see 5.2.

Validation order. Existence is checked before uniqueness, and uniqueness before the write, matching LookupsService's pattern exactly. updatePersonClassification only re-checks the name when it actually changed case-insensitively, so a rename that only fixes casing is accepted as a no-op rather than rejected as a clash against itself.

Caching, precisely. Same mechanism as LookupsService's lists: CacheKeyUtil.build folds the search term, active filter, order, pagination flag, page, and size into the key under CACHE_PREFIX:kind:, so a write to one list clears only that list's prefix — an ethnicity rename cannot invalidate the mother-tongue cache, because nothing about a rename in one list can change what the other list would return.

Transactions. None of these methods wraps its read-then-write in a database transaction, same TOCTOU caveat as LookupsService — see 16.5.

Fail-open vs fail-closed. Every domain failure here is fail-closed; only the cache is fail-soft.

Logger usage. PersonClassificationsService does not log directly, for the same reasons as LookupsService.

7. Runtime Flows

7.1 List departments (with search/filter/pagination)

7.2 Create a department

7.3 Create a designation

7.4 Delete a department / a designation

The designation delete-guard query (staff.designationId = existing.id) is not filtered on staff.deletedAt IS NULL — a soft-deleted staff member who still carries the designation blocks the delete just as an active one would. See 16.5 for why that is the safer default.

7.5 Read the school profile (cache hit and cache miss)

7.6 Update the school profile

Every field on UpdateSchoolProfileDto is optional and independently applied — a PATCH sending only {"phone": "..."} leaves every other column untouched, because a key genuinely absent from the body never survives toPatch into the SET clause. But an unsupplied key and a supplied blank are not always treated alike: on the twelve nullable text columns (logoUrl, registrationNumber, principalName, address, city, state, pinCode, phone, email, website, brandPrimaryColor, brandSecondaryColor) a trimmed-to-empty string is written as null — the one way a client can ever clear one of these fields back to "not set," since class-validator's @IsOptional() treats an actual null in the request body as absent, leaving the empty string as the only signal a "clear" button can send. On name, currencyCode, and timezone — the three NOT NULL columns with a database default — a blank is instead dropped from the patch and the existing value survives untouched, specifically because a blank timezone would silently break admission/employee-number allocation, which resolves the academic year through it (see 16.5).

7.7 List person classifications (ethnicities or mother tongues)

7.8 Create a person classification

7.9 Rename or retire a person classification

Same shape as 7.8, routed through updatePersonClassification: findClassificationOrThrow (404 on a missing publicId), a name pre-check only when the trimmed name actually differs case-insensitively from the current one, then an UPDATE applying only the fields present in the DTO plus a fresh updatedAt, then invalidateClassifications(kind).

7.10 Delete a person classification

The reference check is what makes the refusal honest: users.ethnicity_id/users.mother_tongue_id are both ON DELETE set null, so without this check the DELETE would succeed at the database and silently blank the recorded classification of every person who held it, with no error and no way to reconstruct who had which value. The check queries the column matching kind — users.ethnicityId for "ethnicities", users.motherTongueId for "mother-tongues" — never the other one, which is exactly the mistake classificationTable-style single-decision-point design and the dedicated test in 15 exist to catch.

8. Caching

Verified, not assumed: lookups.service.ts's findAllDepartments/findAllDesignations both call this.cache.getSoft(...) before touching the database and this.cache.setSoft(...) after a miss; invalidate() calls delPatternSoft on both prefixes unconditionally on every write. person-classifications.service.ts follows the identical pattern under its own prefix, scoped further by kind. Every list cache key is built by CacheKeyUtil.build, which folds the entire query — not just the filter fields, but pagination, page, and size too — into the key, so two callers requesting different pages or page sizes of the same filtered list never collide and never serve each other's page.

Deterministic key composition, one rule for every list here and for LookupsService's own README-level comment: the key is a PREFIX, not a key. A single cache key per table would serve the first caller's filtered page to every later caller regardless of what they actually asked for. CacheKeyUtil.build orders segments deterministically and substitutes a sentinel for a null/undefined/empty filter, so a missing filter can never collide with a present-but-empty one.

Why both lookup prefixes clear on every write, department or designation. A designation's list row is denormalized with its department's name (departmentName). A department rename that cleared only the department prefix would leave the designation list — the exact screen a staff form pulls both selects from together — showing the stale department name for up to an hour.

Why classification prefixes clear independently, unlike lookups. An ethnicity rename cannot change anything a mother-tongue list would return, and vice versa; clearing both on every write would be strictly wasted invalidation with no correctness benefit, unlike the department/designation case above where the two lists are genuinely coupled through the denormalized name.

SchoolProfileService remains the module's simplest cache-aside implementation: a single hardcoded key rather than a query-derived one, because there is exactly one profile row and therefore no filter combination to distinguish. All four services share the same fail-soft contract: getSoft treats a Redis error as a miss; setSoft/delSoft/delPatternSoft log a WARN and continue rather than fail the request; and serialization is JSON.stringify/JSON.parse inside RedisCacheService itself, so no service in this module ever touches a raw Redis string.

9. BullMQ, Schedulers, and Async Work

Not applicable. No queue, job, or scheduler touches departments, designations, or the school profile — verified by the absence of any BullMQ import in lookups.service.ts or school-profile.service.ts.

10. Realtime and Events

Not applicable. Neither service emits a Socket.IO event or any other realtime signal.

11. Security, Auth, and Abuse Controls

• Guards. All three controllers carry @UseGuards(JwtAuthGuard, RoleGuard) at the class level (lookups.controller.ts:38-39,191, school-profile.controller.ts:11-12) — every route requires a valid JWT and a role check, with no @Public() route anywhere in this module.
• Permissions. Every handler declares an explicit @Permissions(...) (Departments_READ/CREATE/UPDATE/DELETE, Designations_READ/CREATE/UPDATE/DELETE, PersonClassifications_READ/CREATE/UPDATE/DELETE, SchoolProfile_READ/UPDATE). RoleGuard fails a request open only when a handler declares zero permissions and its controller is not classified as an administrative surface (role.guard.ts:38-63); every route here declares a permission, so that branch never applies — and route-permissions.spec.ts (referenced in the controller's own comment) asserts the same in CI.
• PersonClassifications is the one module in this file whose _READ permission is not admin-only. packages/db/src/seed/seed-auth.ts grants PersonClassifications_READ to both STAFF_PERMISSIONS and TEACHER_PERMISSIONS (the latter is currently defined as exactly the former), alongside Students_READ, Guardians_READ, Staff_READ, Departments_READ, Designations_READ, SchoolProfile_READ, and Activity_READ — every other permission in this document, including the four PersonClassifications write codes, is granted only through an explicit administrative role. The reason is that the pupil and staff people-forms both render an ethnicity select and a mother-tongue select, and a teacher or other staff member who cannot read these two lists sees two empty select boxes with no way to tell that it is a permissions problem rather than an empty table. Write access stays administrator-only because renaming an entry rewrites the recorded classification of every person already pointing at it, retroactively and including in government returns already filed — a consequence a form-filling teacher should not be able to trigger by editing a typo.
• Permission source. PERMISSION_MODULES in packages/db/src/authorization/permission-catalog.ts:78-91 lists Departments, Designations, PersonClassifications, SchoolProfile. Permission codes are generated as the cartesian product of every module and every action in PERMISSION_ACTIONS (CREATE, READ, UPDATE, DELETE, RESTORE) — so Departments_RESTORE, Designations_RESTORE, PersonClassifications_RESTORE, SchoolProfile_CREATE, SchoolProfile_DELETE, and SchoolProfile_RESTORE all exist as seeded, grantable permission codes even though no route in this module ever checks them (there is no restore endpoint, because none of departments/designations/ethnicities/mother_tongues has deleted_at; there is no create/delete route on the singleton profile). These are inert-but-present permissions, not a bug — the catalog generates uniformly across every module by design (see the file's own header on why a per-module hand-maintained list previously drifted and caused a live authorization hole).
• Superadmin bypass. Gated on the is_superadmin boolean flag, never on a role's name, and checked before the permission list — a role literally named "superadmin" grants nothing extra (role.guard.ts:145-152).
• Active-role scoping. Permissions resolve from the caller's active role only, never the union of every role they hold — a staff member who is also a guardian, currently acting as Guardian, does not carry Staff module permissions (role.guard.ts:126-129); the same rule means a user acting as Guardian cannot read the classification lists either, even if their Staff role would grant it.
• Guest identity. Not applicable — no route in this module is reachable without authentication.
• Rate limits. None declared on these controllers specifically; whatever global rate limiting applies to the API applies here uniformly (not module-specific, so not documented as this module's behavior).
• Input normalization. Every writable string field is trimmed server-side before comparison or persistence (see 6.1); the ILIKE search term is always escaped through escapeLikePattern before it reaches a pattern (packages/db/src/search/escape-like-pattern.ts) — unescaped, a search for 100% would match every row beginning 100, and a bare _ or % would silently stop filtering.
• ILIKE escaping mechanism, verified precisely: LookupsService calls the Drizzle ilike() helper directly, passing it a template string built as % + the escaped search term + %, which does not append an explicit ESCAPE '\' clause — it relies on Postgres's own default LIKE/ILIKE escape character, which is backslash. escapeLikePattern's own doc comment recommends pairing it with an explicit ESCAPE '\' clause because the default "is not guaranteed across configurations," so this module's usage is correct on this database's actual configuration but does not carry that extra guarantee explicitly in the query text.
• Sensitive data redaction. None of these fields are sensitive; there is no salary, password, or token data in scope for this module.
• Audit logs. No audit table write accompanies any create/update/delete in this module — verified by the absence of any audit/activity import in either service file. Contrast with modules like StaffSalary, which are gated and audited for that reason; departments/designations/school-profile carry no such requirement in the current code.
• Fail-closed behavior. Every domain error in this module (not-found, name-taken, has-references) is fail-closed — a thrown, coded exception that stops the request. Only cache invalidation is fail-soft.

13. Error Handling

Every response above four hundred is wrapped by AllExceptionsFilter into { statusCode, errorCode, message, timestamp?, path? } — timestamp/path are included outside production only (all-exceptions.filter.ts:29-33,79-89).

14. Observability

15. Testing and Validation

Two real-database integration suites exist under this document's modules; LookupsService (departments/designations) still has none of its own.

apps/api/src/modules/lookups/person-classifications.service.spec.ts — ten tests, against real Postgres via createTestDatabase()/closeTestDatabase(), with the cache stubbed to never hit (getSoft always resolves null) so every assertion is about what the database holds, not what a cache returned. Covers: creating an entry active-by-default with both id and publicId populated; refusing a name that differs only by case; keeping the two lists independent under a shared name (an ethnicity and a mother tongue can share a name without colliding with each other); renaming and retiring an entry; permitting a rename that only changes the casing of its own name (the clash check excludes the row being edited); a 404 on an unknown publicId; deleting an entry nothing points at; refusing to delete an entry people are recorded against (the important one — users.ethnicity_id is ON DELETE set null, so without the check the delete would succeed and silently blank everyone's recorded ethnicity); counting a mother tongue's references against the right column (mother_tongue_id, not ethnicity_id — the two are interchangeable to the type system, so this is checked explicitly rather than trusted by inspection); and filtering to active-only entries.

apps/api/src/modules/school-profile/school-profile.service.spec.ts — five tests, against real Postgres, snapshotting row 1 before the suite runs and restoring it in afterAll (there is only ever one row, so the suite edits the live singleton in place rather than creating a fixture). Covers: writing NULL, not an empty string, when a nullable field is cleared; trimming what it stores; leaving fields a patch omitted untouched; ignoring a blank on a NOT NULL column (timezone, currencyCode, name) rather than writing one; and storing a non-string field (academicYearStartMonth) as itself.

Both suites are named and run as real-database integration tests per this repo's testing rules — neither mocks DatabaseModule, Drizzle, or any @skoolsewa/* package; only the Redis cache is stubbed, and only because these tests exist to prove what the database does, not what the cache does.

No *.spec.ts file exists for LookupsService itself (departments/designations) — verified by directory listing. Its behavior in this document is verified against the current source files listed in 1. Documentation Evidence, not against a test suite.

The repo-wide route-permissions.spec.ts (referenced in lookups.controller.ts's own class comment) does cover this module indirectly: it asserts every administrative-surface handler declares a @Permissions() decorator, which all three controllers satisfy.

Validation commands:

pnpm --filter @skoolsewa/api build
pnpm --filter @skoolsewa/db build

16. Mandatory Backend Deep-Dive Pack

16.1 Submodule Coverage Matrix

No provider, processor, scheduler, or mapper file exists in either module directory beyond what is listed above — verified against the file map in 4.

16.2 UML and Architecture Diagram Pack

State diagrams: no table in this module has a modeled multi-state lifecycle beyond a boolean — see 16.5 for why is_active is a flag, not a state machine, in this schema. This applies identically to ethnicities/mother_tongues.

16.3 Code Flow Narrative

Covered per-method in 6. Services and Responsibilities and per-flow in 7. Runtime Flows. No method in either service exceeds the branching shown there — each create/update/delete follows lookup → validate → write → invalidate, with no additional branch.

16.4 Data Layer Deep Dive

Covered in full in 5.2 (field tables, nullability, business meaning) and the index rationale table at the end of that section (why each index/constraint exists and what it prevents). No JSON column, versioning field, or money unit exists in this module's tables. Seed data dependency: row 1 of school_profile comes from the migration, not a seed script; departments/designations start empty and every row an admin sees is one an admin (or an import) created; ethnicities/mother_tongues are the one exception — both start populated by seedReferenceData() (56 and 42 rows respectively), and a school only ever adds to or retires from that seeded baseline rather than building the list from nothing.

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 both module directories is represented (see 4).
• Every controller, service, and DTO is documented; no processor/scheduler/mapper exists in this module.
• Every method with business behavior has a code-flow narrative (6, 7).
• Every table has field-level detail (5.2).
• Every index/constraint/relation/delete behavior has rationale (5.2, index rationale table).
• is_active is documented as a flag, not a lifecycle, with the reasoning for why no state diagram applies.
• Every read/write flow has a sequence or activity diagram (7).
• Every business invariant is cataloged (16.5).
• Every cache key and its actual (not assumed) invalidation/population path is documented (8).
• Every architectural tradeoff is documented with alternatives and a revisit trigger (16.6).
• Every operational failure mode has a runbook entry (16.7).

18. Backend Completion Checklist

• Module boundaries are documented (2).
• Every controller, service, DTO, and schema file is covered.
• Every database table has a field table and relationship diagram.
• Every runtime flow has a diagram and branch notes.
• API and features/flows docs are linked below.
• No claim is made without a source file reference — every fact above cites the file (and, where load-bearing, the line range) it was read from.

See Also

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