Analytics Engine

This is the architecture reference for OptiLearn's analytics engine. If you're building a dashboard, adding a new reportable entity, debugging an audit log entry, or wondering how tenant isolation is enforced at the SQL level — this is the doc.

TL;DR

• Every chart, KPI, and custom report runs through one endpoint: POST /api/analytics/reports/run
• Every request compiles a ReportDefinition DSL (JSON) into Prisma queries or parameterized SQL
• institutionId is injected from the session, never from the request body
• Role-based scope (self / ownCourses / institution) is always ANDed on top of the tenant guard
• The executor has a hard guard that refuses to run raw SQL without a literal "institutionId" = $1 clause
• Every run writes a row to LMSAuditLog

File Map

src/types/analytics.ts                  DSL types — ReportDefinition, filters, result shapes
src/lib/analytics/dsl.ts                Zod validator for ReportDefinition
src/lib/analytics/filter-logic.ts       Parser for "(1 AND 2) OR 3" expressions
src/lib/analytics/entities.ts           Entity registry (10 entities, allowlisted fields)
src/lib/analytics/scoping.ts            Role-based where-fragment builders (Prisma + raw SQL)
src/lib/analytics/query-builder.ts      DSL → Prisma where/select/orderBy
src/lib/analytics/executor.ts           3-mode query engine (rows / categorical / temporal)
src/lib/analytics/audit.ts              LMSAuditLog writes
src/lib/analytics/presets/index.ts        Preset type + builder interface
src/lib/analytics/presets/platform.ts     Platform dashboard presets
src/lib/analytics/presets/course.ts       Course dashboard presets
src/lib/analytics/presets/student.ts      Student dashboard presets (Phase 6)
src/lib/analytics/presets/engagement.ts   Engagement dashboard presets (Phase 6)
src/lib/analytics/presets/compliance.ts   Compliance dashboard presets (Phase 6)
src/lib/analytics/feature-flag.ts         LMSSettings.enableReportsBuilder gate (Phase 6)
src/lib/analytics/schedule-cadence.ts   computeNextRunAt for LMSReportSchedule
src/lib/analytics/system-runner.ts      "Run as creator" wrapper for cron jobs
src/lib/analytics/alert-evaluator.ts    Threshold matcher for LMSReportAlert
src/lib/analytics/cache.ts              Redis cache layer (Phase 5)
src/lib/analytics/snapshot-rewriter.ts  Rewrite temporal DSL to hit DailyMetricSnapshot
src/lib/analytics/opticrm-resolver.ts   Batch fetch student/user names from OptiCRM
src/lib/analytics/export/render.ts      Result → 2D table view (shared by all formats)
src/lib/analytics/export/csv.ts         CSV serializer (RFC 4180, BOM)
src/lib/analytics/export/xlsx.ts        XLSX serializer (SheetJS)
src/lib/analytics/export/pdf.ts         PDF serializer (jspdf + autotable)
src/lib/permissions.ts                  canBuildReports, scopeAnalytics

src/app/api/analytics/reports/run/route.ts        POST — run a DSL
src/app/api/analytics/reports/export/route.ts     POST — run + serialize as CSV/XLSX/PDF
src/app/api/analytics/reports/route.ts            GET/POST — list/create saved reports
src/app/api/analytics/reports/[id]/route.ts       GET/PATCH/DELETE — manage one report
src/app/api/analytics/reports/[id]/schedules/route.ts   GET/POST — list/create schedules
src/app/api/analytics/reports/schedules/[id]/route.ts   PATCH/DELETE — manage one schedule
src/app/api/analytics/reports/[id]/alerts/route.ts      GET/POST — list/create alerts
src/app/api/analytics/reports/alerts/[id]/route.ts      PATCH/DELETE — manage one alert
src/app/api/analytics/entities/route.ts           GET — entity registry for the builder UI
src/app/api/analytics/courses/[id]/route.ts       Legacy course-meta route (lesson engagement list)
src/app/api/analytics/dashboard/route.ts          Legacy platform dashboard (OptiCRM sync compat)
src/app/api/cron/report-schedules/route.ts        Hourly cron — fires due schedules + alerts
src/app/api/cron/analytics-snapshot/route.ts      Hourly cron — populates DailyMetricSnapshot
src/app/api/analytics/dashboard/revenue/route.ts  Bespoke revenue join endpoint (Phase 6)

