Design Goals
The design converts the project requirements and analysis into a structure that can be implemented and tested. The main goals are consistent scheduling rules, secure role-based access, reliable database changes, clear interfaces, and preservation of historical records.
Technology and Architecture
The application uses a three-layer web architecture:
– A Next.js and React client provides the responsive browser interface.
– A Node.js and Express server provides the REST API, authentication, permissions, validation, and business rules.
– PostgreSQL stores operational and historical data, with Prisma providing typed data access and schema management.
This separation keeps database credentials and business decisions away from the browser. The client can disable an invalid action for usability, but the server still performs the authorization and validation checks.
Database Design
The employee area includes employees, user accounts, positions, certification types, and employee certifications. Availability and request tables store weekly patterns, temporary unavailable periods, and time-off decisions.
Schedule periods contain shift records. Each shift refers to a shift type and stores its own dates, times, minimum staffing value, status, and qualification requirements. Assignments connect employees to shifts. Separate tables support open-shift posts, employee responses, swap requests, attendance records, notifications, and audit logs.
Unique constraints prevent duplicate employee numbers, accounts, assignments, and attendance records. Foreign keys preserve relationships, while status values allow records to be deactivated or closed without deleting their history.
API and Permission Design
The API is divided into modules for authentication, employees, certifications, schedules, requests, open shifts, swaps, attendance, notifications, dashboards, and reports. Requests and responses use JSON with a consistent success or error structure.
Authentication uses a server-side session stored in PostgreSQL. Passwords are stored as Argon2 hashes. Middleware loads the current active user and checks the required role. Record ownership is also checked for personal employee actions.
Zod schemas validate request parameters and bodies. Prisma uses parameterized database operations. Production settings require HTTPS, secure cookies, an approved client origin, protected secrets, and standard security headers.
Scheduling Rule Engine
The assignment validator is designed as a reusable service. It checks employee status, overlap, rest time, approved leave, recurring availability, temporary unavailability, expected hours, and required qualifications. Direct assignments, open-shift approval, and swap approval call the same rule service.
Coverage validation checks the number of assigned employees and the number holding each required certification. Blocking problems and warnings are kept separate. A qualification shortage blocks publication, while a general staffing shortage can be acknowledged with a reason.
Schedule publication runs in a serializable database transaction. The server locks and validates the schedule, records any shortage explanation, publishes the shifts, creates audit information, and creates notifications. If any required step fails, the transaction is rolled back.
Interface Design
The manager navigation includes Dashboard, Employees, Schedules, Requests, Open Shifts, Attendance, Reports, and Notifications. The dashboard summarizes staffing, warnings, pending requests, and certification expiry.
The schedule interface shows schedule periods and their draft or published status. The editor supports shift creation, employee assignment, validation, and publication. Employee pages use a smaller set of actions focused on personal schedules, requests, available shifts, attendance, and notifications.
Forms use clear labels, required-field validation, readable status text, and responsive layouts. Colour supports the interface but is not the only way a status is communicated.
Testing and Deployment Design
Unit tests cover time calculations and scheduling rules. API tests cover authentication, roles, state changes, and invalid input. Browser testing covers complete workflows and responsive layouts. Seed data provides repeatable employees, certifications, schedules, assignments, and attendance.
The application is container-ready. Development uses Docker Compose for PostgreSQL, while production can use separate client, server, and managed database services. Environment variables keep credentials outside the source code.
This design provides a stable structure for implementation while keeping the scheduling rules and transaction boundaries explicit.