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?