Reconciliation Gates for Database Lock

Why this matters

A database can only lock quickly if everyone knows the gates and keeps them current throughout the study. When criteria are vague (“we’ll finish reconciliation later”), teams face last‑minute surprises and weeks of delay. When criteria are explicit, tracked, and evidenced, a prepared team can lock within days of LPLV (Last Patient Last Visit).

Plain‑language idea: “Gates” are the must‑be‑true items before we set the database to read‑only for analysis. Each gate has (1) a clear definition, (2) proof it’s done, and (3) an accountable owner.

Core gates & definitions

Glossary used below: EDC = Electronic Data Capture (your study database). CRF = Case Report Form. LPLV = Last Patient Last Visit. FPI = First Patient In. DMP = Data Management Plan. DM = Data Management. ClinOps = Clinical Operations. IWRS = Interactive Web Response System (randomization; also called IRT). PV = Pharmacovigilance (safety system). AE/SAE = Adverse Event / Serious Adverse Event. MedDRA / WHODrug = Dictionaries used to code events and medications. SAP = Statistical Analysis Plan. Adjudication = Independent committee confirms endpoint outcomes.

Non‑negotiable gates

• External reconciliations complete All external sources that must match the EDC are reconciled per the agreed specification: central labs, local labs (if included), devices/eCOA/ePRO, PK/biomarker vendors, randomization/IWRS, and PV safety cases. No unresolved presence gaps (a record exists in one source but not the other) and no open key value mismatches (e.g., visit date, randomization number, subject identifiers; the treatment arm itself is compared only by unblinded staff).

• Expectedness closed “Expectedness” means every page that should exist actually exists. Based on protocol schedule + subject status, there are no expected‑but‑missing CRF pages for analysis‑critical forms (e.g., disposition, treatment, primary endpoint visits). The listing is often called the missing‑pages report. A visit that did not take place is recorded by the site as not done, so it no longer counts as missing.

• Critical queries closed All critical queries (those that could affect primary/secondary endpoints, safety assessments, treatment assignment, or subject status) are closed (open count zero). Non‑critical (minor) queries are closed by lock too; one that cannot be closed is an exception, kept within the DMP’s age/backlog thresholds, listed with rationale (why it cannot be closed and does not impact analysis) and approved.

• Coding final All AEs, medical histories, and concomitant medications are coded to the MedDRA/WHODrug versions named for the analysis (one version of each for the whole study), with QC completed. Dictionary versions are frozen and recorded.

• Adjudication complete Endpoint adjudication committee decisions are final and tracked. No “pending” or “needs re‑review” statuses remain for analysis endpoints.

• Derivations version‑locked Algorithms that create derived variables (for example, baseline definitions, windowing rules, endpoint flags) are frozen with version numbers. Any edit checks or scripts that support these derivations are under change control.

• Signatures and verification complete Investigators have signed every casebook after its last data change; source data verification (SDV) is complete as the monitoring plan requires.

• Sign‑offs recorded Written approvals from Data Management, Biostatistics, Medical/Pharmacovigilance, and Clinical Operations confirm that gates are met and the database is ready for lock.

Gate → evidence → owner (quick matrix)

The process, step‑by‑step

1. Publish gates in the DMP early (before FPI).

   • Write the exact gate statements, the listings you will use to prove them, and who signs them.
   • Include owner and frequency (e.g., “external recon log updated weekly”).

2. Run a lock rehearsal (mock cut).

   • ~6–8 weeks before LPLV, generate “near‑final” listings/exports as if you were locking today.
   • Score each gate red, amber or green (RAG). Capture gaps, root causes, and owners.
   • Align with Biostats on derivation versions and with Coding on dictionary versions.

3. Burndown plan.

   • A burndown is a dated plan in which the count of open items falls to zero by the lock date. Translate rehearsal gaps into a weekly target: queries closed, recon exceptions to zero, expectedness to zero. Each gap is closed by the data’s owner (site, vendor or PV); DM raises and tracks the queries and edits neither side.
   • Publish a simple RAG dashboard and meet weekly to accept/retire risks.

4. Freeze (when gates are met).

   • Freeze derivation algorithms, coding dictionaries, and key configurations.
   • Snapshot audit trails and record software versions (EDC, coding tools, recon scripts).
   • Freeze the data in EDC (no edits until unfrozen), then produce a test export and verify it against the export specification and the SAP requirements.

