Skip to content

Timesheet Workflows and Business Rules ​

Document version: 1.0 Last updated: 2026-02-24 Intended audience: Developers, QA engineers, and compliance reviewers working on the PPP timesheet module


1. Executive Summary ​

The PPP timesheet module allows medical professionals to log work shifts, submit them for supervisory approval in fortnightly batches, and track compliance with fatigue, overtime, and meal break regulations. The system enforces business rules derived from the UK Working Time Regulations 1998 (WTR) and provides dashboard analytics for workforce wellbeing monitoring.

This document covers:

  • Timesheet and batch status state machines
  • Fatigue detection thresholds and their regulatory basis
  • Overtime calculation rules
  • Meal break compliance requirements
  • Validation rules applied at the schema and service layers
  • tRPC API endpoint reference

2. Data Model ​

2.1 Timesheet Record (timesheets table) ​

ColumnTypeDescription
idinteger (PK)Auto-generated identity
user_idinteger (FK)Owner of the timesheet entry
hospital_idinteger (FK)Hospital where the shift was worked
batch_idinteger (FK)Links to the batch when submitted (null before submit)
start_timetimestampShift start
end_timetimestampShift end
meal_breakintegerMeal break duration in minutes (default: 0)
variation_typevarcharShift classification (see Section 5)
commentsvarchar(1000)Free-text notes
payroll_idvarchar(50)External payroll reference
approver_idinteger (FK)Internal approver (PPP user)
approver_emailvarcharApprover email (may be external)
statusvarcharCurrent lifecycle status
created_attimestampAudit: creation time
updated_attimestampAudit: last update time
created_byintegerAudit: creating user
updated_byintegerAudit: updating user

2.2 Timesheet Batch (timesheet_batches table) ​

ColumnTypeDescription
idinteger (PK)Auto-generated identity
batch_idvarchar (UQ)Human-readable ID, e.g. BATCH_2025_a1b2c3d4
user_idinteger (FK)The professional who submitted the batch
hospital_idinteger (FK)Hospital for the batch period
approver_idinteger (FK)Internal approver (PPP user), nullable
approver_emailvarcharEmail of the approver (may be external supervisor)
start_datetimestampPeriod start
end_datetimestampPeriod end
statusvarcharsubmitted, approved, or rejected
submitted_attimestampWhen the batch was submitted
processed_attimestampWhen the batch was approved/rejected
token_consumed_attimestampSingle-use approval token consumption timestamp
reason_for_rejectionvarchar(1000)Required when status is rejected

3. Status State Machines ​

3.1 Individual Timesheet Status Transitions ​

                  +--------+
                  | (new)  |
                  +---+----+
                      |
                      v
                  +--------+
            +---->| saved  |<----+
            |     +---+----+     |
            |         |          |
            |         v          |
            |   +-----------+    |
            |   | submitted |    |
            |   +-----+-----+   |
            |         |          |
            |    +----+----+    |
            |    |         |    |
            |    v         v    |
            | +--------+ +----------+
            | |approved| | rejected |
            | +--------+ +-----+----+
            |                  |
            +------------------+
         (user edits & resubmits)

Valid transitions:

FromToTrigger
(new)savedUser creates a timesheet entry
savedsubmittedUser submits a batch containing this timesheet
rejectedsubmittedUser resubmits after rejection (via new batch)
submittedapprovedApprover approves the batch
submittedrejectedApprover rejects the batch

Notes:

  • A timesheet transitions from saved to submitted only as part of a batch submission (never individually).
  • When rejected, the batchId foreign key is preserved so the UI can display the rejection reason. On resubmission, the batchId is overwritten with the new batch.
  • Only timesheets with status saved or rejected are eligible for batch submission.

3.2 Batch Status Transitions ​

              +-----------+
              | submitted |
              +-----+-----+
                    |
               +----+----+
               |         |
               v         v
          +--------+ +----------+
          |approved| | rejected |
          +--------+ +----------+

Valid transitions:

FromToTrigger
(new)submittedBatch is always created in submitted status
submittedapprovedApprover clicks approve (via email token or UI)
submittedrejectedApprover clicks reject (via email token or UI)

Terminal states: approved and rejected are final. A batch cannot be re-processed once it reaches either state.

