Migrating a Legacy Insurance Engine to a Modern Stack

In 2021, shortly after I joined Blue Hex Software, we landed our very first client: an insurance provider based out of Miami, Florida. They came to us with a classic, high-stakes problem statement that many growing businesses eventually face.

Their core business revolved around selling customised insurance plans. To calculate the exact premiums and craft the right price for the end customer, they had built a highly complex, proprietary in-house pricing engine.

The catch? That “engine” was a monstrous Microsoft Excel spreadsheet.

They had hit the absolute limit of what a desktop application could handle. They needed a way to migrate their intricate business logic and calculations out of Excel and into a modern, scalable web application. The goal was to ensure seamless customer onboarding, retain the accuracy of their pricing algorithms, and layer a robust Management Information System (MIS) on top to monitor business growth.

To replace a monolithic spreadsheet, we couldn’t just build a simple CRUD app. We proposed a robust 3-tier architecture designed for scalability, easy maintenance, and future growth. Drawing on heavy backend and distributed systems experience, we finalised a highly capable stack:

• Frontend: Next.js (For a fast, SEO-friendly, and reactive user interface)

• Backend: Python with Django (To handle the complex business logic and API routing)

• Database: PostgreSQL, hosted on AWS RDS (For robust relational data integrity)

• Containerization: Docker (Ensuring environment parity across staging and production)

• Message Broker: RabbitMQ (For handling asynchronous background tasks)

• Caching Layer: Redis (To speed up repeated pricing calculations)

• Storage: AWS S3 (For static files and document management)

• CI/CD: GitHub Actions deploying to AWS (ECS/EC2)

The first step was untangling the spreadsheet’s chaotic data into a clean relational database schema. We designed a straightforward entity-relationship model.

While I can’t expose the proprietary fields or the meta-data handling the core business logic, the high-level schema consisted of distinct models for:

Users, Customers, Plans, Insurance Companies, Pricing Rules, Family Demographics, Invoices, and Payments.

When we scoped the migration, we fell into a classic engineering trap: How bad could a spreadsheet really be? We severely underestimated the beast.

It wasn’t a flat table of data. The spreadsheet was a horrific labyrinth of hundreds of interconnected worksheets, hidden columns, nested conditional statements, and localised macros. Standard data extraction libraries like pandas or openpyxl simply choked on the complexity. We couldn’t just read the file; we had to parse its logic. This required serious R&D.

To solve this, we looked outside standard data-entry scripts and borrowed a concept from big data processing: MapReduce.

MapReduce is a programming model used for processing and generating large datasets via a parallel, distributed algorithm. In a traditional sense, it involves a “Map” step (filtering and sorting data into key-value pairs) and a “Reduce” step (aggregating those results).

Here is a simplified Python example of the concept:

We built a custom algorithm based on this exact architecture.

Instead of reading the entire Excel file linearly (which kept crashing out of memory), we built workers to map specific artifacts—extracting rules, hidden column values, and pricing modifiers from individual sheets simultaneously. The reduce function then grouped these fragmented pieces of logic into coherent Python dictionaries that matched our Django models. Finally, a database loader script took these aggregated dictionaries and mapped them to the correct PostgreSQL tables.

The parser wasn’t 100% flawless, but it did exactly what we needed it to do. We completed the migration well within the strict client deadline with less than a 5% data loss rate.

Because the final 5% consisted of deeply corrupted or circular logic within the original sheets that our scripts couldn’t access, we resolved it via manual data entry. To ensure the client was never locked in, we also generated robust seeder scripts. If they ever decide to migrate away from AWS RDS or Postgres in the future, they have a clean, automated way to populate their new databases.

Not all solutions can be found in a neat GitHub repository or at the bottom of a Stack Overflow thread. Sometimes, you have to look at an architectural paradigm designed for massive Hadoop clusters and apply it to a terrifying spreadsheet from Miami.

Engineering is about thinking outside the box, building custom tools when the standard ones break, and constantly optimising the result.

Till next time, happy coding :)