Lesson-checkpoint attempt route gates on a self-issuable enrollment row, bypassing entitlements and the access cutoff (#509 follow-up)

[guillermoscript]

Follow-up to #493 / #494 / #509 — found reviewing the epic's shipped code (2026-07-25).

Problem

#509 (PR #518) gated every student page with requireCourseAccess() and made RLS the database backstop for lessons / exercises / exams (migration 20260724150000). The lesson-checkpoint attempt API route is outside both halves of that fix: it reads with the service-role client, so RLS never applies, and its access check is an enrollments row rather than has_course_access().

Evidence

app/api/lesson-checkpoints/[checkpointId]/attempt/route.ts:

:66   const adminClient = createAdminClient()          // → #509's RLS backstop cannot apply
:70   .from('lesson_checkpoints').select('… exercises(… system_prompt, exercise_config, course_id …)')
:77   if (!checkpoint || checkpoint.tenant_id !== tenantId …)   // tenant check only

:98   const { data: enrollment } = await adminClient
        .from('enrollments')
        .select('enrollment_id')
        .eq('user_id', user.id)
        .eq('course_id', exercise.course_id)
        .eq('status', 'active')
:106  if (!enrollment) return Response.json({ error: 'Not enrolled in this course' }, { status: 403 })

No has_course_access / requireCourseAccess call anywhere in the file (grep -rn "hasCourseAccess\|has_course_access" app does not list it).

Why the enrollment gate is not an access check:

1. enrollments has been a learning-progress record only since migration 20260516150000 — access lives in entitlements. Nothing revokes an enrollment: webhook-dispatch.ts:261-266 revokes entitlements on refund, and tenants.access_cutoff_at (Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494) is read exclusively inside has_course_access().

2. The row is self-issuable. Verified against the cloud project:

   enrollments / INSERT / {authenticated}
   with_check: (auth.uid() = user_id) AND (tenant_id = get_tenant_id())
   

   No entitlement predicate — any tenant member can POST /rest/v1/enrollments for any course_id in their tenant and satisfy this route's gate. (The same predicate also backs lesson_checkpoint_attempts_students_insert_own_rows, so the RLS insert policy this check mirrors is equally self-satisfiable.)

Impact

Three populations reach paid checkpoint content through this route despite the epic's enforcement:

• a student whose tenant is past access_cutoff_at — Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494 enforcement does not reach here at all;
• a student whose entitlement was revoked by a refund (No refund/chargeback clawback for manual payouts already marked paid #498/Subscription refunds never flip transaction status, so they are never clawed back (#498 follow-up) #515) — the enrollment row survives;
• any tenant member with no entitlement who self-inserts an enrollment row.

What they get: AI evaluation of the checkpoint exercise, per-question feedback and score (Response.json(result) at :315), plus writes to lesson_checkpoint_attempts, exercise_evaluations, exercise_completions and the XP ledger. Narrower than the original #509 finding — system_prompt and exercise_config stay server-side, so this is unpaid consumption of paid AI and progress-writing rather than a raw content dump — but it is the same root cause: admin client plus the wrong gate.

Wanted

1. Replace the enrollments lookup with the real access check — resolveCourseAccessState() / hasCourseAccess() on exercise.course_id — returning 403 for a missing entitlement and something distinguishable (or the same 403) for a tenant cutoff. lib/services/course-access.ts is the API-safe module; course-access-guard.ts imports next/navigation and is for pages only.
2. Audit the sibling admin-client routes for the same pattern while in there — app/api/exercises/media/analyze/route.ts and app/api/exercises/media/signed-url/route.ts are tenant/ownership-checked only (both currently reachable only via the gated upload path, so they are defence-in-depth, not live holes).
3. Consider tightening the enrollments INSERT policy, or explicitly documenting that an enrollment row is not an access grant so the next reader does not reuse it as one. Note lesson_checkpoint_attempts' insert policy leans on it too.

docs/MONETIZATION.md's enforcement section should end up accurate about API routes, not just pages — that claim being wrong for lessons/exercises is what #509 was.

[guillermoscript]

An AI agent (Claude Code) is picking this up. Plan of attack below; implementation follows on fix/532-checkpoint-attempt-entitlement-gate.

Root cause

app/api/lesson-checkpoints/[checkpointId]/attempt/route.ts reads through createAdminClient() (so #509's RLS backstop from migration 20260724150000 never applies) and gates on an enrollments row (:97-107) rather than on the entitlements model. Confirmed against the live cloud project:

• has_course_access(uuid, integer) is the only place that reads entitlements.status, expires_at and tenants.access_cutoff_at. The route never calls it.
• enrollments INSERT policy Users can create own enrollments has with_check = (auth.uid() = user_id) AND (tenant_id = get_tenant_id()) — no entitlement predicate, so the row this route trusts is self-issuable by any tenant member.
• The same self-issuable enrollments EXISTS also backs lesson_checkpoint_attempts_students_insert_own_rows, so the RLS insert the route defers to at :276 is equally satisfiable.

Sibling admin-client routes were surveyed as the issue asks. app/api/exercises/media/upload-url/route.ts:68 and app/api/exercises/artifact/evaluate/route.ts:55 already call hasCourseAccess(); media/analyze and media/signed-url check submission ownership + tenant only.

Approach

1. Real access check on the checkpoint attempt route. Replace the enrollments lookup with resolveCourseAccessState(adminClient, user.id, exercise.course_id) from lib/services/course-access.ts (the API-safe module — course-access-guard.ts imports next/navigation and is pages-only). Both refusals return 403; the tenant-cutoff case carries a distinguishable payload so the checkpoint UI can say "your school's access is suspended" rather than "not enrolled". Client-side error handling in components/lesson/checkpoints/checkpoint-exercise-renderer.tsx already surfaces payload.error, so the copy change is a translation string, not a refactor.

2. Defence-in-depth on the two media routes. Add a hasCourseAccess() check on the submission's exercises.course_id in app/api/exercises/media/analyze/route.ts and app/api/exercises/media/signed-url/route.ts. Neither is a live hole today (both are reachable only after upload-url, which is gated), but both burn AI credits / hand out storage URLs off an admin client, and a revoked entitlement or a tenant cutoff arriving between upload and analyze is exactly the gap. signed-url currently selects no course_id, so its select grows an exercises(course_id) join.

3. Migration — stop enrollments being an access grant. Two policy rewrites:

• enrollments INSERT with_check gains has_course_access(auth.uid(), course_id).
• lesson_checkpoint_attempts_students_insert_own_rows swaps its enrollments EXISTS for has_course_access(auth.uid(), course_id).

This is safe because every legitimate enrollment-creating path bypasses RLS: enroll_user(), self_enroll_subscription_course(), grant_free_entitlement() and issue_certificate_if_eligible() are all SECURITY DEFINER (verified in pg_proc), and the seed/admin paths use the service-role client. grep -rn "from('enrollments')" across app/, lib/, components/, hooks/ finds no client-side insert. The policy is tightened rather than dropped so any entitled caller keeps working.

4. Docs. docs/MONETIZATION.md §"Post-Downgrade Access Enforcement" currently claims the enforcement layer covers "the certificate, AI tutor and exercise-submission APIs" — make that list actually true of the API surface, and state explicitly that an enrollments row is a progress record, not an access grant, so the next reader doesn't reuse it as one.

Risks / blast radius

• has_course_access() has no staff branch (it reads entitlements only), so switching a gate to it breaks any teacher/admin path through the same route. Checked: CheckpointExerciseRenderer renders only from app/[locale]/dashboard/student/courses/[courseId]/lessons/[lessonId]/, and proxy.ts keeps non-students out of /dashboard/student/*. Teacher-side checkpoint work goes through app/actions/teacher/lesson-checkpoints.ts, which reads attempts and never posts them. No staff regression expected — will confirm with the teacher checkpoint E2E spec.
• Tightening the enrollments INSERT policy is the widest-blast change here. It reaches anything that inserts an enrollment through an RLS-bound client, including the sibling lms-app mobile client. Survey found no such caller; the mitigation is that the policy still permits the insert for anyone who actually holds access, so only the self-issue bypass dies.
• Free courses stay fine: grant_free_entitlement() writes an entitlements row before the enrollment, so has_course_access() is already true.
• E2E specs that manipulate enrollments (student-exams.spec.ts, enrollment-flows.spec.ts) use the service-role client and are unaffected by the policy change.
• A student mid-attempt when their tenant crosses access_cutoff_at now gets a 403 instead of a silent success. That is the intended Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494 behaviour, and it is the same treatment the lesson page itself gives them.

Test plan

• npm run typecheck · npm run lint · npm run build — judged on new errors in changed files (the repo carries a pre-existing lint baseline).
• npm run test:unit.
• npx vitest run on any checkpoint/entitlement unit coverage; add a unit test for the new refusal branches if the route has a testable seam.
• Migration applied to the local DB via npm run db:reset, then policy predicates re-read from pg_policies to confirm they landed as written.
• SQL-level proof of the fix, run as a non-entitled tenant member against the local DB: the direct INSERT INTO enrollments that used to succeed must now be refused, and has_course_access() must be false for that user/course pair.
• Playwright: npx playwright test -g "checkpoint" plus entitlements-overlap.spec.ts and enrollment-flows.spec.ts (lvh.me, --workers=1).
• Manual: as alice@student.com on Code Academy, complete a checkpoint on an entitled course (still works); revoke the entitlement and repeat (403, "no access"); set tenants.access_cutoff_at in the past and repeat (403, suspended copy).

[guillermoscript]

Closed by #535 (merged as 4b2cb16).

The checkpoint attempt route now gates on resolveCourseAccessState() rather than an enrollments row, the two sibling admin-client media routes got the same check, and migration 20260725160000 makes both the enrollments and lesson_checkpoint_attempts INSERT policies require has_course_access() — so an enrollment row is no longer self-issuable and no longer usable as proof of access anywhere. All three items in "Wanted" were addressed, including the documentation point.

One caveat carried over: the migration has been applied to the local database only, not to the cloud project. Full details, the QA script and that caveat are in #535.