An idempotent daily backfill that's safe to run twice

Some of our balance-transfer leads get stuck. Their credit report has arrived in our system, but the lead is still sitting at the initiated status — the pipeline that's supposed to normalize the report, merge it, group the borrower's existing loans, and advance them to the next stage never ran, or ran halfway and stopped. Left alone, those leads sit in limbo and never see the refinance options we could offer them. It's a quiet failure: nothing is broken loudly, a few applicants just silently don't progress.

We fixed it with a daily job that sweeps up the stuck leads and pushes each one the rest of the way through. But the constraint that actually shaped the design wasn't "process stuck leads" — it was the certainty that this job will run on the same lead twice. A retry, an overlapping manual trigger, a lead that was half-processed in yesterday's run. Given that certainty, a second pass over a lead has to be a no-op, never a corruption. Everything else followed from taking that seriously.

Why this mattered

A backfill that isn't idempotent is a liability you schedule on a cron and then quietly fear. The first time it half-finishes and you re-run it, one of two bad things happens: it crashes on the work it already did, or — worse — it silently redoes that work. For a credit pipeline, redoing work isn't merely wasteful. Re-running a normalization or re-grouping loans that were already grouped can produce a second, conflicting view of someone's financial history, and now you have two answers to a question that should have exactly one. The cost of non-idempotency here isn't wasted CPU; it's data you can no longer trust.

There was a second, gentler requirement: the job had to stay cheap as the lead table grew. The lazy version scans every stuck lead every single night, most of which have no new information. We wanted the job to look only at leads where something actually changed recently, so its cost tracks the day's activity rather than the lifetime size of the table.

The approach

Two ideas carry the whole worker, and both are about doing less.

The first is an incremental candidate query. Rather than considering every lead stuck at initiated, we only look at leads whose credit report arrived in the last twenty-four hours. A lead processed by a previous run isn't revisited unless genuinely new data showed up for it. This is what keeps the nightly cost proportional to the day's inflow instead of the all-time backlog, and it's a one-line where clause on a timestamp — cheap insurance against the job slowly getting more expensive forever.

The second, and the one that makes the whole thing safe, is that idempotency lives inside each step, not in some job-level bookkeeping wrapped around the outside. We never wrote a "have I already processed this lead?" ledger. Instead, every step in the pipeline asks its own narrow question — "is my output already present?" — and returns success immediately if it is. Normalization checks whether a normalized report already exists and, if so, skips straight to done. The merge step reuses an existing unified report instead of rebuilding it. The loan-grouping pass treats "nothing to group" as a successful skip rather than an error. The backfill only fires when an active home loan is actually detected. Each step's "already done" path and its "do the work" path sit right next to each other in the code.

In Elixir this falls out of the language naturally: two function heads for the same step, one guarded to match the "already done" shape and return immediately, the other doing the real work. The steps then chain together with a with, so the first failure short-circuits the rest and a lead that fails at step two never touches steps three through five.

with {:ok, data} <- fetch_experian_data(mobile),
     :ok <- step_normalize_experian(data),
     :ok <- step_regenerate_unified(data),
     :ok <- step_bt_backfill(lead_id, mobile),
     :ok <- step_update_web_status(lead_id) do
  :ok
end

The third investment was in operator legibility, which for a job that runs unattended at dawn matters more than it sounds. Every step emits a structured log line carrying the stage it's in and its status — ok, skipped, or failed — through one shared helper, so the log of a run reads like a narration of exactly what the job decided and why. The run ends with a one-line tally, and we post a summary to Slack — but only when something actually changed. A night where every lead was already up to date stays silent, so the Slack channel carries signal rather than a daily "nothing happened" that everyone learns to ignore.

That "only notify on change" choice is small but deliberate. The fastest way to make an alert useless is to send it on every run; people tune it out, and then they tune out the run that mattered. By staying quiet on no-op nights, the summary becomes something worth reading when it does arrive.

What we learned

Pushing idempotency down to the step level, instead of guarding the job from the outside, made the worker both safe and self-documenting. A reader can look at any single step in isolation and answer "what happens if this runs twice?" without holding the entire pipeline in their head — and that local reasoning is what gives us confidence to let the job retry freely.

The honest cost is the per-step existence checks: every run pays a small read to ask "is this already done?" even on the common path where nothing is. For a once-a-day job over a one-day window, that's invisible. The same pattern run at high frequency would need a rethink, and we'd say so to anyone tempted to copy it wholesale.

There's also a subtler limitation we keep an eye on. A lead with no credit report is reported as skipped, which is correct — but a flood of skips can quietly mean an upstream fetch stopped delivering reports at all, and from inside this job that looks identical to a calm night. The skip count in the Slack summary is how we tell those apart, which is why watching that number is now part of how we know the systems upstream of this one are healthy. The most useful thing the job reports turned out not to be its own success, but the shape of its skips.