Build a School Assignment API with RBAC

Introduction

30 Second Summary

A school system serves people with very different responsibilities. One unchecked action could let a student rewrite an assignment or let a teacher alter another class.

In this project, you will build a school assignment REST API that gives each school role a distinct set of permissions. Your finished backend will enforce those permissions at the request layer and the database layer.

What You'll Build

You will demo an assignment service that rejects a student's write request while teachers and admins manage persistent classwork.

By the end of this project, you'll have:

• A working sign-in flow using Supabase Auth that returns a bearer token plus the user's school role.
• A persistent assignment workflow for creating and listing classwork. You can also update or delete saved assignments through REST endpoints.
• A visible role permission test that proves students cannot change school data. You can also show teachers managing their own assignments while admins manage every assignment.
• Secret Mission: Add a department head role that can update any assignment without gaining permission to delete one.

Are there any prerequisites?

You only need basic computer skills. JavaScript, Express, REST, and SQL are taught from the beginning.

Before We Start

This is your moment to commit to the school assignment API you are about to build. Naming why each school role needs different permissions gives the security work a clear purpose.

Set Up the Express Project

Your school API eventually protects assignment data with several permission levels. First, it needs a predictable local foundation that proves requests can reach the server.

A pinned Express starter removes version drift. In this step, Node.js runs a health endpoint before authentication or database work begins.

In this step, get ready to:

• Create the project folder structure with its configuration files.
• Install the pinned Express and Supabase JavaScript dependencies.
• Run the local server. Confirm that its health endpoint responds successfully.

Create the project structure

Visual Studio Code keeps your source code, scripts, and database files in one project folder. The three subfolders separate those responsibilities from the start.

• Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
• Type Visual Studio Code into the search bar.
• Press Enter to open Visual Studio Code.

Visual Studio Code opens to its main workspace. You can now give the API a dedicated home on your Desktop.

• Click File in the top menu.
• Click Open Folder.
• Select your Desktop in the folder picker.
• Click New Folder.
• Enter school-rbac-api as the folder name.
• Press Enter to create the folder.
• Select the new school-rbac-api folder.
• Click Select Folder to open it in Visual Studio Code.

Good start. The Explorer sidebar now shows an empty school-rbac-api folder.

• Create a src folder inside school-rbac-api with the New Folder control in the Explorer sidebar.
• Create a scripts folder inside school-rbac-api with the same control.
• Create a supabase folder inside school-rbac-api with the same control.

You should see src, scripts, and supabase listed directly inside school-rbac-api.

• Click the New File control in the Explorer sidebar.
• Name the file package.json.
• Fill package.json with the pinned project settings by pasting the code below:

{
  "name": "school-rbac-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env src/server.js"
  },
  "engines": {
    "node": ">=22.0.0"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.118.0",
    "express": "5.3.0"
  }
}

What does this configuration do?

• The name field identifies this project as school-rbac-api.
• The type field enables JavaScript module imports.
• The start script loads local environment values from .env before running src/server.js.
• The engines field records Node.js 22.0.0 as the minimum supported version.
• The dependencies field pins Express to 5.3.0 and the Supabase JavaScript client to 2.118.0.

• Save package.json with Cmd+S (macOS) or Ctrl+S (Windows).
• Confirm that package.json appears beside the three folders in the Explorer sidebar.

Is package.json showing a problem?

Check that every property except the final property in each object ends with a comma. Make sure every property name uses double quotes.

Compare the opening and closing braces with the code above. A missing brace makes the entire configuration invalid.

Help me find the formatting problem in my package.json file.

Installed packages can contain thousands of generated files. Local environment files can later hold sensitive settings. A .gitignore file keeps both categories out of version control.

• Click the New File control in the Explorer sidebar.
• Name the file .gitignore.
• Add the project exclusions by pasting the code below:

node_modules/
.env

What does this file exclude?

• The node_modules/ entry excludes installed dependency files.
• The .env entry excludes local environment values.

• Save .gitignore with Cmd+S (macOS) or Ctrl+S (Windows).
• Confirm that .gitignore appears beside package.json in the Explorer sidebar.

Are the exclusions missing?

Check that the filename starts with a full stop. Make sure node_modules/ and .env sit on separate lines.

Help me check my .gitignore file.

The start script expects an environment file to exist. An empty .env file satisfies that requirement until the Supabase settings are added.

• Create an empty .env file with the New File control in the Explorer sidebar.
• Save the empty .env file with Cmd+S (macOS) or Ctrl+S (Windows).

You should now see .env, .gitignore, package.json, and the three subfolders in the Explorer sidebar.

Install the pinned dependencies

Project dependencies provide code that your server can reuse. Exact version pins keep the same package behavior across different machines.

• Click Terminal in the Visual Studio Code menu.
• Click New Terminal to open the integrated PowerShell panel.

The PowerShell prompt should show a path ending in school-rbac-api. That location ensures npm installs the packages into this project.

• Install the pinned dependencies by running this command:

npm install express@5.3.0 @supabase/supabase-js@2.118.0 --save-exact

What does this command install?

• The express@5.3.0 package supplies the web server and routing features.
• The @supabase/supabase-js@2.118.0 package supplies the database and authentication client used later.
• The --save-exact flag preserves both exact versions in package.json.

• Confirm that PowerShell returns to its prompt without reporting an installation failure.
• Confirm that a node_modules folder now appears in the Explorer sidebar.

That is the dependency setup complete. Your project now has the exact Express and Supabase JavaScript packages expected by the code.

Did the dependency installation fail?

Check that the PowerShell prompt points to school-rbac-api. Confirm that package.json is saved before trying the command again.

A network or package registry problem can also interrupt the download. Retry the same command after your connection is stable.

Help me troubleshoot my npm dependency installation.

Build and test the health endpoint

A health endpoint gives you a small request that checks whether the server can receive traffic. It isolates the local server setup from the authentication and database work that comes later.

• Select the src folder in the Explorer sidebar.
• Click the New File control.
• Name the file server.js.
• Build the starter server by pasting the code below into src/server.js.

import express from 'express'

const app = express()
const port = 3000

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

What does this server code do?

• The express import loads the framework used to create the server.
• The app value holds the Express application. The port value tells it to listen on 3000.
• The express.json() middleware prepares the server to read JSON request bodies.
• The GET /health route returns a JSON response with status set to ok.
• The app.listen() call starts the server and prints its local address.

• Save src/server.js with Cmd+S (macOS) or Ctrl+S (Windows).

Before you start the server, do you expect PowerShell to return to its prompt or remain occupied while the server listens for requests?

• Start the Express server by running this command in the PowerShell terminal:

npm start

What happens when the server starts?

npm runs the start script from package.json. Node.js loads the empty .env file before starting src/server.js.

The terminal remains occupied while the server listens on port 3000. Press Ctrl+C when you eventually want to stop the process.

You should see School RBAC API running at http://localhost:3000 in the terminal. Your Express process is now ready to receive a request.

• Leave the first PowerShell terminal running.
• Click the split-terminal control in the terminal panel to open a second PowerShell terminal.

Before you send the health request, what JSON status do you expect the running server to return?

• Check the health endpoint from the second PowerShell terminal by running this command:

Invoke-RestMethod -Method Get -Uri http://localhost:3000/health

What does this request check?

PowerShell sends a GET request to the local /health route. Express converts the route's JSON response into an object that PowerShell can display.

A successful response proves that the Node.js process is listening on port 3000 and that Express matched the route.

You should see status set to ok. That response confirms your local API foundation works from request to response.

Did the health check fail?

Confirm that the first terminal still shows the running server. Check that the second terminal uses port 3000 in the request.

If the server stopped during startup, compare src/server.js and package.json with the full files below. Save both files before starting the server again.

Help me get the Express health endpoint working.

✔️ Awesome, I've got everything!

Great work. Your pinned Express server is running, and the health request returns ok.

ⓧ I'd like to double check the full code

Compare your three project files with these complete versions. Keep every filename and value exactly as shown.

node_modules/
.env

{
  "name": "school-rbac-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env src/server.js"
  },
  "engines": {
    "node": ">=22.0.0"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.118.0",
    "express": "5.3.0"
  }
}

import express from 'express'

const app = express()
const port = 3000

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

Your local Express foundation is running with pinned dependencies. Next, you will turn this health-only server into an assignment API that accepts real requests.

Build an Open Assignment API

Your Express server already proves the project can answer a health request. The school backend now needs its first assignment workflow.

