Designing for Data Migration

Migration mapping is where design meets the archaeology of data. When HBP Spark replaced a legacy platform, every pathway, section, and content item that existed in the old system needed to find its equivalent in the new one. Some things mapped cleanly. Some things didn’t exist in the new model and had to be handled gracefully. Some things existed in the new model but had no equivalent in the old.

Building the migration mapping diagram was one of the more unglamorous pieces of work in this project. It was also one of the most important.

Why Design Is Involved in Data Migration

There’s a common assumption that data migration is an engineering problem. It has an engineering implementation, yes. But the decisions about how to map old data to new models are product and design decisions.

When you map pathway_section_item.pathway_section_id to PathwaySection/sectionTitle, you’re making a decision about what a “section” means in the new system. If the old system’s section concept includes things the new system doesn’t support, or if the new system has richer section properties that have no analog in the old data, those gaps have to be explicitly designed around — not left for an engineer to improvise.

The Database Schema Context

The migration mapping diagram traced the key tables from the legacy system:

• pathway — the core pathway object, with fields for ID, title, summary, description, and creation date.
• pathway_collaboration — relationships tracking which users had collaboration access to pathways.
• pathway_section — the sections within pathways (titled groupings of content).
• pathway_section_item — individual content items within sections, with fields for asset ID, operation type, and description.

Each field was mapped to its equivalent in the new system, or explicitly flagged as n/a when no equivalent existed.

What “n/a” Actually Means

The n/a notations in the migration map are as important as the positive mappings. They represent either:

1. Features the old system had that the new system doesn’t support — these need explicit decisions about whether to discard the data, store it for potential future use, or flag affected pathways for admin review.
2. Fields that exist for technical reasons in the old system — auto-generated IDs, internal tracking fields, database artifacts that don’t correspond to any meaningful user-facing concept.

Distinguishing between these two types of n/a is critical. Discarding a technical artifact is fine. Discarding a field that held meaningful user data (like description or cl_user_id) requires a decision, documentation, and potentially communication to admins whose content might be affected.

The cl_user_id Question

The pathway_collaboration table’s cl_user_id field — tracking which users have collaboration access — is an interesting case. In the new system, collaboration is managed through the groups and admin access model. There’s no direct cl_user_id equivalent.

This mapping gap required a product decision: do we migrate individual collaboration records into the new access model, lose them entirely, or notify affected admins? The migration mapping diagram surfaced this question clearly so it could be answered deliberately.

What This Work Enabled

Having the migration mapping documented before engineering began data migration work prevented a class of problems that are otherwise nearly impossible to catch: data that looks right but means something different in the new schema.

For example: if pathway_section.title was loosely used in the old system (sometimes empty, sometimes containing instructions, sometimes containing the actual section title), an engineer following the mapping might naively copy those values directly. With the mapping document, design had flagged which fields had data quality issues and needed transformation logic, not just copying.

The Invisible Design Work

Migration mapping is invisible to users. They don’t know it happened. If it goes well, their content simply exists in the new platform, looking and working the way they expect. If it goes badly, they open the new platform and find their carefully organized content scrambled, truncated, or missing entirely.

That invisibility is what makes it important. The moments where design work disappears into seamless function are often the moments of its highest value.