Learner Group Management | WyzQuests Documentation

Feature Owner: Rico Angelo O. Alipit
Module: Organization
Priority: P1
Sprint: #15
Date: August 14, 2026

EXECUTIVE SUMMARY

What is this feature?

Agency-scoped learner groups (cohorts). Creators can create named groups of learners, add members manually or via CSV, optionally assign a manager, and attach published quests to a group. A dedicated group dashboard shows per-member progress (completion %), XP, and badges for the group's assigned quests.

Why does it matter?

Currently there is no way to group learners into teams/departments, so assigning a quest to a cohort and tracking its completion as a unit is manual and error-prone. This feature turns "enroll these 30 people and check each one individually" into "assign the quest to the Sales Team group once and read one completion number."

What's the MVP scope?

Build the group CRUD (create/edit/archive/delete/duplicate), membership management (manual + CSV), quest assignment, and the group dashboard (members + completion + XP/badges). Out of scope for v1: auto-enrolling group members into assigned quests (assignment is metadata-only).

1. USER PAIN POINT & SOLUTION

Current State (Without Feature)

A creator who wants to track a cohort manually enrolls each learner into each quest one-by-one, then checks each learner's progress individually to compute a team completion figure. There is no grouping primitive, no team-level aggregate, and no export.

Pain Point

Emotional: Overwhelm when juggling many individual learners across many quests.
Functional: Slow, repetitive, error-prone; no single view of a team's status.
Business Impact: Lost instructor time; inconsistent reporting to departments/clients.

Future State (With Feature)

A creator creates a "Sales Team 2026" group once, adds members (manual or CSV), assigns quests, and reads one dashboard showing every member's completion %, XP, and badges — plus a one-click CSV export.

Marketing Hook

"Assign a quest to a whole team in one click, and track every member's completion from a single dashboard."

2. 4D FRAMEWORK MAPPING

Diagnose

The group dashboard's per-member completion table exposes exactly which members are falling behind on which assigned quests (completion % is per member per quest, averaged only across quests the member actually enrolled in).

Design

Assigning a quest to a group is the planning primitive — "this cohort should complete these quests" — which shapes the learning path a team follows.

Develop

Groups do not alter quest authoring; the develop phase is unchanged. The feature lives entirely in the agency/creator management layer.

Deliver

CSV export of the member list (name, email, added date, completion %, XP, badges) delivers team results to stakeholders; group stats (members, quests, avg completion, total XP, badges) surface the cohort's outcome.

3. USER FLOWS

Entry Point

Creator Hub dashboard (app/creator/page.tsx) → Learner Management card (rendered by LearnerGroupAnalytics, placed between CreatorAnalyticCards and MyContent). The section is hidden entirely for creators with no agency affiliation.

Success Criteria

A group exists, has members and assigned quests, and its dashboard shows a meaningful average completion rate per member and for the group.

Main Flow (Happy Path)

Creator opens Creator Hub; the Learner Management card lists existing groups with a search box, paginated at 5 per page.
Creator clicks New Group (visible only to OWNER/ADMIN/CREATOR).
GroupModal asks for name (required, ≤100 chars), description (optional, ≤500), manager (optional dropdown of agency OWNER/ADMIN/CREATOR members).
On save → POST /api/agency/groups → group appears in the searchable list (AC 1.4).
Creator clicks the group row → navigates to /creator/learner-groups/[id] (Group Dashboard).
From the dashboard, creator uses Add Members (manual multi-select), Bulk Add (CSV), and Assign Quests (multi-select of published agency quests).
Dashboard shows stat cards + assigned quests + the members table (completion, XP, badges) and Export CSV.

Edge Cases

No data: Empty list shows "No learner groups yet. Create your first cohort."; empty members table shows "No members yet"; empty assigned quests shows "No quests assigned yet."
No candidate learners: Add Members modal shows "No candidate learners found…" when the agency has no assignable learners.
API error: Every fetch wraps in try/catch and surfaces a toast.error; the group list keeps stale state visible rather than crashing.
Permission denied: Non-manager agency members (e.g. REVIEWER) see the list/dashboard read-only — the New Group button and all management actions are hidden (driven by the canManage flag returned by the API).
Deleted/archived group: Group detail returns 404 → "Group not found" screen with a back link.
Stale list response: A sequence counter (fetchSeq) discards out-of-order responses after rapid search/page changes; deleting the last item on a page steps back to the previous page.

