Determine 'sync' mechanism for database objects

[128keaton]

Things like:

• Clips
• Snapshots
• Cameras

Need to be synced bi-directionally.

We need to take into account things like temporary service outages and the like.

We mostly just need to make sure that clips and snapshots show up properly across both UIs

[javaspeak]

Scenario 1: The equivalent table on both sides has some fields modified while connectivity between the two was off. When it comes to synchronizing - how do we deal with that?

In order to know which field is the master we need to know which fields changed since the last time we synced.

Semantics: Consider we have side A and side B, and both sides have the equivalent table with a field called Blah.

If A.blah changed since the last sync but B.blah did not change then the update direction is:

A.blah => B.blah

The opposite is also the case and results in:

B.blah => A.blah

The problem arises when both A.blah and B.blah have changed since the sync.

To deal with the case where both have changed we have a number of rule based solutions that users of the system can learn. Possibilities include:

• The blah with the most recent timestamp wins
• The blah field is not synced and is marked as needing manual decision making. This manual decision making is analogous with a Git Conflict.

So if the above is modelled what is the implications on table design?

• We need to know last updated per field instead of per row
• On sync the last updated fields can be nulled out

[javaspeak]

Solution 1 for Mechanism for managing "last updated per field" instead of per row.

(i) if the "last updated per fields" were optional they could be set outside prisma by stored procedures. Else and possibly LESS easy we just create a timestamp for each field and set it to now if the field changed.

(ii) On syncing data we could then call a function which uses prisma to reset the last updated fields to null.

(iii) After that field reset and assuming we are NOT doing time based precedence of resolution of conflicts (mentioned under the comment entitled Scenario 1), and assuming a field was changed on both sides, then this would require a notification job to be triggered for the conflict to be resolved.

My general feeling is that the exceptional case whereby the same field on both sides has been edited while the cart side was offline is a rare edge case and therefore choosing a field with the latest time stamp to be the master will suffice. This will reduce the complexity of conflict resolution.

[javaspeak]

Thoughts on syncing functionality:

The above comments mean that a sync process needs access to both databases. Web Socket can send the info to the master of the process if they have "last updated fields" which are not null.

If the slave does NOT have any NOT null "field timestamps" then it should send a message on socket re-connection to say it has none.

The Master will then check on its side and act accordingly.

Likely the master will be the cloud.

[128keaton]

I think yes that master should always be cloud. One specific situation I am more concerned about arising is say what happens when the cart's internet is offline. The cart will continue to record/save snapshots, but upon reconnecting we need some sort of logic or function to have the master pull what is missing from the cart somehow. I think we can just request clips since last connection (db field), but that is another big thing to think about

[javaspeak]

I think yes that master should always be cloud. One specific situation I am more concerned about arising is say what happens when the cart's internet is offline. The cart will continue to record/save snapshots, but upon reconnecting we need some sort of logic or function to have the master pull what is missing from the cart somehow. I think we can just request clips since last connection (db field), but that is another big thing to think about

Re clips - if the clips have a database row on the cart then they can also have a last modified field which is reset to null after sync.

So as the clips are created a thread adds them to the db. Another thread reads from the db and nullifies the field when the clip itself is sent.

If the clips are stored in a folder and a database is not being used to track whether the clips are being sent or not, then we could give each clip a count in the name and then have a bigint variable which tracks the increment. The master knows the last received clip's number by inspecting the filenames it has already (which could also be held in a bigint).

The cart on reconnect would then look at its counter and inspect the directory for filenames which are higher than the current count and send those clips.

The problem with this counter is if the box crashes. To mitigate against that the counter would need to be in the db or a memory mapped file.

[128keaton]

Thankfully we do track the clips/snapshots in database, we keep track of if the file is available locally on the box or uploaded to S3. This way we shouldn't have to use an in-memory or DB counter