The Scale Problem

This project started as a timesheet digitisation exercise and became something more interesting: a real-time collaborative workforce management system for an enterprise with 300+ field workers, 12 simultaneously active dispatchers, and a compliance requirement for timesheet accuracy that made every design decision non-trivial.

The existing system was a combination of paper timesheets and a legacy DOS-era application that the operations team had been maintaining since 2009. Data entry errors were endemic. Audit trails were nonexistent. Reconciliation at month-end took three full-time staff two days to complete.

The brief was straightforward: replace the legacy system with something that worked on modern hardware, didn’t require specific browsers, handled multiple concurrent users, and produced audit-ready reports automatically.

The Concurrent Editing Problem

The most technically interesting challenge wasn’t the timesheet logic — it was concurrent editing. When 12 dispatchers are simultaneously managing shift allocations across 300 workers, conflicts are guaranteed. What happens when two dispatchers both try to assign Worker A to Site B at the same time?

The naive solution — last write wins — is unacceptable. A dispatcher who just spent 20 minutes building a rota shouldn’t lose their changes to a simultaneous edit from a colleague.

The approach uses optimistic locking with conflict detection. Every timesheet row and rota entry has a version number. When a client reads a record, they receive the current version. When they save, the update includes the version they read. If the current database version has moved on, the save is rejected with a CONFLICT error and the client must reload and reapply their changes.

This is not a new pattern. What’s worth noting is the user experience work that sits on top of it: when a conflict is detected, the system doesn’t just say “save failed.” It shows the dispatcher exactly what changed (the conflicting assignment, who made the change, and when) and offers three resolution paths: apply my changes anyway, discard my changes, or merge (for non-conflicting field changes).

The Offline-First Architecture

Field workers don’t always have reliable connectivity. A gas engineer on a rural site in Scotland may have no mobile signal for hours. The system needed to work offline and sync when connectivity returned.

This is where the architecture split into two distinct paths.

For field workers (mobile-first): A Progressive Web App with a service worker that caches the application shell and stores timesheet entries in IndexedDB. When online, entries sync immediately. When offline, they’re queued locally with a timestamp. The sync logic on reconnection is careful to handle the case where a server-side version has been modified since the offline entry was created — it applies a last-write-wins conflict resolution for timesheet entries (dispatcher assignments use the more sophisticated optimistic locking described above).

For dispatchers (desktop): Full online operation with real-time updates via Socket.io. Dispatchers are based in offices with reliable connectivity, so the offline complexity is unnecessary for them.

The critical architectural decision was separating these two data paths. The server never sees a “live” flag from the mobile client — it only receives sync events with timestamps and resolves them server-side. This means the server is the single source of truth and the two client types never conflict with each other because they operate on different data subspaces (field workers submit timesheet data; dispatchers manage assignments and approvals).

Socket.io Fan-Out for Live Updates

When a dispatcher updates a rota assignment, every connected dispatcher and every field worker who has that shift in their view needs to see the update in real-time. Socket.io handles this with a room-based fan-out model.

Each job has a Socket.io room keyed by job ID. When a timesheet entry is modified for Job X, the server emits an update to room job:X. Every connected client subscribed to that job receives the update within milliseconds.

The implementation challenge was handling the reconnection case: when a client reconnects after being offline, they need to receive the current state of all their active jobs, not just wait for the next update. The server maintains a short event log (last 100 events per job, retained for 24 hours) and replays relevant events to reconnecting clients.

Automated Rota Generation

One of the most-used features is the automated rota generator. Given a set of job requirements (site coverage needs, required qualifications, worker availability, travel time constraints), it generates a draft rota that satisfies the constraints.

The algorithm is a constraint satisfaction problem implemented in JavaScript. The constraints are weighted: hard constraints (worker has the required qualification, not double-booked, within allowed working hours) must be satisfied; soft constraints (minimise travel time, respect worker preferences where possible) are optimised but can be violated if necessary.

The interesting part is how it handles the travel time problem. Assigning workers to back-to-back jobs in different cities is operationally impossible but easy to accidentally specify in a rota system. The generator uses a distance matrix API to calculate travel times between consecutive assignments and flags assignments where travel time makes the schedule physically impossible.

The generated rota is a draft — dispatchers review and adjust before publishing. The AI assistance layer suggests adjustments when the published rota has conflicts: “Worker B has a 4-hour gap between Site A and Site B. This appears to be a scheduling error.”

PCI-DSS Considerations

The system processes timesheet data, not payment card data. However, the client operates in a sector where PCI-DSS compliance is a procurement requirement, and the compliance questionnaire asks about data handling practices for any system that handles employee data.

The relevant decisions:

  • Database credentials are rotated quarterly with a manual process (not automated — PCI-DSS specifically requires that credential rotation be a deliberate action, not an automated background job)
  • All inter-service communication uses TLS 1.3; TLS 1.0 and 1.1 are explicitly disabled at the load balancer
  • Database access is role-based with principle of least privilege — the application service account can only perform the specific CRUD operations it needs, not arbitrary SQL
  • Audit logs for all data access are written to a separate log store and retained for 12 months

The Compliance Dashboard

The timesheet approval workflow is where the system delivers its most concrete ROI. The previous process required finance staff to manually verify that every timesheet row had a corresponding job authorisation, site sign-off, and breaks taken. This took two days per month-end.

The automated system performs these checks on every timesheet submission and surfaces discrepancies immediately, not at month-end. The compliance dashboard shows: approved timesheets, pending approval, and exceptions (missing sign-off, break violations, unverified hours). Each exception links directly to the specific timesheet row.

The exception rate has dropped from approximately 8% of all timesheets in the legacy system to under 1% in the current system. The reduction isn’t because people are suddenly more careful — it’s because the system catches discrepancies at submission time rather than at month-end reconciliation, when the pressure to “just approve it and move on” is highest.

What Made This Project Hard

The hardest part wasn’t technical — it was organisational. Enterprise workforce management systems sit at the intersection of finance (payroll depends on timesheet accuracy), operations (scheduling depends on timesheet data), and compliance (regulatory requirements for working time directive adherence). Each department had different priorities and different mental models of how the system should work.

The design process required running workshops with all three departments simultaneously, which was time-consuming but essential. A timesheet approval workflow that works for finance but creates unacceptable friction for operations will get worked around, not used. A rota generator that doesn’t respect operational constraints will be abandoned. Getting all three perspectives in the same room before writing any code prevented a lot of expensive reworking.