Villa Aurora, Florence
Libretto
Villa Aurora kept its academic records in an application that had grown for twenty years. I designed and built its replacement — data model, API, interface, security and the migration of every record — and in October 2026 it became the faculty’s source of truth.
The problem
The old application sat on a database that had grown table by table for two decades: one table per degree programme, yes/no flags stored as −1 and 0, and text in several encodings at once. Characters it couldn’t decode were overwritten, so accents in names and course titles were being lost for good.
The secretary’s office needed a system it could trust: grades on the Italian 18–30 scale, credits (CFU) adding up per curriculum, cohorts advancing year by year — and teachers and students able to see exactly their own part of it.
Constraints
- Italian first, English second — and not just the interface: catalog data itself is bilingual.
- WCAG AA, with layouts that hold up on a 320-pixel-wide phone.
- One virtual machine and Docker Compose; no managed platform.
- Data from the old system had to keep flowing in until cut-over, run by a non-technical operator with one command.
- Built mostly by one developer, so decisions, gotchas and runbooks live in the repository: 146 documents.
What I built
A Turborepo monorepo: a NestJS 11 API with Prisma on MySQL 8, a Next.js 16 and React 19 interface, and a shared package of TypeScript contracts so both sides agree on every request and response. The browser never calls the API directly; Next.js route handlers sit in front of it.
The API answers with stable error codes instead of sentences, and the interface translates them. Access is a registry of permissions, each with a scope — own, assigned or all — resolved on the server for every request. Admins can view the system as another user to reproduce exactly what that person sees, and every change is written to an audit log, with sensitive fields redacted, in the same transaction as the change itself.
Around that core: bulk Excel import, PDF generation, files on S3 through presigned URLs with the real file type checked from its bytes, scheduled reminders by email through Microsoft Graph, and history tables that keep every past version of the records that matter.
The hard parts
Filter the server adds
where: { courseId: { in: me.courseIds } }
| Greco I | Storia della Chiesa | Ebraico I | Etica | |
|---|---|---|---|---|
| Student A | 28 | Not sent | 27 | Not sent |
| Student B | 24 | Not sent | 30L | Not sent |
| Student C | 30 | Not sent | 28 | Not sent |
| Student D | 22 | Not sent | 26 | Not sent |
| Student E | 29 | Not sent | 27 | Not sent |
| Student F | 26 | Not sent | 30 | Not sent |
Scopes, not roles
A teacher can read grades — but only for the courses assigned to them. A student, only their own. The secretary’s office, all of them. Instead of hard-coding roles, each permission carries a scope and the API turns it into a database filter on every request. The interface hides what you can’t do; the server is what enforces it.
No tokens in storage
The access token lives only in memory and expires quickly. The refresh token sits in an HttpOnly cookie, is stored hashed and is rotated on every refresh — the old one is revoked as the new one is issued. Every state-changing request also carries a double-submit CSRF token.
Twenty years of data
None of this mattered until every name, title and grade from the old database arrived intact. That story has its own page.
Read: Zero lost charactersResult
- In production. On 13 October 2026 the old system was frozen and Libretto became the faculty’s source of truth.
- 438 users and 580 enrollments in production.
- Reconciled against the old system’s own figures: academic records with mismatched credit totals went from 141 to 3, mismatched weighted averages from 32 to 5.
- About 1,200 automated tests (Jest and Playwright).
The code belongs to the client and stays private. Figures are illustrations with sample data; no real records appear on this page.