5. Lock ceremony.

   • Obtain final functional sign‑offs.
   • Set the EDC to read‑only (site/user permissions adjusted per SOP), then take the final analysis‑ready export with checksums (digital fingerprints of the files). In a blinded study, treatment codes are released for the final analysis only after lock.
   • Archive the lock package (see below) in the study repository.

6. Post‑lock governance.

   • Define a decision tree for discoveries: impact assessment → decision to annotate vs. unlock → actions, documentation, re‑exports, and re‑sign‑offs if needed. “Annotate” means documenting the issue and its handling without changing the locked data. An unlock needs documented approval from the functions that signed the lock and opens only the affected records.

What good looks like

• One‑page gate dashboard with RAG status, counts (e.g., recon exceptions = 0), owners, and next milestone date.

• A dated, versioned lock package that includes:

  1. reconciliation logs,
  2. coding completion/QC and dictionary versions,
  3. expectedness proof,
  4. derivation/algorithm versions and change‑control IDs,
  5. final query metrics with rationale for any residual minors,
  6. endpoint adjudication completion list,
  7. investigator signature and SDV status reports, and functional sign‑offs,
  8. export specification, checksums, and validation notes,
  9. audit trail snapshot instructions/pointers.

• A brief, tested post‑lock decision tree for handling discoveries without panic.

Worked examples

These examples are self‑contained and re‑introduce any terms used.

Example 1 — Cardiovascular (CV) trial: locking 3 days after LPLV

• Context: EDC (study database) + PV (safety system) + central lab + IWRS (randomization). LPLV (Last Patient Last Visit) is a Monday; the days below are working days.

• Rehearsal (6 weeks prior): Found 3 presence mismatches between PV and EDC for SAEs (Serious Adverse Events). Also 12 non‑critical data queries >45 days old. These were PV cases with no SAE form in EDC: DM queried the sites, which entered them. Burndown created with owners and weekly targets (12 aged queries: 2 closed, or documented as unresolvable, per week for 6 weeks).

• At the final gate check (Day 2):

  • External recon logs show 0 exceptions for PV↔EDC, labs, and IWRS.
  • Expectedness listing shows 0 missing pages for disposition, treatment, and primary endpoint visits.
  • Critical queries = 0; 7 minor queries remain with written rationale (do not affect endpoints/safety).
  • Coding 100% complete with QC; MedDRA and WHODrug versions recorded.
  • Adjudication: all MACE (major adverse cardiovascular event) endpoints adjudicated.
  • Derivations frozen and tagged (baseline window, endpoint flags).
  • Investigator signatures in place for every casebook; required SDV complete.

• Lock timeline:

  • Day 0 (LPLV, Monday): Sites enter the last visit; final lab, IWRS and PV transfers requested; export rehearsal repeated.
  • Day 1: Final transfers loaded and reconciled; monitors complete the required SDV; last queries answered and closed (the 7 minor ones that cannot be closed are documented with rationale); last terms coded; investigators then sign the casebooks.
  • Day 2: Gate listings re‑run (results above); data frozen; audit trail snapshot plan executed; functional sign‑offs (DM, Biostats, PV/Medical, ClinOps).
  • Day 3 (Thursday): Lock ceremony: read‑only set; final export with checksums goes to the analysis team; lock package archived.

Example 2 — Post‑lock discovery: ten missing AEs

• Situation: Two weeks after lock, a site emails ten AE (Adverse Event) reports missing from the EDC.

• Action path (decision tree):

  1. Triage: DM assembles Biostats and Medical Safety to assess analysis impact (primary/secondary endpoints, key safety tables, labeling‑relevant summaries). Any event that meets a seriousness criterion is reported by the site to PV at once, whatever the lock decision.

  2. Classify:

     • If no planned safety or efficacy output would change (e.g., non‑serious events that began outside the treatment‑emergent period, the window set in the SAP in which the safety tables count events), document as no analysis impact → record the events and the approved decision in a post‑lock memo (and in the clinical study report where the SOP requires it), and proceed without unlock. Causality alone is not a reason: safety tables count events whether or not they are judged related.
     • If events are serious or may change safety rates or censoring/endpoint status, prepare to unlock.

  3. If unlock is needed:

     • Get written approval to unlock and open a change‑control ticket; re‑enable minimal permissions; the site enters the events from its source records (DM never keys them) and the investigator re‑signs; code the new terms and re‑run reconciliations and affected derivations.
     • Collect re‑sign‑offs and re‑lock.
     • Produce new exports with new checksums from the re‑locked database, and update the lock package with an addendum summarizing what changed and why.

