Group Analytics & Reporting

Feature Owner: clydetims (Clyde Ador)
Module: Dashboards — Analytics & Reporting
Date: 2026-10-02

Branch set covered: feat/11.6-group-analytics (#781), feat/11.6-agency-analytics (#789), feat/11.6.2-agency-analytics-add-buttons (#797), fix/11.6-questa-ssigned-and-completion-rate (#805), feat/11.6-learner-visit-pages (#808).

EXECUTIVE SUMMARY

What is this feature?

11.6 Analytics & Reporting gives Agency Owners/Admins and Creators live visibility into group and learner performance. It delivers two surfaces over one shared data model:

Agency Analytics dashboard — /agency/analytics with Overview, Hierarchy View, Performance, Engagement and Comparison tabs, five drill-in detail pages, a monthly Most Visited Pages report, and a one-click CSV report.
Creator Group Analytics — /creator/analytics with eight metric cards, a parent-group → subgroup → member drill-in, member management, and a comparison tool (Parents | Subgroups modes) with a completion-rate trend chart.

Supporting work: page-visit tracking (page_visits), daily completion snapshots, and a shared quest-scope resolver so assigned-quest counts and completion rates match across both surfaces.

Why does it matter?

Agencies are accountable for cohort learning outcomes. Before this feature, admins could only export raw CSV data and rebuild reports by hand, and two screens could report different completion numbers. Creators had no cohort-level view, no comparison, and no trend. This feature removes the manual reporting work and makes the numbers trustworthy and consistent.

What's the MVP scope?.

Included:

• Agency analytics dashboard (five tabs, detail pages, empty/error states).
• Creator hierarchy view (creator → parent group → subgroup → member).
• Eight creator metric cards.
• Add Group / Add Sub Group with correct creator attribution.
• Export All / per-creator export / agency Export Report.
• Group comparison with parent/subgroup modes and completion trend.
• Real completion history via daily snapshots (projection fallback until history exists).
• Most Visited Pages (top 5/month) and per-learner visited pages.
• Consistent assigned-quest and completion-rate calculation across surfaces.

Excluded: scheduled/emailed reports, PDF export, cross-agency comparison, real-time streaming, learner progress editing from analytics, presence/activity-log features.

USER PAIN POINT & SOLUTION

Current State (Without Feature)

An agency admin opens the dashboard, clicks Export, downloads a CSV, and rebuilds charts in a spreadsheet each reporting cycle. Group-level views, comparisons, completion trends, and page engagement do not exist in-product. A creator sees only a flat learner list. Assigned-quest counts and completion rates are computed by two separate code paths, so the creator view and agency view can disagree.

Pain Point

Emotional: Frustrated and exposed when reporting takes hours and the numbers cannot be trusted.
Functional: No group-level view, no trend, no comparison, no content-engagement data, and no way to fix a mis-attributed group without support.
Business Impact: Slow reporting and inconsistent completion rates prevent early intervention and make it hard to prove agency impact.

Future State (With Feature)

An admin opens Analytics and immediately sees group performance, compares cohorts, reads completion trends, and exports a report in one click. A creator reviews cohort metrics, drills into at-risk members, and compares groups. Both surfaces compute assigned quests and completion from the same shared resolver, so the numbers match.

Marketing Hook

"One click from raw learners to trustworthy, comparable cohort analytics — no spreadsheets, no conflicting numbers."

4D FRAMEWORK MAPPING

Diagnose

Identifies which groups and learners are progressing or at risk via metric cards (Avg Completion, At-Risk Groups, Engagement Rate), the hierarchy drill-in, badge distribution, quest completion and learner/XP growth panels, plus monthly Most Visited Pages.

Design

Provides structure for planning intervention: a consistent two-level hierarchy (main group → subgroup), group creation actions in context, and comparison modes (Parents | Subgroups) that let an admin choose what to contrast.

Develop

One agency hierarchy API (GET /api/agency/analytics) and one creator hierarchy API (GET /api/agency/groups/main) feed every view. A shared quest-scope resolver merges the two assignment sources, and completion snapshots accrue lazily from real loads.

Deliver

Delivers measurable outputs: KPI cards, comparison charts, completion trend, CSV reports (agency_analytics_report.csv, creator_hierarchy_analytics.csv), and page-engagement reporting for monthly stakeholder review.

USER FLOWS

Entry Point

Agency: /agency/analytics — app/agency/analytics/page.tsx; reached from agency navigation (constants/getNavGroups.ts). Visible to agency members; management affordances gated by role and per-group flags.
Creator: /creator/analytics — app/creator/analytics/page.tsx; visible to creators/agency group owners.
Learner tracking: /api/learner/track-visit, invoked from components/learner/QuestPlayer.tsx in learner mode only.

Success Criteria

An agency member sees accurate, agency-scoped group and learner performance.
Management actions appear only when the server will permit them.
Assigned-quest and completion-rate numbers match across agency and creator surfaces.
Exports download with the expected columns.
Page-visit tracking never blocks or slows a learner.
Empty/error/loading states render correctly.

Main Flow (Happy Path)

Agency Analytics

1. Admin opens /agency/analytics
2. Page calls GET /api/agency/analytics (via hooks/agency/useAgencyAnalytics.ts).
3. API returns { creators, canManage, viewer }.
4. Overview tab renders KPI/breakdown cards from creators.
5. Admin opens Hierarchy View; HierarchyTable renders creators → parent groups → subgroups → members with edit/archive/delete actions.
6. Admin clicks Add Group / Add Sub Group → POST /api/agency/groups/main or POST /api/agency/groups/subgroups; on success the hook refetches.
7. Admin switches to Performance / Engagement / Comparison tabs.
8. Admin opens a detail page (e.g. quest_completion), DetailPageContent wraps QuestCompletionPanel.
9. Admin opens Most Visited Pages; useMostVisitedPages(month) calls GET /api/agency/analytics/most-visited.
10. Admin clicks Export Report → handleExportReport() flattens the hierarchy and calls downloadCsv.

Creator Group Analytics

1. Creator opens /creator/analytics.
2. Page calls GET /api/agency/groups/main?limit=100.
3. Response groups and snapshots populate state.
4. Eight metric cards render from the parent/subgroup pipeline.
5. Creator expands a parent group (GroupTable); subgroups and members render.
6. Creator opens LearnerAnalyticsPanel for a member.
7. Creator uses ComparisonView (Parents | Subgroups); CompletionChartCard shows projection or real history.
8. Creator edits/archives/deletes via PATCH/DELETE /api/agency/groups/main/[id].

Learner Page Visit

1. Learner opens a quest in `QuestPlayer`.
2. `useTrackPageVisit` debounces 1000 ms, dedupes within 3000 ms, then POSTs `/api/learner/track-visit`.
3. Route validates, resolves agency, verifies `is_agency_learner`, inserts `page_visits`.
4. Visit appears in Most Visited Pages / learner pages.

Edge Cases

Decision Points

IF role is OWNER/ADMIN THEN see full agency hierarchy ELSE IF CREATOR → only owned/shared groups (per-group flags) ELSE hidden.
IF `creatorId` supplied and differs from actor THEN require OWNER/ADMIN and `isAgencyGroupCreator` ELSE attribute to actor.
IF `!isAgencyManagerRole && (!flagsResolved || !subgroupFlagsResolved)` THEN return retryable 500 ELSE filter hierarchy.
IF comparison group has ≥ 2 captured days THEN plot real snapshots ELSE labeled projection.
IF `quest_id` present THEN attribute to quest's owning agency ELSE learner's own agency; IF quest agency unresolved → skip.
IF export rows length 0 THEN warning toast ELSE download.

INFORMATION ARCHITECTURE

Primary Information (Always Visible)

Creator identity (name, email, avatar).
Parent group / subgroup name, description, status.
Member name, email, avatar, added date.
Member completion rate, XP, badges, assigned quests.
Group average completion and member counts.
Page title/path and visit count.

Secondary Information

Created/updated/archived timestamps.
Member `last_visited_at`.
Subgroup manager (`manager_name`, `manager_email`).
Selected month / time range.
Comparison mode (Parents | Subgroups).

Tertiary Information (Hidden Until Needed)

Snapshot `capture_date` and real-vs-projected flag.
Raw `page_path` behind a page title.
CSV filename.
Badge names inside exported CSV.
Per-group `canView/canEdit/canManage` flags.

Actions

Primary CTA

"Export Report" (`AnalyticsHeader`).
"Export All" / per-creator export (`exportCsv.ts`).

Secondary Actions

Add Group / Add Sub Group.
Expand/collapse creator, parent group, subgroup.
Edit / Archive / Delete group.
Open learner side panel.
Switch tabs.
Change month/time range.
Select comparison groups; toggle Parents | Subgroups.
Retry failed load / Refresh data.

WIREFRAMES

Key Screens

Agency Analytics Dashboard (`/agency/analytics`)

Purpose: agency-wide analytics with tabs. Components: AnalyticsHeader, Tabs, tab components, ErrorState.

+-------------------------------------------------------------+
| Analytics & Reporting                       [ Export Report ]|
| Monitor learner progress and group performance               |
+-------------------------------------------------------------+
| [Overview] [Hierarchy View] [Performance] [Engagement] [Comparison] |
+-------------------------------------------------------------+
| +----------+ +----------+ +----------+ +----------+         |
| | KPI      | | KPI      | | KPI      | | KPI      |         |
| +----------+ +----------+ +----------+ +----------+         |
|                                                             |
| (tab content: tables / charts / comparison)                 |
+-------------------------------------------------------------+

Creator Group Analytics (`/creator/analytics`)

Purpose: creator cohort metrics + comparison. Components: `MetricCard` ×8, `GroupTable`, `MemberTable`, `LearnerAnalyticsPanel`, `ComparisonView`.

+-------------------------------------------------------------+
| Group Analytics                                             |
+-------------------------------------------------------------+
| [Parent Groups] [Subgroups] [Total Members] [Active Members]|
| [Avg Completion] [Total XP Earned] [Engagement] [At-Risk]   |
+-------------------------------------------------------------+
| Parent Group          | Subgroups | Members | Actions        |
| > Grade 1            |     3     |    42   | Edit Archive Del|
|   > Team A           |           | members |                 |
+-------------------------------------------------------------+
| Compare: [Parents v]  [+ select groups]                     |
|  Completion Rate Over Time (projected | recorded snapshots)  |
+-------------------------------------------------------------+

Most Visited Pages

Purpose: monthly top-5 pages. Components: `ChartCard`, recharts `BarChart`, `Table`, `MonthSelector`.

+-------------------------------------------------------------+
| Most Visited Pages            [ < October 2026 > ]          |
| Top 5 pages by learner visits in October 2026               |
| ####################  Page A                                |
| ##############        Page B                                |
+-------------------------------------------------------------+
| # | Page    | Path            | Visits                      |
+-------------------------------------------------------------+

Agency Detail Pages

`/agency/analytics/{badge_distribution,group_performance,learner_growth,quest_completion,xp_growth_over_time}` — each renders `DetailPageContent` (header + `TimeRangeSelector` + `ProgressiveTable`) wrapping a panel.

Modal / Detail Views

Learner Analytics Panel (`components/creator/analytics/LearnerAnalyticsPanel.tsx`) — member details + visited pages.
Group Modal (`components/creator/groups/GroupModal.tsx`) — create/edit group (accepts `creatorId`).
GroupSelectionCard — group picker for comparison.
MonthSelector — month picker for page analytics.

Empty State

Agency: "No agency data yet" / "Creators and groups you add will show up here."
Pages: "No pages to display yet." / "No page visits recorded in {month} yet."
Comparison: `EmptyComparisonState` ("select 2-4 groups").

Loading State

Agency page: "Loading analytics..." (`app/agency/analytics/page.tsx`).
`LoadingState` component for panels.
Creator metric cards: `MetricCard` loading variant.

Error State

`ErrorState` with `message` + `onRetry` (agency page, detail pages).
Background-refresh failure: toast + preserved data (`useAgencyAnalytics`).

Annotations

`useAgencyAnalytics` shows the full-screen loader only on the first load; later refetches are in-place.
Creator page fetches once with `limit=100` and derives all cards client-side.
Comparison renders real data only when every selected group has ≥ 2 captured days (`components/creator/analytics/ComparisonView.tsx:150-154`).

WIREFLOWS

Primary agency flow:

[/agency/analytics]
      |
      v
[GET /api/agency/analytics] --401/error--> [ErrorState + Retry]
      |
      v
[creators.length > 0] --no--> [Loading | Error | Empty State]
      |
      yes
      v
[Tabs: overview/hierarchy/performance/engagement/comparison]
      |
      +-- Hierarchy --> [Add Group / Add Sub Group]
      |                      | POST
      |                      v
      |                 [refetch on success]
      |
      +-- Export Report --> [downloadCsv agency_analytics_report.csv]

Creator page + snapshot flow:

[/creator/analytics]
      |
      v
[GET /api/agency/groups/main?limit=100]
      |
      v
[fetchAnalyticsHierarchy] --> [after(): recordDailyCompletionSnapshots]
      |
      v
[fetchRecentCompletionSnapshots] --> response.snapshots
      |
      v
[MetricCards + GroupTable + ComparisonView]
      |
      v
[every selected group >= 2 days?]
      | yes -> [real completion trend]
      | no  -> [labeled projection fallback]

Learner visit flow:

[QuestPlayer learner mode]
      |
      v
[useTrackPageVisit: 1s debounce, 3s dedupe]
      |
      v
[POST /api/learner/track-visit]
      |
      +-- invalid --> 400
      +-- no agency / not learner --> 200 {tracked:false}
      |
      v
[INSERT page_visits]
      |
      v
[GET most-visited / learner-pages] --> [MostVisitedPages / LearnerAnalyticsPanel]

PROTOTYPE

Figma Prototype Link: Not specified
Other design references: `docs/wireframes/` (general wireframe assets); no analytics-specific prototype found.

How to Test (Manual)

1. Sign in as an Agency Owner/Admin and open `/agency/analytics`.
2. Confirm the five tabs render and Overview KPIs populate.
3. Open Hierarchy View; expand a creator, parent group and subgroup.
4. Click Add Group, create it; confirm it appears under the correct creator.
5. Click Export Report; confirm `agency_analytics_report.csv` downloads.
6. Sign in as a Creator and open `/creator/analytics`; confirm the eight metric cards.
7. Expand a parent group; open a learner side panel.
8. Use Compare; select groups; confirm projection then real trend after two captured days.
9. As a Learner, open a quest in the player; advance nodes; confirm one visit per path.
10. Return to agency; open Most Visited Pages for the current month.

BACKEND SCHEMA

Database Tables

`main_learner_groups`

Purpose: top-level grouping containers (hierarchy root). Source: `supabase/migrations/20260816_01_create_main_learner_groups.sql`.

CREATE TABLE IF NOT EXISTS public.main_learner_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agency_id UUID NOT NULL REFERENCES public.agencies(id) ON DELETE CASCADE,
    creator_id UUID NOT NULL REFERENCES public.app_users(id),
    name TEXT NOT NULL CHECK (char_length(name) BETWEEN 1 AND 100),
    description TEXT CHECK (char_length(description) <= 500),
    status TEXT NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'ARCHIVED')),
    archived_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (agency_id, id)
);