src/app/(app)/analytics/page.tsx                      Platform dashboard
src/app/(app)/analytics/students/page.tsx             Student dashboard (Phase 6)
src/app/(app)/analytics/engagement/page.tsx           Engagement dashboard (Phase 6)
src/app/(app)/analytics/compliance/page.tsx           Compliance dashboard (Phase 6)
src/app/(app)/analytics/revenue/page.tsx              Revenue dashboard (Phase 6, bespoke endpoint)
src/app/(app)/courses/[id]/analytics/page.tsx         Course dashboard
src/app/(app)/analytics/reports/page.tsx              Saved reports list
src/app/(app)/analytics/reports/new/page.tsx          New report builder
src/app/(app)/analytics/reports/[id]/page.tsx         Saved report viewer
src/app/(app)/analytics/reports/[id]/edit/page.tsx    Saved report editor

src/components/analytics/KpiCard.tsx                  KPI card with loading/error/delta states
src/components/analytics/ReportChart.tsx              Recharts dispatcher for 8 chart types
src/components/analytics/ChartCard.tsx                Card wrapper with loading/empty/error
src/components/analytics/DashboardGrid.tsx            Layout primitives
src/components/analytics/DateRangePicker.tsx          Date range preset selector
src/components/analytics/builder/ReportBuilder.tsx    4-step wizard
src/components/analytics/builder/builder-helpers.ts   Pure state-transition helpers
src/components/analytics/ExportButton.tsx             Export dropdown (CSV/XLSX/PDF)
src/components/analytics/SchedulesPanel.tsx           Schedule list + create dialog
src/components/analytics/AlertsPanel.tsx              Alert list + create dialog

src/hooks/useReportRun.ts               Client hook for /api/analytics/reports/run

The ReportDefinition DSL

A ReportDefinition is a JSON object that fully describes a report. The executor compiles it into either Prisma calls or raw SQL and returns rows or buckets.

interface ReportDefinition {
  version: 1;
  entity: ReportEntity;          // e.g. "enrollment"
  fields: string[];              // DSL field names; used in rows mode
  filters: ReportFilter[];
  filterLogic?: string;          // "(1 AND 2) OR 3"
  groupings: ReportGrouping[];   // 0 or 1 in v1; 2 planned
  aggregations: ReportAggregation[];
  orderBy?: ReportOrderBy[];
  limit?: number;                // soft-capped to 10,000
  chartType: ReportChartType;
}

Why it deliberately omits institutionId

The DSL has no institutionId field. The executor always pulls it from the authenticated session. If you pass one in the body, it's silently ignored. This is the first-line defense against multi-tenant data leaks — you literally cannot express "give me data from a different institution" in the DSL.

ReportFilter

interface ReportFilter {
  field: string;        // Must be allowlisted on the entity
  op: FilterOperator;
  value: FilterValue;
}

type FilterOperator =
  | "eq" | "neq"
  | "gt" | "gte" | "lt" | "lte"
  | "in" | "notIn"
  | "contains" | "startsWith"
  | "between"
  | "isNull" | "isNotNull"
  | "relativeDays";

Every operator has a Prisma compilation and a raw SQL compilation in query-builder.ts and executor.ts. Adding a new operator means adding entries in both places.

relativeDays is the interesting one — it's a DSL-only operator, not a Postgres feature. The executor converts { field: "createdAt", op: "relativeDays", value: 30 } into a bind pair [now - 30d, now] and emits BETWEEN. This lets presets like "Enrollments in last 30 days" work without the caller having to compute timestamps.

ReportAggregation

interface ReportAggregation {
  alias: string;        // Output column name
  fn: AggregationFn;    // "count" | "countDistinct" | "sum" | "avg" | "min" | "max"
  field?: string;       // Required for all fns except "count"
}

ReportGrouping

interface ReportGrouping {
  field: string;
  granularity: GroupingGranularity;  // "day" | "week" | "month" | "quarter" | "year" | "field"
}

field granularity means "group by the field as-is" (categorical). Time granularities require the field to be of type date — the executor calls DATE_TRUNC(granularity, field) in SQL mode.

