A Watermark Made of Your Own Success Log

The employee sync mirrors a full master every night, so it never has to remember anything. The leave sync runs every thirty minutes against a leave system that will happily return years of records, so it has to remember exactly one thing: where did I get to last time? Most systems answer that with a dedicated watermark — a row in a sync-state table, a blob checkpoint, a stream offset. This estate answers it with a trick I have never seen written up anywhere, and which I have grudgingly come to admire: the job's own success log is the watermark.

One table, three jobs

Recall StatusSheet from the estate map: every batch job writes a row per run.

CREATE TABLE dbo.StatusSheet (
    Id          INT IDENTITY PRIMARY KEY,
    JobName     NVARCHAR(20)  NOT NULL,   -- remember this width for part 9
    JobResult   NVARCHAR(10)  NOT NULL,   -- 'Success' / 'Failure' / worse
    Message     NVARCHAR(MAX) NULL,
    RunTime     DATETIME      NOT NULL,
    LastRecord  DATETIME      NULL,       -- a watermark, sometimes
    TotalTime   TIME          NULL        -- elapsed, when it fits
);

One table, three distinct responsibilities. It is the run log — operations queried it daily, and the notifier alone contributes roughly 288 heartbeat rows a day. It is the metrics store — Message carries counts like “112 leaves updated”, TotalTime carries duration. And for the leave sync it is the watermark: the next run reads its own history to decide where to resume.

var lastSuccess = db.StatusSheets
    .Where(s => s.JobName == "Leave Sync" && s.JobResult == "Success")
    .OrderByDescending(s => s.RunTime)
    .FirstOrDefault();

var since = lastSuccess?.RunTime.ToString("yyyy-MM-dd")
            ?? config["DefaultSyncDate"];          // "2021-01-16"

var leaves = await FetchLeavesFromApi(pageNumber: 1, modifiedDate: since);

If no Success row exists — first deployment, or a truncated table — the sync falls back to a config default and re-ingests from that date. The watermark is not a maintained piece of state; it is a derived piece of state, computed from evidence that work actually completed. That is the elegant part. A watermark table can lie — a crashed run can leave it advanced past data it never processed. A success log can only say “this run finished”, and if the run didn't finish, no Success row exists and the next run automatically re-covers the same window. Failure recovery isn't a code path; it is the absence of a row.

Two details make the re-covering safe. The watermark is truncated to a date, so every run re-fetches at least the current day's records — deliberate overlap. And the write side is an idempotent upsert keyed on the leave system's own LeaveId, so fetching the same leave twice converges to one row. Overlap plus idempotency is the whole trick: you stop caring about exactly-once delivery the moment reprocessing is harmless.

Ten at a time, recursively

The leave system pages its results, and the sync walks the pages in the most surprising control structure in the estate — recursion:

private static async Task<List<LeaveRecord>> FetchLeavesFromApi(
    int pageNumber, string modifiedDate, List<LeaveRecord> acc = null)
{
    acc ??= new List<LeaveRecord>();

    var page = await client.GetFromJsonAsync<LeaveEnvelope>(
        $"{baseUrl}?modifiedFrom={modifiedDate}&page={pageNumber}&size=10");

    acc.AddRange(page.Content);

    if (!page.Last)
        return await FetchLeavesFromApi(pageNumber + 1, modifiedDate, acc);

    return acc;
}

Each call fetches a page, appends to an accumulator threaded through the arguments, and recurses until the envelope says last = true. With a page size of ten — the API's default, never overridden — a busy fortnight of leave activity means dozens of sequential HTTP round-trips, each one adding a stack frame. It works, because the watermark keeps the window small; a thirty-minute delta rarely spans many pages. But the shape is a loop wearing recursion's clothes, and the page size is a config knob nobody turned. If the default sync date ever comes back into play — that truncated-table scenario — the same code fetches years of leave ten records at a time, recursively. The happy path and the recovery path have wildly different performance envelopes, and only the happy path was ever measured.

SaveChanges as a counter

The upsert loop contains my favourite small crime in the whole estate:

foreach (var leave in leaves)
{
    var existing = db.LeavesApi.FirstOrDefault(l => l.LeaveId == leave.LeaveId);
    if (existing is null)
    {
        db.LeavesApi.Add(Map(leave));
        leavesUpdated += db.SaveChanges();   // rows-affected as a tally
    }
    else if (Differs(existing, leave))
    {
        Apply(existing, leave);
        leavesUpdated += db.SaveChanges();
    }
    else
    {
        leavesUpdated += 1;                  // unchanged also counts?
    }
}

SaveChanges() returns the number of rows affected, and the code launders that return value into a business metric — then pollutes the same counter with +1 for rows it didn't touch. The number that ends up in the StatusSheet message is neither “rows written” nor “rows seen”; it is an accidental blend that looks authoritative in a log and means nothing. A metric you cannot define is worse than no metric, because someone will eventually graph it.

The performance cost is quieter but larger: SaveChanges per row is one database round-trip per leave record. EF Core would batch the whole loop into a handful of statements if the save were hoisted after the loop. In a thirty-minute delta this is invisible; in the recovery scenario it compounds with the page-size-ten pagination into a very long night. The same chatty-write habit turns up far worse in the shift calculator's backfill.

The envelope checked after the fact

And then the defect that undermines the elegant watermark. The API's envelope carries a response code, and the sync does check it — after the recursion has completed, the upserts have run, and the Success row has been written:

var data = await FetchLeavesFromApi(1, since);
WriteStatus("Leave Sync", "Success", $"{leavesUpdated} leaves processed");

if (data.Response.ResponseCode != 0)                 // too late
    WriteStatus("Leave Sync", "Failure", data.Response.Message);

A failing API response therefore produces both a Success row and a Failure row for the same run. That is confusing in a log, but the real damage is to the watermark: the next run resumes from the Success row's timestamp — which now sits past a window the API just said it couldn't serve properly. The self-healing property from the top of this article — no Success row unless the run truly succeeded — was quietly forfeited by writing the Success row one statement too early.

The durable lesson generalises past this codebase: a derived watermark is only as trustworthy as the predicate that writes its evidence. “Watermark = last success” is a genuinely good pattern; it collapses sync state and observability into one table and makes recovery automatic. But it makes the Success row a load-bearing record, and load-bearing records must be written last, after every validation, inside the same failure domain as the work. Write it early and you have rebuilt the lying watermark table you were trying to avoid — with extra rows.

What the sync does with those leaves once they land is the better story, though: a cancelled leave from three weeks ago can reach back and rewrite attendance rows that a human already approved. Next, when a cancelled leave rewrites last month.