Relationships: `agency_id` → `agencies`; `creator_id` → `app_users`; parent of many `learner_groups`.

`learner_groups`

Purpose: sub groups. Source: `supabase/migrations/20260806_create_learner_groups.sql`; `parent_group_id` added by `20260816_02_add_sub_group_hierarchy.sql`.

CREATE TABLE IF NOT EXISTS public.learner_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agency_id UUID NOT NULL REFERENCES public.agencies(id) ON DELETE CASCADE,
    creator_id UUID NOT NULL REFERENCES public.app_users(id),
    name TEXT NOT NULL CHECK (char_length(name) BETWEEN 1 AND 100),
    description TEXT CHECK (char_length(description) <= 500),
    manager_id UUID REFERENCES public.app_users(id),
    status TEXT NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'ARCHIVED')),
    archived_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- parent_group_id UUID with composite FK (agency_id, parent_group_id)
--   -> main_learner_groups(agency_id, id) ON DELETE CASCADE

`group_memberships`

CREATE TABLE IF NOT EXISTS public.group_memberships (
    group_id UUID NOT NULL REFERENCES public.learner_groups(id) ON DELETE CASCADE,
    learner_id UUID NOT NULL REFERENCES public.app_users(id) ON DELETE CASCADE,
    added_by UUID REFERENCES public.app_users(id),
    added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (group_id, learner_id)
);

