Improved Offline Data Sync Pattern in OutSystems

In this article we’ll explore an alternative read/write offline data sync pattern for OutSystems mobile apps, using UUID based primary keys

12 min read3 days ago

Image generated with designer.microsoft.com

TL,DR: This article explores an alternative pattern to the OutSystems read/write offline data sync ones. Two variations are presented, one using client-side UUID key generation, and the other one using a pre-generated pool of long integer keys. Both are simple and easy to implement, timestamp based, and achieve some important advantages such as: only synchronize the data that has changed (deltas), do not require remapping of primary keys, do not require remapping of foreign keys.

Intro to OutSystems Offline Data Sync

OutSystems mobile application, when set to use local data, apply a mobile first approach to data storage. This means that the data is always available on the mobile device’s local storage, and is synchronized with the server database on set intervals or events.

OutSystems will even document five different patterns to handle offline data sync:

Read-only data
Read-only data optimized
Read / write data last write wins
Read / write data one-to-many
Read / write data with conflict detection

From this list, the first two patterns are read-only, meaning that the mobile device will download data from the server to the mobile device, but no data will be uploaded to the server. This means that the data can’t be changed locally, and as such, these two patterns are only viable to be used in applications that show menus, catalogs, or other records that won’t be changed on the mobile device.

As far as the amount of data that is downloaded, the first pattern will always delete all of the records that exist on the mobile device and then download all of the dataset from the server again on each sync event. The second one, data optimized, uses a timestamp approach to download only the data that has changed (Delta), and as such is a much more sensitive approach.

The other three patterns are read/write, meaning that the data can be updated on the mobile device or on the server. When synchronizing data, the mobile device will first upload the records that were changed (using Boolean flags for the records) and then download new data from the server. It has three variations, the first one is “last write wins” (LWW), a pattern that is used to sync tables, but only ones that don’t have any foreign keys, and where the latest update to a record is kept. The second pattern is “one-to-many”, meaning that it can be used in tables with foreign keys referencing other tables, and the third pattern is “conflict detection”, meaning that instead of using a LWW approach, it will let users decide on what data to keep.

Of these five patterns, Service Studio can automatically implement (even if partially) two of them: read-only and the read/write last write wins.

If used in the right scenarios, the two read-only patterns serve their purpose, even if it’s unfortunate that only the simplest one (non data optimized) can be implemented automatically.

In this article, we will focus mainly on an alternative to the “read/write data one-to-many” pattern, as it is the adequate one for most applications that rely on a relational database model.
For the scope of the article, we will ignore the “conflict detection” pattern as its main focus is on versioning records.

The Problem

First, let’s look into the read/write last write wins pattern. This is the simplest of the read/write patterns documented by OutSystems, and it’s the only one that can be implemented automatically in Service Studio.
This pattern has two main shortcomings, which are the inability to handle local records with foreign keys and the fact that it is not very efficient regarding data and bandwidth usage.
This data sync pattern works in a way that on the mobile device, the tables have boolean attributes, that allow for the upload of just the records that were updated since the last sync, which is a good thing!
But then, after the upload, all of the local records are deleted (all of them, not just the updated ones), and the entire dataset is downloaded from the server and rewritten locally.

Unless the amount of data is very small, this will become a problem very fast. Imagine, for example, an app that takes photos, stores them locally, and then syncs them to the server. Only the new photos get uploaded to the server, but then all of the already existing photos will be downloaded again from the server on every sync.
All of the OutSystems read/write patterns documented will have this behavior, and while you can use sync units to determine what entities will be synced, the flow will still be the one described above.
Ideally, only the records that were changed since the last sync should be downloaded from the server to the mobile device (updated or new data is commonly referred to as the deltas).

