Author: Rico Angelo O. Alipit
Reviewer: Patrick Babala, Christian Denzon, and Clyde Timothy
Updated by: clydetims (Clyde Ador)
Creation Date: August 5, 2027
Status: Published
References: https://github.com/wyzlab/WyzQuests/issues/63, https://github.com/wyzlab/WyzQuests/pull/772, https://github.com/wyzlab/WyzQuests/pull/833, https://github.com/wyzlab/WyzQuests/pull/836, https://github.com/wyzlab/WyzQuests/pull/869
Introduction & Goals
Problem Summary
Creators had no way to group learners; a cohort meant manual per-learner
enrollment and manual aggregation. v1 added flat agency-scoped groups with members,
a manager, quest assignments, and a progress dashboard. Agencies actually work two
levels deep ("Grade 1" → "Team A/B/C"), which the flat list could not express.
Goals & Non-Goals
Goals:
Group Management: Support main group and subgroup CRUD, including archive/restore, archive cascade, and type-confirmed deletion.
Membership Management: Support manual multi-select and CSV bulk addition, member removal, and membership management.
Quest Assignment: Allow groups to attach and detach published agency quests.
Member Dashboard: Display per-member completion, XP, and badges, with dashboard statistics and CSV export.
Group Analytics: Provide collapsible analytics organized by main group and subgroup.
Access Control: Enforce agency-scoped role permissions:
OWNER / ADMIN / CREATOR: Manage groups and their configurations.
REVIEWER: Read-only access.
Group Sharing: Keep main groups and subgroups private by default, with explicit Viewer and Editor sharing permissions.
Default Cohort: Automatically provision exactly one default cohort per agency creator. Reuse the existing cohort and prevent duplicate creation or forking.
Non-Goals (v1):
Automatic Quest Enrollment: Group membership and quest assignment will not automatically enroll learners in quests. The
learner_group_queststable stores assignment metadata only; provisioning is limited to scoping quests that learners are already enrolled in.CSV Account Provisioning: CSV imports will not create new learner accounts. Unmatched email addresses will be rejected with a reason for each affected row.
Cross-Agency Groups: Groups and their memberships must remain within the same agency.
Third-Level Grouping: The hierarchy is limited to two levels: main groups and subgroups.
Glossary
Term | Definition |
|---|---|
Main group | Top-level unit represented by a |
Subgroup | A |
Legacy flat group | A group with |
Default cohort | The automatically provisioned pair consisting of a "{creator} (default)" parent group and its "{creator} (default)" subgroup. Exactly one pair per |
Default shape | Any of |
| Boolean identity marker for the default cohort, stored on both tables. The display name is cosmetic and may be stale. |
Owner / Manager | The group's |
Editor / Viewer | An explicit permission grant in |
No access | Any other creator in the same agency. Main groups and subgroups remain private until explicitly shared. |
Independent gate | Subgroup access does not inherit from its parent group. Access to each is resolved independently. |
| Boolean returned by a read endpoint. True for the group's owner or an agency OWNER/ADMIN. Required for deletion and sharing; an edit grant alone is insufficient. |
| Boolean defined as |
Locked | A share-modal row that cannot be changed or revoked because its role is implicit, such as the group owner or an agency manager. |
Agency manager | An agency OWNER or an ACTIVE |
Compensating rollback | Manual reversal of partial writes when a multi-step mutation fails. |
| Batch size of 100 for all |
High-Level Architecture
System Diagram
+---------------------------+ +--------------------------------------------+| BROWSER | | NEXT.JS (API) || Creator Hub card |---->| /api/agency/groups/main (NEW) || (LearnerGroupAnalytics) | | GET hierarchy · POST create || collapsible main -> sub |---->| PATCH/DELETE /main/[id] (NEW) || Group modals (5) |---->| /api/agency/groups/** (v1 flat) || ShareGroupModal | | [id] · /members/** · /quests/** · /meta |+---------------------------+ | /[id]/duplicate | Clerk JWT session ------------>| /[id]/permissions · /subgroups/[id]/perm | Share subgroups (NEW) ----->| /[id]/subgroups-access (NEW, main id) | | authenticateAgencyMember (read) | | authenticateAgencyManager (write) | | groupPermissionAccess.ts (main) | | subGroupPermissionAccess.ts (sub) | | creatorCohortService.ts (NEW, v3.1) | | cohortConsolidation.ts (NEW, v3.1) | | mainGroupNameResolver.ts (unique) | | Zod: create/updateMain, createSub | +---------------------+----------------------+ v +--------------------------------------------+ | SUPABASE | | main_learner_groups (NEW) | | learner_groups + parent_group_id (NEW) | | learner_group_permissions (NEW) | | learner_group_subgroup_permissions (NEW) | | group_memberships / learner_group_quests | | get_learner_group_hierarchy RPC (NEW) | +--------------------------------------------+
All data access uses the service-role client (RLS bypassed); authorization lives in
the route layer, and every query additionally pins `.eq("agency_id", agencyId)`.
The hierarchy routes are thin: `GET /main` delegates the whole tree to one RPC;
writes are single-row statements behind the same agency pin. Permission resolution
is layered on top and split by level — `groupPermissionAccess.ts` for main groups
(PR #833), `subGroupPermissionAccess.ts` for sub groups (PR #836).
Technologies Used
Concern | Technology |
|---|---|
Framework | Next.js (App Router), TypeScript |
Auth | Clerk ( |
Database | Supabase Postgres + PostgREST, |
Validation | Zod ( |
Forms | react-hook-form + zodResolver (modals) |
Toasts | sonner |
UI | Existing shadcn-style components ( |
Detailed Design & Implementation
Data Model / Schema
v1:
20260806_create_learner_groups.sql,20260806_learner_group_stats_rpc.sqlv2 (#772):
20260816_01_create_main_learner_groups.sql,20260816_02_add_sub_group_hierarchy.sql,20260816_03_learner_group_hierarchy_rpc.sqlv2.1 (#833):
20260915_create_learner_group_permissions.sql,20260916_add_granted_by_to_learner_group_permissions.sqlv3.0 (#836):
20260919_create_learner_group_subgroup_permissions.sql,20260920_remove_inert_subgroup_permissions.sqlv3.1 (#869):
20260921_mark_default_cohorts.sql
agencies 1 ──* main_learner_groups ──1 app_users (creator_id)main_learner_groups 1 ──* learner_groups (parent_group_id) <- sub groupslearner_groups 1 ──* group_memberships ──1 app_users (learner_id)learner_groups 1 ──* learner_group_quests ──1 quests main_learner_groups id UUID PK · agency_id FK · creator_id FK name TEXT(1-100) · description TEXT(<=500) · status ACTIVE|ARCHIVED archived_at · created_at · updated_at · UNIQUE (agency_id, id) is_default_cohort BOOLEAN NOT NULL DEFAULT false (v3.1) learner_groups (+ v2) parent_group_id UUID NULL 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 (v3.1) Sharing grants (v2.1 / v3.0) — one table per level, same shape: learner_group_permissions group_id → main_learner_groups(id) learner_group_subgroup_permissions subgroup_id → learner_groups(id) both: user_id · permission_level 'view'|'edit' · granted_by · UNIQUE (x, user_id)
UNIQUE (agency_id, id)exists solely solearner_groupscan carry a composite FK. That makes same-agency containment a database invariant rather than a route check — a sub group cannot attach to another agency's main group even if a future path forgets to pinagency_id.ON DELETE CASCADEonagency_idremoves a main group with its subtree when the agency is deleted.All statements are idempotent
IF NOT EXISTS/DROP … IF EXISTS/ drop-then-create triggers), so each file is re-runnable.
Indexes
Index | Purpose |
|---|---|
| Agency scoping, archive filters, name-prefix search |
| Finds subgroups of a main group |
| Enforces unique |
| Ensures one ACTIVE default per |
| Supports any-status default lookups using the same keys without the status filter |
The unique index is partial on purpose: archived names stay restorable, and legacy
flat groups keep their previous naming behaviour.
Row-Level Security (RLS)
RLS. main_learner_groups mirrors learner_groups — SELECT via is_agency_member, INSERT/UPDATE/DELETE via is_agency_manager (helpers from the v1 migration). GRANT SELECT to authenticated, full DML to service_role, matching reality: browsers never read this table directly.
Archive Cascade
Archive cascade (trigger, not app code). sync_sub_group_status_on_main_group_change() fires AFTER UPDATE OF status and copies status + archived_at onto every direct child that differs, so the subtree always follows its parent and the archive button cannot produce an "ACTIVE child under ARCHIVED parent".
Learner Group Hierarchy
get_learner_group_hierarchy(check_agency_id uuid). SECURITY DEFINER STABLE, granted to authenticated + service_role. One call returns per main group (ordered created_at DESC): fields, sub_group_count, active_sub_group_count, total_member_count, total_quest_count, and a subgroups JSONB array (ordered by name) with manager details and per-group member_count / quest_count.
SECURITY DEFINER is required so child tables are readable for any agency member; the guard auth.role() = 'service_role' OR is_agency_member(check_agency_id) runs at request level (always the real caller), so a direct PostgREST call with an arbitrary agency_id cannot leak another agency's tree. The service_role branch mirrors get_learner_group_stats — API routes already enforce scoping.
Group Sharing — Permissions (PR #833, Main Groups)
Migrations. 20260915_create_learner_group_permissions.sql, 20260916_add_granted_by_to_learner_group_permissions.sql.
learner_group_permissionsid UUID PKgroup_id UUID FK → main_learner_groups(id) ON DELETE CASCADEuser_id UUID FK → app_users(id) -- internal app_users.idpermission_level TEXT CHECK IN ('view','edit') DEFAULT 'view'granted_by UUID FK → app_users(id) (nullable, added 20260916)created_at updated_atUNIQUE (group_id, user_id) -- one grant per user per group
Group Sharing — Permissions (PR #833, Main Groups)
ON DELETE CASCADEmeans deleting a main group (or the app user) removes its grants.granted_byrecords the acting agency member per the user-attribution rule. Pre-existing rows stayNULLrather than fabricating a grantor; the API always writes it.Indexes on
group_id,user_id,granted_by.RLS:
service_rolegetsFOR ALL USING(true);authenticatedgets SELECT on own rows only (via anEXISTSjoin toapp_users.clerk_id = auth.uid()). Enforcement is otherwise at the API layer.
Role Resolution
resolveMainGroupRole(groupId, context) is the single source of truth:
Rank | Role | Source |
|---|---|---|
1 |
|
|
2 |
| Agency OWNER ( |
3 |
| Explicit |
4 |
| Explicit |
5 |
| Everything else — membership alone grants nothing |
Each step short-circuits, so precedence is structural rather than a priority sort.
Derived helpers: canEditMainGroup (owner ∨ manager ∨ edit), canManageMainGroupPermissions (owner ∨ manager — gates share and delete), isAgencyOwnerCheck, isAgencyAdminMember, fetchAgencyCreatorsForGroup, resolveMainGroupRolesBulk.
Agency-Scoped Auth Context
scopeContextToAgency(context, agencyId) fixes a real multi-agency bug: getAgencyAuthContext() prefers the ACTIVE agency_members row while authenticateAgencyManager()/authenticateAgencyMember() prefer the owned agency. For a user who owns agency A and is an ACTIVE member of agency B, the two disagree, so a route validating a group against A would resolve the role against B, get an agency_id mismatch, return null, and deny the owner access to their own group.
Pinning the context makes every ownership/manager check run against the agency that actually scoped the group. It only relocates the agency — isAgencyMember and agencyMemberRole are reset to conservative values so a stale true cannot leak, and the helpers re-query membership.
Bulk Role Resolution
resolveMainGroupRolesBulk returns {canEdit, canManage, canView} per group in two queries (static owner/admin check + one .in() over the caller's grant rows), rather than per-group resolution.
canManage = owner ∨ managercanEdit = canManage ∨ editcanView = canEdit ∨ view
Route Gates Added by #833
Route | Gate |
|---|---|
| Visibility filtered by |
|
|
|
|
|
|
| Any resolved role (owner, manager, or a view/edit grantee); 403 otherwise |
|
|
Permissions API
/api/agency/groups/[id]/permissions, where id = main group id.
All handlers load the group first and pin the auth context to group.agency_id.
Method | Body | Result |
|---|---|---|
| — |
|
|
| 201; 409 if a row already exists ("use PATCH to change it") |
|
| Updates the level |
|
| Revokes; 404 if no row |
Validation via lib/schemas/learner-group-permissions.schema.ts (addLgpPermissionSchema, updateLgpPermissionSchema, deleteLgpPermissionSchema).
Grants are addressed by internal app_users.id, not email. Every mutation rejects self-targeting ("You cannot grant yourself a permission"), verifies the target is a same-agency creator via isAgencyGroupCreator, and POST checks for an existing row before inserting. requireGroup throws a 404 response object rather than leaking a cross-agency existence signal.
Visibility Filter and Fail-Closed Behavior
GET /main resolves flags before filtering.
Agency OWNER/ADMIN see the full hierarchy.
A CREATOR sees only groups they own or were shared with (
groupFlags.get(mg.id)?.canView ?? false).A throw — or a silent empty result from the creator-row query, which is why
{ error }is checked explicitly — leaves flags UNRESOLVED, never "this creator can see nothing".For a CREATOR, this produces a retryable 500 ("Unable to resolve group access right now"), because failing open would leak every agency group and an empty flag map would silently blank the dashboard.
canManageGroupFallback(OWNER/ADMIN only) is applied only when flags are missing, so a CREATOR is never shown affordances the server would reject.
Share Modal
components/creator/groups/ShareGroupModal.tsx
Search agency creators by name or email and click to add.
Defaults to Viewer;
Enteradds the first result.Granted list caps at
MAX_VISIBLE_CREATORS = 6rows, then scrolls.Each row has a Viewer / Editor / Remove access picker.
Owner/manager rows render locked — they cannot be downgraded or removed, matching server precedence.
Entry points in
LearnerGroupAnalytics.tsxandanalytics/GroupTable.tsxare hidden unless the group'scanManageflag is set.Edit / Archive / Delete are
disabled={!canEdit}/!canManage.
Byproduct Fixes
The Share modal initially used a DropdownMenu nested inside the platform-modal FocusScope, which silently failed to open. It was replaced with the Radix Select popover already proven in this app's modals.
scopeContextToAgency was the other byproduct and was applied to the main GET/PATCH/DELETE, subgroup create, and permissions routes.
Sub-Group Sharing — Permissions (PR #836)
Subgroups are now private until explicitly shared. The gate is independent: parent-group access never implies subgroup access, and neither table is read while resolving the other.
Migrations: 20260919_create_learner_group_subgroup_permissions.sql (table + indexes + update_lgsp_updated_at trigger + RLS), 20260920_remove_inert_subgroup_permissions.sql.
learner_group_subgroup_permissionsid UUID PKsubgroup_id UUID FK → learner_groups(id) ON DELETE CASCADEuser_id UUID FK → app_users(id)permission_level TEXT CHECK IN ('view','edit') DEFAULT 'view'granted_by UUID FK → app_users(id)created_at updated_atUNIQUE (subgroup_id, user_id)
Sub-Group Sharing — Permissions (PR #836)
RLS. Same posture as the main-group grants table: service_role gets FOR ALL, while authenticated gets SELECT on own rows only. Enforcement is handled at the API layer. There is no manager/owner value — those roles are implicit and never stored.
Role Resolution
resolveSubGroupRole(subgroupId, context) uses the same precedence as resolveMainGroupRole, reading against learner_groups instead of main_learner_groups:
Rank | Role | Source |
|---|---|---|
1 |
| Subgroup |
2 |
| Agency OWNER or ACTIVE |
3 |
| Explicit |
4 |
| Explicit |
5 |
| Everything else — membership alone grants nothing |
Derived predicates:
canEditSubGroupRole— owner ∨ manager ∨ editcanManageSubGroupPermissionsRole— owner ∨ managerresolveSubGroupRoleForAgency(subgroupId, agencyId)— combinesgetAgencyAuthContext()+scopeContextToAgencyfor flat routes that only authenticated the agency.
Edit and manage are distinct. An edit grantee may rename, archive, and mutate members, but only owner/manager may delete or share. The UI must not surface Delete to an editor.
Route Gates
Every flat subgroup route now resolves the subgroup role instead of trusting authenticateAgencyManager() alone. The two list endpoints also filter unshared subgroups.
Operation | Required Access |
|---|---|
Read / mutate | Any resolved role |
General mutation |
|
Delete / share |
|
New subgroup access routes | Parent's |
Full method-by-method gates are defined in API Specification.
Sub-Group Permissions API
/api/agency/groups/subgroups/[id]/permissions, where id = subgroup id.
The route mirrors the main-group permissions handler:
POSTrejects self-targeting.Verifies
isAgencyGroupCreator.Returns 409 when a permission row already exists.
Rejects inert grants where the target is the subgroup owner or an agency OWNER/ADMIN with 400:
"This user already manages the subgroup and does not need an explicit grant".Legacy inert rows are deleted when encountered.
PATCH/DELETEreturn 404 when no row exists.GETlists same-agency creators with their effective role for any caller holding a role.
Batch Sub-Group Access
/api/agency/groups/[id]/subgroups-access, where id = main group id.
Manages one creator's access to every subgroup of that parent in one call.
Method | Input | Result |
|---|---|---|
|
|
|
|
| Upserts grants and deletes revokes in two statements |
GET behavior:
Includes
role: nullrows that the caller may toggle on.locked: truemarks implicit owner/manager roles.Returns 400 if
user_idis missing or is not a same-agency creator.Returns 404 if the user does not exist.
POST behavior:
Maximum of 100 entries.
Uses
applySubGroupAccessForUser.Actions for locked or foreign/other-parent subgroups are silently dropped server-side, preventing stale UI state from escalating access.
Self-targeting returns 400:
"You cannot change your own subgroup access".
Target-User Access Resolution
listSubGroupAccessForUser / applySubGroupAccessForUser are keyed on the target user, not the caller.
They resolve the target's agency-manager status and ownership to determine which rows are locked, then compute the resulting role as:
agency-manager → owner → explicit grant
Sub-Group Visibility
resolveListableSubGroupIds(agencyId, role, context) returns:
null(= no filter) for agency OWNER/ADMIN.Otherwise, the union of subgroups the caller owns plus subgroups explicitly granted to the caller, re-scoped to the caller's agency.
The result is passed to listGroups() as allowedGroupIds and also filters the all=1 branch.
Therefore, GET /api/agency/groups no longer leaks unshared subgroups. The shipped implementation (53df1047) filters both branches, despite the PR description stating that ?all=1 listing was deferred to avoid breaking delete-reassign pickers.
Side-Effect-Free Access Helpers
No reads mutate data. Unlike the main-group fetchAgencyCreatorsForGroup, these helpers are side-effect-free.
Inert rows are removed by the one-off 20260920 migration and new inert grants are rejected at write time through isInertSubGroupGrant.
UI
ShareGroupModal gained a Share subgroups section:
Per-creator checkbox rows are driven by
accessDraft.lockedrows render checked + disabled.A row counts as granted only when it has an explicit
view/editgrant:!locked && (role === 'edit' || role === 'view').Save posts the diff to
/subgroups-access.The subgroup row menu gained a Share entry.
types/learner-group.tsgained per-subgroupcanView/canEdit/canManage.These flags are used by
/creator/learner-groups/[id]/page.tsxandGroupMembersTable.canManagewas renamed tocanEditin the relevant UI distinction.
Default Cohorts — Identity & Consolidation (PR #869)
Problem. The auto-provisioned creator cohort was previously identified by name, so a TOCTOU race could create both "{creator}" and "{creator} (1)" as separate default families. #869 replaces name-based identity with a signature flag plus database-level uniqueness.
Migration
supabase/migrations/20260921_mark_default_cohorts.sql
The migration runs in three steps.
1. Signature Columns
Adds is_default_cohort BOOLEAN NOT NULL DEFAULT false to both main_learner_groups and learner_groups.
Column comments explicitly state that the flag is the identity marker.
2. Consolidation
A single idempotent DO block consolidates existing default-shaped cohorts.
Candidates: unmarked parent groups belonging to the same (agency_id, creator_id) whose names match a default shape. The derived creator name uses:
app_users.name → email local-part → "Team"
with a maximum length of 100.
Ranking:
"{creator} (default)" → exact "{creator}" → " (N)" leftover → ACTIVE → created_at → id
A family that already has a marked default is skipped, preventing reruns from violating the new unique indexes.
Canonical parent:
Forced ACTIVE.
Marked with
is_default_cohort = true.Renamed to
"{creator} (default)".
Canonical subgroup:
Adopts the best matching ACTIVE unmarked subgroup, with the exact convention name preferred.
Inserts one if no suitable subgroup exists.
Marks and renames it as the default subgroup.
Losers: every other default-shaped parent for that creator has its live data moved into the canonical subgroup before the parent is hard-deleted.
This is required because the foreign keys use ON DELETE CASCADE; deleting first would otherwise remove grants and analytics history.
Data | Consolidation Behavior |
|---|---|
Memberships / quest assignments | Moved with |
| Non-ACTIVE rows always move; ACTIVE rows move only when the canonical subgroup has no ACTIVE enrollment for that quest |
Both permission tables | Merged with |
| Follows the grant that established the winning permission level |
| Moved with |
This ensures consolidation cannot silently downgrade permissions.
3. Default Cohort Indexes
Index | Purpose |
|---|---|
| Unique |
| Unique |
| Non-unique partial indexes on the same keys without the status filter for any-status service lookups |
The two unique partial indexes provide the anti-race guarantee.
Stage A — Creator Cohort Provisioning
ensureCreatorCohort(agencyId, creatorId) in lib/services/creatorCohortService.ts resolves the cohort by marker:
Find the marked row and reactivate it if archived.
Otherwise, find the best pre-marker default-shaped ACTIVE row using the same migration ranking, then adopt, mark, and rename it.
Otherwise, insert a fresh marked cohort.
On a lost race (
23505), re-select the winner and adopt it.
The subgroup follows the same three steps, with the oldest matching ACTIVE subgroup adopted first.
Naming helpers:
deriveCreatorNameisDefaultShapedefaultParentGroupNamedefaultSubgroupName
defaultSubgroupName derives the subgroup name from the parent's base name, removing a trailing " (default)", so a renamed parent remains synchronized.
Wrappers:
ensureGroupCreatorCohort— gated byisAgencyGroupCreator; never throws, logs and returnsnull.Called from
POST /api/agency/team/claimas fire-and-forget.Called from
POST /api/admin/agencies/[id]/membersawaited for CREATOR/ADMIN on add and reactivation.syncDefaultSubgroupName— best-effort rename of the marked ACTIVE subgroup after a default parent rename; no-op otherwise and never throws.Called from
PATCH /api/agency/groups/main/[id].
Stage B — Learner Cohort Provisioning
provisionLearnerCohort({ learnerId, agencyId, questId, creatorId }) runs after each enrollment and is idempotent.
Flow:
Verify the creator is an agency group creator.
Verify the learner exists.
Skip if the learner already belongs to another agency.
Run
ensureCreatorCohort.Adopt the learner into the agency only when
agency_idisnullor already matches.Upsert membership.
Upsert
learner_group_quests.Upsert/reactivate
group_enrollments.Run
autoEnrollNewGroupMemberfor the subgroup's other ACTIVE group quests.
An archived enrollment is reactivated while keeping its original enrolled_at, which analytics uses as the cohort-enrollment date.
Safe wrappers:
provisionLearnerCohortSafe / provisionLearnerCohortForQuestSafe never throw, ensuring cohort-sync failures cannot fail the enrollment itself.
Endpoint | Provisioning Behavior |
|---|---|
| Fire-and-forget |
| Fire-and-forget |
| Awaited through a worker pool with |
The worker pool prevents large CSV imports from creating unbounded database concurrency or leaving provisioning chains orphaned by serverless teardown.
Cohort Consolidation Service
lib/services/cohortConsolidation.ts runs the same consolidation algorithm through the service-role API without RLS or DDL.
consolidateDefaultCohorts({ supabase, dryRun, log }) returns:
families, losersDeleted, membershipsMoved, questsMoved, enrollmentsMoved, enrollmentsDropped, permissionsMoved, snapshotsMoved
All tables are paged using BATCH = 500.
Parity behavior:
Skips families that already have a marked default.
Ranking ignores the marker, preventing the script from accidentally deleting the marked canonical parent.
Accepts both
PGRST204and PostgreSQL42703for missing-column errors.Falls back to the pre-marker fetch when
20260921has not yet landed.
Scripts
Script | Purpose |
|---|---|
| Preview by default; |
| Three passes: consolidate → guarantee a cohort for every agency group creator, including creators with zero enrollments → replay |
API Specification
Routes live under app/api/agency/groups/; responses use the ApiResponseHelper envelope { success, data?, error? }.
Hierarchy Routes (New in #772)
Method & Path | Auth | Purpose | Body / Query |
|---|---|---|---|
| Member | Hierarchy: main groups + nested subgroups + counts; returns |
|
| Manager | Create main group |
|
| Manager | Edit name/description, or archive/restore via | Partial |
| Manager | Hard delete; subgroups cascade | — |
| Manager | Create subgroup |
|
Permission Routes (New in #833)
See Group Sharing — Permissions for role semantics.
Method & Path | Auth | Purpose | Body |
|---|---|---|---|
| Any resolved role | List same-agency creators + effective roles | — → 403 with no role |
| Owner ∨ Manager | Grant |
|
| Owner ∨ Manager | Change level |
|
| Owner ∨ Manager | Revoke grant |
|
Sub-Group Permission Routes (New in #836)
Same shapes, with the opposite ID convention.
Method & Path | Auth | Purpose | Body |
|---|---|---|---|
| Any subgroup role | List same-agency creators + roles | — → 403 with no role |
| Subgroup owner ∨ manager | Grant |
|
| Subgroup owner ∨ manager | Change / revoke |
|
| Parent owner ∨ manager | Every subgroup of main |
|
| Parent owner ∨ manager | Batch grant/revoke for one creator |
|
Schemas are defined in lib/schemas/learner-group-subgroup-permissions.schema.ts:
addLsgpPermissionSchemaupdateLsgpPermissionSchemadeleteLsgpPermissionSchemaapplySubGroupAccessSchema
Validation
Validation is defined in lib/schemas/learner-group.schema.ts using safeParse + validationError(..., issues) so field-level messages reach the client.
Schema | Validation |
|---|---|
|
|
| Same limits, all fields optional, |
| Same group fields + |
These replaced the v1 routes' inline length checks.
Flat Routes
A subgroup is a learner_groups row, so every v1 route remains available:
Route | Purpose |
|---|---|
| List / create groups |
| Group metadata |
| Read / update / delete group |
| Add member |
| Bulk CSV member import |
| Remove member |
| Assign quest |
| Remove quest |
| Duplicate subgroup |
Their response shapes are unchanged, but their authorization is not. As of #836, each route resolves the subgroup role with resolveSubGroupRoleForAgency and requires canEditSubGroupRole or canManageSubGroupPermissionsRole as appropriate. Both list endpoints filter unshared subgroups through allowedGroupIds.
Default Cohort Integration
PR #869 adds no new endpoints and makes no signature changes.
Cohort provisioning uses existing routes:
Existing Route | Cohort Behavior |
|---|---|
| Calls |
| Calls |
| Calls |
| Calls |
| Calls |
| Calls the safe cohort provisioning flow after |
All calls are best-effort and cannot change the endpoint's response.
Authentication
authenticateAgencyMember() is used for reads, while authenticateAgencyManager() is used for main/subgroup writes. Manager access means OWNER or ACTIVE ADMIN/CREATOR.
For subgroup routes, agency authentication is only the first gate. The resolved per-group role is the second gate. Both honor View-As through isRestrictedFromAgencyMember.
The API layer is the sole authorization gate.
Migration-Not-Applied Handling
Every new route catches:
42P01—undefined_table42883—undefined_function
and returns 400:
"Main groups are not yet available. Push the database migrations first."
This allows the feature code to be deployed before the corresponding SQL migrations are applied.
Logic & Workflows
Hierarchy Read
authenticateAgencyMember() → get_learner_group_hierarchy(agency_id).
If the RPC returns 42P01/42883, the route falls back to fetchHierarchyDirect(), which assembles the same structure from:
main_learner_groupslearner_groupsgroup_membershipslearner_group_questsapp_users
Other RPC errors are thrown.
The result is normalized through normalizeMainGroup / normalizeSubGroup, with:
sub_group_countdefaulting tosubgroups.lengthnull counts defaulting to
0
Search is performed in memory across both main-group and nested subgroup name/description. A match in either level keeps the parent row.
The response is then sliced and returned as:
{ groups, canManage, total, pagination }
total represents the filtered in-memory result size, not a database count.
Unique Names
resolveUniqueMainGroupName(agencyId, baseName, excludeGroupId?) in lib/learnerGroups/mainGroupNameResolver.ts is shared by both POST /main and PATCH /main/[id].
This ensures rename and create follow the same naming algorithm.
Excludes the group currently being renamed.
On collision, returns
"<name> (maxN+1)".Base-name regex is escaped.
Subgroups use the same algorithm scoped to one parent through resolveUniqueSubGroupName.
A 23505 from the unique index is caught and returned as a friendly validation error rather than a 500.
Neither resolver is atomic, and there is no database constraint on main_learner_groups.name.
Subgroup Creation Guards
Flow:
Manager authentication.
Zod validation.
Load parent with
.eq("id", parentGroupId).eq("agency_id", agency_id).Reject an ARCHIVED parent.
If
managerIdis provided, build the manager set fromagencies.owner_clerk_id+ ACTIVEagency_memberswith ADMIN/CREATOR roles.Reject a manager outside that set.
Insert the subgroup as ACTIVE using the caller's
app_users.id.
The parent query proves both existence and same-agency ownership.
Archive / Restore
Main group: PATCH /main/[id]; the database trigger cascades the status change.
Subgroup: PATCH /[id].
updateGroup() prevents restoring a subgroup while its parent remains ARCHIVED:
"Restore the main group first"
This prevents the UI from recreating an invalid ACTIVE child under ARCHIVED parent state. The list still returns archived rows so they can be restored.
Delete
Subgroup:
Validate target.
Copy memberships into the reassignment target.
Use chunked operations of 100.
Delete the source.
Perform compensating rollback on failure.
Main group: a single delete. ON DELETE CASCADE removes the entire subtree.
Type-Safe Delete Confirmation
DeleteGroupDialog uses:
deleteType?: DeleteGroupType
where:
DeleteGroupType = "DELETE"
The literal union is passed directly as the fetch method.
The destructive button is disabled until:
submitting || confirmText !== "DELETE"
confirmText is cleared on open, cancel, and success so confirmation cannot carry into another deletion.
The literal type also prevents accidentally sending a PATCH.
Dynamic Endpoint Routing by Group Type
A single GroupModal handles both levels through isMainGroup + parentGroupId.
isMainGroup controls:
Icon
Title
Description
Labels
Button text
Toast text
Manager field visibility
The Manager field is hidden for main groups because main_learner_groups has no manager_id column.
Action | Main Group | Subgroup |
|---|---|---|
Create / Edit |
|
|
Archive / Delete |
|
|
Duplicate | — |
|
For deletion, target and endpoint are stored as one pair. setDeletingGroup is immediately followed by setDeletingEndpointBase, with isParentGroup derived from the endpoint base.
This fixes the previous issue where a main-group ID could be sent to the flat [id] route and return 404.
Analytics View
LearnerGroupAnalytics.tsx
Each main group is displayed as a collapsible card containing:
Name
Archived badge
Description
N active teamsN membersRotating chevron
Expanding displays an N Sub Groups header with an inline add button for ACTIVE parents, followed by a scrollable subgroup list linking to /creator/learner-groups/{id}.
Subgroup row actions:
Edit
Duplicate — ACTIVE only
Archive / Restore
Delete
Share — subgroups only, v3.0
Archive/Restore is suppressed when both child and parent are ARCHIVED because the cascade already owns that subtree and restoring the child directly would be rejected.
Unshared subgroups are completely absent through the canView filter. Each row carries its own canEdit / canManage flags, so menu actions are controlled per group rather than per caller.
Freshness behavior remains unchanged:
No client cache
reloadMainGroups()after every mutationfetchSeqprotects against out-of-order responses250 ms debounce
Empty last page automatically steps back one page
Batching Invariant
Every .in() filter and bulk insert is chunked using:
LIST_BATCH = 100
Multi-batch writes use compensating rollbacks.
The cohort service instead uses:
COHORT_PROVISION_CONCURRENCY = 5
for per-learner provisioning.
Default-Cohort Provisioning (v3.1)
Three triggers call lib/services/creatorCohortService.ts:
Trigger | Service |
|---|---|
Becoming an agency group creator |
|
Team claim / admin member add or reactivation |
|
Learner enrollment into a quest |
|
Both paths are idempotent and non-throwing.
Agency Analytics therefore fills through ordinary enrollment rather than requiring a separate workflow.
The is_default_cohort flag — not the group name — determines the default cohort. This prevents renames from creating a second default and allows the partial unique indexes to serve as the concurrency backstop.
Infrastructure & Operations
Dependencies
Direction | Dependencies |
|---|---|
Upstream |
|
Downstream |
|
Migration Ordering
Required order:
20260816_01 → _02 → _03 → 20260915 → 20260916 → 20260919 → 20260920 → 20260921
The hierarchy migration must not run before the main-group table exists.
20260816_02 adds:
learner_groups_parent_group_fk
which references main_learner_groups. Therefore, the table must be created first, followed by the hierarchy migration and then the RPC.
The permission migrations are independent of the hierarchy ordering but depend on:
20260915→main_learner_groups20260919→learner_groups+app_users
20260920 is a one-off deletion of inert rows and must run after 20260919.
20260921 must run last because it:
Adds
is_default_cohort.Moves sharing grants and snapshots.
Adds the partial unique indexes.
Depends on the tables created by earlier migrations.
Operational Runbook for #869
Always perform a dry run first:
npx tsx scripts/consolidate-default-cohorts.tsnpx tsx scripts/consolidate-default-cohorts.ts --executenpx tsx scripts/backfill-learner-cohorts.ts --dry-runnpx tsx scripts/backfill-learner-cohorts.ts
Both scripts require:
NEXT_PUBLIC_SUPABASE_URLSUPABASE_SERVICE_ROLE_KEY
They bypass RLS and must never be executed against production without reviewing the dry-run diff.
The SQL migration repeats consolidation idempotently for fresh/reset environments.
Monitoring
Errors funnel through ApiResponseHelper.handleError, while the UI displays toast.error.
A 400 "Push the database migrations first" is not a bug. It indicates 42P01/42883, meaning the required SQL is missing from that environment.
There are no dedicated metrics. Monitor 500 responses under /api/agency/groups/** in the VPS logs.
Deployment
Apply migrations before deploying code. Although degradation is graceful, the feature remains unusable until the migrations are applied.
Standard deployment:
merge → deploy.yml → SSH to Hostinger VPS → git reset --hard → npm install → npm run build → PM2 reload → health check → automatic rollback
There are no feature flags.
Rollback is performed by redeploying the previous commit. The change is additive and backward compatible because parent_group_id remains nullable. Rolling code back after SQL is applied leaves the data intact; the main-group list simply becomes empty.
Testing & Quality Assurance
Manual QA Strategy
Manual QA uses docs/learnergroup_handover_docs.md §14 for flat flows plus the hierarchy path:
Create a main group.
Add two subgroups.
Confirm the parent shows
2 active teamsand rolled-up members.Archive the parent.
Confirm both children become Archived.
Attempt direct child restore — must be rejected.
Restore the parent.
Confirm the subtree returns.
Sharing QA
Creator B has no grant.
Confirm Creator B cannot see the group anywhere.
Confirm direct ID access returns 403.
Grant
view.Confirm read-only access everywhere.
Confirm Edit/Delete are hidden and writes return 403.
Upgrade to
edit.Confirm mutations work while Delete remains hidden.
Revoke access.
Confirm the group becomes private again.
Automated Tests
File | Cases | Focus |
|---|---|---|
| 111 | Role resolution for both levels, multi-agency |
| 5 | Per-route 403s for a caller with no subgroup role |
| 11 | Enrollment visibility based on resolved subgroup role |
| 26 | #869 — |
| 11 | #869 — ranking, data movement, max-privilege grant merge, dry-run, |
The last two suites are the only suites covering #869, totaling 37 cases. The PR description claims 45 tests across three service suites; the third suite is not part of the merge diff.
Playwright specs exist under tests/e2e/ for agency scoping and can be extended through TEST_*_EMAIL / _PASSWORD.
There is currently no UI-level test for the Share modal.
Known Limitations — Default Cohorts (PR #869)
Limitation | Details |
|---|---|
Provisioning invisible without |
|
Cross-agency learners are not provisioned |
|
Consolidation is destructive and one-shot | Loser parents are hard-deleted. Only explicitly listed data is moved; other custom rows and individual |
Name collisions on rename are swallowed |
|
Defaults are never unmarked or re-derived | Deleting the default parent allows the next |
Default pair is invisible to reviewers | The UI does not expose which group is the default, so deleting the |
Known Limitations — Sharing (PR #836 / #833)
Limitation | Details |
|---|---|
Main-group read path still writes |
|
Missing inert-row migration for main groups | The claimed |
Admin/owner rows remain in main-group grants | They remain inert under precedence and cannot be changed through the locked UI. |
Share list is capped at 6 | No pagination; users further down the roster must be searched. |
Grants are per-creator and non-expiring | There is no whole-agency share option or expiration. |
| Neither sharing modal exposes who granted access. |
Batch access silently drops unknown actions | Locked, other-parent, and cross-agency subgroup IDs are ignored rather than reported. |
| A creator cannot select an unshared subgroup as a |
UI tests are missing | No coverage for the Share modal or end-to-end |
Known Limitations — Hierarchy (PR #772)
Limitation | Details |
|---|---|
Subgroup duplication detaches the copy |
|
Main-group deletion has no reassignment | A cascade permanently removes the main group, subgroups, memberships, and quest assignments. |
Parent selector only loads the current page |
|
Flat endpoints remain hierarchy-unaware |
|
Name uniqueness is app-side only | No DB constraint on main-group names; resolver is case-sensitive, non-atomic, and reads all agency names. |
DB invariant is tenancy, not authorship | Composite FK prevents cross-agency subgroups; per-creator access remains route-enforced. |
Archive cascade is one level deep | Direct children are handled; a future third tier would require a new trigger design. |
Hierarchy RPC has no | Tables are schema-qualified, but other definer functions pin the search path. |
v1 limitations remain | No auto-enrollment, no CSV account provisioning, sequential dashboard reads, |
Maintenance & Support
Symptom | Likely Cause | Fix |
|---|---|---|
Group missing for a valid agency member | Groups are private until shared | Grant a role through Share, or confirm the user is agency OWNER/ADMIN |
500 | Fail-closed flag resolution for a CREATOR | Check |
403 | No owner/manager role or | Share with Editor or use the group owner/agency manager |
403 | Caller lacks | Use the group creator or agency OWNER/ADMIN |
403 on | Caller has no resolved role | Expected; permissions require at least view access |
409 existing creator permission | POST used when a row already exists | Use PATCH to change the level |
400 self-targeting | Caller attempted to grant themselves access | Select another creator |
400 target is not a creator | Target failed | Use a same-agency creator |
Owner/manager row is locked | Role is implicit | Expected; the row cannot be downgraded or removed |
Share dropdown fails to open |
| Use the Radix |
403 subgroup write despite parent access | Subgroup access is independent | Grant access directly to the subgroup |
400 migration error on |
| Apply |
404 on | Subgroup ID used against main-group route | Use |
Subgroup missing despite parent visibility | Independent subgroup gate | Use Share subgroups |
403 | No owner, manager, or explicit grant | Grant |
403 subgroup write for editor | Delete/share require manager-level access | Expected; editor access does not include management |
400 inert subgroup grant | Target already has implicit owner/manager role | Expected; explicit grant is unnecessary |
400 target not a creator on |
| Use a same-agency creator |
400 | Self-targeting | Select another creator |
400 migration error on sharing |
| Apply |
No cohort for new creator | Missing | Apply |
Duplicate default families after upgrade | Pre-marker leftovers were not default-shaped or consolidation was skipped | Preview |
| Multiple ACTIVE marked defaults | Unmark or archive the incorrect row |
Default subgroup name did not follow parent |
| Rename the conflicting subgroup, then rename the parent again |
Learner missing from creator cohort | Learner belongs to another agency or creator is not an agency group creator | Expected for cross-agency learners; otherwise run backfill |
Parent FK migration fails | Hierarchy migration ran before main-group table creation | Create the table first, then rerun migration 02 |
404 deleting main group | Main ID sent to flat route | Use |
Duplicated subgroup missing from card |
| Expected currently; set parent manually |
Cannot restore archived subgroup | Parent remains archived | Restore the main group |
Cannot create subgroup under archived parent | Parent is archived | Restore the parent |
Manager validation fails | Manager is not owner or ACTIVE ADMIN/CREATOR | Select a valid manager or clear the field |
Parent select is blank | Parent is not on loaded page | Paginate to it or use its row menu |
Wrong name suffix | Name resolver is non-atomic | Rename the conflicting group |
403 on mutation | Caller lacks required agency role or is restricted by View-As | Check role/status and exit View-As |
400 cannot edit archived group | Mutation targets archived group | Restore first |
500 on all group APIs | v1/hierarchy migrations missing | Apply |
Empty XP/badges | RPC auth guard rejected caller | Verify service-role branch of |
Quest missing from assignment list | Quest unpublished or creator outside agency quest set | Publish and verify creator access |
CSV | Email mismatch or non-learner account | Use an existing LEARNER email |
Stale list after rapid search | Out-of-order response | Verify the |
Changelog
Date | Version | Changes |
|---|---|---|
2026-08-14 | 1.0 | Flat learner groups complete; handoff + technical docs |
2026-08-19 | 2.0 | PR #772 merged ( |
2026-09-17 | 2.1 | PR #833 merged ( |
2026-09-18 | 3.0 | PR #836 merged ( |
2026-09-23 | 3.1 | PR #869 merged ( |
Document Version
Version | Description |
|---|---|
1.0 | Initial guide authored alongside the handoff doc on |
2.0 | Updated for PR #772 to document the Main Group → Sub Group hierarchy, 19/08/2026 |
2.1 | Updated for PR #833 to document private-by-default group sharing and the |
3.0 | Updated for PR #836 to document independent private-by-default subgroup sharing, the |
3.1 | Updated for PR #869 to document the one-default-cohort-per-creator identity model, consolidation migration/service/scripts, and enrollment-time provisioning, 23/09/2026 |
Companion document: docs/learnergroup_handover_docs.md — product/PRD view, manual QA checklist (§14), and post-v1 delivery log (§20).
Updated by: clydetims (Clyde Ador)