I'm currently facing a difficult problem regarding re-archituring a legacy webshop, i would really appreciate some insight.
The system depends on a 3rd party API for it's product/variant data the latter returns a JSON "page" containing an array of products.
Currently to keep everything in sync the process is to query this API once in a while to get all the products for a supplier and then updating a local MYSQL database.
I cannot just diff the changes made to a product since there is no reliable information in the API about product updates.
Edit: let's say i update the data twice a week, in order to check if the data changed i need to query my database for each product and then check field by field. I tried it and it takes forever. The API has a field last_updated_at on every record but it doesn't reflect when a field changed only that a new indexation occurred for the whole supplier. Unfortunately, more often than not no information has to change for this indexation to occur.
This process is written in procedural PHP and is quite heavy since it depends on json_decode and some MYSQL inserts/update inside foreach loops (one for each level of the JSON) some of the documents can easily weight more than 20MB, and a supplier can have up to 1000+ products/ 10000+ variants, furthermore we are syncing up to 50+ suppliers.
I would like to depart from this model and introduce some kind of continuous update model.
I don't think i strictly need MYSQL for storing products since I have to re-normalize the data to fit. At first I was thinking about fetching a single product as the front-end requests it and caching the response, then only use MYSQL for the order/quote part. Unfortunately I can't do something like GET /product/ID, the API only accept POST request with a request body so caching seems more complicated.
Anyway to cut it short: every time i think of something it seems I am stopped in my tracks by some hurdle that keep making me question my approach. I seem to go round and round without making any headway to tackle this.
Why would you do in my position?