Immutable As‑Received Files & Chain of Custody

Why this matters

When anyone asks, “What exactly did we receive on March 10?”, you must show the unaltered original file and the path it took into your database. If originals are overwritten—or if you depend only on a vendor’s system—you lose traceability and weaken inspection readiness. Keeping immutable, as‑received copies with clear lineage is the simplest way to prove your study data is trustworthy, reproducible, and complete.

Core concepts & definitions

• As‑received archive A read‑only (write‑once) store that keeps each external delivery exactly as it arrived. It includes a checksum (a digital fingerprint), the date/time received, the sender, and other basic details. This is your “camera photo” of the delivery—untouched and permanent.

• Checksum (e.g., SHA‑256) A long string computed from the file’s content. If the file changes by even a single character, the checksum changes. Compare it with the checksum the vendor supplies to show the file arrived intact; recompute it later to show nothing has changed since.

• Write Once Read Many (WORM) / immutable storage A storage setting or location where files cannot be edited or deleted after they are written. Think of it as sealing the envelope in a vault.

• Promotion path (lineage layers) The controlled steps a file passes through: Received → Staged (validated) → Curated (analysis‑ready). Each step is logged so you can show how data moved and what checks were performed.

• Version tag A short label that travels with the data (e.g., lab_2025-03-10_run02). Here: the lab’s delivery of 2025‑03‑10, second run (the corrected file, re‑sent two days later); the date names the delivery, not the day the file arrived. With algorithm_version (the version of the processing code), it lets you trace every row back to the exact source file and the code that loaded it.

• Corrections / re‑deliveries New or fixed files from a vendor. They add to history; they never replace prior files. Superseded data is clearly marked, not deleted.

• Chain of custody A record of who handled the file, when, and how—including automated steps (e.g., “loaded by pipeline X at 14:03”). It proves control over the data during its life cycle.

The process, step‑by‑step

1. Land & fingerprint (as‑received)

   • Save the file to immutable storage immediately on arrival.
   • Record received_at (with its time zone), received_from, original_filename, checksum, and expected file type.
   • Compare the checksum with the one the vendor supplied. If they differ, do not load the file: log the failure and ask the vendor to send it again. The failed file stays in the archive; the re‑sent file is a new receipt.

2. Log basic metadata

   • Capture schema_version (expected vs. detected), record counts (rows), and a short ingest_notes field.
   • If the vendor sends a manifest (its list of the files sent, with record counts and checksums), store it alongside the file and check your record count against it.

3. Validate in staging (do not touch the archive)

   • Check structure (columns, types), required fields, date formats, and controlled terms.
   • Capture validation results (pass/fail, warnings) in a promotion log: the append‑only audit log of every move between layers (entries are added, never edited).
   • If a technical clean‑up is needed (e.g., trim whitespace), do it with versioned code on staging copies, never by hand and never in the original.
   • A file that fails validation is not promoted and not deleted: log the failure and ask the vendor for a corrected re‑delivery. A wrong value is the vendor’s to correct and re‑send; data management never changes a value in vendor data.

4. Promote to curated with version tags

   • Load clean data into curated tables with columns like: source_file_tag, source_checksum, algorithm_version, load_batch_id.
   • Keep the promotion log: who promoted, when, from which as‑received file, using which code version. Log the record counts too: every row received is accounted for (promoted, or not loaded with the reason logged), so nothing is lost or duplicated.

5. Handle corrections / re‑deliveries

   • Store the new file as a new as‑received copy and log the vendor’s reason for the re‑delivery.
   • In curated, mark prior rows as superseded or set an effective_end_date.
   • Never delete history; new data sits beside old with clear “current” indicators.

6. Expose lineage in outputs

   • Include source_file_tag and algorithm_version in extracts, listings, and reports.
   • This lets reviewers—and you—trace a single result back to the exact file and code that produced it.

Tip: Automate steps 1–6 using a small intake script that calculates and compares checksums, records the intake metadata, and writes to the promotion log. The script is study code: test it and keep it under version control.

What good looks like

• A WORM/immutable “as‑received” bucket with restricted write access, a retention lock (no deletion before the study’s retention period ends), and checksum verification.
• A promotion log linking each curated load back to a single as‑received file and code version, with validation outcomes.
• Curated tables that carry lineage columns such as: source_file_tag, source_checksum, algorithm_version, load_batch_id, effective_start_date, effective_end_date, is_current.
• Clear re‑delivery handling: superseded records remain visible historically; current records are obvious for analysis.
• Chain‑of‑custody visibility across people and systems (e.g., personal user IDs for people, service account names and job IDs for automated steps, timestamps).

Worked examples

These examples are self‑contained. They restate key ideas so you can follow them without other sections.

Example 1 — Central lab correction (Week‑12 sodium)

Context

A central laboratory sends a Week‑12 results file on 2025‑03‑10. Two days later (2025‑03‑12) it sends a corrected file fixing sodium values for 12 participants.

What you do (step‑by‑step)