Decision Points

IF user role is OWNER/ADMIN/CREATOR → show management actions (canManage).
ELSE → read-only view (REVIEWER / other members).
IF a group is ARCHIVED → hide Add Members / Bulk Add / Assign Quests / Edit / Duplicate; show Restore instead of Archive.
IF group has members at delete time → offer "Reassign members to another group" (optional); reassignment is idempotent (skips members already in the target).

4. INFORMATION ARCHITECTURE

Primary Information (Always visible)

Group name + Archived badge (list and dashboard)
Member count · Quest count (list row subtitle)
Dashboard stat cards: Members, Quests, Avg Completion, Total XP, Badges

Secondary Information

Description (dashboard subtitle, "No description" when null)
Manager name (dashboard, only when set)
Member rows: name, email, added date, completion %, XP, badges

Tertiary Information (Hidden until needed)

Reassign target picker (only in Delete dialog when the group has members)
Search inputs (groups list; members table client-side filter)

Actions

Primary CTA:

New Group (list header, manager-only)
Assign Quests (dashboard header, ACTIVE + manager-only)

Secondary Actions:

Row menu per group: Edit, Duplicate, Archive/Restore, Delete
Dashboard: Add Members, Bulk Add, Export CSV
Members table: per-row remove (X), unassign quest (X)

5. WIREFRAMES

Key Screens:

Main view — Learner Management card: search box, paginated group list (rows link to the dashboard), header "New Group" button.

+-------------------------------------------------------+
| Learner Management                          [+ New Group] |
|                                                       |
| +---------------------------------------------------+ |
| | [search] Search groups...                         | |
| +---------------------------------------------------+ |
|                                                       |
| +---------------------------------------------------+ |
| | Sales Team 2026                            >       | |
| |   12 members - 3 quests                            | |
| +---------------------------------------------------+ |
| | Marketing Cohort  [Archived]            >   ...    | |
| |   5 members - 1 quest                              | |
| +---------------------------------------------------+ |
|                                                       |
|            < Previous     Page 1 of 3     Next >      |
+-------------------------------------------------------+

Modal/detail view — GroupModal (Create/Edit), AddGroupMembersModal, BulkAddGroupMembersModal, AssignGroupQuestsModal, DeleteGroupDialog.

Empty state — "No learner groups yet…", "No members yet", "No quests assigned yet."

Loading state — animate-pulse skeleton cards (list + dashboard header + 5 stat cards + table).

Error state — "Group not found" with back link; toasts for API failures.

Annotations:

The New Group button is gated by canManage from GET /api/agency/groups.
Group rows are <Link> to /creator/learner-groups/[id].
The manager/learner/quest dropdowns fetch candidates from GET /api/agency/groups/meta.

6. WIREFLOWS

Plaintext wireflow of the primary journey (create → add members → assign quests → dashboard):

[Creator Hub Dashboard]
        |
        v
[Learner Management card]
  -- canManage? --No--> read-only list (REVIEWER)
        |Yes
        v
[+ New Group] --> [GroupModal]
        |  name (required), description, manager (optional)
        |  POST /api/agency/groups
        v
[List refreshed] -- click row --> [Group Dashboard /creator/learner-groups/[id]]
        |                               |
        |                               |--> [Add Members]   -> POST /api/agency/groups/[id]/members
        |                               |--> [Bulk Add]      -> POST /api/agency/groups/[id]/members/bulk (CSV)
        |                               |--> [Assign Quests] -> POST /api/agency/groups/[id]/quests
        |                               |--> [...] -> Edit (PATCH) / Duplicate (POST .../duplicate)
        |                               |            / Archive (PATCH status) / Delete (DELETE + reassign)
        |                               \--> [Export CSV]    (client-side)

Edge-case wireflow (delete with reassignment):

[... -> Delete] --> [DeleteGroupDialog]
        |  member_count > 0?
        |--No--> confirm -> DELETE /api/agency/groups/[id]
        |
        \--Yes-> optional "Reassign members to another group" picker
                   |
                   |-- no reassign  -> members' group membership dropped
                   |-- reassign X   -> members moved to X (idempotent, skips existing)
                   \-- confirm -> DELETE /api/agency/groups/[id] (reassign rolled back on failure)

