Upgrade Windows & Legacy Data

Why this matters

Upgrades (EDC patches, validation engine updates, OS/database patches, new coding dictionaries) can silently change the rules that produce data, the lineage that explains those rules, or the readability of long‑term archives. A well‑run upgrade window protects both day‑to‑day operations and your ability to reproduce results years later.

Plain language on terms used

• EDC: Electronic Data Capture system used by sites and data managers.
• GxP: “Good Practice” regulations (e.g., Good Clinical Practice).
• 21 CFR Part 11: U.S. FDA rules for electronic records/signatures.
• Archive readability: You can still open and interpret archived files with approved tools.
• Checksum: A digital fingerprint (e.g., SHA‑256) used to prove a file didn’t change.
• Manifest: A small, structured file (often CSV/JSON/YAML) that lists what was delivered, its versions, and checksums.
• Fail‑closed: If structure is wrong, the pipeline stops rather than silently accepting bad data.
• Lineage: The traceable chain from source data through each versioned rule or derivation to a result, so you can explain and reproduce it.

Plan the window: impact & freezes

Every upgrade runs under change control (request, impact assessment, approval before production, filed test evidence): ICH E6 and 21 CFR Part 11 expect computerized systems to stay validated through changes.

1) Run an impact assessment (simple 1–2 pages):

• Scope: What is changing? (validation rules, edit checks, code lists, APIs/integrations, database drivers, security modules).
• Risk to blinding/access: Could any change expose treatment assignments or expand privileges?
• Data at risk: Which studies, environments (DEV/TEST/PROD: development, test, production), and data flows are touched? Will changed checks or derivations re‑run on data already entered?
• Performance/availability: Any expected downtime or throughput changes?
• Regulatory touchpoints: Does this change affect Part 11 controls, audit trails, or e‑signatures?
• Outcome: A risk rating that sets the regression and revalidation scope. The sponsor stays responsible for the assessment and the go/no‑go (a CRO may run them for it); vendor release notes are inputs, not a substitute.

2) Publish a freeze list (what must not change during the window):

• Derivations & algorithms (with version labels).
• Dictionaries/terminologies (e.g., MedDRA, WHODrug) versions, unless a dictionary up‑version is itself the approved change.
• Lock‑critical checks & listings used for interim/final locks.
• Interfaces (file layouts, endpoints) that downstreams expect.

3) Define rollback in advance:

• Technical steps: Snapshots/backups, DB restore points, image versions for app servers/containers/VMs (virtual machines). Keep users out until the go decision: a restore after sites enter new data discards it.
• Decision criteria: Specific “stop & roll back if…” (e.g., failure of blinding test, checksum mismatch on archive probe, any must‑pass failure after deployment, >5% regression failures), and who can authorize rollback.
• Evidence: Capture pre/post audit trail IDs, version numbers, and timestamps.

4) Communication & approvals:

• Who: System owner, CDM lead, QA/CSV (Computer System Validation), clinical ops, vendors, and sites (told of downtime in advance; serious adverse events (SAEs) still go from the site straight to PV (drug safety) on paper during downtime).
• When: Blackout window (when the system is closed to users and no other changes are made), go/no‑go meeting time, escalation path.
• What: Release notes, freeze list, test plan, rollback plan, owner for each test; training on changed screens or workflows before users get them.

What to test: fitness & readability

Think in two layers: (A) Fitness for intended use (does the system still do what users need?) and (B) Readability & continuity (did we preserve long‑term access and lineage?). Run the full test set first in a validated TEST environment that mirrors PROD; after deployment to PROD, and before users return, repeat a short set: the must‑pass and blinding/access checks on test sites and subjects, the audit‑trail comparison and the archive probes.

A) Fitness for intended use

• Regression tests: Re‑run a curated set of edit checks, derivations, workflows (query creation/closure, SAE reconciliation, code list updates).
• Negative tests: Prove the system blocks what it should (e.g., invalid visit windows, out‑of‑range values).
• Blinding/access tests: Confirm role‑based access control and blinded views still mask data.
• Performance smoke test (a quick check): Compare baseline run times (e.g., nightly batch, query dashboards).
• Failed test logging: Track failures, fixes, and re‑tests; nothing closes without evidence.

B) Readability & continuity

