Skip to content

Table-based list APIs for classes & courses + lightweight filter surface (v1)

1. Problem distillation

  • Product standardized the FE on filterable table views (people, classes/courses, timetable share one UX). The current GET /homerooms / GET /subject-groups return a grouped tree (dept → cv → grade → leaves[]) — wrong shape for a table, and unpaginated.
  • v1 ships two flat, paginated, filterable table GETs — classes (homerooms) and courses (subject groups) — plus a dedicated lightweight filter-population surface (/filters/*) that feeds the dropdowns.
  • Rows carry full class/course information except the roster; roster stays on the existing GET /:id detail. The only aggregate on a row is numStudents.
  • The new table GETs are the future GET /homerooms / GET /subject-groups: they land on a temporary /table sub-path, the grouped-tree GETs are marked deprecated and deleted in a later iteration (then /table renames onto the bare paths).

Success criteria (observable behavior that proves this works): - GET /homerooms/table returns { data, meta } pages filtered by any AND-combination of departmentId, curriculumId, gradeId, homeroomId, homeroomTeacherId, studentId; rows have no roster; meta.total reflects the filtered count. - GET /subject-groups/table does the same for departmentId, curriculumId, gradeId, trackId, teacherId, curriculumSubjectId; rows carry the new track summary and subject.code. - Both accept sortBy/sortDir from a whitelisted column set; pagination is deterministic under every sort (stable tiebreaks). - Every /filters/* route returns the uniform { data: [...], hasMore } envelope; people routes cap at 50 with truthful hasMore; structural routes always return the whole set with hasMore: false. - Record-level visibility of the tables is identical to the routes they replace (HomeroomsPolicy / SubjectGroupsPolicy as they stand in tree — teacher = own assignments). - Old grouped-tree GETs still work, marked deprecated in swagger.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - No aggregate/rollup endpoints for the table views (v2). - No people or timetable table views — classes and courses only. - No multi-value (array) filters; every filter param is a single uuid. - No tree-shaped filter responses (the flat rows carry parent ids so the FE can group client-side; trees are v2). - No changes to the grouped boards (grouped-homerooms / grouped-courses) — untouched, not deprecated. - No schema changes of any kind.

2. Patterns survey

Analogous module/spec What we'd borrow What doesn't fit
src/audit-log/ (ListAuditLogQueryDto extends PaginationQueryDto, PaginatedResponseDto) The { data, meta } paginated list contract + query-DTO-extends-pagination pattern Audit list is admin-only and unsorted-configurable; we add whitelisted sortBy/sortDir
src/teachers/ GET /teachers/hour-budgets Paginated aggregate list on a sub-path of an existing module, @AggregateResponse() bypassing scope-grouping It is @RequireRoles('admin'); our tables keep the policy-based every-role audience
src/homerooms/ + src/subject-groups/ current list leaves (HomeroomListItemDto, SubjectGroupListItemDto) Row shapes reused nearly verbatim (summaries, ordered teachers[], numStudents) Leaves are nested in a grouped tree; we serve them flat + paginated + timestamps
src/permissions/ GET /permissions Authenticated-only route posture (JWT, no scope requirement) — precedent for the structural filter routes It's infra, not domain data; divergence logged in §7
docs/superpowers/specs/2026-06-16-eligibility-classifier-pickers-design.md Server-narrowed picker lists for dropdown population Pickers are write-gate-coupled and manager-only; filters are read-only and every-role
src/homerooms/homerooms.policy.ts, src/subject-groups/subject-groups.policy.ts, src/students/students.policy.ts Policy.where(ctx) reuse for record-level narrowing (tables, /filters/homerooms, /filters/students) Fits cleanly

On-axis check (ch16): paved-path work — new read endpoints on existing entities + one new read-only module. The single off-axis element is the authenticated-only RBAC posture of structural filter routes (§7 row 1); no new cascade consumers, no write paths, no post-tx fan-out.

3. Architecture mapping

Primitive Apply? How Justify
Tenant scope yes Every query welds tenantId from @TenantId(); filters module included Standard
Academic-year scope yes Table queries + /filters/{departments,grades,curricula,homerooms,students} accept the AcademicYearQueryDto override and resolve to the active year otherwise (departments/grades are AY-chained in this schema); /filters/{tracks,subjects} inherit the AY through the required curriculumId; /filters/teachers is AY-scoped too (Teacher.academicYearId) and accepts the override Read-only surface → override allowed per convention
RBAC entity key existing only HOMEROOMS, SUBJECT_GROUPS for the tables; TEACHERS/STUDENTS read gates on the two people filter routes; no new entity Filters is not a domain entity; structural routes are authenticated-only (divergence §7)
Scopes existing Tables: @RequireScopes(HOMEROOMS,'read') / @RequireScopes(SUBJECT_GROUPS,'read') (any-scope read, same as today's lists). People filters: @RequireScopes(TEACHERS,'read') / @RequireScopes(STUDENTS,'read') No new scopes
Actions none Read-only surface; read is implicit
Service base custom (existing services + one new plain service) Table methods land in HomeroomsService/SubjectGroupsService next to findGrouped; new FiltersService is a plain injectable (no CRUD, no scope-field mapping) BaseTenantedCrudService is for scoped-entity CRUD — n/a here
queries.ts shape yes homerooms.queries.ts/subject-groups.queries.ts gain findHomeroomsTablePage/findSubjectGroupsTablePage (+ counts) as named functions; new filters.queries.ts holds one named function per filter route Convention
Error codes none new 400s come from DTO validation; unknown/cross-tenant filter ids yield empty results, not errors Anti-oracle + simplicity
DTO conventions yes dto/homerooms-table-query.dto.ts, dto/homeroom-table-response.dto.ts (and SG twins); filters module dto/ with per-route item + response classes List-query DTO pattern
File-backed sub-resources n/a No files involved
Custom fields no Table rows exclude customFields (same as today's list leaves); available on GET /:id Row weight
Profile completeness no n/a — no person fields touched

4. Data model plan

Schema deltas

  • None. Read-only iteration over existing models.

Migration shape

  • n/a — no migration.

Indexes and uniqueness

  • None added in v1. The filter columns (departmentId, curriculumId, gradeId, homeroomTeacherId, FK columns on assignment tables) are FK-backed; tenant datasets are small (≤ a few hundred homerooms/SGs). Revisit only if the table queries show up in slow logs.

5. API surface

Verb Path Decorators Request DTO Response DTO
GET /homerooms/table @ProtectedResource(), @RequireScopes(HOMEROOMS,'read'), @AppliesPolicy(HomeroomsPolicy), @AggregateResponse() HomeroomsTableQueryDto HomeroomTableResponseDto ({ data: HomeroomTableRowDto[], meta })
GET /subject-groups/table @ProtectedResource(), @RequireScopes(SUBJECT_GROUPS,'read'), @AppliesPolicy(SubjectGroupsPolicy), @AggregateResponse() SubjectGroupsTableQueryDto SubjectGroupTableResponseDto
GET /filters/departments @ProtectedResource(), @AggregateResponse() AcademicYearQueryDto DepartmentFiltersResponseDto
GET /filters/grades same AY + departmentId? GradeFiltersResponseDto
GET /filters/curricula same AY + departmentId?, gradeId? (covers-grade) CurriculumFiltersResponseDto
GET /filters/tracks same curriculumId (required) TrackFiltersResponseDto
GET /filters/subjects same curriculumId (required), gradeId? (taught-in-grade), trackId? SubjectFiltersResponseDto
GET /filters/homerooms same + @AppliesPolicy(HomeroomsPolicy) AY + departmentId?, curriculumId?, gradeId? HomeroomFiltersResponseDto
GET /filters/teachers same + @RequireScopes(TEACHERS,'read') AY + search? TeacherFiltersResponseDto
GET /filters/students same + @RequireScopes(STUDENTS,'read') + students policy narrowing AY + search?, departmentId?, gradeId? StudentFiltersResponseDto

Table query DTOs

HomeroomsTableQueryDto extends AcademicYearPaginationQueryDto: - Filters (all optional uuid, ANDed): departmentId, curriculumId, gradeId, homeroomId, homeroomTeacherId, studentId (roster membership in the resolved AY). - sortBy?: 'name' | 'department' | 'grade' | 'homeroomTeacher' | 'numStudents' | 'createdAt', sortDir?: 'asc' | 'desc' (default asc).

SubjectGroupsTableQueryDto extends AcademicYearPaginationQueryDto: - Filters: departmentId, curriculumId, gradeId, trackId (effective track — the anchor subject's own track or, for an in-block subject with no own track, the containing block's track (track ?? optionBlock.track, same rule as the grouped board); strict — effective-track-null (common) subjects excluded when set), teacherId (member of SubjectGroupTeacher), curriculumSubjectId. - sortBy?: 'name' | 'department' | 'grade' | 'subject' | 'numStudents' | 'createdAt', sortDir? as above. (track is not sortable: the effective track is computed across two relations, not a Prisma-sortable path — §9.)

Sort semantics (both tables): - Column mapping: department → department name; grade → grade ordinalPosition; homeroomTeacher → tutor lastName, firstName; subject → anchor subject name; numStudents → roster relation _count. - Omitted sortBy = default composite — classes: department name → grade ordinal → name; courses: department name → grade ordinal → subject name → name. - Determinism: the chosen column is followed by the default composite chain and a final id asc tiebreak. Null relation columns (homeroomTeacher, track) follow Postgres placement (nulls last on asc). - Primary-teacher sort on courses is excluded in v1 (to-many position-0 ordering is not expressible in Prisma orderBy; §9).

Table row DTOs

  • HomeroomTableRowDto = HomeroomListItemDto (id, name, department/curriculum/grade summaries, homeroomTeacher|null, baseRoom|null, numStudents) + createdAt, updatedAt.
  • SubjectGroupTableRowDto = SubjectGroupListItemDto (id, name, dept/cv/grade summaries, subject summary, ordered teachers[], baseRoom|null, combinedClassId|null, numStudents) + track {id,name}|null (the effective track: subject.track ?? optionBlock.track, null = common) + createdAt, updatedAt.
  • Additive change to the shared summary: SubjectGroupSubjectSummaryDto gains code: string | null (projects the existing CurriculumSubject.code; also enriches the deprecated tree list and the :id detail — additive, non-breaking).

Filter response envelope

Every /filters/* response is { data: <ItemDto>[], hasMore: boolean } — full symmetry with the tables' data key; structural routes always hasMore: false, people routes cap at 50 rows (name-ordered) and report the cap truthfully. Item shapes:

  • departments: { id, name }
  • grades: { id, name, ordinalPosition, departmentId }
  • curricula: { id, name, status, departmentId }
  • tracks: { id, name } (no synthetic "Common" row — FE renders that choice; it means "no track filter")
  • subjects: { id /* = curriculumSubjectId */, name, code, trackId /* effective: track ?? optionBlock.track */, optionBlock {id,name}|null }
  • homerooms: { id, name } (policy-narrowed)
  • teachers: { id, firstName, lastName }, search = case-insensitive substring on first/last name
  • students: { id, firstName, lastName } (policy-narrowed), search as above

