Learner Group Management

Feature Owner: Rico Angelo O. Alipit
Module: Organization
Priority: P1
Sprint: #15
Date: August 14, 2026 (original handover)
Last Updated: September 23, 2026 — v3.1, see §20
References: wyzlab/WyzQuests#772 (hierarchy), #833 (main-group sharing), #836 (sub-group sharing), #869 (default cohorts); full detail in docs/learnergroup_tech_docs.md

This document is the original v1 PRD/handover for flat learner groups. Sections 1–19 describe that baseline and are unchanged where later PRs did not alter them. §20 records everything delivered after v1; where the two disagree, §20 and the tech doc win.


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).

As delivered (v3.1). The flat group survived as a supported tier. Four follow-ups sit on top: a Main Group → Sub Group hierarchy (#772), private-by-default sharing for main groups (#833) and then independently for sub groups (#836), and a one-default-cohort-per-creator guarantee with duplicate consolidation (#869). Every group is now permission-scoped, so "any agency member sees everything" (§12) no longer holds — access is owner ∨ agency manager ∨ explicit grant.


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)

  1. Creator opens Creator Hub; the Learner Management card lists existing groups with a search box, paginated at 5 per page.

  2. Creator clicks New Group (visible only to OWNER/ADMIN/CREATOR).

  3. GroupModal asks for name (required, ≤100 chars), description (optional, ≤500), manager (optional dropdown of agency OWNER/ADMIN/CREATOR members).

  4. On save → POST /api/agency/groups → group appears in the searchable list (AC 1.4).

  5. Creator clicks the group row → navigates to /creator/learner-groups/[id] (Group Dashboard).

  6. From the dashboard, creator uses Add Members (manual multi-select), Bulk Add (CSV), and Assign Quests (multi-select of published agency quests).

  7. 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

  • Main Group cards in the Learner Management list are collapsible: main name, N active teams, N members, and a Sub Groups section on expand

  • Share dialogs list each co-creator with their effective role (Owner / Manager / Editor / Viewer) and a locked state

Secondary Information

  • Description (dashboard subtitle, "No description" when null)

  • Manager name (dashboard, only when set)

  • Member rows: name, email, added date, completion %, XP, badges

  • Per-row permission affordances on sub-group rows: canView / canEdit / canManage flags returned by the API

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)

  • Share subgroups checkbox matrix inside the main group's Share dialog (per co-creator × per sub group)

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, Share (sub groups, #836)

  • Main Group card: Add Sub Group; Share (Viewer/Editor per co-creator); per-sub-group Share

  • Dashboard: Add Members, Bulk Add, Export CSV

  • Members table: per-row remove (X), unassign quest (X)


5. WIREFRAMES

Key Screens:

  1. 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 > |
+-------------------------------------------------------+
  1. Modal/detail view — GroupModal (Create/Edit), AddGroupMembersModal, BulkAddGroupMembersModal, AssignGroupQuestsModal, DeleteGroupDialog.

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

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

  4. 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)

7. PROTOTYPE

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

How to test (manual):

  1. As an agency CREATOR/ADMIN, open Creator Hub → Learner Management → New Group → create "Test Cohort".

  2. Add Members → multi-select learners → confirm count toast.

  3. Bulk Add → drop a CSV with email,first_name,last_name → confirm added/failed summary toast.

  4. Assign Quests → select published agency quests.

  5. Open the group → verify stat cards, member completion bars, XP/badges, Export CSV.

  6. Archive then Restore the group; verify actions hide/show correctly.

  7. 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)
);

Added after v1 (see §20 for the full list):

-- main_learner_groups: the top tier. Holds no members/quests.
-- UNIQUE (agency_id, id) exists so learner_groups can carry a composite FK.
-- is_default_cohort BOOLEAN NOT NULL DEFAULT false (#869)
CREATE TABLE 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(),
is_default_cohort BOOLEAN NOT NULL DEFAULT false, -- #869
UNIQUE (agency_id, id)
);
 
