Backend & Database
The kitchen behind the restaurant — everything users never see but can't live without.
The Kitchen Behind the Restaurant
Think about your favourite restaurant. The dining room is beautiful — clean tables, good lighting, a polished menu. The food arrives quickly. Everything feels smooth and professional.
Now imagine the kitchen is a mess. Expired ingredients. No organisation. The chef doesn't know which order belongs to which table. Staff argue about who is supposed to do what. Strangers walking in off the street can open the fridge and take whatever they want.
It doesn't matter how beautiful the dining room is. The restaurant will fail.
Your app works the same way.
The frontend (React) is the dining room — beautiful, interactive, what users see and touch.
The backend and database is the kitchen — all the logic, all the data, all the security. Users never see it. They feel it in everything.
You can build the most beautiful React UI in the world. If your backend is insecure, slow, or unorganised — your app fails. Data leaks. Performance collapses. Clients lose trust.
This module teaches you to build the kitchen properly.
The Excel Analogy (Your Mental Model)
If you're a CA or finance professional, you already understand this distinction — you just use different words.
| Excel | App |
|---|---|
| The formatted, client-facing sheet (pivot tables, charts, conditional formatting) | Frontend — React |
| The raw data sheet (all the actual numbers, formulas protecting the data, named ranges) | Backend database — Supabase/Postgres |
| Sheet protection with passwords | Row Level Security — who can see which rows |
| VLOOKUP pulling data from another sheet | API query — frontend asking backend for data |
| Macros (VBA) that run calculations | Edge functions — serverless business logic |
| Google Sheets (access from anywhere) | Cloud database — Supabase hosts it, always on |
| Track Changes / Version History | Git — for code; Migrations — for schema |
The difference: in Excel, you control the protection manually and hope no one bypasses it. In Supabase, the database enforces the rules automatically — even if someone tries to bypass your frontend code.
What You Will Learn
By the end of this module, you will know how to:
- Design a proper database before writing any code
- Create tables, define columns, and set up relationships
- Set up Supabase and connect it to your React app
- Write Row Level Security policies that protect data at the database level
- Fetch data using React Query with proper loading and error states
- Write edge functions for sensitive server-side operations
- Implement authentication with multiple user roles
Every concept is shown using real code from Aurora Public School — a production platform running at vopaj.com that was built using exactly this stack. Not toy examples. Real code, real patterns, real lessons from production.
The Seven Critical Questions
Before touching any code, a backend developer asks these seven questions:
- What are the entities? (Students, teachers, classes, branches, fee records, attendance records...)
- What are their relationships? (One branch has many classes. One class has many students. One student has many fee records.)
- Who can see what? (Parents see only their own child's records. Teachers see only their assigned class. Branch admins see only their branch.)
- What operations do users need? (Create, read, update, delete — and which roles can do each?)
- What should never be deleted? (Almost everything — use soft delete instead.)
- What happens in the background? (Emails, notifications, payment processing — edge functions.)
- What can go wrong? (Invalid data, unauthorised access, duplicate records — plan for failure.)
A developer who asks these questions first ships features that hold up in production. A developer who skips them ships features that break when real users arrive.
What You Will Build
In Project 2, you will extend your personal website with a real backend. A CA's profile site becomes a client inquiry system with a private admin panel. A portfolio becomes a project showcase with database-driven content.
The concepts here prepare you for that — and for Aurora Public School, where the same database design principles apply at full scale: multiple branches, student records, attendance tracking, and fee payment data that real schools depend on.
Let's start with the foundation: understanding what a database actually is.
Check Your Understanding
Why must Row Level Security (RLS) live in the database — not in your React component?
In the Seven Critical Questions, what must you answer BEFORE designing any tables or writing any code?
Open a blank document
Write down the entities for a simple CA client tracker (hint: clients, engagements, documents, invoices)
For each entity, write one sentence about its relationship to the others
Answer question 3: who can see what? (CA partner sees all clients, junior sees only assigned clients, client sees only their own data)
Answer question 5: what should never be deleted? (almost everything — use soft delete)
✓ Expected result
You should have a one-page data design document with entities, relationships, and access rules — written entirely in plain English, before touching Supabase. This document is what you hand to the Data Architect hat before switching to Backend Developer hat.
Before you move on — can you do all of this?
Click each item you're confident about. Bring the unchecked ones to your next session.
I can name all Seven Critical Questions from memory and explain why each one matters
I understand why RLS must be in the database layer — not the app layer
I understand the kitchen analogy: the backend is everything users never see but can't live without
I can map every backend concept to its Excel equivalent (sheet protection, VLOOKUP, VBA macros)
I have completed the Seven Questions exercise for at least one project I want to build