Learner Group Management

Author: Rico Angelo O. Alipit
Reviewer: Patrick Babala, Christian Denzon, and Clyde Timothy
Creation Date: August 5, 2027
Status: Published
References: https://github.com/wyzlab/WyzQuests/issues/63

Introduction & Goals

Problem Summary

Creators have no primitive for grouping learners into teams or departments. Tracking a cohort means enrolling each learner into each quest individually and manually aggregating progress. This feature introduces agency-scoped learner groups (cohorts) — named groups with members, optional manager, and assigned quests — plus a dashboard that reports per-member completion %, XP, and badges for the group as a unit.

Goals & Non-Goals

Goals:

• Full group CRUD: create, edit, archive/restore, hard-delete, duplicate.

• Membership management: manual multi-select add, CSV bulk add, remove.

• Quest assignment: attach/detach published agency quests (metadata only).

• Group dashboard: stat cards, per-member completion/XP/badges table, CSV export.

• Agency-scoped security with role-gated mutations (OWNER/ADMIN/CREATOR manage; REVIEWER read-only).

Non-Goals (v1):

• No auto-enrollment of group members into assigned quests. learner_group_quests is metadata only; enrollment fan-out is feat/group-based-enrollment (v1.1+).

• No CSV auto-provisioning of new learner accounts — unmatched emails are rejected with a per-row reason, never created.

• No cross-agency groups; every table row is agency-scoped.

Glossary

High-Level Architecture

System Diagram

