The system that held a business together by inertia

Coolkit manufactures refrigerated vehicle conversions. Behind the vans was a system that had grown, one column at a time, for years: a single core table with 779 columns, living in on-premise Microsoft Access and SQL Server, holding roughly 35,000 customers and a multi-level bill of materials of around 52,000 parts. No documentation. No clear owner. Just a structure that worked because nobody dared touch it.

That is a more common situation than the conference talks admit. The system is not elegant, but it runs the business, and every year it gets a little harder to change. Reporting needs a specialist who knows where the bodies are buried. Integrations are effectively impossible, because no modern tool can safely reach into a 779-column table it does not understand. And the real cost is not the ugliness, it is the operational risk: when nobody fully understands the schema, every change is a gamble on cascading failure.

Coolkit did not need a prettier database. They needed to connect this system to modern tooling, get real reporting out of it, and stop being one bad change away from an outage. The brief was modernisation without a rip-and-replace, because the business could not stop while we rebuilt it. This is the story of how that actually went, with the real numbers.

A note on why we can tell it: the outcomes are already published in our Coolkit case study. This piece is the engineering behind them.

Step one is not code, it is archaeology

The instinct on a project like this is to start designing the beautiful new schema. That is a trap. A 779-column table is not a schema, it is an archaeology site, and the first deliverable is an honest map.

We audited every one of those columns. For each, we established three things: is it written to, is it read from, and by what. That sounds tedious because it is, and it is also the single most valuable phase of the whole project. It is where you discover that forty columns are dead cruft from a system retired in 2015, that a dozen more are duplicated under slightly different names because two teams solved the same problem twice, and that one innocuous-looking flag column is quietly load-bearing for a report the finance team runs every month.

The classification is what makes the rest safe. Actively-used columns get modelled into the new normalised structure. Legacy cruft gets documented and quarantined, not silently dropped, because “this column looks unused” is a hypothesis, not a fact, until you have proven nothing depends on it. The most expensive migration bugs are not the ones you can see. They are the columns you were sure were dead.

Why we did not do a big-bang rewrite

The tempting plan is a clean cutover: build the new system, pick a weekend, move everything, switch it on. On a business-critical system, that plan is how value goes to die.

Big-bang rewrites of running systems fail for four consistent reasons, and we have watched all four happen on other people’s projects. Requirements drift, because the business keeps evolving during the months you are heads-down rebuilding. Hidden complexity surfaces, because the legacy behaviour you did not fully understand only reveals itself when you try to remove it. Sunk-cost pressure builds, because after six months nobody wants to admit the approach is not working. And cutover risk is absolute, because a single bad launch weekend can force a full rollback with the business watching.

So we used the strangler fig pattern instead. The name, coined by Martin Fowler, describes new code that grows around a legacy system and gradually takes over its functions until the old system can be retired. Every step is small, independently valuable, and reversible. You trade a dramatic finish for a boring one, and boring is precisely what you want when the system is holding the business together.

The bridge: Access and SQL Server to a cloud API

The heart of the migration was a bridge. On one side, the legacy on-premise world of Microsoft Access, VBScript, and SQL Server. On the other, a cloud API and a serverless processor built on a modern stack. Between them, a layer whose only job was to move data reliably in both directions and let us shift responsibility from old to new one table at a time.

This is what makes strangler fig concrete rather than a slide. The legacy system kept running and kept being the source of truth while we stood up the new schema alongside it. As each part of the new structure was built and validated, the bridge routed the relevant reads and writes to it. Nothing had to be trusted before it had earned trust. If a newly-migrated table misbehaved, promoting the legacy path back to primary was a configuration change, not an incident and a postmortem.

Two engineering disciplines mattered most here. The first was idempotency: because data flowed continuously and steps could be retried, every sync operation had to be safe to run more than once without double-writing. The second was reversibility. We held to a rule that any single step in the migration could be undone with one command. That rule slows you down in the moment and saves you catastrophically later, because it turns every risky change into a cheap experiment.

The backfill, and the last two percent

Modernising the live structure is only half the job. The other half is the history: 37,000 deals and 65,000 quotes of accumulated business record that had to arrive in the new world intact.

We automated 98% of that backfill. The pipeline read from the legacy source, transformed each record into the new schema, and validated it against the origin before it was accepted. Getting to 98% is mostly a matter of good engineering and patience.

The interesting number is the other 2%. On a legacy dataset that has grown organically for years, the last few percent is never uniform noise. It is where the silent data-quality problems live: the records with a date format that changed in 2018, the quotes that reference a customer ID that no longer exists, the deals entered during some long-forgotten system migration that never quite fit the shape everyone assumed. We handled that remainder deliberately, case by case, rather than forcing it through the automated path and corrupting the clean schema we had just built. The temptation is always to chase 100% automation and call the stragglers noise. On business records, the stragglers are often the ones that matter most.

The result was a clean, normalised database that any developer could query, that modern tools could integrate with directly, and that could be changed without fear. That foundation is what later made the Coolkit CRM integration possible: once the data was sane, syncing more than 100,000 deals and quotes between the ERP and a CRM in under ten seconds became an engineering problem rather than an impossibility.

What generalises

Most businesses have a version of the 779-column table somewhere. The lessons travel.

Map before you model. The audit that tells you what every column actually does is not overhead, it is the project. Classify, do not delete. An unused-looking column is a hypothesis until proven. Bridge, do not cut over. Let the old and new systems run in parallel behind a routing layer, and move responsibility one piece at a time. Make every step reversible, because the discipline of a one-command rollback is what lets you move quickly without betting the business. And treat the last few percent of any data migration as the most important few percent, not the least.

None of this is glamorous. That is the point. The best legacy migration is the one nobody outside the engineering team ever notices, because the business never stopped running. If you are staring at your own version of the 779-column table, that quiet outcome is available, and it does not require a big-bang gamble to get there. This is the kind of work we do: we build what we advise, and we would rather ship the safe migration than sell you the dramatic one. If that is the problem in front of you, let’s talk.