Subject-groups "missing courses" coverage-gap widget¶

1. Problem distillation¶

An admin on the courses page (GET /subject-groups/table) wants a persistent widget that surfaces which offered subjects still have no course, so they know what is left to create — grouped as department → curriculum → grade → missing subjects, and reactive to the exact same filters the table uses.

"Course" = SubjectGroup. A subject is covered iff ≥1 SubjectGroup is anchored on that (curriculumSubject, grade) in the active AY. Creating a second/third section never changes coverage — the widget's unit is "at least one course exists", not "enough sections exist". This is the user's explicit "AT LEAST" constraint (N courses for one big subject is fine and must not read as a surplus or a gap).
A subject is offered in a grade = the curriculum has a positive-hours grid cell for it in that grade (the existing cell ⟺ taught rule — same offeredCells source the grouped board uses).
Catalog-driven, not demand-driven. Every offered subject that lacks a course appears — student selections are irrelevant (a subject nobody has chosen yet still needs a course eventually, and the widget must work before the selection window even opens). This is why the endpoint needs no STUDENTS read (unlike grouped-courses).
Mandatory and optional both listed; option-block alternatives carry isOptional: true (+ the block ref) so the FE can render them apart from mandatory gaps.

Success criteria (observable behavior that proves this works):

GET /subject-groups/missing-courses returns departments[] → curricula[] → grades[] → missingSubjects[], where a missingSubjects[] entry is { curriculumSubject: {id,name}, isOptional: boolean, optionBlock: {id,name} | null }.
Every level (dept, cv, grade) carries numMissingMandatory + numMissingOptional rollups so each badge reads its number directly without walking children.
A subject appears iff it is offered in the grade (has an hours cell) AND has zero SubjectGroups on (curriculumSubject, grade, AY) — visible to the caller.
Fully-covered branches are pruned: only nodes with ≥1 missing subject somewhere below are emitted (no badge ⟹ nothing to create).
The endpoint accepts the identical /table query object; it honors departmentId[], curriculumId[], gradeId[], trackId[] + includeCommonTrack, curriculumSubjectId[], academicYearId, and silently ignores teacherId[], page, limit, sortBy, sortDir.
trackId/includeCommonTrack narrow by effective track (subject.track ?? optionBlock.track), byte-identical to the table's buildEffectiveTrackWhere.

Non-goals:

No "created" counts. How many courses exist is already the table's meta.total; this endpoint returns the gap only. (Deferral §9.)
No student/roster/expected/placed data — that stays on grouped-courses.
No change to grouped-courses, grouped-homerooms, the table, pickers, write paths, schema, or RBAC seed.
No demand filtering of optional alternatives (every offered alternative is listed, per user directive "include all subjects, mandatory and not").

2. Patterns survey¶

Analogous module/spec	What we'd borrow	What doesn't fit
`2026-07-10-grouped-courses-iteration-5-design.md`	Catalog-skeleton assembly (`findBoardDepartments` + `findGroupedBoardCurricula` + `findBoardSubjectsForCurricula`), `offeredCells` "cell ⟺ taught" rule, dept/cv/grade ordering, `AggregateResponseDto` envelope	Its `dept → grade → cv → track` nesting inverts to `dept → cv → grade`; drops per-subject roster counts, the track node, and the whole `STUDENTS`-read coverage fold
`src/subject-groups/subject-groups.queries.ts` `buildEffectiveTrackWhere` / `buildSubjectGroupsTableWhere`	Effective-track filter semantics — replicated in-memory over `BoardSubjectRow.effectiveTrack` so the widget and table agree on `trackId`/`includeCommonTrack`	The SQL builder targets `SubjectGroup` rows; here it narrows the offered catalog, so it's a pure in-memory predicate over the same effective-track rule
`src/subject-groups/grouped-courses.controller.ts`	Controller shape: `@ProtectedResource` + `@RequireScopes(SUBJECT_GROUPS,'read')` + `@AppliesPolicyDimensions(SubjectGroupsPolicy)` + `@AggregateResponse()`	Drops `teacher` from `@RequireRoles` and drops the inline `STUDENTS`-read assert (§7 divergence)

3. Architecture mapping¶