7. PROTOTYPE

Figma Prototype Link: N/A (no link provided — attach here).

How to test (manual):

As an agency CREATOR/ADMIN, open Creator Hub → Learner Management → New Group → create "Test Cohort".
Add Members → multi-select learners → confirm count toast.
Bulk Add → drop a CSV with email,first_name,last_name → confirm added/failed summary toast.
Assign Quests → select published agency quests.
Open the group → verify stat cards, member completion bars, XP/badges, Export CSV.
Archive then Restore the group; verify actions hide/show correctly.
Delete the group with reassignment to another group.

8. BACKEND SCHEMA

Database Tables

-- learner_groups: the cohort (agency-scoped, soft-deletable)
CREATE TABLE 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()
);
 
-- group_memberships: learners in a group
CREATE TABLE 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: quests assigned to a group (metadata only, no auto-enroll)
CREATE TABLE 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)
);

Indexes:

idx_learner_groups_agency_id (list/search by agency)
idx_learner_groups_status (ACTIVE/ARCHIVED filter)
idx_learner_groups_manager_id (manager lookups)
idx_group_memberships_learner_id (member reverse-lookups)
idx_learner_group_quests_quest_id (quest reverse-lookups)

Constraints:

PK (group_id, learner_id) and (group_id, quest_id) prevent duplicate membership/assignment.
name 1–100 chars, description ≤500 chars, status in ('ACTIVE','ARCHIVED').
RLS policies enforce agency scoping: SELECT via is_agency_member, write via is_agency_manager; membership inserts additionally require is_agency_learner and an ACTIVE group; quest-assignment inserts require is_agency_quest.

Helper functions (same migration):

is_agency_member(agency_id) — any ACTIVE agency member (owner or member row).
is_agency_manager(agency_id) — owner, or ACTIVE member with role ADMIN/CREATOR.
is_agency_user(agency_id, user_id) — the quest-owning set (owner, direct agency, or ACTIVE member).
is_agency_learner(agency_id, user_id) — LEARNER role + (agency_id match OR enrolled in an agency-owned quest).
is_agency_quest(agency_id, quest_id) — published quest created by an agency user.

Stats RPC (separate migration 20260806_learner_group_stats_rpc.sql):

CREATE OR REPLACE FUNCTION get_learner_group_stats(group_id uuid)
RETURNS TABLE (learner_id uuid, xp_rewards bigint, badges text[])
LANGUAGE sql SECURITY DEFINER STABLE;
-- Aggregates per-member XP from learner_global_stats and unlocked global
-- badge names from learner_achievements (is_quest = FALSE), guarded by
-- agency membership (or service_role).

9. API ENDPOINTS

All endpoints are under /api/agency/groups. Auth is via Clerk JWT; the service uses the Supabase service-role client (RLS bypassed) with access control enforced in the route handlers.

`GET /api/agency/groups`

Purpose: List the caller agency's groups (searchable, paginated).
Auth: authenticateAgencyMember (any member).
Query Params: page (1-based, default 1), limit (1–100, default 10), q (search name/description), all=1 (return every group, no pagination).
Response 200:

{ "success": true, "data": { "groups": [ ... ], "canManage": true, "total": 10,
  "pagination": { "page": 1, "limit": 5, "total": 10, "totalPages": 2 } } }

`POST /api/agency/groups`

Purpose: Create a group.
Auth: authenticateAgencyManager (OWNER/ADMIN/CREATOR).
Body: { "name": "Sales Team", "description": "Q3 cohort", "managerId": "uuid" }.
Response 201: { "success": true, "data": { "group": { ...summary } } }.

`GET /api/agency/groups/[id]`

Purpose: Group detail (group + members + quests + stats).
Auth: authenticateAgencyMember.
Response 200: { "success": true, "data": { "group": {...}, "members": [...], "quests": [...], "stats": {...}, "canManage": true } }.
Response 404: group not found (cross-agency or deleted).

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

Purpose: Update name/description/manager_id, or archive/restore via status.
Auth: authenticateAgencyManager.
Body: partial — { name?, description?, managerId?, status? }.
Response 200: updated summary.

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

Purpose: Hard delete with optional member reassignment.
Auth: authenticateAgencyManager.
Body: { "reassignGroupId": "uuid" } (optional).
Response 200: { "success": true }.