`learner_group_quests`

Purpose: metadata-only quest assignment (one of two assignment sources).

CREATE TABLE IF NOT EXISTS public.learner_group_quests (
    group_id UUID NOT NULL REFERENCES public.learner_groups(id) ON DELETE CASCADE,
    quest_id UUID NOT NULL REFERENCES public.quests(id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (group_id, quest_id)
);

`group_completion_snapshots`

Purpose: one row per group per UTC day holding average completion. Source: `supabase/migrations/20260821_create_group_completion_snapshots.sql`.

CREATE TABLE IF NOT EXISTS public.group_completion_snapshots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subgroup_id UUID REFERENCES public.learner_groups(id) ON DELETE CASCADE,
    main_group_id UUID REFERENCES public.main_learner_groups(id) ON DELETE CASCADE,
    agency_id UUID NOT NULL REFERENCES public.agencies(id) ON DELETE CASCADE,
    avg_completion INT NOT NULL DEFAULT 0 CHECK (avg_completion BETWEEN 0 AND 100),
    capture_date DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT group_completion_snapshots_target_check
        CHECK ((subgroup_id IS NULL) <> (main_group_id IS NULL)),
    CONSTRAINT group_completion_snapshots_subgroup_daily_unique
        UNIQUE (subgroup_id, capture_date),
    CONSTRAINT group_completion_snapshots_main_daily_unique
        UNIQUE (main_group_id, capture_date)
);

