Why Local Auto-Incrementing IDs Break Your Offline-First App Sync

Using sequential auto-incrementing database IDs on local devices creates a synchronization nightmare when offline apps reconnect. Discover why client-side UUIDs are essential to prevent silent data overwrites.

A rugged tablet on a dusty workshop table displays a failed database synchronization screen with a red warning icon next to safety glasses and tools.
A database sync error on a tablet.

An offline-first mobile application is designed to perform some or all of its core business logic and functionality without requiring an active internet connection. In this architectural pattern, the local data source acts as the canonical source of truth for the application on the device. However, managing data state becomes highly complex when the device is disconnected from the network, particularly when creating new records that must eventually reconcile with a central server.

The Challenge of Distributed Data Sources

In an offline-first setup, the local data source is often backed by a structured relational database, such as Room on Android. Meanwhile, the network data source represents the actual state of the entire application globally. Because the network data source can lag behind the local data source or lead it while a device is offline, any changes made locally must be queued for later transmission. This approach, known as lazy writing, writes data locally first and then attempts to update the network when connectivity returns. Reconnecting and synchronizing these distinct data sources introduces significant conflict resolution challenges.

The Auto-Incrementing ID Collision

A common mistake when designing local databases is relying on auto-incrementing integer fields for primary keys. When multiple devices operate independently offline, each local database starts assigning IDs sequentially, such as 1, 2, and 3, for new records. Because neither device knows what the other is doing, they inevitably generate duplicate IDs for entirely different real-world entities.

Consider a hypothetical field service application used by technicians in remote areas. Technician A goes offline and logs three new equipment repairs. Their local database assigns these repairs the sequential IDs 1, 2, and 3. At the exact same time, Technician B is also offline and logs two repairs, which their local database also assigns IDs 1 and 2. When both technicians regain internet access and attempt to sync their data to the central cloud server, a massive collision occurs. If the server attempts to merge these records using the local IDs, it will either silently overwrite Technician A's first two repairs with Technician B's data, or it will fail entirely, breaking the foreign keys of any associated repair parts.

Solving the Sync Crisis with UUIDs

To avoid these synchronization conflicts, modern offline-first architectures decouple local ID generation from sequential database counters. Instead of integers, systems use universally unique identifiers represented as strings. Local and network data sources often maintain distinct data models to handle this separation. For instance, a local database might define its primary key using a String ID, which is populated on the client side with a unique identifier at the moment of record creation. This ensures that every record generated across any offline device is guaranteed to have a globally unique identifier, allowing seamless merging on the central server without any risk of ID collisions.

  • Avoid sequential integer primary keys in local databases to prevent ID collisions.
  • Use client-generated UUIDs as string primary keys to ensure global uniqueness.
  • Design distinct local and network data models to cleanly manage synchronization states.

Your reaction

Loading reactions…

Comments 0

Be respectful. No links or promotions. Comments appear after review.

Keep reading