In v1 you can have 0 or 1 grouping. Two-dimensional grouping is planned. If you send groupings.length > 1 the executor throws DSLValidationError.

The Executor — Three Modes

The executor (src/lib/analytics/executor.ts) dispatches based on the DSL shape:

Rows mode

async function runRowsMode(spec, def, where, limit, started): Promise<ReportRunResult>

Calls prisma[delegate].findMany({ where, select, orderBy, take: limit + 1 }). The +1 lets us detect truncation. Row shaping maps Prisma columns back to DSL field names if the field spec has a column alias.

Categorical mode

async function runCategoricalMode(spec, def, where, limit, started): Promise<ReportRunResult>

If groupings[0] is present, calls prisma[delegate].groupBy({ by: [groupCol], where, _count, _sum, _avg, _min, _max, take: limit + 1 }). Otherwise (pure aggregation), calls prisma[delegate].aggregate({ where, _count, _sum, ... }) which returns one row.

countDistinct caveat: Prisma's groupBy doesn't support COUNT(DISTINCT ...) — it only counts non-null occurrences per group via _count: { fieldName: true }. In categorical mode the executor emits a warning on the result but still runs the approximate query. Time-bucketed mode uses proper SQL COUNT(DISTINCT ...).

Temporal mode

async function runTemporalMode(spec, def, user, scope, limit, started): Promise<ReportRunResult>

Builds a parameterized SQL query like:

SELECT
  date_trunc('day', "createdAt") AS bucket,
  COUNT(*)::bigint AS agg_0
FROM "Enrollment"
WHERE "institutionId" = $1
  AND "courseId" IN (SELECT id FROM "Course" WHERE "createdById" = $2 AND "institutionId" = $3)
  AND "createdAt" BETWEEN $4 AND $5
GROUP BY 1
ORDER BY 1
LIMIT 1001

The bind layout is:

1. $1 — user.institutionId (tenant guard, always first)
2. $2..$N — scope binds from buildScopeSqlClause (if scope ≠ institution)
3. $N+1.. — user filter binds from buildFilterSql

The executor has a hard guard that checks the emitted SQL for the literal string "institutionId" = $1 and throws if it's missing. Even if someone refactors the query builder and accidentally removes the tenant clause, the runtime rejects it.

Tenant Isolation — Four Layers

1. DSL has no institutionId. Cannot be expressed.
2. Executor injects institutionId from session. Pulls from opts.user.institutionId, never from request body.
3. AND composition. The final where clause ANDs three fragments: { institutionId }, scope fragment, user filter where. Any of them evaluating to false → no results.
4. Raw SQL hard guard. Temporal mode's emitted SQL must contain "institutionId" = $1 literally. If it doesn't, throws before running.

Additionally, all PII-marked fields on entities are filtered from the /api/analytics/entities response for non-admins, and rejected at DSL validation time if a non-admin tries to reference them.

Role Scoping

scopeAnalytics(user) in src/lib/permissions.ts returns one of:

• "institution" — admin/super admin see everything
• "ownCourses" — teacher/HOD/principal see only rows touching their own courses
• "self" — students/contacts see only their own rows

The scope is resolved once per request and passed into executeReport(def, { user, scope, ... }). From there, buildScopeFragment (for Prisma modes) or buildScopeSqlClause (for SQL mode) produces a where fragment that gets ANDed with the user filters.

Scope fragments by entity + scope

The raw SQL equivalents use subqueries (courseId IN (SELECT id FROM Course WHERE createdById = ? AND institutionId = ?)) since SQL mode can't express Prisma relation filters.

The Entity Registry

Each EntitySpec in src/lib/analytics/entities.ts is a typed object:

interface EntitySpec {
  name: ReportEntity;
  label: string;
  description: string;
  delegate: PrismaDelegate;          // e.g. "enrollment"
  fields: Record<string, EntityField>;
  supportedChartTypes: readonly ReportChartType[];
}

interface EntityField {
  name: string;
  label: string;
  column?: string;                   // If different from DSL name
  type: "string" | "int" | "float" | "bool" | "date" | "enum";
  enumValues?: readonly string[];
  filterOps: readonly FilterOperator[];
  aggregations: readonly AggregationFn[];
  pii?: boolean;                     // Hidden from non-admin viewers
}

