Sarnıç is a scheduling platform for Turkish private schools (dershane, özel okul, kurs), named after the İstanbul cisterns that collect and distribute water. It was built to replace one very specific artifact: a 24-sheet, macro-enabled Excel workbook called atavip_timetable_v82.xlsm, covering 143 students, 20 teachers, 27 sections, and 24,444 schedule entries across normal and accelerated modes. The stack is Java 21 and Spring Boot 3.5 on PostgreSQL 17, with React 19 on the frontend, deployed on Hetzner via Docker, Traefik, and Let’s Encrypt. Source: github.com/ahmetabdullahgultekin/Sarnic. Live: sarnic.rollingcatsoftware.com.
The Excel workbook was on version 82 because it had been iterated on for years. It worked until it didn’t: referential drift (teacher code MS on sheet 1 vs. M.S. on sheet 17 breaks a vlookup silently), no concurrent editing, and conflict detection that amounted to staring at the grid and hoping. Replacing it meant migrating the existing data without re-entering it by hand. I wrote a Python importer using openpyxl to read the .xlsm directly. The first run migrated 13,069 of the 24,444 entries end-to-end. A separate verification script re-read the Excel and diffed it row-for-row against the database: 13,069/13,069 matched.
For schedule generation I used Timefold (the open-source successor to OptaPlanner). The domain has 10 hard constraints (teacher cannot double-book, section cannot have two activities at the same slot, and so on) and 7 soft constraints (curriculum compliance, teacher hour fairness, even distribution). The ScheduleConstraintProvider declares them in fluent Java; pinning support lets administrators lock specific entries and have the solver fill around them. The architecturally interesting lesson came later: I had 490 green unit tests but the solver never actually persisted its results until I traced the bug against a real PostgreSQL instance. Tests pass in isolation; the integration only breaks under the real database.
Multi-tenancy uses schema-per-tenant PostgreSQL isolation: each school gets its own tenant_{id} schema, and a TenantContext is set by a TenantFilter on every request. There is no WHERE tenant_id = ? clause that can be forgotten. Five RBAC roles (admin, teacher, student, parent, viewer) layer on top, with 67 @PreAuthorize annotations across 21 controllers and a useCanEdit hook on the frontend. Security hardening follows OWASP ASVS Level 2: rate limiting, account lockout, refresh-token rotation, httpOnly cookies, HSTS and CSP headers, server-side XSS sanitization, and a security-event audit log.
- architecture
- constraint-solving
- multi-tenant
- spring-boot