serverpod-offline-sync/docs/one-pager.md

Serverpod Offline Sync — Product One-Pager

This is a one-pager that describes the product and its features, plus a roadmap for the transference process to Serverpod's monorepo.

Overview

Serverpod Offline Sync is a Dart plugin that turns an ordinary Serverpod backend into an offline-first, collaborative system, without changing how developers write their app. It is a delta-CRDT (Conflict-free Replicated Data Type) engine that runs the same code on the client and server databases, transparently and with minimal configuration.

It sits nearly alone in the space of offline-first solutions, because most other solutions either use a non-relational database, require a new data API, or don't preserve foreign-key/unique invariants while merging data. The solution is built on top of the Serverpod framework, leveraging its ORM, server and client to expose such complex capabilities with minimal configuration.

The work on this package was inspired by the excellent work on Synql (Ignat et al., DAIS 2024), which provided the foundation for the CRDT layer. This package goes beyond Synql by covering the full set of foreign-key onDelete actions, extending unique-constraint handling to deeply relational schemas, and shipping a production-ready implementation.

The developer experience

The main selling point of the package is that there is almost nothing to learn. The app is built as a normal offline Serverpod app (generated models, ordinary CRUD) and the plugin handles replication automatically.

The three steps below contain all required changes on a project to use the package:

1. Prepare the synced tables by using database: sync keyword on the models.

   class: Person
   table: person
   database: sync
   fields:
     name: String
   

2. Configure the sync engine on the client.

   final session = await client.createSyncSession(
     databasePath,
     isDebugMode: kDebugMode,
     persistentUserId: persistentUserId,
   );
   

3. Wire the sync call on the client.

   // Push local changes and merge remote ones once (i.e. pull-to-refresh).
   await client.offlineSync.syncOnce(session);
   
   // Or stream changes both ways until cancelled for a near-real-time sync.
   final syncSession = client.offlineSync.syncContinuously(session);
   

After this setup, the app can be developed using normal repository calls against the session instance.

The sync engine is a black box that "just works": changes are tracked atomically with each operation and the developer never intervenes in conflict resolution. Conflicts that need a human decision bubble up as visible state for the end user of the app to act on (e.g. a delete reverted by a concurrent constraint reappears, so the user can simply delete it again). Migrations work normally, and the same applies to deleted rows — which never leave their tables, but are transparently hidden from the user.

Understanding the behavior is also straightforward: conflict resolution respects unique and relational constraints, mirroring what would be expected if the merged data existed in a single database at the time of each operation.

Finally, the data is isolated per space: each user has their own implicit personal space, and can be a member of any number of shared spaces - with read-only or read-write access. On the server, database operations need to be wrapped in a special session.db.transactionForUser method to target the correct space. On the client, it is possible to set a persistentUserId to target the personal space by default, or use the transactionForUser to target a shared space.

Data modeling limitations

Due to the nature of merge conflicts, the package imposes some limitations on the data modeling that are enforced at runtime during the initialization. Most of these limitations are fundamental to the design of the package and can never be lifted.

• Every synced table must:
  • Have a UUID primary key.
  • Declare the field spaceId: int?, relation(parent=offline_sync_spaces, onDelete=Cascade).
• Unique indexes must include spaceId together with the target columns.
• Global unique indexes are unsupported, except for FK-only indexes.
• Unique indexes are only supported with at least one String/UuidValue/nullable column.
• All 1:1 relations must have the foreign-key column nullable (optional relation).
• The only allowed non-synced-to-synced relation is spaceId -> offline_sync_spaces.id.
• Foreign-key relations that foreign-key projection cannot repair — non-nullable columns and composite relations — must be deferred.
• onDelete=Restrict is unsupported on synced tables; use onDelete=NoAction.

Respecting these limitations, all other database invariants, including foreign-key actions, are preserved - with the exception of check constraints, which are also not supported by Serverpod.

The end-user experience

