Architecture & RLS Strategy

Design the database before you write code. Twelve tables, four relationships you must get right, and the RLS rule that protects everything.

This page is the most important in Project 3. Read it twice. The decisions you make here cascade through every module.

The core idea is simple: data belongs to a branch. Every row in every table — students, attendance, exams, marks, fees, circulars — carries the branch_id it belongs to. Row Level Security uses that one column to filter what every user can see.

Get this right and the rest of the project is straightforward. Get it wrong and you will rewrite half the database.

The 12 Core Tables

Drawn out, the schema looks like this:

                            ┌──────────────────┐
                            │ profiles         │  (1 row per user)
                            │   id (=auth.uid) │
                            │   email          │
                            │   full_name      │
                            │   role           │  super_admin | branch_admin | teacher | parent | student
                            │   branch_id      │  null for super_admin
                            └──────────────────┘
                                     │
                ┌────────────────────┼──────────────────────┐
                │                    │                      │
                ▼                    ▼                      ▼
       ┌──────────────┐     ┌──────────────┐       ┌──────────────┐
       │ branches     │     │ teachers     │       │ parents      │
       │   id         │     │   id (FK p)  │       │   id (FK p)  │
       │   name       │     │   branch_id  │       └──────────────┘
       │   city       │     └──────────────┘              │
       │   address    │           │                       │
       └──────────────┘           ▼                       ▼
              │             ┌──────────────┐       ┌──────────────┐
              │             │ class_sections│       │ students     │
              │             │   id         │ ◀──── │   id         │
              │             │   branch_id  │       │   parent_id  │
              │             │   grade      │       │   branch_id  │
              │             │   section    │       │   section_id │
              │             │   teacher_id │       │   roll_no    │
              │             └──────────────┘       │   full_name  │
              │                                    │   admission_no
              │                                    └──────────────┘
              │                                          │
              │  ┌──────────────────────────────────────┤
              │  │                                      │
              │  ▼                                      ▼
              │ ┌──────────────┐                ┌──────────────┐
              │ │ attendance   │                │ exam_marks   │
              │ │   id         │                │   id         │
              │ │   student_id │                │   student_id │
              │ │   date       │                │   exam_id    │
              │ │   status     │  present|absent|exam_id      │
              │ └──────────────┘                │   marks      │
              │                                 └──────────────┘
              │                                        │
              ▼                                        ▼
       ┌──────────────┐                         ┌──────────────┐
       │ fee_plans    │                         │ exams        │
       │   id         │                         │   id         │
       │   branch_id  │                         │   branch_id  │
       │   grade      │                         │   name       │
       │   amount     │                         │   subject    │
       │   due_date   │                         │   max_marks  │
       └──────────────┘                         │   date       │
              │                                 └──────────────┘
              ▼
       ┌──────────────┐                         ┌──────────────┐
       │ fee_payments │                         │ circulars    │
       │   id         │                         │   id         │
       │   student_id │                         │   branch_id  │
       │   plan_id    │                         │   subject    │
       │   amount     │                         │   body       │
       │   status     │  pending|paid|failed   │   audience   │  all|grade|section
       │   razorpay_  │                         │   grade?     │
       │     order_id │                         │   section_id?│
       └──────────────┘                         │   sent_at    │
                                                └──────────────┘

Every box (except profiles) has a branch_id. That column is the spine of the whole system.

The Single RLS Rule

Every table follows the same rule: a user can see a row iff their branch_id matches the row's branch_id, or they are a super admin.

In SQL, the basic pattern (repeated per table with minor variations):

create policy "branch_or_super" on public.<table>
  for all using (
    -- Super admin sees everything
    (select role from public.profiles where id = auth.uid()) = 'super_admin'
    OR
    -- Everyone else only sees rows in their assigned branch
    branch_id = (select branch_id from public.profiles where id = auth.uid())
  );

This is the spine. Once it's in place for every table, most of your RLS is done.

Per-persona refinements (a parent should only see their own child, not every student in the branch) add a second layer on specific tables. We cover those on the personas page.

