- put a local database on the device,
- write changes to an outbox,
- give every write an idempotency key,
- pull deltas from the server,
- mark deletes with tombstones,
- settle conflicts with last-write-wins.
Local-First Database
This is a database on the device that the app reads and writes to instead of an API. Having it means that the UI never waits for a server round trip and keeps working offline. Everything else in the architecture depends on this database.Typical Implementation
Pick an embedded database (usually SQLite, or IndexedDB for web runtimes) and send every read and write in the app to it. There is a common shortcut which is to call the API and fall back to a local cache only if the request fails. This results in two code paths to maintain. The offline one only runs when the network is down, so you would have to make sure it’s thoroughly tested before it’s needed. Add reactivity too: the UI has to update when data changes underneath it, whether from the user or from sync.With PowerSync
Your app gets a SQLite database that is managed by the PowerSync client SDK, so you don’t need to build or manage the local database. Your app always reads from and writes to this database, whether it’s online or offline, so there’s only one code path. You still need to define the client-side schema for the tables you sync, connect the live queries to your UI, and write data with ordinary SQL (e.g.db.execute()). PowerSync persists each write locally and queues it for upload.
Outbox Pattern
The “outbox” is a durable queue of pending writes. It is how writes survive the device going offline: Instead of calling an API directly for persistence, the app writes each change locally and appends it to the outbox. A background process drains the outbox to the server and removes each entry once the server confirms it.Typical Implementation
Create an outbox table next to your data and write to it in the same local transaction as the change it records, so that you never have a change without an outbox entry or vice versa. Then add a worker that drains it in FIFO order:- Only one worker runs at a time
- Decide which errors are permanent and drop those or send them to a dead-letter queue
- Don’t let the next sync overwrite local changes that haven’t been uploaded yet.
With PowerSync
You don’t need to build the outbox. PowerSync’s upload queue is the outbox. EveryINSERT, UPDATE and DELETE on the local database is recorded as a PUT, PATCH or DELETE operation in the same transaction, in a blocking FIFO queue. The SDK drains it, retries on failure, and only removes entries once your code confirms they have been handled.
While the queue has mutations in it, the client doesn’t advance to a new server checkpoint, so newer server state never overwrites local mutations that haven’t been uploaded yet.
You still need to write the function that sends mutations to your backend for processing:
Optimistic UI (Read-Your-Writes)
To get the full UX benefit of this architecture, you must show the result of a user’s action immediately, before the server has confirmed it. In a to-do list app, the user taps “complete” and the item is ticked straight away, instead of waiting while watching a spinner (or worse, a frozen UI). While offline, there is no server to wait for, so optimistic UI is the only option. A related property is read-your-writes: once you’ve written something to the local database, every later read shows it, including after a sync.Typical Implementation
Apply the change to local state first, record it in the outbox, and roll it back if the server rejects it. To build a consistent UI that isn’t confusing to users, you need to make sure to:- Read from the same local store everywhere, so every screen shows the pending change, not just the screen where it was made.
- Prevent an incoming sync from overwriting a pending change with older data. Otherwise data will flip back and forth while the state stabilizes.
- Decide what the user sees when the server rejects a write: revert silently or show a failure they can retry.
- Show pending state where it’s important (e.g. “not yet synced” on a payment), so users know what the server hasn’t confirmed.
With PowerSync
You don’t need to maintain the state or merge it with server data. Writes go straight into the local SQLite database, and live queries update the UI instantly on any changes in the SQLite database. While writes are waiting in the upload queue, the client doesn’t advance to a newer checkpoint, so pending changes are never overwritten by stale server data. Once the server has processed the writes, the client advances to the new state. If your backend rejects or changes a write, the synced result replaces the local change. You still need to decide how your backend handles a rejected write, and how the app notifies the user, since a silent revert can be confusing. To show sync state in the UI, use the sync status for a connection indicator, or the upload queue count (getUploadQueueStats() in the JavaScript SDK) to show how many writes are waiting to upload. To show a per-record state, like “this payment hasn’t been confirmed yet”, you will need to manage your own status column.
Idempotency Key
This is a unique ID attached to a write, which allows the server to recognize a repeat or retry. If the same request arrives twice, the server applies it only once and returns the same result to both requests. By design, the outbox retries on failures, and the retry can’t tell the difference between “the request failed” and “the request succeeded but the response was dropped”. Without an idempotency key, a bad connection will result in the app creating duplicate entries.Typical Implementation
Generate IDs on the device (UUIDs) so that retrying aCREATE will target the same row. Give each outbox entry a unique ID. On the server, either make the operation naturally idempotent, or record the IDs you’ve processed and check them in the same transaction as the write:
With PowerSync
You don’t need to generate separate idempotency keys on the client. Rows are created on the device with an ID ‘primary key’, so a replayedPUT targets the same row and doesn’t create a new one.
The upload queue retries failed uploads automatically, so you still need to design your backend to handle the same operation more than once. PowerSync sends row-level PUT, PATCH and DELETE operations, so most of them are naturally idempotent and need no explicit dedupe table in your server write implementation:
PUT brings it back. If deletes should be final in your domain, check for a deleted ID before you insert (see deletion tables).
Writes that aren’t naturally idempotent, like “charge the card” or “increment stock”, still need the processed_ops check above, keyed on a business key, such as the UUID of the payment row.
Delta Sync (Incremental Pull)
This is a way for the client to download only the changes since its last sync, not everything. Full refetches don’t scale: they are slow and they burn power and data on every sync.Typical Implementation
Add anupdated_at column to every table, keep it current with a trigger, and add an endpoint that returns rows changed since the client’s cursor:
- Use a commit-ordered position (e.g. the Postgres LSN, or
xminwith snapshot checks). Normal timestamps and serial IDs are both assigned before commit, so a long transaction can commit with a value older than the cursor a client has already moved past. If that happens, those rows will never be sent. - Record deletes so they can be sent too. A deleted row isn’t returned by the query (see tombstones below).
- Track who can see what, and tell the device to drop rows when a user loses access (e.g. removed from a team).
- Pull related tables together, so the client never shows e.g. an order without its line items.
With PowerSync
You don’t need to build delta sync. The PowerSync Service reads your database’s replication stream (e.g. Postgres logical replication), so it sees every committed change, including deletes. Clients receive only changes since their last checkpoint. The client applies a checkpoint only once it has all of the operations for that checkpoint, so it never has . When a user’s access changes, rows they should no longer have are automatically removed from the device. You still need to define what data each user syncs with Sync Streams.Tombstones (Soft Deletes)
With cursor-based delta sync, clients never find out that a deleted row is gone. A tombstone is a marker that says “this row was deleted”. It is kept so clients that were offline can learn about the deletion.Typical Implementation
ReplaceDELETE with UPDATE … SET deleted_at = now() (which also bumps updated_at, so it shows up in the delta). Then:
- every query in the app and the API gets
WHERE deleted_at IS NULL - unique constraints have to ignore deleted rows
- the client deletes its local copy when it sees a tombstone
- a cleanup job purges old tombstones, but only once every client has seen them
- any device offline for longer than your retention window is forced to do a full resync
deleted_rows (table_name, row_id, deleted_at). Your main tables, their queries and unique constraints stay unchanged, but you still need the client handling, the cleanup job and the retention window.
With PowerSync
You don’t need tombstones for synced data. Because PowerSync reads deletes from the replication stream, you can use ordinary hard deletes in your source database. The PowerSync Service turns them into remove operations for each affected client, and deletes made on the device go through the upload queue asDELETE operations. PowerSync keeps track of this internally, so no deleted_at column is needed for sync.
One exception: if a late or replayed PUT must not bring back a deleted row (see idempotency key), your backend still needs to keep a record of deleted IDs.
You may still need soft deletes for product reasons, like undo, a trash folder, or audit history. But now that’s a business decision, not a sync requirement.
Last-Write-Wins (LWW)
This is a conflict rule: when two devices change the same data while out of touch, the change that arrives last wins. Offline means concurrent edits, so you need some rule, and LWW is the simplest one.Typical Implementation
LWW sounds trivial, but you have to answer a few questions first:- Whose clock decides “last”? Use the server’s receive time or a hybrid logical clock. Don’t use device clocks: they drift and the user can change them.
- Whole rows or single fields? Use per-field LWW, so one user changing the title doesn’t wipe out another user’s status change. That means the client sends only the fields that changed.
- Deletes against edits. If one device deletes a row while another edits it, decide which wins, and make sure a late edit doesn’t bring the row back.
- The local side. When the client merges server data into local data, apply the same rules, so the two never disagree.
With PowerSync
You don’t need to worry about clocks or resolve conflicts on the client. Writes go through your backend, which is the single authority, so “last” means last received by the server. With a simple backend, the result is per-field LWW, becausePATCH operations only carry the columns that changed. The client waits until the server has acknowledged its writes, then takes the server’s state.
You will still need to write the backend code to handle conflict resolution. You need to customize that where your domain needs it: reject updates to a completed order, say, or use custom conflict resolution for more complex use cases.
For true merging of concurrent edits (usually on larger text fields), see CRDTs below.
CRDTs (Conflict-Free Replicated Data Types)
With CRDTs, concurrent edits always merge to the same result, whatever order they arrive in. Where last-write-wins throws one edit away, a CRDT keeps both. That matters for collaborative text, where two people typing in the same paragraph offline should both keep their words.Typical Implementation
Use a library like Yjs or Automerge. You still need to:- Store the document state and updates locally, so edits survive restarts and work offline.
- Send updates to other clients and the server, and receive theirs (the library merges them).
- Compact the update history from time to time, since it grows with every keystroke.
- Decide on authorization. A CRDT will merge any valid update, so the server has to check who is allowed to send one.
With PowerSync
You don’t need a separate sync channel for CRDT data. PowerSync can be the provider for CRDT data: store Yjs updates as rows in a table, and PowerSync handles local storage, offline writes, upload and sync to other clients, alongside the rest of your data. See the Yjs collaboration demo. You need to wire the library to PowerSync: write updates to the table, apply incoming rows to the document, and compact old updates on the backend.Exactly-Once Delivery
This is the guarantee that each write is applied exactly once: never lost, never duplicated. It’s what everyone wants and what networks don’t inherently provide.Typical Implementation
You can’t get it from the network directly. In practice, “exactly once” means two things combined:- at-least-once delivery: a durable outbox that keeps retrying until the server confirms
- deduplication: an idempotent server that applies a repeated write only once
With PowerSync
You don’t need to build at-least-once delivery or ordering. The upload queue never drops a write, and keeps it until youruploadData() marks it complete. The queue is FIFO, and each client transaction is uploaded as a unit.
You need to make your backend idempotent (see above), and the result is effectively-once.
Event Sourcing
This is a way of storing every change as an immutable event in an append-only log, and deriving current state by replaying the events. An event log is a natural way to sync: devices append events, exchange them, and replay. It also gives you audit history for free. Event sourcing brings its own costs: replay, snapshots, schema evolution of old events, and projection rebuilds. Take those on only if the domain calls for it.Typical Implementation
Create an events table on the client and the server. Devices append events locally and upload them through an outbox. The server orders them and sends them back down, and each client replays them through reducers (“projections”) to rebuild its tables. Plan from the start for snapshots so replay stays fast, for changing the format of old events, and for rebuilding projections when the reducer logic changes.With PowerSync
You don’t need event sourcing to implement sync. PowerSync syncs ordinary database tables/collections, and you write normal SQL on both ends (server and client). Your business domain may still require or recommend an append-only table. For example, ledgers and stock movements are the classic use case for append-only operations. In the case of stock, two devices recording “+3” and “−1” must both count. In this case, PowerSync syncs the append-only table and you derive totals with SQL. See Cumulative Operations.Putting It Together
Here’s a summary of the terms on this page:
None of these are particularly complicated to build yourself as individual parts, but together they create complexity, a maintenance burden, and potential subtle edge cases and bugs which could result in data loss or corruption.
To try PowerSync, start with our Setup Guide.