• Archive openability: Sample archived artifacts of each file type, by risk (e.g., CSV, XML, PDF/A, SAS XPT). Verify each checksum, open with the approved reader, and confirm basic results (e.g., row counts); store the readability report in the archive and reference it in the change control record.
• Checksums (“fixity”): Re‑calculate and compare to the archive index (the manifest listing every archived file’s checksum). Any mismatch triggers investigation and a restore from a verified good copy.
• Manifest validation: Ensure delivery/promotion manifests still parse and fields haven’t shifted or re‑typed.
• Integration guardrails: Use structural conformance (schema checks) that fail‑closed if formats drift.
• Lineage & audit‑trail continuity: Compare audit‑trail record counts before and after; confirm pre‑upgrade history is intact and readable. Re‑run key derivations on a frozen reference dataset: outputs match, or the changed rule has a new version and release note.

Tip: Keep the test evidence pack together—test scripts, results, screenshots, logs, and sign‑offs. It doubles as inspection evidence.

Legacy data strategies

Default approach: do not change legacy archives—prove you can still read them.

1. Compatibility layers (preferred). Keep the same environment that created the archive, accessible on demand:

• A frozen VM or container with the correct OS, drivers, runtime (e.g., Java/.NET), and viewer versions. It runs old, unpatched software, so keep it offline or network‑isolated; store its image and build recipe, each with a checksum, in the archive, and approve it only after it opens reference files whose checksums match.
• Document how to start it, the approved readers inside it, and how to export a viewable copy (PDF/A, CSV) for inspection requests.

2. Controlled migration (only if necessary). When a platform is truly end‑of‑life or a reader is no longer available:

• Plan: List source artifacts, versions, and hashes. Define target format/readers. Approve the migration under change control and validate the migration tool for this use before the run.
• Prove equivalence: Before/after checksums for files that shouldn’t change; row/record counts, variable names/types and value checks (all records, by program, where feasible) for re‑serializations (same data, new format, e.g., SAS7BDAT → SAS XPT).
• New manifest: Record the migration tool/version, parameters, date, operator, and new checksums.
• Chain of custody: Keep originals immutable and retain both manifests.

Golden rule: No silent reformatting. If you must migrate, make the change visible, versioned, and reversible (retain originals).

Worked examples

Example 1 — Minor EDC patch without data model changes

Scenario: Vendor releases 2025.2.4 patch fixing UI bugs and a single validation engine defect.

Plan & freezes

• Freeze: derivations, coding dictionary versions, and all lock‑critical checks.
• Impact assessment: Low risk to schemas; moderate risk to validation outcomes.
• Rollback: VM snapshot of app nodes; DB restore point taken 1 hour prior, with users already locked out.

Tests

• Fitness (in TEST before the window; the must‑pass and blinding/access checks repeated in PROD after deployment):

  • Re‑run 25 key edit checks (10 must‑pass, 10 negative, 5 blinding/access).
  • Open/close a site query and confirm audit trail entries.
  • Performance smoke on the nightly query aging job.

• Readability:

  • Open 5 archived CSVs and 3 SAS XPT files (a sample of each file type held) in approved readers.
  • Verify SHA‑256 checksums vs the archive index (10 files sampled, including the 8 opened).
  • Parse a historical promotion manifest to ensure the pipeline accepts it.

Outcomes

• 1 negative test failed (an invalid date slipped through). Vendor hotfix tested in TEST (failed test plus must‑pass set) before it went to PROD with the patch.
• Go decision documented; rollback not needed. Evidence pack filed in the validation repository.

Example 2 — OS/database patch where archive readers might break

Scenario: Security patch updates the OS and DB client library. Legacy SAS XPT viewer is sensitive to library versions.

Plan & freezes

• Freeze list includes all archive readers and their configs.
• Pre‑stage a compatibility VM containing older library + XPT viewer.

Tests

• Fitness: Connectivity, data extracts, reconciliation jobs.

• Readability:

  • Attempt to open 10 legacy XPT archives on patched servers (expected to fail).
  • Verify checksums, then open the same files inside the compatibility VM (expected to succeed).
  • Record which reader/VM image was used.

Outcomes

• Patched servers cannot open XPT; compatibility VM succeeds.
• Decision: Keep the VM as the approved legacy access path, network‑isolated because its old library is no longer patched, and document the process. No migration performed.

Example 3 — Controlled migration of legacy PDF to PDF/A for inspection readiness

Scenario: A regulator requests PDF/A copies of legacy PDFs (not guaranteed to be PDF/A).

Plan

• Source set: 1,200 PDFs (2008–2012) with SHA‑256 checksums in legacy manifest.
• Target: PDF/A‑2b. Keep originals unchanged.

Execution

• Tool: “PDF‑Normalizer v4.1” (documented and validated for this conversion before the run).

