GradeBook: permissions enforced by Postgres
A multi-tenant app I built to learn Postgres permissions properly. I wrote the database by hand: RLS, functions, triggers and pgTAP tests. The React front end was built with Claude Code.
The problem
Many schools share one database. Inside each school, people need different access:
- The head teacher runs the school and sets staff permissions.
- A TA works only on the courses they’re assigned to.
- A student sees only their own grades.
- A student who leaves loses access to their old grades.
- The platform admin approves new schools but can’t read any school’s grades.
And none of it can depend on the UI hiding a button.
The permission model

I made it a matrix: actions down the side, roles across the top. Each cell holds a scope: None, Own, Assigned courses, or Whole school. The head teacher’s column is locked to full access.
| Action | Head teacher | TA | Senior TA | Student |
|---|---|---|---|---|
| View the grade sheet | Full access | Assigned courses | Assigned courses | Own |
| Edit grades | Full access | None | Assigned courses | None |
| Correct closed grades | Full access | None | Assigned courses | None |
| Close the month | Full access | None | Assigned courses | None |
Three decisions shaped it:
- The head teacher edits the matrix. Each school sets its own staff permissions.
- Each role has a ceiling. The head can give a TA less, never more. The head can’t raise the ceiling either.
- Combinations that mean nothing are refused. “Own” on the grade sheet means nothing for a TA, so the database rejects it instead of storing it.
How it’s enforced
There is no API server. The browser talks to Supabase directly, with a key anyone can see in the browser. So the rules have to live in Postgres.
More than 30 RLS policies call a has_permission function. A request from the app and a request sent by hand get the same answer.
Grades get two more rules:
- Closed months. Closing a month freezes its grades and saves a copy of the score bands, so later band changes don’t rewrite past results. A closed month is never reopened. Fixing a grade needs a separate permission and a written reason.
- History. A database trigger records every grade change: who made it and in which role.

The grade sheet
Staff enter grades all day, so the sheet has to feel like a spreadsheet, not a form:
- Keyboard navigation, range selection, and paste from Excel or Google Sheets.
- Edits save in batches in the background. The sheet updates right away. If a save fails or the database refuses it, the affected cells roll back and the sheet offers a retry.
- Cells lock based on your permissions. If the head teacher takes away your access, the page updates by itself.

How it’s tested
221 pgTAP tests run against a fresh Postgres on every push. They cover tenant isolation, each permission scope, ceilings, closed months and history.

An attack script logs in with real user keys and makes 83 attempts: as another school, as a removed student, as a Senior TA on a course she isn’t on, as a head teacher going past the ceiling, and as the platform admin.
blocked student, gone reads his old grades (0 rows)
blocked Senior TA closes a month of a course she is not on (error 42501)
blocked head gives the TA more than the ceiling (error 23514)
blocked head raises the platform ceiling (no rows changed)
blocked platform admin reads a school's grades (0 rows)
blocked other school reads Kodlab's grades (0 rows)
blocked other school saves Kodlab's matrix (error 42501)
83 of 83 attempts blocked.Try the demo
Sign in as any role at gradebook-project.vercel.app.
- Head teacher
head@kodlab.test- TA
ta@kodlab.test- Senior TA
senior@kodlab.test- Student
ali@kodlab.test- Password, all four
demo+password
To see the permissions at work, sign in as the TA in one window and as the head teacher in another, then take away the TA’s access.