Data Model

How the tables map to the concepts a school actually talks about.

The spine

Code

Academic year
   └── Term
        └── Class (year group)
             └── Section  ── capacity, class teacher
                  └── Student   ── one section per year

Almost everything else hangs off academic year. Change the year and you get a different timetable, different fee structures, different exams, a different roll — with last year's still intact.

A student over time

Code

students                    the person, and where they are NOW
   │
   └── student_enrollments  where they were, year by year
          academic_year · class · section · roll_no · outcome

Promotion moves the students row to the new class and writes an enrolment row for the year just finished. This is why last year's attendance and results still resolve to the right class after a roll-over.

A family

Code

guardians ──< student_guardians >── students
                is_primary
                can_collect

Many-to-many on purpose:

One guardian, several children → one login covers the family.
One child, several guardians → both parents get notifications; only one is primary.
can_collect is the school-gate question, kept separate from who receives circulars.

Money

Code

fee_heads          what can be charged        "Tuition", "Transport"
   ↓
fee_structures     a charge set for a class   Grade 6, Term 1
   ↓
fee_invoices       what one student owes
   ↓
fee_payments       what has actually been paid   ◀ the source of truth

The invoice's paid_cents and status are derived — recomputed from the payments below it on every write. Nothing sets them directly, which is why the balance a parent sees always matches the receipts they hold.

Code

fee_payment_attempts   a gateway payment in flight
     invoice · provider · amount_cents · gateway_ref · status

The attempt exists because a gateway payment happens in two moments — starting and settling — and the amount must live somewhere the client cannot reach in between. Settling credits the stored amount; the callback's numbers are never read.

Assessment

Code

Exam            "Term 1 Finals"        status gates ALL visibility
 └── Paper      per subject             max_marks, passing_marks
      └── Mark  per student             clamped to max_marks
           ↓
      Report card   computed: total, percentage, grade, rank_in_section
           ↓
      is_published  ──┐
      exam.status ────┴── BOTH required before a family sees anything

Marks are entered; report cards are computed. The grade comes from grade_scales, never from whoever typed the marks.

Attendance

Code

attendance
   student · date · period_id
                      │
                      ├── NULL  → the DAY register   → notifies guardians
                      └── set   → a lesson register  → does not

Unique on (student, date, period), so re-taking a register overwrites rather than duplicating.

The percentage a parent sees divides by recorded days, not calendar days. A day the school never took a register counts against nobody — the alternative would punish a family for the office's paperwork.

Reaching people

Code

circulars ── audience: all | parents | class | section
     └── circular_reads   who has seen it

school_notifications      role + person_id → the per-recipient feed
school_devices            push tokens, per role + person

A circular is written once and resolved to an audience at read time. A notification is per-recipient, because "has this parent seen it" is a per-person fact.

The parent feed

The feed is not a table. It is eleven queries merged at request time and ordered by timestamp:

Code

circulars · attendance exceptions · homework · graded submissions
exams · report cards · invoices · payments · leave · calendar · notifications
                              ↓
                    one chronological stream

Nothing is denormalised into a feed table, so a correction in the admin is reflected the next time the app pulls, with no fan-out job to go wrong.

Lookups

Code

master_items ── kind ──┬── blood_group, student_category, house
                       ├── designation, department, employment_type
                       ├── leave_type, exam_type, circular_category
                       └── payment_mode, transport_mode, room, book_category

One table, discriminated by kind, for every list an admin may edit.

Values the code depends on — attendance status, invoice status, admission stage — are TypeScript unions instead. An admin renaming "absent" would break queries; renaming a designation must break nothing. That distinction is the whole reason there are two mechanisms.