• For each file:

  • Validate original checksum vs legacy manifest.
  • Convert to PDF/A‑2b; validate conformance; generate new checksum; save the validation report as a separate file (PDF/A‑2b may embed only PDF/A files).
  • Update a new migration manifest (see sample below).

• Sample 5% (60 files) for human readability spot‑checks against the originals (table borders, fonts, bookmarks); compare page counts for all files.

Sample migration manifest (YAML)

batch_id: PDFA-2025-07
source_collection: legacy-pdf-2008-2012
operator: A. Lee
tool: PDF-Normalizer
tool_version: "4.1"
tool_parameters: "profile=2b; embed_fonts=all; image_downsampling=off"
change_id: CHG-3051
run_started_utc: 2025-07-03T14:15:00Z
items:
  - src_path: /archives/2009/INV001/visit01.pdf
    src_hash_sha256: 5e2d...a9
    dest_path: /archives_pdfa2b/2009/INV001/visit01.pdf
    dest_hash_sha256: e1c9...72
    pdfa_profile: 2b
    validation_report_path: /reports/visit01.pdf.txt

Outcomes

• Originals retained; new PDF/A set delivered with manifest and reports.
• Inspection packet includes conversion rationale, sampling plan, and evidence.

Pitfalls & misconceptions

• Misconception: “Upgrades require migrating archived content.” Reality: They don’t. Prove readability instead; migrate only with a formal, traceable process.
• Treating a “minor” version as “no risk.” Minor patches can change validation logic or default behaviors. Always perform an impact assessment.
• Changing derivations without versioning/release notes. This breaks lineage. Every changed rule needs an ID, version, and date.
• Loose integration checks. If your pipeline does not fail‑closed, silent format drift can corrupt data downstream.
• No rollback criteria. “We’ll decide on the day” is not a plan—define hard triggers to roll back.

Practice checklist

• Complete and circulate a 1–2 page impact assessment (scope, risks, affected studies).
• Publish a freeze list (derivations, dictionaries, lock‑critical checks, interfaces).
• Backups/snapshots taken; rollback criteria documented and owners assigned.
• Run fitness tests (regression + negative + blinding/access + performance smoke).
• Run readability tests on sampled archives; verify checksums vs manifest.
• Validate integration/manifest parsing; ensure fail‑closed behavior.
• Store a compatibility VM/container and document how to access it.
• If migrating, keep originals immutable and create a migration manifest.
• Compile an evidence pack (results, logs, screenshots, sign‑offs) for inspections.

Key points to remember

• Upgrade windows follow a simple cadence: assess → freeze → test → communicate → document → decide (go/rollback).

• Separate two goals and test both:

  • Fitness for intended use — run targeted regression and negative tests, include blinding/access checks, and a performance smoke against baseline.
  • Readability & continuity — sample archived artifacts with approved readers, verify checksums against the manifest, validate that manifests/integration contracts still parse, and confirm audit‑trail history and key derivation outputs carry over unchanged.

• Publish and honor a freeze list: lock derivations/algorithms (version‑labeled), coding dictionaries/terminologies, lock‑critical listings/checks, and external interfaces that downstream systems depend on.

• Plan rollback in advance: take snapshots/restore points and image versions, define explicit “stop & roll back if…” triggers, record who can authorize rollback, and capture pre/post version and audit‑trail IDs.

• Prefer readability over migration: keep legacy access paths (e.g., a compatibility VM/container with the exact OS, drivers, runtimes, and approved readers). Use them to open legacy archives rather than altering the archives themselves.

• Migrate only when truly necessary and make it traceable: originals remain immutable; prove equivalence (e.g., counts, variable names/types and value comparisons for re‑serializations); issue a new migration manifest capturing tool, version, parameters, operator, date/time, and new checksums—retain both manifests.

• Enforce fail‑closed guards: schema/structure checks for feeds and manifests must reject drift rather than accept malformed data.

• Protect blinding and access controls: after upgrades, re‑confirm role segmentation, masked views, and that no elevated privileges or unblinded fields have leaked.

• Maintain evidence and lineage: keep a single “evidence pack” (test scripts, results, screenshots, logs, sign‑offs, version numbers, timestamps) to support inspections and reproduce outcomes.

• Communicate clearly: circulate the impact assessment, freeze list, release notes, test plan, rollback plan, the blackout window, and escalation paths with named owners.

• Version everything that can influence results: any change to rules/derivations must have an ID, version, date, rationale, and appear in release notes.

• Default stance: do not change archived content; prove archive accessibility and integrity with approved tools and checksum confirmation.