Gap-Filling Two Years of Attendance

The inference from part 5 is fifty elegant lines. The machine that feeds it is six hundred inelegant ones: a console application, fired by an external scheduler, that walks every active employee in three Indian metros and makes sure the reporting table has one row per employee per day, from the 1st of January 2019 or their date of joining, whichever is later, with no gaps whatsoever — including the days they never came in, never logged in, and never existed as far as the badge gates are concerned. This part is about why that dense grain is the right call, the sentinel-value vocabulary it forced into being, and what writing it one row at a time actually costs.

Why blank rows beat missing rows

The lazy grain for an attendance table is “one row per day the employee showed up” — you write what the badge gates saw and nothing else. The calculator refuses that grain, and the reason is arithmetic. Every dashboard number downstream is an aggregate over days: average office hours, days present, approval completion. Against a sparse table, AVG(OfficeHours) silently averages only the days that exist — an employee who came in twice last month and worked ten hours each time posts a better average than one who came in twenty times at nine. A sparse fact table makes absence invisible to every aggregate that touches it.

So the calculator writes the blanks explicitly. For each employee, it walks the calendar day by day; where LogRecorder — the per-day badge aggregate filled by the ingestion component the estate doesn't contain — has data, it writes a real row; where it has nothing, it writes a blank one. Data-warehouse people will recognise what this accidentally builds: a dense date spine over a periodic-snapshot fact table, a reading part 16 takes seriously.

for (var day = startDate; day <= today; day = day.AddDays(1))
{
    var badge = logRecords.FirstOrDefault(l =>
        l.EmployeeId == emp.EmployeeId && l.LogDate == day);

    var row = badge is null
        ? GetBlank(emp, day)          // nobody badged: sentinel row
        : BuildFromBadge(emp, badge); // real swipes: infer the shift

    db.ShiftDetailMonthlyReport.Add(row);
    db.SaveChanges();                 // yes - per row; hold that thought
}

The sentinel vocabulary

A blank row poses an immediate question: what do you put in the non-nullable columns? The schema wants a first login, a last logout, an hours figure. The calculator answers with sentinels, and once you learn its vocabulary you see it everywhere in the estate:

Half-open days get the same treatment: an employee who badged in but never badged out — a real occurrence, given doors that people tailgate through — gets a genuine FirstLogIn and a MaxValue logout, and the pairing itself becomes readable: arrived, departure unknown.

I want to be fair to sentinels before I criticise them. The alternative — nullable columns — pushes a three-valued-logic tax onto every query that ever touches the table: every AVG, every comparison, every WHERE grows IS NOT NULL guards, and the ones that forget produce silently wrong numbers. Sentinels keep the columns non-nullable and the simple queries simple. That is a real benefit and it was consciously bought.

But the price is a vocabulary, and vocabularies must be taught. Nothing in the schema says 2000-01-01 means “never” — no check constraint, no comment, no lookup. Every consumer must simply know, and the estate is full of consumers: five executables, four views, thirty-one stored procedures. At least one proc exists specifically to translate the sentinels back out — it resolves a report row's “true” login window by patching the 2000-01-01 placeholder with the previous day's last-seen timestamp before handing the row to the UI. When you need a stored procedure to un-say what your writer said, the vocabulary has become a dialect. And the worst failure mode is quiet: a new query that averages office hours without excluding sentinel rows will happily fold a wall of TimeSpan.Zero values into a manager's dashboard — which, as part 16 shows, is not a hypothetical.

Weekends, holidays, leaves — the stamping order

Not every gap is blank. Before falling through to the sentinel row, each day passes a precedence ladder:

1. Weekend? Write the blank row but auto-approve it — a Saturday needs no manager's signature to be a Saturday.
2. Company holiday? CompanyHoliday is checked per date and location — three metros, three holiday calendars — and the day is stamped with the holiday pseudo-shift, auto-approved.
3. Leave? Applied, approved or utilised leave from the synced LeavesApi table stamps the day with the pseudo-shift from MasterLookUp, subject to part 4's money rule.
4. Badge data? Infer the shift, per part 5.
5. Nothing at all? Sentinel row, unapproved, awaiting a human explanation.

The ladder's order is load-bearing — a holiday during someone's leave is a holiday, not a leave day, which matters because leave balances are debited elsewhere. This is the kind of business rule that lives nowhere in any specification I ever saw; it exists only as the order of if statements in a console application.

What one round-trip per row costs

Now the ledger. That SaveChanges() inside the loop is not a simplification for this article — it is faithful to the source, and it executes one database round-trip per employee per day. For steady-state daily runs this is merely wasteful: a few hundred employees times one new day is a few hundred sequential inserts where one batched save would do.

The backfill scenario is where it becomes a story. A newly onboarded employee triggers the gap-fill from their date of joining — and an employee whose record enters the system late, or the initial 2019 backfill itself, walks years of calendar. Two years is roughly 730 rows; at even five milliseconds of round-trip latency that is four seconds per employee, serialised, inside a job walking hundreds of employees — hours of wall-clock time spent almost entirely on network hops, against a cloud database billed by the DTU. EF Core would batch a hoisted save into a handful of statements; SqlBulkCopy would do the whole employee in one. Neither was reached for, because the job “worked” — it just worked at 2 a.m., where nobody watches the clock. Chatty writes are the debt collector that only visits at night, which is precisely why they survive code review after code review.

Two smaller entries complete the ledger. The row-building logic exists as five near-identical sixty-line blocks — weekend, holiday, leave, badge, blank — copy-pasted and individually drifted, which is how the estate's bugs usually breed. And one block guards a non-nullable TimeSpan with if (min == null) — dead code that compiles, satisfies the eye, and checks nothing, the archaeological trace of a column that was once nullable.

Every row this job writes carries one more column I have carefully avoided until now: ModifiedBy. It sounds like an audit column. It is actually the estate's provenance system, its inter-job protocol and its approval-precedence mechanism, all encoded in magic strings like "SystemA" and "SystemEA". Next, ModifiedBy is a state machine.