Write Patterns for PowerSync

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

PowerSync provides bidirectional sync between your backend database and client-side SQLite. The read path is handled by Sync Rules and buckets. The write path is handled by an upload queue and a developer-defined uploadData() function. What makes PowerSync’s write path interesting is the flexibility it gives you: you control how mutations reach your backend, which API style you use, and how you resolve conflicts. The PowerSync documentation covers a range of patterns.

In this post, we’ll cover:

• The upload queue - How PowerSync captures and queues local writes
• Backend integration patterns - REST, GraphQL, Supabase, serverless, and more
• Conflict resolution strategies - Seven approaches from last-write-wins to CRDTs
• Error handling - What happens when writes fail
• Write checkpoints - How PowerSync ensures consistency after writes

The upload queue

Every write to the local SQLite database is automatically intercepted by the PowerSync SDK and placed into a persistent FIFO upload queue (stored internally in a ps_crud table). The queue survives app restarts, crashes, and network outages—mutations are never lost.

Three operation types are tracked:

Operation	Meaning	Data included
PUT	Insert or replace a row	All non-null column values
PATCH	Update specific fields	Row ID + only the changed columns
DELETE	Remove a row	Row ID only

The SDK invokes your uploadData() function to process the queue. You have two methods for consuming it:

class MyConnector extends PowerSyncBackendConnector {
  async uploadData(database: AbstractPowerSyncDatabase) {
    // Option A: Process operations in batches (may span transactions)
    const batch = await database.getCrudBatch(100);
    if (!batch) return;

    for (const op of batch.crud) {
      await this.processOperation(op);
    }
    await batch.complete();

    // Option B: Process one transaction at a time
    const tx = await database.getNextCrudTransaction();
    if (!tx) return;

    for (const op of tx.crud) {
      await this.processOperation(op);
    }
    await tx.complete();
  }
}

getCrudBatch(limit) returns up to limit entries, potentially spanning multiple database transactions. getNextCrudTransaction() returns entries from a single transaction, which is useful when your backend needs to apply a group of related changes atomically.

The queue is blocking—operations are processed in strict FIFO order. This matters because later operations may depend on earlier ones (e.g., inserting a row and then updating it). PowerSync retries transient failures automatically at 5-second intervals, preserving the original order.

Backend integration patterns

The uploadData() function is your integration point with the backend. PowerSync doesn’t prescribe how you communicate with your server—it provides the queue, and you decide the transport.

REST API

The most common pattern. Your uploadData() function iterates through CRUD operations and maps them to REST endpoints:

async processOperation(op: CrudEntry) {
  switch (op.op) {
    case "PUT":
      await fetch(`/api/${op.table}`, {
        method: "POST",
        body: JSON.stringify({ id: op.id, ...op.opData }),
      });
      break;
    case "PATCH":
      await fetch(`/api/${op.table}/${op.id}`, {
        method: "PATCH",
        body: JSON.stringify(op.opData),
      });
      break;
    case "DELETE":
      await fetch(`/api/${op.table}/${op.id}`, { method: "DELETE" });
      break;
  }
}

This works with any backend framework—Express, Django, Rails, Laravel, .NET. The endpoints are the same ones your web application might already use.

Supabase

For Supabase backends, the client library speaks directly to Supabase’s PostgREST API:

async processOperation(op: CrudEntry) {
  const table = supabase.from(op.table);

  switch (op.op) {
    case "PUT":
      await table.upsert({ id: op.id, ...op.opData });
      break;
    case "PATCH":
      await table.update(op.opData).eq("id", op.id);
      break;
    case "DELETE":
      await table.delete().eq("id", op.id);
      break;
  }
}

No custom backend server needed. For operations requiring server-side business logic, you can route writes through Supabase Edge Functions instead.

GraphQL

The uploadData() function can make GraphQL mutation calls. This pattern suits backends built around a GraphQL schema:

async processOperation(op: CrudEntry) {
  if (op.table === "todos" && op.op === "PUT") {
    await graphqlClient.mutate({
      mutation: CREATE_TODO,
      variables: { id: op.id, ...op.opData },
    });
  }
  // ... other mutations
}

Batch endpoint

Instead of sending individual operations, you can send the entire batch to a single endpoint and let the server handle routing:

