Table lists & filters — iteration 2: multi-value (multiselect) filtering¶

1. Problem distillation¶

The FE filter bars are multiselect: a user can pick several departments, several grades, several tracks, etc. The v1 table + filter surface (landed 2026-07-12) accepts only a single uuid per filter — it was an explicit v1 non-goal (v1 §1) and deferral (v1 §9, "Multi-value (array) filters + 'common-only' track filter value — no FE need yet"). There is now an FE need.
This iteration widens every id filter on the two table GETs and the narrowing params on the /filters/* population routes from a single uuid to an array, with within-field OR / cross-field AND semantics — the exact convention already shipped on the people lists (GET /students).
The /filters/* routes go multivalue because the FE filter bar cascades: selecting departments A+B must narrow the grade / curriculum / homeroom dropdowns to "under A or B". The one exception is the required curriculumId anchor on /filters/tracks and /filters/subjects — tracks/subjects are per-curriculum rows, so that anchor stays single (user's call).
The bundled "Common (no track)" track value (deferred alongside multivalue in v1 §9) is folded in now: the SG table's track filter and /filters/subjects' track filter can select track-less ("Common") courses.

Success criteria (observable behavior that proves this works): - GET /homerooms/table accepts repeated or comma-separated values for departmentId, curriculumId, gradeId, homeroomId, homeroomTeacherId, studentId; each field ORs internally, fields still AND together. ?gradeId=A&gradeId=B returns classes in grade A or B; adding ?departmentId=X further ANDs to dept X. - GET /subject-groups/table does the same for departmentId, curriculumId, gradeId, trackId, teacherId, curriculumSubjectId; the combined-class second source stays consistent (it is single-sourced on the SG WHERE builder). - The SG table + /filters/subjects accept includeCommonTrack=true, returning courses/subjects whose effective track (subject.track ?? optionBlock.track) is null; combinable with trackId[] (e.g. Track A or Common). - /filters/{grades,curricula,homerooms,students,subjects} accept multi-value narrowing on their optional params; /filters/tracks and /filters/subjects keep curriculumId single + required. - Wire back-compat: a lone value (?departmentId=x) still works unchanged — @TransformToArray() coerces it to a one-element array, which yields the same rows as v1's equality filter. No FE migration needed for existing single-value callers; multiselect is purely additive on the wire. - Empty / omitted filters never emit a { in: [] } fragment (which would match zero rows) — @TransformToArray() maps empty input to undefined, so the builder skips the fragment.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - No new filter fields — same filter set as v1, only their arity changes. - No multi-value curriculumId on /filters/tracks or /filters/subjects (the required per-curriculum anchor stays single — user's call). - No multiselect on the free-text search params (/filters/teachers, /filters/students) — text stays a single case-insensitive substring. - No schema changes, no RBAC changes, no new endpoints, no sort/pagination changes. - No aggregate/rollup or tree-shaped filter responses (still v2, per v1 §9).

2. Patterns survey¶

Analogous module/spec	What we'd borrow	What doesn't fit
`src/students/dto/list-students-query.dto.ts` (`departmentId`/`gradeId`/`homeroomId` as `string[]` via `@TransformToArray()` + `@IsUUID('4',{each:true})`)	The entire multi-value query-param pattern verbatim: transform, per-element validation, `@ApiPropertyOptional({ type:[String], isArray })`, and `{ in: [...] }` in the WHERE builder	Nothing — this is the exact precedent; the new surface simply regressed off it in v1
`src/common/utils/transform-to-array.ts` (`TransformToArray`)	Coercion of single / repeated / comma-separated values → `string[]`, empty → `undefined` (the `{ in: [] }` foot-gun is already handled)	Fits cleanly; reused as-is
`docs/superpowers/specs/2026-07-12-table-lists-and-filters-design.md` (v1)	The DTOs, query builders (`buildHomeroomsTableWhere`, `buildSubjectGroupsTableWhere`, filters.queries), envelopes, policies, swagger posture — all unchanged in shape	v1 deliberately capped filters at single uuid (§1/§9); this iteration lifts exactly that cap
`src/subject-groups/subject-groups.queries.ts` `buildCombinedClassesTableWhere`	Combined-class filters are single-sourced (`buildSubjectGroupsTableWhere(query).map(f => ({ subjectGroups: { some: f } }))`), so upgrading the SG builder to arrays carries the combined side for free	Fits cleanly — no separate change
`src/command-center/dto/completeness-query.dto.ts`, `src/referents/dto/list-referents-query.dto.ts`	Further precedents of `@TransformToArray()` multi-value query filters already in production	Different domains; same mechanism

On-axis check (ch16): fully paved-path. This is the DTO/query-builder arity change the students list already models; it invents no new primitive. The only new logic is the effective-track "Common" branch (below), a small WHERE-fragment addition — not a cross-cutting concern.

3. Architecture mapping¶

Primitive	Apply?	How	Justify
Tenant scope	yes	Unchanged — every query still welds `tenantId`; only filter fragments change from `=` to `IN`	Standard
Academic-year scope	yes	Unchanged from v1 (tables + AY-chained filter routes resolve the active year or accept the override)	Read-only surface
RBAC entity key	existing only	No change — `HOMEROOMS`/`SUBJECT_GROUPS` tables; `TEACHERS`/`STUDENTS` people-filter gates; structural routes authenticated-only (v1 §7 row 1)	No new entity
Scopes	existing	No change — same `read` gates as v1	No new scopes
Actions	none	Read-only; `read` implicit
Service base	existing services + `FiltersService`	Same methods, widened param types; `FiltersService` narrowing objects gain `{ in: [...] }` fragments	No structural change
`queries.ts` shape	yes	`HomeroomsTableFilterParams` / `SubjectGroupsTableFilterParams` field types `string → string[]`; builders emit `{ in }`; new shared `buildEffectiveTrackWhere(trackIds, includeCommon)` helper; `filters.queries.ts` narrowing widened	Convention
Error codes	none new	400s from DTO validation (bad uuid in an array element); unknown ids → empty result, not error (anti-oracle preserved)	Simplicity
DTO conventions	yes	The four DTOs affected (2 table query DTOs + `filter-query.dto.ts` classes) swap scalar uuid props for `@TransformToArray()` + `each:true` arrays; add `includeCommonTrack?: boolean` where track applies	List-query DTO pattern
File-backed sub-resources	n/a	No files
Custom fields	no	Unchanged
Profile completeness	no	n/a

4. Data model plan¶

Schema deltas¶

None. Read-only iteration; arity change on query params only.

Migration shape¶

n/a — no migration.

Indexes and uniqueness¶

None added. IN over FK-backed columns on small tenant datasets (≤ a few hundred rows) needs no new index; revisit only if slow logs show it. (Same posture as v1 §4.)

5. API surface¶

No new routes. Same eight /filters/* routes + two /table routes as v1; request DTO arity changes only. Response DTOs, decorators, policies, and sorting are unchanged.

Filter arity changes¶

HomeroomsTableQueryDto — all six id filters become string[] (@TransformToArray() + @IsUUID('4',{each:true}) + @ApiPropertyOptional({ type:[String], format:'uuid', isArray:true })): departmentId, curriculumId, gradeId, homeroomId, homeroomTeacherId, studentId. Sort params unchanged.

SubjectGroupsTableQueryDto — six id filters become string[]: departmentId, curriculumId, gradeId, trackId, teacherId, curriculumSubjectId; plus includeCommonTrack?: boolean. Sort params unchanged.

/filters/* query DTOs (src/filters/dto/filter-query.dto.ts): - GradesFilterQueryDto.departmentId → string[] - CurriculaFilterQueryDto.departmentId, .gradeId → string[] - HomeroomsFilterQueryDto.departmentId, .curriculumId, .gradeId → string[] - StudentsFilterQueryDto.departmentId, .gradeId → string[] (search stays scalar string) - SubjectsFilterQueryDto.gradeId → string[]; .trackId → string[]; plus includeCommonTrack?: boolean; curriculumId stays single + required - TracksFilterQueryDto.curriculumId stays single + required (unchanged) - DepartmentsFilterQueryDto, TeachersFilterQueryDto — unchanged (no id narrowing params)

WHERE translation (queries layer)¶

Direct columns → { col: { in: ids } }. Relation hops keep their shape, id swapped for { in }:

homerooms studentId[] → { assignments: { some: { studentId: { in: ids } } } } (roster contains any)
homerooms homeroomTeacherId[] → { homeroomTeacherId: { in: ids } }; homeroomId[] → { id: { in: ids } }
SG departmentId[] → { curriculumSubject: { curriculum: { departmentId: { in: ids } } } }
SG curriculumId[] → { curriculumSubject: { curriculumId: { in: ids } } }
SG teacherId[] → { teachers: { some: { teacherId: { in: ids } } } }
SG curriculumSubjectId[] → { curriculumSubjectId: { in: ids } }
curricula filter gradeId[] (covers-grade) → { grades: { some: { gradeId: { in: ids } } } }
subjects filter gradeId[] (taught-in-grade) → { hours: { some: { gradeId: { in: ids } } } }

The combined-class table side needs no edit: buildCombinedClassesTableWhere re-maps every SG fragment as { subjectGroups: { some: fragment } }, so it inherits the { in } fragments automatically.

Effective-track + "Common" (the one new fragment)¶

A shared helper buildEffectiveTrackWhere(trackIds: string[], includeCommon: boolean): Prisma.CurriculumSubjectWhereInput returns an OR over the branches that are present: - for each real id set (when trackIds non-empty): - { trackId: { in: trackIds } } (own track), and - { trackId: null, optionBlock: { trackId: { in: trackIds } } } (inherits block track) - when includeCommon (effective track is null): - { trackId: null, optionBlockId: null } (own track null, not in a block), and - { trackId: null, optionBlock: { trackId: null } } (own track null, block track also null)

Consumers: - SG table: wrap it — { curriculumSubject: buildEffectiveTrackWhere(...) } (replaces the inline trackId OR in buildSubjectGroupsTableWhere). - /filters/subjects: merge the returned OR into the CurriculumSubject where (the filter where is already on CurriculumSubject).

Home for the helper + no magic constants: src/subject-groups/subject-groups.queries.ts (primary consumer), imported by src/filters/filters.queries.ts. If neither trackIds nor includeCommon is present, callers omit the fragment entirely (all courses).

Swagger considerations¶

Multi-value params documented with { type:[String], isArray:true } and the standard copy already used on the students list: "Multi-value: repeat the param or send a comma-separated list."
trackId copy on the SG table + subjects filter: note that a track filter still excludes Common courses unless includeCommonTrack=true is also sent.
includeCommonTrack documented as a boolean that admits courses/subjects with no effective track; combinable with trackId.
Re-affirm on /filters/tracks//filters/subjects that curriculumId is required and single-valued.
JSDoc on controllers/DTOs remains 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 — arity change reuses v1's grants
`*_SCOPES` runtime constant	none

7. Divergence ledger¶

Pattern	We diverge by	Reason	Tradeoff accepted
v1 filter arity ("every filter param is a single uuid", v1 §1)	Every id filter (except the two required `curriculumId` anchors) becomes an array	The FE bars are multiselect and cascading; v1 deferred this pending FE need (v1 §9)	Two coexisting arities briefly (single-value callers keep working via `@TransformToArray()` coercion — no break)
v1 track filter ("effective-track-null (common) subjects excluded"; `/filters/tracks` Common choice "means no track filter", v1 §5)	The FE's "Common" choice now means a real filter (effective track null), driven by `includeCommonTrack`, not "no filter"	Folding in the bundled Common value (v1 §9) so a track multiselect can include Common	The semantics of the FE-synthesized "Common" chip change; FE must map it to `includeCommonTrack` instead of "clear the track filter"
Multiselect widget → one query param	"Common" travels as a separate boolean (`includeCommonTrack`) rather than a reserved `common` token inside the `trackId` array	Keeps strict `@IsUUID('4',{each:true})` on `trackId` and invents no custom uuid-or-token validator; "Common" is the absence of a track, semantically distinct from a track id	FE partitions its track multiselect into `trackId[]` + a boolean (a ~2-line map) instead of sending one uniform array

8. Pushback log¶

US says	Conflicts with	Proposed instead	Status
"make both multivalue" (tables + filter routes)	The required `curriculumId` anchor on `/filters/tracks`/`/filters/subjects` — tracks/subjects are per-curriculum rows; a multi-curriculum anchor changes those routes' identity	Keep that anchor single + required; everything else multi	Resolved (user's call in chat 2026-07-13)
"fold in Common"	No uuid represents "no track"; a naive `trackId` sentinel would break strict uuid validation	Separate `includeCommonTrack` boolean (see §7 row 3)	Resolved (boolean flag confirmed at sign-off 2026-07-13)

9. Deferrals¶

Multi-value curriculumId on /filters/tracks//filters/subjects — per-curriculum anchor kept single by design — revisit only if a multi-curriculum track/subject picker is ever needed.
Free-text multi-term search — single substring is enough — revisit on FE demand.
Everything still open from v1 §9 (aggregate/rollup endpoints, tree-shaped filter responses, primary-teacher & track column sorts, people/timetable table views, tree-GET deletion + /table rename, board deprecation, parametric-role narrowing of structural routes) — unchanged by this iteration.

10. Open questions¶

None — the "Common" wire mechanism (the only open item) resolved at sign-off in favour of the includeCommonTrack boolean (§7 row 3, §8 row 2).

11. Verification plan¶

Unit specs:
homerooms.queries.spec.ts — buildHomeroomsTableWhere emits { in } for each array filter; single-element array behaves like v1's scalar; multi-element OR; studentId[] relation hop; omitted/empty → no fragment. ORDER BY unchanged (no new assertions).
subject-groups.queries.spec.ts — same for the six SG filters; buildEffectiveTrackWhere: real ids only, includeCommon only (common-only), both together; trackId still excludes Common when includeCommonTrack absent; combined side inherits { in } (assert buildCombinedClassesTableWhere wraps the array fragments).
filters.queries.spec.ts / filters.service.spec.ts — multi-value narrowing on grades/curricula/homerooms/students/subjects; curriculumId stays single on tracks/subjects; subjects trackId[] + includeCommonTrack; unknown ids → empty data.
E2E specs:
homerooms-table.e2e-spec.ts — repeated-param and comma-separated multi-value; OR-within/AND-across; single value still 200s identically (back-compat).
subject-groups-table.e2e-spec.ts — multi-value combos; includeCommonTrack alone (common-only) and with trackId[]; combined-class rows respect the array filters.
filters.e2e-spec.ts — cascading multiselect (/filters/grades?departmentId=A&departmentId=B → grades under A∪B); curriculumId required+single on tracks/subjects (multi or missing → 400); role matrix unchanged from v1.
Manual verification: hit both /table routes and the cascading /filters/* routes with repeated params on the seeded dev tenant via Scalar; confirm OR/AND semantics and the Common branch.

Patterns: chapter 09 (testing); feedback_e2e_isolation_patterns.md.

12. Sign-off¶

Approved by: Fabio Barbieri
Date: 2026-07-13
Chat reference: "signed off" in chat 2026-07-13, after the three-fork walkthrough (cascading multiselect on both surfaces; curriculumId anchor stays single on tracks/subjects; Common folded in via the includeCommonTrack boolean)

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