Designing an Audit Trail for Attendance Edits Without Overwriting Source Events

Attendance corrections often appear as simple database updates, but they erase crucial context: what was initially recorded, who requested the change, supporting evidence, approval status, and the value used before the modification. In systems impacting payrolls, this missing context creates a design flaw. To address this, treat the original clock event as immutable and create a separate, reviewable audit trail for corrections.

Attendance software typically contains two types of truths: observed truth—the raw event from devices, browsers, or administrator imports—and operational truth—the approved interpretation used for schedules, overtime, and pay. Both facts should be preserved, even if they disagree. For instance, an employee could clock out at 18:47, but an approved work end time of 18:00 may reflect someone forgetting to clock out after changing clothes.

When creating an attendance event, include metadata that identifies its origin and the server's receipt time. Device time is insufficient as phones can be inaccurate or synchronize later. Design the `attendance_events` table to encode these details.

Corrections should not be treated as new clock events. Instead, they are proposals to interpret one or more source events differently. Create a dedicated `attendance_corrections` table for this purpose. Each correction should reference the affected source events and capture essential information like the employee's ID, date, requested clock times, reason for correction, requestor's ID, timestamp, status, decision-maker's ID, decision note, and any additional details.

The correction workflow should reference source events through a join table to handle scenarios like duplicate scans, missing clock-outs, or shifts crossing midnight.

When calculating the effective attendance record, combine raw events with the latest approved correction and the policy version used for interpretation. This approach allows for dynamic adjustments to rules without altering existing data. It is crucial to maintain policy version information, as changing rules should not silently affect previously calculated records.

Implement state transitions explicitly, defining legal transitions between correction statuses (pending, approved, rejected, cancelled) in a domain function. This ensures that only valid transitions are allowed and prevents unauthorized modifications. If an approved correction is found to be incorrect, create a new correction instead of editing the original one to maintain the integrity of the audit trail. This approach avoids ambiguity and preserves the chronological record of decisions.

Concurrency issues may arise when multiple users attempt to approve the same pending correction simultaneously. Use optimistic locking or conditional updates to handle these situations gracefully. Ensure that only one update can succeed at a time, and if an update fails due to another user's modification, the system should refresh the client rather than pretend the approval was successful. This protects the consistency and reliability of the attendance record management workflow.