halkyon/docs/adr/0001-offline-sync.md

ADR 0001: Private and shared data sync with serverpod_offline_sync

• Status: accepted
• Date: 2026-09-27
• Evidence: a spike on a branch of its own, spike/offline-sync, never merged and not published (its developer keeps it): tests in halkyon_learn_server/test/spike/, run against a live server on Serverpod 4.0.2. What they established is below.
• Amended by: ADR 0002 (2026-09-29): there is no end-to-end encryption, so the premise below that the server syncs data it cannot read no longer holds. The decision and its rules stand. Deleted content is redacted before deletion (ADR 0002, rule 2), and how authorship is secured in shared spaces is decided with shared spaces (ADR 0002, Consequences).
• Amended by: ADR 0003 (2026-09-30): rule 2's revisions have a list of parents instead of parentRevisionId and merge per side (front, back); cards have a fractional position.

Context

Halkyon Learn is offline-first: a learner studies and edits on several devices, offline if need be, and the devices converge. Private and shared studysets are end-to-end encrypted later, so the server must be able to sync data it cannot read. Public studysets are a separate tier (plaintext, published, federated) and are not part of this decision.

serverpod_offline_sync (0.0.8, publisher serverpod.dev, BSD-3-Clause) is a delta-CRDT engine for Serverpod: the same code runs over SQLite on each device and PostgreSQL on the server, models opt in with database: sync, conflicts are resolved per field by hybrid logical clocks (last writer wins), and data is isolated in spaces (a personal one per person, shared ones created on the server).

The alternative was plain endpoints plus a local layer of our own: an ordered change log with a monotonic sequence number and tombstones. It is not a timestamp cursor (updated_at > lastPulledAt), because PostgreSQL's now() is the transaction's start time and a row committed late would be missed for good.

Decision

Use serverpod_offline_sync for private and shared data. Every synced model is database: sync.

What the spike established

Question	Result
Runs on our Serverpod?	Yes, on 4.0.2 with dependency_overrides for serverpod, serverpod_client, serverpod_database, serverpod_serialization; the package pins 4.0.0 exactly, but serverpod_database 4.0.0 and 4.0.2 are identical. A real round trip between two devices works.
References to server-only (public) content	No foreign key possible (rejected at generate time); a plain UUID column syncs unchanged.
Blobs (the content envelope)	Byte-identical up to 5 MB (locally 5 MB: 2.3 s up, 3.8 s down). The 512 KB request limit doesn't apply to sync streams.
Concurrent edits	The same field: the later edit wins, never corrupted. Different fields: both survive, only with column-scoped updates.
Change signal for the UI	onMergeSuccess fires once per merge, never when nothing is new. The engine owns the local SQLite database (no Drift, no reactive queries).
bool fields	Survive update round trips (the bug fixed in 0.0.7).
Persons and spaces	The personal space is keyed on AuthUser.id. Rows forged into another person's space never reach that person. Access to another space is granted, rows never move. Deleting a space removes every row and its change history.
Authentication	Syncs with server-side sessions; without a session, 401.

Rules that follow

1. Updates name their columns (updateRow(..., columns: (t) => [...])). A full-row update rewrites every field, and the later full-row edit then wins every field. Repositories are responsible for this.
2. Content is append-only. Card content is stored as revision rows (CardRevision: cardId, parentRevisionId, authorId, optional reportId, payload), never updated in place. Inserts never conflict, so no edit is lost; two revisions with the same parent are a visible conflict. The review log is append-only too. Plain last-writer-wins fields are only for values where losing one of two concurrent edits doesn't matter (a title).
3. No Drift. Repositories expose Stream<List<T>> by re-querying after their own writes and on onMergeSuccess.
4. Public content is referenced softly: a UUID column without a foreign key, globally qualified by its origin. Public content is never physically deleted, only withdrawn. Public tables use UUID keys.
5. Rows never move between spaces, and the same UUID in two spaces stops sync for good. Copies get new UUIDs; merging two persons means granting the surviving person access to the other person's space. Writes into a shared space name it explicitly (transactionForUser(..., spaceId: ...)).
6. Account deletion and pruning run in a fixed order: revoke all sessions → delete the space → the app wipes its local database. A device with a valid session otherwise re-uploads everything.
7. Order by the UUID v7 id, not by a time field alone: timestamps come back at millisecond precision.
8. Schema rules (enforced at generate time): unique indexes include spaceId and one String, UUID or nullable field; non-optional relations are deferred; 1:1 relations are optional; relations only between synced tables.
9. Sync tests run against a separately started server: client and server engines can't share one process.

Consequences

• Per-field last-writer-wins is acceptable for one person's devices, given rule 2. It is not enough for shared spaces with several editors: those need signed append-only operations on top of the engine (a later ADR).
• Deleted rows stay in their tables, hidden, until the whole space is deleted. Without end-to-end encryption, a deleted card's content therefore remains on the server; the privacy page says so. With encryption, it remains as ciphertext.
• Roles are per space (readOnly, readWrite), not per row; reports and authorship in shared spaces need their own design, including signatures.
• Storage grows by roughly 60–300 % for the change history (the package's own benchmark).

Risks

• Maturity: version 0.0.x, "not yet ready for production use", mostly one maintainer, and database: sync is an experimental Serverpod feature.
• Version lockstep: every Serverpod upgrade waits for the package or needs dependency_overrides.
• Silent rejection: a device whose writes are rejected (e.g. into a space it has no access to) gets no error.
• Not yet tested: continuous sync over long periods, unique-constraint conflicts, delete-versus-update conflicts, thousands of rows.

Way out

If the package stalls or fails one of the untested cases: plain endpoints plus our own local layer (the alternative above). Rule 3 and the gateway/repository architecture keep that change inside the data layer: features depend on repositories, never on the sync engine.