Five Executables, One Database — the Reckoning

Seventeen parts ago I claimed the database was the message bus, and promised an honest tour rather than a verdict. Now the verdict, because a retrospective that only lists sins is as dishonest as a case study that only lists wins. I built this attendance platform at a mid-size IT services company; it ran, it paid people's allowances correctly enough that they trusted it, and it survives in my memory as the most instructive thing I have worked on precisely because its cleverness and its damage share the same root. This is the ledger — what table-coordination cost, what earned its keep, and what I would reach for now.

What the architecture actually cost

Strip five executables of their queues and message contracts and the coordination doesn't vanish; it re-materialises as tables, and each table quietly re-implements — badly — a feature a real bus gives you for free.

No retries. The single biggest cost, and the one I feel most. A failed midnight org sync does not retry in five minutes; it waits twenty-four hours, because the failure was swallowed into a StatusSheet row and no exception ever reached the runtime to trigger re-execution. A real bus retries with backoff and dead-letters what it can't deliver. The table-bus has one delivery attempt and a log entry as a consolation prize. Every “swallow to a table” in the estate is a retry that will never happen.

No contracts. The integration schema is a DbContext in a zip file, copied per repo at whatever version each vendored. There is no versioned message contract, no schema registry, no compatibility check. When the model changes, agreement is maintained by discipline and a manual binary fan-out — and the six shift IDs defined in config and as literals show exactly how discipline decays: two writers, one meaning, no enforcement, silent drift.

No visibility semantics. The Tolerance work queue has no locking, no visibility timeout, no poison-message handling. It is single-consumer by luck, not design; one scaling decision away from double-processing, one bad row away from a wedged pipeline.

Invisible absences. The most insidious cost. The estate coordinates through a shared database, so it advertises capabilities that don't exist: a full Hangfire schema with no Hangfire code, a badge-ingestion writer that lives only as the tables it fills, a GraphQL search proc with no API, a Productivity gauge commented out but still in the markup. A message bus makes its participants enumerable — you can list the topics and their subscribers. A database bus makes them inferrable, and inference misses the ghosts. Half of understanding this estate was learning what wasn't there.

And running underneath all of it, the estate's signature failures: an error logger that cannot log, timezone roulette across three notions of “now”, a metric with three definitions, tests that test nothing, and a schema that enforces almost nothing. None of these is exotic. Every one is what happens when coordination has no contract and failure has no path but a log row.

What genuinely earned its keep

Now the other column, because there is a real one, and pretending otherwise would be the creator-voice cowardice of only owning the flattering half.

The self-healing watermark. Deriving the resume point from your own success log is genuinely good. A success row can only mean “this finished”; recovery becomes the absence of a row rather than a code path, and overlap-plus-idempotent-upsert makes reprocessing free. I would use the idea again — I'd just write the success row last, which is the one thing this estate got wrong about it.

Human-wins reconciliation. The rule that automation may inform, may escalate, but may never silently reverse a recorded human decision runs cleanly through the leave rewrite, the approval demotion, and the auto-approval bands. Auto-approve consequences you can retract; gate consequences you cannot. That principle is worth more than any framework, and this estate encoded it correctly everywhere it mattered — even where it encoded it in the wrong column.

Legible algorithms. Manhattan-distance shift inference is a 1-NN classifier that any employee can be talked through: “you were forty minutes off the start, ninety off the end”. In a system that decides people's pay, explainability beats accuracy, and the estate chose explainable every time.

Inspectability. Every piece of coordination state — work queue, watermark, run log, email template — is one SELECT away. Operators who would never open a telemetry portal were fluent in the database. That is a real operational virtue, and the queue-table's transactional enqueue genuinely dodges the dual-write problem a naive broker integration would have created.

The pattern I keep is replay-as-normal-mode: the rerun path pointed the daily code at an earlier date instead of forking a separate fixup implementation, so correction logic could never drift from production logic. That is the discipline the six-shift-IDs and the contractor-LIKE copies lacked, and it is why the reprocessing path was the estate's most trustworthy corner.

What I would build now

Not a grand rewrite — a targeted replacement of each table that was pretending to be infrastructure.

1. A real queue for the signals. Tolerance becomes a Service Bus queue or a storage queue: retries, dead-lettering, visibility timeouts and safe multi-consumer, none of which I would ever hand-roll again. Enqueue transactionally via a proper outbox so the dual-write win survives.
2. Durable orchestration for the pipeline. The gap-fill and rerun flows are long-running, stateful, resumable work — exactly Durable Functions' shape. Checkpointing and retry become the framework's job, not a StatusSheet row's.
3. An owned, versioned contract. The shared model ships from a real feed with semantic versioning and a compatibility gate, not committed .nupkg files fanned out by hand. Reference data — the six pseudo-shift IDs, the node-type constants — lives in the database as the single source of truth both writers read.
4. A schema that defends itself. Foreign keys where integrity matters, datetimeoffset or disciplined-UTC so every timestamp declares its clock, durations as integers, provenance and workflow-state as real columns instead of magic strings, and one conformed definition per KPI.
5. Honest pipelines. Delete the empty test stage or fill it; give the calculator a deployment path; make the green badge mean what it says.

The one thing I would keep

Would I use a database as a message bus again? For an estate this size, maintained by a handful of people, coordinating a few batch jobs — I would take the idea and refuse the implementation. The instinct that put coordination state in the database, transactional with the data it coordinates, inspectable by the people who operate it, was sound. What failed was treating hand-rolled tables as if they were finished infrastructure: a queue table that looks done has none of the retry, visibility and poison-handling machinery a queue's absence would have forced you to notice you needed. A missing bus announces itself; a fake one hides.

That is the whole estate in one sentence, and it is the same sentence as the empty pipeline and the logger that cannot log: the dangerous failures here were never the loud ones. They were the things that performed being finished — the green check, the success row, the watermark, the audit column, the KPI — while quietly doing less than they claimed. If this series has one durable lesson, it is to distrust the green light and go read the code behind it. The database was a fine place to keep the state. It was a terrible place to pretend the infrastructure already existed.

The web application that sits in front of all this — the dashboards, the CAS login, the resurrected Html.Action, the drag-drop report builder — has its own reckoning in its own series. If you came here from the hinterland, the front of the house is An Attendance Platform in ASP.NET Core. Thank you for reading all eighteen parts; the estate deserved an honest witness, and I hope this was one.