Simple Data Synchronisation for Web & Mobile apps(working offline)

Being able to work offline is an expected feature of mobile applications. This means that we have to implement a data synchronisation mechanism that keeps our local and server data in sync. I came across tis issue lately while i was working on an API for an application and i managed to work using the following technique.

Lets say that we have a simple application both for Web and mobile.

Our users can register/login and start building their todo lists,budgets, invitations etc. In our database we store our ids in UUID format (http://en.wikipedia.org/wiki/Universally_unique_identifier), that simplifies the whole synchronisation process since we won’t have to worry that 2 entries (on mobile apps database and/or our database) will share the same ‘id’. Furthermore on both databases(server/mobile app) in the tables we store the timestamp that the entry was created and updated (createdat, updatedat).

So our simple sample tables now on both databases looks like this

Items
id (UUID), title(string), whatever1(string), whatever2(int), whatever3(int), createdat(timestamp), updated_at(timestamp)

*Local databases should also have a flag ’synced’. Whenever a new entry is added locally and the phone/tablet is not connected to the internet that flag should be ‘false’. That will help us later to determine whether this entry needs to be deleted or to be sent to the server.

Its about time to talk with the developer building the iOS or Android application. Lets call him ‘Mark’ :)

STEP 1 | Show me what you’ve got

So we instruct Mark to make a API call to /somethingawesome/fetch.json when the user logs in to the application.

This call will give Mark a json(or whatever) response with the id and the timestamps of all the items stored in the server.
Sample:

{
   "budget":[
      {
         "bid":"f426f326-1cfe-4669-9e74-3c063fba542a",
         "last_update":"2013-05-01 15:42:50"
      },
      {
         "bid":"86c62841-1eae-4014-a719-9c353de55f37",
         "last_update":"2013-05-01 15:42:49"
      },
      {
         "bid":"2858f2ec-eb41-4fd1-b9c6-335429db09d7",
         "last_update":"2013-05-01 15:42:48"
      },      
      {
         "bid":"759f9727-2ffd-41b5-ba0a-fb4fd8bdae65",
         "last_update":"2013-05-01 15:42:24"
      },
      {
         "bid":"cf5dc95c-d132-4f93-bfdc-0f05e377efd1",
         "last_update":"2013-05-01 15:36:06"
      },
      {
         "bid":"d1a327c8-5050-4f63-a4ea-5c25843f2fd6",
         "last_update":"2013-05-01 15:36:06"
      },
      {
         "bid":"083dc833-4e73-4fb7-838e-efe1f379a1b1",
         "last_update":"2013-05-01 15:36:06"
      },           
      {
         "bid":"f77efc1b-0826-453c-b2f7-103095fd8046",
         "last_update":"2013-05-01 15:35:50"
      }
   ],
   "invitation_categories":[
      {
         "icid":"c672b45a-f63d-4ada-8cca-28875e0489d5",
         "last_update":"2013-05-01 15:43:12"
      },
      {
         "icid":"e13b0399-1368-46c0-8659-bdf791069b44",
         "last_update":"2013-05-01 15:43:09"
      },
      {
         "icid":"3766d8e0-b4c4-4c83-aaf8-91b1577298d7",
         "last_update":"2013-05-01 15:43:06"
      },
      {
         "icid":"91ac021d-7452-4f49-a83d-e6d0293b0e1f",
         "last_update":"2013-05-01 15:43:02"
      },
      {
         "icid":"3173a537-35c6-4290-8680-253420c7ac84",
         "last_update":"2013-05-01 15:43:03"
      }
   ],
   "invitations":[
      {
         "iid":"44ad90ef-8d26-4ebc-b570-f1e00dda9589",
         "last_update":"2013-05-01 15:43:42"
      },
      {
         "iid":"91e14912-4529-4413-8ea4-d564973e1c6e",
         "last_update":"2013-05-01 15:43:40"
      },
      {
         "iid":"d1adbac8-b1c2-498e-9b1f-414b7a8eb5c7",
         "last_update":"2013-05-01 15:43:39"
      },
      {
         "iid":"5874b44a-1ff2-42fa-9149-c940548e9301",
         "last_update":"2013-05-01 15:43:38"
      },
      {
         "iid":"4617878f-864d-433a-980b-23092d55463b",
         "last_update":"2013-05-01 15:43:38"
      },
      {
         "iid":"e354e387-00df-4fbc-b1a9-7fd24f27ac3d",
         "last_update":"2013-05-01 15:43:37"
      } 
   ],
   "wishlist":[
      {
         "gid":"6dd3d070-e992-445e-93ef-1e5ffb6f2ca1",
         "last_update":"2013-05-01 15:42:19"
      },
      {
         "gid":"5959f225-de4b-43ee-911d-80f07c061d9b",
         "last_update":"2013-05-01 15:42:17"
      },
      {
         "gid":"a5fa30d7-3ad7-4a0a-b85d-72a8514f6ef4",
         "last_update":"2013-05-01 15:42:15"
      },
      {
         "gid":"53db6a0a-6115-47d9-ae2d-04dab103032a",
         "last_update":"2013-05-01 15:42:13"
      }
   ],
   "gift_list":[

   ],
   "todo":[
      {
         "id":"1e240b66-c984-4028-9562-a32477c3c44b",
         "last_update":"2013-05-01 15:44:54"
      },
      {
         "id":"f1966b50-fbf5-45d9-ace1-8a059f34aeff",
         "last_update":"2013-05-01 15:44:52"
      },
      {
         "id":"f4559157-bb59-4c8a-82a2-30bd914a94cb",
         "last_update":"2013-05-01 15:44:51"
      },
      {
         "id":"97c5d1d0-9b45-4c41-8a10-c3db8d19d00f",
         "last_update":"2013-05-01 15:44:49"
      },
      {
         "id":"8467b52f-a676-4b12-b1e3-14ae9ef3e382",
         "last_update":"2013-05-01 15:44:41"
      },
      {
         "id":"560a668f-e79c-420e-a494-7971949f69e8",
         "last_update":"2013-05-01 15:44:39"
      }    
   ]
}

