Sync customers between shop and CRM-system (two-way)

Question

I have a shop system that is based on a MySQL database and over which I have full control. On this system runs my synchronization plugin. And then there is an external CRM system with a fixed API. This API allows reading and writing of customers. When reading, I can specify a UTC timestamp and it will provide me with all customers that have been changed since that timestamp.

Now it may be that customers change their data themselves in the store, but also the staff in the CRM system when the customer contacts support. I know that two-way synchronization brings some problems, so I want to keep it simple and take the record that is newer (assuming that the time of the servers is reasonably synchronized).

For example, my synchronization process runs every 30 minutes. It first determines all changed local customers of the store since the last synchronization and via API all changed CRM customers. Then it compares if a customer appears in both data packages. If so, the newer data record is used. If not, the record is first put into a queue to be updated/inserted either locally, or in the CRM system.

My only problem now is at the end of the synchronization process. Which timestamp do I save now for the next call of the synchronization. If I take the current timestamp, it is possible that during the synchronization data from the customers or the CRM users will be updated and then I would never synchronize this data again. So I can save only the timestamp of the last update of a customer profile (locally or in CRM). But then on the next run I would be presented again with all the customer-profiles changed by the synchronization itself and an update loop would result.

Unfortunately, it is not possible to lock the CRM during the synchronization. I also cannot compare two date fields with each other in the CRM-system (SELECT ... WHERE updated_timestamp > last_sync_timestamp). I could store locally in the shop-database the modification timestamp of each CRM record (during the last synchronisation) and compare it with that, or am I missing something?

How would you guys solve this problem?

Answer 1

If the CRM system does not provide a mechanism for safe concurrent updates, you can't do concurrent updates safely. Period.

That said, your options are basically to decide on a single source of truth, or synchronize bidirectionally with very short windows of differing data in the two systems.

The single source of truth in your case would be the shop system. Staff would need to have a specialized role in that system to update customer data and would need to be aware that any change to customer data done in the CRM system is likely to be overwritten.

If you decide that you want the systems should be synchronized bidirectionally, you may still find that some data fields are typically changed by the customer, while others such as credit rating can only be changed by staff. This may help to reduce the risk of conflicts as you have a single source of truth per data field. An additional strategy might be to update the CRM data in a tight read-modify-update operation that can detect whether the data being updated has changed between start and commit of the edit operation, so it avoids overwriting data that was changed in the meantime. Merging independent changes would likely be easiest if you keep a mirror copy of the CRM customer record in the shop database and use that as the basis for merge decisions. If both the CRM record and the shop record changed since the last synchronization, some merge strategy would be needed. Here again, you could reduce the chance of conflicts by triggering the synchronization from the shop database editing action, reducing the time window in which a merge conflict could arise..