Replacing the incumbent system was the easy half. The hard half was that a quarter century of the client's operating history lived in a schema nobody had documentation for, where the meanings had drifted from the column names. I treated the mapping as something to be measured against the live data rather than assumed, wrote the findings down as a runbook, and built every importer to be idempotent so a re-run writes nothing instead of silently doubling a member's logbook. The whole thing was rehearsed end to end against a full production snapshot before it was allowed near the real database.
What it's built with.
How it works.
Assume nothing, measure everything
Every mapping decision in the runbook is written next to the evidence for it, because each one fails silently if it is wrong. A legacy schema that has been in production for decades accumulates conventions that were never written down: a status encoded by rewriting a type column, a parent-child relationship expressed only through a self-referencing id, a login field quietly serving as the real primary key for humans. None of that is discoverable by reading column names.
So the method was to form a hypothesis, then check it against the full dataset before writing a line of import code. That is where the cancellation encoding, the nested-dispatch double count, and the true join key all came from. The alternative, mapping what the names suggest and reconciling afterwards, would have produced an import that looked successful and was quietly wrong about a third of the history.
Safe to run twice
The single most dangerous property of a data migration is that the failure mode is duplication, and the most likely reason to re-run one is that something went wrong the first time. Flights are the sharpest edge: a second unguarded run would double every member's logbook hours and every aircraft's meter history, corrupting exactly the records an aviation operation is legally required to keep.
The fix is a natural key from the source system carried into Azimuth as a nullable external_ref, with a partial unique index that constrains only imported rows. That makes the import convergent rather than additive, and it turns the acceptance test into something unambiguous: run every importer again with the apply flag and every one of them must report writing zero rows.
Rehearsed before it was real
The entire migration was executed end to end against a full production snapshot before production was touched, which is how the numbers quoted here are known at all. The rehearsal surfaced work that had nothing to do with data: the migration branch and the mainline had both claimed the same migration numbers while the branch was in flight, and that collision had to be rebased and renumbered before anything could run for real.
It also set the rules for the day itself. Take a fresh export, apply schema changes with the migration tool rather than a push, use a direct connection rather than the pooler because the import runs long, and write the dump outside the repository because it contains member names and email addresses.
The things I'm proudest of.
- ▹Reverse-engineered the legacy schema by measuring it, not by trusting it. The system had no cancelled flag: it rewrote a dispatch's type to 'Y' and prepended the reason to the remarks, which 11,503 of 11,505 such rows confirmed and no row of any other type did, making 37% of the club's dispatch history invisible until it was found.
- ▹Caught a double-counting trap that would have inflated the club's flight hours. Dispatches nest, a rental being a parent row plus one row per leg flown inside it, and the legs subdivide the parent's Hobbs span (786 of 787 parents summed exactly). Counting both levels would have added 1,801 phantom flights worth 1,969.9 hours, about a tenth of the total.
- ▹Established the join key empirically: the legacy short-login maps to Azimuth's member number, with email as fallback, resolving 27,001 of 28,905 dispatches at 93.4%. Three 'customers' turned out not to be people at all but placeholder accounts, and they import with a null pilot rather than inventing a member.
- ▹Made every importer idempotent with nullable external_ref columns backed by PARTIAL unique indexes on (org_id, external_ref) WHERE external_ref IS NOT NULL, so rows the app creates stay unconstrained while imported rows cannot land twice. Acceptance was a second full run of every import reporting written: 0.
- ▹Reconciled the result against the source rather than declaring victory on row counts: flights and hours matched exactly, per member, for the busiest pilots in the legacy system, a check that exercises the join key, the Hobbs span, and the unit conversion at once.
- ▹Wrote the whole thing up as an operational runbook covering the rehearsal, the migration-numbering conflict that had to be resolved before production, the requirement to take a fresh export on the day, and the handling of the export dump as sensitive data since it contains member names and emails.