Pitfalls & misconceptions

• “Lock always takes weeks.” Not if gates are defined early, owned, and kept current—well‑prepared studies lock in days.
• Deferring external reconciliation. Leaving PV/labs/IWRS matching to the end creates large, high‑risk work spikes.
• Ignoring expectedness. You can have “clean” values but still miss required forms—analysis will fail.
• Assuming every post‑lock issue requires unlock. Start with impact assessment; annotate when justified.
• Unfrozen derivations. Changing algorithms after analysis starts leads to version chaos and rework.
• Untracked dictionary versions. Mixed MedDRA/WHODrug versions produce inconsistent counts.

Practice in the wild (checklist)

• Define gates + owners in the DMP before FPI; review monthly in governance meetings.
• List the evidence you will attach for each gate (report names, filters, and where they’re stored).
• Rehearse lock 6–8 weeks before LPLV; publish a burndown with weekly targets and owners.
• Freeze derivations and dictionary versions once gates turn green; record version IDs and change‑control numbers.
• Prove expectedness = 0 for critical forms before initiating sign‑offs.
• Zero critical queries; document rationale for minor residuals with analysis impact stated.
• Archive a lock package with reconciliation logs, coding QC, derivation specs, sign‑offs, exports, checksums, and audit trail instructions.
• Document a post‑lock decision tree (impact assessment → annotate vs. unlock → re‑exports → re‑sign‑offs).

Key points to remember

• Lock readiness = explicit gates kept current. Publish the gates early in the DMP, assign owners, and update evidence routinely so a prepared study can lock within days of LPLV.

• Core gates you must hit before lock

  • Externals reconciled: PV, labs, IWRS, devices/eCOA/ePRO, etc. have 0 presence gaps and 0 key value mismatches (prove with final reconciliation logs).
  • Expectedness closed: For analysis‑critical forms, expected‑but‑missing = 0 (prove with the expectedness listing).
  • Critical queries = 0; any non‑critical residuals sit within age/backlog thresholds with written rationale for why they do not affect analysis.
  • Coding final: 100% coded with QC complete; MedDRA/WHODrug versions frozen and recorded.
  • Adjudication complete: All endpoints adjudicated with no pending statuses. Plus to proceed: investigator signatures and required SDV complete, derivations version‑locked (algorithms/scripts frozen under change control) and cross‑functional sign‑offs recorded (DM, Biostats, Medical/PV, ClinOps).

• Expectedness vs. reconciliation—know the difference

  • Expectedness checks that required CRF pages exist per protocol schedule and subject status.
  • Reconciliation matches EDC vs. external sources on presence and key values.

• Freeze the right things at the right time

  • Derivation algorithms & programs, coding dictionary versions, EDC/config versions, audit‑trail snapshot, and the export spec, together with the data freeze in EDC—freeze and version them (the final export and its checksums follow once read‑only is set) to ensure repeatability and traceability.

• Follow the proven sequence

  • Lock rehearsal (mock cut) → burndown with targets/owners → freeze when gates turn green → lock ceremony (read‑only + sign‑offs) → archive the lock package → post‑lock governance.

• Post‑lock discoveries use a decision tree

  • Start with cross‑functional impact assessment (DM, Biostats, Medical/PV).
  • Annotate when analysis/safety won’t change; unlock only if results or safety outputs would change—then get documented approval, let the data’s owner make the updates (the site in EDC, never DM), re‑run affected checks/derivations, collect re‑sign‑offs, re‑lock, and re‑export.

• Operational habits that keep you fast

  • Keep external reconciliation and query aging current throughout the study (avoid end‑of‑study spikes).
  • Align early on what is “critical data” for the study and endpoints (study‑specific).
  • Maintain a one‑page gate dashboard (RAG, counts, owners, next milestone) and a complete, versioned lock package as evidence of readiness.