Best practice for synchronising list data between database and PhoneGap application

Question

Scenario

I have the following components that make up an inspection styled application

• Web application (schedules new inspections and updates / removes scheduled inspections until they have been sent to the device)
• Database (stores scheduled inspections)
• WCF Restful service (returns a device user's latest schedule data upon request)
• And a PhoneGap application (displays a device user's scheduled inspection list, storing data returned from the WCF Service in local storage)

It will start with a web user scheduling new inspections for a mobile user and save them to the database. The device user will log into the mobile app and refresh their schedule list so that the latest scheduled items will be returned and displayed to them. The web user can then create / update / or remove inspections at any time.

The Problem

Each time the device user refreshes their schedule list all of their schedule data will be returned and added to local storage. This means duplicates will be created because some of the data may have already been on the device.

The Resolution

To prevent duplicates occurring I will add an extra field to the database to flag the item as "received by app" so that flagged items can be ignored the next time the device user refreshes their schedule list, and to stop the web user from updating or removing them.

Loopholes with this Resolution

1. If I flag items as "received by app" in the web service when the list is being returned there could be an error between returning the data from the WCF service and the device receiving it meaning it never reached the device. This could result in items being flagged as "on the device" when they aren't.
2. Alternatively if I choose to wait until the device receives the list and then send a message back to the database to say the device has received the items, this message may not get sent straight away due to their being no internet connection. This means a web portal user can update / remove a scheduled item for a user even though it is on their device.

My Question

I would like to know if anybody has an efficient solution for managing this loophole or if I should be changing any steps in the scenario to begin with to stop this loophole appearing?

Answer 1

You may use Timestamps to check last time local storage was updated. And with every request to the database you may compare the local storage timestamp with database timestamp and fetch only those which are created after the timestamp.