Learner Group Management

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_quests table 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 main_learner_groups row. Holds no members or quests.

Subgroup

A learner_groups row with a non-null parent_group_id. Holds members, manager, and quests.

Legacy flat group

A group with parent_group_id IS NULL. Remains fully supported but is excluded from the main-group list.

Default cohort

The automatically provisioned pair consisting of a "{creator} (default)" parent group and its "{creator} (default)" subgroup. Exactly one pair per (agency_id, creator_id).

Default shape

Any of {creator}, {creator} (N), {creator} (default), or {creator} (default) (N), case-insensitively. Represents legacy or name-collision rows.

is_default_cohort

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 creator_id or an agency OWNER/ADMIN. Has highest precedence, can always edit, and is the only role permitted to share.

Editor / Viewer

An explicit permission grant in learner_group_permissions (main group) or learner_group_subgroup_permissions (subgroup), with level edit or view.

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.

canManage

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.

canEdit

Boolean defined as canManage OR edit grant. Controls renaming, archiving, membership changes, quest assignment, and duplication.

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 agency_members row with role ADMIN/CREATOR.

Compensating rollback

Manual reversal of partial writes when a multi-step mutation fails.

LIST_BATCH

Batch size of 100 for all .in() filters to limit PostgREST URL length.


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 (@clerk/nextjs v7)

Database

Supabase Postgres + PostgREST, @supabase/supabase-js service-role client

Validation

Zod (lib/schemas/learner-group.schema.ts)

Forms

react-hook-form + zodResolver (modals)

Toasts

sonner

UI

Existing shadcn-style components (Button, Card, Dialog, Table, skeleton loading)


Detailed Design & Implementation

