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)
| Column | Type | Description |
|---|---|---|
id | integer (PK) | Auto-generated identity |
user_id | integer (FK) | Owner of the timesheet entry |
hospital_id | integer (FK) | Hospital where the shift was worked |
batch_id | integer (FK) | Links to the batch when submitted (null before submit) |
start_time | timestamp | Shift start |
end_time | timestamp | Shift end |
meal_break | integer | Meal break duration in minutes (default: 0) |
variation_type | varchar | Shift classification (see Section 5) |
comments | varchar(1000) | Free-text notes |
payroll_id | varchar(50) | External payroll reference |
approver_id | integer (FK) | Internal approver (PPP user) |
approver_email | varchar | Approver email (may be external) |
status | varchar | Current lifecycle status |
created_at | timestamp | Audit: creation time |
updated_at | timestamp | Audit: last update time |
created_by | integer | Audit: creating user |
updated_by | integer | Audit: updating user |
2.2 Timesheet Batch (timesheet_batches table)
| Column | Type | Description |
|---|---|---|
id | integer (PK) | Auto-generated identity |
batch_id | varchar (UQ) | Human-readable ID, e.g. BATCH_2025_a1b2c3d4 |
user_id | integer (FK) | The professional who submitted the batch |
hospital_id | integer (FK) | Hospital for the batch period |
approver_id | integer (FK) | Internal approver (PPP user), nullable |
approver_email | varchar | Email of the approver (may be external supervisor) |
start_date | timestamp | Period start |
end_date | timestamp | Period end |
status | varchar | submitted, approved, or rejected |
submitted_at | timestamp | When the batch was submitted |
processed_at | timestamp | When the batch was approved/rejected |
token_consumed_at | timestamp | Single-use approval token consumption timestamp |
reason_for_rejection | varchar(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:
| From | To | Trigger |
|---|---|---|
| (new) | saved | User creates a timesheet entry |
saved | submitted | User submits a batch containing this timesheet |
rejected | submitted | User resubmits after rejection (via new batch) |
submitted | approved | Approver approves the batch |
submitted | rejected | Approver rejects the batch |
Notes:
- A timesheet transitions from
savedtosubmittedonly as part of a batch submission (never individually). - When rejected, the
batchIdforeign key is preserved so the UI can display the rejection reason. On resubmission, thebatchIdis overwritten with the new batch. - Only timesheets with status
savedorrejectedare eligible for batch submission.
3.2 Batch Status Transitions
+-----------+
| submitted |
+-----+-----+
|
+----+----+
| |
v v
+--------+ +----------+
|approved| | rejected |
+--------+ +----------+Valid transitions:
| From | To | Trigger |
|---|---|---|
| (new) | submitted | Batch is always created in submitted status |
submitted | approved | Approver clicks approve (via email token or UI) |
submitted | rejected | Approver 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):
| Regulation | Requirement | System Threshold |
|---|---|---|
| Reg. 10 | 11 consecutive hours rest in each 24-hour period | minRestBetweenShiftsHours: 11 |
| Reg. 11 | 24 hours uninterrupted rest per 7-day period | weeklyRestMinHours: 24 |
| Reg. 6 | Maximum 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:
| Parameter | Value | Regulation |
|---|---|---|
minRestBetweenShiftsHours | 11 | Reg. 10 |
maxDailyHours | 13 | Reg. 6 |
weeklyRestMinHours | 24 | Reg. 11 |
deductMealBreaks | true | -- |
| Enabled rules | All 4 | -- |
Legacy Defaults (LEGACY_DEFAULTS) -- backward-compatible mode:
| Parameter | Value | Notes |
|---|---|---|
minRestBetweenShiftsHours | 10.5 | Original threshold before WTR alignment |
maxDailyHours | Infinity | Not enforced |
weeklyRestMinHours | 0 | Not enforced |
deductMealBreaks | false | -- |
| Enabled rules | insufficient_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 Type | Description |
|---|---|
insufficient_rest | Rest between consecutive shifts is below minRestBetweenShiftsHours |
daily_hours_exceeded | Total working hours in a calendar day exceed maxDailyHours |
weekly_rest_violated | Longest continuous rest gap in a rolling 7-day window is below weeklyRestMinHours |
cross_midnight_rest | Rest period spanning midnight between consecutive shifts is below minRestBetweenShiftsHours |
4.4 Algorithm
- Timesheets are sorted chronologically by
startTime. - For each enabled rule, the system iterates over the sorted list:
- Insufficient rest: Compares
endTime[i-1]tostartTime[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.
- Insufficient rest: Compares
- Each violation is recorded with the threshold value, actual value, and a human-readable message.
- A shift is marked
isFatigue: trueif it has one or more violations. - 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, fromLEGACY_DEFAULTS), both days are flagged as fatigue. - Only shifts with status
approved,submitted, orsavedare included.
5. Overtime Calculation Rules
5.1 Variation Types
Timesheet shifts are classified by variationType:
| Variation Type | Description |
|---|---|
Digital Recall | Remote digital callback |
Oncall | On-call shift |
Overtime | Explicitly marked overtime shift |
Recall | Physical recall to hospital |
Roster | Standard 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.
| Threshold | Value | Scope | Source constant |
|---|---|---|---|
| Daily | 8 hours | Calendar day | OVERTIME_DAILY_THRESHOLD_HOURS |
| Weekly | 40 hours | Mon-Sun week | OVERTIME_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:
| Parameter | Value | Regulation |
|---|---|---|
| Shift length threshold | > 6 hours | Reg. 12 |
| Minimum break duration | 20 minutes | Reg. 12 |
Algorithm:
- Only
approvedtimesheets are evaluated. - Shifts with duration <= 6 hours are excluded from compliance checks (they are not legally required to have a break).
- For eligible shifts (> 6 hours), the
mealBreakfield (in minutes) is checked against the 20-minute minimum. - A break below 20 minutes is counted as "missed."
- 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
endTimemust be strictly afterstartTime(enforced by Zod refinement).- Both times must be valid
Dateobjects. - Meal break must be less than total shift duration (frontend validation).
8. Batch Submission Workflow
8.1 Submission Process
- User selects a hospital, date range, and approver.
- System queries for timesheets matching:
userId+hospitalId+ date range + status in (saved,rejected). - System checks for overlapping submitted batches for the same user/hospital/period. If found, submission is rejected with an error.
- Inside a database transaction: a. A new batch record is created with status
submittedand a UUID-basedbatchId(e.g.,BATCH_2025_a1b2c3d4). b. All matching timesheets are updated:status->submitted,batchId-> new batch ID. - 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
- Approver clicks the approval link (contains a JWT token with
batchId). getBatchIdByTokenverifies the token (rate-limited, single-use).processTimesheetBatchis called with the decision (approved/rejected): a. Guard: batch must be insubmittedstatus. b. Guard:tokenConsumedAtmust be null (prevents replay). c. Atomic update: setsstatus,processedAt,tokenConsumedAt, and optionallyreasonForRejection. d. All linked timesheets are updated to match the batch decision.- 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
rejectedstatus. - The
batchIdFK is preserved so the UI can display the rejection reason. - When the user resubmits, the timesheets are eligible again (status
rejectedis in the allowed list), and theirbatchIdis 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
| Procedure | Type | Input | Description |
|---|---|---|---|
timesheets.create | mutation | { data: TimesheetInsert } | Create a new timesheet entry |
timesheets.update | mutation | { id: number, data: TimesheetUpdate } | Update an existing timesheet |
timesheets.delete | mutation | { id: number } | Delete a timesheet |
timesheets.list | query | (none) | List all timesheets (legacy, capped at 1000) |
timesheets.listAll | query | ListTimesheetsSchema | Paginated listing with filters/sort |
timesheets.getById | query | { id: number } | Get single timesheet with fatigue flag |
timesheets.getUserTimesheets | query | (none) | Get current user's timesheets |
timesheets.getByUserId | query | { userId: number? } | Get timesheets for a specific user |
11.2 Batch Procedures
| Procedure | Type | Input | Description |
|---|---|---|---|
timesheets.batch.create | mutation | { data: TimesheetBatchInsert } | Create a batch (low-level) |
timesheets.batch.update | mutation | { id: number, data: TimesheetBatchUpdate } | Update a batch |
timesheets.batch.getById | query | { id: number } | Get batch by internal ID |
timesheets.batch.getByBatchId | query | { batchId: string } | Get batch by human-readable batch ID |
timesheets.batch.getByUserId | query | { userId: number } | List batches for a user |
timesheets.batch.getTimesheetsInBatch | query | { batchId: number } | Get timesheets linked to a batch |
timesheets.batch.getBatchWithTimesheetsAndUser | query | { batchId: string } | Get batch with timesheets and user info |
timesheets.batch.submitForApproval | mutation | { userId, hospitalId, startDate, endDate, approverId, approverEmail } | Submit timesheets for approval |
timesheets.batch.processBatch | mutation | { batchId: number, decision, comments? } | Approve or reject a batch |
timesheets.batch.emailApprovedToPayroll | mutation | { batchId, filename, filePath } | Email approved batch PDF to payroll admin |
timesheets.batch.getBatchIdByToken | query | { token: string } | Verify approval token (rate-limited) |
11.3 Dashboard Procedures
| Procedure | Type | Input | Description |
|---|---|---|---|
dashboard.timesheets.getTimeAtWorkData | query | { userId?, dateRange?, timezone? } | Heatmap data with fatigue flags |
dashboard.timesheets.getTimesheetsCompletedData | query | { userId?, dateRange? } | Completion counts by status |
dashboard.timesheets.getOvertimeShiftsData | query | { userId?, dateRange? } | Overtime shift counts and breakdown |
dashboard.timesheets.getMissedLunchBreaksData | query | { userId?, dateRange? } | Missed meal break counts and percentage |
dashboard.timesheets.getFatigueShiftsData | query | { userId?, dateRange? } | Fatigue shift counts and percentage |
dashboard.timesheets.getTimesheetCompletionData | query | { userId?, dateRange? } | Timesheet completion metrics |
12. Source File Reference
| File | Purpose |
|---|---|
packages-shared/ppp/src/constants/timesheet.ts | Shared constants (variation types, statuses, max shift) |
packages-shared/ppp/src/schema-groups/timesheets.ts | Drizzle schema for timesheets table |
packages-shared/ppp/src/schema-groups/timesheetBatches.ts | Drizzle schema for timesheet_batches table |
packages-shared/ppp/src/zodSchema.ts | Zod validation schemas (insert/update) |
api/backend-ppp/src/services/timesheetServices/index.ts | Timesheet CRUD service |
api/backend-ppp/src/services/timesheetServices/fatigueShiftUtils.ts | Fatigue detection engine |
api/backend-ppp/src/services/timesheetBatchServices/index.ts | Batch submission and approval service |
api/backend-ppp/src/services/dashboardService/timesheets/index.ts | Dashboard analytics (overtime, fatigue, breaks) |
api/backend-ppp/src/routers/procedures/timesheets/index.ts | tRPC timesheet router |
api/backend-ppp/src/routers/procedures/timesheets/batch.ts | tRPC batch router |
api/backend-ppp/src/routers/procedures/dashboard/timesheets/index.ts | tRPC dashboard router |
instances/ppp/src/utils/timesheetValidation.ts | Frontend validation utilities |
instances/ppp/src/constants/timesheet.ts | Frontend timesheet constants |