role_based_access.md

Implementing Role-Based Access Control (RBAC) in Your Supabase-Powered LMS

To implement RBAC for your LMS, which supports multiple roles (admin, teachers, principal, class teacher, students) across multiple schools, you'll leverage Supabase's built-in authentication system (with Google OAuth and email/password providers) combined with Row-Level Security (RLS) on your database tables. This setup ensures that users can only access and manipulate data they're authorized for, while keeping school data isolated (multi-tenancy). The approach is secure, scalable, and easy to integrate into schools because it relies on Supabase's managed auth and policy-based enforcement—no custom backend auth servers needed. Users sign up or log in via Google or email/password, and roles are assigned post-authentication for controlled access.

Here's a step-by-step conceptual guide to implementation, focusing on ease of school integration and managing role-specific functionalities (e.g., admins handle school-wide setup, teachers manage assignments, students view personal data). This builds a "defense-in-depth" system where even if someone bypasses frontend checks, the database enforces rules.

1. Set Up User Authentication with Supabase Auth

• Base Authentication Flow: Start with Supabase Auth as your identity provider. Enable Google OAuth (for easy school logins, e.g., via school Google Workspace accounts) and email/password (for fallback). When a user signs up or logs in, Supabase generates a JWT (JSON Web Token) containing their unique user ID and any custom metadata.
• Initial Role Assignment During Signup: For new users, use a post-signup trigger or your app's onboarding flow to prompt for basic info (e.g., school affiliation). Default new users to a "pending" or "student" role to prevent unauthorized access until verified. This keeps integration simple—schools can invite users via email links, and the system auto-provisions accounts.
• School Integration Tip: For bulk onboarding (e.g., importing a school's roster), use Supabase's admin API or a secure admin dashboard to create users in batches. Link each user to a school via metadata during creation, making it plug-and-play for school admins to upload CSVs without manual entry.

2. Assign and Manage Multiple Roles

• Store Roles Securely: Attach roles to users by storing them in Supabase's auth.users table metadata (specifically, the raw_app_meta_data field, which is admin-only editable and included in the JWT). For example:
  • Admin: Full system access, including cross-school management.
  • Principal: School-wide oversight, like viewing all classes/reports.
  • Teacher/Class Teacher: Class-specific actions, like assigning/grading.
  • Student: Personal view-only access to their data.
  • If a user needs multiple roles (e.g., a class teacher who is also a principal), store an array of roles in metadata (e.g., ["teacher", "principal"]) for flexible checks.
• Role Assignment Process:
  • Admin-Driven: Build an admin interface where school admins or principals assign/upgrade roles (e.g., promote a teacher to class teacher). Use Supabase's secure functions to update metadata, ensuring only higher roles can modify lower ones.
  • Self-Claim with Verification: During onboarding, users select a role, but it stays "pending" until an admin approves via email notification or dashboard review. This eases school integration—teachers can self-register, but principals control activation.
  • Profile Table for Extended Management: Create a profiles table linked to auth.users (via user_id foreign key). Store additional details here like school_id, role specifics (e.g., classes assigned to a teacher), and preferences. This table allows easy querying for role-based features without touching auth tables directly.
• Handling Multi-Role Access: For users with multiple roles, your app's UI can present a role-switcher (e.g., dropdown to toggle between "teacher" and "principal" views). On the backend, policies check if any of the user's roles satisfy the required permission, making it seamless to manage overlapping responsibilities without duplicating data.
• School Integration Tip: Make role management school-specific—e.g., a principal can only assign roles within their school. This prevents cross-school leaks and allows easy rollout: Schools get a dedicated "setup wizard" to define role hierarchies during initial integration.

3. Enforce Permissions with Row-Level Security (RLS)

• Enable RLS on All Tables: For every database table (e.g., assignments, grades, classes, reports), activate RLS to block unauthorized access by default. RLS acts like an automatic filter, adding invisible conditions to every query based on the user's JWT.
• Define Role-Specific Policies:
  • Policy Structure: Create separate policies for each operation (read/select, create/insert, update, delete) on each table. Policies reference the user's role from JWT metadata and combine it with other conditions like user_id or school_id.
    • Students: Can only read their own data (e.g., view personal assignments/grades where user_id matches their JWT ID). No create/update/delete on shared tables.
    • Teachers/Class Teachers: Read/write within their assigned classes (e.g., insert grades for students in their class_id; read class progress but not other teachers' data).
    • Principals: Read/write school-wide (e.g., view all classes/reports in their school_id; update teacher assignments).
    • Admins: Full access, including cross-school (e.g., bypass school_id filters for system maintenance).
  • Multi-Tenancy Isolation: Add a school_id column to all relevant tables. Every policy includes a condition like "school_id must match the user's school_id from JWT." This ensures data from one school is invisible to another, even for admins unless they have a global role.
  • Hierarchical Permissions: Use OR conditions in policies for role escalation (e.g., allow access if role is "admin" OR "principal" for school-level reads). For sensitive actions (e.g., deleting a class), require explicit role checks to prevent accidental overuse.
• Managing Different Things Based on Roles:
  • Dashboards and UI: In your app, query user metadata on login to route to role-specific views (e.g., student sees personalized feed; teacher sees assignment creator; principal sees analytics heatmap). RLS ensures backend queries only return permitted data, so the UI never shows unauthorized info.
  • Feature-Specific Controls: For assignments—teachers create/distribute (insert into assignments table with class_id check); students submit (update their row only). For grading—auto-grading functions run under the submitter's context, but teachers override via policies allowing updates on flagged rows. For reports—principals export school-wide aggregates, while students see personal mastery levels.
  • Audits and Logs: Automatically log actions via Supabase functions tied to policies, so admins can review who did what (e.g., "Teacher X updated grade Y").
• Edge Cases: For shared resources (e.g., CBSE question bank), use a global table with read-only policies for all authenticated users, but restrict writes to admins.

4. Integrate Seamlessly into Schools

• Onboarding and Bulk Setup: Provide a school registration flow where an initial admin (e.g., principal) creates a school_id and invites users. Use Supabase's email invites or Google Workspace integration for bulk adds—e.g., import from CSV to auto-assign roles and school_id. This minimizes setup time (under 30 minutes per school).
• Customization and Scalability: Allow schools to define custom sub-roles (e.g., "head teacher") by extending metadata fields. For large schools, use Supabase's realtime subscriptions to sync role changes instantly across devices.
• Testing and Rollout: Start with a single school pilot: Assign test roles, simulate actions, and verify RLS blocks unauthorized queries. Gradually add schools, monitoring via Supabase dashboard for policy performance.
• Maintenance: Use Supabase's admin tools to update policies globally without downtime. If roles evolve (e.g., add "parent" role), adjust metadata and policies incrementally.

This RBAC setup is robust yet simple—Supabase handles the heavy lifting, ensuring security scales with your multi-school model. It allows fine-grained management (e.g., teachers focus on teaching without admin distractions) while being easy to integrate: Schools just need to provide user lists, and the system auto-enforces boundaries. If issues arise (e.g., policy conflicts), debug by reviewing query logs in Supabase.

No — you should not implement the complete role-based auth in FastAPI first.

Instead: Implement the core RBAC system inside Supabase first (using RLS + JWT metadata), then wrap it with minimal FastAPI middleware for convenience and security.
Do this before starting Phase 1, but not as a full FastAPI-heavy system — because Supabase already does 90% of the work.

Here’s the correct, fast, secure, and scalable order for your 2-day MVP build using Cursor Pro:

---

Correct Execution Order (2-Day MVP)

Step	What to Do	Why	Time
0	Set up Supabase Project (Auth + DB + RLS)	This is your single source of truth for auth & permissions	30 mins
1	Implement Full RBAC in Supabase (RLS + Policies + Roles)	Supabase enforces access at DB level — unbreakable	2–3 hrs
2	Add Lightweight FastAPI Middleware (JWT Validation + Role Helpers)	FastAPI only reads and passes auth — never decides it	1 hr
3	Start Phase 1: Teacher Dashboard + Upload → Assign	Now safe — every API call is protected by Supabase RLS	Rest of Day 1 & 2

---

Step-by-Step: How to Implement RBAC Correctly (No Code, Just Plan)

Step 0: Supabase Project Setup

1. Create new Supabase project.
2. Enable Email/Password and Google OAuth.
3. Create these tables (with school_id, user_id):
   • profiles → stores role, school, class assignments
   • classes, assignments, submissions, grades, insights, audits
4. Enable RLS on all tables → default policy: false (block all until allowed)

---

Step 1: Implement Full RBAC in Supabase (This is your security core)

A. Store Roles in JWT (via raw_app_meta_data)

• After login, Supabase JWT contains:

  {
    "user_id": "uuid",
    "email": "...",
    "app_metadata": {
      "roles": ["teacher", "class_teacher"],
      "school_id": "school-123"
    }
  }

• Use Supabase Auth Hooks or admin dashboard to set this on user creation/approval.

B. Create RLS Policies (Examples)

Table	Policy Name	Condition (SQL)
assignments	Students can read own	(auth.uid() = student_id) AND (auth.role() = 'anon' OR auth.jwt() -> 'app_metadata' ->> 'roles' ? 'student')
assignments	Teachers can manage their class	(auth.jwt() -> 'app_metadata' ->> 'school_id' = school_id) AND (auth.jwt() -> 'app_metadata' ->> 'roles' ? 'teacher') AND (class_id IN (SELECT class_id FROM teacher_assignments WHERE teacher_id = auth.uid()))
grades	Principal can view all in school	(auth.jwt() -> 'app_metadata' ->> 'school_id' = school_id) AND (auth.jwt() -> 'app_metadata' ->> 'roles' ? 'principal')
profiles	Admin full access	auth.jwt() -> 'app_metadata' ->> 'roles' ? 'admin'

Key: Every policy checks role + school_id + ownership. This is multi-tenant + multi-role security.

C. Use auth.role() and auth.uid() in policies

• auth.uid() → current user
• auth.jwt() -> 'app_metadata' ->> 'roles' → check if user has role
• auth.jwt() -> 'app_metadata' ->> 'school_id' → isolate schools

---

Step 2: FastAPI — Only a Thin, Trusted Layer

Your FastAPI does NOT decide permissions. It only:

Task	How
Validate JWT	Use supabase-py or PyJWT to verify token from Authorization: Bearer <token>
Extract user_id, roles, school_id	From decoded JWT
Pass to Supabase	Use Supabase client with service_role key only for admin actions
Helper Functions	e.g., get_current_user(), require_role("teacher")

Never bypass RLS — even if FastAPI allows it, Supabase will block.

---

Step 3: Now Start Phase 1 — Safe & Fast

With RBAC locked in Supabase, your Phase 1 APIs are automatically secure:

POST /upload-assign
→ FastAPI checks JWT → validates role "teacher"
→ Calls Supabase → RLS allows only if teacher owns class
→ AI runs → stores in DB → RLS protects

All features (grading, student feed, insights) inherit this security.

---

Why This Order Wins (Especially for 2-Day MVP)

Benefit	Explanation
Secure by default	RLS blocks breaches even if FastAPI has bugs
Fast to build	80% of auth is Supabase config — no custom logic
Easy school integration	Admins assign roles in dashboard; no code changes
Scales to 1000 schools	school_id + RLS = perfect isolation
Debuggable	Use Supabase SQL editor to test policies live

---

2-Day Build Timeline (Cursor Pro Optimized)

Time	Task
Day 1 – 9 AM	Supabase: Create tables, enable RLS, write 5 core policies
Day 1 – 11 AM	Set up role assignment flow (admin invites → sets app_metadata)
Day 1 – 12 PM	FastAPI: JWT middleware + get_current_user()
Day 1 – 2 PM	Next.js: Login (Supabase Auth) + role-based routing
Day 1 – 6 PM	Teacher Dashboard: Upload → RAG → Assign (RLS-protected)
Day 2 – 9 AM	Student Dashboard: Feed + Submission (offline PWA)
Day 2 – 12 PM	Auto-Grading + Override UI
Day 2 – 3 PM	Progress Tracking + Exports
Day 2 – 5 PM	Test end-to-end + deploy

---

Final Recommendation

Do NOT build RBAC in FastAPI first.
DO build it in Supabase RLS + JWT metadata first.
THEN use FastAPI as a lightweight, role-aware proxy.

This is the industry-standard pattern used by Vercel, Clerk, and top EdTechs. It’s faster, safer, and easier to integrate into schools.