39 KiB

Raw Permalink Blame History

[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

Phase 2 (stub, details TBD) — analytics dashboard rebuild

Phase 3 (stub, details TBD) — offboarding workflows

Reasons for splitting:

Scope bloat — 7 features in one plan, even with phased execution, is too much for one brain.

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.

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:

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.
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.
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.
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.
Class creation doesn't include assignment. Creating a class is one step; assigning a teacher and a starter roster is a second trip.
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.
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):

-- 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:

// 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):

-- 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):

-- 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):

Close active enrollments: enrollments.status = 'transferred' | 'graduated' | 'inactive' (chosen by reason)
Release hostel: room_assignments.status = 'inactive'
Remove from transport routes
Remove from clubs (if applicable)
Settle fees: block if student_fees.outstanding > 0 UNLESS admin overrides with reason
Set users.is_active = 0
Archive the user record: write alumni_status (active_alumni / inactive_alumni)
Audit log entry

Staff offboarding steps:

Close HR position: users.role unchanged, but set is_active = 0
Close pending leave: any leave_requests with status='pending' → cancelled
Settle payroll: block if last payroll_runs for this staff has unpaid line items UNLESS override
Revoke class teacher / subject teacher assignments (classes.class_teacher_id = NULL, subjects.teacher_id = NULL)
Set users.is_active = 0
Archive: mark archive_status = 'former_staff' (new column on users)
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:

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).
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.
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.
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.
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: …".
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.
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.
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
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
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.
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):

F0 auxiliary roles + class covers + parent-as-staff UI (foundation — touches auth/RBAC, every other feature depends on it)
F1 cohorts (unlocks the cohort picker everywhere)
F2 student assign (depends on F0, F1)
F3 teacher binding (depends on F0; can go in parallel with F2)
F4 create-class wizard (depends on F2, F3)
F5 analytics (depends on F0, F1; can start UI while F1 is in review)
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.

39 KiB Raw Permalink Blame History Unescape Escape