Reports & Analytics — Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• A read-only reporting engine over every other domain's tables: attendance, students/demographics, exams/academics, assignments, promotions, leave, staff/timetable, ID cards, the academic calendar, notices/notifications, complaints, finance (fees, payroll, assets) and cross-domain correlations.
• Its own configuration and delivery tables: report_settings, report_definitions (saved reports and school templates), report_dashboards, report_schedules (+ recipients), and the report_jobs export pipeline (+ recipients, stored objects, event log).
• Every admin route under /api/reports/...: catalog/metrics/filter-options, the live-run route, the custom builder, dashboards and their render route, saved reports, templates, schedules and their lifecycle, settings, the cross-domain overview and year-closing screens, and the student/class/teacher analytic profiles.
• The pupil's and guardian's own read-only reports screen, mounted under /api/mobile (student/reports, guardian/children/:studentId/reports).
• A central metric-definition catalogue (METRIC_DEFINITIONS) that every report's metrics array references, so "attendance rate" or "pass rate" means one calculation everywhere it appears.
• Small-group privacy suppression on demographic and other identity-adjacent breakdowns (suppressTable, driven by report_settings.min_group_size), applied identically whether the report ran live, from a dashboard widget, from the builder, or inside an export.
• Background delivery: a single-worker BullMQ queue (QueueName.REPORTS) for file generation, and a second single-worker queue (QueueName.REPORTS_SWEEP) for the schedule sweep (every minute) and the maintenance sweep (every 15 minutes).

Does Not Own

• The source data of any domain it reports on — attendance marks, exam results, leave requests, fee ledgers, payroll, assets, etc. all remain owned by their own modules; Reports only reads them, inside a single read-only transaction per run.
• Authentication and RBAC enforcement mechanics (JwtAuthGuard, RoleGuard) — Reports supplies the permission codes a report or route needs, the guard enforces them.
• File storage mechanics — StorageManager / @skoolsewa/storage owns upload staging and the report-artifact upload type.
• The outbox and its dispatcher — Reports writes outbox_events rows in the same transaction as a job-state change; the platform's outbox dispatcher relays them to BullMQ.
• Notification delivery — NotificationService owns channel fan-out, dedupe and rendering; Reports supplies kind + variables.
• Row-level identity ("is this my child / am I this pupil") on the portal routes — that is GuardianPortalService.resolveOwnChildId and the student portal's own students lookup; Reports only assembles the sections once identity is proven.

Source of Truth

3. Module Composition