Currently registered entities:

• enrollment — Enrollment model (id, courseId, studentId, status, progress, enrolledAt, completedAt, lastAccessedAt, finalScore, passed, enrollmentMethod)
• course — Course model (title, status, level, categoryId, language, price, totalEnrollments, completionRate, averageRating, estimatedDuration, createdAt, publishedAt)
• student — derived from Enrollment (distinct students with aggregated activity)
• lessonProgress — per-lesson completion records
• quizAttempt — individual quiz attempts with score/percentage/passed
• assignmentSubmission — submission records with grading fields
• certificate — issued certificates
• liveSessionAttendance — attendance records
• complianceRecord — mandatory training records
• forumPost — discussion activity

Adding a New Reportable Entity

It's a TypeScript-only change — no Prisma migration needed. The entity registry is a runtime concept built on top of the existing schema.

Adding a New Filter Operator

Audit Log

Every run, create, update, and delete writes an LMSAuditLog row via src/lib/analytics/audit.ts. The audit write is wrapped in a try/catch — a failure to audit must never break the user-facing operation. Log fields:

{
  action: "report.run" | "report.create" | "report.update" | "report.delete" | ...
  resource: "report",
  resourceId: savedReportId | "adhoc",
  userId,
  userEmail,
  newValues: { entity, mode, rowCount, executionMs, truncated, ... },
  ipAddress,
  userAgent
}

LMSAuditLog existed since Sprint 10 but wasn't written to by any code path. The analytics engine is the first user. Other surfaces should migrate to this pattern.

Presets

Presets are pure functions that return a ReportDefinition JSON. They live in src/lib/analytics/presets/ and are organized by dashboard:

interface Preset {
  id: string;
  title: string;
  description?: string;
  build: (params: PresetParams) => ReportDefinition;
  layout: "kpi" | "chart" | "chart-wide";
  icon?: string;
}

The dashboard pages import presets directly and pass them to useReportRun:

const total = useReportRun(
  useMemo(() => kpiTotalEnrollments.build(params), [params])
);

Because presets are pure, you can unit-test them by asserting the definition shape. No mocking needed.

Performance Notes

Query timeouts

Enforced in Phase 5 — every raw SQL path runs under a Postgres transaction with SET LOCAL statement_timeout = 15000. The runRawWithTimeout(sql, binds) helper in executor.ts wraps the original $queryRawUnsafe call, so a runaway query gets canceled at 15 seconds and the caller sees a 500 rather than an indefinite hang.

The timeout only applies to the temporal and snapshot code paths, because those are the ones that compose raw SQL. Rows/categorical mode uses Prisma's typed query API which has its own per-connection timeout behavior.

Caching

Phase 5 adds a Redis cache layer with SHA256-keyed lookups:

• Key shape: analytics:run:<institutionId>:v<version>:<scope>:<userId>:<sha256>. The hash is computed over a canonicalized JSON of the DSL so two equivalent definitions with different key orderings collide. The version prefix is a per-institution counter bumped on every mutation that would invalidate stale cache.
• TTLs: 60 seconds for live queries, 300 seconds for snapshot-served results. Snapshot results are safe to cache longer because the snapshot table itself is only refreshed hourly by the cron.
• Invalidation: bumpAnalyticsVersion(institutionId) in src/lib/analytics/cache.ts increments the per-tenant version counter. Call it from any mutation that should invalidate cached runs (new enrollment, lesson completion, certificate issued, etc.) — the next read builds a key with the new version, so every pre-bump key is effectively orphaned without having to delete them one at a time.
• Fail-soft: every cache read and write is wrapped in try/catch. If Redis is unreachable, the executor falls through to the live path; callers never see an error.

useReportRun on the client still has no built-in cache — it re-fetches on every definition change. The server-side cache covers the "same user refreshes a dashboard" case which is where real duplicate work happens.

Snapshot rewriter

DailyMetricSnapshot is a wide denormalized table with one row per (institutionId, date, dimension, dimensionId). Columns like newEnrollments, completions, quizAttempts, certificatesIssued are pre-aggregated counts computed by the hourly cron at /api/cron/analytics-snapshot.

