Thoughts on offline, in-browser datastores (localStorage) working in conjunction with canonical DBs (server: MySQL, etc.)

local_and_canonical_datastores.md

This is for a thick-client App that stores data in localStorage (called "Local" in numbered lines below) and on a centralized server, in a MySQL (or other) database (called "Canonical" in numbered lines below). localStorage would also have a "Local Migration Log". Think of this like Redis's "replay" or Rails's timestamped migrations.

1. Canonical DB has updated_at timestamp for each model. Or, in the top-level record (e.g. User), columns for each sub-class with an updated_at timestamp for that model (e.g. receipts_updated_at, categories_updated_at).
2. At initialization, Local store clones Canonical and saves it. Data rendered for user in App comes from Local.
3. User Creates a new record or Updates / Deletes an existing record.

• App saves new record to Local.
• If App is online, App saves record to Canonical.
• If App is offline, App saves a copy of the change to a Local Migration Log.

4. When App is next online, App uploads Local Migration Log and updates Canonical. Local datastore and Local Migration Log are then purged, and Canonical is cloned to Local, getting all third-party updates to Canonical as well.

To Think About

• Would need a mechanism for detecting and resolving duplicates.
• Would need to clearly indicate to user that they were in "offline" mode, and to encourage them to connect soon so their entries aren't lost.

Issues

• If the user is offline and clears their cache, they would lose data in the migration log. Seems like an uncommon scenario, but something to be aware of.

An Alternate Approach

• When offline, let the user read as much of their data as they want to, but don't let them Create / Update / Delete records.

I've only toyed with localStorage here and there as it has never been a necessity for any of the apps I've built.

You should read http://engineering.linkedin.com/mobile/linkedin-ipad-using-local-storage-snappy-mobile-apps if you're using Backbone.js for some ideas.

Makes sense, although as you mention, the tricky bit will be dealing with conflicts. If the user add/alters a record on two offline devices, which wins?

The app would need to be careful in communicating the nature of the transaction. Eg, it'd be possible for the user to think they'd completed an action, although it was added to the migration log. They may go to another machine and wonder where it is, or worse, the user browser may decide to empty caches (low memory on phone, incompatible browser upgrade etc etc), losing data.