A REST API connects request methods to assignment actions. You'll begin with memory storage so you can see each request change the data immediately.

In this step, get ready to:

• Store new assignments in a server-side array.
• Expose public assignment routes.
• Test an anonymous write from PowerShell across a server restart.

Add the in-memory assignment routes

The assignments array gives the API a temporary place to hold each assignment. The routes read from this array or add a new object to it.

• Select src/server.js in the VS Code Explorer sidebar.
• Find the line containing const port = 3000.
• Add the assignment array directly below that line by copying this code:

const assignments = []

What does this line do?

The assignments array holds every assignment created while this server process is running. Each route uses the same array reference.

• Find the closing }) for the /health route.
• Add the assignment routes directly below the health route by copying this code:

app.get('/assignments', (req, res) => {
  res.json(assignments)
})

app.post('/assignments', (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const assignment = {
    id: assignments.length + 1,
    title,
    description,
    class_name,
    due_date
  }

  assignments.push(assignment)
  res.status(201).json(assignment)
})

What do these routes do?

• The GET /assignments route returns the current array as JSON.
• The POST /assignments route reads assignment fields from the JSON request body.
• The required-field check rejects a request that omits title, class_name, or due_date.
• The new object receives an id based on the array length.
• The route stores the object before returning it with status 201.

• Save src/server.js with Ctrl+S.
• Switch back to the PowerShell terminal running the server.
• Press Ctrl+C to stop the current server process.
• Start the updated server by running this command:

npm start

What should happen?

The command runs the start script from package.json. You should see School RBAC API running at http://localhost:3000 before the terminal stays busy serving requests.

• Press the Windows key to open Windows search.
• Type the name of your web browser into the search field.
• Press Enter to open the browser.
• Enter http://localhost:3000/assignments in the address bar.

You'll see [] because no assignments exist yet. That first route is working: your server can return the current assignment collection.

Assignment route not loading?

Confirm the server terminal still shows the startup message. Check that both assignment routes sit above app.listen in src/server.js.

Make sure the browser address uses port 3000 and ends with /assignments.

Ask for help with debugging the assignment routes.

✔️ Awesome, I've got everything!

Great. Save src/server.js and leave the updated server running.

ⓧ I'd like to double check the full code

Compare your complete src/server.js file with this reference.

import express from 'express'

const app = express()
const port = 3000
const assignments = []

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.get('/assignments', (req, res) => {
  res.json(assignments)
})

app.post('/assignments', (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const assignment = {
    id: assignments.length + 1,
    title,
    description,
    class_name,
    due_date
  }

  assignments.push(assignment)
  res.status(201).json(assignment)
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

How is this file organized?

The file creates the Express app before registering JSON middleware. It then defines the health route and both assignment routes before starting the listener on port 3000.

Prove the API accepts anonymous writes

A bearer token identifies a signed-in caller. Your next request intentionally sends no token so you can inspect the route's current behavior.

• Press the Windows key to open Windows search.
• Type PowerShell into the search field.
• Press Enter to open a second PowerShell window.

Before you send the request, do you think the API accepts it or rejects it without a signed-in user?

• Send an assignment without an authorization header by running this request:

$body = @{ title = 'Fractions worksheet'; class_name = 'Math 7'; due_date = '2026-11-06' } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -ContentType 'application/json' -Body $body

What does this request send?

The first line converts the assignment fields into JSON. The second line sends that JSON to POST /assignments.

The request has no -Headers argument. No bearer token identifies the caller.

You'll see the created assignment with id set to 1. The API accepted a school-data write from an anonymous caller.

Before you check the list, do you think the anonymous assignment now appears in the public collection?

• Switch back to the browser showing http://localhost:3000/assignments.
• Refresh the browser page.

You'll see a JSON array containing the Fractions worksheet assignment. This proves the public write changed data that any caller can list.

Now test whether the assignment survives a server restart. This check distinguishes temporary storage from persistent storage.

• Switch back to the PowerShell terminal running the server.
• Press Ctrl+C to stop the server process.
• Restart the server by running this command:

npm start

What did the restart change?

The command launches a fresh Node.js process. The updated routes remain available because they come from src/server.js.

Before you refresh the assignment list, what do you expect the fresh server process to return?

• Switch back to the browser showing http://localhost:3000/assignments.
• Refresh the browser page.

You'll see [] again. Restarting the server recreated the in-memory array without the assignment.

Still seeing the assignment after restarting?

Confirm you stopped the terminal process that displayed the server startup message. A second process may still be serving the earlier data.

Refresh the exact /assignments address after the new startup message appears.

Ask for help with checking which server process is still running.

You've built a working assignment API and exposed its two limits: callers have no identity checks and restarts erase the data. Next, you'll connect Supabase so the API can recognize school users and retain assignments.

Connect Supabase Auth and Seed Users

Your public Express API accepts assignment changes from anyone. It also forgets every assignment when the server restarts.

In this step, Supabase Auth gives each caller a trusted identity. Postgres stores school roles in tables protected by Row Level Security.

In this step, get ready to:

• Create a Supabase Free project with local environment variables.
• Create the profiles and assignments tables with initial security policies.
• Seed three school users with a working login endpoint.

Create your Supabase project and store its keys

A Supabase project provides the hosted database and trusted user accounts for your API. Your local environment file connects the Express server to that specific project.

• Open the Supabase dashboard in your browser.
• Start the dashboard's New project flow.
• Enter school-rbac-api as the project name.
• Choose the Free plan.
• Select the available region closest to you.
• Enter a strong database password that you do not use elsewhere.
• Submit the project creation form.

Provisioning may take a few minutes. The dashboard shows your new project when it is ready.

Good progress. Your hosted database now has a permanent home while the API continues running on your computer.

• Return to the school-rbac-api workspace in VS Code.
• Create .env.example inside the school-rbac-api folder with the new-file button in the Explorer sidebar.
• Paste this environment variable template into .env.example:

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-publishable-key-here
SUPABASE_SECRET_KEY=your-secret-key-here

What does this configuration do?

• The SUPABASE_URL value tells the client which Supabase project receives each request.
• The SUPABASE_PUBLISHABLE_KEY value identifies your project while keeping database access constrained by RLS.
• The SUPABASE_SECRET_KEY value gives the trusted seed script elevated access to create users.

• Save .env.example.
• Confirm that .env.example appears beside package.json in the Explorer sidebar.

Missing the environment template?

Check that the file is inside school-rbac-api. Confirm that its name ends with .example.

Ask for help if the file appears in another folder. Help me place .env.example in the correct VS Code folder.

Keep the secret key local

The secret key bypasses RLS. Your existing .gitignore excludes .env so the key stays outside version control.

Never paste the secret key into src/server.js or share a screenshot that reveals it.

• Create .env inside the school-rbac-api folder with the new-file button in the Explorer sidebar.
• Copy the three template lines from .env.example into .env.
• Return to your Supabase project in the browser.
• Click Connect at the top of the project page.
• Copy the project URL.
• Replace the placeholder after SUPABASE_URL= in .env.
• Return to the Connect dialog.
• Copy the publishable key.

Why are there two keys?

The publishable key remains subject to RLS. The API combines it with each caller's access token so database policies can identify that user.

The secret key has elevated access. Only the trusted local seed script uses it to create the three demo accounts.

• Replace the placeholder after SUPABASE_PUBLISHABLE_KEY= in .env.
• Select Settings in the Supabase sidebar.
• Select API Keys.
• Copy the secret key.
• Replace the placeholder after SUPABASE_SECRET_KEY= in .env.
• Save .env.
• Confirm that .env remains listed in .gitignore.

Cannot find a project key?

Use the Connect dialog for the project URL and publishable key. Use API Keys under Settings for the secret key.

Keep any copied key out of chat messages. Help me find the Supabase project URL and keys without exposing their values.

Create the database schema and initial policies

The database needs one table for school roles and another for assignments. Grants define which operations authenticated users can attempt.

RLS policies decide which rows each signed-in user can access. This keeps authorization attached to the data boundary.

• Create 01_schema.sql inside the existing supabase folder with the new-file button in the Explorer sidebar.
• Paste the table definitions and database privileges into supabase/01_schema.sql:

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  full_name text not null,
  role text not null constraint profiles_role_check
    check (role in ('admin', 'teacher', 'student'))
);

create table public.assignments (
  id bigint generated by default as identity primary key,
  title text not null,
  description text not null default '',
  class_name text not null,
  due_date date not null,
  teacher_id uuid not null references auth.users (id) on delete cascade,
  created_at timestamptz not null default now()
);

create index assignments_teacher_id_idx
on public.assignments using btree (teacher_id);

alter table public.profiles enable row level security;
alter table public.assignments enable row level security;

revoke all on table public.profiles from anon, authenticated;
revoke all on table public.assignments from anon, authenticated;

grant select on table public.profiles to authenticated;
grant select, insert, update, delete on table public.assignments to authenticated;

What does this schema create?

• The profiles table connects every Auth user to one school role.
• The assignments table stores persistent assignment details and the owning teacher ID.
• The index helps policies find rows by teacher_id.
• The grants expose only the required table operations to authenticated users.

• Save supabase/01_schema.sql.
• Confirm that 01_schema.sql appears inside the supabase folder in the Explorer sidebar.

Schema file in the wrong folder?

Drag 01_schema.sql into the existing supabase folder in the Explorer sidebar. The final path must read supabase/01_schema.sql.

Ask for help if VS Code shows a different path. Help me place 01_schema.sql inside the supabase folder.

• Add the initial RLS policies below the existing grants in supabase/01_schema.sql:

create policy "Users read their own profile"
on public.profiles for select
to authenticated
using ((select auth.uid()) = id);

create policy "Authenticated users read assignments"
on public.assignments for select
to authenticated
using (true);

create policy "Initial authenticated insert"
on public.assignments for insert
to authenticated
with check ((select auth.uid()) = teacher_id);

create policy "Initial owner update"
on public.assignments for update
to authenticated
using ((select auth.uid()) = teacher_id)
with check ((select auth.uid()) = teacher_id);

create policy "Initial owner delete"
on public.assignments for delete
to authenticated
using ((select auth.uid()) = teacher_id);

How do the initial policies work?

• A signed-in user can read only their own row from profiles.
• Every signed-in user can read assignments.
• Write operations require teacher_id to match the authenticated user ID.
• These policies establish ownership checks before the role rules become stricter.

• Save supabase/01_schema.sql.
• Open your Supabase project in the browser.
• Select SQL Editor in the sidebar.
• Click New Query.
• Return to supabase/01_schema.sql in VS Code.
• Press Ctrl+A to select the full SQL file.
• Press Ctrl+C to copy the selected SQL.
• Paste the SQL into the new query.

Before you run the query, picture whether Supabase should report a successful schema change or a failed statement.

• Click Run in the SQL Editor.

You should see the query complete successfully. This confirms that the two tables and their initial RLS policies exist.

SQL query did not complete?

Confirm that you copied the full contents of supabase/01_schema.sql. A partial policy or missing closing parenthesis prevents the query from completing.

Avoid running the schema repeatedly after it succeeds because the tables already exist. Help me diagnose my Supabase schema query without changing the intended tables or policies.

Seed users and add the login endpoint

The schema can store roles once matching Auth users exist. A trusted seed script creates confirmed accounts with the secret key.

The API then signs in through the publishable client. A successful response returns the caller's token and trusted profile role.

• Create seed-users.js inside the existing scripts folder with the new-file button in the Explorer sidebar.
• Paste the seed configuration and account list into scripts/seed-users.js:

import { createClient } from '@supabase/supabase-js'

const password = process.argv[2]
const supabaseUrl = process.env.SUPABASE_URL
const supabaseSecretKey = process.env.SUPABASE_SECRET_KEY

if (!password) {
  throw new Error('Pass a demo password as the first argument')
}

if (!supabaseUrl || !supabaseSecretKey) {
  throw new Error('SUPABASE_URL and SUPABASE_SECRET_KEY are required')
}

const adminClient = createClient(supabaseUrl, supabaseSecretKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false
  }
})

