Building Duplicate Protection into a Real Flow: A Worked Example

The rest of our duplicate detection and idempotency series explains the pieces — where duplicates come from, how to detect them, why the ledger-plus-retry construction is the honest end state. This closing article assembles the pieces the way they actually get assembled in real life: after something goes wrong. It is the story of one ordinary nightly ingest, the incident that exposed it, and the retrofit that fixed it. It also shows the after-state with the log lines to prove it.

The company and names are invented; every technical detail is typical. If you run anything that receives files at night and loads them into anything else, this pipeline will feel familiar — probably uncomfortably so. Read it as a template: the incident you can skip by doing the retrofit first, and a design you can lift almost verbatim.

The Flow Before: Ordinary, Useful, Unprotected

A mid-sized distributor receives settlement files from a payments partner. The partner's automation uploads to the distributor's SFTP server each night, into an intake/ folder. At 02:00 a scheduled job on the ingest machine runs a script that has grown by accretion for years. Its logic:

1. List intake/ for files matching settle_*.csv.
2. For each file: if a file with the same name exists in archive/, skip it — the flow's entire duplicate defense.
3. Load the file's rows into the finance database (plain inserts).
4. Move the file to archive/.
5. Email a one-line summary: files and rows loaded.

Nobody designed this flow; it accreted. The script began life as a manual procedure, gained a scheduler, gained the archive check after an early same-name resend, and then went quiet. In operations, quiet reads as proof. By the time of this story the flow had run for years with a clean record. That is exactly why nobody had looked at it critically since the day it stopped being anyone's project.

Note the shape of the defense: a name check against the archive folder. It is not nothing — it stops the crude case of the same filename arriving twice — and it costs one directory lookup. The team knew resends happened occasionally and believed the check covered them. The three gaps are visible with this series behind you. A renamed resend sails through (nothing checks content). The check happens at listing time rather than at the load gate (a file arriving mid-run is invisible to it). The load itself is plain inserts with no memory (nothing downstream of the name check can say "I have seen these rows"). For years, none of that mattered. Then it did.

The Incident: One File, Two Loads, Nine Days of Silence

The partner's uploader, like most, retries on failure. Its filenames carry a datestamp and an attempt number: settle_YYYYMMDD_a01.csv, and on a retry, settle_YYYYMMDD_a02.csv. The team had never seen an a02 — retries had always happened before the nightly job ran, and the earlier attempt's file was overwritten or absent. The incident night threaded the needle:

# reconstructed timeline
01:57:40  partner uploads settle_YYYYMMDD_a01.csv — every byte arrives
01:59:55  server's success reply is lost; partner client times out
02:00:00  nightly job starts; sees a01; no name match in archive/; loads 1,240 rows
02:04:30  partner retry uploads identical content as settle_YYYYMMDD_a02.csv
02:15:00  next job run (operator re-ran "to be safe" after seeing a02 arrive)
02:15:12  a02 has a different NAME — name check passes it; loads the same 1,240 rows
02:15:40  a02 moved to archive/; summary email reports another successful night

This is the timeout-that-succeeded from where duplicate files actually come from, wearing its most common disguise. The retry arrived under a different name, so the only defense in the flow never even fired. Every row of that settlement now existed twice in the finance database. No error appeared anywhere, because nothing had failed — every component did its job. The double-posting surfaced nine days later, when a reconciliation against the partner's monthly statement would not balance.

The cleanup took two people most of a week. Finding the duplicated rows was the quick part. The slow part was everything the rows had touched in nine days. That included a revenue report circulated to management, a partner-facing statement, and a handful of automated decisions keyed to settlement totals. Each needed checking and, where wrong, correcting with documented adjustment entries. Meanwhile an auditor asked the question that stung most: how can you be sure this has not happened before? The incident review that followed was notably free of blame, because there was no one to blame. The partner's retry was correct. The operator's re-run was reasonable. The script did what it had always done. The finding wrote itself: the system had no memory, and every party had behaved safely except the pipeline.

The Investigation: What the Records Could and Could Not Say

