School Management System Architecture: Lessons from Building an ERP for a Local Market

Over the past few years I built M Smart School, a school management system, and I am now rolling it out in the real world: one pilot school, 300+ students. Before starting, it looked like "plain CRUD" to me: a student list, a grade book, attendance. In practice it turned out to be a full ERP — with its own domain, a time dimension, and, most importantly, users who are far from technology. Below are the architecture decisions I made along the way and the lessons behind them. No internal secrets — just general engineering takeaways.

Domain model: not tables, but relationships and time

At first glance the domain is simple: student, class, subject, timetable, attendance, grade — six tables. But the real complexity lives not in the entities themselves, but in the relationships between them and in the time dimension:

• A student does not simply "belong" to a class — they study in that class during a specific academic year. Next year "5-A" becomes "6-A", and the student may transfer to another school.
• A grade is bound not to the pair "student + subject" but to the triple "student + subject + term". A grade without a term is meaningless — you cannot tell which period it was given for.
• Attendance is recorded against a concrete lesson, and a lesson is a projection of the timetable onto a concrete date. If the timetable changes, the attendance history must not break.

That is why the grades table carries the time context from day one:

CREATE TABLE grades (
  id         BIGSERIAL PRIMARY KEY,
  school_id  BIGINT NOT NULL,   -- tenant
  student_id BIGINT NOT NULL,
  subject_id BIGINT NOT NULL,
  term_id    BIGINT NOT NULL,   -- term: a grade cannot live without time context
  lesson_id  BIGINT,            -- which lesson it was given in (optional)
  value      SMALLINT NOT NULL CHECK (value BETWEEN 1 AND 5),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_grades_lookup ON grades (school_id, student_id, term_id);

My biggest lesson: do not make the "student — class" link a direct foreign key. Instead you need an enrollment table: student, class, academic year, and a date range. Then a student who arrives or leaves mid-year, or moves between classes, is all expressed without destroying history.

Multi-tenant: a database per school or one shared database

In a system serving multiple schools, this is the first big decision. Both paths have a price:

• Separate databases: strong isolation, easy backup/restore of a single school, minimal risk of data leakage. But N schools means N migrations, N monitoring targets, and growing costs for connections and infrastructure.
• Shared database + tenantid: one migration, cheap infrastructure, cross-school statistics come from a plain query. The main risk — a single forgotten WHERE schoolid can expose another school's data.

I chose the shared database + tenantid. The logic is simple: the team is small, there are few schools for now, and the operational burden (feeding N databases) costs more at this stage than the isolation benefit. To reduce the risk, the tenant filter went from a good habit to a mandatory layer: all queries go through a repository layer that simply does not allow building a query without schoolid. In PostgreSQL you can also enable Row Level Security as an extra barrier. An important point: since the schema is identical, if a large client demands a separate database in the future, migrating remains a realistic and cheap path — this decision is reversible.

Roles and permissions: keeping RBAC simple

There are four core roles: director, teacher, parent, student. The main temptation here is to write a universal permission engine "for the future". In practice, a role → permissions map and one scope check were enough:

const PERMISSIONS: Record<Role, string[]> = {
  director: ["reports:read", "schedule:write", "users:manage"],
  teacher:  ["attendance:write", "grades:write", "schedule:read"],
  parent:   ["grades:read", "attendance:read"],
  student:  ["grades:read", "schedule:read"],
};

function can(user: User, permission: string): boolean {
  return PERMISSIONS[user.role].includes(permission);
}

// Scope is checked separately: a parent sees only their own child
function inScope(user: User, studentId: number): boolean {
  if (user.role === "parent")  return user.childIds.includes(studentId);
  if (user.role === "student") return user.studentId === studentId;
  return true; // director and teacher see within their own school
}

Separating the permission ("what they can do") from the scope ("over whom") keeps the code flat. A new role — say, a deputy head — is one extra line in the map. Per-object ACLs, dynamic roles and other complexity get added when they are needed; during the pilot they never were.

Local constraints: unstable internet and low technical literacy

These two facts shaped the architecture more than any technology choice:

• Pages are light: server-side rendering, minimal JavaScript. Every page must open even on slow mobile internet.
• Forms are small: marking attendance takes 2-3 taps, long forms are split into steps.
• Saving is confirmed explicitly: instead of an optimistic UI, a clear "Saved" signal. This matters for users to trust the system.
• If the connection drops, entered data is not lost: the form state is stored locally and resubmitted when connectivity returns.

I thought a lot about a full offline-first PWA, but deliberately did not build one: managing sync conflicts is too expensive a complexity for a small team. A simple retry plus draft saving closed most of the problem.

The general principle: simplicity beats feature count. Every new button is a teacher-training cost and an extra stream of support questions. When a feature is requested, my first question is: "can we live without it?"

Reports: compute in real time or prepare overnight

Management needs aggregates: attendance percentage per class, achievement dynamics per term, per-subject breakdowns. With 300 students, real-time queries over raw tables work just fine — yet I still chose daily aggregate tables.

There are two reasons. The first is technical: report queries should not compete during the day with the main write stream (attendance, grades). The second is a product one: for a director, "as of yesterday" is completely sufficient — nobody expects second-level precision in school reporting.

-- Nightly cron: daily attendance aggregate per class
INSERT INTO attendance_daily (school_id, class_id, date, present, absent)
SELECT school_id, class_id, date,
       COUNT(*) FILTER (WHERE status = 'present'),
       COUNT(*) FILTER (WHERE status = 'absent')
FROM attendance
WHERE date = CURRENT_DATE
GROUP BY school_id, class_id, date
ON CONFLICT (school_id, class_id, date) DO UPDATE
   SET present = EXCLUDED.present,
       absent  = EXCLUDED.absent;

A bonus effect: report pages that read from aggregate tables open very fast — which is especially appreciated on weak internet.

Lessons from the pilot rollout

We did not launch into ten schools at once, but into one pilot school — and it proved to be the best decision:

• Real data stress-tests the schema. A student arriving mid-year, children with identical names, class changes — all of it surfaced in the first weeks, and the enrollment model paid for itself.
• Training matters more than features. Even the best feature does not exist if the user cannot find it. Live demos and short guides took no less time than writing code.
• Keep the feedback loop short. We reworked the attendance module in the very first week — teachers pictured the process differently than we expected. Without a pilot we would have learned this in ten schools at once.
• Invest time in internal admin tools. Data import, fixing mistakes, transferring a student — without these, every small problem lands in the developer's hands.

Conclusion

The hard part of a school ERP is not the technology. The hard part is the domain (relationships with a time dimension), the people (users far from technology), and the environment (unstable internet). If the architecture respects these three, you can build a reliable system on a plain monolith, a shared database, four-role RBAC, and overnight aggregates. Add complexity when a problem demands it — not in advance.