Data Model / Schema

  • v1: 20260806_create_learner_groups.sql, 20260806_learner_group_stats_rpc.sql

  • v2 (#772): 20260816_01_create_main_learner_groups.sql, 20260816_02_add_sub_group_hierarchy.sql, 20260816_03_learner_group_hierarchy_rpc.sql

  • v2.1 (#833): 20260915_create_learner_group_permissions.sql, 20260916_add_granted_by_to_learner_group_permissions.sql

  • v3.0 (#836): 20260919_create_learner_group_subgroup_permissions.sql, 20260920_remove_inert_subgroup_permissions.sql

  • v3.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 groups
learner_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 so learner_groups can 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 pin agency_id.

  • ON DELETE CASCADE on agency_id removes 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

idx_main_learner_groups_agency_id / _status / _name (text_pattern_ops)

Agency scoping, archive filters, name-prefix search

idx_learner_groups_parent_group_id (partial, parent NOT NULL)

Finds subgroups of a main group

uq_learner_groups_active_subgroup_name (unique, partial)

Enforces unique (agency_id, parent_group_id, lower(name)) where parent_group_id IS NOT NULL and status = ACTIVE

uq_main_learner_groups_default / uq_learner_groups_default (unique, partial, v3.1)

Ensures one ACTIVE default per (agency_id, creator_id) / per parent — prevents duplicate default cohorts

idx_main_learner_groups_default / idx_learner_groups_default (partial, v3.1)

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_permissions
id UUID PK
group_id UUID FK → main_learner_groups(id) ON DELETE CASCADE
user_id UUID FK → app_users(id) -- internal app_users.id
permission_level TEXT CHECK IN ('view','edit') DEFAULT 'view'
granted_by UUID FK → app_users(id) (nullable, added 20260916)
created_at updated_at
UNIQUE (group_id, user_id) -- one grant per user per group

Group Sharing — Permissions (PR #833, Main Groups)

  • ON DELETE CASCADE means deleting a main group (or the app user) removes its grants.

  • granted_by records the acting agency member per the user-attribution rule. Pre-existing rows stay NULL rather than fabricating a grantor; the API always writes it.

  • Indexes on group_id, user_id, granted_by.

  • RLS: service_role gets FOR ALL USING(true); authenticated gets SELECT on own rows only (via an EXISTS join to app_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

owner

main_learner_groups.creator_id === context.userInternalId

2

manager

Agency OWNER (agencies.owner_clerk_id) or ACTIVE agency_members.role = 'ADMIN'

3

edit

Explicit permission_level = 'edit' row

4

view

Explicit permission_level = 'view' row

5

null

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 ∨ manager

  • canEdit = canManage ∨ edit

  • canView = canEdit ∨ view

Route Gates Added by #833

Route

Gate

GET /api/agency/groups/main

Visibility filtered by canView; response decorated with canEdit/canManage

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

canEditMainGroup

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

canManageMainGroupPermissions

POST /api/agency/groups/subgroups

canEditMainGroup(parentGroupId) — adding a subgroup edits the parent

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

Any resolved role (owner, manager, or a view/edit grantee); 403 otherwise

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

canManageMainGroupPermissions only

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

GET

—

{ members[], myRole, canManage } — same-agency creators (owner + ACTIVE ADMIN/CREATOR) with their effective role, hasExplicitPermission, sorted owner → manager → edit → view

POST

{ user_id_permission, permission_level } (level defaults view)

201; 409 if a row already exists ("use PATCH to change it")

PATCH

{ user_id_permission, permission_level }

Updates the level

DELETE

{ user_id_permission }

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; Enter adds the first result.

  • Granted list caps at MAX_VISIBLE_CREATORS = 6 rows, 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.tsx and analytics/GroupTable.tsx are hidden unless the group's canManage flag 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_permissions
id UUID PK
subgroup_id UUID FK → learner_groups(id) ON DELETE CASCADE
user_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_at
UNIQUE (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

owner

Subgroup creator_id === context.userInternalId

2

manager

Agency OWNER or ACTIVE agency_members.role = 'ADMIN'

3

edit

Explicit permission_level = 'edit' row

4

view

Explicit permission_level = 'view' row

5

null

Everything else — membership alone grants nothing

Derived predicates:

  • canEditSubGroupRole — owner ∨ manager ∨ edit

  • canManageSubGroupPermissionsRole — owner ∨ manager

  • resolveSubGroupRoleForAgency(subgroupId, agencyId) — combines getAgencyAuthContext() + scopeContextToAgency for 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

canEditSubGroupRole

Delete / share

canManageSubGroupPermissionsRole

New subgroup access routes

Parent's canManageMainGroupPermissions

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:

  • POST rejects 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 / DELETE return 404 when no row exists.

  • GET lists 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

GET

?user_id=…

{ group_id, group_name, user_id, subgroups[{ id, name, role, locked }] } for every subgroup

POST

{ user_id_permission, subgroups[{ subgroup_id, permission_level, grant }] }

Upserts grants and deletes revokes in two statements

GET behavior:

  • Includes role: null rows that the caller may toggle on.

  • locked: true marks implicit owner/manager roles.

  • Returns 400 if user_id is 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.

  • locked rows render checked + disabled.

  • A row counts as granted only when it has an explicit view/edit grant: !locked && (role === 'edit' || role === 'view').

  • Save posts the diff to /subgroups-access.

  • The subgroup row menu gained a Share entry.

  • types/learner-group.ts gained per-subgroup canView / canEdit / canManage.

  • These flags are used by /creator/learner-groups/[id]/page.tsx and GroupMembersTable.

  • canManage was renamed to canEdit in 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 ON CONFLICT DO NOTHING; originals deleted

group_enrollments

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 DO UPDATE; higher level wins (edit > view)

granted_by

Follows the grant that established the winning permission level

group_completion_snapshots

Moved with DO NOTHING against the per-day unique constraint; canonical observation wins

This ensures consolidation cannot silently downgrade permissions.

3. Default Cohort Indexes

Index

Purpose

uq_main_learner_groups_default

Unique (agency_id, creator_id) where status = 'ACTIVE' AND is_default_cohort

uq_learner_groups_default

Unique (agency_id, parent_group_id) where status = 'ACTIVE' AND is_default_cohort AND parent_group_id IS NOT NULL

idx_main_learner_groups_default / idx_learner_groups_default

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:

  1. Find the marked row and reactivate it if archived.

  2. Otherwise, find the best pre-marker default-shaped ACTIVE row using the same migration ranking, then adopt, mark, and rename it.

  3. Otherwise, insert a fresh marked cohort.

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

  • deriveCreatorName

  • isDefaultShape

  • defaultParentGroupName

  • defaultSubgroupName

defaultSubgroupName derives the subgroup name from the parent's base name, removing a trailing " (default)", so a renamed parent remains synchronized.

Wrappers:

  • ensureGroupCreatorCohort — gated by isAgencyGroupCreator; never throws, logs and returns null.

  • Called from POST /api/agency/team/claim as fire-and-forget.

  • Called from POST /api/admin/agencies/[id]/members awaited 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:

  1. Verify the creator is an agency group creator.

  2. Verify the learner exists.

  3. Skip if the learner already belongs to another agency.

  4. Run ensureCreatorCohort.

  5. Adopt the learner into the agency only when agency_id is null or already matches.

  6. Upsert membership.

  7. Upsert learner_group_quests.

  8. Upsert/reactivate group_enrollments.

  9. Run autoEnrollNewGroupMember for 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

POST /api/creator/enrollment/create-enrollment

Fire-and-forget

POST /api/learner/enroll

Fire-and-forget

POST /api/creator/enrollment/bulk-create

Awaited through a worker pool with COHORT_PROVISION_CONCURRENCY = 5

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 PGRST204 and PostgreSQL 42703 for missing-column errors.

  • Falls back to the pre-marker fetch when 20260921 has not yet landed.

Scripts

Script

Purpose

scripts/consolidate-default-cohorts.ts

Preview by default; --execute performs the moves/deletes

scripts/backfill-learner-cohorts.ts

Three passes: consolidate → guarantee a cohort for every agency group creator, including creators with zero enrollments → replay provisionLearnerCohort for pre-existing quest_enrollments; supports --dry-run and --limit N

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

GET /main

Member

Hierarchy: main groups + nested subgroups + counts; returns canManage

page, limit (client sends 5, server default 100, max 100), q

POST /main

Manager

Create main group

{ name, description? } → 201

PATCH /main/[id]

Manager

Edit name/description, or archive/restore via status

Partial { name?, description?, status? }

DELETE /main/[id]

Manager

Hard delete; subgroups cascade

—

POST /subgroups

Manager

Create subgroup

{ name, description?, parentGroupId, managerId? } → 201

Permission Routes (New in #833)

See Group Sharing — Permissions for role semantics.

Method & Path

Auth

Purpose

Body

GET /[id]/permissions

Any resolved role

List same-agency creators + effective roles

— → 403 with no role

POST /[id]/permissions

Owner ∨ Manager

Grant view/edit

{ user_id_permission, permission_level } → 201, 409 on conflict

PATCH /[id]/permissions

Owner ∨ Manager

Change level

{ user_id_permission, permission_level }

DELETE /[id]/permissions

Owner ∨ Manager

Revoke grant

{ user_id_permission } → 404 if no row

Sub-Group Permission Routes (New in #836)

Same shapes, with the opposite ID convention.

Method & Path

Auth

Purpose

Body

GET /subgroups/[id]/permissions

Any subgroup role

List same-agency creators + roles

— → 403 with no role

POST /subgroups/[id]/permissions

Subgroup owner ∨ manager

Grant view/edit

{ user_id_permission, permission_level } → 201; 400 if inert, 409 on conflict

PATCH|DELETE /subgroups/[id]/permissions

Subgroup owner ∨ manager

Change / revoke

{ user_id_permission, permission_level? }

GET /[id]/subgroups-access

Parent owner ∨ manager

Every subgroup of main [id] + target's role + locked

?user_id= → 400 if not a creator

POST /[id]/subgroups-access

Parent owner ∨ manager

Batch grant/revoke for one creator

{ user_id_permission, subgroups[{ subgroup_id, permission_level, grant }] } (≤100)

Schemas are defined in lib/schemas/learner-group-subgroup-permissions.schema.ts:

  • addLsgpPermissionSchema

  • updateLsgpPermissionSchema

  • deleteLsgpPermissionSchema

  • applySubGroupAccessSchema

Validation

Validation is defined in lib/schemas/learner-group.schema.ts using safeParse + validationError(..., issues) so field-level messages reach the client.

Schema

Validation

createMainGroupSchema

name 1–100 characters, description ≤500 characters

updateMainGroupSchema

Same limits, all fields optional, status: ACTIVE|ARCHIVED

createSubGroupSchema

Same group fields + parentGroupId UUID + optional managerId UUID

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

GET|POST /

List / create groups

GET /meta

Group metadata

GET|PATCH|DELETE /[id]

Read / update / delete group

POST /[id]/members

Add member

POST /[id]/members/bulk

Bulk CSV member import

DELETE /members/[learnerId]

Remove member

POST /[id]/quests

Assign quest

DELETE /[id]/quests/[questId]

Remove quest

POST /[id]/duplicate

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

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

Calls syncDefaultSubgroupName when name is present

POST /api/agency/team/claim

Calls ensureGroupCreatorCohort

POST /api/admin/agencies/[id]/members

Calls ensureGroupCreatorCohort for new group creators

POST /api/creator/enrollment/create-enrollment

Calls provisionLearnerCohortSafe after syncEnrollmentCount

POST /api/learner/enroll

Calls provisionLearnerCohortForQuestSafe after syncEnrollmentCount

POST /api/creator/enrollment/bulk-create

Calls the safe cohort provisioning flow after syncEnrollmentCount

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_table

  • 42883 — 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_groups

  • learner_groups

  • group_memberships

  • learner_group_quests

  • app_users

Other RPC errors are thrown.

The result is normalized through normalizeMainGroup / normalizeSubGroup, with:

  • sub_group_count defaulting to subgroups.length

  • null 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:

  1. Manager authentication.

  2. Zod validation.

  3. Load parent with .eq("id", parentGroupId).eq("agency_id", agency_id).

  4. Reject an ARCHIVED parent.

  5. If managerId is provided, build the manager set from agencies.owner_clerk_id + ACTIVE agency_members with ADMIN/CREATOR roles.

  6. Reject a manager outside that set.

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

  1. Validate target.

  2. Copy memberships into the reassignment target.

  3. Use chunked operations of 100.

  4. Delete the source.

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

POST /main · PATCH /main/{id}

POST /subgroups · PATCH /{id}

Archive / Delete

PATCH /main/{id} · DELETE /main/{id}

PATCH /{id} · DELETE /{id}

Duplicate

—

POST /{id}/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 teams

  • N members

  • Rotating 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 mutation

  • fetchSeq protects against out-of-order responses

  • 250 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

ensureGroupCreatorCohort

Team claim / admin member add or reactivation

ensureGroupCreatorCohort

Learner enrollment into a quest

provisionLearnerCohort{,ForQuest}Safe

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

agencies, app_users, agency_members, quests, quest_enrollments, learner_global_stats, learner_achievements, is_agency_* RLS helpers, Clerk, service-role client

Downstream

feat/group-based-enrollment, which consumes canEdit / canView / canManage from both group levels

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_groups

  • 20260919 → 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.ts
npx tsx scripts/consolidate-default-cohorts.ts --execute
npx tsx scripts/backfill-learner-cohorts.ts --dry-run
npx tsx scripts/backfill-learner-cohorts.ts

Both scripts require:

  • NEXT_PUBLIC_SUPABASE_URL

  • SUPABASE_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:

  1. Create a main group.

  2. Add two subgroups.

  3. Confirm the parent shows 2 active teams and rolled-up members.

  4. Archive the parent.

  5. Confirm both children become Archived.

  6. Attempt direct child restore — must be rejected.

  7. Restore the parent.

  8. Confirm the subtree returns.

Sharing QA

  1. Creator B has no grant.

  2. Confirm Creator B cannot see the group anywhere.

  3. Confirm direct ID access returns 403.

  4. Grant view.

  5. Confirm read-only access everywhere.

  6. Confirm Edit/Delete are hidden and writes return 403.

  7. Upgrade to edit.

  8. Confirm mutations work while Delete remains hidden.

  9. Revoke access.

  10. Confirm the group becomes private again.

Automated Tests

File

Cases

Focus

tests/agency/groups/learner-groups.test.ts

111

Role resolution for both levels, multi-agency scopeContextToAgency, visibility rules, listSubGroupAccessForUser / applySubGroupAccessForUser, inert-grant rejection, fail-closed flag resolution

tests/agency/groups/subgroup-route-gates.test.ts

5

Per-route 403s for a caller with no subgroup role

tests/services/group-enrollment-service.test.ts

11

Enrollment visibility based on resolved subgroup role

tests/services/creator-cohort-service.test.ts

26

#869 — ensureCreatorCohort adoption/23505 retry, ensureGroupCreatorCohort gating, provisionLearnerCohort steps and cross-agency skip, syncDefaultSubgroupName, naming helpers

tests/services/cohort-consolidation.test.ts

11

#869 — ranking, data movement, max-privilege grant merge, dry-run, PGRST204/42703 fallback

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 20260921

is_default_cohort queries fail with 42703/PGRST204; ensureGroupCreatorCohort returns null and only console.error("[creatorCohortService] …") provides a trace.

Cross-agency learners are not provisioned

provisionLearnerCohort exits when app_users.agency_id belongs to another agency. This is intentional and product-confirmed.

Consolidation is destructive and one-shot

Loser parents are hard-deleted. Only explicitly listed data is moved; other custom rows and individual quest_enrollments are removed by cascade.

Name collisions on rename are swallowed

23505 from syncSubgroupName / syncDefaultSubgroupName is ignored, so the default subgroup can retain a stale display name while the marker remains correct.

Defaults are never unmarked or re-derived

Deleting the default parent allows the next ensureCreatorCohort call to create a fresh pair.

Default pair is invisible to reviewers

The UI does not expose which group is the default, so deleting the "{creator} (default)" pair can silently result in a new pair.


Known Limitations — Sharing (PR #836 / #833)

Limitation

Details

Main-group read path still writes

fetchAgencyCreatorsForGroup removes inert owner/manager rows during GET /[id]/permissions.

Missing inert-row migration for main groups

The claimed 20260917_remove_inert_learner_group_permissions.sql is not in the repository.

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.

granted_by is not shown

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.

?all=1 filtering affects reassignment

A creator cannot select an unshared subgroup as a reassignGroupId target.

UI tests are missing

No coverage for the Share modal or end-to-end /subgroups-access lock-drop behavior.


Known Limitations — Hierarchy (PR #772)

Limitation

Details

Subgroup duplication detaches the copy

duplicateGroup() does not write parent_group_id, so the copy becomes a legacy flat group.

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

PAGE_SIZE = 5; unloaded main groups cannot be selected from the parent selector.

Flat endpoints remain hierarchy-unaware

/groups, /meta, and ?all=1 do not reference main_learner_groups; subgroup rows remain flat.

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 SET search_path

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, added_by always null, and reassignment moves members but not quest assignments.


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 "Unable to resolve group access right now. Please try again."

Fail-closed flag resolution for a CREATOR

Check console.error("[GET /api/agency/groups/main] Failed to resolve per-group access flags"); retry after resolving the underlying query failure

403 "You do not have edit access to this group"

No owner/manager role or edit grant

Share with Editor or use the group owner/agency manager

403 "Only the group owner or an agency manager can manage sharing"

Caller lacks canManageMainGroupPermissions

Use the group creator or agency OWNER/ADMIN

403 on GET /permissions

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 isAgencyGroupCreator

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

DropdownMenu used inside modal FocusScope

Use the Radix Select popover

403 subgroup write despite parent access

Subgroup access is independent

Grant access directly to the subgroup

400 migration error on /main or /subgroups

42P01/42883

Apply 20260816_01/02/03 in order

404 on /[id]/permissions

Subgroup ID used against main-group route

Use /subgroups/[id]/permissions for subgroups

Subgroup missing despite parent visibility

Independent subgroup gate

Use Share subgroups

403 "You do not have access to this subgroup"

No owner, manager, or explicit grant

Grant view or edit

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 /subgroups-access

isAgencyGroupCreator failed

Use a same-agency creator

400 "You cannot change your own subgroup access"

Self-targeting

Select another creator

400 migration error on sharing

20260919 not applied

Apply 20260919, then 20260920

No cohort for new creator

Missing is_default_cohort or creator fails creator-role check

Apply 20260921 or run backfill-learner-cohorts.ts --dry-run

Duplicate default families after upgrade

Pre-marker leftovers were not default-shaped or consolidation was skipped

Preview consolidate-default-cohorts.ts

uq_main_learner_groups_default violation

Multiple ACTIVE marked defaults

Unmark or archive the incorrect row

Default subgroup name did not follow parent

23505 was swallowed

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 /groups/main/{id}

Duplicated subgroup missing from card

duplicateGroup() omits parent_group_id

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 20260806_*learner_group*.sql and 20260816_* trio

Empty XP/badges

RPC auth guard rejected caller

Verify service-role branch of get_learner_group_stats and grants

Quest missing from assignment list

Quest unpublished or creator outside agency quest set

Publish and verify creator access

CSV "No account found"

Email mismatch or non-learner account

Use an existing LEARNER email

Stale list after rapid search

Out-of-order response

Verify the fetchSeq guard


Changelog

Date

Version

Changes

2026-08-14

1.0

Flat learner groups complete; handoff + technical docs

2026-08-19

2.0

PR #772 merged (1d1d6744): two-tier hierarchy, main_learner_groups, parent_group_id with same-agency composite FK, archive-cascade trigger, get_learner_group_hierarchy RPC, main/sub API routes, Zod schemas, shared unique-name resolver, collapsible analytics, type-safe DELETE confirmation, and type-derived endpoint routing

2026-09-17

2.1

PR #833 merged (e7a1bcbf): learner_group_permissions + granted_by, permission schemas, role resolution (owner > manager > edit > view > none), private-by-default semantics, scopeContextToAgency, permission routes, canEdit/canManage/canView gates, Share modal, and role/visibility tests

2026-09-18

3.0

PR #836 merged (89191f24): subgroup permission table, inert-row cleanup, subGroupPermissionAccess.ts, subgroup permission schemas, subgroup permission CRUD, batch subgroup sharing, flat-route permission gates, per-subgroup flags, list filtering, Share subgroups dialog, and 127 automated tests

2026-09-23

3.1

PR #869 merged (9cba07a8): is_default_cohort signature columns, partial unique indexes, duplicate-cohort consolidation, creatorCohortService Stage A/B, cohortConsolidation, migration fallback handling, consolidation/backfill scripts, enrollment-time provisioning, and 37 automated tests


Document Version

Version

Description

1.0

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

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 learner_group_permissions access model, 17/09/2026

3.0

Updated for PR #836 to document independent private-by-default subgroup sharing, the learner_group_subgroup_permissions access model, and subgroup route gates, 18/09/2026

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)


Was this article helpful?