1. Front Matter
Title: Enrollment
Author: Joshua Uriel Tribiana — original author (2026-06-29)
Updated by: Patrick Babala (patpatty19) — author/co-author of update PRs #706, #718, #790, #869, #871, #875
Reviewers: Joylynne Esportuno (teruterubozuuu) — original review. The update review approvers could not be resolved in this pass (GitHub tooling unavailable in this session).
Creation Date: 2026-06-29
Updated: 2026-10-01
Status: Published (feature live)
References:
Issue:
[6.2] Enrollment(feature ticket). The GitHub issue number could not be confirmed without GitHub access and is intentionally not fabricated.Security hardening: issue #860 — Security audit L5 (enrollment-count trigger + RLS hardening), delivered by PR #875 (confirmed in
supabase/migrations/20260925_harden_core_table_rls_followups.sql:4).Related feature:
[11.5]group-based enrollment — a separate feature/guide; this document only cross-references it where enrollment interacts with cohorts.Related guide: Hand-Off Documentation — Enrollment (product-facing flows, rules, information architecture, and QA checklist). This guide holds engineering detail and cross-references the Hand-Off instead of duplicating it.
Update PRs covered by this revision: #706 add-learners enhancement; #718 bulk-enrollment fixes (random temporary passwords,
enrollment_limit, count syncing, progress dialog); #790 file-upload / enrollment-progress fixes touchingcreate-enrollment, the enrollment page, andupdate-progress; #869 default cohorts provisioned on enrollment; #871 H3 bulk-CSV caps + recovery link instead of password reset; #875 statement-level enrollment-count triggers +quest_enrollmentsRLS hardening.
2. Introduction & Goals
Problem Summary
Creators need a quick and efficient system to manage learner access to their quests. The Enrollment feature provides the tools for creators to add learners individually or in bulk via CSV upload, track status and progress, and manage access. It is a core function for distributing content within the WyzQuests platform. (2.0) The feature now also auto-provisions learner accounts for B2B enrollment, supports authenticated learner self-enrollment into published quests, and hooks new enrollments into the creator's default cohort.Goals
Provide a UI for creators to enroll a single learner by email (the learner's display name is derived from the email; a debounced autocomplete searches existing agency learners).
Provide a UI for creators to bulk-enroll learners by uploading a CSV file.
Send an email notification to newly enrolled learners (a quest-link email for existing learners, an account + set-password email for auto-provisioned learners).
Persist enrollment records, linking learners to quests.
Allow creators to view all enrolled learners for a quest, including progress.
Allow creators to update an enrollee's status (
ongoingvs.finished).Allow creators to delete an enrollment (revoke access).
(Added in 2.0 — PRs #706 / #718 / #871.) Auto-provision (or recover) a learner account during enrollment when the email does not yet exist.
(Added in 2.0 — PR #869.) Provision each newly enrolled learner into the quest creator's default cohort.
(Added in 2.0.) Let authenticated learners self-enroll into published quests.
Non-Goals
Self-enrollment via a public link.Resolved (2.0): authenticated learner self-enrollment now exists (POST /api/learner/enroll, PR covered by this revision). Anonymous public-link enrollment (no sign-in) is still not supported.Payment processing or paid course enrollment. (Still open.)
Automatic enrollment based on learner groups (this is a separate feature,[11.5]).Resolved / re-scoped (2.0): group-based enrollment shipped as Feature[11.5](separate guide); the enrollment flow now provisions a learner into the creator's default cohort on enrollment (PR #869). Detailed cohort/group enrollment remains out of scope for this document.Detailed error reporting for individual failed rows in a CSV upload (noted as a scope gap).Resolved (2.0):bulk-createnow returns a per-rowfailedEnrollments[]array with a machine-readablereason(e.g."Already enrolled","User is not a learner") alongsidesuccessfulEnrollments(app/api/creator/enrollment/bulk-create/route.ts:528-531).
Glossary
Enrollment: A record in the
quest_enrollmentstable linking a learner (learner_id) to a specific quest (quest_id).Enrollee: A learner who has been enrolled in a quest.
Auto-provisioned learner: A learner whose account is created during enrollment because no
app_usersrow existed for the submitted email.Recovery link: A one-time Supabase Auth recovery link sent to an auto-provisioned (or recovered) learner so they set their own password; replaces the removed practice of resetting an existing user's password.
Progress: The
progressJSONB blob onquest_enrollments(percentage,visited_cards,last_visited_at,completed_nodes).enrollment_count: A denormalized counter onquests, maintained by database triggers, compared againstenrollment_limitbefore enrollment.Default cohort: The agency-group structure a creator's learners are auto-provisioned into on enrollment (Feature
[11.5], PR #869).
3. High-Level Architecture
System Diagram
Technologies Used
Next.js — App Router API route handlers and frontend components.
React / TypeScript — UI components (the enrollment page,
BulkAddLearnersModal,EnrolleeTable, group-enrollment modals).Supabase (PostgreSQL) — Stores
quest_enrollmentsand related data.Supabase Auth — Authenticates creators and learners.
Zod — Validates request bodies and query params.
Papaparse — Server-side CSV parsing for bulk enrollment.
React Email + Nodemailer — Renders and sends enrollment notification emails.
Sonner — Toast notifications.
TailwindCSS — Styling.
Corrected in 2.0: the original guide listed Clerk for creator authentication. The platform migrated to Supabase Auth; enrollment routes resolve the caller through Supabase auth helpers —
getCreatorAuthContext()/verifyAgencyResourceOwnership()(lib/auth/agencyOwnership.ts) for creators andauthenticateUser()(lib/auth/authenticate.ts) for learners. The legacy-namedapp_users.clerk_idcolumn now stores the Supabaseauth.users.idas text, so.eq("clerk_id", uid)lookups continue to work unchanged (app/api/creator/enrollment/create-enrollment/route.ts:107). No enrollment route imports Clerk.
4. Detailed Design & Implementation
Data Model / Schema
The feature primarily interacts with thequest_enrollmentstable.
Caveat (2.0): the baseCREATE TABLEforquest_enrollmentsis not present in version control (supabase/migrations/). The columns below are reconstructed from how the current code reads/writes the table and from the RLS/counter migrations; treat the DDL as the source of truth once recovered.quest_enrollmentstable:Column
Type
Description
idUUIDPrimary key.
quest_idUUIDForeign key to
quests.id.learner_idUUIDForeign key to
app_users.id(internal user UUID). Enables theapp_users!innerembeds inlist-enrollments/update-enrollment.statusTEXTEnrollment status. Creator-created rows use
ongoing; learner self-enroll usesactive; the creatorEnrolleetype modelsongoing | finished(types/creator.d.ts:248-261).progressJSONBProgress blob:
percentage,visited_cards,last_visited_at,completed_nodes.enrolled_atTIMESTAMPTZWhen the enrollment was created (the list endpoint orders by this).
Corrected in 2.0: the original table documented
learner_idasTEXTholding the Clerk ID, and listedcreated_at/updated_at. The current code never reads or writescreated_at/updated_at— it usesenrolled_at— andlearner_idis now the internalapp_users.idUUID.Related tables
app_users—id(UUID, PK),name,email,clerk_id(legacy column name holding the Supabaseauth.users.idas text),agency_id,role.user_roles— checked for theLEARNERrole gate before an existing user can be enrolled.quests—enrollment_limit(nullable cap) andenrollment_count(maintained counter).Cohort tables (Feature
[11.5], related only) —learner_groups,group_memberships,group_enrollments.
Enrollment counter (changed in 2.0 — PR #875 / issue #860)
quests.enrollment_countis now authoritative in the database via statement-level triggers onquest_enrollments:sync_quest_enrollment_count_ins—AFTER INSERT ... REFERENCING NEW TABLE ... FOR EACH STATEMENT.sync_quest_enrollment_count_del—AFTER DELETE ... FOR EACH STATEMENT.sync_quest_enrollment_count_upd—AFTER UPDATE OF quest_id ... FOR EACH ROW(a row trigger, because PostgreSQL forbidsUPDATE OFwith a transition relation).
They call
private.recompute_quest_enrollment_counts(UUID[]), which recomputes once per distinct quest per statement (supabase/migrations/20260925_harden_core_table_rls_followups.sql:127-225).Corrected in 2.0: the old API-side
syncEnrollmentCount()helper /sync_quest_enrollment_count()RPC has been removed from the API; the trigger is authoritative on every write path (including direct PostgREST writes). The now-unused RPC is revoked from API roles (:244-254).Row Level Security
RLS for the core tables (includingquest_enrollments) was swept and hardened across20260922_export_core_table_rls.sql,20260924_harden_core_table_rls_gaps.sql, and20260925_harden_core_table_rls_followups.sql. The learner self-enroll policy is gated byprivate.quest_is_published(UUID)(published-only), and the trigger functions areSECURITY DEFINERin the non-exposedprivateschema.API Specification
Creator APIs (all require a creator/agency context via
getCreatorAuthContext()and, where a quest is named,verifyAgencyResourceOwnership())POST
/api/creator/enrollment/create-enrollment— Body{ quest_id, learner_email }. Creates a single enrollment. If noapp_usersrow exists for the email, auto-provisions a Supabase Auth user (random secure password,email_confirm: true), syncs it viasyncUser, and sends the account + enrollment email; otherwise it verifies theLEARNERrole, blocks duplicates, enforcesenrollment_limit, and sends the enrollment email. Provisions the creator's default cohort when the creator has anagency_id(app/api/creator/enrollment/create-enrollment/route.ts:14-17,209-242).POST
/api/creator/enrollment/bulk-create?questId=<uuid>— Multipart CSV upload. H3 caps (PR #871): rejects bodies over1 MB(+64 KB multipart overhead) and caps rows at500. Emails are sent with bounded concurrency (5) and a100 msinter-send delay. Never resets an existing user's password; issues a recovery link instead. Returns{ successfulEnrollments, failedEnrollments: [{ email, reason }] }(app/api/creator/enrollment/bulk-create/route.ts:28-32,385-405,528-531).GET
/api/creator/enrollment/list-enrollments?quest_id=<uuid>— Returns all enrollments for a quest (learner name/email,status,enrolledAt, numericprogress), newest first (app/api/creator/enrollment/list-enrollments/route.ts:41-86).GET
/api/creator/enrollment/search-emails?q=<query>&quest_id=<uuid>— Agency-scoped autocomplete. Requires an agency member; returns up to20learner emails matching the query (min length2) that are not already enrolled (app/api/creator/enrollment/search-emails/route.ts).PUT
/api/creator/enrollment/update-enrollment— Body{ enrollment_id, quest_id, status?, progress? }. Updatesstatusand/orprogress(clamped 0–100) (app/api/creator/enrollment/update-enrollment/route.ts:7-17).DELETE
/api/creator/enrollment/delete-enrollment— Body{ enrollment_id, quest_id }. Permanently deletes an enrollment (revokes access) (app/api/creator/enrollment/delete-enrollment/route.ts).
Corrected in 2.0: the original spec listed
GET /list,PATCH /update-status, andDELETE /delete, and a bulk body of{ quest_id, learners: [{ name, email }] }. The current endpoints arelist-enrollments(GET),update-enrollment(PUT),delete-enrollment(DELETE), andbulk-create(multipart CSV keyed by?questId=).search-emailsis new.Learner APIs
POST
/api/learner/enroll— Body{ quest_id }. Requires an authenticated learner; the quest must bepublished. Idempotent (returns the existing row if already enrolled); writesstatus: "active"and a fresh zeroedprogress; triggers default-cohort provisioning fire-and-forget (app/api/learner/enroll/route.ts).GET
/api/learner/get-enrollment— Returns the learner's enrollment state for the quest player.POST
/api/learner/update-progress— The primary learner write path; computes weighted progress (60 % card visits / 40 % interactables) and persists it before any achievement/milestone evaluation (see the Gamification guide).
Logic & Workflows
Single Enrollment
The creator opens the Enrollment page in the quest editor and adds a learner in the inline Add Learners dialog (email field with a debounced
search-emailsautocomplete).On submit,
EnrollmentService.createEnrollment()posts{ quest_id, learner_email }tocreate-enrollment.Server-side: auto-provision (or recover) the account if needed, verify the
LEARNERrole, reject duplicates, enforceenrollment_limit, insert the row withstatus: "ongoing", and send the appropriate email.The page refetches the list; the new enrollee appears in the table.
Bulk Enrollment (CSV)
The creator opens
BulkAddLearnersModal, chooses a CSV whose accepted column isemail(extra columns are ignored;bulkEnrollmentSchemavalidates an array of{ email }).The file is posted as multipart to
bulk-create?questId=<uuid>.Server-side: the H3 size/row caps are enforced, then each email is resolved to an existing learner or auto-provisioned; duplicates, non-learners, and provisioning failures are collected in
failedEnrollments[].Surviving rows are inserted in a single batch; emails are sent with bounded concurrency; successful and failed counts are returned and surfaced in the UI.
Default-cohort provisioning runs for each newly enrolled learner via a bounded worker pool (awaited so serverless teardown cannot orphan the chains).
Auto-provisioning & recovery (2.0 — PRs #706 / #718 / #871)
New emails → create the Supabase Auth user with
generateSecurePassword(),syncUser, send the account + enrollment email.Existing Auth user without an
app_usersrow → recover it (findAuthUserByEmail), sync, and send an enrollment email.Bulk: an existing user's password is never reset; a one-time Supabase Auth recovery link is sent instead (
bulk-create/route.ts:385-405).
Self-enrollment
A signed-in learner opens a published quest.
The client posts
{ quest_id }to/api/learner/enroll.The route rejects unpublished quests, returns the existing enrollment if present (idempotent), otherwise inserts with
status: "active", then provisions the creator's default cohort fire-and-forget.
Key Files
app/quest-editor/[questID]/(sections)/enrollment/page.tsx— the page that orchestrates the dialog, tables, modals, andsearch-emailscalls.lib/enrollment/enrollmentService.ts— client service layer (EnrollmentService:fetchEnrollments,createEnrollment,updateEnrollmentStatus,deleteEnrollment).components/creator/enrollment/BulkAddLearnersModal.tsx— bulk CSV upload UI.components/creator/enrollment/GroupEnrollmentTable.tsx,AssignToGroupsModal.tsx,ConfirmEnrollmentModal.tsx— cohort enrollment (Feature[11.5]).components/shared/confirm-destructive-modal.tsx— delete confirmation.app/api/creator/enrollment/create-enrollment/route.ts— single enrollment.app/api/creator/enrollment/bulk-create/route.ts— bulk enrollment.app/api/creator/enrollment/list-enrollments/route.ts— list enrollments.app/api/creator/enrollment/search-emails/route.ts— learner email autocomplete.app/api/creator/enrollment/update-enrollment/route.ts— status/progress update.app/api/creator/enrollment/delete-enrollment/route.ts— delete enrollment.app/api/learner/enroll/route.ts— learner self-enrollment.lib/email/renderEmail.tsx—renderQuestEnrollmentEmail,renderAutoProvisionedEnrollmentEmail.lib/services/creatorCohortService.ts—provisionLearnerCohortSafe,provisionLearnerCohortForQuestSafe(default cohorts).lib/auth/passwordUtils.ts—generateSecurePassword.lib/utils.ts—normalizeProgress.
Corrected in 2.0: the original Key Files listed
components/creator/enrollment/AddLearnerModal.tsx. That component still exists but is no longer used by the enrollment page, which renders the single-add dialog inline; single-add now takes email only, not a name.EnrolleeTablealso appears to be defined locally inside the page rather than as a standalone component.
5. Infrastructure & Operations
Dependencies
Internal:
quests(andenrollment_limit/enrollment_count) andapp_users(id,email,role,agency_id);user_rolesfor the learner gate.External: Supabase (PostgreSQL for storage, Supabase Auth for identity); an SMTP provider (Nodemailer) for enrollment emails, configured through the shared email service.
Default cohorts:
lib/services/creatorCohortService.ts(Feature[11.5]) — a failure is swallowed by the*Safewrappers so it can never fail an enrollment.
Monitoring & Alerting
Standard monitoring for all
/api/creator/enrollment/*and/api/learner/enrollendpoints.Alert on a high rate of
5xxresponses, which can indicate a database or email-service problem.Log email-send failures at high severity;
bulk-createlogs per-recipient email failures without aborting the batch.bulk-createandcreate-enrollmentauto-provisioning failures are logged (console.error); bulk failures are also returned per-row infailedEnrollments[].
Deployment Plan
Apply database migrations in order. RLS/counter migrations must be applied before the code that drops the
syncEnrollmentCount()calls:Core-table RLS:
20260922_export_core_table_rls.sqlRLS gaps:
20260924_harden_core_table_rls_gaps.sqlRLS + counter follow-ups:
20260925_harden_core_table_rls_followups.sql(statement-levelenrollment_counttriggers; revokes the unusedsync_quest_enrollment_count()RPC). Once the API call is gone, this migration's trigger is the only thing keepingquests.enrollment_countcorrect — there is no runtime fallback if it is skipped.
Environment variables:
NEXT_PUBLIC_APP_URL(orAPP_URL) — used to build the recovery-linkredirectTo(.../reset-password); SMTP variables for the email service; Supabase URL/keys.Verify:
enrollment_countstays correct across insert/delete (including bulk), learner self-enroll is blocked for unpublished quests, and recovery links resolve.Roll out the updated enrollment page, API routes, and cohort provisioning.
Promote to staging after validation.
6. Testing & Quality Assurance
Test Strategy
Automated coverage (current)
Area
Test file
Notes
Bulk enrollment API
tests/creator/enrollment/bulk-enrollment.test.tsExercises the bulk-create route/schema and failure paths.
Enrollment E2E
tests/e2e/creator/enrollment.spec.tsCreator enrollment flow.
Group enrollment service
tests/services/group-enrollment-service.test.tsCohort/group enrollment (Feature
[11.5]).Manual coverage
Single add: existing learner, new (auto-provisioned) learner, duplicate (blocked), non-learner (blocked),
enrollment_limitreached (blocked), autocomplete excludes already-enrolled learners.Bulk add: valid CSV, malformed CSV, over 1 MB, over 500 rows, mixed valid/failed rows (verify
failedEnrollments[]reasons), duplicate emails.Manage: change status (
ongoing/finished), delete an enrollment, verifyenrollment_countdecrements.Self-enroll: published quest succeeds and is idempotent; unpublished quest is rejected.
Emails: quest-link email for existing learners; account + set-password/recovery-link email for auto-provisioned learners.
Corrected in 2.0: the original guide claimed comprehensive unit tests in
enrollmentService.test.tsand component tests inAddLearnerModal.test.tsx. Those exact files are not present in the current tree; the enrollment test coverage is the files listed above.Known Limitations
Base DDL is not in version control.
quest_enrollmentshas no committedCREATE TABLE; the schema here is reconstructed. (Documented in 2.0.)Status vocabulary drift. Creator-created rows use
status: "ongoing", while learner self-enroll writes"active"; the creatorEnrolleetype only modelsongoing | finished, so a self-enrolled learner's"active"status is outside the creator type's union. (Documented in 2.0.)Learner self-enroll RLS enforces self + published only — not enrollment limits or fees (noted in the #875 review). (Documented in 2.0.)
Anonymous/public-link self-enrollment is not supported (requires an authenticated learner). (Still open.)
Payment / paid enrollment is not implemented. (Still open.)
Detailed CSV error reporting.Resolved (2.0): per-rowfailedEnrollments[]with reasons (PR #718).Group-based/cohort enrollment is documented separately under Feature
[11.5]; this guide covers only the enrollment-time default-cohort hook. (By design.)Cohort provisioning depends on the creator having an
agency_id; individual (non-agency) creators do not get default-cohort provisioning. (Documented in 2.0.)
7. Maintenance & Support
Troubleshooting
Learner not appearing in the table after being added
Check the browser console for network errors on
create-enrollmentorlist-enrollments.Confirm the response was a success;
create-enrollmentreturns specific 4xx errors for duplicate ("Learner is already enrolled in this quest"), non-learner ("Only users with LEARNER role can be enrolled in quests."), and limit ("Enrollment limit reached for this quest").Refresh to force a refetch.
Enrollment emails are not being sent
Check server logs for email-service / SMTP errors.
Verify SMTP environment variables in the deployment environment.
For auto-provisioned learners, verify
NEXT_PUBLIC_APP_URL(orAPP_URL) so the set-password/recovery link is correct.
Bulk CSV upload fails
Ensure the file has an
emailcolumn.Verify the file is ≤ 1 MB and ≤ 500 rows (H3 caps, PR #871).
Check
failedEnrollments[]in the response for per-row reasons ("Already enrolled","User is not a learner","Failed to provision account","Account exists but setup incomplete").
enrollment_countis wrong / the limit is mis-enforcedConfirm the statement-level triggers
sync_quest_enrollment_count_ins/_del/_updexist onpublic.quest_enrollments(migration20260925).Apply the migration before deploying code that no longer syncs counts from the API; there is no runtime fallback.
Self-enrollment returns “Quest not available”
The quest must have
publishing_status = 'published'. Unpublished quests are rejected by design.
Auto-provisioning reports an account already exists
The email exists in Supabase Auth but has no usable
app_usersrow; the flow attempts recovery and, on failure, returns"Account exists but setup incomplete". Ask the user to log in first.
Changelog
1.0 (2026-06-29) — Approved, feature refactored and documented. Written by Uriel.
2.0 (2026-10-01) — documented by Patrick Babala
Corrected the auth provider (Clerk → Supabase Auth) and
learner_id(Clerk ID →app_users.idUUID); fixed the schema (enrolled_atreplacescreated_at/updated_at).Corrected the API spec:
list-enrollments(GET),update-enrollment(PUT),delete-enrollment(DELETE),bulk-create(multipart?questId=), plus the newsearch-emailsendpoint and learner self-enroll.Documented auto-provisioning and account recovery (PRs #706 / #718 / #871) and the H3 CSV caps + recovery-link-instead-of-password-reset (PR #871).
Documented
enrollment_countas trigger-authoritative in the DB and removed the obsoletesyncEnrollmentCount()reference (PR #875 / issue #860).Documented enrollment-time default-cohort provisioning (PR #869, Feature
[11.5]).Marked resolved Non-Goals / Known Limitations (CSV error reporting, self-enrollment, automatic group enrollment) instead of deleting them; added new limitations (status drift, missing base DDL, agency-only cohorts).
Replaced the ASCII diagram with a layered architecture diagram recreated from the current code.
Document Version
2.0 – Updated by Patrick Babala, 2026-10-01. Overhauled for the current codebase: Supabase Auth, corrected schema/endpoints/helpers, auto-provisioning + recovery-link flow, H3 bulk-CSV caps, trigger-maintained
enrollment_count, default-cohort provisioning, resolved items marked, and a redrawn layered architecture diagram. Update PRs: #706, #718, #790, #869, #871, #875.1.0 – Approved, feature refactored and documented, 2026-06-29 (written by Uriel)