Cleaning Legacy Deviation and CAPA Data Before an eQMS Migration

Why Deviation and CAPA Records Are the Hard Part of an eQMS Data Migration

When a pharma or biotech company replaces its quality management system, the document side of the migration usually gets the attention. SOPs, forms, and specifications have clear owners, clear effective versions, and a finite list. Quality event records are a different kind of problem. A deviation is not a file. It is a structured record with a workflow state, a dozen or more coded fields, a set of relationships to other records, and a history of who did what and when. A CAPA is the same, with the added burden of actions that may still be open and effectiveness checks that may not be due for months.

That structure is exactly what makes these records useful. It lets a quality team trend deviations by root cause, find recurring problems across sites, show that a CAPA was closed only after its effectiveness check passed, and answer an inspector’s question in minutes instead of days. It is also what makes them easy to damage in a migration. A document can move with its content intact and a few metadata fields wrong. A deviation can move with every field populated and still lose its meaning, because its root cause code now points to a different category, its CAPA link is gone, or its approval shows as “Approved” with no name, date, or signature meaning behind it.

Four Things That Make Quality Event Records Different

• State. Open records are in the middle of a workflow. The new system’s workflow states rarely match the old ones one for one, so every open record needs a decision about where it enters the new process.
• Codes. Categories, root cause codes, severity classifications, product, site, and equipment identifiers all come from picklists that drift over the years. Values get retired, added at one site, or typed as free text into an “Other” field.
• Links. A deviation links to its CAPA, the CAPA links to a change control, the change control links to an effectiveness check, and any of them may link to a complaint or an out-of-specification (OOS) investigation. Break one link and the chain of evidence breaks with it.
• History. Approvals, electronic signatures, audit trail entries, extension requests, and reasons for change are part of the record. They are often stored in separate tables from the record itself, which makes them easy to leave behind.

What the Regulations Expect From a Migration

The regulatory text on migration is short, which is part of why it gets underestimated. Annex 11 of the EU and PIC/S GMP Guides says in clause 4.8: “If data are transferred to another data format or system, validation should include checks that data are not altered in value and/or meaning during this migration process.”¹ Clause 17 adds that archived data “should be checked for accessibility, readability and integrity,” and that when relevant changes are made to the system, “the ability to retrieve the data should be ensured and tested.”¹

On the US side, 21 CFR 11.10 requires the ability to produce “accurate and complete copies of records in both human readable and electronic form,” protection of records for their “accurate and ready retrieval throughout the records retention period,” and audit trails that “shall be retained for a period at least as long as that required for the subject electronic records.”² FDA’s Part 11 scope and application guidance says the agency does not intend to object if firms archive required electronic records to paper or to a standard electronic format such as PDF or XML, provided predicate rules are met and “the records themselves and any copies of the required records should preserve their content and meaning.”³

Two words carry most of the weight in those passages: value and meaning. Value is the easy part. A date either came across or it did not. Meaning is harder. A root cause code can arrive with exactly the same text it had in the old system and still mean something different, because the new system’s code list groups things differently or feeds a different trend report. Chapter 4 of the PIC/S GMP Guide adds a practical requirement that migrations often break: “It should be clearly defined which record is related to each manufacturing activity and where this record is located.”⁴ If half of a deviation’s history is in the new system and half is in a legacy archive, someone has to be able to say where each half is.

What Inspectors Will Ask For Afterward

Recent FDA warning letters show the kind of historical question a migrated system has to answer. In a May 2026 letter to Medline, FDA cited repeated microbial contamination findings over more than two years and asked for an “independent, retrospective review of all critical investigations” for the previous three years. That review was to check whether past related investigations had been reviewed well enough “to identify potential associated causes or linkages.” FDA also asked for an assessment of whether the firm’s CAPA program “analyzes investigations trends.”⁵ In an August 2026 letter to Fresenius Medical Care, FDA pointed to “numerous complaints, Field Alert Reports, and recalled batches in the last three years” for the same failure mode.⁶

Neither letter is about a system migration. They are about investigations. But they show what the data has to support: a multi-year view of related records, with their links, their root causes, and their CAPA outcomes intact. If a migration cuts the history at go-live, or brings it across without the relationships, that kind of request becomes a manual reconstruction project at the worst possible moment.

Profile the Legacy Data Before You Map Anything