async uploadData(database: AbstractPowerSyncDatabase) {
  const batch = await database.getCrudBatch(100);
  if (!batch) return;

  await fetch("/api/sync/batch", {
    method: "POST",
    body: JSON.stringify({
      operations: batch.crud.map((op) => ({
        table: op.table,
        op: op.op,
        id: op.id,
        data: op.opData,
      })),
    }),
  });

  await batch.complete();
}

This reduces the number of HTTP round trips and lets the server apply all operations in a single database transaction.

Critical constraint: synchronous processing

Regardless of which pattern you use, your backend must process writes synchronously. Do not place writes into a server-side queue for later processing. PowerSync’s write checkpoint system assumes that once the client’s upload succeeds, the data is in the backend database. If the checkpoint doesn’t contain the uploaded changes, the client will remove those changes from the local SQLite database, causing the UI to flash or revert.

Conflict resolution strategies

When two offline clients edit the same data, conflicts are inevitable. PowerSync leaves conflict resolution to your backend, which means you choose the strategy that fits your domain. The documentation covers several approaches.

Last-write-wins (default)

The simplest strategy. Operations are applied in the order they reach the server. If two clients update the same row, the last one to upload wins. This is the default behavior when your backend doesn’t implement any special conflict logic—standard INSERT/UPDATE/DELETE operations overwrite previous state.

For many applications, this is sufficient. If conflicts are rare (most writes target distinct rows) and the consequences of a lost update are low, the simplicity of LWW is worth the occasional overwrite.

Last-write-wins per field

A refinement of LWW that reduces data loss. Instead of overwriting the entire row, you track timestamps per field (e.g., title_modified_at, status_modified_at) and only apply a field if its timestamp is newer than the server’s current value:

UPDATE todos SET
  title = CASE
    WHEN $title_modified_at > title_modified_at THEN $title
    ELSE title
  END,
  title_modified_at = GREATEST($title_modified_at, title_modified_at),
  status = CASE
    WHEN $status_modified_at > status_modified_at THEN $status
    ELSE status
  END,
  status_modified_at = GREATEST($status_modified_at, status_modified_at)
WHERE id = $id;

This means two clients can update different fields on the same row without conflict—client A changes the title while client B changes the status, and both edits are preserved. The cost is additional timestamp columns and per-field comparison logic.

Version number detection

Replace timestamps with an incrementing version integer managed by a database trigger:

-- Trigger to auto-increment version on every update
CREATE OR REPLACE FUNCTION increment_version()
RETURNS TRIGGER AS $$
BEGIN
  NEW.version = OLD.version + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The client submits its last-seen version with each update. The backend compares it to the current version:

const current = await db.query("SELECT version FROM todos WHERE id = $1", [id]);
if (op.opData.version !== current.rows[0].version) {
  // Conflict detected — reject or merge
  throw new ConflictError("Stale version");
}
await db.query("UPDATE todos SET title = $1 WHERE id = $2", [title, id]);

This eliminates clock drift issues that plague timestamp-based approaches. The tradeoff is all-or-nothing—you can’t selectively merge individual fields.

Business rule validation

Domain-specific constraints that don’t depend on timing. Examples: shipped orders become immutable, status must progress in a specific direction (pending → processing → shipped, never backward), inventory quantities can’t go negative. These rules layer naturally on top of other strategies:

if (currentOrder.status === "shipped" && op.op === "PATCH") {
  // Reject — shipped orders are immutable
  return;
}

Delta operations

For counters, inventory quantities, and other accumulative values, treat writes as deltas rather than absolute values. Two concurrent sales each subtract from the current stock rather than overwriting it:

-- Instead of: UPDATE products SET stock = $new_value
-- Use: UPDATE products SET stock = stock + $delta WHERE id = $id
UPDATE products SET stock = stock - $quantity WHERE id = $id AND stock >= $quantity;

This requires a clear protocol distinguishing delta operations from absolute-value operations in your upload queue processing. You can use PowerSync’s custom metadata feature to tag operations:

await db.execute(
  "UPDATE products SET stock = stock - ?, _metadata = ? WHERE id = ?",
  [quantity, JSON.stringify({ type: "delta", field: "stock" }), productId]
);

Server-side conflict recording

When conflicts can’t be auto-resolved, save both versions in a write_conflicts table and sync it back to the client. The user sees a conflict resolution UI and makes the final decision:

INSERT INTO write_conflicts (entity_table, entity_id, client_version, server_version, resolved)
VALUES ('todos', $id, $client_data::jsonb, $server_data::jsonb, false);

This is the right approach for high-stakes data—medical records, legal documents, financial transactions—where silently overwriting data is unacceptable.

