Auditable data migration, from Excel to production

Context

A B2B SaaS for auto-repair shops (my current role) onboards customers that already have years of history somewhere else. Two projects show the pattern. In the first, new multi-branch customers needed their orders, payments, receivables and bank movements loaded from their previous system, exported to Excel files whose layout differed by branch. In the second, a corporate fleet-maintenance customer with 24 branches and mobile units needed its spare-parts catalog, standardized suppliers and a year of purchases loaded so it could run inventory and costs. This case is anonymous, and the demo uses invented data.

My role

• Decided: the migration rules per branch, the order of stages, what gets documented as partial instead of filled in, and when a run is good enough to go to production.
• Verified: every run in the QA environment and a reconciliation against the original Excel before anything touched production, and the first purchase load in production, which I audited myself.
• Delegated to AI: writing the TypeScript migration and repair scripts and the written analyses, with Claude Code, under my rules. I own the written analysis that management signs off on before any code is written.

Research

Before programming I directed a written analysis of the migration rules per branch and took it to management for approval. In one branch I compared the structure of 18 files (6,657 rows, 2,636 documents). Management noticed that my first analysis had missed 3 files; I corrected the analysis itself before writing any code. In another branch the files mixed several locations, so I filtered by label, and in a third only about 15% of the orders had a complete report: I documented a partial migration instead of inventing the rest.

Decisions

Decision	Alternatives rejected	Why
Two steps: build a homogenized, auditable JSON from the Excel files, then load the database from the JSON	Load directly from Excel	The JSON can be diffed, reviewed and reconciled before any write; the source file is never the thing being loaded.
Always dry-run, then QA, then production, verified against the source	Apply directly	Production writes need a number to compare. The first purchase load reached production with 3 errors, which is why it was rebuilt this way.
Document a partial migration (about 15% of orders in one branch)	Fill gaps to look complete	Invented data in a customer’s history is worse than a documented gap.
Merge duplicate suppliers into the one with the most dependencies, matching by tax ID (RFC), then normalized name, then manual alias	Delete duplicates	Deleting breaks history that points at them; merging keeps it.
Soft delete for spare parts	Physical delete	History must remain readable.

Validation & QA

Findings I made, and the rules they produced:

• Omitted columns. Four bugs came from manual inserts that skipped columns (description, payment method, customer approval, parts). They were fixed with backfills, and the rule is now “decide every column; never omit one”.
• A field the schema marks optional but the app requires broke 188 orders when opened. I generated it in the app’s own format, checked uniqueness and repaired the 188.
• Missing mirror financial movements from earlier runs were backfilled.
• First purchase load. Three errors: wrong branch assigned, suppliers mixed into one purchase and costs left at zero. A batch cost had also been captured as a unit cost. I rebuilt it with the auditable pipeline and verified it after loading.
• Exclusions with a reason. Rows that were not real branches (4 of 8,262) and one document with an unresolvable tie were left out and documented.

QA ran the full cycle: delete, load, verify, delete, before production.

Results

Migration reconciled against the source

Historical orders from 3 branches: source files against production, and the errors found.

Data table

Panel	Series	Value
Orders	source files	8,339
Orders	production	8,339
Errors	errors in production	0

• 8,339 historical orders from 3 branches migrated to production with 0 errors; 2 more branches followed. The same process has since been used for more than 50 customer branches.
• A base branch’s catalog cloned to 5 branches: 1,101 labor concepts and 126 service packages.
• 5,908 spare parts loaded across 24 accounts.
• 1,393 purchases with 7,975 lines from the first load, regenerated as 1,485 homogenized groups.
• A mobile-unit dry run mapped 58 of 59 rows with 0 errors.

What it solves and where else it applies

A new customer cannot use a system that starts empty: it needs years of orders, parts and suppliers from its old tools. Moving them by hand is slow, and the errors surface months later. This pipeline moves the data in a rehearsed and checked way, so the customer starts with complete history and the numbers match the source.

Where else the same pattern applies:

• Customer onboarding in any B2B SaaS that must import a client’s history from spreadsheets or a previous product.
• Consolidating systems after a merger or acquisition, where two companies’ records must become one without losing history.
• Switching ERP or CRM, where years of records must move to the new system and be shown to be complete.

What I’d do differently

The first purchase load reached production with three errors and had to be rebuilt; I would make the full dry-run to QA cycle a precondition for every load, whoever runs it. I would also write the “decide every column” rule as an automated check instead of a convention, so a script that omits a column fails before it runs. And I would push for complete reports from the customer before the analysis starts: the branch with 15% complete data could only be documented, not solved.