Concurrency protection: The token_consumed_at column acts as an optimistic lock. The processTimesheetBatch service uses an atomic UPDATE ... WHERE token_consumed_at IS NULL to prevent double-processing from concurrent approval link clicks.


4. Fatigue Detection ​

4.1 Regulatory Basis ​

Fatigue thresholds are derived from the UK Working Time Regulations 1998 (SI 1998/1833), which implement the EU Working Time Directive (2003/88/EC):

RegulationRequirementSystem Threshold
Reg. 1011 consecutive hours rest in each 24-hour periodminRestBetweenShiftsHours: 11
Reg. 1124 hours uninterrupted rest per 7-day periodweeklyRestMinHours: 24
Reg. 6Maximum average 48 hours/week (reference period: 17 weeks)maxDailyHours: 13 (daily proxy)

4.2 Configuration Profiles ​

The system supports two fatigue detection profiles defined in fatigueShiftUtils.ts:

UK WTR Defaults (UK_WTR_DEFAULTS) -- full compliance mode:

ParameterValueRegulation
minRestBetweenShiftsHours11Reg. 10
maxDailyHours13Reg. 6
weeklyRestMinHours24Reg. 11
deductMealBreakstrue--
Enabled rulesAll 4--

Legacy Defaults (LEGACY_DEFAULTS) -- backward-compatible mode:

ParameterValueNotes
minRestBetweenShiftsHours10.5Original threshold before WTR alignment
maxDailyHoursInfinityNot enforced
weeklyRestMinHours0Not enforced
deductMealBreaksfalse--
Enabled rulesinsufficient_rest only--

Current production behaviour: The identifyFatigueShifts() function (used in timesheet CRUD and batch services) defaults to LEGACY_DEFAULTS for backward compatibility. The identifyFatigueShiftsDetailed() function defaults to UK_WTR_DEFAULTS and returns per-violation detail.

4.3 Fatigue Violation Types ​

Violation TypeDescription
insufficient_restRest between consecutive shifts is below minRestBetweenShiftsHours
daily_hours_exceededTotal working hours in a calendar day exceed maxDailyHours
weekly_rest_violatedLongest continuous rest gap in a rolling 7-day window is below weeklyRestMinHours
cross_midnight_restRest period spanning midnight between consecutive shifts is below minRestBetweenShiftsHours

4.4 Algorithm ​

  1. Timesheets are sorted chronologically by startTime.
  2. For each enabled rule, the system iterates over the sorted list:
    • Insufficient rest: Compares endTime[i-1] to startTime[i] for each consecutive pair.
    • Cross-midnight rest: Same as above but only fires when the rest period spans different calendar dates.
    • Daily hours exceeded: Groups shifts by calendar date (UTC), optionally deducts meal breaks, sums hours per day, and flags all shifts on days that exceed the threshold.
    • Weekly rest violated: For each shift, looks back 7 days and finds the longest continuous rest gap among all shifts in that window.
  3. Each violation is recorded with the threshold value, actual value, and a human-readable message.
  4. A shift is marked isFatigue: true if it has one or more violations.
  5. Duplicate violations of the same type on the same shift are suppressed.

4.5 Dashboard Fatigue Calculation ​

The dashboard service (dashboardService/timesheets/index.ts) uses a day-level aggregation approach:

  • Shifts are grouped by calendar date in the user's timezone.
  • Per-day aggregates are computed (earliest start, latest end, total hours).
  • Consecutive days are compared: if the rest between day N's last end and day N+1's first start is below FATIGUE_THRESHOLD_HOURS (10.5h, from LEGACY_DEFAULTS), both days are flagged as fatigue.
  • Only shifts with status approved, submitted, or saved are included.

5. Overtime Calculation Rules ​

5.1 Variation Types ​

Timesheet shifts are classified by variationType:

Variation TypeDescription
Digital RecallRemote digital callback
OncallOn-call shift
OvertimeExplicitly marked overtime shift
RecallPhysical recall to hospital
RosterStandard rostered shift

5.2 Overtime Detection Rules ​

The dashboard overtime calculation (getOvertimeShiftsData) uses three complementary rules. A shift is counted as overtime if any rule matches:

Rule 1 -- Explicit classification: A shift with variationType === 'Overtime' is always counted as overtime.

Rule 2 -- Daily threshold (8 hours): All shifts on a calendar day are summed. If the total exceeds 8 hours, every shift on that day is flagged as overtime.