`page_visits`

Purpose: one row per learner page view. Source: `supabase/migrations/20260907_create_page_visits_table.sql`.

CREATE TABLE IF NOT EXISTS public.page_visits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    learner_id UUID NOT NULL REFERENCES public.app_users(id) ON DELETE CASCADE,
    page_path TEXT NOT NULL,
    page_title TEXT,
    quest_id UUID REFERENCES public.quests(id) ON DELETE SET NULL,
    visited_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    agency_id UUID NOT NULL REFERENCES public.agencies(id) ON DELETE CASCADE
);

Indexes

Constraints

`main_learner_groups`: `UNIQUE (agency_id, id)`; name 1–100; description ≤ 500; status enum.
`learner_groups`: name 1–100; description ≤ 500; status enum; composite FK `(agency_id, parent_group_id)`.
`group_completion_snapshots`: target exclusivity CHECK; per-level daily UNIQUE; `avg_completion` 0–100.
`page_visits`: `page_path` trimmed, 1–2048, matches `^/[A-Za-z0-9._~/-]+$`; `page_title` NULL or trimmed ≤ 300 (added by `20260909_harden_page_visits_paths.sql`).
`group_memberships` / `learner_group_quests` composite primary keys prevent duplicate rows.

RLS / Database Security

Server routes use the service-role client, so RLS is bypassed in practice; routes re-implement the same checks server-side (see §12).