Answering "has this happened before?" turned out to be the pivotal exercise, because it exposed exactly which records existed. The transfer server's own log was the strongest evidence. The server records every transfer, so both uploads — a01 at 01:57 and a02 at 02:04, same size, same account — were right there with timestamps and source addresses. (On a server like Sysax Multi Server, which logs every transfer to file and to a database, this is a query. The team's equivalent was an evening of grepping, guided by the techniques in reading transfer logs.) Arrival history: available.

Processing history was the hole. The job's log recorded row counts per run but not per file. The archive folder proved only that a file had been moved, not what had been done with its contents. The team could show two arrivals; they could not cheaply show two loads — that took database forensics. The retrofit's core requirement wrote itself: the pipeline needs its own durable, per-file memory of what it has processed. A sweep of the archive hashed files pairwise and compared the intake and archive areas folder by folder. The folder compare task in Sysax FTP Automation's wizard does this side-by-side listing for you. The sweep found two earlier renamed resends that had, luckily, arrived on weekends and been loaded only once each. Luck is not a control.

The Design: Five Decisions and Their Whys

The retrofit was scoped deliberately small — no new servers, no queue infrastructure, three days of work including testing. Five decisions, each with its reasoning:

1. Identity is name plus content hash. The incident proved names alone are not identity. Each completed file is hashed (SHA-256 — mechanics in hashing explained) on arrival; the hash is what makes a renamed resend recognizable. Files run tens of megabytes at most, so hashing adds seconds to the night.
2. The ledger is a SQLite table, not a flat file. Two writers exist in practice — the scheduled run and the occasional manual run — so the atomic claim matters. A UNIQUE constraint provides it without any locking code. The schema is exactly the one from detecting duplicates.
3. Claim before load, finalize after. The insert into the ledger happens before the database load, so a crash mid-load leaves a visible claimed row to investigate instead of a silent maybe. The claim is the last gate before the irreversible step — not a scan at the start of the run.
4. Duplicates are kept, logged, and counted — not deleted, not alarmed. Skipped files move to duplicates/ with a log line naming the original run. A weekly glance at the skip count watches for new duplicate sources; a lone skip pages nobody.
5. Reprocessing becomes a mode, not an improvisation. The same script gains --reprocess with a mandatory reason, an explicit file list, suppressed notifications, and append-only ledger events — the design from safe reprocessing. The team knew a "re-run history" request was coming eventually; the incident cleanup was one.

One prerequisite decision sat underneath all five: the job only touches files whose upload is genuinely finished. It relies on the server's upload-in-progress naming plus a size-stability wait — the arrival discipline from our partial-file safety series. Hashing a half-uploaded file would record the wrong identity, so completeness detection had to come first. The diagram shows the flow before and after the retrofit.

What They Deliberately Did Not Build

Half of a good retrofit is restraint, and the review notes recorded four rejections that are worth as much as the five decisions:

• No message queue or streaming platform. Correct technology for pipelines moving thousands of events a minute; comic overkill for a flow receiving a handful of files a night. The ledger delivers the same effectively-exactly-once outcome at this scale for the cost of one table.
• No nightly hash sweep of the whole archive. An early proposal was to hash every arriving file against every archived file. That re-reads gigabytes nightly to answer a question the ledger answers with one indexed lookup, because hashes are computed once and remembered. Detection compares fingerprints, not files.
• No request to the partner to stop retrying. The team briefly drafted that email and wisely deleted it. Retries are what make the partner's deliveries survive bad networks — the whole argument of our retry and error handling series. A partner who stops retrying converts future duplicates into future missing files, a strictly worse trade. The receiving side owns deduplication; that principle is what makes partner flows robust without cross-company coordination.
• No shopping trip for an exactly-once product. Any tool honestly delivering that outcome is running this same construction — at-least-once delivery plus an idempotent gate — somewhere inside. Since the gate must understand this pipeline's irreversible step (the finance load), it was going to be built here regardless.

The Code Shape

The heart of the retrofit is small enough to read in one screen. In Python-flavored pseudocode, with the ledger behind two functions:

for f in completed_files("intake/", "settle_*.csv"):
    h = sha256_of(f)

    claimed = ledger_claim(name=f.name, sha256=h, run=RUN_ID)
    #   INSERT INTO processed_files(...) VALUES (...)
    #   -> UNIQUE(file_name, sha256) violation means "already handled"

    if not claimed:
        prior = ledger_lookup(f.name, h)
        log("SKIP duplicate %s sha256=%s prior=%s", f.name, h, prior.run)
        move(f, "duplicates/")
        continue

    rows = load_into_finance_db(f)          # the irreversible step
    ledger_finalize(f.name, h, result="loaded", rows=rows)
    move(f, "archive/")
    log("LOADED %s rows=%d run=%s", f.name, rows, RUN_ID)