const accounts = [
  { email: 'admin@example.com', full_name: 'Avery Admin', role: 'admin' },
  { email: 'teacher@example.com', full_name: 'Taylor Teacher', role: 'teacher' },
  { email: 'student@example.com', full_name: 'Sam Student', role: 'student' }
]

What does this seed configuration do?

• The script reads the demo password from its first command argument.
• The secret key creates an admin client that can manage Auth users from this trusted local script.
• The accounts array defines one admin, one teacher, and one student.

• Save scripts/seed-users.js.
• Confirm that seed-users.js appears inside the scripts folder in the Explorer sidebar.

Seed file missing from scripts?

Confirm that the scripts folder is expanded in the Explorer sidebar. The file path must read scripts/seed-users.js.

Ask for help if the path differs. Help me place seed-users.js inside the scripts folder.

• Add the account creation loop below the accounts array in scripts/seed-users.js:

for (const account of accounts) {
  const { data, error } = await adminClient.auth.admin.createUser({
    email: account.email,
    password,
    email_confirm: true
  })

  if (error || !data.user) {
    throw new Error(`Could not create ${account.email}: ${error?.message}`)
  }

  const { error: profileError } = await adminClient.from('profiles').insert({
    id: data.user.id,
    full_name: account.full_name,
    role: account.role
  })

  if (profileError) {
    throw new Error(`Could not create profile for ${account.email}: ${profileError.message}`)
  }

  console.log(`Created ${account.email} as ${account.role}`)
}

How does the seed loop connect Auth and roles?

• The admin API creates a confirmed Auth user for each account.
• The returned user ID becomes the primary key of that user's profiles row.
• The profile stores the corresponding admin, teacher, or student role.

• Save scripts/seed-users.js.
• Create a second PowerShell terminal with the plus button in VS Code's Terminal panel.
• Replace your-demo-password with a password used only for these demo accounts.
• Create the three users by running this command once:

node --env-file=.env scripts/seed-users.js "your-demo-password"

What does this command do?

Node loads the Supabase values from .env. The script passes your demo password to each account creation request.

Run this seed script once. Repeating it targets email addresses that already exist.

You should see Created admin@example.com as admin, Created teacher@example.com as teacher, and Created student@example.com as student on separate lines.

That is a major security building block complete. Your database now has three confirmed identities tied to three distinct school roles.

Seed script stopped early?

Confirm that all three values in .env contain values from the same Supabase project. Keep the values private while checking them.

If the output indicates that the email addresses already exist, do not rerun the script. Help me diagnose my user seed script without asking me to reveal any keys or passwords.

• Create supabase.js inside the existing src folder with the new-file button in the Explorer sidebar.
• Paste the server-side authentication client into src/supabase.js:

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabasePublishableKey = process.env.SUPABASE_PUBLISHABLE_KEY

if (!supabaseUrl || !supabasePublishableKey) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const serverAuthOptions = {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false
  }
}

export const authClient = createClient(
  supabaseUrl,
  supabasePublishableKey,
  serverAuthOptions
)

What does the authentication client do?

• The file reads the project URL and publishable key from .env.
• The server options disable browser-style session storage and automatic token refresh.
• The exported authClient handles password sign-in from the API.

• Save src/supabase.js.
• Confirm that supabase.js appears inside the src folder in the Explorer sidebar.
• In src/server.js, replace the existing Express import with this pair:

import express from 'express'
import { authClient } from './supabase.js'

Why import authClient?

The existing Express import still creates the API. The new authClient import gives the login route access to Supabase Auth.

• Save src/server.js.
• Press Ctrl+C in the original PowerShell terminal to stop the running server.
• Restart the server with its updated environment configuration by running:

npm start

What does this command do?

The command runs the start script from package.json. Node loads .env before importing src/server.js.

You should see School RBAC API running at http://localhost:3000. This proves the publishable Supabase client loaded successfully.

Server no longer starts?

Check that .env contains SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY. Confirm that neither value still contains its example placeholder.

Confirm that src/server.js imports ./supabase.js with the leading dot. Help me diagnose why npm start fails after importing authClient.

• Append the user-scoped client function to src/supabase.js:

export function createUserClient(accessToken) {
  return createClient(supabaseUrl, supabasePublishableKey, {
    ...serverAuthOptions,
    global: {
      headers: {
        Authorization: `Bearer ${accessToken}`
      }
    }
  })
}

Why create a client for each user?

The createUserClient() function adds the caller's bearer token to database requests. Supabase can then apply RLS as that specific user.

The next step uses this function when assignment routes move from memory into Postgres.

• Save src/supabase.js.
• In src/server.js, add this credential-checking route below the /health route:

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' })
  }

  const { data, error } = await authClient.auth.signInWithPassword({
    email,
    password
  })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Login failed' })
  }
})

How does the route verify credentials?