As for the “read/write one-to-many” pattern, its focus is on the synchronization of data for local entities that have foreign keys. This type of relationship between entities presents a problem in that the primary keys for records created locally will be discarded when writing those records to the server.
This happens in order to avoid clashes between local primary keys created on different devices, and this behavior is also present on the other read/write patterns.
But when you have foreign keys and you rewrite the primary keys for the entities, unless you have a way of dealing with this change, the references between entities that reference each other will be lost.
The way this OutSystems pattern deals with the keys change is by saving a Key/Value pair list that maps all the new records local primary keys for the entity that is referenced, and it’s corresponding new primary key generated by the server. Then, before writing the referencing entity to the server, its foreign keys are remapped using that KV pair list created before, and then finally, the entity will be written to the server.

This is as complicated as it sounds! And it adds a lot of complexity to the data sync actions used on this pattern to sync entities with one-to-many relationships.
In cases of mobile applications that have a large amount of entities with relationships between them, and even more so if there are long referential chains, this can lead to code that is both overwhelming to debug and maintain, and ultimately, can even lead to the loss, duplication, or orphaned records.

Solution

The basis for the pattern presented in this article is the OutSystems Read/Write Last Write Wins pattern, and the two issues to be addressed are:

Downloading only the delta records, to avoid the unnecessary download of records that have not changed since the last sync
A method to avoid having to remap all foreign keys on entities that reference others, in order to simplify the implementation of the sync process

Regarding the sync of only the delta records, the solution is rather easy, as you only have to implement timestamp based attributes on the entities, similar to what is already being used on the OutSystems “read only data optimized” pattern, with the major difference being that we are going to implement it for both the local storage records and also for the server records.

As far as entities and their attributes go, the generic entities will be as shown in the image below.

The generic local entities will have an attribute named “IsActive” of Boolean type, to keep track of deleted records (we will be using soft-delete) and another attribute named “ModifiedOn” of type DateTime that will be used to query what records were created or updated since the last sync operation.
Still on the local device, there will be an entity called SyncTimeStamp that keeps track of the date and time of the last sync, for each of the local entities that will be synced.

As for the generic server entities, they will have an extra attribute named “ServerTimeStamp” of type DateTime. This attribute will be used to keep track of the time when the record was uploaded to the server, it’s purpose is to help with the LLW logic, and, alongside the “ModifiedOn” attribute, also help with the sync logic.

As for the one-to-many relationships, a possible solution is to use keys that are immutable so that we don’t have to remap everything on every sync. A solution for this is the use of UUID based keys.
Universally Unique Identifiers, UUID, are randomly generated keys that have negligible probability of being duplicated, and as such they can be created in a decentralized way (the term Globally Unique Identifier, GUID, is also used).
This makes them an almost perfect fit for our use case, as we can create them on any mobile device without the need of assistance or verification from the server, and they will still be unique.

As such, we just need to use UUID primary keys on the entities that we need to sync, and we don’t have to worry anymore about remapping primary keys or foreign keys.

OutSystems has an out-of-the-box solution for generating UUIDs server side, but for the client side you need to generate one with JavaScript or to use a Forge component, like, for example, Generate Guid Client Action, that can generate V4 and V7 UUIDs.

How Does it Work?

In this section we’ll have a conceptual look into the CRUD data actions used as well as into the flow for the Synchronization action. You can have a look at the actual actions in the example application from the following link: OutSystems Forge | Optimized Read/Write Sync Pattern

As already stated before, this pattern is based on the ones documented by OutSystems, and as such, we will be using similar CRUD actions. The purpose of these actions is not only to perform the intended CRUD action on the database but also to set the attributes that will then be used during the synchronization process. They are separated into two groups, the actions to be used on the mobile device, and the ones to be used on the server side:

Actions on the mobile devices:

CreateOrUpdate - Besides wrapping the corresponding Entity CreateOrUpdate action, this action will also set the attribute ModifiedOn to the current date and time, to be later on used by the sync action. If the record is a new one, a new UUID will also be created and set as the primary key of the record.

Delete Record - This action will simply set the IsActive attribute to False (soft delete), and update the ModifiedOn attribute to the current date and time.

Actions on the server side:

CreateOrUpdate - Same as for it’s mobile counterpart, it wraps the corresponding Entity CreateOrUpdate action, and sets the attribute ModifiedOn as well as the ServerTimestamp to the current date and time, to be later on used by the sync action. If the record is a new one, a new UUID will also be created and set as the primary key of the record.

Delete Record - This action will set the IsActive attribute to False (soft delete), and update the ModifiedOn as well as the ServerTimestamp attribute to the current date and time.

Notes about the ServerTimestamp attribute - this attribute tracks when the record was stored to the server database, while the ModifiedOn attribute tracks when changes to the record data were made.
They might appear redundant, but they are important for the LWW sync logic.

The Sync Algorithm

Once triggered, the offline data sync will run the following flow:

Similarly to the documented read/write OutSystems sync patterns, it starts by getting all the records that were modified since the last sync and sending them to the server.

The flow then keeps going server side, where the uploaded records are updated (if needed, since some checks for the LWW logic will take place here). Still on the server side, the recently updated records will be read and sent back to the mobile device.

Again back to the mobile device, the flow now updates all the records received from the server, deletes all records marked as not active, and saves a new sync timestamp.

As you can see, this flow is super simple and didn’t need any special attention to primary keys or secondary keys, and as such, no remapping was needed, all of this thanks to the immutable nature of UUID keys.

Not Everything is Sunshine and Roses

UUIDs are not the ideal choice for primary keys as they can lead to slower operations, especially in OutSystems, where UUID keys must be stored as text (some databases support UUID as a native data type).

Performance wise, it’s hard to quantify the impact as it depends on a lot of different factors, but in general terms, the performance of large integer keys can be 40% faster when compared to UUID keys. Careful coding and proper indexing could mitigate most performance hits.

For more information on the performance impact of UUID vs large integer keys, check this great article: Ardent Performance Computing | UUID Benchmark War

A Variation of this Pattern

A variation of this pattern can also be used, one that uses long integer based keys instead of UUIDs while still avoiding the issue of having to remap all foreign keys.
One way to accomplish this can be to create a pool of empty records on the mobile device, ready to be used, even if the device is offline. These records will be empty and hidden, but their ID will already be set and registered on the server.
As such, when using any record from this pool of empty records, when uploading them to the server, the ID can be kept, and thus no remapping is needed.
Couldn’t we store and maintain a list of reserved IDs on each device and on the server to be used when needed? Yes, there are many ways to go around this issue, but I think this approach is as simple and efficient as it gets :)

You can have a look into an example that uses this alternative approach from the following link: OutSystems Forge | Optimized Read/Write Sync Pattern

Wrap Up

Generally speaking, the offline data sync patterns documented by OutSystems have some shortcomings, and except maybe for the “read-only optimized”, there are not many scenarios where their use is advisable.

Specifically for the read/write patterns, the shortcomings are: the need to re-download and rewrite all new local records with the primary keys coming from the server, the need to remap the foreign keys on all records that reference new records, and also the fact that all of the documented read/write patterns delete all local records and then download again the entire dataset from the server (ideally only the Deltas should be downloaded from the server).

The pattern suggested in this article provides a solution that will, at all times, have the same primary keys for the mobile device and for the server entities. This avoids the need to do any re-writing of records or remapping of foreign keys, making the sync process much simpler.
The other improvement was to implement a way to upload and download only the delta records. This was achieved by the use of simple timestamps (similar to what is used on the OutSystems “read-only optimized” pattern).

While both the alternatives for this method will work, the “pre-generated key pool” method has some advantages, such as keeping the better query speed of long integer keys when compared with UUID text keys, and also because it is easily implementable in applications that are already using long integer primary keys (which is the OutSystems default).

The end result is an offline data sync pattern that is simple to implement, is safer, faster, and it also saves bandwidth and data usage.

Some aspects were intentionally left out of this study, such as database denormalization and the exact behavior of LWW. This was done so that the focus of the pattern could stay on how database primary and foreign keys are used and to make it as simple as possible while still adding value.

AI disclaimer: Apart from the main image of the article that is AI generated, all this article was written by the author.