Helper Functions

RPC / Stored Procedures

API ENDPOINTS

`GET /api/agency/groups/main`

Purpose: Creator/agency group hierarchy with member, quest, XP, completion and snapshot data.
Auth: `authenticateAgencyMember()`.
Query Params: `page` (default 1), `limit` (default 100, max 100), `q` (search text).
Path Params: None.
Request Body: None.

Response:
{
  "success": true,
  "data": {
    "groups": [
      {
        "id": "uuid", "name": "Grade 1", "description": null, "status": "ACTIVE",
        "sub_group_count": 3, "active_sub_group_count": 2,
        "total_member_count": 42, "total_quest_count": 7,
        "canEdit": true, "canManage": true,
        "subgroups": [
          {
            "id": "uuid", "name": "Team A", "status": "ACTIVE",
            "canView": true, "canEdit": true, "canManage": true,
            "quests": [{ "id": "uuid", "title": "Quest" }],
            "members": [
              {
                "learner_id": "uuid", "name": "Jane", "email": "jane@x.com",
                "xp_rewards": 120, "completion_rate": 75.5,
                "last_visited_at": "2026-09-30T10:00:00Z",
                "quests": [{ "id": "uuid", "title": "Quest", "percentage": 75 }]
              }
            ],
            "stats": { "avgCompletion": 75.5, "totalXp": 120 }
          }
        ]
      }
    ],
    "canManage": true,
    "total": 1,
    "snapshots": [
      { "groupId": "uuid", "level": "subgroup", "avgCompletion": 75, "captureDate": "2026-09-30" }
    ],
    "pagination": { "page": 1, "limit": 100, "total": 1, "totalPages": 1 }
  }
}

Error Responses: 401 unauthenticated; 500 `"Unable to resolve group access right now. Please try again."` (creator flags unresolved); 400 `"Main groups are not yet available. Push the database migrations first."` (`42P01`/`42883`).

POST /api/agency/groups/main

Purpose: Create a main (parent) group.
Auth: `authenticateAgencyManager()`.
Query/Path Params: None.
Request Body: `{ name: string(1-100), description?: string|null, creatorId?: uuid|null }`.
Response: `{ success: true, data: { group: {...} } }` (201).
Error Responses: 422 invalid input; 401 user not found; 403 `"Only agency owners and admins can create groups for other creators"`; 400 selected creator not an active member; 400 migrations missing.

PATCH /api/agency/groups/main/[id]

Purpose: Update/archive/restore a main group.
Auth: `authenticateAgencyManager()` + `canEditMainGroup(id, context)`.
Path Params: `id` (main group UUID).
Request Body: `{ name?, description?, status?: "ACTIVE"|"ARCHIVED" }` (Zod `updateMainGroupSchema`, all optional).
Response: `{ success: true, data: { group: {...} } }`.
Error Responses: 404 group not found / not in agency; 403 `"You do not have edit access to this group"`; 422 invalid input; 400 migrations missing.

DELETE /api/agency/groups/main/[id]

Purpose: Delete a main group (cascades to sub groups).
Auth: `authenticateAgencyManager()` + `canManageMainGroupPermissions(id, context)`.
Path Params: `id`.
Request Body: None.
Response: `{ success: true, data: { message: "Main group deleted" } }`.
Error Responses: 404 not found; 403 `"You do not have permission to delete this group"`; 400 migrations missing.

`POST /api/agency/groups/subgroups`

Purpose: Create a sub group under a parent main group.
Auth: `authenticateAgencyManager()` + `canEditMainGroup(parentGroupId, context)`.
Request Body: `{ name: string(1-100), description?, parentGroupId: uuid, managerId?: uuid|null, creatorId?: uuid|null }`.
Response: `{ success: true, data: { group: {...} } }` (201).
Error Responses: 404 parent not found; 400 parent archived; 403 no edit access; 422 duplicate name (`23505`); 400 migrations missing.

`GET /api/agency/analytics`

Purpose: Full agency analytics hierarchy for the dashboard.
Auth: `authenticateAgencyMember()`.
Query/Path Params: None.

Response:
{
  "success": true,
  "data": {
    "creators": [
      {
        "id": "uuid", "name": "Creator", "email": "c@x.com", "profile_image_url": null,
        "parentGroups": [
          {
            "id": "uuid", "name": "Grade 1", "status": "ACTIVE",
            "subgroups": [
              {
                "id": "uuid", "name": "Team A", "manager_id": null,
                "members": [
                  {
                    "learner_id": "uuid", "name": "Jane", "email": "jane@x.com",
                    "added_at": "...", "last_visited_at": "...",
                    "xp_rewards": 120, "badges": ["Starter"], "completion_rate": 75.5,
                    "quests": [{ "quest_id": "uuid", "title": "Quest", "progress": { "percentage": 75 } }]
                  }
                ]
              }
            ]
          }
        ]
      }
    ],
    "canManage": true,
    "viewer": { "id": "uuid", "canCreateForAnyCreator": true }
  }
}

