Marcos Henrique
Dev com negócios
System Integration: Challenges and the Value of Long-Term Planning
You've probably seen a system or website that offers the ability to integrate with another provider. One example I came across recently is Strava, a fitness tracking app for running, cycling, etc. It allows you to connect with Spotify, making it easier to manage your music directly within the app.
In fact, there are many types of integrations. Some allow you to handle information via endpoints, others notify the application using a webhook (event), and some even connect directly to the database (which I wouldn’t recommend). Among them, webhooks are likely the most widely used and appropriate standard today.
But building an integration like this isn't one of the easiest tasks. In this post, I’ll explore not only the mistakes I made along the way, but also the valuable lessons I learned, which can help other developers avoid similar pitfalls and build more robust and efficient systems.
From the Beginning
COSTPAT
If you've been following me for a while, you probably already know about COSTPAT. But for those who are new here, let me briefly introduce it. At the age of 15-16, I had the opportunity to build a government asset management system. This project came at a time when I was almost giving up on programming and entrepreneurship after several unfinished projects. I decided to give myself one more shot and dedicated my school vacation to what would become the first version of COSTPAT. And it worked! I not only regained my motivation to build, but also faced several challenges that are usually handled by senior devs 😅. That taught me how to better deal with mistakes and stop believing I wasn’t capable of delivering what was needed.
Back to the topic, after COSTPAT succeeded across multiple cities in Sergipe and went through a long bidding process, we reached the City Hall of Aracaju. That contract forced me to deal with scenarios I had never faced before, like migrating over 300,000 assets from CSV using Python validations and integrating with the city’s ERP system.
And that’s where the problems started. The first one:
1 - Infrastructure
At the time, it seemed most efficient to set up dedicated servers just for that contract, since it was my biggest client. In my mind, it made sense to separate everything—including the code.
So that’s what I did: I created databases, DigitalOcean droplets (servers), and new repos by cloning the existing ones from the “main” project.
2 - Database Modeling
Since we already had a separate database and time was short, I thought: why not adapt our schema to match the external system? So I went ahead and changed our entire data structure, originally designed for our app's needs, to something completely different. You can probably guess where this is going...
3 - Creation and Update Routines
Another key challenge was creating routines to fetch updates. I had to call the provider’s API and filter for records created or updated on that day, then sync the data. Sounds simple, right? Just a cron job that makes a request? Not exactly.
When I Noticed the Problem
One thing I’ve learned in recent years is that the more you plan for the long term, the fewer headaches you'll have. All the changes I made to adapt COSTPAT to this new setup worked at first—integration was running, the client was happy, and everything flowed smoothly. But then improvement requests started coming in—which is totally normal.
And because I had separated the codebases, I now had to:
- Update both backends.
- Modify the schemas of two different databases.
- Maintain two different frontends.
For every update, I had to do the same job twice—because most of the features made sense for both environments (what I call the shared base). Speaking of which, the shared base handled user and unit registration, meaning the city hall frontend had to interact with two different backends. That quickly turned into a snowball.
Another growing pain was the database model itself. Naming conventions, performance issues, and denormalized data that should’ve had integrity all started to cost me time with manual fixes and patch scripts. It was still manageable... until:
The integration broke. Data stopped syncing correctly with the provider. As new datasets were added weekly, the synchronous request started taking more than 5 minutes, and if it timed out, the job would stop. Even if it didn’t, it was overwhelming the database with reads and writes.
That’s when I knew I had to change my mindset and fix things properly.
Wrong Decisions
All those issues stemmed from a series of technical decisions that, at the time, seemed unrelated—but weren’t. Back then, I lacked confidence and always focused on short-term results, underestimating the system’s future. Eventually, my system became limited by its own architecture.
How I Solved It
Now, with more experience and a long-term mindset, I decided to address each of those issues at the root. The goal: make COSTPAT independent and capable of integrating with any system, pain-free. That’s why, in 2024, I rebuilt the entire system from scratch (I'll dive deeper into that in another post).
1 - Fixing the Infrastructure
First, I unified the infrastructure—one base code, lower costs, and centralized management. It took time, but after a year-long migration, I have no regrets.
I went from a fragmented, bloated codebase to a clean, unified setup.
2 - Fixing the Database Design
The database is one of the most crucial parts of any software. You can rebuild the frontend, backend, whatever—but if the DB is messy or poorly optimized, no caching strategy can save you.
So I spent over two weeks designing every entity, attribute, and relation with care. I took advantage of JOINs (which can hurt at scale), because in my case, data integrity matters more.
3 - Fixing the Integration
Now to the toughest challenge: making the integration modular and scalable for huge datasets.
Step one was creating an “integration” table for each entity. That way, one external system could sync only assets, another only products, etc. The sync routine now only considers clients with those specific associations. This modular setup even allows the client to toggle integrations without breaking things.
Step two: improve performance. The obvious solution was to go async—trigger the update request, then process the data in the background.
To do this, I implemented queues for the first time.
The concept is simple: queues (like SQS or RabbitMQ) work like checkout lines. One task is processed, and when done, the next one is picked up. Meanwhile, the rest of the app works normally.
This drastically reduced database stress and made cron jobs respond instantly.
As I said in another post: not everything should use queues—but here, they were perfect. The user doesn’t need an immediate response, so I just notify them when processing is done. That’s what I implemented: when the sync finishes, an email is sent to me and my partners with the success/failure summary, and a log is saved so I can track what happened.
Lessons Learned
This was a real problem, with a real solution. Sure, online courses teach you the tech you need—that foundation is important—but most of my growth as a dev came from the battlefield.
I hope this real-world story helps you see tech through a new lens and gives you motivation to face the challenges ahead. I’ve learned a lot in 4 years of development, but the biggest lessons were about myself and how to keep going without giving up.
That’s it for now! If you want to hear more about the technical challenges behind rebuilding COSTPAT from scratch, drop a comment below. See you in the next one!
