Web service + sync database + main application architecture

Question

I got to develop a web service that will help synchronize some of our data with APIs from Google, as well other services in the future. I thought, and my boss too, we should go for a stand alone web service mounted on a React server, allowing everybody to interact with it through HTTP.

There's a synchronization database that serves to log every change made by everyone (thus what should be changed if you're outdated). I sorted out 4 "architecture", in all of them the WS (web service) will manage Google & Co APIs independently, it's all about how to interact with the sync DB and/or the application :

• WS (webservice) and application both query the sync DB, but no contact between them ;
• WS solely deals with the application, no interaction with the DB that is managed by the application ;
• WS manage eveything separately (APIs, including our application, and the DB) ;
• WS queries the sync DB, but the application will push notify the WS everytime it makes changes to the db (and WS will push notify the application everytime it receives changes from APIs) ;

My boss wants me to go with the 4th solution, since he doesn't like the idea that I may have to write down a UDF (yeah it's MySQL, would be better off with PostgreSQL functions :s) to push notify the WS everytime a change occured in the DB (no matter the DB would send HTTP notification or write down a socket the WS would listen to).

He told me that it's making things a lot more complicated, that it breaks the "logic domains separation", adds C code in it and stuffs. The point is that doing so, the sync DB will stand between the WS and the app, and will get bypassed by them when it comes to notifications. So roles of each entities isn't that clear anymore to me : is the WS an abstract gateway to various API ?? A synchronization module ?? Is it really a stand alone web service then ?? Will the web service reall be a... well... web service !? Solution 1 is the easiest to implement, solution 3 seems the most "logic", solution 2 is somewhere between ease of implementation and domain separation... Solution 4 seems like providing no benefit at all !

Am I wrong to think any other solutions would be a lot better ? Although I will not try to go against the decisions, I would like to clear things up for myself, and my terrible curiosity !

Thanks for reading !!

Answer 1

So, if I have this right - you want a blackboard architecture where data is written to the DB, and then something triggers your WS to notify it that data is ready to be read.

That's a fair architecture, it works even if the notification stops working for some reason (assuming the notification simply tells you to go look for all changes rather than specific new data).

You could pass the data that has changed directly to the WS in the notifications though, if the data is small, but then you might have sync issues if a notification change doesn't get through.

You could poll the DB for changes regularly - this isn't much different from the DB+notification architecture, but is easier to code. Its probably best to start with this and add notifications that trigger the poll later.

But the concept of putting the notification in the DB so trigger a notification when a row changes, seems sensible - it means you won't have to change the applications to send the notifications, basically you're centralising the notification mechanism in the place where every app writes to anyway. I'd do it that way, unless there is a reason you do not want updates on aper-change basic (eg, if you write several changes and then you want to sync in a single call), but even then you can simply add a new entry to the DB that can be checked to send the notification or not.

So, I think your preferred architecture is the right way assuming your use-case for it is right, eg you might not want to be called 10,00 times if a row was added so often, but I think in your case you do. BTW, there is a UDF already written for MySQL to do this.