Helper Functions

To avoid repeating (select role from public.profiles where id = auth.uid()) everywhere, create two reusable Postgres functions. Run this in the Supabase SQL editor:

-- True if the calling user is a super admin
create or replace function public.is_super_admin()
returns boolean
language sql
security definer
set search_path = public
as $$
  select role = 'super_admin' from public.profiles where id = auth.uid();
$$;
 
-- The calling user's branch_id (null for super admins)
create or replace function public.current_branch_id()
returns uuid
language sql
security definer
set search_path = public
as $$
  select branch_id from public.profiles where id = auth.uid();
$$;

Now policies become much shorter:

create policy "branch_or_super_select" on public.students
  for select using (
    public.is_super_admin() or branch_id = public.current_branch_id()
  );

The Order to Create Tables (Migration Order)

Because of foreign keys, you must create tables in this order. Use one migration per major group.

branches (no dependencies)
profiles (references branches via branch_id, but the column is nullable — super admins have no branch)
class_sections (references branches)
teachers (references profiles, branches)
Now update class_sections.teacher_id foreign key to point at teachers.id
parents (references profiles)
students (references parents, branches, class_sections)
attendance (references students)
exams (references branches)
exam_marks (references students, exams)
fee_plans (references branches)
fee_payments (references students, fee_plans)
circulars (references branches)

If you try to create students before parents, Supabase will error. The migration scripts go in supabase/migrations/ in numbered files (00001_branches.sql, 00002_profiles.sql, etc.).

Database Conventions

To keep the schema readable across 12 tables, every table follows the same conventions:

