Skip to main content

Architecture II: Supabase, RLS & Student-Data Privacy

Pixel art cutaway of a vast filing vault, every drawer a row of student grades. A tiny teal security guard with a badge reader stands in front of every drawer, hundreds of them receding into the distance. One student in warm amber waits at the left with a single badge. A counter overhead reads 105,000 checks. Tagline: One Badge. Every Row. Every Query.

Reminders​

  • Office hours: on Discord, or by appointment
  • Discussion also on Discord: Join via Pawtograder
  • DUE Wed Sep 16: The Ticket Hunt. 2 filed, 2 triaged
  • DUE Thu Sep 17: Project Bids
  • DUE Thu Sep 24: Onboarding: Gradebook Column Groups

If local dev still isn't running, that's today's problem, not next week's. Deliverable 1 is a migration, and a migration needs Path B.

CS 4535: Software Design & Delivery

Architecture II: Supabase, RLS & Student-Data Privacy​

©2026 Jonathan Bell, CC-BY-SA

Learning Objectives​

After this session, you'll be able to:

  1. Turn a domain concept into tables and keys, and say when to store a fact twice on purpose
  2. Say what a query costs, in pages, and what an index changes about that
  3. Explain how Postgres evaluates a row-level security policy: once per row, inside the query
  4. Predict both effects of a policy change: what a user can now see, and what it costs to ask
  5. Design the schema, policies and constraints for the column-groups table you hand in on the 24th
  6. Recognize an authorization bug during code review

How Today Works​

Today needs two kinds of thinking, and they are not the same skill:

Deciding what things are

Is a group a thing, or a property of a thing? A design question. No SQL required to have an opinion.

Knowing what they cost

What happens to that decision when the table has 105,000 rows in it and ten courses share a database.

Three working breaks. Grab a partner for those. Some slides are marked self-study and we won't cover them in the room.

The assignment due on the 24th needs both of them.

Start With the Nouns​

Before any SQL. What things exist in the world this product is about?

  • A class. CS 4535, Fall 2026
  • People in it, each with a role: student, grader, instructor
  • A gradebook. One per class
  • Columns in it: Assignment 1, Lab 3, Exam 2
  • A score: one student, one column, one number
  • A group of columns. "The labs." "The exams."

Five of those six are tables. The sixth is a naming convention and a memo.

A Noun Becomes a Table. A Relationship Becomes a Key.​

Three rules, and that is all of them. One row per thing. The "many" side carries the other one's id. That column is a foreign key, and Postgres refuses to store one that points at nothing.

You Can Just Look At It​

Supabase Studio ships with your local stack. Nothing to install. localhost:54323

Supabase Studio table editor showing the gradebook_columns table. A list of tables runs down the left. The grid shows 89 records with columns for id, created_at, class_id, name and description, listing rows such as Demo Assignment, Lab 1 (Group), Assignment 1, and twelve Skill rows.
Table editor. Every table, every row. 89 records in gradebook_columns, and no group column anywhere.
Supabase Studio policies page filtered to gradebook_column. It lists the policies on gradebook_column_students (instructors and graders edit, instructors and graders view all, instructors delete, student views non-private only) and on gradebook_columns (everyone in class can view, instructors CRUD), each labeled with its command.
Policies. Every rule protecting every table, as a list you can read. These two are the ones yours has to sit beside.

A schema isn't a thing you have to hold in your head. It's a thing you can open.

Say Each Fact Once​

Tempting shortcut: put the course name on every score row, so you never have to join.

 id | student | column | score | course_name
----+---------+--------+-------+--------------
  1 | alice   | lab-1  |  95.0 | CS 4535
  2 | alice   | lab-2  |  88.0 | CS 4535
  3 | bob     | lab-1  |  72.0 | CS 4535      ... x 105,000

Now the registrar renames the course.

  • 105,000 rows to update, and if 4,000 of them fail, the course now has two names
  • A class with no scores yet has no name at all, because names only live on score rows
  • "What is this course called?" has no answer. It has a vote

So: each fact lives in exactly one place, and everything else points at it. That's normalization.

Now Say It Twice, On Purpose​

Every gradebook table in Pawtograder carries class_id, even though you could always find it by following keys upward:

gradebook_column_students -> gradebook_columns -> gradebooks -> classes

That is a duplicated fact, added deliberately. It buys two things:

  1. The security question becomes answerable on the row itself, with no join
  2. Three joins per row is three joins you pay for on every query

And it costs what duplication always costs: two copies that can disagree. So you buy a constraint that makes disagreement impossible to write down.