Swagger considerations

  • Old GET /homerooms + GET /subject-groups swagger decorators gain deprecated: true with copy pointing at /table ("this route will be replaced by the table shape").
  • /table routes must be declared before @Get(':id') (static-segment precedence; same situation as the grouped boards).
  • Filter routes: document the 50-row cap + hasMore semantics on the two people routes; mark curriculumId required on tracks/subjects.
  • JSDoc on all new controller methods is FE-facing copy only.

6. RBAC seed plan

Seed file Delta
PermissionScope (rbac-catalogue.ts) none
PermissionAction (rbac-catalogue.ts) none
ScopeFieldMapping (rbac-catalogue.ts) none
Role grants (roles.ts) none — tables reuse existing homerooms/subject_groups read grants; people filters reuse existing teachers/students read grants
*_SCOPES runtime constant none

7. Divergence ledger

Pattern We diverge by Reason Tradeoff accepted
Every domain read is scope-gated Structural filter routes (departments/grades/curricula/tracks/subjects/homerooms) are authenticated-only (JWT, no @RequireScopes) Audience is every role (students/referents lack departments/curricula scopes but their views need the dropdowns); id+name of school structure is not sensitive Any authenticated tenant user can enumerate structure names. Homerooms stay policy-narrowed; people routes stay scope-gated
PaginatedResponseDto { data, meta } for lists Filter GETs use a lighter { data, hasMore } envelope, unpaginated Dropdowns don't paginate; hasMore + type-to-narrow beats page controls in a select Two list envelopes coexist (shared data key keeps FE parsing uniform)
One canonical route per resource Transitional double surface: deprecated grouped-tree GETs + new /table sub-path on the same resources FE migrates screen-by-screen; /table renames onto the bare paths when the trees are deleted Temporary duplicate list logic in both modules until the cleanup iteration
Grouped boards are "the list view" (iter-5 spec language) Tables become the FE list view; boards stay as coverage/dashboard surfaces Product UX consistency decision Boards keep their own maintenance cost; not deprecated in v1

