typescript-api-enroll-by-email-null-school

Answer

1. Root cause: staff_find_student_by_email never matched school_id IS NULL

Self-registered students have users.school_id = NULL. The SECURITY DEFINER helper enforced u.school_id = auth.current_school_id() — and in SQL, NULL = anything evaluates to NULL (falsy), so the row was filtered out and the endpoint 404'd even though the student existed.

Migration 025_enroll_by_email_null_school.sql — keep the staff scope, but allow school_id IS NULL as an additional match:

-- migrations/025_enroll_by_email_null_school.sql
BEGIN;

CREATE OR REPLACE FUNCTION staff_find_student_by_email(p_email TEXT)
RETURNS SETOF users
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT u.*
  FROM users u
  WHERE u.email = lower(p_email)
    AND (u.school_id = auth.current_school_id() OR u.school_id IS NULL)  -- NULL branch added
  LIMIT 1;
$$;

-- Staff-scoped: only the staff role may execute this function
REVOKE ALL ON FUNCTION staff_find_student_by_email(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION staff_find_student_by_email(TEXT) TO app_staff;

COMMIT;

The function remains SECURITY DEFINER + staff-gated (app_staff), so admitting school_id IS NULL students does not leak cross-school data: an enrollment token can only be minted by a staff caller, and school-scoped students of other schools still don't match.

2. Zod .uuid() on enroll studentId (500 → 400)

The enroll handler previously sent the raw studentId straight to Postgres; a non-UUID string raised 22P02 invalid_text_representation → unhandled → 500. Validate as UUID up front:

// src/routes/enroll.ts
import { z } from "zod";

const enrollByEmailSchema = z.object({
  studentId: z.uuid(),   // was z.string() — invalid input now 400, not a DB 500
  courseId:  z.uuid(),
}).strict();

router.post("/enroll-by-email", async (req, res) => {
  const parsed = enrollByEmailSchema.safeParse(req.body);
  if (!parsed.success) {
    return res.status(400).json({
      error: "Invalid request body",
      issues: parsed.error.issues.map((i) => ({ path: i.path.join("."), message: i.message })),
    });
  }
  // ...lookup via staff_find_student_by_email, then insert enrollment
});

3. .strict() on updateQuizSchema (silent 200 no-op → 400)

Without .strict(), Zod strips unknown keys by default. A client sending { publish: true } (the wrong endpoint) got a silent 200 with no change — a debugging trap. Now unknown keys fail fast with a pointer to the correct route:

// src/routes/quizzes.ts
const updateQuizSchema = z.object({
  title: z.string().min(1).optional(),
  description: z.string().optional(),
}).strict();   // unknown keys → 400 instead of silent strip

router.patch("/api/quizzes/:id", async (req, res) => {
  const parsed = updateQuizSchema.safeParse(req.body);
  if (!parsed.success) {
    const unknown = parsed.error.issues
      .filter((i) => i.code === "unrecognized_keys")
      .flatMap((i) => i.keys);
    return res.status(400).json({
      error: `Unknown field(s): ${unknown.join(", ")}`,
      hint: "To publish a quiz, use POST /api/quizzes/:id/publish",
    });
  }
  // ...apply allowed updates
});