• The route reads email and password from the JSON body.
• A missing field returns HTTP 400.
• Supabase Auth checks the supplied credentials.
• A rejected sign-in returns HTTP 401.

• Save src/server.js.
• Press Ctrl+C in the original PowerShell terminal to stop the server.
• Confirm that the credential-checking route loads by running:

npm start

What does this check prove?

The startup message confirms that the new asynchronous route has valid syntax. It also confirms that Express can import the Supabase client.

You should see School RBAC API running at http://localhost:3000 again.

Seeing a startup syntax error?

Check the closing braces around signInWithPassword() and the final }) of the route. Confirm that the route sits above GET /assignments.

Ask for help with the route structure if the server still stops. Help me compare my login route with the required structure.

• In the new /auth/login route, find the final closing }) directly above app.get('/assignments'.
• Replace that closing line with the profile lookup and completed response below:

  const { data: profiles, error: profileError } = await authClient
    .from('profiles')
    .select('role')
    .eq('id', data.user.id)
    .setHeader('Authorization', `Bearer ${data.session.access_token}`)

  if (profileError || !profiles?.[0]) {
    return res.status(403).json({ error: 'No school role is assigned' })
  }

  res.json({
    access_token: data.session.access_token,
    email: data.user.email,
    role: profiles[0].role
  })
})

How does the API return a trusted role?

• The profile query filters by the authenticated user's ID.
• The access token places that query in the caller's RLS context.
• A missing profile returns HTTP 403.
• A successful response returns the access token, email address, and stored school role.

• Save src/server.js.
• Press Ctrl+C in the original PowerShell terminal to stop the server.
• Start the completed login API by running:

npm start

What does this restart load?

The restarted process loads the completed login route. It keeps the public assignment routes and in-memory assignments array unchanged.

You should see School RBAC API running at http://localhost:3000 in the original terminal.

• In the second PowerShell terminal, replace your-demo-password with the password used for the seeded accounts.

Before you send the request, picture which school role the response should identify.

• Sign in as the student by running:

$login = @{ email = 'student@example.com'; password = 'your-demo-password' } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $login

What does this request do?

PowerShell converts the student's credentials into JSON. It sends that JSON to POST /auth/login.

The API asks Supabase Auth to verify the credentials. It then loads the matching profile through RLS.

You should see a response containing access_token, email, and role. The email value should be student@example.com. The role value should be student.

Excellent work. Your API can now verify a real user and return a role stored behind database security.

Login request failed?

Confirm that the email is student@example.com. Use the same demo password that you passed to the seed script.

Check that the server was restarted after you completed the login route. Help me diagnose my student login without asking me to share the password or access token.

Use this checkpoint to compare every non-secret project file created or changed in this step.

✔️ Awesome, I've got everything!

Great. Keep .env private and leave the Express server running for the next step.

ⓧ I'd like to double check the full code

Compare each file below with your workspace. Your private .env should use the same variable names as .env.example with values from your Supabase project.

node_modules/
.env

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-publishable-key-here
SUPABASE_SECRET_KEY=your-secret-key-here

{
  "name": "school-rbac-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env src/server.js"
  },
  "engines": {
    "node": ">=22.0.0"
  },
  "dependencies": {
    "@supabase/supabase-js": "2.118.0",
    "express": "5.3.0"
  }
}

import { createClient } from '@supabase/supabase-js'

const password = process.argv[2]
const supabaseUrl = process.env.SUPABASE_URL
const supabaseSecretKey = process.env.SUPABASE_SECRET_KEY

if (!password) {
  throw new Error('Pass a demo password as the first argument')
}

if (!supabaseUrl || !supabaseSecretKey) {
  throw new Error('SUPABASE_URL and SUPABASE_SECRET_KEY are required')
}

const adminClient = createClient(supabaseUrl, supabaseSecretKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false
  }
})

const accounts = [
  { email: 'admin@example.com', full_name: 'Avery Admin', role: 'admin' },
  { email: 'teacher@example.com', full_name: 'Taylor Teacher', role: 'teacher' },
  { email: 'student@example.com', full_name: 'Sam Student', role: 'student' }
]

for (const account of accounts) {
  const { data, error } = await adminClient.auth.admin.createUser({
    email: account.email,
    password,
    email_confirm: true
  })

  if (error || !data.user) {
    throw new Error(`Could not create ${account.email}: ${error?.message}`)
  }

  const { error: profileError } = await adminClient.from('profiles').insert({
    id: data.user.id,
    full_name: account.full_name,
    role: account.role
  })

  if (profileError) {
    throw new Error(`Could not create profile for ${account.email}: ${profileError.message}`)
  }

  console.log(`Created ${account.email} as ${account.role}`)
}

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabasePublishableKey = process.env.SUPABASE_PUBLISHABLE_KEY

if (!supabaseUrl || !supabasePublishableKey) {
  throw new Error('SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY are required')
}

const serverAuthOptions = {
  auth: {
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false
  }
}

export const authClient = createClient(
  supabaseUrl,
  supabasePublishableKey,
  serverAuthOptions
)

export function createUserClient(accessToken) {
  return createClient(supabaseUrl, supabasePublishableKey, {
    ...serverAuthOptions,
    global: {
      headers: {
        Authorization: `Bearer ${accessToken}`
      }
    }
  })
}

import express from 'express'
import { authClient } from './supabase.js'

const app = express()
const port = 3000
const assignments = []

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' })
  }

  const { data, error } = await authClient.auth.signInWithPassword({
    email,
    password
  })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Login failed' })
  }

  const { data: profiles, error: profileError } = await authClient
    .from('profiles')
    .select('role')
    .eq('id', data.user.id)
    .setHeader('Authorization', `Bearer ${data.session.access_token}`)

  if (profileError || !profiles?.[0]) {
    return res.status(403).json({ error: 'No school role is assigned' })
  }

  res.json({
    access_token: data.session.access_token,
    email: data.user.email,
    role: profiles[0].role
  })
})

app.get('/assignments', (req, res) => {
  res.json(assignments)
})