The bundled desktop demo shows the model directly: two independent replicas of one account side by side, plus the server's merged truth. Drive each replica's sync independently and watch them converge under all common usage scenarios. It visualizes hidden / tombstoned rows and every foreign-key onDelete action, with guided scenarios for the restrict / cascade / set-null / set-default / unique-conflict cases.

Performance

One common concern about offline-first solutions is the performance impact of the CRDT layer. Since all operations are tracked, it can weigh heavily on both storage and speed. This package was created with this concern in mind, achieving a minimal performance penalty.

Below is one of the last performance reports of the package (available at each pull request as a comment). It uses 1,000 rows to measure the performance against a baseline of the same operations without the CRDT layer, both on SQLite:

📊 SELECT performance impact:
  Time: 12.62 ms --> 12.67 ms (48.50 μs)
  CRDT overhead: 0.38% slower

📊 INSERT performance impact:
  Time: 251.01 ms --> 482.05 ms (231.04 ms)
  CRDT overhead: 92.04% slower
  Delay per insert: 231.04 μs

📊 UPDATE performance impact:
  Time: 249.57 ms --> 2.13 s (1.88 s)
  CRDT overhead: 753.54% slower
  Delay per update: 1.88 ms

📊 DELETE performance impact:
  Time: 11.53 ms --> 420.28 ms (408.75 ms)
  CRDT overhead: 3,546.27% slower
  Delay per delete: 408.75 μs

🔭 SELECT space impact (100 extra users, 100,000 CRDT noise rows):
  find all (1,000 rows): 9.91 ms --> 10.91 ms space-scoped (+10.07%) / 11.19 ms unscoped (+12.96%)
  findById (per lookup): 295.04 μs --> 375.24 μs space-scoped (+27.19%) / 348.88 μs unscoped (+18.25%)

💽 Storage impact (base footprint removed):
  Base database size: 540.00 KB --> 540.00 KB
  INSERT only:
    Net storage: 116.00 KB --> 188.00 KB (+72.00 KB)
    CRDT overhead: 62.07% increase
    Extra storage per inserted row: +73.73 B
  INSERT + UPDATE:
    Net storage: 128.00 KB --> 472.00 KB (+344.00 KB)
    CRDT overhead: 268.75% increase
    Extra storage per updated column: +34.82 B
  INSERT + UPDATE + DELETE:
    Net storage: 128.00 KB --> 512.00 KB (+384.00 KB)
    CRDT overhead: 300.00% increase
    Extra storage per deleted row: +40.96 B
  Storage overhead range: 62.07% --> 300.00%

Despite the high percentage of slowdown in the insert/update/delete operations, the absolute impact is mostly in the microsecond range, making it barely noticeable in most cases - and way below a network delay if those operations were to be performed through an online endpoint.

The storage impact is also minimal, reaching at most 3x the base storage size, which is perfectly acceptable for the benefits of offline-first speed and availability.

A report similar to the one above is generated per pull request as a comment on the CI to track the performance evolution of the package.

Technical fundamentals

Architecture. The engine is a transparent layer between the application and its database. Its models and database implementation live in the shared package, while the client package only binds the generated module caller to the shared sync transport. Because identical code runs on the client's SQLite and the server's Postgres, there are no asymmetric merge results to reconcile. Migrations are also generated symmetrically for both ends, which makes the migration process result in the exact same data on both ends. The entire engine is built respecting the three mathematical properties that must hold for a CRDT to be consistent: idempotence, associativity, and commutativity.

Metadata model. Three metadata tables track change history without copying row data: crdt_data_rows (insert + visibility), crdt_data_fields (update), and crdt_data_tombstone (delete/restore). HLCs are used to order field-level values and break ties. A monotone causal-length tombstone governs row existence, so add/delete/restore advance generations forward and can never oscillate. All metadata is normalized, with normalized IDs cached, to keep both the storage and performance impact minimal.