Denormalize deliberately, state what it bought, and pay for it with a constraint. Never by accident.

Discussion: Is a Group a Thing?​

In pairs, five minutes. Take a position and name one consequence.

A group is an entity

Its own table, its own id. Columns point at it.

A group is an attribute

One more field on gradebook_columns. No new table.

Three Claims​

1. A query's cost is the number of pages it touches.

2. A security policy is a predicate, evaluated once per row.

3. So a function call inside a policy is a loop you wrote without noticing.

Claim 3 is the one that will bite your migration. Claims 1 and 2 are why.

Everything Is Pages​

Postgres reads 8 kB pages, never individual rows. Rows live inside the pages.

A gradebook table built to the scale this product is heading for:

Rows20,000,000
Size on disk1,754 MB
Pages224,450
Rows per page89

Want one student's 200 scores? Those 200 rows live on about 3 pages. The question is whether Postgres knows which 3.

Where the Page Lives Decides What It Costs​

Where the page isRoughly how long to get itRelative
CPU L1 cache1 ns1x
Main memory (RAM)100 ns100x
NVMe SSD50,000 ns (50 µs)50,000x
Another machine, same datacenter500,000 ns (0.5 ms)500,000x
Spinning disk seek5,000,000 ns (5 ms)5,000,000x

Six orders of magnitude between the best case and the worst. No algorithm you write in TypeScript moves a number that far.

The Cache Is Smaller Than the Table​

Postgres keeps recently-used pages in a fixed pool, shared_buffers. On the instance I measured, that pool is 512 MB. The table is 1,754 MB.

So a full scan evicts itself as it runs. EXPLAIN (ANALYZE, BUFFERS) tells you which pages were already there and which had to be fetched:

Seq Scan on gcs_big  (actual rows=200 loops=1)
  Filter: (student_id = '...'::uuid)
  Rows Removed by Filter: 19999800
  Buffers: shared hit=44409 read=180041

hit was already in memory. read was not. 19,999,800 rows were examined and thrown away to find 200.

"Just Add RAM" Stopped Being the Cheap Answer​

For thirty years the standard response to a slow database was to buy memory until the working set fits. Then AI datacenters bought the memory.

In 2023 I bought 112 Micron 32 GB DDR4 modules for a research cluster.

2023Today
One module$93.48$396.00
Per GB$2.92$12.38
All 112 of them, 3.5 TB$10,470$44,352

Same part number. Same cluster. The memory I already own would cost $33,882 more to buy again.

The cheapest page is the one you never read. That has always been true. It is now also true on the invoice.

What an Index Buys​

One student's 200 scores, out of 20,000,000 rows. Same query, same machine, single-threaded.

TimePages touched
Read every row3,133 ms224,450
Find 200 rows, no index1,353 ms224,450
Find 200 rows, with an index0.1 ms6

Six pages: a short walk down the index, then the handful of heap pages holding the rows. 224,450 to 6.

The index costs 131 MB and has to be updated on every write. Cheap next to the alternative, but not free.

An Index Is a Narrow Copy​

The table row carries everything. The index carries the one column you search on, plus a pointer to the row.

the table
id
class_id
gradebook_column_id
student_id
score
is_private
is_missing
created_at
the index
student_id
→ which row
table, 1,754 MB
cache, 512 MB
index, 131 MB

The index fits in memory. The table never will. That's what you bought.

Authorization Is a WHERE Clause You Didn't Write​

The browser asks for this:

select id, student_id, score
from gradebook_column_students
where class_id = 1;

Postgres runs this:

select id, student_id, score
from gradebook_column_students
where class_id = 1
  and (  -- the policy, added by the database
    class_id in (select up.class_id from user_privileges up
                 where up.user_id = auth.uid())
  );

The rows are protected, so there is no code path to forget and no endpoint to remember to guard.

Four Things to Read Before You Write a Policy​

Reference, not a lecture. You'll want it the day you write your own policy.

USING vs WITH CHECKUSING filters rows that already exist. WITH CHECK constrains rows you're about to create. An UPDATE takes both, because it does both
The loose WITH CHECKOmit it on a FOR ALL policy and Postgres reuses USING, which is safe. Write one looser than USING, usually with check (true), and you've opened a door
Reads gate writesFor UPDATE, the SELECT policy is applied to the new row too. Loosen a read policy for a good reason, open a write hole you weren't looking at
SECURITY DEFINERA function marked this way runs as its owner, so the caller's policies don't apply inside it. Deliberate, necessary, and the easiest way to build a leak

Each of these is a way a policy can be wrong while reading as correct. Two of them are in a migration you'll read later today.