Rule 3 -- Weekly threshold (40 hours): All shifts in a Monday-to-Sunday week are summed. If the total exceeds 40 hours, every shift in that week is flagged as overtime.

ThresholdValueScopeSource constant
Daily8 hoursCalendar dayOVERTIME_DAILY_THRESHOLD_HOURS
Weekly40 hoursMon-Sun weekOVERTIME_WEEKLY_THRESHOLD_HOURS

Note: Only approved timesheets are included in overtime calculations.


6. Meal Break Compliance ​

6.1 Regulatory Basis ​

Under UK Working Time Regulations 1998, Regulation 12, workers are entitled to an uninterrupted rest break of at least 20 minutes when daily working time exceeds 6 hours.

6.2 System Implementation ​

The dashboard service (getMissedLunchBreaksData) enforces:

ParameterValueRegulation
Shift length threshold> 6 hoursReg. 12
Minimum break duration20 minutesReg. 12

Algorithm:

  1. Only approved timesheets are evaluated.
  2. Shifts with duration <= 6 hours are excluded from compliance checks (they are not legally required to have a break).
  3. For eligible shifts (> 6 hours), the mealBreak field (in minutes) is checked against the 20-minute minimum.
  4. A break below 20 minutes is counted as "missed."
  5. The percentage is calculated against eligible shifts only, to avoid skewing by short shifts.

6.3 Frontend Validation ​

The frontend (timesheetValidation.ts) additionally validates that meal break duration does not exceed the total shift duration. This is a UX guard rather than a regulatory requirement.


7. Shift Duration Validation ​

7.1 Maximum Shift Duration ​

A single timesheet entry cannot exceed 24 hours (MAX_SHIFT_DURATION_HOURS). This is enforced at the Zod schema level in both timesheetInsertSchema and timesheetUpdateSchema.

7.2 Time Range Validation ​

  • endTime must be strictly after startTime (enforced by Zod refinement).
  • Both times must be valid Date objects.
  • Meal break must be less than total shift duration (frontend validation).

8. Batch Submission Workflow ​

8.1 Submission Process ​

  1. User selects a hospital, date range, and approver.
  2. System queries for timesheets matching: userId + hospitalId + date range + status in (saved, rejected).
  3. System checks for overlapping submitted batches for the same user/hospital/period. If found, submission is rejected with an error.
  4. Inside a database transaction: a. A new batch record is created with status submitted and a UUID-based batchId (e.g., BATCH_2025_a1b2c3d4). b. All matching timesheets are updated: status -> submitted, batchId -> new batch ID.
  5. Outside the transaction (to avoid rollback on failure): a. An approval email is sent to the approver with a single-use token link. b. A bell notification is created for the approver (if they are a PPP user).

8.2 Approval Process ​

  1. Approver clicks the approval link (contains a JWT token with batchId).
  2. getBatchIdByToken verifies the token (rate-limited, single-use).
  3. processTimesheetBatch is called with the decision (approved/rejected): a. Guard: batch must be in submitted status. b. Guard: tokenConsumedAt must be null (prevents replay). c. Atomic update: sets status, processedAt, tokenConsumedAt, and optionally reasonForRejection. d. All linked timesheets are updated to match the batch decision.
  4. If approved, the batch PDF can be sent to payroll via sendApprovedBatchToPayroll.

8.3 Rejection and Resubmission ​

  • When a batch is rejected, linked timesheets are set to rejected status.
  • The batchId FK is preserved so the UI can display the rejection reason.
  • When the user resubmits, the timesheets are eligible again (status rejected is in the allowed list), and their batchId is overwritten with the new batch.

9. Duration Calculation ​

The backend computes shift duration in minutes using:

durationMinutes = floor((endTime - startTime) / 60000)

Business rule: Meal breaks are not deducted from the displayed duration. The mealBreak field is stored and displayed separately. However, the fatigue detection system optionally deducts meal breaks when deductMealBreaks: true (UK WTR mode).


10. Access Control ​

10.1 Ownership Enforcement ​

All timesheet tRPC procedures use enforceUserScope() middleware:

  • Non-admin users are automatically scoped to their own userId. They can only create, view, update, and delete their own timesheets.
  • Admin users can access timesheets for any user.
  • verifyTimesheetOwnership() performs an explicit DB check for update and delete operations.