Most mapping spreadsheets are built from the old system’s configuration: the list of picklist values, the field definitions, the workflow states. That tells you what values were allowed. It does not tell you what is in the data. After ten or fifteen years of use, the two are rarely the same. Retired values remain on old records. An administrator at one site added three categories that nobody else uses. A field became mandatory in year six, so every earlier record has it blank. A paper-to-electronic transition left duplicates. Someone used the production system for training and never deleted the test records.

Profiling means running queries against the actual data to find out what is there, before anyone designs a crosswalk. It is not glamorous work, and it is the single step most likely to be squeezed when a project runs late. It is also where the surprises come from, and a surprise found in week two of a project is far easier to handle than one found during the final load.

Pharma has published very little on how legacy quality data migrations go in practice. Healthcare has published more, and the lessons carry over. A 2023 systematic review of transitions from one electronic health record to another found eight publications dealing with data migration, and reported that approaches varied from site to site, with little empirical evidence to guide the choice.⁷ A 2020 review in Applied Clinical Informatics noted that when two systems do not share the same meaning for their data, an intermediary or archive is needed to retain or migrate it, and that some health systems convert only key data while keeping the legacy system available to users.⁸ Both points apply directly to quality records.

Profile a Frozen Copy, and Keep the Queries

Run profiling against a read-only copy of production, not against an export someone has already cleaned up in a spreadsheet. Keep the queries under version control. You will run them again before each trial migration and again at final cutover, and the counts they produce become part of the migration evidence. If the numbers move between runs, you want to know why, and you can only know that if the queries did not change.

Treat What You Find as Quality Information, Not Just Data Defects

Profiling will turn up things that look like data errors but are quality questions. A CAPA closed without an effectiveness check. A deviation classified as minor whose description reads like a major one. A cluster of deviations on one piece of equipment with three different root cause codes. These are not problems a migration script should fix. They are matters for the quality system, handled through its normal process, with an owner and a record.

The Fresenius letter shows why this matters. FDA noted that the firm’s own risk matrix recommended the highest severity level for a complaint trend, but the firm assigned the lowest.⁶ Classification decisions like that are examined closely by inspectors. A migration that “corrects” severity values in bulk would rewrite quality decisions without an investigation, without a rationale, and without the people who made the original call. Flag what you find, route it to quality, and migrate the record as it was decided.

Deciding What to Migrate and What to Archive

Not every historical record belongs in the new system as a live, editable object. Bringing everything across makes the migration larger, the verification longer, and the new system’s reports noisier. Bringing too little across leaves trending and inspection support split between two places. The right answer depends on record type, status, age, product status, and what the history is used for.

FDA’s Part 11 guidance gives useful room here. It suggests that decisions on how to maintain records be based on predicate rule requirements and on “a justified and documented risk assessment and a determination of the value of the records over time.”³ It also accepts archiving to standard formats, as long as content and meaning are preserved.³ Annex 11 clause 17 says archived data must be checked for accessibility, readability, and integrity.¹ Together, those give a clear frame: you may choose to archive rather than migrate, but the choice must be documented, based on risk and use, and the archive must work.

Choosing the Lookback Window

The lookback window is the period of closed history you bring across in structured, mapped form. It is the most consequential scope decision in the project, and it is often made by default: “everything from the go-live year,” or “whatever the vendor quoted for.” A better approach starts from how the history is used.

• Trending. FDA’s quality systems guidance notes that while 21 CFR 211.180(e) requires product review at least annually, a quality systems approach calls for trending more often as risk warrants. In its words: “Trending enables the detection of potential problems as early as possible to plan corrective and preventive actions.”⁹ If your annual product reviews or management reviews compare several years of deviation data, the new system needs that many years in mapped form, or the trend breaks at cutover.
• Inspection support. The Medline letter asked for a three-year retrospective review of critical investigations.⁵ A request like that should be answerable from one place, with one code list.
• Product lifecycle. For products still on the market, older deviations may still matter for recurrence checks. For discontinued products, archive is often enough.
• Retention. Your retention schedule sets the floor for keeping records, not for migrating them. The PIC/S GMP Guide sets specific retention rules for batch documentation and says that for other documents, retention depends on the business activity the documentation supports.⁴ If your deviations are part of your batch record set, the batch rule applies to them.

What an Archive Package Should Contain

An archive that nobody can search is not much better than no archive. For records you decide to archive rather than migrate, a workable package has three parts:

1. A human-readable rendering of each record, typically PDF, that includes every field, the approval history with signature manifestations, and the audit trail entries for that record.
2. A structured export (CSV or XML) of the same records and their links, so the data can be searched, sorted, and trended later without reopening the old system. FDA’s Part 11 guidance notes that if you can search, sort, or trend records, copies given to the agency should keep that capability where reasonable and technically feasible.³
3. An index entry in the new eQMS for each archived record, with the legacy ID, title, dates, product, site, status, and archive location. This is how you meet the expectation that it is clearly defined where each record is located.⁴

Then test retrieval. Pick archived records at random, retrieve them by legacy ID and by product, and confirm the rendering and the structured export agree. Annex 11 asks for exactly this kind of check.¹

Standardizing Categories and Root Cause Codes Without Rewriting History

A new eQMS is usually an opportunity to clean up the code lists. The legacy system has forty deviation categories, some overlapping, some unused. Root cause codes were never harmonized across sites. Severity went from three levels to four at some point. The new system will have a cleaner, agreed set of values, and there is strong pressure to make the old records fit the new lists.

That pressure is where most migration damage happens. The rule we recommend is simple: map, do not mutate. Every coded field on a migrated record should carry the original legacy value, the new mapped value, and the identifier of the mapping rule that connected them. The original value is never overwritten. That way the new system can trend on the new codes, and anyone reviewing a record can still see exactly what the original investigator selected. It is the most direct way to show that data were not altered in value or meaning.¹

Designing the target code list is its own piece of work, especially across multiple sites, and it should be settled before the crosswalk starts. This section is about the crosswalk itself: how to get from what the legacy data says to what the new system expects.

Build the Crosswalk From the Data

Start from the profiling output, not the configuration list. Every value that appears on at least one record needs a row in the crosswalk. Each row gets a mapping type, and the type determines how it is handled.

The one-to-many rows are where the real decisions are. A script cannot split a legacy code into two new codes without reading each record and making a judgment, and a judgment about root cause is an investigation decision. If the volume is small and the records matter (open records, or recent records in the trending window), a qualified reviewer can assign new codes record by record, with the review documented. If the volume is large, map to a clearly labeled legacy code and accept that trends before cutover will be less granular than trends after it. Either choice is defensible. A silent split by keyword matching is not.

The Human Error Problem

Almost every legacy deviation data set has a root cause code for human error, and in many it is the most used code by a wide margin. That matters for a migration because the new system’s code list usually splits human performance into finer categories, and because regulators have been clear about the code itself. The PIC/S GMP Guide says: “Where human error is suspected or identified as the cause, this should be justified having taken care to ensure that process, procedural or system based errors or problems have not been overlooked, if present.”⁴ An MHRA inspector put it more bluntly at the 2016 GCP symposium: “if you’ve stopped at human error as your root cause, you’ve stopped too soon!”¹⁰

How often the code is used varies widely. In a 2014 Pharmaceutical Online column, Joanna Gallant described a company where 58% of root causes were coded as human error, and observed that average rates at companies seemed to be around 25%.¹¹ Those are one practitioner’s observations, not a benchmark, but they make the point: in some legacy data sets, “human error” is less a finding than a default.

The temptation during migration is to re-code those records into the new, finer categories so the history looks as good as the future. Resist it. Re-coding a root cause is re-investigating a deviation, and it belongs in the quality system, not in a mapping script. Map legacy human error to a clearly labeled legacy code. If profiling shows the rate is high, raise that as a quality finding and let the quality team decide whether a retrospective review is warranted. FDA’s letters show that retrospective reviews of investigation adequacy are a real request, with their own scope and evidence.⁵ That work deserves its own plan, owner, and record, separate from the migration.

Undetermined, Unknown, and Other

Legacy data sets usually have some version of “root cause undetermined.” The PIC/S GMP Guide expects that where the true root cause cannot be determined, consideration be given to identifying the most likely root cause and addressing it.⁴ Your new code list may handle this with a “most probable cause” structure. Whatever it does, do not map “undetermined” into “other,” and do not map it into the most common code in the category. An undetermined root cause is information in its own right. It tells a reviewer that the investigation did not reach a conclusion, and that fact should survive the migration.

Severity, Classification, and Master Data Fields

Severity scales change over time, most often from three levels to four. A legacy “major” cannot be split automatically into a new “major” or “critical.” Map conservatively, keep the legacy classification visible on the record, and report pre-cutover and post-cutover severity counts separately until the scales can be compared honestly.

