Academia Connect.

One backend for 73 schools, and no school can see another's data.

The Punjab Workers Welfare Fund needed its schools on one system. I led a team of three, from client requirements to architecture, building three role-based portals and a student app on one backend. It runs 73 schools and 50,000+ students.

Role: Team lead · full-stack
Team: 3 developers
Time: 2025
Stack: React · React Native · PostgreSQL · Supabase
Access: Private: production system for the client

50,000+
students in production
73
schools on one backend
8
standard monthly reports, generated
1st
place, final-year project

One backend, four kinds of usersimplified · example values73 schools · 1 backend

Overview. Three role-based portals in one web app, plus a student mobile app: four kinds of user on one Supabase backend, with each school's data kept apart by row-level security.

Key decisions.

Isolation in the database, not the app
I kept each school's data apart with row-level security in Postgres instead of relying on checks in application code, so a single missed check in a query can never leak one school's data to another.
One table, 73 schoolssimplified · example values
1. Query: A signed-in teacher's portal asks for students. The app adds no school filter of its own.
2. Lookup: Inside Postgres, a helper looks up which school the signed-in user belongs to, from their user id.
3. Filter: The policy compares every row's school_id with that school. Only School A's rows match.
4. Result: 73 schools share one table, and each only ever sees its own students.
Query. A signed-in teacher's portal asks for students. The app adds no school filter of its own.
Monthly reports as one submission, not loose files
I had each school build the eight standard reports from live records, save each once per month, and send all eight together as one zipped submission (a second for the same month is refused) instead of report by report, so head office always knows exactly who has reported for the month and who hasn't.
Eight monthly reports, one submissionsimplified · example valuesMarch
1. Build: A school picks the month and one of eight standard reports (staff profiles, vacancies, student strength, transport and more), built from its live records.
2. Calculate: Some are calculated. The vacancy report compares each post's sanctioned strength with the staff actually working.
3. PDF: Each report becomes an A4 PDF right in the browser.
4. Save: Saving checks for an existing copy first, so each report is saved once per month.
5. Duplicate: Saving the same report for the same month again is refused.
6. Send: Once all eight are saved, they go to head office together as one zipped submission. A second submission for the same month is refused.
7. Pending: Head office sees which schools haven't submitted for the month yet, and can email each one a reminder.
Build. A school picks the month and one of eight standard reports (staff profiles, vacancies, student strength, transport and more), built from its live records.
A transfer moves the teacher, not their history
I made a transfer create the teacher's record at the new school and mark the old one Transferred with the date, instead of editing the record in place, so both schools' staff history stays true, the old login stops working, and head office's totals never count a teacher twice.
A transfer that keeps historysimplified · example values
1. Find: Head office finds the teacher by their national ID (CNIC). Only a teacher who's currently working can be transferred.
2. New record: A new record is created at the receiving school, with an ID from that school's series and a login of its own.
3. Close old: The old record isn't edited away. It's marked Transferred, with the school and the date, so School A's history stays true and head office's totals count the teacher once.
4. Old login: Signing in with the old account no longer works: the login checks that the teacher is still Working.
Find. Head office finds the teacher by their national ID (CNIC). Only a teacher who's currently working can be transferred.
One phone per student
I limited each student to one signed-in device: signing in on a new phone ends the session on the old one in real time, instead of letting a login live on every phone it was ever typed into.
One phone per studentsimplified · example values
1. Signed in: Each student has one session row, holding the one device that's signed in.
2. New login: The student signs in on a new phone.
3. Sign out: The old device is cleared first. The old phone is listening in real time, so it's signed out at once: “You have been logged in from another device.”
4. Claim: Then the new phone is recorded as the student's one active device.
5. Backup: If the old phone's realtime connection had dropped, a backup check every couple of minutes catches it anyway.
Signed in. Each student has one session row, holding the one device that's signed in.

Row-level security · simplified patternpolicy.sql

-- simplified: find the caller's school from their user idcreate function current_school_id() returns textlanguage sql stable security definer set search_path = public as $$  select "SchoolID" from "School" where user_id = auth.uid()  union all  select "SchoolID" from "Teacher" where user_id = auth.uid()  limit 1$$;-- each school sees only its own rowscreate policy school_isolation on students  using (school_id = current_school_id());

What I'd change

Invest in per-tenant seed and test data from day one. Verifying isolation by hand was the slow part.

Background

The Punjab Workers Welfare Fund runs a network of schools, and it needed them on one system: a head office that oversees every school, schools that manage their own students and staff, teachers who run their classes, and students who follow it all on their phones. It's in production for 73 schools and 50,000+ students, and it took 1st place as our final-year project.

I led the team of three. I owned the requirements with the client (what head office has to see every month, how a teacher moves between schools, who may read or change what), turned them into the data model and the access rules, split the work across the team, made the technical calls, and saw it through to production.

Each part in detail

Head Office Portal

Web · React 19 + Vite

Where the Fund oversees the whole network: every school, teacher and student, the notices and the monthly reporting.

Network dashboard: totals, growth over the years, staffing and enrolment per school, and a notice calendar
Schools added, edited and removed; a school that still has teachers can't be deleted
Teacher records with 22 validated fields and IDs issued in each school's own series
Transfers between schools that keep both schools' history intact, counting each teacher once in head office's totals
Notices to schools, teachers and students: government or school, holiday or event, with an urgent flag and an end date
Monthly reports received and pending per school, with one-click email reminders sent by an Edge Function

