Designing an Audit Trail for Attendance Edits Without Overwriting Source Events

An attendance correction looks like a simple update:

UPDATE attendance_records
SET clock_out_at = '2026-10-15T18:04:00+09:00'
WHERE id = 8412;

That query fixes the current row, but it also destroys the answer to several questions: What was recorded first? Who requested the correction? What evidence supported it? Who approved it? Which value was used for payroll before the change?

For systems that affect pay, deleting that context is a design problem. A safer model treats the original clock event as immutable and records corrections as a separate, reviewable trail.

Start with two different kinds of truth

Attendance software usually contains at least two truths:

1. Observed truth: the raw event produced by a device, browser, QR scan, or administrator import.
2. Operational truth: the approved interpretation used for schedules, overtime, and payroll.

Those values can disagree without either being useless. A raw 18:47 clock-out may be genuine, while an approved 18:00 work end may reflect an employee who forgot to clock out after changing clothes. The system should preserve both facts instead of rewriting one into the other.

In Korean workplaces, teams often search for these tools as 출퇴근기록기 (clock-in recorder) or 근태관리기 (attendance management tool). Whatever label the product uses, the important architectural question is the same: does a correction replace evidence, or does it add a decision to the evidence?

Model source events as append-only data

A source event should identify where it came from and when the server received it. Device time alone is not enough because a phone clock can be wrong or an offline client may synchronize later.

CREATE TABLE attendance_events (
  id                BIGSERIAL PRIMARY KEY,
  employee_id       BIGINT NOT NULL,
  event_type        TEXT NOT NULL CHECK (event_type IN ('clock_in', 'clock_out')),
  occurred_at       TIMESTAMPTZ NOT NULL,
  received_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  source_type       TEXT NOT NULL,
  source_reference  TEXT,
  payload_hash      TEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

The application role that inserts these rows should not have permission to update or delete them. Retention and privacy rules may eventually require controlled deletion, but that should be a separate administrative process rather than an ordinary edit endpoint.

Put corrections in their own workflow

A correction request is not another clock event. It is a proposal to interpret one or more source events differently.

CREATE TABLE attendance_corrections (
  id                  BIGSERIAL PRIMARY KEY,
  employee_id         BIGINT NOT NULL,
  work_date           DATE NOT NULL,
  requested_clock_in  TIMESTAMPTZ,
  requested_clock_out TIMESTAMPTZ,
  reason_code         TEXT NOT NULL,
  reason_detail       TEXT,
  requested_by        BIGINT NOT NULL,
  requested_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  status              TEXT NOT NULL CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled')),
  decided_by          BIGINT,
  decided_at          TIMESTAMPTZ,
  decision_note       TEXT
);

The correction should reference the affected source events through a join table. This matters when one workday has duplicate scans, a missing clock-out, or a shift that crosses midnight.

Build the effective record instead of mutating it

Read models can combine raw events with the latest approved correction. The calculation can live in a database view, a materialized projection, or an application service.

effective attendance
  = original source events
  + latest approved correction
  + policy version used for interpretation

Keeping the policy version is important. A record calculated under a 30-minute break rule should not silently change when the company later adopts a 45-minute rule.

For a practical example of the employee-facing recording flow, this attendance record management guide is useful context. The implementation detail to preserve is the boundary between captured events and later administrative decisions.

Make state transitions explicit

Do not let clients set any status they want. Define legal transitions in one domain function:

type Status = "pending" | "approved" | "rejected" | "cancelled";

const allowed: Record<Status, Status[]> = {
  pending: ["approved", "rejected", "cancelled"],
  approved: [],
  rejected: [],
  cancelled: [],
};

export function canTransition(from: Status, to: Status): boolean {
  return allowed[from].includes(to);
}

If an approved correction is wrong, create a new correction that supersedes it. Reopening and editing the old decision makes the audit trail ambiguous.

Protect the workflow from concurrency bugs

Two managers can open the same pending request and approve different values. Use optimistic locking or a conditional update:

UPDATE attendance_corrections
SET status = 'approved',
    decided_by = $1,
    decided_at = now()
WHERE id = $2
  AND status = 'pending';

The update must affect exactly one row. A zero-row result means the request was already handled and the client should refresh instead of pretending the approval succeeded.

For payroll export, store a snapshot identifier or cutoff time. Otherwise, an approval that arrives during export can create a file that cannot be reproduced later.

Log business meaning, not just HTTP requests

An infrastructure log saying PATCH /attendance/8412 200 is not enough. Emit a domain event with the decision context:

{
  "event": "attendance_correction_approved",
  "correction_id": 392,
  "employee_id": 117,
  "work_date": "2026-10-15",
  "previous_effective_clock_out": "2026-10-15T18:47:12+09:00",
  "new_effective_clock_out": "2026-10-15T18:00:00+09:00",
  "reason_code": "forgot_to_clock_out",
  "decided_by": 42,
  "policy_version": "attendance-policy-2026-09"
}

Avoid copying free-form notes, precise location data, or device identifiers into every analytics system. The audit store can retain necessary evidence with stricter access controls, while downstream events carry only the fields needed for operations.

Test invariants, not only endpoints

The most valuable tests assert properties that must remain true across refactors:

• A source event cannot be updated through the normal application role.
• A pending correction can be decided only once.
• A rejected correction never changes the effective attendance record.
• An approved correction does not erase the referenced source events.
• Payroll export can be reproduced from a recorded cutoff and policy version.
• A manager cannot approve a correction outside the manager's authorized team.

Property-based tests are particularly useful around overnight shifts, duplicate events, daylight-saving transitions, and late offline synchronization.

A small implementation checklist

Before shipping an attendance correction feature, verify that the system can answer these questions without reading application logs by hand:

1. What did the employee or device originally submit?
2. When did the server receive it?
3. What replacement value was requested, and why?
4. Who approved or rejected the request?
5. Which policy version produced the payable result?
6. Which version was included in a specific payroll export?

If any answer disappears after an edit, the design is still overwriting history.

The goal is not to implement event sourcing everywhere. It is to preserve the few boundaries that matter: observations remain observations, decisions remain decisions, and payroll results remain reproducible. That separation makes corrections easier to explain, safer to authorize, and much less painful to debug.