Product, site, material, and equipment identifiers need the same care. Map them to current master data where a clear match exists, keep the legacy identifier on the record, and list every unmatched value. A deviation on equipment that has since been decommissioned should still say which equipment it was, even if that equipment no longer exists in the new system’s master data.

Closing or Reconciling Open Records Before Cutover

Open records are the part of an eQMS data migration that can go wrong in front of everyone. A closed record that migrates badly is a data problem found in verification. An open CAPA that migrates badly is a missed due date, a lost action owner, or an effectiveness check that never fires. Every open record needs an explicit decision before cutover, and the cutover date should not make that decision by default.

Burn Down the Backlog, but Do Not Rush Closures

Most projects set a target to reduce the open backlog before cutover, and they should. Fewer open records means a simpler cutover and fewer records to verify one by one. The risk is that closures get rushed to meet the date. A CAPA closed early to make the migration easier, which later turns out not to have worked, is worse than a CAPA carried open into the new system. In the Medline letter, FDA called the firm’s response inadequate because it had “not explained why your previous CAPA attempts were not successful.”⁵ A backlog burn-down that produces weak closures creates exactly that kind of history.

Set one rule and write it into the migration plan: closure criteria do not change because of the migration. Records that can be closed properly before the freeze are closed. Everything else migrates open.

Map Workflow States Explicitly

Legacy and new workflows almost never line up. The legacy system may have “Pending QA approval” where the new system has “Impact assessment,” then “QA review,” then “Approval.” For every legacy state that holds open records, the migration plan should say which new state those records enter and why. If a new step has no legacy equivalent, decide whether migrated records must complete it or can enter after it, and write down the rationale. If a legacy state has no sensible new equivalent, move the records out of that state in the legacy system before extraction, so nothing arrives in limbo.

Carry the Dates Forward

Dates are where open records most often go wrong. The new system needs the original open date, the original due date, any approved extensions with their history, action due dates, and effectiveness check due dates. If the new system calculates aging from its own creation timestamp, and the creation timestamp is the migration date, every migrated record will look new, and every aging report and overdue metric will be wrong. Make sure the original dates are the dates the new system uses, and that the migration date is recorded separately.

An Example Cutover Sequence

The exact timing depends on the size of the data set and the number of sites, but the order of steps is consistent. The sequence below is one we find workable for a single eQMS replacement.

Verifying Migrated Records: Counts, Values, Meaning, and Links

Annex 11 asks for checks that data are not altered in value or meaning.¹ Industry guidance fills in how. The APIC data integrity guide, compiled by the data integrity task force of the Active Pharmaceutical Ingredients Committee, says that migration from one system to another requires data verification as part of computer system validation, and that data “shall be verified for completeness and accuracy using a statistically relevant sample.”¹² That is a sensible floor. For structured quality event data, we recommend going further, because most of the checks can be automated and run against every record.

We recommend verification on four levels. Each level answers a different question, and none of them replaces another.

Why Values Should Be Checked in Full, Not Sampled

Sampling works well when defects are random. Migration defects often are not. They are systematic, caused by one mapping rule or one conversion step, and they tend to affect a whole class of records in the same way. Common examples to test for:

• Dates and times shifted by a time zone conversion, so every record’s open date moves by several hours, and some move to the previous day.
• Long text fields cut off at a character limit in the target system, so investigation narratives lose their last paragraphs.
• Special characters such as the micro sign, degree sign, or accented names garbled by an encoding mismatch.
• Numeric values rounded or stripped of trailing zeros.
• Multi-value fields (for example, several affected batches) reduced to the first value only.

A sample may or may not pick up a defect that affects only records with long narratives, or only records created at one site. A full automated comparison between a source extract and a target extract, with the approved mapping reversed, finds all of them. The effort is in building the comparison once. After that it runs on every trial load at no extra burden.

How to Sample for Meaning

Meaning cannot be checked by a script, because it depends on how a person reads the record. The check is simple: a qualified reviewer opens the legacy record and the migrated record side by side and confirms that they tell the same story. Same event, same classification, same root cause (as mapped), same actions, same approvals, same attachments.

Use a documented sampling approach. Acceptance sampling methods, such as those described in the NIST/SEMATECH e-Handbook of Statistical Methods, give a defensible way to choose a sample size and an acceptance number for a given population.¹³ Then add full coverage for the subsets where a meaning error would matter most:

• All records migrated as open.
• All records linked to a recall, field alert, or regulatory commitment.
• All records where a coded value was mapped by record-level review rather than by an automatic rule.
• A sample drawn from every crosswalk rule, so no mapping goes unreviewed.

Test the Migration, Then Test It Again

Run at least two trial migrations using full production copies, and run the complete verification each time. Handle every defect in a migration defect log, with a root cause and a fix to the migration logic. Do not fix loaded data by hand in the test environment; the fix will not be there at final cutover. The final production load should either pass cleanly or have every exception listed, assessed, and approved by QA before release.

Keeping Audit Trails, Signatures, and Legacy Access Intact

A deviation record without its history is a summary, not a record. Part 11 requires audit trail documentation to be retained for at least as long as the records it describes.² The practical question is where that history lives after the migration.

Three Ways to Keep the History

• Migrate the history as read-only data attached to each record. Many eQMS platforms can hold a legacy history object or table on each migrated record. This keeps everything in one place and is the easiest for reviewers and inspectors to use.
• Render the history into the archive package and link to it. The migrated record carries its current data and a link to the archived rendering, which includes the legacy audit trail. This works when the new platform cannot hold legacy history in a useful form.
• Keep the legacy system available in read-only mode. This preserves everything exactly as it was, but it means maintaining, validating, and securing an old system for years. It is sometimes the right short-term choice and rarely the right long-term one.

Whichever option you choose, the new system’s own audit trail will show that each migrated record was created by a migration account on the migration date. That is expected and correct. Document the migration account, who controlled it, and when it was disabled, so that entry is explained rather than questioned.

Signatures Must Keep Their Meaning

Part 11 requires signed electronic records to show the printed name of the signer, the date and time of signing, and the meaning of the signature, such as review, approval, responsibility, or authorship.² A migrated CAPA that shows a status of “Approved” with no approver, no date, and no signature meaning has lost part of its content. Legacy signatures cannot be re-executed in the new system, and they should not be. They should come across as data: signer name, date and time, and meaning, clearly marked as legacy signatures from the original system and included in the human-readable view of the record.

Decommissioning the Legacy System

Annex 11 clause 17 is direct: when relevant changes are made to a system, the ability to retrieve the data should be ensured and tested.¹ Switching off the old QMS is such a change. Before it happens, confirm that every record is either in the new system, in the archive with an index entry, or formally retired. Run a final retrieval test on the archive. Keep the legacy system read-only for a defined period after go-live, long enough to answer questions that verification did not anticipate, and then decommission it under change control, with a record of what was done and where the data went.

Who Owns the Decisions and What to Write Down

Migration projects often run as IT projects with quality sign-off at the end. For deviation and CAPA data, that is backward. Most of the important decisions in this article are quality decisions: what history is needed, how root causes map, which open records close and which carry over, and what verification is enough. IT and the migration vendor execute those decisions. They should not make them.

FDA’s Part 11 guidance ties records decisions to “a justified and documented risk assessment.”³ In practice that means the lookback window, the archive choice, and the sampling approach each need a written rationale that someone outside the project could follow. It does not need to be long. It needs to say what was decided, why, and what risk was accepted.

One more check belongs before go-live: confirm that the new system can produce the trend reports your quality team relies on, run across the migrated history. FDA’s quality systems guidance describes trending as a way to detect problems early and plan corrective and preventive actions.⁹ If the first management review after go-live cannot show a deviation trend that spans the cutover, the migration is not finished, whatever the verification report says.

Conclusion

Cleaning legacy deviation and CAPA data before an eQMS migration is less about cleaning than about deciding. Deciding which history has to stay structured and searchable, and which can live in a well-indexed archive. Deciding how each old code maps to each new one, and refusing to let a script make judgments that belong to investigators. Deciding what happens to every open record before the cutover date decides for you. And then proving, on four levels, that what arrived is what left: the same counts, the same values, the same meaning, and the same links. Done this way, the new system starts with a history your quality team can trend and an inspector can follow. Done the other way, it starts with a gap that someone will have to explain later, usually under time pressure.

Sakara Digital works with pharma and biotech organizations planning and running quality system transitions, including the data decisions behind them. If you are preparing an eQMS data migration and want an independent view on scope, crosswalk design, or verification before you commit to a plan, we are happy to have that conversation.

For Further Reading