Case study · Backend & databases
UoC Payroll System
A payroll and HR system in Node, Express and SQL. The project where the interesting work is the schema, not the screen.
The problem
Payroll is the classic case where the user interface is the easy part. An employee has a department, a contract, a salary that changes, and a history of payments that must still be explainable a year later.
Get the data model wrong and every feature after it becomes a workaround. That is the actual exercise here, and the reason this is the project I point at when someone asks whether I can do more than Firebase.
The goal
A system that manages employees, departments and contracts, runs payroll from that data without manual intervention, and can produce reports that reconcile with what was actually paid.
The constraint that matters: a payroll record is a historical fact, not a calculated value. What someone was paid in March does not change because their salary changed in April.
What I built
- A relational schema designed from scratch for employees, departments, contracts, payroll and reports.
- RESTful routes over each of those resources, with the API boundary drawn along the entities rather than the screens.
- Automated payroll processing, so a run is triggered rather than assembled by hand.
- Reporting dashboards reading from the recorded runs.
- A vanilla HTML, CSS and JavaScript front end — no framework, deliberately, so the project stayed about the back end.
Technical decisions
Relational, and not apologetically so
Employees, departments and contracts are a textbook case of related entities with referential integrity that actually matters. A document store would have made the writes easier and every report harder. SQL keeps the invariants in the database instead of in the application code that happens to be running.
Cost: migrations. Every schema change is a deliberate act rather than a shrug, which is the point, but it is slower to move early on.
Payroll runs are stored, not recomputed
Recomputing a past payslip from current data is how systems quietly rewrite history. A run writes a row to payrollrecord, the money that moved is recorded separately in paymenttransaction, and salary changes accumulate in salaryhistory instead of overwriting a single figure on the employee. What someone was paid in March stays true after their April raise.
Cost: stored results duplicate data that could be derived, and a bug in a run leaves persisted wrong numbers that have to be corrected rather than just recalculated once the formula is fixed.
Reporting through database views
The reporting layer is five SQL views — active contracts, current salary, payroll history, department costs, payroll summary — rather than query-building in Node. The definition of "current salary" lives in one place, so two reports cannot quietly disagree about it.
Cost: logic in views is harder to test and version than logic in application code, and it is invisible to anyone reading only the JavaScript.
REST boundaries drawn on entities, not on pages
Routes are grouped by resource — employees, departments, contracts, payroll, reports — so the API stays usable when the interface changes. Endpoints shaped around whatever a screen needed would have to be rewritten every time a screen did.
Cost: the front end makes more calls and assembles more views itself than a purpose-built endpoint would require.
What was hard
Time. Almost every difficult case in payroll is a date problem: a contract that starts mid-month, a salary that changes between runs, a report that has to describe a period rather than a moment.
The schema answers that by refusing to keep a single current value for anything that moves. Salaries live in salaryhistory, employment terms in contract, and "what is this person paid right now" is a view over those rather than a column someone overwrites. That is the difference between a schema that works and one that only works until the first raise.
The result
Graded 100/100, with the full source, database scripts and documentation on GitHub.
It is coursework, and I would not pretend otherwise. It is also the clearest evidence I have that I can design a relational model and an API around it, rather than only assembling products on a managed platform.
Have a project in mind?
I take on client websites, web apps, and product builds across Cyprus and Greece.
Start a conversation