+---------------------------+      +-----------------------------------------+
|         BROWSER           |      |              NEXT.JS (API)              |
|                           |      |                                         |
| Creator Hub card          |----->| /api/agency/groups/** (9 handlers)     |
| (LearnerGroupAnalytics)   |      |        |                                |
| Group Dashboard           |----->|        v                                |
| /creator/learner-groups/..|      | authenticateAgencyMember /             |
| Group modals (5)          |----->| authenticateAgencyManager              |
+---------------------------+      |   + View-As guard                      |
        |                          |        |                                |
        | Clerk JWT session        |        v                                |
        +------------------------->| learnerGroupService.ts                 |
                                   |   (service-role client, RLS bypassed)   |
                                   |        |                                |
                                   |        v                                |
                                   | Zod schemas (learner-group.schema.ts)   |
                                   +-----------------+-----------------------+
                                                     |
                                                     v
                                   +-----------------------------------------+
                                   |               SUPABASE                   |
                                   |  learner_groups / group_memberships /   |
                                   |  learner_group_quests  (RLS policies +  |
                                   |  is_agency_* helpers)                   |
                                   |  get_learner_group_stats RPC            |
                                   |  (SECURITY DEFINER)                     |
                                   +-----------------------------------------+

All data access goes through the service-role client (RLS bypassed); authorization is enforced in the route layer via authenticateAgencyMember / authenticateAgencyManager, and every service query additionally pins .eq("agency_id", agencyId) so cross-agency reads are impossible even with service-role privileges.

Technologies Used

Detailed Design & Implementation

Data Model / Schema

Migrations: supabase/migrations/20260806_create_learner_groups.sql, supabase/migrations/20260806_learner_group_stats_rpc.sql.

agencies 1 ─── * learner_groups ─── 1 app_users (creator_id / manager_id)
learner_groups 1 ─── * group_memberships * ─── 1 app_users (learner_id)
learner_groups 1 ─── * learner_group_quests * ─── 1 quests
 
learner_groups
  id UUID PK  agency_id FK  creator_id FK
  name TEXT (1-100)  description TEXT (≤500)
  manager_id FK (nullable)  status ACTIVE|ARCHIVED
  archived_at  created_at  updated_at
 
group_memberships
  group_id PK/FK  learner_id PK/FK
  added_by FK (always null in v1)  added_at
 
learner_group_quests
  group_id PK/FK  quest_id PK/FK  assigned_at

• Composite PKs (group_id, learner_id) / (group_id, quest_id) prevent duplicates.

• All FKs ON DELETE CASCADE — deleting a group removes memberships/assignments.

• updated_at maintained by learner_groups_updated_at_trigger.

• RLS enabled on all three tables. Helpers: is_agency_member, is_agency_manager, is_agency_user, is_agency_learner, is_agency_quest. SELECT via member; INSERT/UPDATE/DELETE via manager; membership inserts also require ACTIVE group + agency learner; quest inserts require ACTIVE group + published agency quest.

• get_learner_group_stats(group_id) — SECURITY DEFINER RPC returning (learner_id, xp_rewards, badges) from learner_global_stats + learner_achievements (global badges, is_quest = FALSE). Guards on agency membership, but allows service_role (no sub in service JWT) since API routes already enforce scoping.

API Specification

All routes live under app/api/agency/groups/. Responses use the ApiResponseHelper envelope: { success, data?, error? }.

Auth model: Clerk JWT → authenticateAgencyMember() (any ACTIVE member) for reads; authenticateAgencyManager() for mutations (throws unless OWNER or ACTIVE ADMIN/CREATOR member). Both honor "View As" switching via isRestrictedFromAgencyMember. The service uses the Supabase service-role client; the API layer is the sole authorization gate.

Logic & Workflows

Completion-rate computation (getGroupDetail):

1. Load group, memberships, assigned quests (assignment order preserved).

2. Batch-fetch quest_enrollments progress for all member×quest pairs (chunks of 100).

3. Call get_learner_group_stats once for XP + badges per member.

4. Per member, average progress only over quests with an enrollment row — a member never enrolled in an assigned quest is excluded from their rate (and from the group average), so they are not penalized with a skewed 0%.

5. Group stats = member count, quest count, avg completion (over members with ≥1 enrollment), total XP, total badges.

Delete with reassignment:

1. Validate target (not self, exists, not archived).

2. Insert memberships into target (idempotent — skips already-present members), chunked at 100.

3. Delete source group; on failure, compensating rollback removes the just-inserted memberships from the target.

Duplicate: insert new group (name + " (Copy)", capped to 100 chars), copy memberships, copy quests; any mid-copy failure deletes the partial new group (members/quests cascade).

CSV bulk add: case-insensitive email match via PostgREST ilike (emails containing .or() grammar chars are excluded as invalid), reject non-learner / other-agency / duplicate / unknown emails per row, backfill profile name only when empty (best-effort, never overwrites), memberships inserted before name updates so a backfill error can't misreport a failed add.

Batching invariant: every .in() filter is chunked at LIST_BATCH = 100 so PostgREST URLs never overflow; bulk inserts are chunked; multi-batch writes carry compensating rollbacks so no mutation leaves partial state.

Frontend freshness: no client cache — every mutation refetches detail/list. The list guards out-of-order responses with a sequence counter (fetchSeq); search is debounced 300 ms; deleting the last row on a page steps back a page.

Infrastructure & Operations

Dependencies

Upstream (existing systems this feature relies on):

• agencies, app_users, agency_members (agency model)

• quests, quest_enrollments (enrollment/progress)

• learner_global_stats, learner_achievements, global_achievements (XP/badges)

• Clerk for session auth; Supabase service-role client

Downstream (features that depend on this):

• feat/group-based-enrollment — actually enrolling cohorts into quests.

Migration ordering: 20260806_create_learner_groups.sql then 20260806_learner_group_stats_rpc.sql (RPC references the new tables).

Monitoring & Alerting

• Route errors funnel through ApiResponseHelper.handleError → { success: false, error: { code, message } }; frontend surfaces toast.error.

• Service-level anomalies are logged via console.error (e.g., failed name backfill, unexpected auth failures in authenticateAgencyMember).

• No dedicated metrics/alerting for this feature — watch API error-rate via existing app logging; a regression typically surfaces as 500s on /api/agency/groups/** in VPS logs.

Deployment Plan

• Migrations must be applied to Supabase before deploying code (tables/RPC are hard dependencies).

• Standard deploy flow: merge to develop/staging → GitHub Actions (deploy.yml) SSHes to the Hostinger VPS, git reset --hard, npm install, npm run build, PM2 reload with health check + automatic rollback of the previous .next on build/health failure.

• No feature flags. Rollback path = redeploy previous commit; data changes are additive and backward-compatible (new tables only).

Testing & Quality Assurance

Test Strategy

• Manual QA checklist: docs/learner-group-management-handoff.md §14 (happy path + edge cases). No automated suite — the learner-groups test suite was removed from the branch (tests/agency/groups/learner-groups.test.ts exists in the working tree but is not part of this feature's scope).

• E2E infrastructure: Playwright specs exist under tests/e2e/ for agency scoping patterns and can be extended for groups using TEST_*_EMAIL / TEST_*_PASSWORD env vars.

• Peer review: three review rounds recorded under docs/review/.

Known Limitations

• No auto-enrollment — quest assignment is metadata only; members are not enrolled, so completion % for a member without an enrollment is excluded from averages by design.

• No CSV auto-provisioning — unmatched emails fail per row.

• Sequential dashboard reads — group → members → quests → progress → stats RPC run sequentially; acceptable for MVP, candidate for Promise.all.

• added_by is always null — no audit trail for who added a member.

• Page size fixed at 5 in the Creator Hub card (product confirmation pending).

• Reassign-on-delete only moves members, not quest assignments.

Maintenance & Support

Troubleshooting

Changelog

• 2026-08-14 — Feature complete on feat/learner-group-management; handoff + technical docs authored

Document version

1.0 - Draft, Initial technical guide authored alongside handoff doc on feat/learner-group-management, 14/08/2026