app.post('/assignments', (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const assignment = {
    id: assignments.length + 1,
    title,
    description,
    class_name,
    due_date
  }

  assignments.push(assignment)
  res.status(201).json(assignment)
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  full_name text not null,
  role text not null constraint profiles_role_check
    check (role in ('admin', 'teacher', 'student'))
);

create table public.assignments (
  id bigint generated by default as identity primary key,
  title text not null,
  description text not null default '',
  class_name text not null,
  due_date date not null,
  teacher_id uuid not null references auth.users (id) on delete cascade,
  created_at timestamptz not null default now()
);

create index assignments_teacher_id_idx
on public.assignments using btree (teacher_id);

alter table public.profiles enable row level security;
alter table public.assignments enable row level security;

revoke all on table public.profiles from anon, authenticated;
revoke all on table public.assignments from anon, authenticated;

grant select on table public.profiles to authenticated;
grant select, insert, update, delete on table public.assignments to authenticated;

create policy "Users read their own profile"
on public.profiles for select
to authenticated
using ((select auth.uid()) = id);

create policy "Authenticated users read assignments"
on public.assignments for select
to authenticated
using (true);

create policy "Initial authenticated insert"
on public.assignments for insert
to authenticated
with check ((select auth.uid()) = teacher_id);

create policy "Initial owner update"
on public.assignments for update
to authenticated
using ((select auth.uid()) = teacher_id)
with check ((select auth.uid()) = teacher_id);

create policy "Initial owner delete"
on public.assignments for delete
to authenticated
using ((select auth.uid()) = teacher_id);

Your API now recognizes seeded school users while the assignment routes remain open and in memory. Next up, you'll require bearer tokens and move assignments into Supabase.

Require Authentication and Persist Assignments

Your three school accounts can now sign in through Supabase. The assignment routes still accept anonymous requests.

This step adds authentication with bearer tokens. It also moves assignments from temporary memory into the database so they survive server restarts.

In this step, get ready to:

• Verify each bearer token before an assignment route runs.
• Store assignment records in the Supabase database.
• Demonstrate that an authenticated student can still create an assignment.

Build the authentication middleware

A middleware function runs between an incoming request and its route handler. Your middleware rejects missing tokens before protected assignment code can run.

• In the VS Code Explorer sidebar from earlier, right-click the src folder.
• Select New File.
• Enter auth.js as the file name.
• Paste this request boundary into src/auth.js.

import { authClient, createUserClient } from './supabase.js'

export async function authenticate(req, res, next) {
  try {
    const authorization = req.headers.authorization

    if (!authorization?.startsWith('Bearer ')) {
      return res.status(401).json({ error: 'Missing bearer token' })
    }
  } catch (error) {
    next(error)
  }
}

What does this code do?

• The import gives the middleware access to the shared authentication client and the user-scoped client factory.
• The authorization check requires the request header to begin with the expected bearer token prefix.
• The catch block forwards unexpected failures to Express error handling.

• Save src/auth.js with Ctrl+S.
• Switch to the second PowerShell terminal from earlier.
• Check the new file for JavaScript syntax errors by running:

node --check src/auth.js

What does this check do?

The Node.js syntax checker reads src/auth.js without starting your API. PowerShell returns to the prompt when the file has valid syntax.

Good progress. Your middleware now has a valid request boundary that can reject a missing bearer token.

Seeing a syntax error?

Check that the closing braces in src/auth.js match the code block. Confirm that the import path ends with ./supabase.js.

Ask for help with the middleware syntax.

The next part validates the token with the Auth server. It then loads the caller's role through a user-scoped Supabase client so Row Level Security evaluates requests as that caller.

• In src/auth.js, place the cursor after the missing-token if block.
• Add the following code before the catch block:

    const accessToken = authorization.slice(7)
    const {
      data: { user },
      error: userError
    } = await authClient.auth.getUser(accessToken)

    if (userError || !user) {
      return res.status(401).json({ error: 'Invalid or expired token' })
    }

    const userClient = createUserClient(accessToken)
    const { data: profiles, error: profileError } = await userClient
      .from('profiles')
      .select('role')
      .eq('id', user.id)

    if (profileError) {
      return res.status(500).json({ error: profileError.message })
    }

    if (!profiles?.[0]) {
      return res.status(403).json({ error: 'No school role is assigned' })
    }

    req.user = user
    req.role = profiles[0].role
    req.supabase = userClient
    next()

How does authentication flow?

• The middleware removes the bearer prefix so accessToken contains only the token.
• The auth.getUser() request verifies the token with Supabase Auth.
• The user-scoped client reads the matching role from profiles under the caller's database permissions.
• The request receives the verified user, role, and scoped database client before next() continues to the route.

• Save src/auth.js.
• Check the completed middleware for syntax errors by running:

node --check src/auth.js

What confirms success?

PowerShell returns to the prompt without reporting a syntax error. The completed middleware is ready to protect assignment routes.

Does the syntax check fail now?

Confirm that the new code sits inside the try block. Check that next() appears immediately before the catch block.

Help me find the syntax problem in my completed authentication middleware.

Protect assignment reads

The middleware only changes a request when a route includes it. You will connect it to the assignment list while replacing the in-memory response with a database query.

• In src/server.js, find this block at the top of the file:

import express from 'express'
import { authClient } from './supabase.js'

const app = express()
const port = 3000
const assignments = []

What are you replacing?

This reference shows the current imports and the temporary assignments array. That array loses every assignment whenever the server stops.

• Replace that block with the authenticated version below:

import express from 'express'
import { authenticate } from './auth.js'
import { authClient } from './supabase.js'

const app = express()
const port = 3000

What changes here?

The server imports authenticate for protected routes. Removing the array prepares the API to use persistent rows.

• In src/server.js, find the current assignment list route:

app.get('/assignments', (req, res) => {
  res.json(assignments)
})

Why is this route public?

This route has no middleware between its path and handler. Any caller can currently read the in-memory list.

• Replace the public list route with this protected database route:

app.get('/assignments', authenticate, async (req, res) => {
  const { data, error } = await req.supabase.from('assignments').select()

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.json(data)
})

How does the protected route work?

• Express runs authenticate before the async route handler.
• The route queries assignments through the caller's scoped client.
• The active select policy allows authenticated users to receive assignment rows.

• Save src/server.js.
• Switch back to the PowerShell terminal running the server.
• Press Ctrl+C to stop the current process.
• Restart the API with the updated code by running:

npm start

What happens when the server restarts?

The start script loads your environment variables before running src/server.js. The terminal remains occupied while the API listens for requests.

Before you test the route, predict whether a request without a bearer token can still list assignments.

• Switch to the second PowerShell terminal from earlier.
• Test the assignment list without a token by running:

Invoke-RestMethod -Method Get -Uri http://localhost:3000/assignments

What should you see?

PowerShell reports that the request failed. The response body identifies a Missing bearer token, which proves the middleware blocked anonymous access.

That is the first security boundary working. Anonymous callers can no longer read school assignments.

Did the request return assignments?

Confirm that authenticate appears between '/assignments' and the async handler. Restart the server after saving src/server.js.

Help me work out why my assignment list is still public.

Persist writes and expose the authorization gap

The list route now reads persistent rows. The create route must also use the scoped client so each assignment carries the verified caller's user ID.

• In src/server.js, find the current public create route:

app.post('/assignments', (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const assignment = {
    id: assignments.length + 1,
    title,
    description,
    class_name,
    due_date
  }

  assignments.push(assignment)
  res.status(201).json(assignment)
})

What does the old route rely on?

The old handler creates an ID from the array length. It pushes the new object into temporary server memory.

• Replace the public create route with this authenticated insert route:

app.post('/assignments', authenticate, async (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const { data, error } = await req.supabase
    .from('assignments')
    .insert({
      title,
      description,
      class_name,
      due_date,
      teacher_id: req.user.id
    })
    .select()

  if (error) {
    return res.status(403).json({ error: error.message })
  }

  res.status(201).json(data[0])
})

How does this persist an assignment?

• The authenticate middleware supplies the verified user and scoped client.
• The insert stores the request fields in the assignments table.
• The route sets teacher_id from req.user.id so the caller cannot submit a different owner ID.
• The final .select() returns the row that Postgres created.

• Save src/server.js.
• Switch to the PowerShell terminal running the server.
• Press Ctrl+C to stop the current process.
• Restart the API with the persistent route by running:

npm start

What is running now?

The restarted API includes bearer token verification and persistent assignment queries. Keep this terminal running while you send requests from the second terminal.

Before you send the next request, predict whether an authenticated student can create an assignment under the initial database policies.

• Switch to the second PowerShell terminal from earlier.
• Replace your-demo-password with the demo password you used when seeding the users.
• Sign in as the student and submit an authenticated assignment by running:

$login = @{ email = 'student@example.com'; password = 'your-demo-password' } | ConvertTo-Json
$student = Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $login
$headers = @{ Authorization = "Bearer $($student.access_token)" }
$assignment = @{ title = 'Student should not create this'; class_name = 'Math 7'; due_date = '2026-11-06' } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -Headers $headers -ContentType 'application/json' -Body $assignment

Why does the student request succeed?

You will see the created assignment as JSON. Its title is Student should not create this.

Authentication proves the student's identity. The initial insert policy still allows any authenticated caller to create a row whose teacher_id matches their own user ID.

This successful student write is the intended authorization failure. The next step adds role checks that close this gap.

• Confirm that the persistent row appears in the assignment list by running:

Invoke-RestMethod -Method Get -Uri http://localhost:3000/assignments -Headers $headers

What proves persistence?

You will see the new assignment in the returned list. The GET route reads it from the Supabase table through the student's authenticated client.

You have now reproduced the exact security gap this step was designed to reveal. The API knows who the student is while still granting too much write access.

Did the authenticated request fail?

• Confirm that the password matches the one used by scripts/seed-users.js.
• Check that the server terminal still shows the API running.
• Confirm that the initial assignment insert policy still exists in supabase/01_schema.sql.

Help me debug the authenticated student assignment request.

Compare both changed files with the completed versions below before moving on.

✔️ Awesome, I've got everything!

Great. Your authentication middleware and persistent assignment routes are saved.

ⓧ I'd like to double check the full code

Compare your completed src/auth.js with this reference.

import { authClient, createUserClient } from './supabase.js'

export async function authenticate(req, res, next) {
  try {
    const authorization = req.headers.authorization

    if (!authorization?.startsWith('Bearer ')) {
      return res.status(401).json({ error: 'Missing bearer token' })
    }

    const accessToken = authorization.slice(7)
    const {
      data: { user },
      error: userError
    } = await authClient.auth.getUser(accessToken)

    if (userError || !user) {
      return res.status(401).json({ error: 'Invalid or expired token' })
    }

    const userClient = createUserClient(accessToken)
    const { data: profiles, error: profileError } = await userClient
      .from('profiles')
      .select('role')
      .eq('id', user.id)

    if (profileError) {
      return res.status(500).json({ error: profileError.message })
    }

    if (!profiles?.[0]) {
      return res.status(403).json({ error: 'No school role is assigned' })
    }

    req.user = user
    req.role = profiles[0].role
    req.supabase = userClient
    next()
  } catch (error) {
    next(error)
  }
}

What should match in the middleware?

Your file should verify the token before creating the scoped client. It should attach req.user, req.role, and req.supabase before calling next().

Compare your completed src/server.js with this reference.

import express from 'express'
import { authenticate } from './auth.js'
import { authClient } from './supabase.js'

const app = express()
const port = 3000

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' })
  }

  const { data, error } = await authClient.auth.signInWithPassword({
    email,
    password
  })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Login failed' })
  }

  const { data: profiles, error: profileError } = await authClient
    .from('profiles')
    .select('role')
    .eq('id', data.user.id)
    .setHeader('Authorization', `Bearer ${data.session.access_token}`)

  if (profileError || !profiles?.[0]) {
    return res.status(403).json({ error: 'No school role is assigned' })
  }

  res.json({
    access_token: data.session.access_token,
    email: data.user.email,
    role: profiles[0].role
  })
})