10.2 Batch Access ​

Batch procedures are protected by protectedProcedure (authentication required). The getBatchIdByToken endpoint uses rateLimitedSensitiveProcedure to prevent brute-force token enumeration.


11. tRPC API Reference ​

11.1 Timesheet Procedures ​

ProcedureTypeInputDescription
timesheets.createmutation{ data: TimesheetInsert }Create a new timesheet entry
timesheets.updatemutation{ id: number, data: TimesheetUpdate }Update an existing timesheet
timesheets.deletemutation{ id: number }Delete a timesheet
timesheets.listquery(none)List all timesheets (legacy, capped at 1000)
timesheets.listAllqueryListTimesheetsSchemaPaginated listing with filters/sort
timesheets.getByIdquery{ id: number }Get single timesheet with fatigue flag
timesheets.getUserTimesheetsquery(none)Get current user's timesheets
timesheets.getByUserIdquery{ userId: number? }Get timesheets for a specific user

11.2 Batch Procedures ​

ProcedureTypeInputDescription
timesheets.batch.createmutation{ data: TimesheetBatchInsert }Create a batch (low-level)
timesheets.batch.updatemutation{ id: number, data: TimesheetBatchUpdate }Update a batch
timesheets.batch.getByIdquery{ id: number }Get batch by internal ID
timesheets.batch.getByBatchIdquery{ batchId: string }Get batch by human-readable batch ID
timesheets.batch.getByUserIdquery{ userId: number }List batches for a user
timesheets.batch.getTimesheetsInBatchquery{ batchId: number }Get timesheets linked to a batch
timesheets.batch.getBatchWithTimesheetsAndUserquery{ batchId: string }Get batch with timesheets and user info
timesheets.batch.submitForApprovalmutation{ userId, hospitalId, startDate, endDate, approverId, approverEmail }Submit timesheets for approval
timesheets.batch.processBatchmutation{ batchId: number, decision, comments? }Approve or reject a batch
timesheets.batch.emailApprovedToPayrollmutation{ batchId, filename, filePath }Email approved batch PDF to payroll admin
timesheets.batch.getBatchIdByTokenquery{ token: string }Verify approval token (rate-limited)

11.3 Dashboard Procedures ​

ProcedureTypeInputDescription
dashboard.timesheets.getTimeAtWorkDataquery{ userId?, dateRange?, timezone? }Heatmap data with fatigue flags
dashboard.timesheets.getTimesheetsCompletedDataquery{ userId?, dateRange? }Completion counts by status
dashboard.timesheets.getOvertimeShiftsDataquery{ userId?, dateRange? }Overtime shift counts and breakdown
dashboard.timesheets.getMissedLunchBreaksDataquery{ userId?, dateRange? }Missed meal break counts and percentage
dashboard.timesheets.getFatigueShiftsDataquery{ userId?, dateRange? }Fatigue shift counts and percentage
dashboard.timesheets.getTimesheetCompletionDataquery{ userId?, dateRange? }Timesheet completion metrics

12. Source File Reference ​

FilePurpose
packages-shared/ppp/src/constants/timesheet.tsShared constants (variation types, statuses, max shift)
packages-shared/ppp/src/schema-groups/timesheets.tsDrizzle schema for timesheets table
packages-shared/ppp/src/schema-groups/timesheetBatches.tsDrizzle schema for timesheet_batches table
packages-shared/ppp/src/zodSchema.tsZod validation schemas (insert/update)
api/backend-ppp/src/services/timesheetServices/index.tsTimesheet CRUD service
api/backend-ppp/src/services/timesheetServices/fatigueShiftUtils.tsFatigue detection engine
api/backend-ppp/src/services/timesheetBatchServices/index.tsBatch submission and approval service
api/backend-ppp/src/services/dashboardService/timesheets/index.tsDashboard analytics (overtime, fatigue, breaks)
api/backend-ppp/src/routers/procedures/timesheets/index.tstRPC timesheet router
api/backend-ppp/src/routers/procedures/timesheets/batch.tstRPC batch router
api/backend-ppp/src/routers/procedures/dashboard/timesheets/index.tstRPC dashboard router
instances/ppp/src/utils/timesheetValidation.tsFrontend validation utilities
instances/ppp/src/constants/timesheet.tsFrontend timesheet constants

Internal documentation — org members only