CRM migration

The problem

A UK recruitment agency was leaving a legacy CRM. What they had was a SQL Server backup: about 18 GB restored, 574 tables, and no declared primary or foreign keys. The destination was a SaaS recruitment CRM that could only be written through its public REST API. The rule was simple and strict: represent everything exactly as in the source, and invent nothing.

What I built

• A three-stage pipeline: extract (PowerShell + sqlcmd to JSON), transform (mapped entities with deterministic UUIDv5 ids), load (REST), then file associations, contact and action fixes, and a read-only reconciliation pass.
• Idempotent by design: ids salted per account, adopt-on-duplicate, checkpoints, and a refusal to run against the wrong account.
• Batch-split retry: when a batch hit a duplicate, isolate the single bad record and retry the rest. That's the difference between hours and days.
• Safety rails: 50-record canary gates, a drops ledger that fails the run, self-halting on anomalous duplicates, and a watchdog for unattended overnight runs.

What I found on the way

• A platform bug: concurrent POSTs created "ghost" rows (id reserved, record invisible). I diagnosed it, switched to sequential loading and re-salted the ids.
• Integrity without keys: I proved 71 inferred ID/FK columns across 12 tables with joins, and found 3 broken-reference cases (about 400 orphan rows) before they reached the client.
• Undocumented API limits: batch caps, which endpoints honour historical dates, and uniqueness rules that include trashed rows.

Results on the live account

• Companies 7,611 / 7,611 · People 36,661 / 36,661 · Activities 253,541 with original dates.
• Contact → company links 21,375 · File associations 170,834.
• Post-migration repair: 1,901 / 1,901 hiring managers re-linked with 0 failures; 49,725 activities restored, 99.97% content-exact, 0 duplicates.

Stack