In this series of short blog posts, we’ll try to break down some of the engineering principles behind integrations. We’ll use examples from Seamless, but the principles will be universal.

What happens if we delete records in one of the systems in the sync?

Well, you might not like the answer – but the best answer is that in day-to-day operations you probably shouldn’t be deleting records at all. (Cough cough GDPR … I know, we’ll get there).

In this post we’ll look at what it means to delete a record and then we’ll consider what that means for an integration.

Deleting records

Let’s talk about deletion in principle before we talk about integration.

In most systems, a record, let’s say a Contact record, will have connections to a handful (or many more) other records. A Contact might belong to a Company, they may be referred to on Orders, on Help Desk Tickets, on Sales Opportunties, etc. In database terms, each of these are relations between records. In the olden days as far back as the 1990’s, we talked about “relational databases” for this very reason.

Deleting just one Contact record could have a significant knock-on effect. What happens to all those other records referring to that Contact record? The answer is very system specific, we were looking at this recently for a sync between Freshdesk and Dynamics 365.

• Freshdesk will delete the Contact and all associated Tickets. That has a significant implication on reporting and, in the case of a helpdesk system, trying to identify systemic problems which are causing multiple tickets to be raised.
• Dynamics would leave the Tickets in situ but delete the reference to the Contact.

Ultimately, each system will have a different approach to this. In some cases it will be elegant (Dynamics), in some cases efficient (Freshdesk) and in some cases, the system just won’t have the ability to delete.

One term sometimes bandied about is a soft delete. This is more like an archiving status: the record still exists, it is just not visible to all users. This can solve the relational issues, but might have UI/expectations challenges to manage.

Okay, so deleting is not great, let’s talk about integrations

Let’s talk about deletions in the context of a Seamless integration. Seamless is architected as a message queue, it polls a source system for new/changed data and transmits it to a target system. Seamless doesn’t actually know about the data itself or maintain a list of all records on each system. It was designed this way for a range of security and flexibility reasons.

The challenge of this approach is that Seamless cannot know about records that no longer exist. When polling the source system, a record that no longer exists doesn’t appear as changed because it doesn’t appear. It is a classic “Donald Rumsfield scenario” of unknown unknowns.

Simply put, if you delete a record from a source system, Seamless will never know.

You’re smart guys, you can fix this

Of course, it it is possible to engineer a solution. As always, there are options.

• Option one: A register of records. In this option Seamless would maintain a register of records in each system. We can do this by record id so there’s no content stored inside Seamless and this would address the pressing security consideration.
• Option two: Flag for deletion. In this option, we would add a flag to the record in either/both systems titled “Flag for deletion” or similar. As this flag is effectively a change to an existing record, Seamless will recognise the flag and act accordingly. This could be deleting the record in both systems and cleaning up references as required.

Flagging for deletion can be achieved in config and gives Seamless flexibility to clean-up records as required by either system in the sync. To date, this is the option that has been preferred when the question is raised.

Sometimes you just gotta delete

There are can be legitimate drivers for deleting records. A request from an individual to delete all records on hand is one such example. Under the GDPR, this is something all businesses operating in Europe should be considering. SaaS app vendors will increasingly find this is a requirement but, given Seamless’ ability to connect to all sorts of endpoints (not just modern SaaS apps), we need to ensure we have a few tricks up our sleeve.

In this context, there are a few options (always with the options!). We could implement a deleting mechanism along the lines of those described above. Or we could consider data anonymisation.

If a system doesn’t have an elegant way to clean up relationships between records, it might be better to anonymise data rather than delete it. This could be done by changing names (and other personal info) to random strings. This is certainly something that could be done with Seamless’ data transform features.

So, what happens when someone deletes a record?

That very much depends what you mean by “delete”. Is it a hard delete where the record disappears? Is it a soft delete where the record is archived? Does the system have a way of telling us the record has been deleted or do we need to construct a way of doing this?

As always, there are options.