Error Responses: 401; 400 `"Main groups are not yet available. Push the database migrations first."`.
Side effect: records daily completion snapshots via `after()`.

GET /api/agency/analytics/most-visited

Purpose: Top 5 most visited pages for the selected month.
Auth: `authenticateAgencyMember()`.
Query Params: `month` (`YYYY-MM`, defaults to current UTC month).

Response:
{ "success": true, "data": { "agency_id": "uuid", "month": "2026-10",
  "pages": [{ "page_path": "/quests/uuid", "page_title": "Quest", "visit_count": 12 }] }
}

Error Responses: 400 invalid month; 400 migrations not pushed (`42P01`/`42883`); 500 generic.

GET /api/agency/analytics/learner-pages

Purpose: One learner's visited pages for a month.
Auth: `authenticateAgencyMember()`.
Query Params: `learner_id` (UUID, required), `month` (`YYYY-MM`, optional), `limit` (≤ 1000, default 1000).

Response:
{ "success": true, "data": { "agency_id": "uuid", "learner_id": "uuid", "month": "2026-10",
  "total_pages": 7,
  "pages": [{ "page_path": "/quests/uuid", "page_title": "Quest", "visit_count": 4 }] }
}

Error Responses: 400 invalid learner_id/month; 400 migrations not pushed; 500 generic.

POST /api/learner/track-visit

Purpose: Best-effort learner page-visit insert.
Auth: `authenticateUser()` (learner).
Request Body: `{ page_path: string(1-2048), page_title?: string|null (≤300), quest_id?: uuid|null }`.
Response: `{ "success": true, "data": { "tracked": true } }`. Non-tracked: `{ "tracked": false, "reason": "no_agency" | "not_agency_learner" }`.
Error Responses: 401 unauthenticated / user not found; 400 invalid JSON or Zod validation.
Side effect: inserts `page_visits` with the resolved `agency_id`.

GET /api/agency/status

