AG
TR

// work / sarnic

Sarnıç

A multi-tenant school-scheduling platform that replaces a 24-sheet, macro-enabled Excel workbook with a collaborative, OWASP-hardened web application for Turkish private schools.

Role
Solo engineer — backend, frontend, and database design
Date
Apr 2025 – May 2026

Stack

  • Spring Boot
  • Kotlin
  • PostgreSQL
  • Timefold Solver
  • React
  • TypeScript
  • Docker
  • Traefik

The problem

A Turkish exam-prep school was running its entire timetable from a single 24-sheet, macro-enabled Excel workbook covering 143 students, 20 teachers, 27 sections, and 24,444 schedule entries. It was fragile, single-user, and impossible to share safely with teachers, students, and parents.

Constraints

  • Real schedule data already existed in the workbook and had to be migrated, not re-entered by hand.
  • Admins, teachers, students, and parents need different, safe views of the same data — so multi-tenancy and role-based access are foundational.

Approach

A Spring Boot + Kotlin monolith on PostgreSQL with schema-per-tenant isolation backs a React + TypeScript SPA whose centerpiece is a 3-panel drag-and-drop schedule designer with undo/redo and real-time conflict detection. A Python importer migrates the original .xlsm workbook, and the Timefold constraint solver generates weekly schedules.

Key decisions

  • Model conflict detection in real time, not as a batch validation step

    Teacher, section, and student double-bookings plus teacher unavailability are caught as the user drags, with click-to-navigate red-ring resolution — so the schedule is always correct-by-construction rather than validated after the fact.

  • Harden to OWASP from the start

    Rate limiting, account lockout, refresh-token rotation, httpOnly cookies, HSTS and CSP headers, server-side XSS sanitization, dependency scanning, and a security-event audit log are built in — a multi-role platform holding student data cannot treat security as optional.

Architecture

A React and TypeScript single-page app reaches a Spring Boot and Kotlin monolith through Traefik. The monolith persists to schema-isolated PostgreSQL, runs the Timefold solver for automated schedule synthesis, and ingests the original Excel workbook through a one-off Python importer.

flowchart TB
  spa["React + TypeScript SPA<br/>3-panel drag-and-drop designer"]
  traefik["Traefik"]
  api["Spring Boot + Kotlin monolith"]
  solver["Timefold Solver<br/>ScheduleConstraintProvider"]
  db[("PostgreSQL<br/>schema-per-tenant")]
  importer["Python .xlsm importer<br/>(one-off migration)"]

  spa --> traefik --> api
  api --> solver
  api --> db
  importer --> db
A schema-per-tenant monolith with a Timefold solver and a one-off Excel importer.

Outcome

Sarnıç turned a fragile single-user spreadsheet into a real, collaborative, multi-tenant web application with role-aware views for admins, teachers, students, and parents, plus Excel/CSV/PDF export, a PWA shell, and Turkish-first bilingual UI. The Python importer migrated 13,069 entries from the original workbook on first import.

By the numbers

  • 24,444 Schedule entries in the source workbook
  • 13,069 Entries migrated on first import
  • 50 Undo/redo history depth
  • 5 Roles (admin · teacher · student · parent · viewer)
  • schema-per-tenant Tenant isolation

Deep dive

Sarnıç is named after the İstanbul cisterns — the underground reservoirs that collect water and distribute it where it is needed. The platform does the same with school schedules: it collects timetable data and distributes the right view to admins, teachers, students, and parents.

From a 24-sheet spreadsheet to a real application

The project was born from a concrete pain: a Turkish exam-prep school was managing 143 students, 20 teachers, 27 sections, and 24,444 schedule entries out of a single macro-enabled Excel workbook with 24 sheets. A spreadsheet that large is fragile, single-user, and unsafe to hand to teachers or parents. The job was to turn it into a real, collaborative web application without losing the data already in it — so a Python importer reads the original .xlsm directly and migrated 13,069 entries on the first import.

The schedule designer

The centerpiece of the frontend is a 3-panel drag-and-drop schedule designer with undo/redo backed by a 50-action history. The important design choice is that conflict detection runs in real time as the user drags: teacher, section, and student double-bookings — plus teacher unavailability — surface immediately with click-to-navigate red-ring resolution. The schedule is correct-by-construction rather than validated after the fact, and the Timefold constraint solver (ScheduleConstraintProvider, pinning support, SolverController) can synthesize weekly schedules automatically.

Multi-tenant and OWASP-hardened

Because the same data must be visible to five different roles, multi-tenancy and access control are foundational, not bolted on: schema-per-tenant isolation, tenant-scoped JWTs, a role-aware sidebar, and useCanEdit guards. Security is built in — rate limiting, account lockout, refresh-token rotation, httpOnly cookies, HSTS + CSP headers, server-side XSS sanitization, dependency scanning, and a security-event audit log.

Rounded out with Excel/CSV/PDF export (strategy-pattern renderers and print templates including the MEB format), a PWA shell, dark/light/system theming, and a Turkish-first bilingual UI across 16+ pages, Sarnıç is the spreadsheet’s replacement rather than its companion. The source is on GitHub.

All case studies