A Policy Runs Once Per Row​

Pawtograder's original helper. It looks like the obviously correct way to write this:

create function authorizeforclass(class__id bigint) returns boolean
  language plpgsql stable security definer
as $$
begin
  return exists (select 1 from user_roles r
                 where r.class_id = class__id and r.user_id = auth.uid());
end;
$$;

create policy "everyone in class can view" on gradebook_columns
  for select using (authorizeforclass(class_id));

One instructor opens one gradebook. 105,000 score rows come back.

pg_stat_user_functions says the function was called 105,000 times. Once per row.

The Measurement​

Same query, same 105,000 rows, same machine. Only the policy predicate changes.

Policy predicateTime
true (no check at all)23 ms
authorizeforclass(class_id), the plpgsql helper1,337 ms
the same helper rewritten in language sql1,779 ms
inline exists (select 1 from user_privileges ...)57 ms
class_id in (select ... from user_privileges ...)33 ms

Same rows out. Same security. 40x.

Rewriting the helper in plain SQL made it worse. Reaching for a "faster function" is the wrong move, because the problem was never which language the function was in.

Why the Function Version Loses​

A function call

Index Scan on gradebook_column_students
  (actual rows=105000 loops=1)
  Filter: authorizeforclass(class_id)

The planner sees a black box returning a boolean. It cannot look inside, so it calls it. 105,000 times.

An inline subquery

Index Scan on gradebook_column_students
  (actual rows=105000 loops=1)
  InitPlan 1
    Index Only Scan on user_privileges
      (actual rows=1 loops=1)

loops=1. The planner hoisted it out, ran it once, and compared 105,000 rows against the answer.

loops= in a query plan is the number you are looking for. loops=1 means once. Anything else means once per row above it.

Self-study. We won't cover this in the room.

Pawtograder Already Did This, Twice​

WhenWhat happened
Aug 2025Move the role check into the JWT to avoid the table lookup
Aug 2025Revert it. COUNT(*) becomes EXISTS, back to the table
Sep 2025Add user_privileges, a denormalized copy kept in sync by triggers
Sep 2025optimize-submission-rls.sql: 2,211 lines, rewriting policies across the app
2026perf(rls): inline user_privileges checks, drop authorize* helpers

"As RLS policy predicates they can't be inlined by the planner (a per-row function call against the larger user_roles table)."

Read the comments in that migration. They include the words EXPERIMENTAL, FINAL ATTEMPT, and a note about 1.7 ms.

Self-study. We won't cover this in the room.

Your Turn: gradebook_column_groups​

The student gradebook in Pawtograder for CS 4535. Collapsible headers read 4 Labs, 4 Assignments, 3 Exams, 2 Quizzes, 2 Quizzes again, and 12 Skills. Below them, Skills Meeting Expectations, Skills Approaching Expectations, Skills Not Meeting Expectations and Average HW sit outside any group.

Deliverable 1, due Thu Sep 24:

"A migration: schema for groups, and the RLS that goes with it. Groups describe student-visible data in one Postgres database holding ten courses' grades at once. The policies are part of the design, not paperwork you attach at the end."

That's the feature, rendered by a heuristic in the browser. Start where we started this morning: what is a column group, in the world?

  1. What does a group need to know about itself?
  2. Who may read one, and who may write one?
  3. What must never be true of one?
  4. What does your backfill see that your users don't?

Sketch It. No SQL.​

Seven minutes, in pairs. Fill in the four blanks in whatever notation you like.

THING  a column group
       what does one need to know about itself?
       ............................................

READ   who is allowed to see a group, and how do you
       decide that by looking at the group?
       ............................................

WRITE  who is allowed to create or change one?
       ............................................

NEVER  what must never be true of a group, no matter
       who is asking or what the app does?
       ............................................

The READ line is the one to argue about. Whatever you wrote, ask where the answer comes from.

Here's What Somebody Shipped​

create table gradebook_column_groups (
  id bigserial primary key,
  gradebook_id bigint not null references gradebooks (id),
  name text not null,
  sort_order integer
);
alter table gradebook_column_groups enable row level security;

create policy "everyone in class can view" on gradebook_column_groups
  for select using (gradebook_id in (select id from gradebooks));

create policy "instructors CRUD" on gradebook_column_groups
  for all using (authorizeforclassinstructor(
                   (select class_id from gradebooks where id = gradebook_id)))
  with check (true);

alter table gradebook_columns
  add column group_id bigint references gradebook_column_groups (id);

insert into gradebook_column_groups (gradebook_id, name, sort_order)
select distinct gradebook_id, split_part(slug, '-', 1), 0
from gradebook_columns;