`POST /api/agency/groups/[id]/members`

Purpose: Add learners by id (manual selection).
Auth: authenticateAgencyManager.
Body: { "learnerIds": ["uuid", ...] } (1–1000, deduped).
Response 200: { "added": 3, "failed": [ { "id", "reason" } ] }.

`POST /api/agency/groups/[id]/members/bulk`

Purpose: Bulk-add via CSV (multipart field file).
Auth: authenticateAgencyManager.
CSV headers: email, first_name, last_name. Matching by email (case-insensitive) against existing LEARNER accounts; no auto-provisioning.
Response 200: { "added": 5, "failed": [ { "email", "reason" } ] }.

`DELETE /api/agency/groups/[id]/members/[learnerId]`

Purpose: Remove a member.
Auth: authenticateAgencyManager.
Response 200: { "success": true }.

`POST /api/agency/groups/[id]/quests`

Purpose: Assign quests (metadata only).
Auth: authenticateAgencyManager.
Body: { "questIds": ["uuid", ...] }.
Response 200: { "assigned": 2, "invalid": [] }.

`DELETE /api/agency/groups/[id]/quests/[questId]`

Purpose: Unassign a quest.
Auth: authenticateAgencyManager.

`POST /api/agency/groups/[id]/duplicate`

Purpose: Duplicate group (copies metadata, members, quest assignments).
Auth: authenticateAgencyManager.
Response 201: { "success": true, "data": { "groupId": "uuid" } }.

`GET /api/agency/groups/meta`

Purpose: Modal data — candidate learners (with alreadyInGroup), candidate managers, agency quests.
Auth: authenticateAgencyManager.
Query Params: groupId (optional; marks existing members).
Response 200: { "learners": [...], "managers": [...], "quests": [...] }.

10. DATA REQUIREMENTS

Frontend Needs

LearnerGroupSummary — list rows (id, name, description, status, member_count, quest_count, manager).
LearnerGroupDetail — dashboard (group + manager, members with completion/XP/badges, quests, stats).
GroupMetaData — modal dropdowns (learners + alreadyInGroup, managers, quests).

API Calls Frontend Will Make

GET /api/agency/groups — list (mount, search debounce 300ms, page changes).
GET /api/agency/groups?all=1 — delete-dialog reassign picker.
POST /api/agency/groups — create.
PATCH /api/agency/groups/[id] — edit / archive / restore.
DELETE /api/agency/groups/[id] — delete (+ reassign).
POST .../duplicate — duplicate.
GET /api/agency/groups/[id] — dashboard detail.
GET /api/agency/groups/meta — modal data.
POST .../members, POST .../members/bulk, DELETE .../members/[learnerId] — membership.
POST .../quests, DELETE .../quests/[questId] — assignment.

Caching Strategy

No client cache; every mutation calls fetchDetail/reload to refetch. The list fetch is guarded by a sequence counter (fetchSeq) against out-of-order responses. Search is debounced 300ms.

11. PERFORMANCE CONSIDERATIONS

Database Optimization

All .in() lookups are chunked at LIST_BATCH = 100 so PostgREST URLs never overflow.
Per-member progress is fetched by batching member×quest pairs in chunks of 100.
Member/quest counts use count: "exact", head: true.
XP/badges are aggregated in a single SECURITY DEFINER RPC instead of per-member queries.

Caching Strategy

None (server-rendered client components refetch on mutation).

API Response Time

Target: sub-second for the list; the dashboard detail makes several sequential reads (group → members → quests → progress → stats RPC) — acceptable for MVP but a candidate for parallelization (Promise.all) in a follow-up.

12. SECURITY & AUTHORIZATION

Access Matrix

Action	OWNER	ADMIN	CREATOR	REVIEWER / other member	Learner
View groups list	✓	✓	✓	✓	✗
View group dashboard	✓	✓	✓	✓	✗
Create / edit / archive / delete / duplicate	✓	✓	✓	✗	✗
Add / remove members (manual + CSV)	✓	✓	✓	✗	✗
Assign / unassign quests	✓	✓	✓	✗	✗

Authorization Logic

