5

I would like to make an offline-first To-do app which works on both the mobile app (SQLite) and the web app (PostgreSQL). The app will have these business models:

User:

  • id
  • name
  • created_datetime

Todo:

  • id
  • user_id
  • title
  • done
  • created_datetime
  • updated_datetime

Creating new users:

  • Only can create a new user with the web app.

Creating new to-dos:

  • Can create to-do with the web app.
  • If the mobile app is online, post the to-do on the web app. If the post was successful, create the to-do with the same successful to-do's id in the mobile app as well.
  • If the mobile app is offline, still create the new to-do, and update/post the new to-do to the web app, whenever it is online again.

I am unsure how to create a new to-do if the mobile is offline and if I don't have any id, to begin with. If I create a dummy ID for the mobile app, it may clash with other to-do ids. Or should I use UUID for all my to-do's id in the mobile app, and then post it to the web app? How does other offline-first app database design work?

2
  • A "web app" usually refers to an application running in a browser. Did you mean a "web service"? Although if the server only communicates with your mobile app, there's no "web" involved at all (unless you implement a HTTP API)
    – Bergi
    Dec 27, 2022 at 18:59
  • 1
    An offline-first design needs many more considerations. What if multiple users share access to a todo list? What about editing and deleting todo items? Have a look at Conflict-free Replicated Data Types for example.
    – Bergi
    Dec 27, 2022 at 19:09

1 Answer 1

5

I am unsure how to create a new to-do if the mobile is offline and if I don't have any id, to begin with. If I create a dummy ID for the mobile app, it may clash with other to-do ids.

These are potential solutions for keying your data:

  1. Use a UUID / GUID: The pros are it's simple and minimizes chance of collision (though doesn't completely eliminate it). The cons are they're not human friendly, and can affect query performance a little bit.

  2. Use a Snowflake ID: The pros are it offers a high level of uniqueness as well, helps minimize collisions, is a bit more human readable, and is more performant of a key than a GUID, when indexed (because they are more sortable). It is also used by large companies like Twitter and Instagram, so it has some backing to it. Cons are it's still a little less human friendly than an integer or natural key.

Snowflakes are 64 bits in binary. (Only 63 are used to fit in a signed integer.) The first 41 bits are a timestamp, representing milliseconds since the chosen epoch. The next 10 bits represent a machine ID, preventing clashes. Twelve more bits represent a per-machine sequence number, to allow creation of multiple snowflakes in the same millisecond. The final number is generally serialized in decimal.

  1. Use a composite primary key that naturally decouples the data from collisions appropriately, such as by user. In your case, your Todos table's primary key could be defined on (id, user_id). Then it wouldn't matter if two Users generated the same id while offline, since your keys are defined and decoupled by user_id as well.

  2. Use a combination of identity columns: One that's the LocalIdentityKey which is an AUTOGENERATED key column in your entity in the SQLite database (and a nullable placeholder integer-based field in PostgreSQL), and the other being the GlobalIdentityKey which is defined as a GENERATED ALWAYS AS IDENTITY key column in your PostgreSQL entity, and is a nullable placeholder integer-based column in your SQLite entity.

The workflow for #4 would follow these steps:

  1. A new instance of an entity is created on the mobile app and saved locally to the SQLite database. Its LocalIdentityKey gets auto-generated.

  2. The app syncs that entity to the remote PostgreSQL database. This either happens immediately if the app is online or is delayed until the next time the app is back online. Upon INSERTing the new entity into the PostgreSQL database, the INSERT should be called with the RETURNING clause, as discussed in Option 3 in this StackOverflow answer. This will allow you to get the newly automatically generated identity value for the GlobalIdentityKey in the PostgreSQL entity.

  3. Update the local SQLite entity instance's placeholder field for GlobalIdentityKey to the returned value from PostgreSQL.

The pros of this methodology are that you use a more standard integer-based key, that is both more human readable and performant from an indexing standpoint, with a guarantee of no possibility for collisions. The cons are it requires a little more work to wire up.


Also, if you go with either solution 1 or 2 above (which has a tiny chance of collisions), it is important that you try/catch the INSERT to handle collisions by re-generating a new key. This new key will need to be updated on the local copy of the entity in addition to when INSERTing that entity remotely to the PostgreSQL database. You may need to retry multiple times (until a unique value is chosen) as it is possible, though rare, for multiple consecutive collisions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.