Purpose: Agency membership/role status (modified in #808).
Auth: authenticated user.
Change: agency member lookup now filters `status = 'ACTIVE'`.
Response: `AgencyStatusResponse` (`types/agency.ts`).

POST /api/learner/update-progress (modified)

Purpose: Persists quest progress (touched in #781).
Change: XP/completed-courses awarded only on the transition to 100% (`!wasAlreadyComplete`) to prevent re-farming XP by re-submitting a completed quest, and awards XP from `quest_gamifications` via `add_learner_xp`.

DATA REQUIREMENTS

Frontend Needs

Key TypeScript shapes (`types/agency.ts`, `hooks/agency/useAgencyAnalytics.ts`, `lib/analytics/completionSnapshots.ts`):

export type TabId = "overview" | "hierarchy" | "performance" | "engagement" | "comparison";
export interface HierarchyViewer { id: string | null; canCreateForAnyCreator: boolean; }
export type SnapshotLevel = "subgroup" | "main";
export interface CompletionSnapshotPoint {
  groupId: string; level: SnapshotLevel; avgCompletion: number; captureDate: string;
}
// Member payload
interface Member {
  learner_id: string; name: string | null; email: string | null;
  profile_image_url: string | null; added_at: string;
  xp_rewards: number; completion_rate: number; last_visited_at: string | null;
  quests: Array<{ id: string; title: string; percentage: number }>;
}
// Page visits
export interface MostVisitedPage { page_path: string; page_title: string | null; visit_count: number; }
export interface PageVisitInfo { path: string; title?: string | null; questId?: string | null; }

Required fields: `id`, `name`, `status` on groups; `learner_id`, `completion_rate`, `xp_rewards` on members. Nullable fields are normalized in `normalizeCreators` (hook). `types/creator.d.ts` `Group` gains `status?: string` and `total_member_count?: number`.

API Calls Frontend Will Make

`GET /api/agency/analytics`

Agency dashboard mount + refetch after saves.

`GET /api/agency/groups/main?limit=100`

Creator analytics page mount.

`GET /api/agency/groups/subgroups`

Add Sub Group / subgroup flows.

`POST /api/agency/groups/main`

Add Group.

`PATCH/DELETE /api/agency/groups/main/[id]`

Edit/archive/delete.

`GET /api/agency/analytics/most-visited?month=`

Month change / panel mount.

`GET /api/agency/analytics/learner-pages?learner_id=&month=`

Learner panel open.

`POST /api/learner/track-visit`

Learner page change (debounced).

Caching Strategy

Client-side: none (no React Query/SWR). Data is component state via custom hooks.
Refetch: explicit `refetch()` (`requestKey` increment) after create/update/delete; `useMostVisitedPages` refetches on `month` change.
Invalidation: manual refetch after mutations; no global cache to invalidate.
Debouncing: `useTrackPageVisit` 1000 ms trailing debounce.
Deduplication: `useTrackPageVisit` 3000 ms dedupe window keyed on `page_path`.
Stale-data handling: `AbortController` cancels in-flight requests on unmount/param change; background-refresh failures keep last good data.
Server-side caching: none; `cache: "no-store"` on fetches.

PERFORMANCE CONSIDERATIONS

Database Optimization

`get_learner_group_stats_bulk` replaces N per-subgroup XP calls with one RPC.
Chunking: `chunkArray(..., LIST_BATCH=100)` for `.in()` lookups to avoid oversized filters.
Aggregation in SQL for `page_visits` (count + window `total_pages`) avoids shipping raw rows.
Indexes on agency/date and page_path/date support monthly scans.
Snapshot growth bounded at ≤ 365 rows/group/year with `ON DELETE CASCADE`.
N+1 prevention: memberships, stats and enrollments are batch-fetched by id set.

Caching Strategy

No caching layer. Analytics is read-on-load; snapshots are the only persisted history. `cache: "no-store"` is used to keep numbers current.

API Response Time

Performance targets: Not specified.
Expected response time: Not specified (no benchmarks recorded).
Known bottlenecks: multiple sequential Supabase queries in both hierarchy routes; creator page loads up to 100 parent groups with nested members in one payload; enrollments are fetched for all members at once.
Potential future optimizations: paginate/limit hierarchy payloads per tab; add server caching or materialized aggregates; incremental member loading on expansion.

SECURITY & AUTHORIZATION

Access Matrix

View agency analytics

✓

✓

✓

✓

✗

View creator analytics

✓

✓

✓

✗

✗

Create group (own panel)

✓

✓

✓

✗

✗

Create group for another creator

✓

✓

✗

✗

✗

Edit/archive group

✓

✓

✓

✗

✗

Delete group

✓

✓

✓

✗

✗

Export report

✓

✓

✓

✓

✗

Track page visit

✗

✗

✗

✗

✓

View page-visit analytics

✓

✓

✓

✓

✗

Notes: `canManage` = OWNER/ADMIN/CREATOR. `canCreateForAnyCreator` = OWNER/ADMIN. Creator per-group rights come from ownership or explicit grants (`learner_group_permissions`).

Authorization Logic

Authentication: `authenticateAgencyMember`, `authenticateAgencyManager`, `authenticateUser` (`lib/auth/authenticate`).
Role checks: OWNER/ADMIN/CREATOR gate management; REVIEWER read-only.
Permission checks: `canEditMainGroup`, `canManageMainGroupPermissions`, `resolveMainGroupRolesBulk`, `resolveSubGroupRolesBulk`, `isAgencyGroupCreator`.
Resource ownership: group must belong to the validated `agency_id` (`requireGroupInAgency`).
Tenant/agency scoping: every query filters `agency_id`; `scopeContextToAgency` prevents multi-agency role bleed.
Server-side authorization: all mutations re-checked server-side; frontend flags are cosmetic.
Frontend permission visibility: `canManage`, per-group `canEdit/canManage/canView`, and `viewer` drive button rendering.

Data Validation

Validation library: Zod (`lib/schemas/learner-group.schema.ts`; inline schemas in `track-visit`).
Input limits: group name 1–100, description ≤ 500, page_path 1–2048, page_title ≤ 300, learner-pages `limit` ≤ 1000.
Required fields: `name`, `parentGroupId`, `learner_id`, `page_path`.
UUID validation: `creatorId`, `managerId`, `parentGroupId`, `learner_id`, `quest_id`.
Enum validation: `status` in `ACTIVE|ARCHIVED`; track-visit `reason`.
File validation: N/A (no file upload).
Server-side validation: all route bodies parsed with `safeParse`; DB CHECK constraints back the page-path allowlist.
Cross-tenant validation: `requireGroupInAgency`, `.eq("agency_id", agency_id)`, `is_agency_learner`, `is_agency_member` guards.

ERROR HANDLING

Transaction/rollback: no multi-statement transactions; group delete relies on `ON DELETE CASCADE`. Snapshot writes are fire-and-forget and non-transactional.

TESTING CHECKLIST

Happy Path

[ ] Agency member opens `/agency/analytics`; all five tabs render.
[ ] Overview KPIs populate; detail pages open.
[ ] Hierarchy expands creator → parent group → subgroup → member.
[ ] Add Group creates under the correct creator.
[ ] Add Sub Group creates under the selected parent.
[ ] Edit/archive/restore updates the group and timestamps.
[ ] Delete removes the group (and cascades sub groups).
[ ] Export Report downloads `agency_analytics_report.csv` with correct columns.
[ ] Creator page shows all eight metric cards with live values.
[ ] Comparison works in Parents and Subgroups modes.
[ ] Completion chart shows projection then real data after two captured days.
[ ] Most Visited Pages shows top 5 for the selected month.
[ ] Learner side panel shows visited pages and `last_visited_at`.
[ ] A learner quest visit records exactly one row per page path.

Edge Cases

[ ] Agency with no groups / no learners / no data.
[ ] Group with no members; learner with no enrolled quests.
[ ] Quest assigned via both sources is counted once.
[ ] Learner in two subgroups does not repeat another subgroup's quest list.
[ ] Reviewer cannot see management buttons; forged mutation returns 403.
[ ] Creator with unresolved flags gets a retryable 500 (no data leak).
[ ] Cross-tenant group fetch returns 404.
[ ] Archived group operations behave (no sub group creation; restore works).
[ ] Invalid UUID / month / empty page_path rejected.
[ ] Junk or script-like page paths rejected by DB CHECK.
[ ] Preview, shared-link and SCORM modes do not record visits.
[ ] Snapshot table missing → projection fallback, no error.
[ ] Month with no visits shows empty message.
[ ] Duplicate sub group name returns 422.
[ ] Large datasets (many groups/members) load without timeout; > 100 groups truncated.
[ ] Concurrent quest completions do not drop XP.
[ ] Page-visit tracking failure does not block navigation.
[ ] Partial failure in background refresh preserves last good data.

OPEN QUESTIONS

For Frontend

Should the creator page paginate parent groups beyond `limit=100`?
Should the completion chart show a data-volume/confidence indicator when groups are small?
Should Most Visited Pages support sorting, more than 5 rows, or a date range?
Should the exported report honor the active tab filters or always export the full dataset?

For Backend

Should completion snapshots be backfilled from historical enrollment data instead of starting at first deploy?
Should `page_visits` have a retention/pruning policy, and what duration?
Should the hierarchy API expose per-tab endpoints/caching to reduce payload size?
Should snapshot capture happen on a schedule instead of lazily on analytics load to avoid gaps?

SUCCESS METRICS

Admin can review group and learner performance without exporting (workflow completion).
Admin can export a report in one action (reporting efficiency).
Assigned-quest and completion-rate values are identical between agency and creator surfaces (error reduction: zero known mismatches).
Creator can identify at-risk groups from metric cards (adoption of Hierarchy/Comparison tabs).
Completion trend renders real history once two days of data exist (data fidelity).
Admin can identify the top content by visits per month (content insight).
Page-visit tracking never blocks a learner (navigation failure rate: 0%).
Performance target: Not specified.

DEPENDENCIES

This Feature Depends On

Existing features: Learner Groups (11.4/11.5), cohort enrollment (`group_enrollments`), quest editor → Assign-To-Groups, quest gamification (`quest_gamifications`), Supabase Auth.
Database tables: `main_learner_groups`, `learner_groups`, `group_memberships`, `learner_group_quests`, `group_enrollments`, `quest_enrollments`, `app_users`, `learner_global_stats`, `learner_achievements`, `global_achievements`, `agencies`, `agency_members`, `group_completion_snapshots`, `page_visits`.
APIs: all endpoints in §9.
Services: `lib/analytics/completionSnapshots.ts`, `lib/learnerGroups/groupQuestScope.ts`, `lib/learnerGroups/groupCreator.ts`, `lib/learnerGroups/groupPermissionAccess.ts`, `lib/auth/authenticate`.
Authentication: Supabase Auth (`app_users.clerk_id` stores `auth.users.id`).
Migrations: `20260806_create_learner_groups.sql`, `20260816_01_create_main_learner_groups.sql`, `20260816_02_add_sub_group_hierarchy.sql`, `20260816_03_learner_group_hierarchy_rpc.sql`, `20260815/20260821_learner_group_stats_bulk_rpc.sql`, `20260821_create_group_completion_snapshots.sql`, `20260821_add_learner_xp_rpc.sql`, `20260907_create_page_visits_table.sql`, `20260909_harden_page_visits_paths.sql`.
Libraries: Zod, recharts, `lib/csv.ts`.

These Features Depend On This

Creator Hub group management surfaces (shared hierarchy API).
Agency dashboard and reporting.
Presence/activity-log handover references the same agency analytics module.
Learner page-visit reports consumed by agency analytics.

TIMELINE & OWNERSHIP
Owner: clydetims (Clyde Ador)
Implementation timeline: Not specified (branch work merged August–September 2026).
Completion date: Merged via PRs #781, #789, #797, #805, #808.
Follow-up work: apply migrations in order; backfill decisions for snapshots and page_visits retention (see §15).

Blocking environment prerequisites:

Apply `20260821_*`, then `20260907`, then `20260909` migrations in order.
Completion history accrues from first analytics load after deploy; the trend chart stays on its projection until each group has two captured days.

Document Version

1.0 - Initial Version - 2026-10-02