Read routes use authenticateAgencyMember() and return a canManage flag (role === "OWNER" | "ADMIN" | "CREATOR"); the UI hides management actions based on it.
Mutation routes use authenticateAgencyManager() which throws unless the caller owns the agency or is an ACTIVE ADMIN/CREATOR member.
Both honor "View As" role switching (isRestrictedFromAgencyMember guard).
Service-layer re-checks enforce agency scoping on every query (.eq("agency_id", agencyId)), so cross-agency access is impossible even with the service-role client.

Data Validation

Zod schemas (lib/schemas/learner-group.schema.ts): name 1–100, description ≤500, learnerIds/questIds1–1000 deduped, status enum, reassignGroupId/managerId UUID.
Server re-validates manager/learner/quest against the agency's candidate sets (a crafted request cannot add learners or assign quests from another agency).
CSV is capped at 1MB, structurally validated, and per-row failures are surfaced in failed.

13. ERROR HANDLING

Common Errors

401 Unauthorized: authenticateAgencyMember/Manager throws; ApiResponseHelper.handleError maps to a JSON error; frontend shows toast.error.
403 Forbidden: non-manager hitting a mutation route → "Access denied - Only agency owners, admins and creators can manage learner groups".
404 Not Found: group/member/quest not found (or cross-agency) → "Group not found"; dashboard renders the "Group not found" screen.
422 Validation Error: Zod failure → validationError("Invalid group data", issues); form surfaces field errors (react-hook-form + zodResolver).
400 Bad Request: archived-group mutation ("Cannot edit an archived group"), reassign to self/archived group, malformed CSV, oversized CSV.
500 Server Error: compensating rollbacks ensure a mid-batch failure (members, quests, reassign, duplicate) never leaves partial state; the frontend shows a generic toast and retries on next action.

14. TESTING CHECKLIST

Happy Path

[ ] Create a group with name/description/manager → appears in list.
[ ] Search filters the list; pagination works (5/page).
[ ] Add members manually (multi-select) → count toast + table updates.
[ ] Bulk add via CSV → added/failed summary.
[ ] Assign quests → appears under "Assigned Quests".
[ ] Dashboard shows stat cards + per-member completion/XP/badges.
[ ] Export CSV downloads correctly.
[ ] Duplicate copies members and quests.
[ ] Archive hides management actions; Restore brings them back.
[ ] Delete with reassignment moves members; without reassignment drops them.

Edge Cases

[ ] Empty states (no groups / no members / no quests).
[ ] Reviewer sees read-only (no New Group button).
[ ] Cross-agency group id → 404.
[ ] Archived group blocks edit/member/quest mutations.
[ ] Duplicate learner ids / duplicate CSV emails rejected with reason.
[ ] CSV with unknown/non-learner/other-agency emails → per-row failure reasons.
[ ] Mid-batch failure leaves no partial state (rollback).

15. OPEN QUESTIONS

For Frontend

None blocking — search debounce (300ms), page-size (5), and the sequence guard are implemented. Confirm whether the page size of 5 matches product expectations.

For Backend

None blocking — XP/badges aggregation is an RPC and quest assignment is metadata-only by design. Confirm whether auto-enrollment (a follow-up feature) is desired and whether added_by should be populated (currently always null).

16. OUT OF SCOPE (v1.1+)

Not building in v1:

Auto-enrollment of group members into assigned quests — assignment is metadata-only (learner_group_quests); actual enrollment is a separate, later feature.
CSV auto-provisioning of brand-new learner accounts — unmatched emails are rejected, not created.

Why: The core need is cohort organization + team-level tracking; enrollment fan-out carries its own idempotency/capacity concerns that deserve a dedicated design.

17. SUCCESS METRICS

How will we know this feature is successful?

Creators can create a group and attach members + quests in under a minute.
A team's completion can be reported from one dashboard without per-learner manual aggregation.
CSV export is used to share team progress with stakeholders (reduces manual report building).

18. DEPENDENCIES

This feature depends on:

agencies, app_users, agency_members (existing agency model).
quests, quest_enrollments, learner_global_stats, learner_achievements, global_achievements(existing learner/progress model).
Supabase migrations 20260806_create_learner_groups.sql and 20260806_learner_group_stats_rpc.sqlapplied.

These features depend on this:

Group-Based Enrollment (feat/group-based-enrollment) builds on learner_groups to actually enroll cohorts into quests.

19. TIMELINE & OWNERSHIP

Completed — Feature is fully implemented in Sprint #15.