The snapshot rewriter (src/lib/analytics/snapshot-rewriter.ts) inspects a temporal DSL and, when it matches a known metric mapping, returns a raw SQL query that reads from the snapshot table instead of the live table. The executor calls it at the top of runTemporalMode; if rewriteAsSnapshot returns non-null, runSnapshotMode executes the rewritten query and returns a ReportRunBucketsResult that looks identical to the live path (same keys, same series, plus a warnings entry noting the snapshot source).

Criteria for rewrite (v1):

1. Exactly one temporal grouping (day/week/month/quarter/year).
2. Exactly one aggregation whose (entity, fn, field?) tuple maps to a known snapshot column (see METRICS in snapshot-rewriter.ts).
3. Scope is "institution" (per-course and per-student snapshots are Phase 5.1).
4. No filters other than (optionally) a date-range filter on the grouping field, which is translated into a date BETWEEN on the snapshot table.

Any DSL that doesn't match all four falls through to the live SQL path. This means the rewriter is opt-in by query shape — there's no config to maintain; new queries that happen to match the criteria automatically benefit.

Expected speedup: a "count of enrollments per day for the last 90 days" query that used to scan ~10k enrollments per run now reads ~90 rows from the snapshot table. In practice, 10–100× depending on the live table size.

Snapshot writer cron

/api/cron/analytics-snapshot runs hourly. For each institution with any enrollment activity:

1. Computes yesterday's snapshot row (stable — rewritten once per tick to catch late-arriving rows).
2. Computes today's partial snapshot (overwritten on every tick so the latest numbers are always available without waiting a day).
3. Calls bumpAnalyticsVersion(institutionId) to invalidate every cached query for that tenant.

Run order is institution-by-institution, not parallel, to bound concurrent Prisma connections. If a single institution fails, the cron logs the error and continues.

Deployment: add to Coolify cron / cron-job.org alongside the Phase 4 report-schedules cron:

30 * * * * curl -fsS -H "Authorization: Bearer $CRON_SECRET" \
  https://learn.opticrm.app/api/cron/analytics-snapshot

Offset from the schedule cron (0 * * * *) by 30 minutes so the two don't contend for the same DB connections.

Rate limiting

Phase 5 adds a per-user rate limit in front of the heavy endpoints:

• POST /api/analytics/reports/run — 30 runs per user per minute.
• POST /api/analytics/reports/export — 10 exports per user per minute (tighter because each call materializes a file up to ~10MB).

Both use the existing rateLimit() helper from src/lib/rate-limit.ts, which implements a sliding-window counter in Redis and fails soft when Redis is down. Exceeded limits return HTTP 429 with Retry-After, X-RateLimit-Limit, X-RateLimit-Remaining, and X-RateLimit-Reset headers.

Phase 6 — Feature flag + remaining dashboards

Feature flag. LMSSettings.enableReportsBuilder (default true) gates the ad-hoc Reports Builder surface. When false, the following endpoints and pages return a friendly 403:

• GET/POST /api/analytics/reports (list/create)
• GET/PATCH/DELETE /api/analytics/reports/[id] (manage one)
• POST /api/analytics/reports/export

The POST /api/analytics/reports/run endpoint is NOT behind the flag because the prebuilt dashboards (useReportRun on the Platform/Student/Engagement/Compliance pages) hit the same endpoint. Gating it would break dashboards whenever the flag is off. Schedules, alerts, and the entities registry are also unguarded for the same reason — they're downstream of the flagged pages so they're effectively unreachable when the UI is hidden.

The gate is implemented in src/lib/analytics/feature-flag.ts with a 5-second in-memory cache per institution so the same request doesn't hit the DB multiple times for the flag. Missing LMSSettings rows default to "enabled" — fresh institutions get the feature without explicit setup.

Four new prebuilt dashboards. Phase 6 ships the remaining dashboards planned in the roadmap:

• /analytics/students — active students, completions, avg final score, dropouts; daily active chart + lifecycle pie + completions trend.
• /analytics/engagement — lessons completed, quiz attempts, avg quiz score, forum posts; 3 trend charts.
• /analytics/compliance — total/completed/overdue records + completion velocity; status pie + completions trend.
• /analytics/revenue — list revenue (SUM(course.price × enrollments in window)), top 10 courses by revenue, daily revenue trend.

