When a Cancelled Leave Rewrites Last Month

Attendance data has an inconvenient property: the past keeps changing. An employee books leave for the 3rd on the 1st, cancels it on the 5th, and applies again retroactively on the 20th because they were actually off sick. Meanwhile the attendance report for the 3rd was generated on the 4th, and a manager approved it on the 6th. The leave sync from part 3 does not just mirror leave records into the database — it reconciles every one of those late arrivals against report rows that already exist, already carry a shift, and may already carry a human signature. This part is about that reconciliation: the most business-critical logic in the estate, and the place where the code is most visibly careful.

Leaves become shifts, via a lookup table

The reporting fact table — ShiftDetailMonthlyReport, one row per employee per day — has no concept of “leave”. It has shifts. So the platform models absence as pseudo-shifts: alongside the real morning and night shifts in ShiftAllowance sit rows for “company holiday”, “other leave”, “client site”, “no shift” and “work from home”. A day on leave is a day whose shift is the leave.

Mapping a leave type to its pseudo-shift goes through MasterLookUp, the estate's stringly-typed indirection table:

var mapping = db.MasterLookUp
    .Where(m => m.FieldType == leave.LeaveType)
    .Select(m => m.Value)
    .FirstOrDefault();

var shiftId = int.Parse(mapping);   // shift IDs stored as nvarchar

A key-value table where the values are database identity values stored as strings, parsed at every use. It works, and it let new leave types be wired up without a deployment — but note the shape: the meaning of a foreign key lives in a text column, invisible to the schema, unenforced by any constraint. We will meet this table again when the archaeology series-within-the-series starts in part 13.

The pseudo-shift IDs the sync needs constantly — not per leave type, but structurally — arrive a different way: injected from configuration.

{
  "NotApplicableShiftId": 28,
  "CompanyHolidayShiftId": 25,
  "OtherLeavesShiftId": 46,
  "ClientSiteShiftId": 26,
  "NoShiftShiftId": 30,
  "WorkFromHomeShiftId": 365
}

Six database identity values as app settings. Hold that thought; it becomes a defect at the end of this article.

The rewrite walks the calendar

When a synced leave touches dates in the past, the sync loads the affected report rows and re-derives three columns on each: SuggestedShiftId (what the system believes), InputShiftId (what stands), and IsApproved. The walk is date-by-date across the leave's range:

foreach (var day in leave.StartDate.To(leave.EndDate))
{
    if (IsWeekend(day)) continue;                    // weekends untouched

    var row = rows.FirstOrDefault(r => r.LogDate == day);
    if (row is null) continue;                       // calculator owns gaps

    if (IsHumanEdited(row) && row.IsApproved)
        continue;                                    // people outrank syncs

    row.SuggestedShiftId = shiftId;
    row.InputShiftId     = shiftId;
    row.IsApproved       = AutoApprovalAllowed(leave, employee);
    row.ModifiedBy       = "SystemA";                // provenance - part 7
}

Three rules in that loop carry the design's weight:

• Weekends are skipped. A leave spanning a weekend must not stamp leave-shifts onto Saturday rows, because weekend rows are auto-approved as non-working days by the calculator, and overwriting them would un-approve them.
• Missing rows are left missing. The sync does not create report rows; that is the calculator's job, and having exactly one writer responsible for the table's grain avoided a whole class of duplicate-row fights.
• Human decisions win. If a row was edited and approved by a person, the sync leaves it alone entirely. How the code decides “was this edited by a person” is its own remarkable story — the ModifiedBy column doubles as a state machine, and part 7 dissects it — but the precedence rule itself is exactly right. An automated system that silently reverses recorded human judgements is how you lose your users' trust in one payroll cycle.

Cancellations are the heavy case. A cancelled leave doesn't just clear the pseudo-shift — the day now needs its shift re-inferred from whatever badge data exists, as if the leave had never been. For that, the sync calls the same shift-calculation routine the console job uses, shipped to it as a shared library. The next part covers the algorithm; part 17 covers the uncomfortable way it is shared.

Money means no auto-approve

The subtlest rule in the reconciliation is one if statement. Work-from-home days can carry an allowance for eligible employees — actual money, paid per day, driven by this very table. When the sync lands a WFH leave for an employee who is not allowance-eligible, it auto-approves the row; there is nothing at stake beyond record-keeping. When the employee is eligible:

private static bool AutoApprovalAllowed(LeaveRecord leave, Employee emp)
{
    if (leave.Type == LeaveType.WorkFromHome && emp.IsAllowanceEligible)
        return false;   // a person must sign off anything that pays out

    return true;
}

Anywhere the row's approval releases money, a human must set the flag. The system happily records what it believes happened — the suggested shift is stamped either way — but the state transition that triggers a payout is reserved for a manager. Cheap to implement, easy to explain in an audit, and it encodes a principle worth stealing for any workflow system: auto-approve consequences you can retract; gate consequences you cannot. You can un-approve an attendance row; you cannot conveniently claw back an allowance that already left payroll.

Two sources of truth for six magic numbers

Now back to those six shift IDs in configuration. Injecting them was a defensible call on its own: the values differ between environments (a freshly seeded dev database will not reuse production's identity values), and config keeps the code environment-agnostic.

The problem is the other executable. The shift calculator — the console job that owns this same fact table — carries the same six IDs as compile-time literals: 28, 30, 25, 26, 46, 365, sprinkled through six hundred lines of reconciliation code. Two executables, one table, and the pseudo-shift vocabulary defined twice in two different media.

1. At best, this is a maintenance trap: reseed a pseudo-shift, update the config, and the console job silently keeps stamping the old ID.
2. At worst, it is a split-brain trap: the sync writes rows meaning “work from home” with one ID while the calculator interprets that ID as something else entirely — and no foreign key or check constraint exists to object, because the schema enforces almost nothing.

The honest fix is boring and structural: these six values are not configuration and they are not literals — they are reference data, and the database that both executables already share is sitting right there. A WellKnownShift table, or seeded stable IDs, or even the existing MasterLookUp used consistently by both writers, would have made the database the single source of truth for its own vocabulary. When five executables share one database, the database has to own the meanings, not just the rows — otherwise every executable ships its own private dictionary, and they drift.

The routine the cancellation path calls — the one that re-infers a shift from raw badge times — deserves its own article, because it is the closest thing the platform has to machine intelligence: a nearest-neighbour classifier wearing a foreach loop's clothing. Next, guessing your shift with Manhattan distance.