It applies cleanly and CI is green. Hold it next to your sketch. Where do the two disagree?

Where the Class Comes From​

Most READ rules say some version of you can see a group if you're in its class. So: where does the group's class come from?

Walk to it

group -> gradebook -> class

Nothing is stored twice. The rule needs a join every time it runs.

Store it on the row

class_id on the group itself.

The rule is a local check. The class is now written down twice.

Every neighboring table already chose the second one: gradebook_columns, gradebook_column_students, gradebook_row_recalc_state.

Make the Broken Row Impossible​

The policy checks class_id. It says nothing about gradebook_id. So an instructor of class 1, entirely within their rights:

insert into gradebook_column_groups (class_id, gradebook_id, slug, name, sort_order)
values (1, 2, 'exam', 'Exams', 3);      -- class 1's group, class 2's gradebook

 id | class_id | gradebook_id
----+----------+--------------
 84 |        1 |            2

Both columns pass every check. The row is still nonsense, and it is now permanent.

alter table gradebooks add constraint gradebooks_id_class_key unique (id, class_id);

alter table gradebook_column_groups
  add foreign key (gradebook_id, class_id) references gradebooks (id, class_id);

ERROR:  insert or update on table "gradebook_column_groups"
        violates foreign key constraint

Your Backfill Doesn't See What Users See​

A migration runs as the table owner. The owner is exempt from row-level security.

So does Studio. The same query, same database, one dropdown apart:

Supabase Studio SQL editor. The query counts rows in gradebook_columns and returns 89. The role selector is open, offering postgres, Anonymous and Authenticated, with a note reading: the postgres role, which bypasses all Row Level Security policies.
The same Supabase Studio query re-run with the role selector set to authenticated and impersonating a seeded student account. The same count now returns 86 instead of 89.

RLS filters. It does not reject. "The gradebook is empty" and "you are not allowed" look identical.

Before You Open the PR​

  • Does every row carry the class_id its policy is checked against?
  • Does your policy reach through another table? Whose policy did you just inherit?
  • Does the write policy's WITH CHECK say the same thing as its USING?
  • Is there a constraint that makes a cross-class row impossible, not merely disallowed?
  • Did you EXPLAIN (ANALYZE, BUFFERS) the query the app sends?
  • Did you open the gradebook as a student, an instructor, and someone in neither?

Deliverable 6 is "what you tested against, and the down path." Answer this list in the PR and you have written most of it.

Self-study. We won't cover this in the room.

Why Any of This Is Law​

Pawtograder holds real coursework, real grades and real records for ten courses this term, including this one. Those are educational records protected under FERPA, a federal law.

  • You will never have access to production data. Not per-task, not read-only, not for debugging
  • You work against a local seed and against staging, which carries synthetic data only
  • A bug you can't reproduce on seeded data is still your bug

If you ever find yourself looking at real data: stop, and tell us the same day. Reporting is never penalized. Continuing to look is an integrity violation.

Key Takeaways​

  1. Nouns become tables. The many side carries the key. Say each fact once
  2. Then say one of them twice on purpose, name what it bought, and pay with a constraint
  3. A query's cost is pages touched. An index turned 224,450 into 6
  4. A policy is a predicate evaluated once per row, so its cost is rows times predicate
  5. A function call in a policy is a loop. 105,000 rows, 105,000 calls, 40x
  6. Your backfill runs without RLS. Test as a user, in another course

The database is the application. Today that stopped being a slogan and became a thing you have to write.

Column Groups: What You're Claiming​

One PR from your fork, final Thu Sep 24, 23:59. Six things in it, and a band you claim in the description.

BandWhat it takes
PassSchema, RLS, and a backfill that reproduces today's grouping, with the table reading groups from your data instead of splitting slugs
CreditYou found where the heuristic gets it wrong, your migration corrects it, and the PR carries the written list of what you found and how
DistinctionCreate, rename, reorder and delete groups; move columns between them. Reorder preserves membership
High DistinctionPart two of the writeup: a design for the CS 2100 case, the one this display can't express. No implementation required

Each band includes the one below. You claim one; I check whether the claim is true. An over-claim falls back to what you reached, it doesn't fail.

High Distinction needs no code. It's the design I'll build from in October, with your name on it.

Up Next​

Wed Sep 16: Architecture III: The Client

Today was the database half of this system. Wednesday is the other half: the 156,000 lines you actually type in, and the gradebook table you're all editing this week.

Before then:

  • The Ticket Hunt, due Wed Sep 16, 23:59
  • Project Bids, due Thu Sep 17, 23:59