The first three are built on the standard preset + useReportRun pattern, each with its own src/lib/analytics/presets/<name>.ts file. The revenue dashboard is the one outlier: the analytics engine's categorical/temporal modes can't aggregate across a Prisma relation (needed to sum enrollment.course.price), so revenue uses a bespoke /api/analytics/dashboard/revenue endpoint that does the join manually. When the engine grows two-dim grouping + relation aggregation support in a future phase, revenue should be rewritten to use presets like the others.

Sidebar wiring. The "Insights" group in DashboardSidebar.tsx now has six entries: Platform, Students, Engagement, Compliance, Revenue, Reports. The old single "Analytics" entry was renamed to "Platform" to avoid ambiguity with the expanded section.

Phase 5.1 — Per-course snapshot dimension + auto-invalidation

Phase 5.1 extends the writer and rewriter to cover per-course queries (previously only institution-wide), and wires cache invalidation automatically through a Prisma client extension.

Writer. /api/cron/analytics-snapshot now writes two sets of rows per tick per institution:

1. dimension="institution", dimensionId="_all" — one row per day. Covers global dashboards.
2. dimension="course", dimensionId=<courseId> — one row per day per course. Only emitted for courses that have ever had an enrollment, so drafts and archived empty courses stay out of the table. Per-course writes are batched 10-at-a-time to keep Prisma connection pressure bounded on large catalogues.

The writer uses a unified writeSnapshotForScope(institutionId, day, courseId) helper — passing courseId: null produces the institution row, passing a string produces the per-course row. Both paths compute the same metric set via the same where shapes (institution scope for enrollment/certificate, nested enrollment.courseId scope for lessonProgress/quizAttempt/assignmentSubmission/forumPost).

Rewriter. rewriteAsSnapshot now routes to either dimension based on the DSL filters:

• No courseId filter → dimension="institution" AND dimensionId="_all"
• courseId eq <id> → dimension="course" AND dimensionId=<id>
• courseId in <ids> → dimension="course" AND dimensionId IN (...) (capped at 100 ids to bound the bind count)

A teacher scope ("ownCourses") query that doesn't supply a courseId filter still falls through to the live path — the rewriter doesn't have access to the scope helper's course list, so it plays safe and lets the Prisma scope fragment do the work.

Auto-invalidation. src/lib/prisma.ts now wraps the base PrismaClient with a $extends query handler on every snapshot-tracked model (enrollment, lessonProgress, quizAttempt, assignmentSubmission, certificate, forumPost). On every write operation (create/update/upsert/delete/etc.) it extracts institutionId from the args and fires bumpAnalyticsVersion(institutionId) in the background. The handler is fail-soft — if extraction fails or Redis is unreachable, the mutation itself succeeds and the version counter stays put (causing stale reads for up to 60s, the live-TTL window).

This means every current and future mutation site automatically invalidates the cache. No per-route wiring to maintain. If a new route adds a write to one of these tables, it inherits invalidation for free; if it adds a write to a new snapshot-tracked table, add the model to the $extends block.

Indexes

Phase 0 added six indexes to hot tables to support time-bucketed queries:

• Enrollment(institutionId, createdAt)
• Enrollment(institutionId, completedAt)
• LessonProgress(institutionId, lastAccessedAt)
• QuizAttempt(institutionId, completedAt)
• AssignmentSubmission(institutionId, submittedAt)
• Certificate(institutionId, issuedAt)

Without these, the temporal mode would full-scan on every dashboard load. If you add a new entity that will be queried by time, remember to add the equivalent index.

Phase 4 — Exports, Schedules, Alerts

Phase 4 adds three orthogonal capabilities on top of the executor: synchronous exports in three formats, recurring scheduled deliveries, and threshold alerts. None of them touch the executor itself — they all sit on top of executeReport() and reuse its tenant-isolation guarantees.

Export pipeline

