The project
Campus Circle is a .NET 8 platform for university students and staff, with courses, timetables, attendance, and class spaces. Teammates designed the backend and built the frontend and the real-time features. I owned the database and the infrastructure: the move to PostgreSQL, query performance, and the servers and deploys.
From SQL Server to PostgreSQL
The platform started on SQL Server. I moved it to PostgreSQL while it was in use:
- Added PostgreSQL support to the Entity Framework Core data layer and recreated the migrations for PostgreSQL.
- Switched date and time parameters to Npgsql's types, because PostgreSQL is stricter about time zones than SQL Server.
- Moved the production data, checked it after the move, and kept downtime short.
Query performance
I rewrote slow queries and restructured the indexes behind them. For the heaviest paths, the admin dashboard, attendance data, and batch attendance marking, I moved the logic into PostgreSQL functions, so the work runs next to the data.
Batch attendance marking with the logic in the API. First it reads the class roster.
A sketch of the idea, not a trace of the old code. The class size is made up.
Logic in the API
- Batch attendance marking with the logic in the API. First it reads the class roster.
- Then it writes the marks, one query per student. Each query is a network round trip.
- Then it reads the result back. The trips add up as the class gets bigger.
PostgreSQL function
- The same work as a PostgreSQL function. The API makes one call with the class, the date, and the marks.
- The function reads the roster and writes the marks inside PostgreSQL, next to the data.
- One round trip, whatever the size of the class.
Servers and deploys
The API runs on Oracle Cloud as two instances under systemd. Caddy is the reverse proxy and handles TLS, and Cloudflare sits in front. At first I deployed with a set of Bash scripts. Later I moved the project to minideploy, which restarts the two instances one at a time with health checks and keeps old releases for rollback.
Smaller pieces
I added Google sign-in next to the password login. I also built an Android app for students in Kotlin, with Jetpack Compose and Room for offline reads. The app is no longer in use.