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
  • Java 21
  • 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 + Java 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. Errors surface immediately rather than accumulating silently until a manual check.

  • 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 Java 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 + Java 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: 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 key 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. Errors do not accumulate silently. 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. The source is on GitHub.

All case studies