Quiz results teachers can trust, question by question
School LMS lets teachers turn an uploaded exam or a blank page into a reviewed quiz, assign it to a class and see how each question went, while answers and scores stay inside the database.
Role
Full-stack web app
Platforms
Web
Stack
Next.js, Supabase, TanStack Query, Recharts
The problem
Fair quizzes are hard
A quiz is easy to build and hard to make fair. Anything sent to the browser can be read, a timer on a student's device can be changed, and a double click or a second tab can turn one attempt into two. If the answer key or the score passes through the client, the grade stops meaning anything.
Teachers also need to trust what they publish and what they read afterwards. A quiz drafted by a model from an uploaded exam can carry invented options or a wrong answer, and a per-question report only helps if it counts the skipped questions too.
Architecture
Postgres holds the rules, Next.js the experience
I put the rules that must never be wrong in Postgres. Row-level security ties reads to the caller's role and to what they own or belong to. Attempts and answers have no write policies for signed-in users, so the only way in is a small set of database functions.
Uploaded exams take a slower path: a checkpointed job parses the file, sends it to a model in chunks and writes a draft that a teacher must approve. The interface runs in English and Vietnamese.
Teachers fix flagged questions and approve each one
Data and rules
Supabase Postgres
Access policies, attempts, grading and analytics in SQL
Reporting
Recharts
Score trends and distributions
Key decisions
Three places trust is enforced
01
Grading students cannot reach
A browser can read anything it receives and lie about anything it sends, so correct answers and scores cannot depend on it. One attempt also has to stay one attempt across double clicks and second tabs.
Students have no read policy on questions or options, so the paper comes from a database function that leaves out the answer key. Submitting locks the attempt row, stores the answers and scores them in SQL, and a second submit just returns the finished attempt. A partial unique index allows only one open attempt per student and assignment.
Answers stop being accepted 60 seconds after the server-side deadline.
Results hide correct answers until the due date passes or every attempt is used.
An expired attempt is scored from what was saved in time, even if nobody submits it.
02
Model output is only a draft
A model can invent options or stop halfway through a long exam, and a serverless request can die in the middle of the work.
The job runs one step per request under a lease and saves a checkpoint after every parse submission and extracted chunk. Writes are fenced by the lease token, so a worker that lost its lease changes nothing. Output is validated and flagged, and publishing refuses any quiz with unapproved questions or blocking problems.
Questions whose options are mostly missing from the parsed exam get flagged.
A truncated reply splits its chunk in half instead of retrying the same input.
The 320 second lease outlasts the 300 second route limit, so live workers do not overlap.
03
Analytics inherit the access rules
A per-question report touches students, attempts, answers and answer keys. A separate permission layer for reporting would be one more place to get it wrong.
Teacher analytics functions run with the caller's own rights over a view that does the same, so the row-level policies decide what each teacher can count. A student with several attempts contributes one: the best score, with ties going to the earliest. Grading writes a row for every question, so skipped questions appear in the report instead of vanishing.
Each question reports responses, correct answers, skips and picks per option.
Published quizzes lock their questions and options, so results keep referring to the same content.
Policies work out the caller's ids once per query instead of for every row.
