PuzzleRoster
1. About PuzzleRoster
PuzzleRoster is built for STEM education centers that offer in-depth study of specific subjects (physics and/or chemistry) to students from multiple schools. Centers like this face a specific scheduling challenge — class days and times need to be coordinated across every partner school contributing students to a group. The application makes it easier for different staff roles — administrators, teachers, lab assistants — to manage dozens of operational workflows: from partner-school coordination to assigning labs and classrooms based on equipment ordered by teachers. It gives center management the ability to manage teacher workload (total working hours, teaching hours, group sizes) and make a variety of operational decisions. The system also collects, processes, and analyzes all statistical data related to the center's operations.
- Unlimitednumber of partner schools
- Flexible groupingstudents from different schools
- Smart allocationof labs based on set parameters
- Built-in searchfor substitute teachers
- Personal dashboardfor every teacher
- Hallway displaysfor teachers and students
2. Who Uses the System
The system has eight roles. A staff member can be assigned more than one role at a time.
| Role | Who They Are | What They Do |
|---|---|---|
| Super Admin | Technical lead | Full access. Manages reference data, staff, and global settings. |
| Admin | Any staff member with admin rights — including as a secondary role | Reviews incoming schedule-change requests, clarifies details, and approves them. The only role that actually applies changes to the data — approve or reject. |
| Teacher | Instructor | Initiates schedule changes: reschedules, cancellations, substitutions, equipment orders. Everything goes through a request to the admin — teachers never edit data directly. |
| Lab Assistant | Lab staff member | Sees the list of upcoming lessons with ordered equipment. Prepares and delivers kits. Never creates or edits orders — only fulfills them. |
| Principal | Center principal | Full read access to the schedule and reports for any period. Manages teacher workload and assigns substitutes. Can also teach lessons (if also assigned as a teacher). |
| Vice Principal | Deputy principal for academics | Read access to the schedule and reports for teachers in their subject. Manages teacher workload and assigns substitutes. Can also teach lessons (if also assigned as a teacher). |
| Secretary | Secretary / office administrator | Read-only: full schedule, all reports, any period. Cannot make changes. |
| Center Coordinator | Center staff member | Handles school communication on behalf of the center. Supports partner schools, coordinates logistics. |
3. Core Processes
3.1 Annual Template & Propagation
The schedule has two levels. The upper level is the annual weekly template: each teacher's and group's standard workload. The lower level is the actual week-by-week schedule for the whole academic year, generated from that template.
archived status: it disappears
entirely from every schedule view, so teachers don't scroll past dozens of "ghost" cancelled lessons for
the rest of the year — yet it physically stays in the table, since foreign keys from request and audit
history don't allow deleting it.
3.2 Schedule Changes
The schedule is built for the academic year, but day-to-day adjustments come up constantly. The system routes them through a clear path:
3.3 Equipment Preparation
3.4 Substitute Teachers
If a teacher can't take a lesson, the admin, principal, or vice principal picks a substitute from the list of teachers available at that time with a matching subject profile. The lesson immediately shows as a substitution everywhere — in the schedule and on the corridor displays.
3.5 Populating the System (Data Workflow)
Data is entered in a strict order — each step depends on the one before it.
- 11 tables — initial system bootstrap
- 2 tables — experiment catalogs (physics and chemistry)
- 2 tables — requests and threaded comments
- 4 tables — day-editing locks and snapshots
- 2 tables — history and logs
- 1 table — access permissions
- 4 tables — email queue, rules, sender addresses, delivery status
- 1 table — system settings
- 2 tables — academic calendar
- 2 tables — staff detail
4. What the System Is Made Of
The system is built from several independent components, each responsible for its own part:
| Component | What It Does |
|---|---|
| Database | Stores all data and enforces every rule: validation, locking, change history |
| API server | Accepts requests, checks who's allowed to do what, and passes commands to the database |
| Web interface | PuzzleRoster, fully in Hebrew (RTL) |
| Corridor displays | Dedicated screens for the center's hallways — the day's live schedule for teachers, and a simplified version for students, with no personal login (authenticated via a static device key) |
| Notifications | An event-driven pipeline: every action (request submitted/approved/rejected, equipment order, substitution, server maintenance) writes an event to a queue inside the same transaction, so a failed write never blocks the action itself. A background sender resolves recipients by role, subject, and event participant from a rules table, renders an email, and delivers it via a transactional email provider — with automatic retries and an admin kill switch. Messenger channels (Telegram, WhatsApp) remain a possible future extension. |
| Infrastructure | Deployed in Docker containers on an AWS server, with automatic daily database backups |
5. Reliability & Security
- Every user gets an access token after logging in. The token is valid for 2 hours (auto-logout after 30 minutes of inactivity) and carries their role. No valid token, no access — no exceptions.
- Every rule is enforced at the database level — there's no way to bypass them through the interface.
- The backend connects to the database under a separate, restricted role with no direct
INSERT/DELETErights on lesson, log, or editing-session tables — any schedule change can only go through stored functions running with elevated rights (SECURITY DEFINER). - The change history can't be altered or deleted — by any user, in any role. This guarantees full transparency for every action in the system.
- The system automatically blocks scheduling conflicts.
- Internet access is architected on a Zero Trust basis — via Cloudflare Tunnel.
- 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.