558 lines
39 KiB
Markdown
558 lines
39 KiB
Markdown
# [SUPERSEDED 2026-07-22] Auxiliary Roles + Cohorts + Class/Teacher Assignments + Analytics Dashboard + Offboarding
|
||
|
||
> **Superseded by the three-phase plan set.** This document is kept for paper trail only — do NOT execute against it.
|
||
>
|
||
> - **Phase 1** (active plan, ready to ship) — [auxiliary roles (thin slice) + cohorts + class/teacher assignments](./2026-07-22-phase1-cohorts-assignments.md)
|
||
> - **Phase 2** (stub, details TBD) — [analytics dashboard rebuild](./2026-07-22-phase2-analytics.md)
|
||
> - **Phase 3** (stub, details TBD) — [offboarding workflows](./2026-07-22-phase3-offboarding.md)
|
||
>
|
||
> Reasons for splitting:
|
||
> 1. Scope bloat — 7 features in one plan, even with phased execution, is too much for one brain.
|
||
> 2. Auth-layer risk — "touch every controller" is the kind of line that hides regressions. Thin-slice the auxiliary-roles change so existing checks keep working, migrate controllers incrementally in follow-up PRs.
|
||
> 3. Sync engine complexity — appending 5–7 new tables with FK relationships to a server-wins sync engine is a chunk of work in its own right; better to ship Phase 1 first and validate the sync process before scaling up.
|
||
>
|
||
> See the new plan docs for the executable details.
|
||
|
||
**Date:** 2026-07-22
|
||
**Owner:** Mavis (orchestrator) + backend-expert / frontend-expert / database-expert / tester reins
|
||
**Branch (target):** `feature/admin-cohorts-2026-07-22` (worktree from `dev`)
|
||
**Status:** plan (not started)
|
||
|
||
## Problem
|
||
|
||
Seven gaps in the admin/principal surface that block day-to-day school ops:
|
||
|
||
1. **No auxiliary roles.** A user has exactly one `users.role` (CHECK constraint enforces it). We need to express "teacher who also runs the library", "sports coach covering a sick teacher's class for 2 weeks", and "teacher whose own child learns at this school". Today all three are either impossible or hacky.
|
||
2. **No student cohorts.** Admins can't express "Form 4 IGCSE 2026" or "Grade 7 ZIMSEC" as a first-class object that links to classes, students, and the right exam paper set. `exam_groups` exists but is for exam *papers*, not student cohorts.
|
||
3. **Student → class assignment is half-built.** `Students.tsx` has a form but the UX is bare; the existing `enrollments.request_status` workflow is rarely exercised.
|
||
4. **Teacher → class binding is split.** `classes.class_teacher_id` (form tutor) and `subjects.teacher_id` (subject teacher) live on different pages. Admins can't manage both in one place.
|
||
5. **Class creation doesn't include assignment.** Creating a class is one step; assigning a teacher and a starter roster is a second trip.
|
||
6. **Reports page is a single screen, not a dashboard.** The principal (who shares the same portal as `school_admin`) needs real analytics — KPI tiles, time-series, drilldowns, filters, exports.
|
||
7. **No offboarding flow.** We have `users.is_active` and `enrollments.status` but no end-to-end process: deactivate user, close enrollments/positions, settle fees/payroll, archive to alumni/former-staff, audit log.
|
||
|
||
## Scope (in / out)
|
||
|
||
**In this plan:**
|
||
- **Auxiliary roles + class covers + parent-as-staff UI** (foundational; lands first).
|
||
- New `student_cohorts` table + cohorts page (admin + principal).
|
||
- Improve student→class assignment UX (batch assign, search/filter, status workflow).
|
||
- Class detail page: assign form tutor + subject teachers in one place.
|
||
- Create-class modal with optional initial teacher + cohort selection.
|
||
- Rewrite `Reports.tsx` as a full analytics dashboard; add `principal` to the allowed roles for `/reports`.
|
||
- Offboarding flow for students AND staff (full scope, with audit + audit log).
|
||
- New tables: `user_roles`, `student_cohorts`, `cohort_students`, `cohort_classes`, `cohort_exam_groups`, `offboarding_records`, `offboarding_actions`.
|
||
|
||
**Out of this plan (open follow-ups, ask first):**
|
||
- Replacing the SyncEngine mapping (each new table will be appended to `tablesToSync` in dependency order — that's in-scope, but a full sync rewrite is not).
|
||
- BI-grade export (PDF, scheduled email) — we ship CSV only this round.
|
||
- Finance-side "student_groups" rename or merge. `student_groups` (fees) stays as-is; cohorts are a separate concept.
|
||
- Migration framework (still `CREATE TABLE IF NOT EXISTS` only — adding a new Knex migration is the convention).
|
||
|
||
## Approach
|
||
|
||
### Feature 0 — Auxiliary roles, class covers, parent-as-staff UI *(foundational; lands first)*
|
||
|
||
**Why this is foundational:** every other feature in this plan needs to know "what can this user do, and for which resources?". If we build cohorts / offboarding / analytics first, we'd either have to retro-fit the auth layer to consider auxiliary roles, or ship the rest with the old single-role model and refactor later. Land this first; everything else rides on it.
|
||
|
||
**Schema (new Knex migration `2026072200000000_user_roles.js`):**
|
||
|
||
```sql
|
||
-- user_roles: auxiliary (or scoped) roles beyond users.role
|
||
CREATE TABLE user_roles (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
user_id INTEGER NOT NULL REFERENCES users(id),
|
||
role TEXT NOT NULL CHECK(role IN (
|
||
'systems_admin','school_admin','teacher','student','parent','accountant',
|
||
'librarian','nurse','clubs_head','principal','hr','bursar','dining_staff',
|
||
'driver','groundsman','matron','boarding_master','security','janitor'
|
||
)),
|
||
-- Optional scope. NULL scope = auxiliary role that applies app-wide
|
||
-- (e.g. "Mrs Moyo is also a librarian"). A row with scope applies only
|
||
-- to the scoped resource (e.g. cover teacher for Form 3A until 2026-07-29).
|
||
scope_class_id INTEGER REFERENCES classes(id),
|
||
scope_subject_id INTEGER REFERENCES subjects(id),
|
||
scope_cohort_id INTEGER REFERENCES student_cohorts(id),
|
||
starts_at DATETIME,
|
||
expires_at DATETIME,
|
||
reason TEXT, -- "covering sick leave", "acting librarian"
|
||
granted_by INTEGER REFERENCES users(id),
|
||
granted_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
revoked_by INTEGER REFERENCES users(id),
|
||
revoked_at DATETIME,
|
||
revoke_reason TEXT,
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0
|
||
);
|
||
|
||
-- Active-grant index: a user can hold the same role+scope pair only while the prior
|
||
-- grant is revoked. Enforce at app layer; the index is the safety net.
|
||
CREATE UNIQUE INDEX idx_user_roles_active
|
||
ON user_roles(user_id, role, scope_class_id, scope_subject_id, scope_cohort_id)
|
||
WHERE revoked_at IS NULL AND is_deleted = 0;
|
||
```
|
||
|
||
`users.role` stays as the **primary** role — it's still used for default landing page, sync identity, and as the anchor for any role that doesn't need a scope. `user_roles` carries everything else.
|
||
|
||
**Auth + RBAC rewiring:**
|
||
|
||
The current `auth` middleware in each controller only knows `req.user.role`. We add a small helper module `server/src/utils/rbac.js`:
|
||
|
||
```js
|
||
// Effective roles = primary + non-revoked, non-expired user_roles
|
||
function getEffectiveRoles(user) { /* ... */ }
|
||
|
||
// Boolean check: "does this user have role X right now?"
|
||
function hasRole(user, role) { /* ... */ }
|
||
|
||
// Scoped check: "does this user have role X for class Y right now?"
|
||
// - Primary 'teacher' role applies to all classes they teach
|
||
// - Scoped user_roles with scope_class_id=Y count for that class only
|
||
// - Unscoped user_roles (scope=NULL) apply app-wide
|
||
function hasRoleForClass(user, classId, role) { /* ... */ }
|
||
```
|
||
|
||
All existing `req.user.role !== 'teacher'` style checks in controllers get rewritten as `!hasRole(req.user, 'teacher')`. Same for `ProtectedRoute` on the client.
|
||
|
||
**Login flow change:** when a user logs in (`POST /api/auth/login`), the server reads `users.role` + active `user_roles` rows, embeds `{ id, role, effective_roles: [...] }` into the JWT payload. The 7-day expiry means a freshly-granted role won't be visible until the user's next login — to soften that, we expose `GET /api/auth/refresh-roles` which re-reads and re-signs. The client calls it on app load and after every grant/revoke mutation.
|
||
|
||
**Class covers (time-scoped auxiliary role):** when an admin covers a teacher, the wizard inserts a `user_roles` row with `role='teacher'`, `scope_class_id=X`, `starts_at=now`, `expires_at=now+14d`, `reason='Covering Mrs Nkomo (sick leave) - ticket #123'`. RBAC for "take attendance for class X" then matches both the regular class teacher and any active cover row. The cover row auto-expires (the RBAC check filters `WHERE expires_at > datetime('now')`); we don't need a cron job, but a daily cleanup task prunes expired rows for hygiene.
|
||
|
||
**Parent-as-staff UI (separate small PR, but ships with Feature 0):** the schema is already there (`parent_students` table). The work is:
|
||
- On the user profile page: a "Linked children at this school" card with add/remove (admin-only write; a user can request a link from their own profile).
|
||
- A "View as parent" toggle in the user menu (top-right avatar dropdown) that appears whenever the user has at least one `parent_students` row. The toggle swaps the active dashboard view to `/dashboard/parent` and filters the side nav to the parent role; the underlying role is unchanged (they're still a teacher, just viewing the parent side). When toggled off, they go back to their primary-role dashboard.
|
||
- One new endpoint: `GET /api/users/me/children` returns the children linked via `parent_students`. Already partly served by an existing route; this is the canonical one.
|
||
|
||
**New controller `server/src/controllers/userRoles.controller.js`:**
|
||
- `GET /api/users/:id/roles` — list all grants (active + revoked) for a user
|
||
- `POST /api/users/:id/roles` — body: `{ role, scope_class_id?, scope_subject_id?, scope_cohort_id?, starts_at?, expires_at?, reason }` — admin only, audit-logs the grant
|
||
- `PUT /api/user-roles/:id` — update expires_at / reason (extend or amend a cover)
|
||
- `DELETE /api/user-roles/:id` — soft-revoke (`revoked_at`, `revoked_by`); admin only, audit-logs the revoke
|
||
- `GET /api/user-roles/active-covers?class_id=X&date=YYYY-MM-DD` — used by the class detail page to show "currently covering: Mr Moyo (until 2026-07-29)"
|
||
- `GET /api/auth/refresh-roles` — re-signs the JWT with the latest effective roles (called by the client on app load + after grant mutations)
|
||
|
||
**Frontend (per conventions checklist, 5-file commit):**
|
||
- `client/src/store/userRoles.ts` — Zustand slice
|
||
- `client/src/pages/admin/UserRoles.tsx` — list/manage page (or a section inside Users.tsx detail)
|
||
- `client/src/components/RoleBadge.tsx` — small visual showing effective roles next to a user's name
|
||
- `client/src/components/CoverAssignmentForm.tsx` — modal for the class-cover wizard
|
||
- `client/src/components/ParentViewToggle.tsx` — the avatar-dropdown toggle
|
||
- Update `client/src/store/auth.ts` to add `effectiveRoles: string[]` to the user shape, and re-compute on `refreshRoles()`
|
||
- Update `client/src/App.tsx` `ProtectedRoute` to check `effective_roles` instead of `user.role`
|
||
- Update `client/src/components/Nav.tsx` to consider effective roles when picking the default nav
|
||
|
||
**Audit:** every grant, update, and revoke writes an `audit_log` row via `AuditService` (already in repo). One row per action. The new `user_roles` table itself is also audited via the standard `last_synced_at` / `sync_status` columns for the offline-sync trail.
|
||
|
||
**SyncEngine:** append `user_roles` to `tablesToSync` after `users` (FK dependency).
|
||
|
||
**Verification (this feature, isolated):**
|
||
- A teacher with `user_roles` row for `librarian` (scope=NULL) can see `/library` and `/dashboard/librarian` without losing teacher access.
|
||
- A `clubs_head` with a `user_roles` row for `teacher` scoped to `class 5A`, `expires_at = tomorrow` can take attendance for class 5A today but not tomorrow, and cannot take attendance for class 5B.
|
||
- Revoking a grant makes the route inaccessible within one client refresh (≤5s).
|
||
- The parent-as-staff toggle moves the user to the parent dashboard, links to the right child, and reverts on toggle off.
|
||
- JWT inspection: payload contains both `role` and `effective_roles`.
|
||
|
||
### Feature 1 — Student Cohorts
|
||
|
||
**Why new table, not reusing `exam_groups`:** `exam_groups` already means "a set of exam questions/papers" (used by `exams.controller.js` for `attempts`, `schedules`, etc.). Forcing it to also mean "a student cohort for ZIMSEC Grade 7" would create two unrelated meanings on the same row. Cohorts get their own table; cohorts then `JOIN` to `exam_groups` so "Form 4 IGCSE 2026 cohort" gets the right exam paper set.
|
||
|
||
**Schema (new Knex migration `2026072200000001_cohorts.js`):**
|
||
|
||
```sql
|
||
-- student_cohorts: the academic-programme grouping
|
||
CREATE TABLE student_cohorts (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
name TEXT NOT NULL, -- "Form 4 IGCSE 2026", "Grade 7 ZIMSEC"
|
||
programme TEXT NOT NULL CHECK(programme IN ('zimsec', 'igcse', 'as', 'a2', 'primary', 'other')),
|
||
level TEXT, -- "Form 4", "Grade 7"
|
||
academic_year TEXT, -- "2026"
|
||
description TEXT,
|
||
start_date DATE,
|
||
end_date DATE,
|
||
is_active INTEGER DEFAULT 1,
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0
|
||
);
|
||
|
||
-- cohort_students: M:N cohort <-> student
|
||
CREATE TABLE cohort_students (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
|
||
student_id INTEGER NOT NULL REFERENCES users(id),
|
||
joined_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
status TEXT DEFAULT 'active' CHECK(status IN ('active','inactive','transferred','graduated')),
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0,
|
||
UNIQUE(cohort_id, student_id)
|
||
);
|
||
|
||
-- cohort_classes: M:N cohort <-> class
|
||
CREATE TABLE cohort_classes (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
|
||
class_id INTEGER NOT NULL REFERENCES classes(id),
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0,
|
||
UNIQUE(cohort_id, class_id)
|
||
);
|
||
|
||
-- cohort_exam_groups: M:N cohort <-> exam_groups (which exam papers this cohort takes)
|
||
CREATE TABLE cohort_exam_groups (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
cohort_id INTEGER NOT NULL REFERENCES student_cohorts(id),
|
||
exam_group_id INTEGER NOT NULL REFERENCES exam_groups(id),
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0,
|
||
UNIQUE(cohort_id, exam_group_id)
|
||
);
|
||
```
|
||
|
||
**`enrollments.cohort_id` add-on:** add a nullable `cohort_id INTEGER REFERENCES student_cohorts(id)` to `enrollments` so each enrollment row can carry the cohort the student is in *for that academic year*. Existing rows: `cohort_id = NULL` (acceptable — old data, will be backfilled from `student_cohorts` + `cohort_students` opportunistically, or set manually on next rollover).
|
||
|
||
**New controller `server/src/controllers/cohorts.controller.js`:**
|
||
- `GET /api/cohorts` — list, with `?programme=`, `?academic_year=`, `?is_active=`
|
||
- `GET /api/cohorts/:id` — detail + students + classes + exam_groups
|
||
- `POST /api/cohorts` — create
|
||
- `PUT /api/cohorts/:id` — update (admin only for programme/level/year; everyone in role list can update description)
|
||
- `DELETE /api/cohorts/:id` — soft delete (`is_deleted = 1`)
|
||
- `POST /api/cohorts/:id/students` — bulk add students
|
||
- `DELETE /api/cohorts/:id/students/:studentId` — remove student
|
||
- `POST /api/cohorts/:id/classes` — link a class
|
||
- `DELETE /api/cohorts/:id/classes/:classId` — unlink
|
||
- `POST /api/cohorts/:id/exam-groups` — link exam paper set
|
||
- `DELETE /api/cohorts/:id/exam-groups/:examGroupId` — unlink
|
||
|
||
**Auth:** `school_admin` + `systems_admin` + `principal` for read; create/update/delete admin-only. Audit on every write (`AuditService` already exists).
|
||
|
||
**SyncEngine:** append `student_cohorts`, `cohort_students`, `cohort_classes`, `cohort_exam_groups` to `tablesToSync` in dependency order (parents first).
|
||
|
||
**Frontend (per conventions checklist, 5-file commit):**
|
||
- `client/src/store/cohorts.ts` — Zustand slice
|
||
- `client/src/pages/admin/Cohorts.tsx` — list (programme filter, academic-year filter, search)
|
||
- `client/src/pages/admin/CohortDetail.tsx` — detail with tabs: Overview / Students / Classes / Exam Groups
|
||
- `client/src/components/CohortForm.tsx` — create/edit modal (programme dropdown drives `level` suggestions: ZIMSEC → ECD-A-B, Grade 1-7, Form 1-6; IGCSE → Year 1-2 / IGCSE; AS → Form 5; A2 → Form 6)
|
||
- Routes in `App.tsx` for `school_admin`, `systems_admin`, `principal`
|
||
- Nav entries in `Nav.tsx` for the same three roles
|
||
|
||
### Feature 2 — Student → Class assignment (enhance)
|
||
|
||
**Backend:** no new tables. Reuse `enrollments` with the existing `request_status` workflow. Add two new endpoints:
|
||
- `POST /api/students/bulk-enroll` — accept `{ studentIds: number[], classId: number, academicYear: string }`, insert with `status='active', request_status='approved'`, return rows created
|
||
- `POST /api/enrollments/:id/transfer` — accept `{ newClassId }`, set old enrollment to `status='transferred'`, create new active row in one transaction. Used by offboarding too.
|
||
- `POST /api/enrollments/:id/withdraw` — accept `{ reason }`, set `status='inactive', request_status='withdrawn'`
|
||
|
||
**Frontend (`client/src/pages/Students.tsx`):**
|
||
- Keep existing single-student form. Add a "Bulk assign" button that opens a modal:
|
||
- Left: searchable, multi-select student list (filter by "no current enrollment" to focus on unassigned)
|
||
- Right: target class picker (with cohort context if a cohort is selected first)
|
||
- Footer: "Assign N students to {class name} for {academic year}"
|
||
- Show an enrollment-status column in the student table (active / pending / transferred / graduated)
|
||
- Pending enrollments get an Approve / Reject button row (only admins) — uses the existing `enrollments.request_status` workflow
|
||
|
||
### Feature 3 — Teacher → Class binding (class tutor + subject teacher in one place)
|
||
|
||
**Backend:** new endpoints (no new tables):
|
||
- `PUT /api/classes/:id/class-teacher` — set `classes.class_teacher_id`
|
||
- `GET /api/classes/:id/teachers` — return form tutor + subject teacher list
|
||
- `POST /api/classes/:id/subjects/:subjectId/teacher` — set `subjects.teacher_id` (UPSERT semantics)
|
||
|
||
**Frontend (`client/src/pages/admin/ClassDetail.tsx` — new):**
|
||
- Tabs: Overview / Roster / Teachers / Subjects / Cohorts
|
||
- "Teachers" tab: form-tutor dropdown (single), then a table of subjects with a per-row teacher dropdown. Edits inline. Reuses `subjects` table.
|
||
- Accessible to `school_admin`, `systems_admin`, `principal`
|
||
- Add a "Quick assign" link from the existing `Classes.tsx` list rows to jump to this detail
|
||
|
||
### Feature 4 — Create class + assign in one flow
|
||
|
||
**Frontend (`client/src/pages/Classes.tsx` create modal — extend existing):**
|
||
- Tab 1 "Class details": name, section, capacity, academic year, **cohort picker** (optional)
|
||
- Tab 2 "Assign teacher": form-tutor dropdown + "skip for now" checkbox
|
||
- Tab 3 "Roster": optional bulk-import of students via the same multi-select used in Feature 2 (admins rarely use this, so it's collapsed by default)
|
||
- "Create" button submits in one go: `POST /api/classes` + `PUT /api/classes/:id/class-teacher` + bulk-enroll in a single transaction on the server
|
||
|
||
**Backend:** add a server-side `POST /api/classes` variant `?withAssignments=1` that accepts `{ ...class, classTeacherId, initialStudentIds }` and runs all three steps in `db.transaction()`. Keep the old simple endpoint untouched for the "just a class" case.
|
||
|
||
### Feature 5 — Full analytics dashboard
|
||
|
||
**Why this is more than a UI rebuild:** the existing Reports.tsx renders 4–5 charts but is one screen with one filter. A real dashboard is a small SPA — KPI tiles, tabbed sections, drilldown dialogs, role-scoped data.
|
||
|
||
**Backend (`server/src/controllers/reports.controller.js` — extend, not replace):**
|
||
Add 4 new endpoints, all gated by `auth + (school_admin | systems_admin | principal)`:
|
||
|
||
- `GET /api/reports/kpis?range=6m` — returns:
|
||
- `enrollments: { total, active, newThisTerm, byProgramme: { zimsec: N, igcse: N, ... } }`
|
||
- `attendance: { rate30d, rate7d, trend: 'up'|'down'|'flat' }`
|
||
- `academics: { avgMark, passRate, topSubject, bottomSubject }`
|
||
- `finance: { collected30d, outstanding, overdueCount }`
|
||
- `staff: { activeTeachers, onLeave, pendingApprovals }`
|
||
- `GET /api/reports/timeseries?metric=enrollments|attendance|marks|attendance&range=6m&granularity=week|month` — returns time-series points
|
||
- `GET /api/reports/cohorts` — returns per-cohort performance: `{ cohortId, name, programme, studentCount, avgMark, passRate, attendanceRate, feeCollectionRate }`
|
||
- `GET /api/reports/export?type=...&format=csv` — server-side CSV streaming, uses the same queries as the JSON endpoints
|
||
|
||
The existing `/api/reports/summary` and `/api/reports/dashboard` endpoints stay — they're consumed elsewhere.
|
||
|
||
**Frontend (`client/src/pages/admin/Reports.tsx` — rebuild):**
|
||
- Header: time-range picker (7d / 30d / 90d / 6m / YTD / custom), cohort filter, programme filter, class filter
|
||
- KPI tile row (5 tiles matching the backend shape) with sparklines
|
||
- Tab 1 "Overview" — enrollment area chart, attendance line, fee collection stacked bar
|
||
- Tab 2 "Academics" — grade distribution, subject performance, top/bottom classes
|
||
- Tab 3 "Cohorts" — per-cohort table with the metrics from `/api/reports/cohorts`; click row → drilldown modal with that cohort's students ranked
|
||
- Tab 4 "Finance" — only available to roles that have finance access (bursar, principal, school_admin, systems_admin)
|
||
- Tab 5 "Staff" — teachers on leave, pending approvals, staff count by department
|
||
- Export button (top right): CSV per tab
|
||
- All charts already use Recharts (in repo); no new chart lib
|
||
|
||
**Routing:** add `principal` (and `bursar`, `hr` where finance/staff tabs apply) to the `/reports` route in every `getRoutes()` arm of `App.tsx`. Right now `/reports` is gated `['school_admin']` only in some role blocks.
|
||
|
||
**Performance:** SQLite + WAL is fine for the time-series queries at the size this school runs. Each KPI query should be a single SQL statement with one index scan. If a query takes >500ms, add a covering index. Don't introduce a cache this round.
|
||
|
||
### Feature 6 — Offboarding (students + staff, full)
|
||
|
||
**Schema (new Knex migration `2026072200000002_offboarding.js`):**
|
||
|
||
```sql
|
||
-- offboarding_records: one row per offboarding case
|
||
CREATE TABLE offboarding_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
user_id INTEGER NOT NULL REFERENCES users(id),
|
||
audience TEXT NOT NULL CHECK(audience IN ('student','staff')),
|
||
reason TEXT CHECK(reason IN ('graduated','transferred','withdrawn','expelled','resigned','terminated','retired','deceased','other')),
|
||
reason_notes TEXT,
|
||
initiated_by INTEGER REFERENCES users(id),
|
||
status TEXT DEFAULT 'in_progress' CHECK(status IN ('in_progress','completed','cancelled')),
|
||
effective_date DATE NOT NULL,
|
||
destination TEXT, -- "Hillside High" for transfers; "n/a" for resigned
|
||
alumni_status TEXT, -- students: 'active_alumni' | 'inactive_alumni' | NULL
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0
|
||
);
|
||
|
||
-- offboarding_actions: per-step audit trail (one row per action taken)
|
||
CREATE TABLE offboarding_actions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid TEXT UNIQUE,
|
||
record_id INTEGER NOT NULL REFERENCES offboarding_records(id),
|
||
step TEXT NOT NULL, -- 'enrollment_closed','hostel_released','transport_removed','fee_settled','leave_closed','payroll_finalized','account_deactivated','record_archived'
|
||
status TEXT NOT NULL CHECK(status IN ('pending','completed','skipped','failed')),
|
||
notes TEXT,
|
||
performed_by INTEGER REFERENCES users(id),
|
||
performed_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
-- standard sync columns
|
||
created_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
updated_at DATETIME DEFAULT (datetime('now','localtime')),
|
||
last_synced_at DATETIME,
|
||
sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced','pending','conflict')),
|
||
is_deleted INTEGER DEFAULT 0
|
||
);
|
||
```
|
||
|
||
**Student offboarding steps** (each becomes a row in `offboarding_actions`):
|
||
1. Close active enrollments: `enrollments.status = 'transferred' | 'graduated' | 'inactive'` (chosen by reason)
|
||
2. Release hostel: `room_assignments.status = 'inactive'`
|
||
3. Remove from transport routes
|
||
4. Remove from clubs (if applicable)
|
||
5. Settle fees: block if `student_fees.outstanding > 0` UNLESS admin overrides with reason
|
||
6. Set `users.is_active = 0`
|
||
7. Archive the user record: write `alumni_status` (active_alumni / inactive_alumni)
|
||
8. Audit log entry
|
||
|
||
**Staff offboarding steps:**
|
||
1. Close HR position: `users.role` unchanged, but set `is_active = 0`
|
||
2. Close pending leave: any `leave_requests` with `status='pending'` → `cancelled`
|
||
3. Settle payroll: block if last `payroll_runs` for this staff has unpaid line items UNLESS override
|
||
4. Revoke class teacher / subject teacher assignments (`classes.class_teacher_id = NULL`, `subjects.teacher_id = NULL`)
|
||
5. Set `users.is_active = 0`
|
||
6. Archive: mark `archive_status = 'former_staff'` (new column on `users`)
|
||
7. Audit log entry
|
||
|
||
**Backend (`server/src/controllers/offboarding.controller.js` — new):**
|
||
- `POST /api/offboarding/students/:userId` — body: `{ reason, effectiveDate, destination?, reasonNotes? }` → runs student pipeline, returns the record + actions
|
||
- `POST /api/offboarding/staff/:userId` — body: `{ reason, effectiveDate, reasonNotes? }` → runs staff pipeline
|
||
- `GET /api/offboarding/records?audience=student|staff&status=in_progress|completed` — list
|
||
- `GET /api/offboarding/records/:id` — detail with actions
|
||
- `POST /api/offboarding/records/:id/cancel` — re-open if not yet completed
|
||
- `POST /api/offboarding/records/:id/override` — admin override for the fee/payroll block, with mandatory reason
|
||
- `GET /api/offboarding/alumni?status=active_alumni&cohort_id=` — alumni directory (for principal review)
|
||
|
||
All endpoints run inside a single `db.transaction()`. Any step failure rolls back the whole offboarding. The `offboarding_actions` rows are written in the same transaction so the audit is always consistent with the state.
|
||
|
||
**Frontend:**
|
||
- `client/src/pages/admin/Offboarding.tsx` — list of in-progress + recent offboardings
|
||
- `client/src/pages/admin/OffboardingWizard.tsx` — multi-step wizard (Reason → Effective date → Checklist of steps → Confirm). For staff: HR + finance steps visible. For student: hostel/transport/fees steps visible.
|
||
- `client/src/pages/admin/Alumni.tsx` — searchable alumni directory (read-only, principal can also see this)
|
||
- Add "Offboard" button on the student detail (Users.tsx) and on the staff directory (`/hr/staff`) — admin/principal only
|
||
|
||
**Auth:** initiate/complete = `school_admin | systems_admin | principal`. View records = same. Override = `school_admin | systems_admin` only.
|
||
|
||
## Files to add / change (summary)
|
||
|
||
### Backend (new)
|
||
- `server/src/database/migrations/knex/2026072200000000_user_roles.js` — `user_roles` table + active-grant unique index
|
||
- `server/src/database/migrations/knex/2026072200000001_cohorts.js` — 4 cohort tables + `enrollments.cohort_id` ALTER
|
||
- `server/src/database/migrations/knex/2026072200000002_offboarding.js` — 2 offboarding tables + `users.archive_status` ALTER
|
||
- `server/src/database/migrations/knex/2026072200000003_users_archive_status.js` — if the previous migration can't be split cleanly; combine if simpler
|
||
- `server/src/controllers/userRoles.controller.js`
|
||
- `server/src/controllers/cohorts.controller.js`
|
||
- `server/src/controllers/offboarding.controller.js`
|
||
- `server/src/services/OffboardingService.js` — the pipeline logic (testable in isolation)
|
||
- `server/src/utils/rbac.js` — `getEffectiveRoles`, `hasRole`, `hasRoleForClass` helpers
|
||
|
||
### Backend (modify)
|
||
- `server/src/controllers/classes.controller.js` — `?withAssignments=1` variant on `POST /`
|
||
- `server/src/controllers/students.controller.js` — `POST /bulk-enroll`
|
||
- `server/src/controllers/enrollments.controller.js` — `POST /:id/transfer`, `POST /:id/withdraw`
|
||
- `server/src/controllers/reports.controller.js` — add `kpis`, `timeseries`, `cohorts`, `export` endpoints
|
||
- `server/src/services/SyncEngine.js` — append 7 new table names to `tablesToSync` in dependency order (users first, then user_roles, then the rest)
|
||
- `server/src/index.js` — register `userRoles`, `cohorts`, `offboarding` controllers
|
||
- `server/src/controllers/auth.controller.js` (or equivalent login handler) — embed `effective_roles` in JWT payload, add `GET /api/auth/refresh-roles`
|
||
- Every existing controller — replace `req.user.role !== X` with `!hasRole(req.user, X)`, and where class-scoped, with `!hasRoleForClass(req.user, classId, X)`
|
||
|
||
### Frontend (new)
|
||
- `client/src/store/userRoles.ts`
|
||
- `client/src/store/cohorts.ts`
|
||
- `client/src/store/offboarding.ts`
|
||
- `client/src/pages/admin/UserRoles.tsx` — list/manage page (or section inside Users.tsx detail)
|
||
- `client/src/pages/admin/Cohorts.tsx`
|
||
- `client/src/pages/admin/CohortDetail.tsx`
|
||
- `client/src/pages/admin/ClassDetail.tsx` — class detail with teacher assignments (incl. active covers)
|
||
- `client/src/pages/admin/Offboarding.tsx`
|
||
- `client/src/pages/admin/OffboardingWizard.tsx`
|
||
- `client/src/pages/admin/Alumni.tsx`
|
||
- `client/src/components/RoleBadge.tsx`
|
||
- `client/src/components/CoverAssignmentForm.tsx`
|
||
- `client/src/components/ParentViewToggle.tsx`
|
||
- `client/src/components/CohortForm.tsx`
|
||
- `client/src/components/CreateClassWizard.tsx` — extends existing `Classes.tsx` modal
|
||
|
||
### Frontend (modify)
|
||
- `client/src/store/auth.ts` — add `effectiveRoles: string[]` to user shape, `refreshRoles()` action
|
||
- `client/src/App.tsx` — `ProtectedRoute` checks `effective_roles`; add 5 new routes per role arm, add `principal` (and `bursar`/`hr` where appropriate) to `/reports`
|
||
- `client/src/components/Nav.tsx` — add Nav entries; default-nav picker considers effective roles
|
||
- `client/src/pages/admin/Reports.tsx` — full rebuild
|
||
- `client/src/pages/Students.tsx` — bulk assign + status column + pending approvals
|
||
- `client/src/pages/Classes.tsx` — wire CreateClassWizard, link to ClassDetail
|
||
- `client/src/pages/admin/Users.tsx` — add "Offboard" button on student detail; "Linked children" card; add auxiliary role section
|
||
- `client/src/pages/admin/HRManagement.tsx` — add "Offboard" button on staff detail
|
||
- `client/src/pages/hr/StaffDirectory.tsx` — same
|
||
- `client/src/pages/parent/ParentDashboard.tsx` (or wherever the user lands on "view as parent") — verify the view-as-parent toggle re-uses it correctly
|
||
- `client/src/store/api.ts` — no changes (single axios instance stays as-is)
|
||
|
||
### Docs / changelogs (commit per conventions)
|
||
- `.harness/changelogs/2026-07-22-user-roles.md` — F0
|
||
- `.harness/changelogs/2026-07-22-cohorts.md`
|
||
- `.harness/changelogs/2026-07-22-assignments.md`
|
||
- `.harness/changelogs/2026-07-22-analytics-dashboard.md`
|
||
- `.harness/changelogs/2026-07-22-offboarding.md`
|
||
- Update `.harness/AGENTS.md` — note `student_cohorts` is the canonical ZIMSEC/IGCSE/etc. concept; note the `user_roles` auxiliary-role pattern; note `ProtectedRoute` checks `effective_roles` not `user.role`
|
||
- Update `.harness/docs/conventions.md` — replace the "Roles: `admin`, `teacher`, ..." line with a reference to `server/src/utils/rbac.js` and `effective_roles`
|
||
|
||
## Constraints / decisions baked in
|
||
|
||
- **Naming:** `student_cohorts` (not `groups`) — explicit user decision. `student_groups` (fees) and `exam_groups` (papers) are unchanged.
|
||
- **Auxiliary role model:** one `user_roles` table, scoped (class / subject / cohort / time) or unscoped. `users.role` stays as the primary role. RBAC rewires to a small `server/src/utils/rbac.js` helper + `effective_roles` in the JWT. This is foundational and lands first.
|
||
- **Class covers** are a `user_roles` row with a time range and class scope — same table, different shape. No second mechanism.
|
||
- **Parent-as-staff:** schema already there (`parent_students`); we ship UI for "link my child" + "view as parent" toggle.
|
||
- **Auth/RBAC:** write paths = admin; read paths include principal. No new role. Every controller that checks role must use `hasRole` / `hasRoleForClass`, not `req.user.role !== X`.
|
||
- **No sync rewrite** — just append new tables. Sync team owns the order in SyncEngine.
|
||
- **No migration framework upgrade** — continue with `CREATE TABLE IF NOT EXISTS` + targeted `ALTER` per file.
|
||
- **PWA offline:** cohort/offboarding/auxiliary-role screens must work offline (just like the rest of the admin pages) — data layer mutations go through the existing sync contract; no new offline-only quirks. JWT carries a snapshot of effective roles; a `/api/auth/refresh-roles` endpoint lets the client re-sync after a grant mutation.
|
||
- **Audit:** every offboarding action writes an `offboarding_actions` row in the same transaction; every user_roles grant/revoke writes an `audit_log` row via `AuditService`. No second source of truth.
|
||
- **Currency / locale:** analytics stay in the school's locale (use `useEducationTerms()` and the existing locale helpers, not hardcoded strings).
|
||
- **Reuse over new:** use existing AuditService, SyncEngine, Recharts, lucide, axios. No new libraries.
|
||
- **No principal-specific portal** — principal shares `school_admin` portal with role-scoped data, per existing convention.
|
||
|
||
## Verification (acceptance bar)
|
||
|
||
This plan is "done" when **all** of the following pass:
|
||
|
||
1. **Migrations apply cleanly** on a fresh DB and on an existing DB. `npm run db:init` succeeds. Roll forward + back works (`npm run db:rollback` exists or manual `down` runs).
|
||
2. **Auxiliary roles:** a teacher with an unscoped `librarian` grant can see `/library` and `/dashboard/librarian`; a `clubs_head` with a class-scoped `teacher` grant (expires_at = tomorrow) can take attendance for that class today and cannot tomorrow. Revoking a grant removes the route access within one client refresh (≤5s). JWT payload contains both `role` and `effective_roles`. `parent_students` toggle moves a teacher (with a linked child) to the parent dashboard.
|
||
3. **Cohort flow:** admin can create a cohort "Form 4 IGCSE 2026", link a class, link 30 students, link 1 exam_group. After creation, querying `/api/reports/cohorts` returns that cohort with the right student count. After academic rollover, the cohort correctly carries its students forward.
|
||
4. **Assignment flow:** admin can bulk-assign 10 unassigned students to a class in one action; each gets a row in `enrollments` with `status='active', request_status='approved'`. Transferring a student creates a new active row + sets the old to `transferred` in one transaction.
|
||
5. **Teacher binding:** on the class detail, admin can set the form tutor AND change a subject teacher, both persist, both show in the class list. Active covers (time-scoped `user_roles`) show up on the class detail as "currently covering: …".
|
||
6. **Create-class wizard:** admin creates "Form 1B" with form tutor "Mrs Moyo" and 0 students in one go. All three pieces land in the DB.
|
||
7. **Analytics dashboard:**
|
||
- `/reports` renders for `school_admin`, `systems_admin`, `principal` (not for `student`, `parent`, `teacher`).
|
||
- KPI tiles compute against realistic data (seed has at least 3 cohorts, 1 graduated cohort, mixed attendance).
|
||
- Time-range filter actually changes the data.
|
||
- Cohort tab shows per-cohort pass rate. Drilldown modal renders the cohort's students ranked.
|
||
- CSV export downloads a non-empty file.
|
||
8. **Offboarding — student:** offboard a sample student. After completion:
|
||
- `users.is_active = 0`, `users.archive_status = 'active_alumni'`
|
||
- All `enrollments` for that student are `status = 'transferred'`
|
||
- Hostel/transport rows are closed
|
||
- Fee block fires if balance > 0, override requires a reason
|
||
- 8 `offboarding_actions` rows for the 8 steps, all `status='completed'`
|
||
- Alumni directory lists the student
|
||
9. **Offboarding — staff:** offboard a sample teacher. After completion:
|
||
- `users.is_active = 0`, `users.archive_status = 'former_staff'`
|
||
- `classes.class_teacher_id` and `subjects.teacher_id` rows referencing this teacher are `NULL`
|
||
- Pending leave is `cancelled`
|
||
- Payroll block fires if last run unpaid
|
||
- Audit log entry exists
|
||
10. **E2E (Playwright):** one spec per major flow (auxiliary role grant, cohort create, bulk assign, class cover, offboard student, offboard staff, reports loads for principal, view-as-parent toggle). Existing portal-smoke tests still pass.
|
||
11. **No regression:** existing admin/principal dashboards, the exam-review module, and the finance module all load and respond. Sync pushes the new tables without errors.
|
||
|
||
## Execution order (suggested)
|
||
|
||
The 7 features have these dependencies:
|
||
|
||
```
|
||
F0 (auxiliary roles + class covers + parent-as-staff)
|
||
├─► F1 (cohorts) ─┬─► F2 (student assign, uses cohort picker)
|
||
│ └─► F5 (analytics, uses cohort data + effective roles)
|
||
└─► F3 (teacher) ─┘
|
||
F4 (create-class) uses F3 (teacher) and F2 (bulk-enroll)
|
||
F6 (offboarding) is mostly independent of F0..F5
|
||
F5 (analytics) uses F1 (cohorts) for the Cohorts tab
|
||
```
|
||
|
||
Recommended order to ship (each is its own PR into `dev`):
|
||
|
||
1. **F0 auxiliary roles + class covers + parent-as-staff UI** (foundation — touches auth/RBAC, every other feature depends on it)
|
||
2. **F1 cohorts** (unlocks the cohort picker everywhere)
|
||
3. **F2 student assign** (depends on F0, F1)
|
||
4. **F3 teacher binding** (depends on F0; can go in parallel with F2)
|
||
5. **F4 create-class wizard** (depends on F2, F3)
|
||
6. **F5 analytics** (depends on F0, F1; can start UI while F1 is in review)
|
||
7. **F6 offboarding** (mostly independent; can run in parallel with F4/F5)
|
||
|
||
Each PR: branch from `dev`, worktree at `.worktrees/<name>/`, follow `.harness/AGENTS.md` branch model, conventional commit messages, changelog entry, E2E spec where applicable.
|
||
|
||
## Out-of-scope but worth noting (do not silently take on)
|
||
|
||
- Bulk CSV import of historical cohorts / students. Currently out of scope; can be a separate feature.
|
||
- Connecting offboarding to Paynow (settle outstanding fees via Paynow). Today the wizard just *blocks* on outstanding fees; the actual settlement is a separate action on the existing Paynow flow.
|
||
- Auto-creating a cohort when a class is created (we keep cohort creation explicit, so admins think about programme and year).
|
||
- A "former staff" directory page (we keep it as a list under `/offboarding/former-staff` for now, not a full directory like Alumni).
|
||
- Tightening JWT secret, CORS, request-size limit — those are flagged in the repo's open follow-ups; not this plan.
|