Enrollment

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 touching create-enrollment, the enrollment page, and update-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_enrollments RLS 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 (ongoing vs. 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-create now returns a per-row failedEnrollments[] array with a machine-readable reason (e.g. "Already enrolled", "User is not a learner") alongside successfulEnrollments (app/api/creator/enrollment/bulk-create/route.ts:528-531).

  • Glossary

    • Enrollment: A record in the quest_enrollments table 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_users row 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 progress JSONB blob on quest_enrollments (percentage, visited_cards, last_visited_at, completed_nodes).

    • enrollment_count: A denormalized counter on quests, maintained by database triggers, compared against enrollment_limit before 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_enrollments and 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 and authenticateUser() (lib/auth/authenticate.ts) for learners. The legacy-named app_users.clerk_id column now stores the Supabase auth.users.id as 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 the quest_enrollments table.

    Caveat (2.0): the base CREATE TABLE for quest_enrollments is 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_enrollments table:

    Column

    Type

    Description

    id

    UUID

    Primary key.

    quest_id

    UUID

    Foreign key to quests.id.

    learner_id

    UUID

    Foreign key to app_users.id (internal user UUID). Enables the app_users!inner embeds in list-enrollments / update-enrollment.

    status

    TEXT

    Enrollment status. Creator-created rows use ongoing; learner self-enroll uses active; the creator Enrollee type models ongoing | finished (types/creator.d.ts:248-261).

    progress

    JSONB

    Progress blob: percentage, visited_cards, last_visited_at, completed_nodes.

    enrolled_at

    TIMESTAMPTZ

    When the enrollment was created (the list endpoint orders by this).

    Corrected in 2.0: the original table documented learner_id as TEXT holding the Clerk ID, and listed created_at / updated_at. The current code never reads or writes created_at / updated_at — it uses enrolled_at — and learner_id is now the internal app_users.id UUID.

  • Related tables

    • app_users — id (UUID, PK), name, email, clerk_id (legacy column name holding the Supabase auth.users.id as text), agency_id, role.

    • user_roles — checked for the LEARNER role gate before an existing user can be enrolled.

    • quests — enrollment_limit (nullable cap) and enrollment_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_count is now authoritative in the database via statement-level triggers on quest_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 forbids UPDATE OF with 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 (including quest_enrollments) was swept and hardened across 20260922_export_core_table_rls.sql, 20260924_harden_core_table_rls_gaps.sql, and 20260925_harden_core_table_rls_followups.sql. The learner self-enroll policy is gated by private.quest_is_published(UUID) (published-only), and the trigger functions are SECURITY DEFINER in the non-exposed private schema.

  • 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 no app_users row exists for the email, auto-provisions a Supabase Auth user (random secure password, email_confirm: true), syncs it via syncUser, and sends the account + enrollment email; otherwise it verifies the LEARNER role, blocks duplicates, enforces enrollment_limit, and sends the enrollment email. Provisions the creator's default cohort when the creator has an agency_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 over 1 MB (+64 KB multipart overhead) and caps rows at 500. Emails are sent with bounded concurrency (5) and a 100 ms inter-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, numeric progress), 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 to 20 learner emails matching the query (min length 2) 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? }. Updates status and/or progress (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, and DELETE /delete, and a bulk body of { quest_id, learners: [{ name, email }] }. The current endpoints are list-enrollments (GET), update-enrollment (PUT), delete-enrollment (DELETE), and bulk-create (multipart CSV keyed by ?questId=). search-emails is new.

    Learner APIs

    • POST /api/learner/enroll — Body { quest_id }. Requires an authenticated learner; the quest must be published. Idempotent (returns the existing row if already enrolled); writes status: "active" and a fresh zeroed progress; 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-emails autocomplete).

    • On submit, EnrollmentService.createEnrollment() posts { quest_id, learner_email } to create-enrollment.

    • Server-side: auto-provision (or recover) the account if needed, verify the LEARNER role, reject duplicates, enforce enrollment_limit, insert the row with status: "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 is email (extra columns are ignored; bulkEnrollmentSchema validates 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_users row → 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, and search-emails calls.

    • 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. EnrolleeTable also appears to be defined locally inside the page rather than as a standalone component.

5. Infrastructure & Operations

  • Dependencies

    • Internal: quests (and enrollment_limit / enrollment_count) and app_users (id, email, role, agency_id); user_roles for 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 *Safe wrappers so it can never fail an enrollment.

  • Monitoring & Alerting

    • Standard monitoring for all /api/creator/enrollment/* and /api/learner/enroll endpoints.

    • Alert on a high rate of 5xx responses, which can indicate a database or email-service problem.

    • Log email-send failures at high severity; bulk-create logs per-recipient email failures without aborting the batch.

    • bulk-create and create-enrollment auto-provisioning failures are logged (console.error); bulk failures are also returned per-row in failedEnrollments[].

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

      • RLS gaps: 20260924_harden_core_table_rls_gaps.sql

      • RLS + counter follow-ups: 20260925_harden_core_table_rls_followups.sql (statement-level enrollment_count triggers; revokes the unused sync_quest_enrollment_count() RPC). Once the API call is gone, this migration's trigger is the only thing keeping quests.enrollment_count correct — there is no runtime fallback if it is skipped.

    • Environment variables: NEXT_PUBLIC_APP_URL (or APP_URL) — used to build the recovery-link redirectTo (.../reset-password); SMTP variables for the email service; Supabase URL/keys.

    • Verify: enrollment_count stays 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.ts

    Exercises the bulk-create route/schema and failure paths.

    Enrollment E2E

    tests/e2e/creator/enrollment.spec.ts

    Creator enrollment flow.

    Group enrollment service

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

    Cohort/group enrollment (Feature [11.5]).

    Manual coverage

    • Single add: existing learner, new (auto-provisioned) learner, duplicate (blocked), non-learner (blocked), enrollment_limit reached (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, verify enrollment_count decrements.

    • 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.ts and component tests in AddLearnerModal.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_enrollments has no committed CREATE 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 creator Enrollee type only models ongoing | 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-row failedEnrollments[] 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-enrollment or list-enrollments.

      • Confirm the response was a success; create-enrollment returns 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 (or APP_URL) so the set-password/recovery link is correct.

    • Bulk CSV upload fails

      • Ensure the file has an email column.

      • 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_count is wrong / the limit is mis-enforced

      • Confirm the statement-level triggers sync_quest_enrollment_count_ins / _del / _upd exist on public.quest_enrollments (migration 20260925).

      • 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_users row; 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.id UUID); fixed the schema (enrolled_at replaces created_at/updated_at).

      • Corrected the API spec: list-enrollments (GET), update-enrollment (PUT), delete-enrollment (DELETE), bulk-create (multipart ?questId=), plus the new search-emails endpoint 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_count as trigger-authoritative in the DB and removed the obsolete syncEnrollmentCount() 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)


Was this article helpful?