Primitive	Apply?	How	Justify
Tenant scope	yes	`DepartmentsPolicy` / `CurriculaPolicy` / `SubjectGroupsPolicy` `where`s + RLS on every query
Academic-year scope	yes	`resolveActiveYear(prisma, tenantId, query.academicYearId)` — read-override honored (parity with `/table`)
RBAC entity key	existing	`SUBJECT_GROUPS`, `read` scope only — no `STUDENTS` (catalog-only, no student data)
Scopes / Actions	existing `read` / none	Unchanged
Service base	custom method	`SubjectGroupsService.getMissingCourses(tenantId, query, ctx)` — pure fold, no tx
`queries.ts` shape	add 1 query	`findCoveredSubjectGradePairs(tx, where)` = `subjectGroup.groupBy({ by: ['curriculumSubjectId','gradeId'], where })`; reuse `findBoardDepartments`, `findGroupedBoardCurricula`, `findBoardSubjectsForCurricula` unchanged	groupBy is the lean "does ≥1 course exist per (subject,grade)" probe
Error codes	existing	None new
DTO conventions	new response DTO file	`MissingCoursesResponseDto` + node/leaf DTOs; query DTO reused (`SubjectGroupsTableQueryDto`)	forbidNonWhitelisted ⟹ reuse guarantees the FE's exact filter object validates
File-backed / custom fields / completeness	n/a

4. Data model plan¶

None. Pure read surface. No migration, no indexes, no schema change.

5. API surface¶

Verb	Path	Decorators	Request DTO	Response DTO
GET	`/subject-groups/missing-courses`	`@ProtectedResource` · `@RequireScopes(SUBJECT_GROUPS,'read')` · `@RequireRoles('admin','department_head')` · `@AppliesPolicyDimensions(SubjectGroupsPolicy)` · `@AggregateResponse()`	`SubjectGroupsTableQueryDto` (reused)	`MissingCoursesResponseDto`

Response shape (contract):

{
  "departments": [{
    "department": { "id", "name" },
    "numMissingMandatory": 4,
    "numMissingOptional": 2,
    "curricula": [{                          // cvs in the dept with ≥1 gap — DRAFT and READY alike
      "curriculum": { "id", "name" },
      "numMissingMandatory": 4,
      "numMissingOptional": 2,
      "grades": [{                           // covered grades with ≥1 gap
        "grade": { "id", "name" },
        "numMissingMandatory": 2,
        "numMissingOptional": 1,
        "missingSubjects": [{
          "curriculumSubject": { "id", "name" },
          "isOptional": false,               // true = option-block alternative
          "optionBlock": null                // { id, name } when isOptional
        }]
      }]
    }]
  }]
}

Normative rules:

Offered set: for each visible cv (any status), each grade it covers (GroupedBoardCurriculumRow.grades), each BoardSubjectRow with an offeredCells entry for that grade. isOptional = subject.optionBlock !== null; mandatory ⟹ optionBlock: null.
Missing test: the (subject.id, grade.id) pair is absent from the covered-pairs set built from findCoveredSubjectGradePairs (visible SGs ∩ AY).
Filter application (all AND together; each is OR-over-values):
departmentId[] — narrows findBoardDepartments + findGroupedBoardCurricula.
curriculumId[] — narrows findGroupedBoardCurricula.
gradeId[] — restricts which covered grades are walked.
curriculumSubjectId[] — restricts which offered subjects are candidates.
trackId[] + includeCommonTrack — keep a subject iff (trackId.length && effectiveTrack && trackId.includes(effectiveTrack.id)) OR (includeCommonTrack && effectiveTrack === null); applied only when trackId.length || includeCommonTrack. Mirrors buildEffectiveTrackWhere.
teacherId[], page, limit, sortBy, sortDir — ignored (a not-yet-created course has no teacher; the widget is not paginated/sorted).
Pruning: emit a grade node iff missingSubjects.length > 0; a cv node iff it has ≥1 emitted grade; a dept node iff it has ≥1 emitted cv.
Ordering: departments by ordinalPosition then name; curricula by name; grades by ordinalPosition then name; missingSubjects by (ordinalPosition, name) — same key rules as the grouped board.
Rollups: numMissingMandatory/numMissingOptional at grade = count of its missingSubjects split by isOptional; cv = Σ over its grades; dept = Σ over its cvs.

Swagger considerations¶

