Bi-Directional Offline Sync Logic ( WebSql / IndexedDb / LocalStorage on the client side using JayData frameworks and ASP.NET Web Api2 OData REST and Sql Server on the Server)



I could not find a good fully baked framework to do this so i decided to write the logic based off of the Microsoft Sync Framework algorithm. Hopefully they bake something like this into EF 7 someday.

Entity Model Setup
These are the Extra Fields Needed on each Entity/Table to make a bi-directional offline sync senerio work:

Guid (Client Generated)
_IsTombstoned = false
_UpdatedAt = UnixTime.Now

js - and c#

_State = add/updated/delete is only needed in the client model (override jaydata entity and add filed that does not get passed to server)

Client Logic

  1.  Only offline sync those Enities that are needed offline (cachedEntities). We are only going to allow a subset of entities to be available in the offlinedb this will be known as the cachedEntites array there is rarely a need to sync entire database (not a safe solution as a database can grow to TB+, doubt a mobile phone could handle that 🙂
    1.  UI – we need an Offline Sync Settings page that will lists all main entities and asks if user want to store offline: TodoItems => available offine y| N
    2. ???maybe??? Need a user settings table in server database to contain offline subset entity metadata, telling what Entity Items are stored offline for a particular user

2. Use Guid’s
use client generated GUID to avoid having separate Id’s on client and server side. Every entity must have a _State , _UpdatedAt, _IsTomstoned fields to keep data in sync on the server and client side => the following are assume since we are using a bi-directional sync – the newest _UpdatedAt field either from the client or server wins the update. That means this can undo a Tombstoned items as the newer _UpdatedAt date wins

3. Sync with the server in Batches using OData
have an offline and online db instance using jaydata. Use the setSync batch (oData) alogarthm to batch the sync in sets of 200. This is because if a client if offline for a long time, lets say a year you could build up a large number of updates, possibly causing one HTTP request payload to be GB’s in size causing the request to fail or recieve an “413 Request Entity Too Large” status code. Reference here:

4. Track Client Side Entity Changes
Everytime something is updated/deleted/added in the client simply update it in the offlinedb and set the appropriate _State = ‘add’ ‘update’ ‘delete’,also set the _UpdatedAt = datetime.UtcNow (we assume the client has the correct UTC date, we are using mobile phones connected to a cell network so this is a given, also azure is connected and has the UTC correct time

5. Client to Server Sync First

  • Every 60 seconds a batch sync will occur in the client using setInterval()
  • First check that a sync is not already in progess (datacontext.isSyncing = false)
    • if no, then check you are online ( ) then loop all items in offlinedb. do a pre-flight check to make sure none of the items are tombstoned on the server side (if they are then a msg needs to be sent to the user), then
      • state = add => use offlinedb.entityItems.add(entity)
      • state = update => use offlinedb.entityItems.attach(entity)
      • state = delete => use offlinedb.entityItems.remove(entity)
      • state = no => do nothing
    • if  yes, datacontext.isSyncing = true then wait another 60 seconds

6. Server to client sync Second

if an item is updated/deleted on the server we need to true-up with the client
does server side trump client (server to client sync) or does client trump server (client to server sync) or does last _updatedat trump all (two way sync) ?

7. SyncInit() Logic at App load if network is available

  • Set global variable isSyncing = true;
  • First sync client to server (server will take care of any conflicts)
  • Second loop cachedEntited array and get latest updates from server to client, this will be done batches.
  • The logic in SyncInit loop will :
    1. Compare the server’s json payload’s _UpdatedAt field with local payload
    a) if datetime is the same then no change
    b) if dates are different then update the local entity with the servers entity data
  • Last set isSyncing = false;

8. ???maybe??? Need conflict resolution built into the app, for instance when a conflict is detected pop a message up to the user stating there has been a conflict between the local and server version and allow the user to choose a) use local version b) use server version might not need this due to bi-directional sync and the fact we are using an _UpdatedAt field on each entity

Server Logic
9. Each Controller Needs custom logic in the REST API Verbs

// GET (Read)
// none

// POST (Add/Create New)
// none

// PUT (Full Update)
// check for conflicts
// is item already tombstoned?
// Y: then compare _UpdatedAt dates to see if client or server wins
// If client wins then untombstone and update data, if server wins then tell send back the http status 404 Not Found (the subsequent server to client sync will clean this up)
// N: then then compare _UpdatedAt dates to see if client or server wins
// If client wins proceed with the update, if server wins then simply pass back the success response with the server data

// PATCH (Partial Update)
// same logic as PUT

// DELETE (Delete/TombStone)
// don’t do a real delete simply tombstone this entity, need a schedule sproc to clean this data up periodically

// TODO think about the logic here, if somebody deletes in the past, then somebody updates in the future what happens? See Test #3 probably send a conflict back
// if the user accepts the delete then simply update the clients _UpdatedAt at on the delete status


test senerios for bi-directional sync

1. Very late update sync from offline user
// If a user collects data from the offline mobile app and does not get access for a week, the data is marked status = add/updated locally
// Then a few weeks later the data is synced to the server, the server finds a conflict because another user updated the data at a later date
// therefore that remote server trumps the mobile app because the _UpdatedAt date is newer.
// We might want track this merge conflict and maybe let the user know what version to take (take local or take server)

2. An item is deleted on server side but the client has updated it since the last sync due to offline connectivity (Tombstoned Test)
// an item is deleted out of the database by a user using the online web app
// _IsTomstoned = true and _UpdatedAt = Unix.Now
// then an offline user of the mobile app tries to update this entry.
// The user should get a pop up stating the item has been deleted, Do you want to restore?

