ERP Scheduling System for an Education Center

Context and Problem

After I started working with a science education center for physics and chemistry, I found the organization running on a legacy system built on Excel. It kept crashing, froze under load, and made daily operational changes painful and error-prone.

That's exactly why I decided to build a system that would make schedule and resource management more reliable and predictable. In my free time outside my main job, I built my own Scheduling System, and in mid-August, just before the new school year, I deployed and configured a dedicated instance of it for the center, free of charge, to make my colleagues' daily work easier.

My experience with relational databases shaped the project's core architectural principle: schedule and resource changes should be handled as transactions, not as simple spreadsheet edits.

Data integrity was pushed down to the database level, the change history is immutable via an Audit Log, and the architecture allows safely restoring the schedule state for any given day.

My Role

System Overview

An ERP system for schedule and operations management:

• Annual template + a generated year-long schedule
• Day-level schedule editing with freeze / unfreeze / rollback
• Teacher substitutions
• Lesson cancellations and reschedules
• Creating new lessons and extra activities
• Lab equipment ordering workflow
• Request queue (a single-window approval workflow)
• Email notifications for every key event — schedule changes, room reassignments, teacher substitutions, lesson cancellations, lab/equipment orders, request approvals, plus operational alerts for admins
• Granular role-based access
• Corridor TV displays for teachers and students
• Fully immutable audit log

Technology stack:

• PostgreSQL — chosen for native support of EXCLUDE USING GIST. At the database engine level, this ensures no overlapping time intervals and automatically rules out race conditions on concurrent requests.
• Logic in the database — moving invariants into the database gave a single source of truth and data integrity regardless of the entry point (API, background jobs, or a direct admin session).
• Node.js + Express — a thin, stateless API gateway limited to authentication and request routing, with no duplicated business logic.
• Vue 3 + PrimeVue — cut time-to-market thanks to a ready-made ecosystem of complex UI components for admin screens.
• Cloudflare Tunnel — isolated the infrastructure on a Zero Trust basis, removing the need to expose a public IP or open any inbound ports.

Project structure:

Repository structure — the system was designed from day one with a clean separation between the application, the database logic, and the infrastructure.

The system in real life:

Corridor display in the teachers' room — live, on the wall of the education center.

Corridor display for students — live, on the wall of the education center.

Production monitoring — the system is under continuous external uptime monitoring. 100% uptime over the last 30 days; 99.995% since September 1, 2026, with a single 5-minute incident during a planned version deployment (database migration and backend/frontend container restart). Continuously up ever since.

Email delivery infrastructure — notifications are sent through a verified domain via a transactional email provider (Resend), ensuring reliable delivery and proper authentication (SPF/DKIM).

System Interface

Click any screenshot to view it full-size.

Daily Gantt chart — the day's live schedule by room. Subject color-coding, substitute highlighting, current-time indicator.

Annual template (Basic Schedule) — the source of truth. Changes propagate safely to the generated yearly schedule through a two-phase algorithm.

Teacher dashboard — a personal workspace: upcoming lessons, equipment requests, substitution history, and work schedule.

Admin dashboard — the central "single window." Teachers only submit requests; admins review, adjust, and apply the changes.

Lab assistant dashboard — an operational overview: equipment orders and notes for a selected day.

Audit log — a fully immutable history of every significant action. Even a superadmin cannot edit or delete a record.

Email notification rules — who gets notified about what. Every event type (new request, approval, equipment order, substitution, server maintenance...) can be routed independently to specific roles and to the people directly involved (request author, lesson teacher, substitute, acting teacher) — configured from the Control Panel, with a global kill switch to pause all email instantly.

Real system mailbox — incoming notifications sort themselves by event type (equipment order, lesson cancellation, room change, substitution...) into a dedicated folder per type, with its own sender address.

Equipment order confirmation — with item codes and direct links to the Hemda material library.

Display board for students — a public screen with the live schedule.

Engineering Culture — Code and Documentation

One of the project's core principles: logic and decisions are explained directly in the code — not only what a function does, but why it exists and what breaks if the invariant is violated.

1. Clear separation of structure and business logic

schema.sql — an explicit rule: this file holds structure only. Business logic lives in the modular files.

2. Room assignment with explicit business rules

campus._find_room() — room-priority rules for physics and chemistry are documented and encoded directly in the query.

year_schedule.sql — the file header explains the purpose of every key function and the freeze / unfreeze day flow.

3. Quality Assurance & Invariant Verification

391 automated regression checks (API + database layers) run before every deploy — verifying request approval workflows, teacher substitutions, day freeze/rollback mechanics, scheduling-conflict prevention (EXCLUDE USING GIST), equipment orders, and the notification-rules matrix.

4. Release & Operations Discipline

Each of the ~20 production releases over 9 months follows the same protocol: a migration rehearsal on a fresh copy of the production database, Docker images tagged :rollback before rebuilding, a backup taken right before applying changes, and a written deploy log on the server. The actual container-switch window runs 13 to 55 seconds.

The full-outage recovery plan isn't just theory: the entire cycle — wiping the volume, a clean init from scratch, restoring a real production dump into a temporary database — has been run end to end, with key tables (staff, lessons, requests, permissions) checked row by row against the live database.

5. User Adoption & Change Management

To ensure a smooth transition from legacy Excel, I built a gradual rollout strategy:

• Early stakeholder engagement and demonstrations. During development, I regularly showcased working prototypes to colleagues, using concrete examples to demonstrate how the system would eliminate daily chaos and routine. Initial indifference quickly evolved into genuine anticipation of the release.
• Smooth adaptation two weeks before launch. I closely monitored staff reactions and assessed their openness to change. Seeing that the vast majority was positive, I approached the director with a proposal: avoid overcomplicating the transition with redundant parallel work in two systems, as dual-entry would only frustrate the team. The system turned out objectively simpler and more convenient than the old one, so I suggested starting the school year directly on it, and the director agreed. To give the team time to get comfortable, sandbox access was opened to all employees in mid-August, allowing everyone to explore the UI and their personal dashboards with zero risk. Simultaneously, the new schedule view was broadcast across all corridor display screens — giving the team two weeks before September 1 to visually adapt to the new format.
• Hands-on training. I conducted several rounds of hands-on training for teachers and lab assistants: navigating personal workspaces, requesting lab equipment in a couple of clicks, submitting reschedule requests, and tracking approval statuses in real time.
• Respect for existing habits and paper notes. I accounted for the fact that some lab assistants and teachers prefer holding a physical sheet — ticking off prepared experiments with a pencil or making margin notes. This exact functionality (equipment readiness checkboxes, lesson notes) is built into the system itself — some enter it directly into the interface, while others mark it on paper first and update the system later. That's why I added the ability to export to Excel, for printing, the information shown on the screen.
• Outcome. Thanks to the intuitive UI, even the most conservative staff members adapted with virtually no confusion. By the end of September (the first month of the academic year), the system was fully and seamlessly woven into all daily operations of the science center.

Results

• The system has been running successfully in production since September 2026
• Handles the center's real daily operational load
• Critical invariants (schedule conflicts, access rights, audit) are enforced at the database level
• A complete, immutable history of every significant action
• About 50 staff members use the system every day