Analytics¶

Status: Implemented (V1). Query helpers live in src/lib/analytics/course.ts (admin/aggregate), src/lib/analytics/learner.ts (per-learner), src/lib/course/enrollment-summaries.ts (dashboard enrolment rows), src/lib/course/entry-titles.ts (score-row titles), and src/db/queries/quiz-attempts/latest-for-course.ts (course-outline quiz badges). Admin surfaces are /admin/courses/\[slug\]/dashboard (completion, entry funnel, quiz pass rates) and /admin/courses/\[slug\]/dashboard/quizzes (per-question averages, attempts-to-pass). Learner surfaces are /learn/dashboard (tabbed enrolments/scores panels; course/entry labels with acronyms; progress and latest quiz-attempt date) and /learn/course/\[slug\] (overall progress, module progress, per-entry state, latest quiz score badge, lock state).

Analytics answer course-admin and tenant-admin questions about learner progress, quiz outcomes, and content drop-off. V1 reads from OLTP tables directly because the expected launch volume is modest. When query cost becomes visible in production, move the same query shapes behind materialized views without changing dashboard semantics.

Source tables¶

The initial source of truth is:

• enrollments — course enrollment status, start date, completion date.
• entry_progress — per-entry completion and last viewed timestamp.
• quiz_attempts — quiz score, pass/fail, attempt number, submitted answers, and feedback.
• CMS course hierarchy — module and entry order from src/lib/cms/client.ts.

All queries are tenant-scoped first. Course-admin queries additionally verify the course slug is in the user's assigned courses before reading analytics.

Query contracts¶

Course completion rate¶

Inputs: tenantId, courseSlug.

Definition:

select
  count(*) filter (where status in ('active', 'completed')) as enrolled_count,
  count(*) filter (where status = 'completed') as completed_count
from enrollments
where tenant_id = ?
  and course_slug = ?;

completionRate = completed_count / enrolled_count, with 0 when enrolled_count = 0.

Entry drop-off funnel¶

Inputs: tenantId, courseSlug, ordered CMS entries.

For each entry in CMS order:

select
  entry_slug,
  count(*) filter (where completed_at is not null) as completed_count,
  count(*) filter (where last_viewed_at is not null) as viewed_count
from entry_progress
where tenant_id = ?
  and course_slug = ?
group by entry_slug;

viewed_count is the number of distinct student learners who opened the entry (one entry_progress row per learner/entry pair). Admin, course-admin, and super-admin preview sessions are excluded from view tracking.

The dashboard joins this result to the CMS hierarchy so entries with zero progress still appear. Drop-off for an entry is the difference between the previous required entry's completed_count and the current entry's viewed_count.

Quiz pass rate per entry¶

Inputs: tenantId, courseSlug.

Use each learner's latest attempt per quiz entry:

with latest_attempts as (
  select distinct on (user_id, entry_slug)
    user_id,
    entry_slug,
    passed,
    score,
    attempt_number
  from quiz_attempts
  where tenant_id = ?
    and course_slug = ?
  order by user_id, entry_slug, attempt_number desc
)
select
  entry_slug,
  count(*) as attempted_count,
  count(*) filter (where passed = true) as passed_count,
  avg(score) as average_latest_score
from latest_attempts
group by entry_slug;

SQLite tests should use an equivalent window-function query because distinct on is PostgreSQL-specific.

Latest quiz attempt per course entry (learner outline)¶

Inputs: tenantId, userId, courseSlug.

The enrolled course page needs one latest-attempt record per quiz entry so it can render score badges beside entry rows. The query uses a portable NOT EXISTS latest-attempt filter keyed by (user_id, tenant_id, course_slug, entry_slug, attempt_number):

select qa.entry_slug, qa.score, qa.passed, qa.attempt_number, qa.submitted_at
from quiz_attempts qa
where qa.user_id = ?
  and qa.tenant_id = ?
  and qa.course_slug = ?
  and not exists (
    select 1
    from quiz_attempts qa2
    where qa2.user_id = qa.user_id
      and qa2.tenant_id = qa.tenant_id
      and qa2.course_slug = qa.course_slug
      and qa2.entry_slug = qa.entry_slug
      and qa2.attempt_number > qa.attempt_number
  );

The helper returns a Map<entrySlug, { score, passed, attemptNumber, submittedAt }> and never includes answer payloads.

Average score per question¶

Inputs: tenantId, courseSlug, entrySlug.

quiz_attempts.feedback_json stores per-question correctness. The query layer extracts { questionId, correct } into a typed result and computes:

• attempts per question.
• correct count.
• averageCorrect = correctCount / attempts.

V1 implementation: the helper selects the scoped feedback_json rows (decoded by Kysely's ParseJSONResultsPlugin) and aggregates per-question correctness in TypeScript, returning the same typed shape on PostgreSQL and SQLite. A future optimisation may push this into jsonb_to_recordset on PostgreSQL without changing the return type.

Attempts-to-pass distribution¶

Inputs: tenantId, courseSlug, optional entrySlug.

For each learner and quiz entry, find the first passing attempt:

select
  entry_slug,
  attempt_number,
  count(*) as learner_count
from quiz_attempts
where tenant_id = ?
  and course_slug = ?
  and passed = true
group by entry_slug, attempt_number
order by entry_slug, attempt_number;

Learners who exhaust attempts without passing are counted separately from latest attempts where passed = false.

Dashboard consumers¶

Admin analytics may use unstable_cache or React cache() for stable inputs. Per-user learner dashboard data must not be shared across users.

Funnel rows refresh immediately when completion-bearing actions run (markEntryCompleteAction, submitQuizAttemptAction) through revalidateTag("progress:<courseSlug>"); otherwise they refresh on the 5-minute cache window.

Materialized-view migration path¶

When OLTP reads become too expensive:

1. Add materialized views for course completion, entry funnel, and quiz aggregates.
2. Refresh views on a schedule and after high-signal events (enrollment.completed, submitQuizAttempt).
3. Keep the query helper return types unchanged so dashboard components do not change.
4. Add freshness labels in the admin UI if data is no longer real-time.

Do not add a separate analytics event table in V1. The existing domain tables are the source of truth and avoid event-stream consistency problems until scale requires them.

Security and privacy¶

• Course admins see only assigned courses.
• Tenant admins see only their tenant.
• Super admins must use withTenantOverride() for cross-tenant analytics.
• Aggregates should avoid exposing personally identifiable learner data unless the route is an explicit roster or student-detail view.
• Logs for analytics queries include traceId, tenantId, courseSlug, and query name, but not learner names or emails.

Testing¶

• Unit-test each query helper against SQLite fixtures with multiple tenants to prove tenant isolation.
• Include zero-enrollment and zero-attempt cases.
• Verify course admins cannot query unassigned course analytics.
• Component-test empty, loading, and populated dashboard states.
• E2E-test the admin dashboard after enrollment, progress completion, and quiz submission flows.