send_summary(RUN_ID)   # counts come from the ledger, not from memory

Some details are worth copying. The skip path does as much logging as the happy path. The summary email is generated from the ledger. So a rerun reports "0 new files loaded, 3 duplicates skipped" instead of re-claiming last night's numbers. There is no check() function anywhere — the claim is the check. That is what makes two simultaneous runs safe. The reprocess mode reuses everything above, swapping ledger_claim for an append of a reprocessed event when the reason flag is present.

The After-State: The Same Bad Night, Replayed

Weeks later, the same weather returned: a partner-side timeout, a retry, an a02 file. The log now tells a different story. Note that the operator's nervous 02:15 re-run, the very behavior that doubled the damage in the incident, is now completely harmless:

Mar 14 02:00:03  RUN run_0042 start; intake=1 file
Mar 14 02:00:05  LOADED settle_YYYYMMDD_a01.csv rows=1240 run=run_0042
Mar 14 02:00:06  RUN run_0042 done; loaded=1 skipped=0
Mar 14 02:15:01  RUN run_0043 start; intake=1 file (operator rerun)
Mar 14 02:15:04  SKIP duplicate settle_YYYYMMDD_a02.csv sha256=9f86d081...
                 prior=run_0042 — moved to duplicates/
Mar 14 02:15:04  RUN run_0043 done; loaded=0 skipped=1

Three quieter improvements matured with the ledger. The weekly skip review — a one-line query of skips per week — became the team's early-warning instrument. When a misconfigured second delivery path briefly appeared during a partner migration, the skip count jumped from roughly one a week to a dozen a night. The review caught it in days. The auditor's question ("how do you know it hasn't happened before?") now has a real answer. The ledger, cross-checkable against the server's transfer log, shows one loaded event per content hash, ever. And when the settlement transform later needed a bug fix applied to five weeks of history, the team ran --reprocess. They used a file list generated from the ledger — thirty-eight files, one rehearsal file first, notifications suppressed. The reprocessing was done in an afternoon, with audit lines explaining the whole thing.

The cultural change was quieter but just as real. Before the retrofit, "should we re-run it?" was a senior-person question, answered by reading the script and holding one's breath. After, it became a junior-person non-question: run it, watch the skip lines, done. The summary email now states loads and skips separately, so a rerun that did nothing says so in plain numbers. Nobody has re-litigated an ambiguous night since.

What It Cost, Honestly

Budget-shaped facts for anyone proposing the same work. Effort: three days. Half a day went to hashing and completeness checks, a day to the ledger and claim logic, and half a day to the reprocess flags. A day went to testing, including the deliberate double-run test from idempotency in plain words, which the flow now passes eight for eight. Runtime: seconds per night of hashing; a ledger growing by a few hundred bytes per file, backed up with everything else. Ongoing attention: the weekly skip review, five minutes. One incident cost two people most of a week, plus corrections, plus the standing embarrassment of an unanswerable audit question. Set against that, the retrofit paid for itself before the month was out. Most flows never get this protection because no one prices the alternative.

Adapting the Template to Your Flow

The retrofit generalizes with almost no imagination required. The sequence transfers. Make completeness detection trustworthy first (partial-file safety). Define identity as name plus hash. Add the ledger with an atomic claim in front of your one irreversible step. Divert skips to a kept folder with loud log lines. Generate summaries from the ledger. Add the reprocess mode while the code is open. Volume changes the physics but not the design. At very high file counts the ledger wants a real database server. At those file counts, the hashing wants to happen as files arrive rather than in one batch. An event-driven intake (see watch folders and event-driven transfer) moves the claim into the arrival handler. The gate stays the gate.

If you want the deeper reasoning behind each piece before you build, detecting duplicates is the ledger's construction manual. The article safe reprocessing is the rerun mode's manual. And where duplicates come from is the catalog of weather your version of this flow will face. This article was the assembly; those are the parts.

Frequently Asked Questions