PowerSync is an open-source devtool that syncs between your own backend database and in-app SQLite databases on your users' devices. It guarantees Causal+ Consistency while allowing partial replication and offline work, handling the tricky parts of network retries, browser storage, and cross-tab sync. This makes it a great tool for offline-capable collaborative apps where you want to remain in control of your backend.
The PowerSync Service specifically syncs rows, filtered by sync rules that you define. This works well for CRUD operations, but what about a Google Docs-style collaborative text document, which allows fine-grained edits that don't translate well to row-level changes?
After meeting at Local-First Conf, Kobie and Conrad from the PowerSync team reached out to me about tackling this problem. In particular, they asked if I could implement my talk Collaborative Text Editing without CRDTs or OT on top of PowerSync - a great opportunity to turn that idea into a working prototype!
This blog post describes the resulting demo of collaborative text editing over PowerSync. The demo uses Tiptap as the editor, has basic support for shared cursors and anonymous share links, and includes a "Sync Active" toggle that you can use to try out concurrent edits.
If you're in a hurry, you can skip to the demo repo, or check out the live version.
Approach
The basic idea of the demo is that, instead of trying to "fit" collaborative text into a database table, we merely record the log of all updates to the text. So, when a user inserts some characters, formats a range of text, etc., their client inserts a row containing that update (as arbitrary JSON) into their local PowerSync table.
Eventually, those rows reach the backend database - Supabase in the demo - which assigns them an auto-incrementing %%server_version%%. That establishes a canonical total order on updates. All clients compute the current text state by processing the updates in %%server_version%% order.
What about local updates that haven't yet reached the backend, including updates that you performed offline or in another tab? Those are still in the local PowerSync table but have NULL %%server_version%%. We apply those after all of the backend updates, in the order that they were inserted into the local PowerSync table. That gives us the expected "optimistic" state: the state that the text document will be in once the backend receives our updates, assuming no collaborators make edits before then.
Text Updates
The hard part is knowing what to put in the updates' JSON. Naively, we could use literal text updates such as %%{ type: "insert", index: 17, char: "X" }%%. However, this update breaks if, before it reaches the backend, someone else performs the update %%{ type: "delete", index: 10 }%%: that changes the index of our desired insertion point (17 -> 17 - 1 = 16).
To solve this, we describe insertion points (and character positions generally) using unique per-character IDs instead of indexes. For example, if the user types "X" following a character "W" with ID "abc123", we send an update like %%{ type: "insert", afterId: "abc123", char: "X", newId: "def456" }%%.
Our actual updates are ProseMirror steps but with the ProseMirror positions (indexes) replaced by per-character IDs like above. These IDs are generated and stored by the Articulated library that I made for my Local-First Conf talk.
Vs A CRDT?
Instead of ProseMirror steps + character IDs, we could have gotten our updates from a Conflict-free Replicated Data Type (CRDT) for rich text, storing each CRDT operation as a row. In fact, PowerSync already has a Yjs demo that works this way. The CRDT axioms then guarantee that we can apply operations in any order or even multiple times and still get a consistent result across users.
Why does this prototype take a different, non-CRDT approach? In brief: PowerSync's architecture makes it possible to obtain a consistent total order on updates, with no duplicates. Thus the CRDT rules are not necessary: you're allowed to choose arbitrary (deterministic) updates and you'll still get a consistent result across users.
Bring-your-own updates have some nice properties:
- Transparency: You can "see" what the updates are doing, since they are defined by your own application logic instead of an external library's CRDT internals.
- Flexibility: You control how updates are generated and applied, so you can tweak how they interact with conflicting updates, or generalize them to add new functionality - without first becoming a CRDT expert. (E.g., ignore letters inserted inside words that were just deleted.)
- Authority: Your updates can enforce invariants on the output state, and your backend can rewrite or reject updates if desired for e.g. fine-grained authorization rules.
Implementation Notes
Querying the Update Log
Relevant code: server_reconciliation.ts
Recall that to compute the current text state, each client applies all updates in the order: updates from the backend ordered by %%server_version%%, followed by (optimistic) local updates in the order that they were inserted into the local PowerSync table.
We can query the local insertion order using SQLite's rowid column. Thus we can obtain the complete update log using a PowerSync query like:
SELECT "update" FROM text_updates
WHERE doc_id=?
ORDER BY server_version NULLS LAST, rowid;Because of how PowerSync works, %%text_updates%% is actually a view instead of a table in the local SQLite database (though it's a literal table in the backend database). To get at the %%rowid%%, we need to query the underlying table, %%ps_data__text_updates%%. Luckily, PowerSync's watched queries allow this; we just need to do some extra work (normally handled by the view) to extract columns from the table's %%data%% JSON blob:
SELECT
CAST(json_extract(data, '$.doc_id') as TEXT) AS doc_id,
CAST(json_extract(data, '$.update') as TEXT) AS "update",
CAST(json_extract(data, '$.server_version') as INTEGER) AS server_version,
rowid
FROM ps_data__text_updates
WHERE doc_id=?
ORDER BY server_version NULLS LAST, rowid;Incremental Updates
It would be inefficient to re-run the above query every time the update log changes. Instead, we want to update the current state incrementally, applying new updates as they arrive.
A recent PowerSync feature, High Performance Diffs, makes this possible. After running the above query once to get the initial state of the log, we use %%powerSync.triggers.trackTableDiff%% to watch for newly inserted rows and rows that change %%server_version%% (when they are acknowleged by the backend). That lets us update the text state without doing %%O(# total updates)%% effort.
One wrinkle: due to concurrency, the new/changed rows are not necessarily last in the log. In particular, because the demo uses Priority 0 sync, new updates from the backend might appear "underneath" optimistic local updates. We handle this using Server Reconcliation, a generic strategy for collaboration over a central server.
Batched Uploads
Relevant code: SupabaseConnector.ts
Another important optimization is batched uploads. Each keystroke generates a new update, hence a new database row, which PowerSync asks us to upload to our backend (here Supabase). If we upload these rows one at a time - calling await on each call to the Supabase API - then they'll upload pretty slowly, potentially more slowly than you can type.
Instead, the demo uses the pre-sorted batch strategy described in PowerSync's docs. This uploads new rows to Supabase in batches, avoiding a separate network round-trip per keystroke.
Conclusion
The demo is a prototype, so expect some rough edges. For example, PowerSync tolerates inserting a new database row per keystroke, but it would be wise to optimize this:
- Throttle writing to PowerSync so that each row describes a few characters, if the user is typing rapidly.
- Periodically "compact" the updates, replacing them with the resulting document state.
We're also interested in polishing (& thoroughly testing) the reusable pieces of the demo - shared presence over PowerSync, a generalized server reconciliation hook, and rebaseable ProseMirror steps - before making them available as separate tools.
I personally have learned a lot about PowerSync (& ProseMirror steps) from working on this project, and I'm grateful for the opportunity to make a collaborative text demo that has proper reconnection & offline support - all handled by PowerSync - instead of my usual basic WebSocket servers :)
You can find the demo code at https://github.com/mweidner037/powersync-text-editing.