Migrating 12 Years of Patient Records Without Losing Anyone

A woman arrives at OPD and says she was treated here about six years ago. The clerk searches her name and gets seven results. Three spellings of the same surname, two carrying an initial that may or may not be her husband's. Two records share a phone number. One shows a date of birth of 01/01/1980, which is what the old system wrote whenever the field was left blank. Upstairs, a consultant wants to know whether she has been on thyroxine since 2019, and the answer sits in one of those seven records, or in a scanned discharge summary nobody indexed.

That is what a data migration actually deals with. Not a million clean rows. Twelve years of a hospital doing its best under queue pressure.

Twelve years of good intentions

Nobody sets out to build a messy database. It is 9:15, thirty people are waiting, the patient cannot remember her MRN and has no card, and it is faster to register her afresh than to hunt. Multiply that by twelve years and several lakh registrations. The patterns repeat everywhere:

• The same person registered five times across a decade, spelt differently each time, because names transliterated from a regional script have no single correct English form.
• Ages recorded instead of dates of birth. A record saying "45 years" entered in 2014 tells you nothing useful today.
• Free-text diagnoses that were never coded. "?koch's", "CAD s/p PTCA", "fever ? viral, r/o dengue" — clear to the doctor who wrote it, invisible to any structured query.
• Scanned PDFs in folders named by date, with no MRN in the filename and no metadata inside.

A migration is an archaeology exercise with a technical deliverable.

The phone number is the only honest key

Names are unreliable. Dates of birth are often invented. MRNs are duplicated by design failure rather than malice. What survives twelve years reasonably intact is the phone number, because follow-up calls, reports and TPA queries depended on it — somebody had a reason to get it right.

It is still not clean. Families share one number across a husband, a wife and three children, and numbers change.

So matching has to be scored, not binary. Combine phone number, name similarity, gender and an age band, then sort every candidate pair into three outcomes: auto-merge on high confidence, human review queue in the middle, leave separate by default. The default matters more than the algorithm. A duplicate is an inconvenience; a wrong merge is a clinical incident, because it puts one patient's allergy history into another patient's chart.

ABDM helps forward, not backward. An ABHA number gives new registrations a clean national spine, but it does not retrospectively repair a decade of duplicates.

Migrate, archive, or leave behind

Three buckets, decided by the hospital, not the vendor.

• Migrate as live structured data: demographics, the current MRN plus every old MRN as a searchable alias, allergies, active problem list, current medication, admissions and discharge summaries, lab results within a clinically useful window, outstanding balances and open TPA cases.
• Archive as searchable read-only: old billing detail, lab results beyond the window, scanned documents. Openable from inside the chart, never poured into structured clinical fields.
• Leave behind entirely: audit logs, system tables, abandoned drafts, and the test patients every hospital has — "Test Test", "ABC XYZ", a staff member's own name used to check a printer in 2016.

A hospital that migrates everything as structured data ends up with twelve years of unreliable structure — worse than three years of reliable data beside a searchable archive.

The same discipline applies to free text. Do not auto-code a decade of diagnoses into ICD. Carry it across verbatim, flagged as legacy, and start coding properly from go-live. The urge to produce a tidy coded history is what generates a chart claiming a patient has a condition they never had.

Extract, map, validate, reconcile — then do it again

The loop is unglamorous and does not get shorter.

• Extract from a timestamped read-only snapshot, never from the live legacy database. Every run must be reproducible from that snapshot, or two runs cannot be compared.
• Map field by field, in a document signed by somebody from the hospital, not only by IT. It must state the ugly rules: what an age-only record becomes, what happens when gender is blank, what 01/01/1980 becomes — flagged as estimated, not deleted.
• Validate with rules that fail loudly. No record without an identifier. No discharge dated before its admission. No result without a patient. Counts by year and department, so a year that quietly lost half its OPD visits shows up.
• Reconcile against arithmetic. Records out must equal records in, minus documented merges and exclusions. Outstanding balances must tie back to the last trial balance finance signed.

Run at least three full dry runs. The first exposes schema surprises. The second exposes volume and timing, which matters when the real window is six hours on a Sunday morning. The third is a rehearsal against the clock. Publish the exception report after each; the count should fall and its shape change. If the same four thousand records fail three times running, nobody is fixing anything.

This is where tooling earns its place. In Kōami a transformed record keeps a pointer to its source row and the rule that shaped it, so when a consultant queries a date six months later the answer is a lookup, not an argument.

Proving to a clinician that nothing was lost

Clinicians do not read reconciliation reports. They test a system with the patients they remember, so run the acceptance the way they will run it anyway. Ask each department to nominate thirty to fifty of their own records — the diabetic on six drugs, the oncology follow-up, the patient readmitted four times in two years — and sit with them, old screen beside new. Trust is built by a consultant seeing their own note, in their own words, against the right date. No report produces that.

Then give them the archive link from inside the chart. "Everything before March 2021 is one click away, searchable by MRN" is an answer a clinician can work with. "It has all been migrated", when it visibly has not, ends confidence in the whole system.

Publish the exception list openly, including duplicates deliberately left unmerged and dates of birth marked as estimated. A documented gap is a managed risk; a hidden one surfaces at the worst possible moment. And give registration a merge request button rather than merge rights, so duplicates are flagged where noticed and resolved by people holding the records.

Nobody lost, some still doubled

At the end of a good migration you will still have duplicates. That is not failure, it is an honest state. There was never a perfect database to move.

What you are owed is this: nobody lost, every record traceable to where it came from, every remaining gap written down. The woman with seven records becomes one active chart with six aliases pointing at it, and when the consultant asks about thyroxine, 2019 is on the screen.