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.

Stack: React 19, TypeScript, TanStack Query, Tailwind CSS, Supabase (Postgres, RLS), pgTAP, GitHub Actions.

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

The GradeBook permission matrix: actions such as view_sheet and edit_grades down the side; head teacher, TA, Senior TA and student across the top; each cell a scope.

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.

Scopes per role for four grade actions
ActionHead teacherTASenior TAStudent
View the grade sheetFull accessAssigned coursesAssigned coursesOwn
Edit gradesFull accessNoneAssigned coursesNone
Correct closed gradesFull accessNoneAssigned coursesNone
Close the monthFull accessNoneAssigned coursesNone

Three decisions shaped it:

  1. The head teacher edits the matrix. Each school sets its own staff permissions.
  2. Each role has a ceiling. The head can give a TA less, never more. The head can’t raise the ceiling either.
  3. 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 Correct a closed grade dialog: a new score and a written reason, saved to the cell’s history.

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.
A TA opening a course sheet without access: a lock and the message You don’t have access to this sheet.

How it’s tested

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

    A GitHub Actions run of the database tests: every pgTAP file ok, 221 tests, result PASS.
  2. 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.