8. Pushback log

US says Conflicts with Proposed instead Status
"GET ENTITY should return THE FILTERED list" (restructure in place) FE migration safety — the tree GETs are live New /table endpoints now; trees deprecated, deleted + renamed later (user's own call) Resolved
"subject (all eng courses)" — implies school-wide subject filtering No cross-curriculum subject identity exists (subjects are per-cv rows) Filter by curriculumSubjectId (user's pick): precise, but "all English school-wide" spans one curriculum at a time; name-based matching rejected Resolved
Filters "flat as lists" User also wants future dept-scoped trees for selects v1 flat with parent ids on each row (departmentId on grades/curricula) so a tree shape can land in v2 without breaking Resolved

9. Deferrals

  • Aggregate/rollup endpoints per table view — v2 per the product plan — follow-up: next iteration spec.
  • Multi-value (array) filters + "common-only" track filter value — no FE need yet — revisit at next iteration.
  • Tree-shaped /filters/* responses (e.g. grades grouped by dept) — v1 rows already carry parent ids — v2.
  • Primary-teacher column sort on the courses table — not expressible in Prisma orderBy (to-many, position 0); needs raw SQL if ever wanted — revisit on FE demand.
  • Track column sort on the courses table — the effective track is computed (track ?? optionBlock.track), not a Prisma-sortable relation path — revisit on FE demand.
  • People (teachers/staff/students) and timetable table views — separate iterations.
  • Deletion of the grouped-tree GETs + renaming /table onto the bare paths — cleanup iteration after FE migrates.
  • Board (grouped-homerooms/grouped-courses) deprecation — explicitly left untouched in v1; revisit when the FE list screens are fully on tables.
  • Parametric-role narrowing of structural filter routes (e.g. department_head seeing only their dept in /filters/departments) — v1 returns tenant-wide catalogs; the tables themselves stay policy-narrowed — revisit with the parametric-roles threading iteration.

10. Open questions

None — all resolved in chat 2026-07-12 (endpoint placement, subject-filter identity, filters surface, audience, envelope symmetry, RBAC posture, sorting).

11. Verification plan

  • Unit specs:
  • homerooms.queries.spec.ts / subject-groups.queries.spec.ts additions — table WHERE builders (each filter alone + combined; studentId/teacherId relation hops; trackId strictness excluding track-null subjects) and ORDER BY builders (every sortBy value maps to the documented column chain + id tiebreak; default composite when omitted).
  • filters.queries.spec.ts — per-route narrowing params, people-route cap + hasMore computation, search case-insensitivity.
  • Policy interplay — table queries compose Policy.where(ctx) (reuse the existing policy spec harness from homerooms.policy.spec.ts / subject-groups.policy.spec.ts).
  • E2E specs:
  • homerooms-table.e2e-spec.ts — pagination determinism under sorts, filter combos, roster absence on rows, studentId filter, deprecated tree GET still 200s.
  • subject-groups-table.e2e-spec.ts — same + track/subject.code presence, trackId strictness, teacherId filter, curriculumSubjectId filter.
  • filters.e2e-spec.ts — role matrix: student/referent can call structural routes (200, no scope needed) and see policy-narrowed homerooms/students; teacher/students routes 403 without the read grant; people cap + hasMore: true beyond 50; curriculumId required → 400; unknown filter ids → empty data.
  • Manual verification: hit both /table routes and each /filters/* route via the Scalar playground on the seeded dev tenant; confirm envelope shapes match §5 examples.

12. Sign-off

  • Approved by: Fabio Barbieri
  • Date: 2026-07-12
  • Chat reference: "approved" in chat 2026-07-12, after examples walkthrough + envelope-symmetry / RBAC-posture / sorting decisions

Until this section is filled, no implementation code is written. When you fill it, flip the frontmatter status: to Approved in the same edit.