-- learner_groups gains two columns:
-- parent_group_id UUID NULL (#772)
-- FOREIGN KEY (agency_id, parent_group_id)
-- REFERENCES main_learner_groups (agency_id, id) ON DELETE CASCADE
-- is_default_cohort BOOLEAN NOT NULL DEFAULT false (#869)
 
-- Sharing grants: one table per level, same shape. (#833 / #836)
CREATE TABLE public.learner_group_permissions ( -- main groups
group_id UUID NOT NULL REFERENCES main_learner_groups(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES app_users(id),
permission_level TEXT NOT NULL DEFAULT 'view' CHECK (permission_level IN ('view','edit')),
granted_by UUID REFERENCES app_users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (group_id, user_id)
);
CREATE TABLE public.learner_group_subgroup_permissions ( -- sub groups
subgroup_id UUID NOT NULL REFERENCES learner_groups(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES app_users(id),
permission_level TEXT NOT NULL DEFAULT 'view' CHECK (permission_level IN ('view','edit')),
granted_by UUID REFERENCES app_users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (subgroup_id, user_id)
);
-- Neither table stores owner/manager: those roles are implicit and always outrank a row.

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)

  • idx_learner_groups_parent_group_id (partial) — sub groups of a main group (#772)

  • uq_learner_groups_active_subgroup_name (unique, partial) — sibling sub-group names (#772)

  • uq_main_learner_groups_default / uq_learner_groups_default (unique, partial) — one ACTIVE default cohort per (agency, creator) / per parent (#869); the anti-duplicate guarantee

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": [...] }.

Added after v1 (#772 / #833 / #836)

Method & Path

Auth

Purpose

GET /api/agency/groups/main

any resolved main-group role

Hierarchy (main + nested sub groups + counts); unshared groups/sub groups are filtered out and each row is decorated canEdit / canManage / canView

POST /api/agency/groups/main

manager

Create main group

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

canEdit on that main group

Edit / archive / restore; also renames the default sub group when a default parent is renamed (#869)

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

canManage on that main group

Hard delete; the whole subtree cascades (no reassignment)

POST /api/agency/groups/subgroups

canEdit on the parent

Create sub group

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

any resolved main-group role

Co-creators + effective roles. id = main group id

POST | PATCH | DELETE /api/agency/groups/[id]/permissions

main-group owner ∨ manager

Grant view/edit, change, revoke (granted_by recorded)

GET /api/agency/groups/subgroups/[id]/permissions

any resolved sub-group role

Same for sub groups. id = sub group id — note the /subgroups/ prefix

POST | PATCH | DELETE /api/agency/groups/subgroups/[id]/permissions

sub-group owner ∨ manager

Grant / change / revoke a sub-group grant

GET /api/agency/groups/[id]/subgroups-access

parent owner ∨ manager

Every sub group of the main [id] with the target's role + locked. ?user_id= required

POST /api/agency/groups/[id]/subgroups-access

parent owner ∨ manager

Batch grant/revoke one creator's access across sub groups

Every flat [id] route above now also requires a resolved role on that sub group ([id] read: any role, mutations: edit, delete/share: owner ∨ agency manager). Sub-group access is independent — parent access never grants it. No new endpoint exists for default cohorts (#869); provisioning rides existing enrollment and team-claim routes.


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

⚠️ Superseded by #833/#836: the "everyone in the agency sees everything" rows below no longer hold. Access is now owner ∨ agency manager ∨ explicit grant, resolved per group, and every unshared group is hidden from the list.

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

✓

✓

✓

✗

✗

(v3.1) View a group / sub group not shared with them

✓

✓

✗

✗

✗

(v3.1) Edit a group where they hold an Editor grant

✓

✓

✓ (grant)

✓ (grant)

✗

(v3.1) Share / delete a group

✓

✓

✓ (owner or grant-holder owner)

✗

✗

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.

  • Per-group layer (v3.1). After the agency check, routes resolve the caller's role on that group — resolveMainGroupRole / resolveSubGroupRole — with precedence owner > manager > edit > view > no access. Membership in the agency grants nothing by itself.

  • Grants are addressed by internal app_users.id, never by email, and self-targeting is rejected.

  • scopeContextToAgency(context, agencyId) pins the auth context to the group's agency before any ownership check, so a user who owns agency A and is a member of B is not denied their own group.

Data Validation

  • Zod schemas (lib/schemas/learner-group.schema.ts): name 1–100, description ≤500, learnerIds/questIds 1–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.

  • 400 "Main groups are not yet available. Push the database migrations first." — the new routes catch 42P01/42883 so the code can deploy before the SQL. Not a bug.

  • 403 "You do not have access to this subgroup" / "Only the group owner or an agency manager can manage sharing" — no resolved role on that group, or a share attempt without owner/manager.

  • 409 "This creator already has a saved permission. Use PATCH to change it." — POST where a grant row exists.

  • 400 "This user already manages the subgroup and does not need an explicit grant" — the target is the sub-group owner or an agency OWNER/ADMIN, so a row would be inert.


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).

Added after v1

  • Create a Main Group, add two Sub Groups → the card shows 2 active teams and rolled-up member counts (#772).

  • Archive the Main Group → both children flip to Archived; a direct child restore is rejected until the parent is restored (#772).

  • Creator B (no grant) does not see the group anywhere and gets 403 opening it by direct URL (#833).

  • Grant B Viewer → B sees the group read-only; Edit/Delete hidden and writes 403 (#833).

  • Upgrade B to Editor → B can rename/archive/manage members but still cannot delete or share.

  • B (Viewer on the parent) still cannot see an unshared Sub Group; share it via Share subgroups → B now sees only that sub group (#836).

  • Revoke a sub-group grant → it disappears from B's list immediately.

  • Enroll a learner into a creator's quest → the creator gains one {creator} (default) parent + sub group with the learner in it (#869).

  • Enroll again / enroll a second learner → no second "{creator} (default)" family is created; the pair grows instead.

  • Rename the default parent → its default sub group name follows to {new base} (default) (#869).

  • Run scripts/consolidate-default-cohorts.ts (dry-run) → previews duplicate families only, writes nothing.


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.

Status as of v3.1: the hierarchy (#772) and both sharing tiers (#833/#836) are now built. Auto-enrollment remains metadata-only for manually assigned quests — the only automated enrollment path is cohort provisioning (#869), which fires when a learner is enrolled into a quest and then scopes that quest plus the cohort's other ACTIVE group quests under the creator's default sub group. CSV account provisioning is still not implemented.


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.sql applied.

  • Migrations applied in this order (a wrong order fails):
    20260816_01_create_main_learner_groups → 20260816_02_add_sub_group_hierarchy →
    20260816_03_learner_group_hierarchy_rpc → 20260915_create_learner_group_permissions →
    20260916_add_granted_by_to_learner_group_permissions →
    20260919_create_learner_group_subgroup_permissions →
    20260920_remove_inert_subgroup_permissions → 20260921_mark_default_cohorts.

These features depend on this:

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

  • Agency Analytics reads the canEdit / canManage / canView flags returned at both group levels.


19. TIMELINE & OWNERSHIP

Completed — Feature is fully implemented in Sprint #15.


20. POST-v1 DELIVERY LOG (v2.0 → v3.1)

Version

PR

Merged

Delivered

v2.0

#772 1d1d6744

2026-08-19

Main Group → Sub Group hierarchy: main_learner_groups, learner_groups.parent_group_id with a same-agency composite FK, archive-cascade trigger, get_learner_group_hierarchy RPC, main/sub API routes, collapsible analytics, type-confirmed delete

v2.1

#833 e7a1bcbf

2026-09-17

learner_group_permissions + granted_by, role resolution owner > manager > edit > view > none, /[id]/permissions CRUD, private-by-default main groups, Share modal

v3.0

#836 89191f24

2026-09-18

learner_group_subgroup_permissions, subGroupPermissionAccess.ts, /subgroups/[id]/permissions + /[id]/subgroups-access, permission gates on every flat sub-group route, Share subgroups dialog

v3.1

#869 9cba07a8

2026-09-23

is_default_cohort identity columns + partial unique indexes, SQL consolidation of duplicate default-cohort families, cohort provisioning on enrollment / team claim / admin member add, backfill + consolidation scripts

What a new team member should know first:

  1. A sub group is a learner_groups row; a main group is a main_learner_groups row. Legacy flat groups (parent_group_id IS NULL) still work and are invisible in the main-group list.

  2. Access is per group, not per agency. Main and sub groups have separate grant tables and separate gates; parent access never implies sub-group access.

  3. Edit ≠ manage. edit allows rename/archive/members/quests/duplicate; only owner ∨ agency manager may delete or share. The UI must not surface Delete to an editor.

  4. The default cohort's identity is the is_default_cohort flag, not its "{creator} (default)" name — renaming cannot fork a second default, and the partial unique indexes are the backstop.

Known gaps carried by v3.1 (detail in the tech doc): the main-group share GET still sweeps inert grant rows (the sub-group path does not); batch sub-group access silently drops locked/foreign ids instead of reporting them; cross-agency learners are never provisioned into a creator's cohort; duplicating a sub group drops parent_group_id (the copy becomes a flat group); deleting a main group has no reassignment path.

Operational note: both cohort scripts are service-role and bypass RLS — always --dry-run first.


Was this article helpful?