ModifiedBy Is a State Machine

Every row the calculator writes and every row the leave sync rewrites carries a ModifiedBy column. You have one too — nearly every enterprise schema does, stamped by convention next to ModifiedDate, dutifully recording who last touched the row and consulted by approximately nobody. In this estate, ModifiedBy took a different career path. It became the mechanism by which five executables that never speak to each other negotiate ownership of individual rows. It is not an audit column any more. It is a state machine, and its alphabet is four magic strings.

An audit column's day job

The intended contract of ModifiedBy is passive: record the actor, ask nothing of it. The web application honours that contract — when a manager corrects a shift or approves a day, the row gets the human's identity. If that were the whole story, the column would be what it appears to be: forensic metadata, read by people, ignored by code.

The batch jobs broke the contract in the most natural way imaginable. They also had to stamp something, and "System" was the obvious value. Then a second code path needed to be distinguishable from the first, and "SystemA" was born. By the time the estate settled, four spellings were live in production data:

System    SystemA    SystemEA    SystemB

One per writing code path — the calculator's badge-derived rows, the leave sync's rewrites, the re-suggestion path, the backfill. Which suffix means which path is documented nowhere; the mapping exists only in the writers' source code, and reconstructing it means reading all of them. The suffixes are provenance — genuinely useful provenance, the difference between “a badge swipe produced this” and “a leave cancellation reconstructed this” — recorded in a scheme that only its authors could read, and by now perhaps not even them.

The predicate that runs the estate

Here is where the column stops being metadata. When the calculator revisits a row — a rerun, a recompute triggered by the work queue in part 8, a leave cancellation — it must decide: is this row mine to overwrite, or did a person touch it? The decision, everywhere it occurs, is this:

var isSystemRow = row.ModifiedBy != null &&
                  row.ModifiedBy.ToLower().Contains("system");

if (isSystemRow)
{
    // machine territory: recompute freely
    row.SuggestedShiftId = inferred;
    row.InputShiftId     = inferred;
    row.IsApproved       = AutoApprove(row);
    row.ModifiedBy       = "SystemB";
}
else
{
    // a human was here: suggestion may update, decision may not
    row.SuggestedShiftId = inferred;
    if (row.InputShiftId != inferred)
        row.IsApproved = false;        // demote: a person must re-confirm
}

Read the branch conditions again, because the design inside them is better than its encoding. Human edits win. A row a person has touched keeps its InputShiftId — the standing answer — no matter what the machine now believes. The machine's belief still lands in SuggestedShiftId, so the disagreement is visible. And if the recomputed suggestion contradicts the human's standing answer, the row's approval is demoted — not overwritten, demoted — pulling it back into a manager's queue for re-confirmation. That is a genuinely well-considered reconciliation policy: automation may inform, may escalate, but may never silently reverse a recorded human judgement. It is the same principle that runs through parts 4 and 5, and it is the single best design idea in the estate.

The encoding, though. The entire policy — the boundary between machine territory and human territory, on a table that drives allowances — rests on a case-insensitive substring match against a free-text column.

What a substring match cannot promise

In ascending severity:

1. The alphabet is open. Nothing constrains ModifiedBy to the four known values plus human names. A new code path that stamps "AutoProcess" — reasonable, descriptive — silently lands on the human side of the predicate, and its rows become untouchable to every recompute forever. The opposite error is worse: any writer that stamps something containing those six letters has marked its rows as machine-owned, whether it meant to or not.
2. Humans are matched by exclusion. A person is "anything that doesn't contain system". The web app writes human identities as names and IDs — and a name is user-shaped data. An account rendered as "Systems Administrator", a team login, a service desk user with an unfortunate display name: their careful manual corrections match the predicate and get bulldozed on the next run. I never saw it happen. Nothing prevents it.
3. The state machine has no diagram. Four system values plus a human namespace form the states; the writers' branch logic forms the transitions — System rows may become SystemB, human rows never become system rows, demotion fires on contradiction. None of this is written down anywhere except as scattered if statements across two codebases. When a new developer asks "can I recompute this row?", the answer requires reading every writer in the estate. The protocol exists; only the specification is missing.
4. The audit column no longer audits. This is the quiet, permanent cost. The moment code branches on ModifiedBy, the column's original job is forfeit. You cannot enrich it — switching to "System (leave-sync v2)" for better forensics breaks nothing visibly and changes ownership semantics invisibly. You cannot correct it. A column that logic depends on is frozen by that logic, and what froze here was the audit trail of a table that determines pay.

The honest version costs three columns

None of this needed inventing; the row was one migration away from saying what it means:

ALTER TABLE dbo.ShiftDetailMonthlyReport ADD
    WrittenBy     TINYINT NOT NULL DEFAULT 0,  -- enum: Calculator, LeaveSync, Web...
    IsHumanEdited BIT     NOT NULL DEFAULT 0,
    ApprovalState TINYINT NOT NULL DEFAULT 0;  -- Pending, AutoApproved,
                                               -- HumanApproved, Demoted

Provenance becomes an enum the schema can constrain. Ownership becomes a bit the predicate can test without string gymnastics. Workflow state becomes an explicit ladder — and notice that Demoted gets to exist as a state, instead of being inferable only as “approval false but input shift present and human-edited”. ModifiedBy goes back to its day job, free-text and trustworthy, holding names for auditors instead of protocol tokens for jobs.

The rule I carry forward from this table is blunt: the first time code branches on an audit column, stop and mint a real column. The branch is never wrong because the need is never wrong — provenance and ownership are legitimate, load-bearing facts. Smuggling them through a column with a different contract is what hurts, because it hides a state machine somewhere nobody will look for one, and state machines that nobody can see still run.

ModifiedBy governs who may touch a row. The estate has a second, stranger channel governing when rows must be revisited: a whole table whose rows are instructions from one executable to another, left like notes on a colleague's desk. Next, a work queue in a table named Tolerance.