ReportDefinition ─► executeReport() ─► ReportRunResult
                                            │
                                            ▼
                                  buildTableView(result, def)
                                            │
                                            ▼
              ┌───────────────┬─────────────┴──────────────┐
              ▼               ▼                            ▼
     tableViewToCsv()  tableViewToXlsx()           tableViewToPdf()

buildTableView is the shared projection layer. It collapses both rows-mode and buckets-mode results into a 2D headers + cells matrix so each format serializer doesn't have to re-derive the shape. For rows mode the headers are the DSL field names in definition order; for buckets mode the first column is the group key and the rest are aggregation series.

POST /api/analytics/reports/export is the inline endpoint. It accepts the same DSL as /run, plus a format: "csv" | "xlsx" | "pdf" field. The response streams the file body with Content-Disposition: attachment and writes a report.export audit row.

v1 is synchronous and inline-only. The executor's hard cap of 10,000 rows means even an XLSX render stays under a couple of seconds for realistic reports. The async path (BullMQ + R2 + presigned URL) is wired into the schema (LMSReportExport) but not yet implemented — file a follow-up if you need exports beyond the cap.

Schedule cron

Hourly cron tick
       │
       ▼
SELECT * FROM LMSReportSchedule WHERE nextRunAt <= now() AND isActive
       │
       ▼ (for each)
runReportAsSystem(report.def, scope = schedule.scope)
       │
       ▼
buildTableView → renderForFormat → sendEmail({ attachments: [...] })
       │
       ▼
evaluateAlert(alert, result) for every active alert on the same report
       │
       ▼
schedule.nextRunAt = computeNextRunAt(...)

The hourly cron lives at /api/cron/report-schedules and uses the same Bearer ${CRON_SECRET} auth pattern as assignment-reminders. It pulls up to 200 due schedules per tick, runs each one through the executor as the snapshotted creator, renders the format, and emails the file as an attachment.

runReportAsSystem (in system-runner.ts) synthesizes a SessionUser from the report's createdById plus the schedule's snapshotted scope field. We don't need to fetch the creator's full profile from OptiCRM at cron time because the executor only uses id, institutionId, and the scope — and the role is collapsed into the snapshotted scope.

The scope is snapshotted on the schedule (and on each alert) at creation time. This is the trick that lets the cron run without a live identity lookup. If a teacher creates a schedule, future fires stay scoped to their own courses. If an admin creates one, they see the institution. A snapshot prevents privilege creep — even if the creator's role changes later, the schedule keeps running with the scope they had when they set it up.

Alert evaluation

runReportAsSystem result
       │
       ▼
for each LMSReportAlert where reportId = ... AND isActive:
       │
       ▼
evaluateAlert(alert, result)
       │
       ├── buckets mode → match alert.field against series names,
       │                   compare each bucket value to threshold
       │
       └── rows mode    → pull alert.field from each row,
                           compare to threshold

if matched → email + bump triggerCount + audit

Alerts are tied to a report and fire during scheduled runs only in v1. A standalone alert cron is intentionally deferred — schedules already give every report a recurring evaluation cadence, so the marginal value of a separate alert loop is low. Alerts on reports without schedules show a banner in the UI explaining that they won't auto-fire.

The threshold matcher in alert-evaluator.ts returns { matched, matchCount, matchedValues[] }. The cron uses these in the email body so the recipient sees not just "the alert fired" but how many rows matched and a sample of the matched values.

Failure handling

A schedule run failure stamps lastRunStatus = "ERROR" and lastRunError on the row but does not advance nextRunAt — the next hourly tick will retry. This means transient DB hiccups don't cause a missed delivery. There's no auto-pause after N consecutive failures in v1; that's on the v1.1 list.

Email sending is fire-and-forget through the existing sendEmail() in lib/email.ts, which tries Resend then falls back to SMTP. The cron does not block on email delivery confirmation — if Resend takes 30s, the next schedule waits 30s. Future versions should route through emailQueue (BullMQ) for true async retry.

Schema additions in this phase

Two new columns on existing models:

• LMSReportSchedule.scope: String @default("ownCourses") — snapshotted analytics scope for the cron
• LMSReportAlert.scope: String @default("ownCourses") — same, for any future standalone alert cron

No new tables. The LMSReportExport table already exists from Phase 0 and is unused in v1 (reserved for the async path).

Cron deployment