ReportsModule is a pure aggregate — it declares no controllers of its own. Every leaf below is what ADMIN_MODULES in apps/api/src/config/swagger/swagger-documents.ts lists (Swagger's include does not recurse through an aggregate), and every leaf now declares real controllers and providers — none are stubs.

Every domain class implementing ReportDomain is constructor-injected into ReportRegistryService, whether or not it contributes report keys today; all fifteen domain classes plus BuilderReports now ship at least one definition — see §17 (the report inventory lives in the features/flows doc, §11).

4. File and Directory Map

apps/api/src/modules/reports/
  reports.module.ts                 the aggregate (no controllers)
  shared/                           the engine every report, builder query and job runs through
    report-types.ts                 every contract type (ReportDefinition, AuthorizedRun, ReportResult, …)
    report-registry.service.ts      assembles every domain class into one key → definition map
    register-taken-sql.ts           registerTakenSql — the one definition of a taken register (session status is not `not_started`)
    report-principal.service.ts     who is asking (live request or background)
    report-authorizer.service.ts    the ONE authorisation entry point for a single report/builder run
    report-composite-authorizer.service.ts   authorisation for overview/profiles (multi-report composite screens)
    report-package-authorizer.service.ts     authorisation for multi-part exports (annual + custom packages)
    report-filters.ts               filter parsing/validation (well-formed, not yet resolved)
    report-filter-resolver.service.ts   public ids → internal ids, BS → AD, periods → dates
    report-reference.service.ts     id resolution + teaching-scope computation
    report-query.service.ts         the guarded read-only transaction (semaphores, timeouts)
    report-cache.service.ts         cache-aside for single report results
    report-settings.service.ts      reads the report_settings singleton
    report-audit.service.ts         explicit audit records the global interceptor can't see
    report-result.ts                builders for kpi/chart/table/warning/emptyResult, suppressTable
    report-sql.ts                   shared SQL scope predicates (used by domain query files)
    metric-definitions.ts           the central METRIC_DEFINITIONS catalogue
    overview-alerts.port.ts         port interface for overview alert widgets
    dto/report-config.dto.ts, dto/report-result.dto.ts   wire DTOs shared across surfaces
  catalog/                          catalog, metrics, filter options, the live-run route
    report-catalog.controller.ts    ReportCatalogController + ReportRunController
    report-catalog.service.ts       catalog filtering, metric listing, filter-bar options
    report-run.service.ts           live-run orchestration: authorize → cache → query → run()
    reports-catalog.module.ts
  domains/                          one class per catalogue domain, each implementing ReportDomain
    students/        9 report keys (students.*, demographics.*)
    calendar/         1 report key (calendar.summary)
    id-cards/         4 report keys (coverage, missing, status, register)
    attendance/        7 report keys (daily … staff_summary)
    leave/            3 report keys (requests, summary, balances)
    academics/         8 report keys (exams.*)
    assignments/       3 report keys (overview, submissions, student_workload)
    promotions/        3 report keys (summary, decisions, progression)
    staff/             5 report keys (directory, distribution, teacher_workload, timetable.*)
    communication/     3 report keys (notices.summary, notices.register, notifications.delivery)
    complaints/        2 report keys (complaints.summary, complaints.register)
    cross-domain/      7 report keys (cross.*), split across five files sharing cross-domain-shared.ts
    finance/           7 report keys (fees_overview, fee_balances, aging, collections, fee_categories, payroll, assets)
    year/              1 report key (year.comparison, spanning 9 comparable metric keys)
  builder/
    builder.reports.ts              BuilderReports — the single `builder.custom` definition
    builder-reports.module.ts       provides/exports BuilderReports
    dataset-registry.ts             BUILDER_DATASETS — the frozen field lists per dataset
    datasets/                       one file per dataset family (people, attendance, academics, leave, school-only)
    builder-compiler.ts             resolveBuilderQuery: validates a body against the chosen dataset
    builder-query-runner.ts         runBuilderQuery: compiles and executes the validated query
    report-builder.controller.ts    ReportBuilderController (GET /reports/builder/datasets, POST /reports/builder/preview)
    report-builder.service.ts       datasets() + preview()
    reports-builder.module.ts       the admin route leaf
  dashboards/
    dashboard-validation.ts         widget/layout shape checks (position bounds, max widgets, shortcut target shape)
    report-dashboard-render.service.ts   runs every widget on a dashboard, isolating per-widget failure
    report-dashboards.service.ts    CRUD, optimistic concurrency, personal-vs-school authorisation
    report-dashboards.controller.ts ReportDashboardsController — 6 routes
    reports-dashboards.module.ts
  saved/
    report-config-validation.ts     validates a saved/template/schedule config against its report definition
    report-definition-support.ts    shared row-mapping between saved reports and templates
    saved-reports.service.ts        a caller's own saved reports (kind='saved')
    saved-reports.controller.ts     SavedReportsController — 7 routes
    report-templates.service.ts     school-wide templates (kind='template')
    report-templates.controller.ts  ReportTemplatesController — 5 routes
    reports-saved.module.ts
  schedules/
    report-schedule-cadence.ts      next_run_at computation per frequency, BS-month arithmetic
    report-schedule-recipients.service.ts   eligibility checks, recipient-options route
    report-schedule-lifecycle.service.ts    activate/pause/archive/run-now, conditional UPDATEs
    report-schedule.mapper.ts       row → ReportScheduleDto, including BS date twins
    report-schedules.service.ts     CRUD, config validation
    reports-schedules.controller.ts ReportSchedulesController — 10 routes
    reports-schedules.module.ts
  settings/
    report-settings-admin.service.ts   read()/update() with the singleton row and version bump
    report-settings.controller.ts   ReportSettingsController — 2 routes
    reports-settings.module.ts
  overview/
    overview-sections.query.ts      per-domain KPI composition for the overview screen
    overview-register-tally.query.ts  class-day registers expected vs taken (taken means a session whose status is not `not_started`, the `registerTakenSql` rule in `shared/register-taken-sql.ts` that the class table and the calendar also read), optionally narrowed by grade, section and class — feeds the overview's attendance coverage KPIs, the unmarked-registers alert and the `coverageRatePct`/`expectedCount` KPIs of `attendance.class_summary`
    overview-alerts.provider.ts / overview-alerts.service.ts   alert widgets (below-threshold, overdue, etc.)
    overview-report-runner.ts       runs an underlying report definition for one overview/closing section
    year-closing-checks.query.ts    the blocking/info checks before closing an academic year
    year-closing-gating.util.ts     which checks a caller sees, gated by their own domain permissions
    reports-overview.service.ts, reports-year-closing.service.ts
    reports-overview.controller.ts  ReportOverviewController — GET /reports/overview, GET /reports/year-closing
    reports-overview.module.ts
  profiles/
    student-profile.service.ts, class-profile.service.ts, teacher-profile.service.ts
    queries/                        one query file per profile section (attendance, results, leave, timetable, …)
    shared/run-domain.util.ts, profile-result.util.ts   composite-section execution and result shaping
    reports-profiles.controller.ts  ReportProfilesController — 3 routes
    reports-profiles.module.ts
  portal/
    reports-portal.service.ts       shared section assembly for both portal controllers
    reports-student-portal.controller.ts    GET /api/mobile/student/reports
    reports-guardian-portal.controller.ts   GET /api/mobile/guardian/children/:studentId/reports
    reports-portal.module.ts
  jobs/                             the export pipeline
    report-jobs.controller.ts       ReportJobsController — 8 routes
    report-jobs.service.ts          create/list/get/events, idempotency, active-job cap
    report-job-actions.service.ts   cancel/retry/delete-artifact/download
    report-job-planner.service.ts   turns a job request into AuthorizedRun[] (single report or package)
    report-job-writer.service.ts    inserts the report_jobs row + outbox event in one transaction
    report-job-access.ts            pure predicates: canView/canManage/canDownload/isRequesterOnlyJob
    report-tx.type.ts               the transaction executor type shared by writer/lifecycle
    dto/report-job.dto.ts
    generation/
      report-generation.service.ts  ReportGenerationService — claims a job, builds the file, finalizes/fails it
      report-job-lifecycle.service.ts  every lease-conditional write to a job row
      report-job-errors.ts          ReportJobFailure, ReportLeaseLost, classifyFailure
      report-export-document.ts     assembles the multi-part export document handed to the renderers
    workers/
      reports-queue.processor.ts    the ONE worker on QueueName.REPORTS (concurrency 1)
      reports-sweep.processor.ts    the ONE worker on QueueName.REPORTS_SWEEP (concurrency 1)
      reports.scheduler.ts          @Cron ticks that enqueue the two sweep jobs
      report-schedule-sweep.service.ts   turns due report_schedules rows into report_jobs
      report-maintenance.service.ts expire artifacts, delete orphan objects, purge history, recover deliveries
    reports-jobs.module.ts
    reports-worker.module.ts        composes the four worker/scheduler providers (outside the fast profile)

Key Files

5. Data Model

5.1 Schema Source

packages/db/src/schema/school/reports.ts     every table below
packages/db/src/migrations/0048_reports_analytics.sql            drizzle-generated DDL
packages/db/src/migrations/0049_reports_analytics_integrity.sql  hand-written: composite FKs, an IMMUTABLE-function CHECK, the append-only trigger, and seeds
packages/db/src/migrations/0050_reports_job_session_index.sql    one added index: report_jobs_session_idx

Three constraints are deliberately not in the drizzle schema file because drizzle-kit cannot express them, and live only in 0049_reports_analytics_integrity.sql:

• report_jobs_requested_role_is_held_fk and report_schedules_run_as_role_is_held_fk — composite foreign keys onto user_role (user_id, role_id) with a column-list ON DELETE SET NULL (role column). Revoking a role must null only the role column; report_schedules.owner_user_id is NOT NULL, so a whole-column SET NULL would raise 23502 on a role revoke. sessions_active_role_is_held_fk uses the same column-list shape, declared in 0058_session_active_role_fk_column_list.sql.
• report_settings_age_bounds_shape — needs an IMMUTABLE SQL function (report_age_bounds_ascending) that a generated migration cannot create.
• The append-only trigger on report_job_events (no UPDATE/DELETE).

0050 adds a single partial btree index, report_jobs_session_idx on report_jobs.academic_session_id (WHERE academic_session_id IS NOT NULL) — it serves the year-closing and overview screens' "jobs for this session" lookups, which 0048/0049 did not anticipate.

5.2 Tables

report_settings

Singleton (one row forever; retained indefinitely).

The admin update route (PATCH /reports/settings) accepts attendanceThresholdPercent (a 0–100 percent with up to 2 decimals) on the wire and stores it as attendance_threshold_bp — the only field with a unit conversion between DTO and column.

report_definitions

Saved reports and school templates. Retained until the owner deletes it (soft); a hard-deleted owner takes their saved reports with them (ON DELETE CASCADE).

Indexes: report_definitions_saved_name_unique (owner + lower(name), partial on kind='saved' AND deleted_at IS NULL) — enforced by SavedReportsService.create as REPORT_SAVED_NAME_TAKEN; report_definitions_template_name_unique (lower(name), partial on kind='template' AND deleted_at IS NULL) — REPORT_TEMPLATE_NAME_TAKEN; report_definitions_owner_idx, report_definitions_template_idx (both partial on live rows, ordered by updated_at DESC, serving "my saved reports" / "school templates" list screens).

SavedReportsService caps a single owner at 100 live saved rows (MAX_SAVED_PER_USER); exceeding it is 409 REPORT_SAVED_LIMIT_REACHED, application-level (not a DB constraint).

report_dashboards

Retained until deleted (soft).

Indexes: report_dashboards_personal_name_unique, report_dashboards_school_name_unique (each partial and case-insensitive on name, live rows) — REPORT_DASHBOARD_NAME_TAKEN; report_dashboards_owner_idx (partial on live rows).

A view: "shortcut" widget's config must validate against isValidShortcutTarget at save time (a target: {reportKey, savedId?, templateId?} object) — it never runs like the other views; the render route returns it unresolved for the client to navigate with. Creating a scope: "school" dashboard needs ReportDashboardsShared codes (see §11); a caller without them can still read a school dashboard (editable: false on the response) if they hold ReportDashboards_READ.

report_schedules

Retained until deleted (soft); a deleted schedule is always archived (report_schedules_deleted_is_archived).

Indexes: report_schedules_due_idx (partial on status='active' AND deleted_at IS NULL, ordered by next_run_at) — the schedule sweep's candidate query; report_schedules_owner_idx, report_schedules_run_as_idx, report_schedules_last_job_idx.

report_schedule_recipients

Retained with its schedule.

Unique: (schedule_id, user_id), (schedule_id, role_id) — a person or role can appear once per schedule. ReportSchedulesService.create/update checks every named user/role for eligibility (the exact canDownload predicate the sweep will later use) before writing the row — an ineligible recipient at create/update time is 400 REPORT_SCHEDULE_RECIPIENTS_INVALID, never silently accepted and skipped later.

report_jobs

The export pipeline's central row. Retained per its own rule: the artifact expires after report_settings.artifact_retention_days; the row is purged job_history_retention_days after finished_at once terminal (failed/cancelled/expired) and it has no report_job_objects left.

Constraints of particular note: report_jobs_expired_was_completed requires an expired row to still carry row_count/expires_at/generated_scope — expiry clears only artifact_key, preserving the historical facts. report_jobs_lease_iff_processing and report_jobs_processing_started together make "processing with no lease" or "processing with no start time" unrepresentable.

Indexes: report_jobs_requester_idx, report_jobs_created_idx, report_jobs_lease_idx (partial, processing), report_jobs_queued_idx (partial, queued), report_jobs_expiry_idx (partial, completed states), report_jobs_purge_idx (partial, terminal-failure states), report_jobs_schedule_idx, report_jobs_active_by_user_idx (partial, queued/processing — backs the 5-active-job cap check), report_jobs_requested_role_idx, report_jobs_session_idx (0050, partial on non-null session).

Unique: report_jobs_schedule_slot_unique on (schedule_id, scheduled_for) — the row-level guarantee that one schedule slot produces exactly one job even under concurrent sweeps. report_jobs_idempotency_unique on (requested_by_user_id, idempotency_key).

report_job_recipients

Retained with its job.

Composite PK (job_id, user_id); index on user_id (backs "exports delivered to me").

report_job_objects

One row per stored object of a job, retained until that object is deleted. job_id FK uses ON DELETE RESTRICT deliberately — a job row cannot be purged while any tracked object still exists, which is exactly the invariant that stops purgeHistory() from producing an orphaned file.

report_job_events

Append-only audit trail of a job's lifecycle. No UPDATE/DELETE trigger (enforced in the integrity migration); removed only when its job is purged (ON DELETE CASCADE).

Index: report_job_events_job_idx on (job_id, id) — the job-history read path.

5.3 Relationship Diagram

6. Services and Responsibilities

6.1 ReportPrincipalService

Permissions resolve from one role — the same role RoleGuard resolves them from — never the union of every role a person holds. A superadmin's permission set is buildPermissionCatalog() (every code), computed once and cached in the instance.

6.2 ReportAuthorizer

The single entry point every single-report or builder run — live, dashboard widget, profile section, job creation, job generation, schedule sweep — must pass through. See §7.1 for the full flow. Key methods: authorize() (builds an AuthorizedRun), canRun() (cheap pre-check for catalog/dataset listing and package "omit" decisions), holdsAll(), requiredCodes().

6.3 ReportCompositeAuthorizer and ReportPackageAuthorizer

ReportCompositeAuthorizer authorises the overview screen, the year-closing screen and the three profile screens — each composes several underlying report definitions, and each section is independently checked through ReportAuthorizer.canRun/authorize; on the overview and year-closing screens a section whose codes the caller lacks is simply absent from the response rather than failing the whole request (profile sections are returned with available: false). authorizeProfile additionally resolves the target (student/class/teacher) and 404s (REPORT_PROFILE_NOT_FOUND) if it does not exist or falls outside the caller's scope — the same "404, never 403" rule as job access, so a profile id space is never an enumeration oracle.

ReportPackageAuthorizer authorises a multi-part export (package.annual_academic, package.custom): every part is authorised independently, an unauthorisable part is silently omitted (contributing PACKAGE_PARTS_OMITTED), and the export as a whole fails with 403 REPORT_DOMAIN_FORBIDDEN only if every part was omitted.

6.4 ReportQueryService

Every transaction runs at REPEATABLE READ, READ ONLY with a transaction-local statement_timeout set via set_config(..., true) — it never leaks to the pooled connection for the next borrower. A global Semaphore caps concurrently-held connections to REPORTS_MAX_CONCURRENT_QUERIES (default 4); a per-user Semaphore caps one user to 2 in-flight (a dashboard render takes both slots via units: 2). Both wait up to 8s before returning "busy". A live (non-export) run additionally has a 15s wall-clock deadline measured from arrival, including the semaphore waits, except a dashboard render, which is given an 18s deadline for its whole multi-widget batch. withDeadline() races the transaction against a timer and still lets the transaction's own statement_timeout fire and roll back even after the deadline has already returned to the caller. Both gates are per API process — this deployment runs one process, so they are also effectively global.

6.5 ReportCacheService

Cache-aside, Redis-backed, soft-fail: a Redis outage falls through to live computation rather than erroring. Only single-report results are cached (getOrCompute), and only after authorisation — the cache key (keyFor) is built from the run's resolved filters (periods already turned into concrete dates), resolved columns, sort, page, size, the compiled builder body, a scope component (sw for school-wide, else t-{staffId}-{sessionIds}, or u-{userId}-{roleId} when cacheScope: "principal"), and the settings version — so a cached value can never leak across scope or a stale threshold. TTL is 120s for analytic reports, 60s for dataset reports. Concurrent identical misses in the same process share one computation via an in-flight Map. assertRefreshAllowed() enforces one bypass-cache refresh per report per user per 10s via a Redis SET NX EX, failing open (allowing the refresh) if Redis itself is unreachable. Dashboard widgets and profile sections call the same cache path per underlying report, so a dashboard with two widgets on the same report+filters shares one cached value.

6.6 ReportSettingsService and ReportSettingsAdminService

ReportSettingsService reads the one report_settings row per request (a single-row PK read, cheap), never caches it in memory — a raised privacy threshold must take effect immediately, not wait out a cache TTL. Throws 503 REPORT_BUSY if the seed row is somehow missing (an installation fault). ReportSettingsAdminService (the admin CRUD layer behind GET/PATCH /reports/settings) validates the DTO's ranges (already enforced by class-validator), converts attendanceThresholdPercent to basis points, applies only the provided fields, and bumps version in the same UPDATE — a settings change is a single-row write, never a sweep over cached values.

6.7 ReportReferenceService

Resolves ten kinds of filter public-id (grade, section, class, subject, department, designation, leaveType, complaintCategory, feeCategory, room) to internal integer ids and names — every lookup 404s (REPORT_REFERENCE_NOT_FOUND) on an unknown id rather than silently dropping it, because a dropped filter would silently widen the report's coverage. It also computes scopeFor(principal, sessionIds) — a school-wide principal gets an unconstrained scope object; a non-school-wide principal's teachingClassIds come from classes where they are class_teacher_id or have an active class_subjects row, restricted to active classes in the given sessions.

6.8 ReportAuditService

Explicit audit records for what ActivityAuditInterceptor (which only sees mutating POST/PATCH/etc requests) cannot see: GET views of profiles/composites/portal reads, downloads, schedule runs. Metadata carries only ids and counts — never names or labels, so a child cannot be identified from the audit trail. Best-effort: swallows a Mongo outage via ActivityRecordService.recordActivity.

6.9 ReportBuilderService and the builder pipeline

datasets(principal) lists every BUILDER_DATASETS entry the caller may query — a dataset whose domain codes (or, for a school_only dataset, school-wide scope) the caller lacks is omitted entirely, mirroring the report catalog. Each returned field carries contact, aggOnly and demographic flags; a contact field is dropped from the list for a caller without ReportsContacts_READ rather than shown and then refused.

preview(user, dto) builds a builder.custom AuthorizedRun through the same ReportAuthorizer.authorize() every other report uses (with builder: dto as the resolve input) and executes it through the same ReportRunService/cache/query path — a builder preview is authorised, cached and rate-limited identically to a catalog report.

builder-compiler.ts's resolveBuilderQuery is where the builder's own invariants live, all surfaced as 400 REPORT_BUILDER_INVALID:

• An aggOnly field may only appear inside an aggregates entry, never as a plain columns/groupBy/row-filters reference.
• A demographic or aggOnly field may not be used in a row-level filter (only in groupBy/aggregates), because filtering individual rows on a demographic attribute is exactly the shape of query small-group suppression exists to prevent.
• groupBy may not include an identifying field (e.g. a name or roll number) in the same query as a demographic field — that combination would re-identify the small groups suppression is meant to hide.
• Groups whose row count falls under report_settings.min_group_size are dropped from the result entirely (not blanked-but-shown, since a builder result has no fixed column set to blank a cell in).
• GROUP BY compiles to the field's own SQL expression (not an alias), and every result carries a deterministic ORDER BY tiebreak (group key, then a stable column) so paginated builder pages never reorder between requests.

6.10 ReportDashboardsService and ReportDashboardRenderService

ReportDashboardsService owns CRUD: list() returns the caller's personal dashboards plus every school dashboard (editable: false on a school dashboard the caller cannot edit); create/update/remove enforce the optimistic-concurrency version check and, for scope: "school", the ReportDashboardsShared codes. remove accepts an optional expectedVersion query parameter — if given it must match, if omitted the delete is unconditional (last-writer-wins), unlike every other version-checked mutation in this module.

ReportDashboardRenderService.render(user, id, dto) loads the dashboard, merges dto.filters over default_filters, and runs every widget concurrently (bounded — see §6.4's concurrency-2/18s-deadline note): a kpi/bar/stacked_bar/line/scatter/donut/table widget runs its reportKey through the normal single-report path; an alerts widget runs the fixed overview.alerts composite; a shortcut widget is never executed — it returns its stored target unresolved for the client to navigate to. Any widget whose run throws an HttpException reports that as its own DashboardWidgetResultDto.error; a non-HTTP error is logged and reported the same way — the rest of the dashboard still renders, so one broken widget never blanks the whole screen.

6.11 SavedReportsService and ReportTemplatesService

Both validate config against the target report's definition via report-config-validation.ts (unknown filter/column/sort keys are rejected the same way a live run would reject them) before writing. SavedReportsService scopes every read/write to owner_user_id = caller; ReportTemplatesService has no owner column to scope by — every template is school-wide, so its mutating routes require the dedicated ReportTemplates_* codes rather than an ownership check. list() marks each row runnable: false if the caller no longer holds the codes its reportKey needs (a saved/template row is never deleted just because a permission changed under it). Every mutating route (archive, restore, update, delete) requires expectedVersion and 409s (REPORT_VERSION_CONFLICT) on a mismatch, checked inside the UPDATE ... WHERE version = $expectedVersion itself, not via a separate read-then-compare.

6.12 ReportSchedulesService, ReportScheduleLifecycleService and ReportScheduleRecipientsService

ReportSchedulesService.create/update validates config (as above), the cadence fields (weekday present iff weekly, dayOfMonth iff monthly/monthly_bs), and — via ReportScheduleRecipientsService — every named user/role recipient's eligibility to receive this exact report+config, before the row is written. ReportScheduleLifecycleService owns every state transition (activate, pause, archive, run-now, remove) as a conditional UPDATE ... WHERE status = <expected> [AND version = $expectedVersion]; runNow re-plans and creates a manual-origin report_jobs row through the same planner a POST /reports/jobs call uses, so a manual "run now" is indistinguishable in the job pipeline from an ordinary export request. recipientOptions (GET /reports/schedules/recipient-options) runs the identical eligibility predicate the sweep will later apply, bounded by a candidate pool and result limit, so the picker never offers someone who would be silently skipped at run time.

6.13 Overview, year closing and profiles

ReportOverviewService composes one OverviewSectionDto per domain (students, attendance, academics, assignments, promotions, complaints, finance, staff, id_cards, communication) by running a small fixed set of underlying report/metric queries per section and layering OverviewAlertsService's threshold-driven alerts (below-attendance-threshold classes, overdue fees, schedules needing attention, etc.) on top; a section the caller lacks codes for is skipped (if (!spec.allowed) continue) and is absent from the sections array, so the client renders only the sections it may see. Every section that is built has available: true; with no resolvable academic session the allowed sections are returned with empty kpis/alerts. ReportYearClosingService additionally requires ReportsSchoolWide_READ (enforced by ReportCompositeAuthorizer, 403 REPORT_SCOPE_FORBIDDEN otherwise) and composes a set of blocking/info YearClosingCheckDto rows (open leave/complaints, unpublished exams, missing ID cards, etc.) plus summary KPI sections — each check or summary section the caller lacks the underlying domain permission for is omitted from the response entirely, not shown as a locked row, matching the report catalog's own "never a greyed-out placeholder" rule. Every YearClosingCheckDto carries a unit field, "count" or "paisa", telling the client how to render its count value — every check is "count" except FEES_OUTSTANDING (label "Outstanding fee balance", reportKey: "finance.fee_balances", severity "info"), whose count is a sum of outstanding balances in paisa, not a number of pupils.

StudentProfileService/ClassProfileService/TeacherProfileService each assemble a fixed set of ProfileSectionDto entries (attendance, results, assignments, leave, complaints, timetable, etc., varying by profile kind) via run-domain.util.ts, which authorises and runs each underlying report/query independently — a section the caller cannot see is available: false, result: null.

?sessionId= selects the session for session-scoped sections. Behaviour differs by profile kind because a class belongs to exactly one session while a student or teacher can be looked up regardless of session:

• Student profile: sessionId must be a session visible to the caller (resolved against the sessions the caller's scope can see); an unknown or out-of-scope id is 404 REPORT_PROFILE_NOT_FOUND (student-profile.service.ts) — the identity layer's rule, so a session id is not an enumeration oracle either.
• Class profile: a class belongs to exactly one session, so ?sessionId= cannot select a different one — a mismatched id is 400 REPORT_FILTER_INVALID (class-profile.service.ts:76-79), not a 404, because the class does exist, the filter is simply incoherent.
• Teacher profile: any session is acceptable (a teacher's identity is not session-scoped); an unresolvable id still 404s.

Within a class profile, student-attendance-section.util.ts/class-sections-grid.query.ts compute a class sections grid scoped to composite.visibleClassIds (school-wide callers get null, meaning every sibling class; a teaching-scoped caller's siblings are restricted to their own visible classes); small groups are suppressed the same way any other breakdown is, and classId on every row is the class's public_id (UUID), never the internal integer id. A subject teacher (holding Students_READ/Reports* but not the class-teacher relationship) sees only published exam results in the results section, and sees overall (cross-subject) results only for classes where they are the class teacher; student-results.query.ts passes publishedOnly: !schoolWide and restricts classTeacherClassIds to the composite scope's own list for a non-school-wide caller.

6.14 ReportsPortalService

Shared by both portal controllers: given (actorUserId, studentId), it assembles a fixed PortalReportsDto — placements (current grade/section/roll/session), and four report sections (attendance, results, assignments, promotions) — running each through the normal authorised report path as the pupil's own record, never gated by a permission code (the portal carries none). Two independent identity checks gate access before this service ever runs:

• Student portal (GET /api/mobile/student/reports): assertPortalAudience(actor, "student") proves the caller is in the student scope kind; the controller then looks up students by userId = actor.id AND deleted_at IS NULL itself (no PeopleAccessService.scopeFor, per the portal's own rule) — a miss is 404 STUDENT_NOT_FOUND.
• Guardian portal (GET /api/mobile/guardian/children/:studentId/reports): assertPortalAudience(actor, "guardian"), then GuardianPortalService.resolveOwnChildId(actor, studentId) — the identity-layer predicate that is the authorization decision for "is this my child" — 404s the same way on a mismatch, never 403, so the studentId path parameter is not an enumeration oracle over the pupil roll.

Assignment counts inside the portal's assignments section are pre-filtered to what a pupil should actually see as "my work": only assignments that are published or closed (never a draft the teacher hasn't released) and, for anything counted as "already due", only work whose due date has passed as of the query time — an assignment due tomorrow is not counted as outstanding today. Attendance's "expected days" figure is bounded at today: it never counts school days in the future as days a pupil should already have an attendance mark for.

6.15 Percentage arithmetic

Every report percentage is computed by pct(n, d) in apps/api/src/utils/reports/report-math.util.ts: null when the denominator is not positive, a RangeError for a negative or fractional count, otherwise the share rounded to two decimals. pctStrict(n, d) in the same file is pct with one difference: a share strictly between none and all (0 < n < d) is clamped to [0.01, 99.99], so a rounded result never reads 0 or 100 while some, but not all, records qualify. n === 0 stays 0 and n === d stays 100.

Attendance shares (rate, absence, late, excused, staff rate, register coverage) use pctStrict. Other percentages (pass, promotion, completion, collection, per-100) use pct and are not clamped. A comparison between two rates, such as the "unusually high absence" test in attendance.absence_analysis (a class rate at least 1.5 times the scope rate, and above zero), runs on the pct values; only the value a KPI or cell shows is clamped.

7. Runtime Flows

7.1 Authorisation (every single-report or builder run passes through this exact order)

7.2 Live report run (POST /reports/run) and builder preview (POST /reports/builder/preview)

Branch notes: a cache-bypassing refresh: true first calls assertRefreshAllowed (429 REPORT_REFRESH_RATE_LIMITED if used again within 10s for the same report+user). IpThrottlerGuard additionally caps both routes at 60 requests/minute per user (RATE_LIMITS.ADMIN_REPORT_RUN).

7.3 Export job creation (POST /reports/jobs)

Authorisation happens outside the advisory-locked transaction deliberately: holding the per-user lock across the filter/reference lookups would serialise a user's concurrent requests behind their own slowest lookup. The lock only needs to cover the idempotency check + active-count + insert, which is the actual race. ReportScheduleLifecycleService.runNow and the schedule sweep both go through the same planner and writer, so a manual, "run now" and scheduled job are created identically once past the planning step.

7.4 Export generation (worker side)

buildExportDocument composes the title block every rendered file carries, including schoolIdentifiers — a single line reading "IEMIS <code> · PAN <pan>", either half alone when only one is set, or null when the school profile has neither. report-generation.service.ts's own school() reads iemisCode/panNumber from the school_profile singleton alongside the name/timezone/logo it already fetches, trims each, and maps a blank to null; buildExportDocument then folds both into schoolIdentifiers via a private schoolIdentifiersOf helper. All three renderers (render-csv.util.ts, render-pdf.util.ts, render-xlsx.util.ts) print it the same way: immediately below the schoolName line in the header block, and only when it is not null — a school with neither an IEMIS code nor a PAN number gets no identifiers line at all rather than a blank one.

Failure branch: any thrown error → handleFailure. A ReportLeaseLost (the job was cancelled or reclaimed mid-run) logs and returns silently — no notification, because the new owner of the job will send one. Any other error is classified (classifyFailure) into a category + retryable flag; if retryable, attempts remain, and this is not the second timeout in a row, the lifecycle writes attempt_failed and returns the job to queued for BullMQ's own backoff to redeliver — then the generator still throws (a plain Error) so BullMQ's retry machinery fires. Otherwise the lifecycle writes the terminal failed state and the generator throws UnrecoverableError so BullMQ never retries and ReportsQueueProcessor.onFailed records a job_failures row unconditionally.

7.5 Schedule sweep (every minute)

A run always fires for the latest due slot and never back-fills: a server down for three days produces one report on restart, not three. Candidates are read without a lock so two API replicas split the work via SKIP LOCKED on the per-row lock, rather than blocking on each other; one bad schedule's failure (rolled back to a fresh pause() transaction) never rolls back another candidate's success. ReportScheduleRecipientsService.resolveRecipients (step P) expands role recipients to live members and drops anyone who no longer passes canDownload for this exact job — each drop is recorded as a recipient_skipped event with a reason, never a silent omission.

7.6 Maintenance sweep (every 15 minutes)

Order is deliberate: expiring first turns artifacts into orphans (so step 2 has something to clean), deleting orphans first unblocks report_job_objects' ON DELETE RESTRICT before step 3 tries to purge the row, and recovery runs last. Each step is one bounded batch (EXPIRE_BATCH=100, OBJECT_BATCH=200, PURGE_BATCH=500, RECOVER_BATCH=50) — a large backlog drains over several ticks rather than one long transaction. Recovery has two independent triggers: (1) a processing job whose lease expired more than 5 minutes ago (LEASE_GRACE_MINUTES) — the worker holding it likely died; (2) a queued job older than 15 minutes (QUEUED_STALE_MINUTES) whose outbox row is not pending and whose corresponding BullMQ job (looked up by outboxJobId(dispatch_key), the same outbox-<dispatch_key> id the dispatcher enqueued under) is not in a live state — the delivery was lost somewhere between the outbox and the queue. A job that has already been recovered 5+ times and is over 24 hours old is given up on (abandoned, terminal failed with error_category: internal, reason: undeliverable) rather than resent forever.

8. Caching

Both are read/written through RedisCacheService's getSoft/setSoft (fails open on a Redis error — the report is simply computed live) except the refresh limiter, which uses the raw REDIS_CLIENT SET NX EX directly and explicitly catches and logs a connection error, allowing the refresh through. There is no cache for dashboards/profiles/overview as a whole — only their constituent per-report results are cached, so a dashboard's cache-hit ratio depends on how many of its widgets share a report+filters combination with something else that already ran.

9. BullMQ, Schedulers, and Async Work

The export queue is its own queue at worker concurrency 1, deliberately separate from every other queue in the system: an export can hold a read-only transaction for up to two minutes, and it must never delay notifications, attendance, or fee background work. It is enqueued only through the outbox — a direct queue.add() in the request handler would let the write commit while the enqueue silently fails. The sweep queue is separate again, so a long-running export never delays the next schedule tick.

10. Realtime and Events

Reports & Analytics emits no realtime/SSE events. All lifecycle communication is via NotificationService (in-app + email), never RealtimeService.

Every kind is repeatable (a job can finish, fail, retry, and finish again — each is a separate fact) and its dedupe.requestId is chosen to distinguish repeats (ready, failed-{retryCount}, scheduled-ready, attention-{episode}). Variables never carry report data or a person's name — only titles, labels and urls, because a notification payload is not gated by the report's own permission codes.

11. Security, Auth, and Abuse Controls

• Guards: JwtAuthGuard (global) + RoleGuard on every controller; IpThrottlerGuard additionally on ReportRunController, ReportBuilderController.preview, and ReportDashboardsController.renderDashboard.
• Permissions: every single report requires Reports_READ and every code in ReportDefinition.requires, checked with holdsAll() (superadmin bypasses via isSuperadmin). A school-wide report additionally needs ReportsSchoolWide_READ; a contact column needs ReportsContacts_READ. Every management surface (saved reports, templates, dashboards, schedules, settings, builder) has its own CRUD permission module (see §5 of the API doc) independent of the domain codes needed by whatever reportKey it references.
• Scope enforcement: rows are limited to the caller's ReportScope (teaching classes, or their own staff row) unless schoolWide. Filters can only narrow this scope, never widen it — checkNarrowingFilters 403s a class/pupil/staff filter outside it. The builder enforces the identical rule per dataset.
• Rate limits: ADMIN_REPORT_RUN — 60 requests/minute, keyed per user, shared by the live-run route, the builder preview and the dashboard render route (a school's staff often share one NATed IP, so IP-keying would be too coarse); ADMIN_REPORT_EXPORT on job creation; the report-refresh limiter (10s per report per user via Redis).
• Contact-data gating: any column marked contact: true (phone, email, guardian phone/email) is excluded from defaultColumns and 403s (REPORT_COLUMN_FORBIDDEN) if explicitly requested without ReportsContacts_READ — checked in both column resolution and sort resolution, and identically for builder fields.
• Small-group suppression: suppressTable() (built on suppressSmallCells) blanks any count cell below report_settings.min_group_size in demographic/gender/ethnicity/mother-tongue/disability breakdowns, with dependentKeys also suppressing derived percentage columns. The builder drops whole groups under the same threshold rather than blanking a cell, since a builder result has no fixed column set.
• 404-not-403 on job/profile access: jobNotFound() and ReportCompositeAuthorizer.authorizeProfile's 404 path are both thrown for "does not exist" and "you may not see it" alike — an id space (job publicId, student/class/teacher id) must not become an existence oracle.
• Download re-checks permissions at download time, not at generation time: canDownload() re-verifies every code in the job's stored required_codes (+ contacts) against the current caller's permissions and, if the job was school_wide, that they currently hold ReportsSchoolWide_READ — a role revoked after a file was generated can no longer retrieve it.
• isRequesterOnlyJob: a job generated in teaching scope, or built from a principal-cache-scoped definition, or a builder query on a non-school_only dataset, is downloadable only by its requester, never by a schedule recipient — because its content depends on who asked.
• Idempotency: Idempotency-Key header (8–128 chars [A-Za-z0-9_-]) on job creation is scoped per user; a repeat with a different body 409s (REPORT_JOB_IDEMPOTENCY_CONFLICT) rather than silently returning a mismatched job. Schedule-originated jobs never carry an idempotency key (enforced by both the DTO layer's absence and the DB CHECK).
• Optimistic concurrency: every mutating route on saved reports, templates, dashboards, and schedules (except dashboard delete, where it is optional) requires the caller's expectedVersion, checked inside the UPDATE ... WHERE version = $expectedVersion itself — a stale write is 409 REPORT_VERSION_CONFLICT, never a silent overwrite.
• Recipient eligibility is checked twice: once at schedule create/update time (400 REPORT_SCHEDULE_RECIPIENTS_INVALID for an ineligible name), and again by the sweep at run time (a recipient_skipped event, never an error) — a permission revoked between those two moments degrades to a skip, not a broken schedule.
• Portal identity, not permission: the two portal routes carry no @Permissions() at all; assertPortalAudience plus an identity-layer row lookup (resolveOwnChildId, or the student's own users.id match) is the entire control, and both 404 rather than 403 on a mismatch.
• File names: contentDisposition() builds RFC 5987 filename*=UTF-8''... so a Devanagari report title survives into the downloaded file name; ASCII fallback strips non-printable/quote characters.
• Response headers on download: Cache-Control: no-store, private and X-Content-Type-Options: nosniff are set explicitly — an export can contain personal data and must never be cached by an intermediary or sniffed into an executable content type.

12. Error Handling

Every REPORT_* code, from apps/api/src/common/types/error-codes.ts (verified this session — all are now live; none are reserved for a future route):

13. Observability

14. Testing and Validation

Validation commands (per repo convention):

pnpm --filter @skoolsewa/api test              # includes every reports integration spec
pnpm --filter @skoolsewa/api test:structure     # route/guard fingerprint — verifies every controller's routes
pnpm check-types                                 # after: find apps packages -name '*.tsbuildinfo' -delete

See Also

• Features and flows doc: /docs/developer/reports/feature
• API doc: /docs/developer/reports/api