Replacing a 24-sheet macro Excel: how Sarnıç became a constraint-solver

Sarnıç — Ottoman Turkish for cistern, the structure that collects and distributes water — is a multi-tenant scheduling platform for Turkish private schools (dershane, özel okul, kurs). It was born from one very specific pain point: a real exam-prep school running its entire weekly operation out of a 24-sheet, macro-enabled Excel workbook called atavip_timetable_v82.xlsm — 143 students, 20 teachers, 27 sections, 24,444 schedule entries across normal and accelerated (Hızlandırma) modes. The platform now ships under RollingCat Software; the source is at github.com/ahmetabdullahgultekin/Sarnic, the API at sarnic-api.rollingcatsoftware.com, Java 21 / Spring Boot 3.4 on PostgreSQL 17 with React 19 on the frontend, deployed on Hetzner via Docker + Traefik + Let’s Encrypt.

This piece is about why Excel works until it doesn’t, and what we had to build to replace it without losing what made it work.

1. Excel works, until referential drift kills it

Excel was working for the school. That is the most important thing to understand before deciding to replace it. The v82 in the filename was not a joke — it was version 82 of a workbook that had been iterated on for years, with bespoke macros for every recurring task. Teachers, sections, subjects, weekly hours, fractional curriculum (1.5 hours, 2.5 hours), 14 activity types from D (sınıf dersi) through RT (system) — all of it fit in spreadsheets. The shape of the domain matched the shape of the tool.

What killed it was three things, in this order:

Referential drift. When a teacher’s code is MS on sheet 1 and M.S. on sheet 17, a vlookup silently returns nothing. One letter, one period, one space — the workbook never tells you which row is broken.
No concurrent edit. The administrative office is one person editing while the rest of the building waits for them to save and close.
Conflict detection by stare-and-pray. A teacher double-booked at 13:40 on Tuesday across two sections is technically visible in the grid. In practice nobody catches it until the teacher walks into the wrong room.

Don’t model the schedule as cells; model it as a constraint problem. That is the one sentence we kept coming back to.

2. Modeling the schedule as a constraint problem

The domain has 10 hard constraints (H1-H10) and 7 soft constraints (S1-S7), enumerated in our internal scheduling-constraints spec. Hard constraints are non-negotiable: a teacher cannot be in two rooms at once (H1), a section cannot have two activities at the same slot (H2), the week is locked once the registrar locks it (H8). Soft constraints are trade-offs: curriculum compliance (S1), teacher max-hours fairness (S2), even distribution across the week (S3), no back-to-back identical activities (S4), lunch breaks (S5).

We picked Timefold (the open-source successor to OptaPlanner) and modeled the domain as ScheduleEntry (the planning entity) with TimeSlot and Teacher as planning variables. The ScheduleConstraintProvider declares constraints in fluent Java; the solver does the rest. Pinning support was non-negotiable — administrators must be able to lock a few entries and have the solver fill around them.

The architecturally important bit is that the domain layer has zero framework imports. Timefold annotations live on a thin domain-side class, not on the JPA entity. JPA entities live in adapter/out/persistence/entity/ and are mapped to/from domain objects explicitly. This is hexagonal architecture not as decoration but as the property that lets us swap the solver later without touching anything else.

3. The Excel importer was the hardest part

People underestimate this. Building the constraint model is the fun part of the project; reading 24 sheets of merged cells, formula references, and locale-specific date parsing is the part that takes weeks.

The importer is a Python script (scripts/seed_from_excel.py) using openpyxl to read the .xlsm and Apache POI (SAX streaming) on the backend for the in-app upload path. The first import migrated 13,069 entries end-to-end on the very first run — and we count that as a small miracle. The verification harness (scripts/verify_excel_db.py) re-reads the Excel and diffs against the database row-for-row: 13,069/13,069 matched, all 30 weeks matched, all 18 teachers matched, 7/7 sample spot checks passed.

The edge cases that nearly broke us:

Merged cells. A merged 3x1 block looks like one value to a human and three values to a parser. We treat the top-left as canonical and fill down; this is wrong about 2% of the time and we have a manual-fix workflow for those.
Formula references. =Ogt!B12 only resolves if you’ve also loaded the Ogt sheet. Order of ingestion matters.
Locale dates. Turkish Excel writes dates as dd.mm.yyyy; openpyxl on a non-Turkish machine sometimes returns datetime objects, sometimes strings. Always parse defensively.

Never re-save the .xlsm with openpyxl. It corrupts VBA macros and complex formatting silently. The original file is read-only by policy; all our work is in docs/original/, untouched.

4. Multi-tenant from day one

We made this decision before writing the first line of code: schema-per-tenant PostgreSQL multi-tenancy. Each school gets its own tenant_{id} schema; a shared _shared schema holds platform-level data (plans, pricing, audit config). A TenantContext is virtual-thread-scoped and set by a TenantFilter on every request. Hibernate’s MultiTenantConnectionProvider swaps search_path on the connection.

The reason for this choice: there is no WHERE tenant_id = ? clause that someone can forget to add. Cross-tenant data leakage is the single most common multi-tenant SaaS bug, and the schema-per-tenant model makes it structurally impossible — a query against the wrong schema simply returns nothing.

RBAC layers on top: Admin, Teacher, Student, Parent, Viewer. 67 @PreAuthorize annotations across 21 controllers — that’s not a flex, it’s a remediation count from our 2026-04-07 production-readiness audit, where we found a previous version had been missing them. The frontend mirrors this with a useCanEdit hook on every CRUD module.

5. OWASP-hardened, because parents trust you with their kids

The threat model for a school scheduler is small but not zero. Parents log in via a public link to view their child’s schedule. Students log in to see their tutoring sessions. A leaked token is not a bank-account breach, but it is a child’s daily location.

The hardening checklist (audited in docs/security-hardening-design-2026-03.md):

Rate limiting and account lockout (3 failed attempts confirmed working end-to-end).
Refresh-token rotation with httpOnly cookies — no tokens in localStorage.
Security headers: HSTS, CSP, X-Frame-Options, Permissions-Policy.
Server-side XSS sanitization via a Jackson deserializer (defense in depth, not just CSP).
Parameterized queries everywhere; no string concatenation for SQL.
OWASP dependency scanning in CI.
Security event audit logging in a cascade-isolated table.

What we’d do differently

Build the importer first, the UI second. We did the opposite for two weeks and burned them. The shape of the domain is hiding in the Excel; you find it by parsing, not by drawing wireframes.
Pick the constraint library before modeling the constraints. Timefold’s API influences how you express H1-H10; a generic CSP solver would have led to a different model. Let the tool shape the spec.
Treat verification as a first-class artifact. scripts/verify_excel_db.py saved us four times in the first month. Build the diffing tool the same day you build the importer.

Reading list

The Timefold documentation — start with the school-timetabling quickstart, it maps almost 1-to-1 onto our domain.
OWASP ASVS 4.0 — we used Level 2 as the bar.
Designing Data-Intensive Applications, ch. 5 (Replication) and ch. 7 (Transactions) for the multi-tenant connection-pool model.

Repository: github.com/ahmetabdullahgultekin/Sarnic. Live demo: sarnic.rollingcatsoftware.com (login admin / admin). Swagger UI: sarnic-api.rollingcatsoftware.com/swagger-ui.

architecture
constraint-solving
multi-tenant
spring-boot