app.get('/assignments', authenticate, async (req, res) => {
  const { data, error } = await req.supabase.from('assignments').select()

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.json(data)
})

app.post('/assignments', authenticate, async (req, res) => {
  const { title, description = '', class_name, due_date } = req.body

  if (!title || !class_name || !due_date) {
    return res.status(400).json({
      error: 'title, class_name, and due_date are required'
    })
  }

  const { data, error } = await req.supabase
    .from('assignments')
    .insert({
      title,
      description,
      class_name,
      due_date,
      teacher_id: req.user.id
    })
    .select()

  if (error) {
    return res.status(403).json({ error: error.message })
  }

  res.status(201).json(data[0])
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

What should match in the server?

Both assignment routes should include authenticate. They should query the database through req.supabase instead of an in-memory array.

Authentication and persistence now work together. Next, you will add role-based authorization so students lose write access while teachers and admins keep the permissions they need.

Enforce RBAC in Express and Supabase

Your Express API now verifies each caller with a bearer token. It also stores every assignment in Supabase.

A valid login proves identity. This step adds role-based access control to stop students from writing assignments. Tighter Row Level Security policies enforce the same permissions inside Postgres.

In this step, get ready to:

• Add reusable role checks to the assignment routes.
• Replace the temporary write policies with role-aware database policies.
• Verify the student, teacher, and admin permission matrix.

Add Express role gates

Authentication already places the trusted profile role on req.role. Authorization middleware can compare that role with the roles permitted to use each route.

Why use two authorization layers?

Express produces a clear rejection before a blocked write reaches the database. Supabase RLS evaluates the caller again when Postgres changes a row.

This applies least privilege at both boundaries. A request must satisfy both layers before school data changes.

• Switch back to src/auth.js in the VS Code editor.
• Add the role middleware below authenticate() by pasting this code:

export function allowRoles(...roles) {
  return function authorizeRole(req, res, next) {
    if (!roles.includes(req.role)) {
      return res.status(403).json({ error: 'Forbidden for this role' })
    }

    next()
  }
}

What does this middleware do?

• The roles parameter contains the roles permitted by a route.
• The middleware returns HTTP 403 when the caller's role is absent from that list.
• The next() call passes an approved request to the next route callback.

• Save src/auth.js.
• Click the new file icon in the VS Code Explorer sidebar.
• Enter src/permissions.js as the file path.
• Add the assignment role lists by pasting this code:

export const assignmentCreateRoles = ['admin', 'teacher']
export const assignmentUpdateRoles = ['admin', 'teacher']
export const assignmentDeleteRoles = ['admin', 'teacher']

Why centralize the role lists?

Each exported list defines who can perform one write operation. A separate permissions file keeps the route handlers focused on assignment data.

The three lists currently include admin plus teacher. Students remain outside every write list.

• Save src/permissions.js.
• Return to src/server.js in the editor.
• Replace the three imports at the top of the file with this import group:

import express from 'express'
import { authenticate, allowRoles } from './auth.js'
import {
  assignmentCreateRoles,
  assignmentUpdateRoles,
  assignmentDeleteRoles
} from './permissions.js'
import { authClient } from './supabase.js'

What changed in the imports?

The server now imports allowRoles beside the existing authentication middleware. It also imports one permission list for each write operation.

• Find the existing POST /assignments route.
• Replace the complete route with this protected version:

app.post(
  '/assignments',
  authenticate,
  allowRoles(...assignmentCreateRoles),
  async (req, res) => {
    const { title, description = '', class_name, due_date } = req.body
    if (!title || !class_name || !due_date) {
      return res.status(400).json({
        error: 'title, class_name, and due_date are required'
      })
    }
    const { data, error } = await req.supabase
      .from('assignments')
      .insert({
        title,
        description,
        class_name,
        due_date,
        teacher_id: req.user.id
      })
      .select()
    if (error) {
      return res.status(403).json({ error: error.message })
    }
    res.status(201).json(data[0])
  }
)

How does the create route work?

• The authenticate middleware verifies the token before authorization begins.
• The allowRoles(...assignmentCreateRoles) middleware rejects students before the insert handler runs.
• The caller-scoped Supabase client still submits the insert under that user's database identity.

• Save src/server.js.
• Stop the running API by pressing Ctrl+C in its PowerShell terminal.
• Restart the API with the new create gate by running this command:

npm start

You should see School RBAC API running at http://localhost:3000 without an import or syntax error.

Before you test the protected route, do you expect the signed-in student to reach the insert handler?

• Switch back to the second PowerShell terminal from the earlier request tests.
• Repeat the student assignment request by running this code:

$login = @{ email = 'student@example.com'; password = 'your-demo-password' } | ConvertTo-Json
$student = Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $login
$headers = @{ Authorization = "Bearer $($student.access_token)" }
$assignment = @{ title = 'Student should not create this'; class_name = 'Math 7'; due_date = '2026-11-06' } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -Headers $headers -ContentType 'application/json' -Body $assignment

What does this request prove?

The login still produces a valid student token. The assignment request now stops at the Express role gate.

PowerShell reports an HTTP 403 response containing Forbidden for this role. The student never reaches the Supabase insert.

That is the first permission boundary working. The same authenticated request that succeeded earlier is now blocked by its trusted profile role.

Student request still succeeds?

• Confirm that the create route places allowRoles(...assignmentCreateRoles) immediately after authenticate.
• Check that assignmentCreateRoles contains only admin plus teacher.
• Restart the API after saving the updated files.

Ask for help with a student request that bypasses the role middleware.

The create gate protects one operation. Update plus delete need the same sequence so every write route checks identity before role.

• Stop the API by pressing Ctrl+C in its PowerShell terminal.
• Add the update route below the completed POST /assignments route by pasting this code:

app.patch(
  '/assignments/:id',
  authenticate,
  allowRoles(...assignmentUpdateRoles),
  async (req, res) => {
    const allowedFields = ['title', 'description', 'class_name', 'due_date']
    const changes = {}
    for (const field of allowedFields) {
      if (field in req.body) {
        changes[field] = req.body[field]
      }
    }
    if (Object.keys(changes).length === 0) {
      return res.status(400).json({ error: 'No supported fields supplied' })
    }
    const { data, error } = await req.supabase
      .from('assignments')
      .update(changes)
      .eq('id', req.params.id)
      .select()
    if (error) {
      return res.status(403).json({ error: error.message })
    }
    if (!data[0]) {
      return res.status(404).json({ error: 'Assignment not found or not allowed' })
    }
    res.json(data[0])
  }
)

How does the update route stay focused?

• The allowedFields list limits updates to assignment fields supported by the API.
• The route filters the query with the assignment ID from req.params.id.
• A missing returned row produces HTTP 404 because the assignment may be absent or hidden by RLS.

• Add the delete route below the completed update route by pasting this code:

app.delete(
  '/assignments/:id',
  authenticate,
  allowRoles(...assignmentDeleteRoles),
  async (req, res) => {
    const { data, error } = await req.supabase
      .from('assignments')
      .delete()
      .eq('id', req.params.id)
      .select()
    if (error) {
      return res.status(403).json({ error: error.message })
    }
    if (!data[0]) {
      return res.status(404).json({ error: 'Assignment not found or not allowed' })
    }
    res.json({ deleted: data[0] })
  }
)

How does deletion stay protected?

The Express permission list allows admins plus teachers through the route boundary. The caller-scoped database query still decides whether that user can delete the selected row.

• Save src/server.js.
• Check the update plus delete routes by restarting the API:

npm start

You should see the server startup message again without a syntax error.

Seeing a route syntax error?

• Confirm that each app.patch() call ends with a closing parenthesis.
• Confirm that each app.delete() call ends with a closing parenthesis.
• Check that both routes appear before app.listen.

Ask for help with finding an unmatched brace in src/server.js.

• Stop the API by pressing Ctrl+C in its PowerShell terminal.
• Add these fallback handlers immediately above app.listen in src/server.js.

app.use((req, res) => {
  res.status(404).json({ error: 'Route not found' })
})

app.use((error, req, res, next) => {
  console.error(error)
  res.status(500).json({ error: 'Internal server error' })
})

What do the fallback handlers cover?

The first handler returns JSON for an unknown route. The second handler catches errors forwarded through Express error handling.

• Save src/server.js.
• Restart the completed Express API by running this command:

npm start

You should see School RBAC API running at http://localhost:3000. Leave this terminal running for the permission tests.

Tighten the database policies

The route middleware now rejects roles before a write begins. Database policies provide a second boundary if a route is ever misconfigured.

• Click the new file icon in the VS Code Explorer sidebar.
• Enter supabase/02_rbac.sql as the file path.
• Add the policy cleanup plus create policy by pasting this code:

drop policy "Initial authenticated insert" on public.assignments;
drop policy "Initial owner update" on public.assignments;
drop policy "Initial owner delete" on public.assignments;

create policy "Admins and teachers create assignments"
on public.assignments for insert
to authenticated
with check (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

How does the create policy work?

The first statements remove the temporary policies from the initial schema. The new insert policy checks the caller's trusted profile role.

Admins can create assignments. Teachers can create rows only when teacher_id matches their authenticated user ID.

• Add the row filter for the update policy below the create policy by pasting this section:

create policy "Admins update any assignment; teachers update their own"
on public.assignments for update
to authenticated
using (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
)

What does the update filter select?

The using condition controls which existing rows a caller may target. Admins can target every assignment.

Teachers can target assignments whose teacher_id matches their authenticated user ID.

• Complete the update policy by pasting this connected section directly below the row filter:

with check (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

Why does update need another check?

The with check condition validates the row after its proposed changes. It keeps the updated row inside the caller's permitted scope.

• Finish supabase/02_rbac.sql by adding the delete policy:

create policy "Admins delete any assignment; teachers delete their own"
on public.assignments for delete
to authenticated
using (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

How does the delete policy limit access?

Admins can delete any assignment. Teachers can delete rows only when teacher_id matches their authenticated user ID.

• Save supabase/02_rbac.sql.
• Return to the Supabase dashboard from the earlier database step.
• Select SQL Editor in the project sidebar.
• Click New Query.
• Copy the complete contents of supabase/02_rbac.sql from VS Code.
• Paste the migration into the query editor.
• Click Run.

The query should complete successfully. The temporary write policies are now replaced by admin, teacher, plus ownership rules.

Policy migration did not complete?

• Confirm that supabase/01_schema.sql ran earlier in this same Supabase project.
• Check that the three temporary policy names match the policies created earlier.
• Avoid running this migration twice because a successful run removes the temporary policies.

Ask for help with a failing RLS policy migration.

Use this checkpoint to compare the four files changed in this step.

✔️ Awesome, I've got everything!

Great. Double check that every file is saved. Your Express routes plus Supabase policies now use the same role matrix.

ⓧ I'd like to double check the full code

Compare your complete src/auth.js with this version.

import { authClient, createUserClient } from './supabase.js'

export async function authenticate(req, res, next) {
  try {
    const authorization = req.headers.authorization

    if (!authorization?.startsWith('Bearer ')) {
      return res.status(401).json({ error: 'Missing bearer token' })
    }

    const accessToken = authorization.slice(7)
    const {
      data: { user },
      error: userError
    } = await authClient.auth.getUser(accessToken)

    if (userError || !user) {
      return res.status(401).json({ error: 'Invalid or expired token' })
    }

    const userClient = createUserClient(accessToken)
    const { data: profiles, error: profileError } = await userClient
      .from('profiles')
      .select('role')
      .eq('id', user.id)

    if (profileError) {
      return res.status(500).json({ error: profileError.message })
    }

    if (!profiles?.[0]) {
      return res.status(403).json({ error: 'No school role is assigned' })
    }

    req.user = user
    req.role = profiles[0].role
    req.supabase = userClient
    next()
  } catch (error) {
    next(error)
  }
}

export function allowRoles(...roles) {
  return function authorizeRole(req, res, next) {
    if (!roles.includes(req.role)) {
      return res.status(403).json({ error: 'Forbidden for this role' })
    }

    next()
  }
}

Compare your complete src/permissions.js with this version.

export const assignmentCreateRoles = ['admin', 'teacher']
export const assignmentUpdateRoles = ['admin', 'teacher']
export const assignmentDeleteRoles = ['admin', 'teacher']

Compare your complete src/server.js with this version.

import express from 'express'
import { authenticate, allowRoles } from './auth.js'
import {
  assignmentCreateRoles,
  assignmentUpdateRoles,
  assignmentDeleteRoles
} from './permissions.js'
import { authClient } from './supabase.js'

const app = express()
const port = 3000

app.use(express.json())

app.get('/health', (req, res) => {
  res.json({ status: 'ok' })
})

app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body

  if (!email || !password) {
    return res.status(400).json({ error: 'Email and password are required' })
  }

  const { data, error } = await authClient.auth.signInWithPassword({
    email,
    password
  })

  if (error || !data.session) {
    return res.status(401).json({ error: error?.message ?? 'Login failed' })
  }

  const { data: profiles, error: profileError } = await authClient
    .from('profiles')
    .select('role')
    .eq('id', data.user.id)
    .setHeader('Authorization', `Bearer ${data.session.access_token}`)

  if (profileError || !profiles?.[0]) {
    return res.status(403).json({ error: 'No school role is assigned' })
  }

  res.json({
    access_token: data.session.access_token,
    email: data.user.email,
    role: profiles[0].role
  })
})

app.get('/assignments', authenticate, async (req, res) => {
  const { data, error } = await req.supabase.from('assignments').select()

  if (error) {
    return res.status(400).json({ error: error.message })
  }

  res.json(data)
})

app.post(
  '/assignments',
  authenticate,
  allowRoles(...assignmentCreateRoles),
  async (req, res) => {
    const { title, description = '', class_name, due_date } = req.body

    if (!title || !class_name || !due_date) {
      return res.status(400).json({
        error: 'title, class_name, and due_date are required'
      })
    }

    const { data, error } = await req.supabase
      .from('assignments')
      .insert({
        title,
        description,
        class_name,
        due_date,
        teacher_id: req.user.id
      })
      .select()

    if (error) {
      return res.status(403).json({ error: error.message })
    }

    res.status(201).json(data[0])
  }
)

app.patch(
  '/assignments/:id',
  authenticate,
  allowRoles(...assignmentUpdateRoles),
  async (req, res) => {
    const allowedFields = ['title', 'description', 'class_name', 'due_date']
    const changes = {}

    for (const field of allowedFields) {
      if (field in req.body) {
        changes[field] = req.body[field]
      }
    }

    if (Object.keys(changes).length === 0) {
      return res.status(400).json({ error: 'No supported fields supplied' })
    }

    const { data, error } = await req.supabase
      .from('assignments')
      .update(changes)
      .eq('id', req.params.id)
      .select()

    if (error) {
      return res.status(403).json({ error: error.message })
    }

    if (!data[0]) {
      return res.status(404).json({ error: 'Assignment not found or not allowed' })
    }

    res.json(data[0])
  }
)

app.delete(
  '/assignments/:id',
  authenticate,
  allowRoles(...assignmentDeleteRoles),
  async (req, res) => {
    const { data, error } = await req.supabase
      .from('assignments')
      .delete()
      .eq('id', req.params.id)
      .select()

    if (error) {
      return res.status(403).json({ error: error.message })
    }

    if (!data[0]) {
      return res.status(404).json({ error: 'Assignment not found or not allowed' })
    }

    res.json({ deleted: data[0] })
  }
)

app.use((req, res) => {
  res.status(404).json({ error: 'Route not found' })
})

app.use((error, req, res, next) => {
  console.error(error)
  res.status(500).json({ error: 'Internal server error' })
})

app.listen(port, () => {
  console.log(`School RBAC API running at http://localhost:${port}`)
})

Compare your complete supabase/02_rbac.sql with this version.

drop policy "Initial authenticated insert" on public.assignments;
drop policy "Initial owner update" on public.assignments;
drop policy "Initial owner delete" on public.assignments;

create policy "Admins and teachers create assignments"
on public.assignments for insert
to authenticated
with check (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

create policy "Admins update any assignment; teachers update their own"
on public.assignments for update
to authenticated
using (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
)
with check (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

create policy "Admins delete any assignment; teachers delete their own"
on public.assignments for delete
to authenticated
using (
  exists (
    select 1
    from public.profiles as profile
    where profile.id = (select auth.uid())
      and (
        profile.role = 'admin'
        or (
          profile.role = 'teacher'
          and teacher_id = (select auth.uid())
        )
      )
  )
);

Test the role matrix

The application plus database now share one permission model. The final requests prove that identity alone cannot grant write access.

Before you repeat the student request, do you expect the new database policies to agree with the Express rejection?

• Repeat the student create request against both authorization layers by running this code:

$login = @{ email = 'student@example.com'; password = 'your-demo-password' } | ConvertTo-Json
$student = Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $login
$headers = @{ Authorization = "Bearer $($student.access_token)" }
$assignment = @{ title = 'Student should not create this'; class_name = 'Math 7'; due_date = '2026-11-06' } | ConvertTo-Json
Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -Headers $headers -ContentType 'application/json' -Body $assignment

What should you see?

PowerShell reports HTTP 403 for the assignment request. The response confirms that students cannot create assignments.

Before you try the same operation as a teacher, do you expect the request to pass both permission layers?

• Sign in as the teacher plus create an assignment by running this code:

$login = @{ email = 'teacher@example.com'; password = 'your-demo-password' } | ConvertTo-Json
$teacher = Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $login
$headers = @{ Authorization = "Bearer $($teacher.access_token)" }
$assignment = @{ title = 'Fractions worksheet'; class_name = 'Math 7'; due_date = '2026-11-06' } | ConvertTo-Json
$teacherAssignment = Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -Headers $headers -ContentType 'application/json' -Body $assignment
$teacherAssignment

Why does the teacher request succeed?

The teacher passes the Express create-role check. Supabase also accepts the insert because teacher_id matches the authenticated teacher.

You should see the created assignment JSON. It includes an id plus the title Fractions worksheet.

You have now proved the central RBAC result. A student receives HTTP 403. A teacher receives a persistent assignment row.

• Update the teacher's own assignment by running this code:

$update = @{ description = 'Review before Friday' } | ConvertTo-Json
Invoke-RestMethod -Method Patch -Uri "http://localhost:3000/assignments/$($teacherAssignment.id)" -Headers $headers -ContentType 'application/json' -Body $update

What does the teacher update prove?

The returned assignment contains the new description. Express permits the teacher role while RLS confirms ownership of that row.

• Delete the teacher's own assignment by running this code:

Invoke-RestMethod -Method Delete -Uri "http://localhost:3000/assignments/$($teacherAssignment.id)" -Headers $headers

What does the teacher delete prove?

The response contains the deleted assignment. The teacher can delete a row that carries their own authenticated user ID.

• Sign in as the admin plus create an admin-owned assignment by running this code:

$adminLogin = @{ email = 'admin@example.com'; password = 'your-demo-password' } | ConvertTo-Json
$admin = Invoke-RestMethod -Method Post -Uri http://localhost:3000/auth/login -ContentType 'application/json' -Body $adminLogin
$adminHeaders = @{ Authorization = "Bearer $($admin.access_token)" }
$adminBody = @{ title = 'School assembly reflection'; class_name = 'English 7'; due_date = '2026-11-13' } | ConvertTo-Json
$adminAssignment = Invoke-RestMethod -Method Post -Uri http://localhost:3000/assignments -Headers $adminHeaders -ContentType 'application/json' -Body $adminBody
$adminAssignment

What does the admin create prove?

The admin passes the shared create-role list. The database policy also recognizes the admin profile role.

Before you use the teacher token against the admin-owned row, do you expect ownership RLS to expose that row to the update?

• Try to update the admin-owned assignment with the teacher token by running this code:

Invoke-RestMethod -Method Patch -Uri "http://localhost:3000/assignments/$($adminAssignment.id)" -Headers $headers -ContentType 'application/json' -Body $update

What should ownership block?

PowerShell reports HTTP 404 containing Assignment not found or not allowed. The teacher passes the route-level role check, but RLS hides the admin-owned row from the update.

• Update the same assignment with the admin token by running this code:

Invoke-RestMethod -Method Patch -Uri "http://localhost:3000/assignments/$($adminAssignment.id)" -Headers $adminHeaders -ContentType 'application/json' -Body $update

What does the admin update prove?

The returned assignment contains the updated description. The admin policy can target an assignment regardless of its owner.

Before the final request, do you expect the admin to delete this row successfully?

• Complete the role matrix by deleting the assignment with the admin token:

Invoke-RestMethod -Method Delete -Uri "http://localhost:3000/assignments/$($adminAssignment.id)" -Headers $adminHeaders

What does the final response confirm?

• Students can list assignments but receive HTTP 403 from write routes.
• Teachers can create assignments plus change rows they own.
• Admins can update or delete any assignment.

Teacher or admin request rejected?

• Confirm that the SQL migration completed in the Supabase project referenced by .env.
• Check that each request uses the access token returned for the intended account.
• Restart the local API after saving the latest Express changes.

Ask for help with a role request rejected after the RBAC migration.

Secret mission

Add a Department Head Role

Schools often need a role with more reach than a teacher but less control than an administrator. In this mission, you will add a department head who can review any assignment without gaining create or delete access.

Clean Up Your Resources

Clean Up Your Resources

The project stays within Supabase's Free plan at $0 per month. Decide whether to keep your resources available, pause them for later, or delete them entirely.

Resources you used:

• A local Express API process running in Windows PowerShell at http://localhost:3000.
• A local Node.js project folder named school-rbac-api, including .env and the installed dependencies.
• One Supabase Free project containing the profiles table and the assignments table.
• Four confirmed users in Supabase Auth with admin, teacher, student, and department head roles.
• Database grants and Row Level Security policies protecting assignment data.

Keep everything running

No cloud cleanup is needed. Choose this if you want to continue building school modules with the same users and data.

• Switch back to the PowerShell terminal where the API is running.
• Press Ctrl+C to stop the local server.
• Keep the school-rbac-api folder on your computer.
• Leave the Supabase project active for future work.

A Free project with low activity may be paused over a 7-day period. Your local project files remain available if that happens.

Pause - I'll come back to this later

Pause the cloud project to preserve its data while you take a break. Your local files stay on your computer.

• Switch back to the PowerShell terminal where the API is running.
• Press Ctrl+C to stop the local server.
• Sign in to the Supabase Dashboard.
• Select the project you created for this API.
• Select Settings in the sidebar.
• Select General.
• Find Project availability.
• Select Pause Project.

Your project data remains preserved. You can resume the project when you return.

Delete - I don't want to use this again

Deleting your Supabase project is permanent. It removes the database, Auth users, API keys, and project configuration.

• Switch back to the PowerShell terminal where the API is running.
• Press Ctrl+C to stop the local server.
• Sign in to the Supabase Dashboard.
• Select the project you created for this API.
• Select Settings in the sidebar.
• Select General.
• Select Delete project.
• Enter the project name in the confirmation field.

Confirming the next action clears every hosted resource from this project. Your local folder remains separate until you remove it.

• Confirm the project deletion when prompted.
• Open File Explorer from Windows search.
• Navigate to the location containing school-rbac-api.
• Right-click the school-rbac-api folder.
• Select Delete.

Nice Work!

Nice Work!

You did it! You built a secure school assignment API with distinct permissions for admins, teachers, students, and department heads.

You've learned how to:

• Build an Express REST API with persistent create, list, update, and delete operations for school assignments.
• Use Supabase Auth to issue bearer tokens that identify callers before protected assignment requests run.
• Create layered role-based access control with Express middleware plus Postgres Row Level Security.
• Secret Mission: Added a department-head role that can update any assignment while delete requests remain blocked.

Ready to quiz yourself?