Hardest part

Creating logins for other people without losing your own. The portal creates school and teacher accounts, and signing a new user up replaces the current session, so the admin's session is saved before each new account and restored straight after it.

React 19 · Vite · Material UI · Formik · Yup · Recharts · Supabase

School Portal

Web · React 19 + Vite

Each school's own office: admissions, staff, classes, timetables, notices and the monthly returns.

Admissions in nine sections: registration numbers issued per school and year (STU-<school>-<yy>-NNN), and a father's national ID (CNIC) fills in family details and finds siblings
Staff records with their documents: CNIC, photo, certificates and experience letters
Teachers assigned to classes and subjects, with a clash check so no subject in a class goes to two teachers
Class, exam and faculty timetables, bus routes and teacher attendance uploads
Notices to the school's own teachers and students
Eight standard monthly reports, built, saved and sent from here; student performance weighted across assignments, quizzes, mid-terms and finals

Hardest part

Reports in the form head office expects. Eight standard returns (the monthly staff profile, staff information, teaching and non-teaching staff, vacancies against sanctioned posts, student strength by class and quota, students using transport, and staff with disabilities) are generated as A4 PDFs in the browser from live data, saved once per month, and sent all together as one zipped submission.

React 19 · Vite · Material UI · jsPDF · JSZip · Supabase Storage

Teacher Portal

Web · React 19 + Vite

A teacher's day: their classes, attendance, assessments, marks and study material.

Dashboard of assigned classes and subjects, the timetable and current notices
Attendance for a whole class: mark everyone, then adjust
Assignments, quizzes, mid-terms and finals with deadlines, files and total marks
Marks capped at each assessment's total
Study material uploads for each class

Hardest part

Saves that can safely be repeated. Attendance and marks are written as upserts keyed on the student and the date (or the assessment), so a teacher who saves twice, or retries after a dropped connection, never creates duplicates.

React 19 · Vite · Material UI · Supabase

Student App

Mobile · React Native + Expo

What students carry: attendance, marks, schedules, assessments, study material and notices, on their phone.

Attendance with a calendar and charts
Marks and a report screen
Class, exam and bus schedules, ready to share
Assignments, quizzes and study material for the student's own class and section
Announcements and an events calendar from head office and the school
Bus route map, feedback to the school, and push notifications

Hardest part

One device at a time. Signing in on a new phone clears the old device from the student's session row, and the old phone, listening in real time, signs itself out with a message; a backup check every couple of minutes catches a phone whose connection had dropped.

React Native · Expo · Expo Router · EAS Build · Supabase Realtime · Expo Notifications · React Native Maps

How it was built

Leading the requirements

The hard part was understanding the organisation before writing any code. I worked with the client to pin down what head office must see each month, how staff and students move through the system, and who may see or change what, and turned that into the data model, the access rules and the plan the three of us built against.

Monthly reporting

Every school owes head office the same eight returns each month. The school portal builds them from live records, including vacancies calculated against each school's sanctioned posts, as A4 PDFs in the browser. Each is saved once per month, and once all eight are saved they're sent as one zipped submission (a second for the same month is refused), so head office has a received-and-pending view of the whole network and can send any school that's behind an email reminder, through an Edge Function.

Access and sessions

Each portal checks the signed-in user against its own role table, row-level security keeps every school's rows to that school, and a transferred teacher's old account can't sign in. Forms check the rules that matter before saving: unique CNIC, email and phone and one Principal per school against the database, and a hire date after the date of birth. On mobile, a student is signed in on one device at a time.

My role

I led the team of three and built across the web app's three portals and the mobile app myself. It went to production for 73 schools and 50,000+ students and took 1st place as our final-year project.

Highlights

Three role-based portals in one web app (head office, school and teacher), plus a React Native student app, on one Supabase backend
Row-level security so each of 73 schools only ever sees its own data
Eight standard monthly reports generated as PDFs, submitted once per school per month, with received and pending tracking and email reminders
Teacher transfers between schools that keep both schools' history
One active device per student, enforced in real time
Admissions that fill in family details from a father's CNIC and find siblings
Form rules checked against the database before saving: unique CNIC, email and phone, and one Principal per school
Attendance and marks saved as upserts, so a repeated save never duplicates

Full stack

Web portals

React 19 · Vite · Material UI · Formik · Yup · Recharts · jsPDF · JSZip

Mobile

React Native · Expo · Expo Router · EAS Build · Expo Notifications · React Native Maps

Backend

Supabase · PostgreSQL · Row-Level Security · Auth · Storage · Realtime · Edge Functions

Delivery

Vercel · EAS

Want to talk about this build?

I'm open to remote full-time roles and freelance work, and happy to walk through the decisions behind it.

Email me Send a message Résumé (PDF)

Key decisions.

Isolation in the database, not the app

Monthly reports as one submission, not loose files

A transfer moves the teacher, not their history

One phone per student

What I'd change

Head Office Portal

Hardest part

School Portal

Hardest part

Teacher Portal

Hardest part

Student App

Hardest part

Leading the requirements

Monthly reporting

Access and sessions

My role

Web portals

Mobile

Backend

Delivery

Want to talk about this build?