CRDTs over PowerSync

PowerSync doesn’t implement CRDTs itself, but you can use CRDT libraries like Yjs for specific data types and sync the CRDT state through PowerSync. The PowerSync documentation describes using Yjs with Tiptap for collaborative rich-text editing, with PowerSync handling the storage and transport of CRDT update blobs. This approach makes sense when you need CRDTs for specific features (collaborative text editing) but want PowerSync’s infrastructure for everything else. For more on CRDTs, see our post on local-first databases.

Error handling

PowerSync classifies write outcomes into four categories, and how your backend responds determines what happens on the client.

Successful application. The server processed the write. The client marks the operation as complete, and the change appears in the next sync.

Transient failure (5xx). The server had a temporary problem—network timeout, database unavailable, rate limiting. The SDK retries automatically at 5-second intervals, preserving FIFO order. Return 5xx only for genuinely transient failures.

Rejection (2xx with error). The server acknowledged the request but rejected the write—validation failure, permission denied, business rule violation. Return 2xx (not 4xx) so the SDK marks the operation as processed and moves on to the next one. Returning 4xx blocks the entire upload queue.

// Backend endpoint example
app.patch("/api/todos/:id", async (req, res) => {
  const todo = await db.findById(req.params.id);
  if (!todo) {
    // Return 200 with error info — don't block the queue
    return res.json({ error: "not_found", message: "Todo does not exist" });
  }
  await db.update(req.params.id, req.body);
  res.json({ success: true });
});

When a rejected write’s operation is completed (marked as processed), the local SQLite state won’t match the server. PowerSync handles this naturally: once the upload queue empties and a write checkpoint is created, the local database converges to match the server state—effectively rolling back the rejected change. There’s no explicit rollback API.

Partial application. The server accepts the write but modifies it (e.g., the server normalizes a value or applies server-side defaults). The client syncs the server’s version on the next sync, overwriting the local optimistic state.

Write checkpoints

PowerSync uses write checkpoints to maintain consistency between client and server after writes. The protocol works like this:

1. The client uploads all pending mutations from the queue via uploadData().
2. Once the queue is empty, the client creates a write checkpoint.
3. The PowerSync service tracks the current position in the CDC stream (the Postgres LSN, MongoDB resume token, or MySQL binlog position).
4. The service notifies the client when replication has caught up to include the uploaded changes.
5. At that point, the client’s local state is guaranteed consistent with the server.

This mechanism is why synchronous backend processing matters. If your backend queues writes for later processing, the write checkpoint may complete before the writes hit the database, and the client will incorrectly remove the local changes.

Custom metadata

PowerSync supports attaching arbitrary metadata to write operations via a _metadata column. This metadata travels with the operation in the upload queue and is accessible in your uploadData() function, but it’s never synced to the server database.

// Enable metadata tracking in the schema
const schema = new Schema({
  todos: new Table(
    {
      title: column.text,
      completed: column.integer,
    },
    { trackMetadata: true }
  ),
});

// Attach metadata when writing
await db.execute(
  "UPDATE todos SET completed = 1, _metadata = ? WHERE id = ?",
  [JSON.stringify({ source: "mobile", offline: true, userId: currentUser.id }), todoId]
);

// Access metadata in uploadData()
async uploadData(database) {
  const tx = await database.getNextCrudTransaction();
  for (const op of tx.crud) {
    console.log(op.metadata); // { source: "mobile", offline: true, userId: "..." }
    // Route or process differently based on metadata
  }
}

Common uses include tracking the originating device, passing user context for authorization, tagging operations for special handling, and implementing source-based conflict resolution.

Wrapping up

PowerSync’s write path gives you the infrastructure—a persistent upload queue, automatic retries, FIFO ordering, and write checkpoints—while leaving the integration and conflict resolution logic to you. This means more work upfront than a fully opinionated sync engine, but it also means you’re not locked into a specific backend, API style, or conflict resolution strategy.

For most applications, start with last-write-wins and add complexity only where your domain demands it. Field-level LWW covers a surprising number of use cases. Business rule validation layers naturally on top. Server-side conflict recording is there for the cases where automatic resolution isn’t appropriate.

For the full picture on PowerSync’s architecture, see our post on PowerSync as an offline-first sync engine. To see how other sync engines handle writes, check out our posts on write patterns for ElectricSQL and write strategies for Replicache. For a comparison of all three tools, see ElectricSQL vs. PowerSync vs. Replicache.