Integrating the systems you already own

A recurring theme in my work is that companies do not have a software problem. They have two systems that should be talking to each other, and a person in the middle doing it by hand.

Some of these are large projects. Most are not, and the ones that go badly are usually the ones that were assumed to be small.

What an integration really is

Strip away the interfaces and an integration is a process that takes data from somewhere, transforms it, and puts it somewhere else. The transformation is usually trivial. The interesting parts are everything around it:

• What happens when the source has no record for this?
• What happens when it has three records that should have been one?
• What happens when the destination is temporarily unavailable?
• How does anyone know what ran, when, and what it did?

These questions are the project. A script that works when everything is well-formed and does the wrong thing when it is not is worse than the manual process, because it produces confident errors at scale.

Where the time actually goes

The API integration itself — the part everyone estimates — is often a small share of the work. In my experience the order is roughly:

Understanding the source data. Not the API; the data. Which fields are required, which can be null, what a status value actually means, whether the same record can appear twice. This is where the time goes, and it requires talking to whoever uses the system daily, not reading the documentation.

Handling failure. Retries with backoff, idempotency so a retry does not create a duplicate, alerts when something is consistently failing. This is the part that gets skipped in the estimate and paid for in production.

Making it observable. A log the person on the ground can read. When a human needs to intervene — and they will — they need to see what the system did and override it.

The transformation itself. Usually the smallest part, and the only one that gets written down in advance.

Idempotency, the thing that matters most

If you take one idea from integrating systems into an automation, take this one.

A network call can fail after the work was done. The server processed it, the response was lost, and now you have no idea whether to try again. If the operation is not idempotent, retrying creates a duplicate.

The fix is to make every step safe to repeat: key records on an external identifier so a repeat overwrites rather than inserts, track what has been processed and do not process it twice, and make each write replace rather than append.

Most of the duplicate data I have been called in to clean up was created by a retry during a brief outage, not by a bug in the application logic.

Design for the person who will fix it

This is the point most often missed, and the one I care about.

The integration runs. Something needs correcting — a record was mapped wrong, a source had bad data, a customer wants it changed. The person who has to do this is not a developer. They are doing their job and they have just discovered that something automated got it wrong.

If correcting requires SSH access and a database client, the integration will be abandoned quietly and someone will go back to doing it by hand. Build the correction path into the product. Show what ran, let them fix it, log what changed.

An integration nobody can operate does not get replaced by a person. It gets ignored, which is worse, because nobody gets the error.

What I reach for

Python, because integration work is glue code and glue code should be boring. FastAPI for a service that needs an interface, or simply a scheduled job and a database table where it does not. PostgreSQL or SQLite depending on write volume — SQLite is genuinely fine until concurrent writes become a problem, which for most business processes takes a long time.

Docker for reproducibility, a VPS for hosting, and a logging setup that someone can actually read. I would rather have a slightly less elegant stack and a system that tells a human what happened than the reverse.

The one question to ask first

Before anything else: what should happen when this cannot be done correctly?

If the answer is “someone should know”, then the design starts from the alerting and works backwards. If the answer is “it should not happen”, then something earlier in the process needs to change — usually a validation step at the point the data enters the system, which is the fix people avoid because it moves the problem rather than removing it.

In my experience the projects that went smoothly all started with that question, and the ones that struggled had assumed the data would always be clean.

If you have two systems and a person in the middle, describe the handoff and tell me what it costs you today. The first conversation is free, and often the answer is that one step of the process should be removed rather than automated — which is a good outcome to learn about early.