Entity Relationship Diagram

The core of the school domain. Platform tables (users, settings, media, pages) are omitted for clarity.

Academic backbone

Code

academic_years ──┬──< terms
                 ├──< school_classes ──< class_sections
                 │                          │
                 ├──< timetable_slots ──────┘
                 ├──< school_calendar
                 └──< fee_structures, exams, homework, student_enrollments

school_classes ──< class_subjects >── subjects
class_subjects ──> staff            (the allocated teacher, optional)

school_periods ──< timetable_slots >── subjects
                                   └── staff   (teacher)
                                   └── master_items (room)

A timetable_slot is unique on (academic_year, section, day, period). Teacher and room clashes are refused at write time — the same teacher cannot appear in two sections in one period.

People

Code

students ──< student_guardians >── guardians
   │              (is_primary, can_collect)
   ├──< student_enrollments      one row per student per academic year
   ├──< student_documents
   ├──> school_classes           current placement
   ├──> class_sections
   └──> master_items             blood group · category · house · transport mode

staff ──< staff_qualifications
      ├──< staff_documents
      ├──< salary_structures ──< payslips
      └──> master_items         designation · department · employment type

student_enrollments is what makes history survive promotion: the student row moves to the new class, and last year's placement stays exactly where it was.

Attendance

Code

attendance ──> students
           ├──> class_sections
           ├──> school_periods   NULL = the DAY register
           └──> subjects         set only for a period register

staff_attendance ──> staff       with optional check_in / check_out

Unique on (student, date, period), which is what makes re-taking a register an overwrite rather than a duplicate.

Fees

Code

fee_heads ──< fee_structure_items >── fee_structures ──> school_classes
                                             │
fee_invoices ──< fee_invoice_items ──────────┘
     │  ├──> students
     │  ├──> academic_years, terms
     │  └──< fee_payments            ◀ the ledger; the source of truth
     └──< fee_payment_attempts       a gateway payment in flight

fee_concessions ──> students, fee_heads

fee_invoices.paid_cents and .status are derived from fee_payments, recomputed on every write. They are never set from a request. fee_payment_attempts holds the server's own record of what was asked for, so settlement credits that amount and never a number from a gateway callback.

Assessment

Code

exams ──< exam_schedules ──> subjects, class_sections
   │            │
   │            └──< marks ──> students
   └──< report_cards ──> students
                     └──> terms

grade_scales        percentage band → grade, points

A report card is computed, not entered. It is visible to a family only when report_cards.is_published and exams.status = 'published'.

Homework and communication

Code

homework ──> class_sections, subjects, staff
    └──< homework_submissions ──> students
                              └──> staff  (graded_by)

circulars ──< circular_reads          (role, person_id)
          └──> school_classes / class_sections   when the audience is narrowed

school_notifications      role + person_id — the per-recipient feed the apps read
leave_requests ──> students | staff
message_threads ──< thread_messages
school_devices            push tokens, per role + person

circulars.audience is one of all, parents, class, section; the class or section column is only consulted for the narrower two.

Admissions

Code

admission_applications ──> academic_years, school_classes
                       ├──> master_items   relation · source
                       └──> students       set on conversion

Guardian details are held flat on the application, not as a guardians row: an enquiry that never converts must not leave a login-capable account behind. Conversion creates the student, creates or reuses the family's guardian, and stamps student_id — which is what makes a second conversion attempt return the existing student rather than a duplicate.

Facilities

Code

library_books ──< library_copies ──< library_loans ──> students | staff
transport_routes ──< transport_stops
                 └──< transport_assignments ──> students, transport_vehicles
hostels ──< hostel_rooms ──< hostel_allocations ──> students
        └──< hostel_visitors
inventory_items ──< inventory_movements
                └──> inventory_suppliers

A loan is against a copy, not a title — which is what stops two students being issued the same physical book.

Lookup data

master_items holds every admin-editable list, discriminated by kind: blood groups, designations, departments, employment types, qualifications, student categories, houses, transport modes, admission sources, guardian relations, leave types, exam types, circular categories, payment modes, book categories, rooms.

Structural values — attendance status, invoice status, admission stage — are not here. They are TypeScript unions, because an admin renaming "absent" would break queries, whereas renaming a designation must not.