Visibility as a pure function. Replicas never coordinate. Each merge applies remote facts monotonically, then derives the visible database — tombstone state, unique-conflict winners, and foreign-key repair — as a deterministic projection computed to a fixed point. This is the architecture introduced by Synql (Ignat et al., DAIS 2024), extended here to the full set of ON DELETE actions and to unique constraints over deeply relational schemas.

Ownership & spaces. A space is the unit of replication and ownership; a user is an authentication identity. Each row is globally identified by (table, rowId) and owned by exactly one space (spaceId), so merging is always one chain per row. Sharing is a membership layer in front of spaces, not a change to row ownership. Spaces can be purged for GDPR / account erasure via a database-enforced cascade. Ownership collisions (the same (table, rowId) claimed by two spaces, which is mostly a malicious attack vector) fail the sync and are recorded in a durable offline_sync_integrity_violations table.

Sync protocol. A session handshakes once to validate both ends have the same table schema and ensure that a merge will reach the same result. Then, per cycle the server sends the authoritative space set and all spaces that the user has access to are synced (data is transferred all at once, but merged one at a time). The data is transferred in chunks to avoid pressuring the bandwidth with too many small messages. This same stream-based implementation is used for both "one-shot" and "continuous" sync modes, so a one-shot sync with large amounts of data will be as performant as a continuous sync. It also means that all tests hold for both sync modes with minimal duplication.

Testing

The package is tested extensively following the Serverpod testing principles. The vast majority of the tests live in the serverpod_offline_sync_test_server package, which is a Serverpod project that is used to test the package. Its server is also shared with the example/offline_sync_demo project, which is a real-world application that uses the package.

Roadmap

The package is functionally in a production-ready state, but some APIs need to improve the UX and it must naturally pass through a review process to be merged into the Serverpod monorepo. Before the review, some minor technical debts must be solved.

Technical debts

• Rename the space name for data isolation to space. This is a small refactor to avoid confusion with the space keyword used in the Serverpod Auth modules. Using space is also clearer for usage on an application level.

Post-integration UX improvements

The package already requires minimal configuration from the developer, but it can be even smoother if the serverpod_cli is made aware of the offline sync capabilities.

• Add a fourth sync option for the database keyword to be used on models. Models that use it will automatically receive the spaceId relation and be included in a generated list of synced tables on the server and client. This feature will enable all improvements below.
• Simplify the Serverpod initialization by generating the class. This is mapped on Serverpod's issue #4544 and will allow the CRDT database interceptor to be injected on the Serverpod constructor without the user having to call it manually whenever a sync model exists.
• Move the restrictions that are runtime-enforced to a static analysis step. The package enforces the limitations of the CRDT layer at runtime (during the initialization), but a better UX would be to apply the restrictions to sync models during static analysis of generate.
• Inject the serverpod_offline_sync packages on the pubspec.yaml file. This would remove the last manual step from the developer to make the solution work. Otherwise, the user will have to manually add the dependencies to their pubspec.yaml to generate the project.

If these changes are made, all a user will have to do is to add the sync keyword to the database keyword on synced table models and the package will be completely wired up.

Given that all these changes are on the serverpod_cli, it doesn't even require other internal packages to depend on the serverpod_offline_sync packages.

Review strategy

Besides not being that big (~35k LOC for the packages, ~14k LOC for the tests), the codebase is dense on the logic. So the best way to review it is to read the performance reports first, then all tests, and finally the main files of the packages as needed.

The design docs can also be used as a reference to understand the architecture and the implementation, as they were kept in sync throughout the development process.

Transference process

The license transfer and transference process will be done following the steps below:

1. A branch transferring the copyrights to the Serverpod organization will be merged into the main branch of the original repository.
2. The original repository with the updated license will be made public on GitHub by the author. This copy will be kept for historical purposes and as a portfolio of the author's work.
3. The package will be reviewed by the Serverpod team on the original repository to prepare the codebase for the transference.
4. After all changes originated from the review are merged into the main branch, a PR will be created to merge the packages on the Serverpod monorepo, preserving its commit history.
5. With the PR merged on Serverpod's monorepo, the original repository will be archived with a note about the new location of the package.