Operation description states the honored-vs-ignored filter split explicitly (FE-facing: it MUST call out that teacherId/pagination/sort are accepted but no-ops, since the reused SubjectGroupsTableQueryDto advertises them).
DTO JSDoc: contract only — isOptional meaning, optionBlock null-for- mandatory, "empty branches pruned".

6. RBAC seed plan¶

No deltas. Reuses SUBJECT_GROUPS read + existing roles/policies. The route gate is admin + department_head (see §7).

7. Divergence ledger¶

Pattern	We diverge by	Reason	Tradeoff accepted
`grouped-courses` gate = `admin`/`department_head`/`teacher`, self-narrowing SGs for teachers	Gate = `admin`/`department_head` only; no `teacher`	A teacher's `SubjectGroupsPolicy` self-narrows to their own assigned groups, so the covered-pairs set would miss courses other teachers own → false gaps. Admin/dept_head see all SGs in scope, so coverage is correct. Also an admin planning tool per the ask.	Teachers can't see the widget (acceptable — they don't create courses)
`grouped-courses` asserts `STUDENTS` read (coverage counts derive from students)	No `STUDENTS` assert	This widget is catalog-only; no student data is read	None
Grouped board nests `dept → grade → cv → track`	`dept → cv → grade` (no track node)	Matches the requested widget hierarchy (grouped by cv, then grade); track is a filter here, not a level	Two boards with different nesting (already true post-iter-5)

8. Pushback log¶

US says	Conflicts with	Proposed instead	Status
"how many courses I created VS how many I should still create" (opening framing)	The refined widget surfaces only the gap list; the "created" side already exists as the table's `meta.total`	Endpoint returns gaps only; FE composes the "VS" from `/table` `meta.total`	Accepted by user (chat 2026-07-13) — clarified to "subject names for which you didn't create ≥1 course"

9. Deferrals¶

numCreatedCourses per node — the "created" half of the original "VS" framing. Omitted: the courses table already returns it as meta.total, and adding it here would reintroduce the teacherId-ignored inconsistency (a created-course count would sensibly honor teacherId, which this endpoint ignores). Follow-up: add if the FE cannot source it from the table.
Demand annotation (e.g. numStudentsExpected per missing subject) — not requested; grouped-courses already carries expected/placed if needed.
Emitting fully-covered nodes with a 0 badge — user chose pruning; revisit only if the FE wants "✓ all covered" affordances.

10. Open questions¶

None — gap semantics (catalog-driven, subject-name list, isOptional flag), teacherId handling (ignore), role gate (drop teacher), and pruning were all resolved in chat (2026-07-13).

11. Verification plan¶

Unit specs — subject-groups.service.spec.ts, new getMissingCourses describe:
mandatory subject with 0 SGs → listed (isOptional:false, optionBlock:null).
mandatory subject with ≥1 SG → absent; with ≥2 SGs → still absent (AT LEAST).
option-block alternative with 0 SGs → listed (isOptional:true, block ref).
subject not offered in a grade (no hours cell) → absent even with 0 SGs.
pruning: fully-covered grade/cv/dept omitted.
rollups: numMissingMandatory/numMissingOptional at each level.
filters: departmentId/curriculumId/gradeId/curriculumSubjectId narrow candidates; trackId+includeCommonTrack match effective track (own + inherited-block + common); teacherId/page/sortBy no-op.
ordering (dept ordinal, cv name, grade ordinal, subject ordinal-then-name).
academicYearId override honored.
E2E specs — test/subject-groups-missing-courses.e2e-spec.ts: admin sees gaps for the seed cv; department_head narrowed to their dept; teacher → 403; a teacherId filter leaves the gap list unchanged; covered subject absent after a course is created.
Manual verification: none beyond e2e.

Doc updates on landing: ch14 grouped-boards / subject-groups section, docs/REFERENCE.md subject-groups row, swagger copy, and (if the FE guide set covers the courses page) a short FE guide note.

12. Sign-off¶

Approved by: Fabio
Date: 2026-07-13
Chat reference: design approved in chat 2026-07-13 ("yes" / "go on") after clarifying gap semantics (catalog-driven subject-name list + isOptional), teacherId handling (ignore), role gate (drop teacher), and pruning.

Until this section is filled, no implementation code is written.