At this point. Mark will check all id’s in the according tables.

Case 1: He has not entry for that id in his local database. Action: he needs to insert the entry in his database.

Case 2: He has entry for that id in his local database. Action: Checks the timestamp, if it is newer than the server we will update the server entry otherwise we will update the local entry.

Case 3: Mark has some ids in his database that are missing in the fetch.json. Action: Checks the flag ’synced’ if it is false this means the entry was created offline and we need to add it to our server, if synced us true means the entry was deleted and Mark need to delete it locally too.

STEP 2 | Can we be friends now?

So far so good. Mark knows what needs to be add/updated or deleted both locally and remotely.

Initiating phase 2....

We instruct him to make a API call to /somethingawesome/synchronise.json that should look like this. In each key(add,update,delete,get) we have an array of objects that we need to add/update or delete on our server.
Using the 'get' key, Mark informs us about the entries he needs more info so we can send them to him.

{
   "add":{
      "budget":[

         {
            "bid":"e162cf44-f6d4-4f55-9096-242cb0d2f9a4",
            "expense_title":"ADDITIONAL PRINTS\/VIDEOS",
            "assigned_to":8,
            "status":1,
            "cost":200,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"725cd03b-7307-497e-912a-63ae421fb4c7",
            "expense_title":"INVITATIONS & REPLY CARDS",
            "assigned_to":8,
            "status":1,
            "cost":200,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"edd11083-5efd-4a04-9857-2ff7d16f533f",
            "expense_title":"Wedding rings",
            "assigned_to":8,
            "status":1,
            "cost":300,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"6d1ed956-3eb1-48d2-8de3-f1c61eb66860",
            "expense_title":"LIMO(S)\/CAR RENTAL",
            "assigned_to":8,
            "status":1,
            "cost":230,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         }
      ],
      "invitations":{

      },
      "gifts":{

      }
   },
   "update":{
      "budget":[
         {
            "bid":"d9bd512f-f3c0-4aba-b3ea-e1eafb39a33a",
            "expense_title":"Ceremony location fee",
            "assigned_to":8,
            "status":1,
            "cost":200,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"22971134-2de0-41ed-b1a7-ce95e5953422",
            "expense_title":"Officiant Fee\/Donation",
            "assigned_to":8,
            "status":1,
            "cost":200,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },

         {
            "bid":"725cd03b-7307-497e-912a-63ae421fb4c7",
            "expense_title":"INVITATIONS & REPLY CARDS",
            "assigned_to":8,
            "status":1,
            "cost":200,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"edd11083-5efd-4a04-9857-2ff7d16f533f",
            "expense_title":"Wedding rings",
            "assigned_to":8,
            "status":1,
            "cost":300,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         },
         {
            "bid":"6d1ed956-3eb1-48d2-8de3-f1c61eb66860",
            "expense_title":"LIMO(S)\/CAR RENTAL",
            "assigned_to":8,
            "status":1,
            "cost":230,
            "couple_id":"ac646699-47b1-4b70-b794-e069891bad56",
            "brideOrGroom":"1",
            "created":"2013-05-02 10:24:23"
         }
      ],
      "invitations":{

      },
      "gifts":{

      }
   },
   "delete":{
      "budget":[
         "ac646699-47b1-4b70-b794-e069891bad56",
         "204efd52-a6e1-4b6d-85de-086502cfb95e",
         "ac646699-47b1-4b70-b794-e069891bad56",
         "204efd52-a6e1-4b6d-85de-086502cfb95e",
         "ac646699-47b1-4b70-b794-e069891bad56"
      ]
   },
   "get":{
      "budget":[
         "ac646699-47b1-4b70-b794-e069891bad56",
         "68279950-2dc2-48ae-a29e-b1b8cc8484f1"
      ]
   }
}

This call will return Mark all the detailed info for the entries he need to store in his local database and it will also inform us about the entries we need to add/update or delete.

We can also use the same calls (fetch and synchronise) anytime we want in the application. For example on ‘Release to update’ or whenever internet connectivity is restored.
We could also make it function per section by not sending data from all the tables but by constructing the json to include only specific tables(only budget for example.).

It is also important to implement a locking mechanism to make sure that 2 mobile applications (for example our Android app & our iOS app) are not syncing at the same time.

I would love to hear your thoughts on synchronising web and mobile applications. How did you manage to solve synching issues? Are there any helpful tools we can use in these situations?