3. An item is deleted on the client, but due to offline connectivity and the time passed the data has been updated on the server (Very late delete sync from offline user Test)
// If a user deletes data from the offline mobile app and does not get access for a week, the data is marked status = deleted locally
// Then a few weeks later the data is synced to the server, the server finds a conflict because another user updated the data at a later date
// Should the client trump the server because it was a delete? Or should we notify the client of a conflict stating, i know you are
// trying to delete this data but somebody updated it since then…do you really want to delete?
To Read
// read this article to figure out sync alogarthm
// IsDirty = true when you do an insert(add/post) or update (attach/put)
// that means in each
// IsTombstone = true when you delete an item and it does a soft delete on the server (have a scheduled sproc clean this up periodically).
// that means in each delete method we need to do a tombstone instead
// – sync alogathim
// – unidirectional (server to client)
// azure mobile service offline sync –

// and some borrowed logic from the Microsoft Sync Framework
// Maybe One day this should be baked into EF7 from my speculations from this blog post -

My experience developing a Windows 8 Store App using PhoneGap jQuery and Facebook


I have not posted in a while but had to post my less than satisfactory experience signing up for the Windows Store Dev Center and porting my HTML5/JavaScript app over to a Windows 8 Store app. In comparison I had a great experience signing up and getting my app in the Google Play store, that was a streamlined process with no hoops to jump through. On the other hand Microsoft really messed up and dropped the ball  this time with their dev center experience. To start off they had to verify a payment from my bank, which triggered my bank to call me on suspected fraud activity. I told them it was a legit payment.  A million other services use this type of verification process by depositing a small amount (pennies) in the users account, then the user verifies it. Leave it to Microsoft to charge over 2 dollars for the verification and cause a fraud alert to go off at my bank.

Then to make matters worse I am trying to verify my account to submit an app to the store but get this error: We can’t connect to the service. Please try again later. So it forced me to submit a support case to M$FT. Hopefully they fix it, but it has not been a pleasure working on the Microsoft platform at all.

Another issue I had with my Windows 8 App was making PhoneGap + jQuery + Facebook Javascript SDK  work. I had to change a number of things in my code for my PhoneGap build app to work in a Windows Store environment. Unfortunately it does not just work out of the box…. come on msft 😦 .

Below are some of the issues I ran into:

1. jQuery – special version (jquery-1.8.0-windows8-ready.js)
2. iFrames
3. Facebook Authentication with the FB Javascript SDK

To make Facebook Authentication work in a PhoneGap Windows 8 Store app

* Create Azure Mobile Service
* Change the logic code in Auth.js to first authenicate with mobile services then pass the token back to the client:

// set up mobile services
App.mobileService = new WindowsAzure.MobileServiceClient(”,

App.mobileService.login(“facebook”).then(function () {

Other things I had to modify

* When in doubt and you get a javascript exception while debugging try wrapping your code in this call:

MSApp.execUnsafeLocalFunction(function () {
// your code

* When using jquery append, sometimes you need to sanitize your html with this code: `.append(toStaticHTML(“<br/>stuff”));`

* Changed getting data from facebook by passing in the access token and avoided using the fb js sdk. Changed FB.api(/me) code to:

$.get(‘; + fid + “?access_token=” + YOUR_TOKEN, function (r) {
name = “<br/>” +;

* I had to wrap line 3061 of `jquery-1.8.0-windows8-ready.js` in a try-catch to avoid an error.

// nick added try-catch… for some reason an error was being thrown 0x800a01b6
// – JavaScript runtime error: Object doesn’t support property or method ‘apply’
ret = ((jQuery.event.special[handleObj.origType] || {}).handle || handleObj.handler)
.apply(matched.elem, args);
catch (e) {
ret = undefined;

* I had to remove all the iframes in the application. I read there is a workaround online, but did not bother to implement, I simply removed the youtube iframes.

* Removed the export to csv functionality since it has to do a full page post back to a web page on a different domain, there is a possible workaround I did not
investigate yet.

Git – Non-Fast-Forward Error


If you get the error below:

To origin.git
! [rejected] dev -> dev (non-fast-forward)
error: failed to push some refs to ‘origin.git’
To prevent you from losing history, non-fast-forward updates were rejected
Merge the remote changes (e.g. ‘git pull’) before pushing again. See the
‘Note about fast-forwards’ section of ‘git push –help’ for details.


Simply add –force to your git push


Git – How to ignore file extension


Alot of times in a visual studio project you will want to ignore binary files types, such as .sln and .suo to make merging easier. This is how you do this:

touch .gitignore

git add .gitignore

git commit -m “added gitignore file”

Now you must edit the .gitignore file add the file extensions you want excluded



To stop tracking a file that is currently tracked, use git rm –cached.

GIT Merge Error


If you are trying to merge a dev branch onto the master and get an error similar to the error below you might need to use rebase.

$ git merge dev
Auto-merging index.html
CONFLICT (content): Merge conflict in index.html
Automatic merge failed; fix conflicts and then commit the result.

If you are trying to make master look like your dev branch then you need to use rebase like this:

git checkout master

git rebase dev

git push origin master

Git Cheat Sheet


Switch Between Dev and Prod Branches:

git checkout dev

git checkout master

Force a push (+)

git push origin +master

Reset back to a previous commit

git reset –hard commitID

Delete a local branch

git branch -D dev

Delete a Remote branch (make sure you don’t delete the main branch)

git push origin :dev

Windows Junctions – Great for mirroring a project to a PhoneGap Folder


Windows Junctions – Great for mirroring a project to a PhoneGap Folder

I have been recently working on project in my visual studio projects folder and needed to keep coping them over to my andoid workspace in eclipse for building my phonegap project in the www folder. This was a pain, so i found a sysinterals tool to make symbolic links in windows. What a time saver!