Add to your scheduler (Coolify cron, GitHub Actions, cron-job.org):

0 * * * * curl -fsS -H "Authorization: Bearer $CRON_SECRET" \
  https://learn.opticrm.app/api/cron/report-schedules

Hourly is the right cadence — schedules fire on hour boundaries and the cron picks up everything due since the last tick.

The endpoint caps each tick at 200 due schedules. If you have more than 200 schedules firing at the exact same minute you'll want to shard the cron across offset minutes (e.g. 5 * * * * plus 35 * * * *), but that's unlikely for realistic tenant counts.

Error Handling Contract

The executor throws:

• DSLValidationError — invalid DSL (unknown entity, bad field, unsupported operator, etc.). API routes catch and return 400.
• Error with message "AUTH_REQUIRED" — not authenticated. Routes return 401.
• Any other Error — bubbles up as 500. The audit row still gets written with the error message.

Client callers (useReportRun) normalize these into { data: null, error: string }.

Prisma Models

The Phase 0 migration added these models:

• LMSReport — saved report definition (name, description, entity, definition JSON, chartType, visibility, sharedWithRoles, folderId, createdById, timestamps). The definition JSON is Zod-validated on every write.
• LMSReportRun — run history (one row per execution). Tracks rowCount, executionMs, truncated, cached, error, and a triggeredBy string ("manual" | "schedule" | "alert" | "export").
• LMSReportAlert — data-exception alerts attached to a report. Fires during scheduled runs. Phase 4 — has a snapshotted scope column.
• LMSReportFolder — tree for organizing reports and dashboards. Phase 3.5.
• LMSReportSchedule — recurring email delivery config. Phase 4 — has a snapshotted scope column.
• LMSReportExport — one-shot or scheduled export jobs. Modeled in Phase 0; the async path is not yet wired in Phase 4 (inline export is sufficient up to the 10k row cap).
• LMSDashboard — config-driven dashboard definition. Phase 2 uses this implicitly via presets; Phase 4 will add a "save my dashboard layout" feature.
• DailyMetricSnapshot — generic per-dimension daily aggregate table, feeds the snapshot rewriter in Phase 5.

See prisma/schema.prisma for the full field lists.

Known Limitations in v1

• Single grouping only (two-dim planned)
• countDistinct in Prisma groupBy is an approximation (non-null count); temporal mode is correct
• LMSDashboard table is currently unused — dashboards are preset-driven from code
• LMSReportFolder is modeled but not wired into the UI
• Async/queued exports to R2 are not implemented (Phase 4 ships inline-only, capped at 10k rows)
• Standalone alert cron — alerts evaluate inside the schedule cron, so an alert without a schedule never fires
• Audit log has no viewer UI (read via DB or future admin tool)
• Per-course and per-student snapshot dimensions not yet populated (Phase 5 ships the institution-wide path only)
• Snapshot rewriter doesn't yet support two-dim grouping or filter pushdown beyond date ranges

Testing the Engine

There's no automated test suite for the engine yet. To smoke-test:

curl -X POST http://localhost:3001/api/analytics/reports/run \
  -H "Content-Type: application/json" \
  -H "Cookie: authjs.session-token=<your-jwt>" \
  -d '{
    "definition": {
      "version": 1,
      "entity": "enrollment",
      "fields": [],
      "filters": [],
      "groupings": [],
      "aggregations": [{ "alias": "total", "fn": "count" }],
      "chartType": "kpi"
    }
  }'

Then try a categorical grouping:

{
  "version": 1,
  "entity": "enrollment",
  "fields": [],
  "filters": [],
  "groupings": [{ "field": "status", "granularity": "field" }],
  "aggregations": [{ "alias": "count", "fn": "count" }],
  "chartType": "pie"
}

And a temporal grouping:

{
  "version": 1,
  "entity": "enrollment",
  "fields": [],
  "filters": [{ "field": "createdAt", "op": "relativeDays", "value": 30 }],
  "groupings": [{ "field": "createdAt", "granularity": "day" }],
  "aggregations": [{ "alias": "enrollments", "fn": "count" }],
  "chartType": "line"
}

Each should return a different mode ("buckets" for the first two, "buckets" with date keys for the third).