SchoolHub — School Management System

A working MVP for private school operations: attendance, grades, parent notifications, and reporting — built with Next.js 14 (App Router) and Supabase (Postgres + Auth + Row Level Security).

This codebase covers three roles end-to-end: School Administrator, Teacher, and Parent. (Student self-login was intentionally left out of this pass — see "What's not included" below.)

What's included

• Auth & roles — Supabase Auth with a profiles table (school_admin, teacher, parent, plus super_admin for future multi-school use), enforced by Postgres Row Level Security, not just app-layer checks.
• Student management — registration, class assignment, profile pages, guardian linking.
• Attendance — teachers mark Present/Absent/Late/Excused per class per day; admins get trend charts, per-student summaries, and a chronic- absenteeism flag (≥20% absences in the selected window).
• Grades — teachers create assignments/quizzes/exams per subject and enter scores; parents and the admin report view see them in real time.
• Notifications — in-app notification bell (live via Supabase Realtime) fires automatically when a student is marked absent/late, a grade is posted, or an announcement is published. Email/SMS are wired as stubs (see below) so you can drop in Resend/Twilio without restructuring anything.
• Announcements — school-wide, class-targeted, or role-targeted, with optional scheduling.
• Reports — PDF report cards (jsPDF) and Excel exports (SheetJS) for students and attendance summaries.
• Audit log — append-only table recording key actions (student created, attendance marked, grades updated, announcements published, etc.), visible to admins.

What's not included (by design, for this pass)

• Student self-login (admin/teacher/parent only).
• Live email/SMS sending — the notification records and the in-app inbox are fully functional; wiring a provider is a ~10 line change (see src/lib/notifications.ts).
• Billing/subscription plan enforcement (the schools.plan column exists but nothing gates features on it yet).
• Multi-school super-admin console (the data model supports multiple schools, but there's no UI for a super admin to manage them).

---

1. Create a Supabase project

1. Go to supabase.com → New Project.

2. Once it's ready, open SQL Editor and run the three migration files in supabase/migrations/ in order:

   • 0001_init_schema.sql
   • 0002_rls_policies.sql

   (Paste each file's contents in and click Run. If you have the Supabase CLI installed, supabase db push does this automatically.)

3. Go to Project Settings → API and copy:

   • Project URL
   • anon public key
   • service_role key (keep this secret — server-only)

2. Configure environment variables

cp .env.example .env.local

Fill in NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY, and SUPABASE_SERVICE_ROLE_KEY from the previous step.

3. Install and run

npm install
npm run dev

Visit http://localhost:3000 — you'll be sent to /login.

4. Seed demo data (recommended)

This creates a demo school with a school admin, two teachers, three parents, two classes, six students, two weeks of attendance, and a graded assignment — so you have something to click through immediately.

npm run seed

It prints the demo logins when done (password for all: Password123!). Re-running it is safe — it skips users that already exist, but it will create a second copy of the school/classes/students each time, so only run it once per fresh database.

5. Inviting real users

From the Admin → Teachers or Admin → Parents pages, "Invite" sends a Supabase Auth invite email to set a password. This requires your Supabase project's email sending to be configured — by default Supabase sends a limited number of auth emails for free; for production, configure a custom SMTP provider in Project Settings → Auth → SMTP Settings.

6. Wiring up email/SMS notifications (optional)

In-app notifications work out of the box. To also send email or SMS:

1. Add RESEND_API_KEY (and NOTIFICATIONS_FROM_EMAIL) and/or TWILIO_ACCOUNT_SID / TWILIO_AUTH_TOKEN / TWILIO_FROM_NUMBER to .env.local.
2. Open src/lib/notifications.ts and uncomment the example fetch/Twilio calls inside sendEmail / sendSms.
3. Call sendEmail(...) / sendSms(...) alongside notifyUsers(...) / notifyGuardiansOfStudent(...) wherever you want a channel beyond in-app (e.g. in src/app/teacher/actions.ts and src/app/admin/announcements/actions.ts).

Project structure

src/
  app/
    login/                  Public login page
    admin/                  School admin: dashboard, students, teachers,
                             parents, classes, attendance reports, announcements
    teacher/                Teacher: dashboard, per-class attendance + grades
    parent/                 Parent: dashboard, per-child detail
  components/
    ui/                     Shared primitives (Button, Card, Input, Badge, StatCard…)
    layout/                 AppShell (sidebar/topbar), NotificationsBell
    attendance/             AttendanceGrid (teacher attendance entry)
    grades/                 GradeEntryTable
    charts/                 AttendanceTrendChart (Chart.js)
    reports/                PDF/Excel export buttons
  lib/
    supabase/               Browser/server/middleware Supabase clients
    auth.ts                 requireProfile() route guard, audit logging
    notifications.ts        In-app notification fan-out + email/SMS stubs
    exports.ts              jsPDF / SheetJS export helpers
  types/                    Domain types + hand-written Database type
supabase/
  migrations/                0001 schema, 0002 RLS policies
scripts/
  seed.mjs                  Demo data seed script

Tech stack

• Next.js 14 (App Router, Server Actions, Server Components)
• Supabase — Postgres, Auth, Row Level Security, Realtime (for the notification bell)
• Tailwind CSS for styling
• Chart.js for the attendance trend chart
• jsPDF / SheetJS for PDF and Excel report generation
• TypeScript throughout, including hand-written Supabase types in src/types/database.types.ts (swap for npm run supabase:types once your schema is finalized and you have the Supabase CLI linked)

Security model

All access control is enforced at the database level via Postgres RLS policies (supabase/migrations/0002_rls_policies.sql), not just in the Next.js app:

• Teachers can only read/write attendance and grades for classes they actually teach (checked via homeroom_teacher_id and class_subjects).
• Parents can only see students they're linked to via parent_students.
• Everyone is scoped to their own school_id — there's no cross-school data leakage even if application code has a bug.
• service_role (used only in server-only admin actions like inviting users) bypasses RLS, as intended — never expose that key to the browser.

Known limitations / next steps

• Embedded-relation TypeScript types (e.g. students(classes(name))) use a hand-written Database type without foreign-key Relationships metadata, so a handful of joined queries are cast with as any at the call site for pragmatism. Running npm run supabase:types against your live project (once linked via the Supabase CLI) will replace this file with fully accurate generated types and let you remove those casts.
• Report cards currently average raw assignment scores; weighting by assignments.weight for a true term GPA is a small addition in src/lib/exports.ts / the student detail pages.
• No automated tests yet — recommend adding Playwright for the core flows (mark attendance → parent sees notification → grade posted → parent sees grade) before going to production.