1. Save both the original and the corrected files to the as‑received location (immutable). Compute and store checksums for each, and compare each with the checksum the lab supplied.
2. Log: who sent it, when, file names, record counts, and lab schema version.
3. Validate the corrected file in staging (column checks, units, ranges) and compare it with the first file: only the 12 announced sodium values should differ.
4. Promote corrected rows to curated with source_file_tag = lab_2025-03-10_run02 (the original rows carry lab_2025-03-10_run01).
5. In the curated table, mark the earlier rows for those 12 participants as superseded (set effective_end_date and is_current = 0).
6. Keep the earlier file and rows for audit. Your listings now show current values, but history is preserved. Record the lab’s reason for the correction in the promotion log, and re‑run the lab–CRF reconciliation for the 12 participants.

Why this works

• You can prove exactly what the lab sent (twice).
• You can show the moment you switched to corrected values.
• Reproducibility is intact because every row carries source_file_tag and algorithm_version.

Example 2 — ePRO (electronic patient‑reported outcomes) daily exports with gaps

Context

An ePRO vendor delivers daily CSVs. Day 15 is missing; Day 16 arrives and later the vendor backfills Day 15.

What you do

1. As‑received: store Day 16; later store Day 15 when it arrives—both immutable with checksums.
2. Day 15 is missing from the daily series: flag the gap and ask the vendor for the file. Staging validates each file as it arrives.
3. Day 16 is promoted once it passes validation, and Day 15 when it arrives (unless the transfer spec requires files to be applied in order; then Day 16 waits in staging). Rows are ordered by the date in the data, not by arrival order.
4. Curated rows carry source_file_tag values like epro_2025-07-16 and epro_2025-07-15.
5. Downstream reports read the is_current rows and show their load_batch_id, so a report run before the backfill can be reproduced.

Why this works

• Late files are handled without manual edits.
• Auditors can see the exact delivery pattern and your controlled response.

Example 3 — Imaging re‑read (blinded central review)

Context

An imaging core lab sends a set of reads. A blinded re‑read is issued later.

What you do

1. Store both drops as separate as‑received files with unique checksums.
2. Promote re‑read results with a new source_file_tag and do not overwrite prior reads.
3. In curated, use effective_start_date/effective_end_date or a read_version flag to indicate the read that drives analysis. Which read that is comes from the imaging review charter and the statistical analysis plan, not from load order.

Why this works

• You maintain a full audit trail of reads and re‑reads without losing earlier evidence.

Lightweight implementation pattern

Directory layout

/as-received/
  2025-03-10/
    lab_results_week12_v1.csv
    lab_results_week12_v1.sha256
    manifest_2025-03-10.csv
  2025-03-12/
    lab_results_week12_v2.csv
    lab_results_week12_v2.sha256
    manifest_2025-03-12.csv

/logs/
  promotion_log.csv   # one row per load: who, when, from_file, checksum, algorithm_version, outcome

Minimum fields to track

• original_filename, received_at, received_from, checksum_sha256, record_count, schema_version, ingest_notes
• In curated: source_file_tag, source_checksum, algorithm_version, load_batch_id, is_current, effective_start_date, effective_end_date

Pitfalls & misconceptions

• Anti‑pattern: Keeping only an edited “working” file and discarding the original.
• Misconception: “The vendor has an audit trail, so we don’t need our own.” The vendor’s audit trail covers only the vendor’s system. Your as‑received archive is your own proof of what reached you, and when; it does not replace the source records held by the site or the vendor.
• Anti‑pattern: Overwriting to “keep it clean.” Clean analysis comes from clear supersession, not deletion.
• Pitfall: Missing checksums or not verifying them on restore—making it impossible to prove integrity.
• Pitfall: Lineage fields (source_file_tag, algorithm_version) missing from extracts—breaking traceability in outputs.
• Pitfall: Letting too many people write to the archive—undermines immutability and chain of custody. Limit read access too: some deliveries (e.g., drug concentrations) could unblind the study team.

Practice in the wild (checklist)

• Land every delivery to immutable storage; capture checksum and basic metadata.
• Keep a promotion log linking curated loads to as‑received files and code versions.
• Treat corrections as new versions; never overwrite earlier files or rows.
• Include source_file_tag and algorithm_version in curated tables and in study extracts.
• Use is_current and effective dating to manage superseded records.
• Restrict write and read permissions; include people and automated jobs in chain‑of‑custody logs.
• Test restoration: re‑compute checksum after restore to confirm integrity.

Key points to remember

• Keep an immutable, as‑received copy of every external delivery with a checksum (checked against the vendor’s) and basic intake metadata (who sent it, when, original filename, record count)
• Move data through Received → Staged (validate) → Curated (analysis‑ready) and capture each step in a promotion log with who/when, source file, checksum, code version, and outcome
• Never overwrite originals—treat corrections/re‑deliveries as new as‑received files with new checksums; in curated, supersede prior rows using is_current and effective dating
• Carry lineage fields end‑to‑end—at minimum source_file_tag, source_checksum, algorithm_version, and load_batch_id—and include them in all outputs (listings, extracts, analysis datasets) for traceability
• Maintain chain of custody across people and systems with restricted access, personal user IDs for people, service account IDs/job IDs for automated steps, and timestamps
• Verify integrity on restore by recomputing and matching checksums
• Rule of thumb: Immutable originals + promotion log + lineage fields = defensible provenance