A Work Queue in a Table Named Tolerance

Part 7's magic strings settle who owns a row. This part is about the estate's other coordination channel — the one that answers a different question: how does one executable tell another “redo your work for this employee, from this date”, when nothing in the estate sends messages? The answer is a table named Tolerance, and it is the purest expression of the series title: a work queue built from rows, complete with an action vocabulary, a consume-and-mark lifecycle, and a companion stored procedure that rewinds time. If you only ever read one part of this series to understand what “the database is the message bus” means in practice, read this one.

Why reruns exist at all

The calculator's normal mode is strictly forward: process each employee's badge data up to today, remember how far you got, continue tomorrow. But attendance data does not respect forward. Badge devices double-fire and produce duplicate events that need cleaning up. Aggregation runs land while raw data is still arriving. A support ticket reveals that an employee's gate events were attributed wrongly for a week. In every case the fix is the same shape: some employee's history is now wrong, and the pipeline must go back and re-derive it.

Going back is genuinely invasive here, because the pipeline is layered. Raw badge events sit in Transactions; a per-employee-per-day aggregate sits in LogRecorder; the report rows sit above that. A rerun cannot just recompute the top layer — the middle layer is stale too. Something has to delete the derived data, re-expose the raw data, and get the calculator to walk it again, without disturbing the hundreds of employees whose data is fine.

The queue is four columns

The signal for all of this is a row:

CREATE TABLE dbo.Tolerance (
    Id          INT IDENTITY PRIMARY KEY,
    EmployeeId  INT           NOT NULL,   -- whose history
    LastRecord  DATETIME      NOT NULL,   -- rewind to here
    EventId     INT           NULL,       -- the offending event, if known
    Action      NVARCHAR(50)  NOT NULL    -- what to do about it
);

Action is a string holding one of a small enum's values — ReportUpdate, LogRerun, LogRunnerInProcess, Duplicate — an enum whose C# name, in one of the estate's most endearing fossils, is spelled Tolorance while the table is spelled Tolerance. (The naming archaeology gets its full excavation in part 13.) A producer — the web app on a support action, a job detecting duplicates — inserts a row. The calculator, on its next run, queries for pending actions, does the work, and overwrites the row's action with Done. That is the entire lifecycle: insert, poll, process, mark. No broker, no subscription, no acknowledgement protocol; the row is the message, and Done is the ack.

The vocabulary is worth a second look, because it encodes a real distinction. ReportUpdate means “the aggregates are fine, re-derive the report rows” — the cheap rerun. LogRerun means “the aggregates themselves are suspect, rebuild from raw events” — the expensive one. Duplicate records that a double-fired badge event was involved, which a diagnostic proc (GetDuplicateEvents, hunting events that share a timestamp and employee but differ in event ID) helps surface. The queue does not just say redo; it says how deep.

The proc that rewinds time

The expensive path is where the design earns respect. Rebuilding an employee's aggregates means deleting forward from the rewind point and replaying raw events — and that is packaged as a single stored procedure:

CREATE PROCEDURE dbo.DeleteLogForRerun
    @EmployeeId INT,
    @FromDate   DATETIME
AS
BEGIN
    DELETE FROM dbo.LogRecorder
    WHERE  EmployeeId = @EmployeeId
      AND  LogDate >= @FromDate;

    SELECT EmployeeId, LogDateTime, NodeType, EventId, DoorDescription
    FROM   dbo.Transactions
    WHERE  EmployeeId = @EmployeeId
      AND  LogDateTime >= @FromDate
    ORDER BY LogDateTime;
END

Delete the derived layer, return the raw layer, let the caller re-aggregate. The employee's high-water mark — the “last processed” date the calculator tracks per person — gets rewound to @FromDate, and the normal forward machinery takes over from there, re-deriving LogRecorder and then the report rows as if the intervening days had never been processed. Replay is not a special mode; it is the normal mode pointed at an earlier date. That is the property worth stealing: the rerun path reuses the exact code the daily path runs, so it cannot drift out of agreement with it. Estates that build a separate “fixup” script for corrections end up with two implementations of the truth, and part 4 already showed how this estate's other duplicated logic drifted.

What the table buys that a broker wouldn't

It is easy to sneer at a hand-rolled queue table, so let me argue its side properly first.

• Enqueueing is transactional with the domain data. When the web app fixes an employee's record and inserts the rerun row, both happen in one database transaction. With a real broker that is the classic dual-write problem — the fix commits, the message send fails, and the systems disagree forever. Solving it properly needs an outbox pattern… which is a queue table. This estate simply started there.
• The queue is inspectable with a SELECT. Pending work, processed work, who requested what and when — one query. No management portal, no peek-lock semantics to learn, and the Done rows double as an audit history of every correction ever requested.
• Zero additional infrastructure. No connection strings, no SDK versions, no dead-letter queues to monitor. For a five-executable estate maintained by a handful of people, the operational surface you don't add has real value.

What it costs

The ledger, in ascending severity:

1. Done rows accumulate forever. The queue is also its own archive, which is charming until the table is 99% history and every poll scans past it. An index on Action mitigates; nobody added one.
2. The vocabulary is stringly-typed at both ends. A typo'd action in an insert is not an error; it is a message nobody will ever consume — the estate's recurring disease, meanings the schema cannot enforce.
3. There is no poison-message story. If processing a rerun throws, the row stays pending and the calculator retries it on every run, failing identically each time, forever. A real queue moves it aside after N attempts; here, one bad row can wedge the correction pipeline until a human deletes it.
4. Single-consumer is an accident, not a guarantee. No locking, no UPDLOCK/READPAST, no visibility timeout. Today exactly one consumer polls the table, so races cannot happen. The day someone adds a second calculator instance for throughput, two processes will consume the same row and double-process an employee's history — and nothing in the schema or code will even notice.

My verdict, honestly held: for this estate's scale, the table was the right call, and points 1–3 are afternoon-sized fixes. The pattern's real risk is that it looks finished. A broker's missing features announce themselves in the documentation you didn't read; a queue table's missing features announce themselves in production, at 2 a.m., one scaling decision later.

Every failure mode in this article ends the same way — an error handler writes a row to StatusSheet and the estate moves on. It is time to look hard at that error channel, because its most important write — the one meant to fire on the most alarming data condition in the calculator — has never once succeeded, and cannot. Next, the error logger that cannot log.