CEU Locker - Zero-Loss Migration of a 1M-Row Legacy Database

Problem

CEU Locker's original ColdFusion-era system had outgrown its own schema after years of feature additions — column purposes had drifted, naming was inconsistent, and there was no single source of truth for how V1 data actually mapped onto the redesigned V2 schema. Every row belonged to a real professional association, CEU provider, or licensed professional tracking credits against real compliance deadlines, so the migration had zero tolerance for silent data loss or corruption.

Challenges

• Reverse-engineering ~85 undocumented V1 tables with inconsistent naming and columns that no longer matched their original purpose
• Producing an authoritative column-by-column mapping between the V1 ColdFusion schema and the newly designed V2 Laravel schema, including renamed, split, and merged fields
• Preserving referential integrity across two structurally different schemas when foreign keys didn't line up 1:1
• Confirming that mapped fields were actually populated the same way in production on both systems, not just structurally compatible
• Migrating 1M+ live records without extended downtime or a rollback plan that could lose data

Architecture

Before writing a single migration script, both the V1 ColdFusion application and the new Laravel V2 codebase were audited table by table to see how each column was actually read and written in application logic — several V1 fields had been repurposed informally over the years and no longer matched their names. That research fed a full set of table and column mapping spreadsheets covering all ~85 tables: source table/column, destination table/column, required transformation, nullability differences, and dependent tables. Migration scripts were then written directly against those mapping sheets and run in foreign-key dependency order — parent tables before children — each wrapped in a transaction with row-count and checksum validation before the next table was allowed to proceed.

Database Design

V2 restructured much of the V1 schema — normalizing tables that had accumulated redundant columns and splitting overloaded tables into cleanly related ones. An old-ID-to-new-ID lookup table was maintained for every migrated entity during the transform, so every foreign key in V2 could be rewritten to point at the correct new record instead of a stale V1 ID, leaving no orphaned relationships after cutover.

Scalability

Large tables were migrated in batches rather than single bulk inserts, keeping locks short enough that the production V1 database stayed usable throughout the migration window. The full migration was rehearsed against a staging copy of production data first, so the real cutover was a re-run of an already-validated process rather than a first attempt.