Primary key: id uuid primary key default gen_random_uuid()
Created/updated: created_at timestamptz not null default now() + a trigger to maintain updated_at
Soft delete: deleted_at timestamptz (null = active; never hard-delete)
Branch column: branch_id uuid not null references public.branches(id) on delete restrict (except profiles where it's nullable)
RLS enabled: every single table — no exceptions

You will write a small migration that adds the updated_at trigger function once, then reuse it on every table.

What This Schema Does NOT Have (Yet)

A few things you'll probably want eventually, but they're not in the core 12:

Subjects — for now exams have a subject text column. Later, normalise to a subjects table.
Academic year — for now exams and fees use a date column. For a real multi-year system, add an academic_year table.
Document uploads — student photos, exam papers. Use Supabase Storage; reference the file path in a column. We'll touch this in the Students module.
Audit log — every important action should be logged for the super admin to review. Add school_audit_log later.

Don't add these to your initial migration. They'll only slow you down. Add them when you actually need them.

The Permission Matrix (Preview)

You'll work out the full permission matrix on the next page (personas.mdx). But here's the executive summary:

Action	Super	Branch admin	Teacher	Parent	Student
Create / edit branches	✓	—	—	—	—
Create / edit student	✓	✓ (own branch)	—	—	—
Mark attendance	✓	✓	✓ (own sections)	—	—
Enter exam marks	✓	✓	✓ (own sections)	—	—
View own child's data	n/a	n/a	n/a	✓	—
View own data	n/a	n/a	n/a	n/a	✓
Pay fees	✓	✓	—	✓	—
Send circulars	✓	✓ (own branch)	—	—	—
Receive circulars	—	✓	✓	✓	✓

This matrix is the source-of-truth for every RLS policy you'll write.

The Three Things to Get Right Before Writing Any Code

The 12-table schema is migrated. You can select * from students and see an empty table.
is_super_admin() and current_branch_id() work. Test them in the SQL editor while logged in as different users.
The basic "branch_or_super" RLS policy is on every table. Test by signing in as a branch admin and querying — you should see only their branch.

When all three are true, the rest of Project 3 is "build features" — not "fight RLS."

The next page is personas.mdx — the full permission matrix and how each one logs in, what they see, and what they can do.

Next: Personas & permissions

Skip to: First module — Students

This page is the most important in Project 3. Read it twice. The decisions you make here cascade through every module.

Get this right and the rest of the project is straightforward. Get it wrong and you will rewrite half the database.

The 12 Core Tables

Drawn out, the schema looks like this:

                            ┌──────────────────┐
                            │ profiles         │  (1 row per user)
                            │   id (=auth.uid) │
                            │   email          │
                            │   full_name      │
                            │   role           │  super_admin | branch_admin | teacher | parent | student
                            │   branch_id      │  null for super_admin
                            └──────────────────┘
                                     │
                ┌────────────────────┼──────────────────────┐
                │                    │                      │
                ▼                    ▼                      ▼
       ┌──────────────┐     ┌──────────────┐       ┌──────────────┐
       │ branches     │     │ teachers     │       │ parents      │
       │   id         │     │   id (FK p)  │       │   id (FK p)  │
       │   name       │     │   branch_id  │       └──────────────┘
       │   city       │     └──────────────┘              │
       │   address    │           │                       │
       └──────────────┘           ▼                       ▼
              │             ┌──────────────┐       ┌──────────────┐
              │             │ class_sections│       │ students     │
              │             │   id         │ ◀──── │   id         │
              │             │   branch_id  │       │   parent_id  │
              │             │   grade      │       │   branch_id  │
              │             │   section    │       │   section_id │
              │             │   teacher_id │       │   roll_no    │
              │             └──────────────┘       │   full_name  │
              │                                    │   admission_no
              │                                    └──────────────┘
              │                                          │
              │  ┌──────────────────────────────────────┤
              │  │                                      │
              │  ▼                                      ▼
              │ ┌──────────────┐                ┌──────────────┐
              │ │ attendance   │                │ exam_marks   │
              │ │   id         │                │   id         │
              │ │   student_id │                │   student_id │
              │ │   date       │                │   exam_id    │
              │ │   status     │  present|absent|exam_id      │
              │ └──────────────┘                │   marks      │
              │                                 └──────────────┘
              │                                        │
              ▼                                        ▼
       ┌──────────────┐                         ┌──────────────┐
       │ fee_plans    │                         │ exams        │
       │   id         │                         │   id         │
       │   branch_id  │                         │   branch_id  │
       │   grade      │                         │   name       │
       │   amount     │                         │   subject    │
       │   due_date   │                         │   max_marks  │
       └──────────────┘                         │   date       │
              │                                 └──────────────┘
              ▼
       ┌──────────────┐                         ┌──────────────┐
       │ fee_payments │                         │ circulars    │
       │   id         │                         │   id         │
       │   student_id │                         │   branch_id  │
       │   plan_id    │                         │   subject    │
       │   amount     │                         │   body       │
       │   status     │  pending|paid|failed   │   audience   │  all|grade|section
       │   razorpay_  │                         │   grade?     │
       │     order_id │                         │   section_id?│
       └──────────────┘                         │   sent_at    │
                                                └──────────────┘

Every box (except profiles) has a branch_id. That column is the spine of the whole system.

The Single RLS Rule

Every table follows the same rule: a user can see a row iff their branch_id matches the row's branch_id, or they are a super admin.

In SQL, the basic pattern (repeated per table with minor variations):

create policy "branch_or_super" on public.<table>
  for all using (
    -- Super admin sees everything
    (select role from public.profiles where id = auth.uid()) = 'super_admin'
    OR
    -- Everyone else only sees rows in their assigned branch
    branch_id = (select branch_id from public.profiles where id = auth.uid())
  );

This is the spine. Once it's in place for every table, most of your RLS is done.

Per-persona refinements (a parent should only see their own child, not every student in the branch) add a second layer on specific tables. We cover those on the personas page.

Helper Functions

To avoid repeating (select role from public.profiles where id = auth.uid()) everywhere, create two reusable Postgres functions. Run this in the Supabase SQL editor:

-- True if the calling user is a super admin
create or replace function public.is_super_admin()
returns boolean
language sql
security definer
set search_path = public
as $$
  select role = 'super_admin' from public.profiles where id = auth.uid();
$$;
 
-- The calling user's branch_id (null for super admins)
create or replace function public.current_branch_id()
returns uuid
language sql
security definer
set search_path = public
as $$
  select branch_id from public.profiles where id = auth.uid();
$$;

Now policies become much shorter:

create policy "branch_or_super_select" on public.students
  for select using (
    public.is_super_admin() or branch_id = public.current_branch_id()
  );

The Order to Create Tables (Migration Order)

Because of foreign keys, you must create tables in this order. Use one migration per major group.

branches (no dependencies)
profiles (references branches via branch_id, but the column is nullable — super admins have no branch)
class_sections (references branches)
teachers (references profiles, branches)
Now update class_sections.teacher_id foreign key to point at teachers.id
parents (references profiles)
students (references parents, branches, class_sections)
attendance (references students)
exams (references branches)
exam_marks (references students, exams)
fee_plans (references branches)
fee_payments (references students, fee_plans)
circulars (references branches)

If you try to create students before parents, Supabase will error. The migration scripts go in supabase/migrations/ in numbered files (00001_branches.sql, 00002_profiles.sql, etc.).

Database Conventions

To keep the schema readable across 12 tables, every table follows the same conventions:

Primary key: id uuid primary key default gen_random_uuid()
Created/updated: created_at timestamptz not null default now() + a trigger to maintain updated_at
Soft delete: deleted_at timestamptz (null = active; never hard-delete)
Branch column: branch_id uuid not null references public.branches(id) on delete restrict (except profiles where it's nullable)
RLS enabled: every single table — no exceptions

You will write a small migration that adds the updated_at trigger function once, then reuse it on every table.

What This Schema Does NOT Have (Yet)

A few things you'll probably want eventually, but they're not in the core 12:

Subjects — for now exams have a subject text column. Later, normalise to a subjects table.
Academic year — for now exams and fees use a date column. For a real multi-year system, add an academic_year table.
Document uploads — student photos, exam papers. Use Supabase Storage; reference the file path in a column. We'll touch this in the Students module.
Audit log — every important action should be logged for the super admin to review. Add school_audit_log later.

Don't add these to your initial migration. They'll only slow you down. Add them when you actually need them.

The Permission Matrix (Preview)

You'll work out the full permission matrix on the next page (personas.mdx). But here's the executive summary:

Action	Super	Branch admin	Teacher	Parent	Student
Create / edit branches	✓	—	—	—	—
Create / edit student	✓	✓ (own branch)	—	—	—
Mark attendance	✓	✓	✓ (own sections)	—	—
Enter exam marks	✓	✓	✓ (own sections)	—	—
View own child's data	n/a	n/a	n/a	✓	—
View own data	n/a	n/a	n/a	n/a	✓
Pay fees	✓	✓	—	✓	—
Send circulars	✓	✓ (own branch)	—	—	—
Receive circulars	—	✓	✓	✓	✓

This matrix is the source-of-truth for every RLS policy you'll write.

The Three Things to Get Right Before Writing Any Code

The 12-table schema is migrated. You can select * from students and see an empty table.
is_super_admin() and current_branch_id() work. Test them in the SQL editor while logged in as different users.
The basic "branch_or_super" RLS policy is on every table. Test by signing in as a branch admin and querying — you should see only their branch.

When all three are true, the rest of Project 3 is "build features" — not "fight RLS."

The next page is personas.mdx — the full permission matrix and how each one logs in, what they see, and what they can do.

Architecture & RLS Strategy

The 12 Core Tables

The Single RLS Rule

Helper Functions

The Order to Create Tables (Migration Order)

Database Conventions

What This Schema Does NOT Have (Yet)

The Permission Matrix (Preview)

The Three Things to Get Right Before Writing Any Code

Next: Personas & permissions

Skip to: First module — Students

On this page

Architecture & RLS Strategy

The 12 Core Tables

The Single RLS Rule

Helper Functions

The Order to Create Tables (Migration Order)

Database Conventions

What This Schema Does NOT Have (Yet)

The Permission Matrix (Preview)

The Three Things to Get Right Before Writing Any Code

Next: Personas & permissions

Skip to: First module — Students

On this page