[checkpoint]: one-instance architecture

[sanam2405]

keep the postgres SQL and mongoDB NoSQL database in synchronisation with each other

Discussed in #37

^{Originally posted by sanam2405 April 23, 2024}
SITUATION - I have two databases (one Postgres and another MongoDB) storing data about my users (some data have intersections like email, name, age, etc.) and some data is exclusive to the two databases.

TASK - Now I wish to have my data in sync with each other. Basically the common fields in both the SQL and NoSQL should be in sync real time even after performing CRUD in any of the DBs or both the DBs.

I am thinking about implementing a Kafka CDC(change data capture) but I mostly find that people are syncing SQL-SQL or NoSQ-NoSQL DBs using Kafka.

What is a viable solution if i want the synchronisation to work in both ways?

To-Do

• mongoDB user schema should have the postgres user id
• seed the sql and nosql dbs with common data
• add kafka change data capture to stream the CRUD data changes across the databases

[sanam2405]

A problem however exists in this solution that if the CRUD in loc server is successful but the CRUD in ts-backend server fails, then the loc server CRUD is not rolled back. Hence, consistency is not maintained across Postgres and mongoDB in case of a successful Postgres R/W followed by a failed mongoDB R/W

This can be resolved by using an event driven service which continuously checks for the DB read-writes and updates the states of the DBs to the latest committed one that was consistent.