Data Pipelines

Availability: Custom Write Checkpoints are available for customers on our Team and Enterprise plans.

The alpha Checkpoint Requests API uses the term “custom checkpoint requests” for asynchronous upload backends. Client support is currently available for Swift. This page retains the previous “Custom Write Checkpoints” name for the source-side configuration.

To ensure consistency, PowerSync relies on Write Checkpoints. These checkpoints ensure that clients have uploaded their own local changes/mutations to the server before applying downloaded data from the server to the local database. The essential requirement is that the client must get a Write Checkpoint after uploading its last write/mutation. Then, when downloading data from the server, the client checks whether the Write Checkpoint is part of the largest sync checkpoint received from the server (i.e. from the PowerSync Service). If it is, the client applies the server-side state to the local database. The default Write Checkpoints implementation relies on uploads being acknowledged synchronously, i.e. the change persists in the source database (to which PowerSync is connected) before the uploadData call completes. Problems occur if the persistence in the source database happens asynchronously. If the client’s upload is meant to mutate the source database (and eventually does), but this is delayed, it will effectively seem as if the client’s uploaded changes were reverted on the server, and then applied again thereafter. Chained data pipelines are a common example of asynchronous uploads — e.g. data uploads are first written to a different upstream database, or a separate queue for processing, and then finally replicated to the ‘source database’ (to which PowerSync is connected). For example, consider the following data pipeline:

The client makes a change locally and the local database is updated.
The client uploads this change to the server.
The server resolves the request and writes the change into an intermediate database (not the source database yet).
The client thinks the upload is complete (i.e. persisted into the source database). It requests a Write Checkpoint from the PowerSync Service.
The PowerSync Service increments the replication HEAD in the source database, and creates a Write Checkpoint for the client. The Write Checkpoint number is returned and recorded in the client.
The PowerSync Service replicates past the previous replication HEAD (but the changes are still not present in the source database).
It should be fine for the client to apply the state of the server to the local database. But the server state does not include the client’s uploaded changes mentioned in #2. This is the same as if the client’s uploaded changes were rejected (not applied) by the server. This results in the client reverting the changes in its local database.
Eventually the change is written to the source database, and increments the replication HEAD.
The PowerSync Service replicates this change and sends it to the client. The client then reapplies the changes to its local database.

In the above case, the client may see the Write Checkpoint before the data has been replicated. This will cause the client to revert its changes, then apply them again later when it has actually replicated, causing data to “flicker” in the app. For these use cases, Custom Write Checkpoints should be implemented.

Custom Write Checkpoints

Custom Write Checkpoints allow the developer to define Write Checkpoints and insert them into the replication stream directly, instead of relying on the PowerSync Service to create and return them. An example of this is having the backend persist Write Checkpoints to a dedicated table which is processed as part of the replication stream. The PowerSync Service then needs to process the (ordered) replication events and correlate the checkpoint table changes to Write Checkpoint events.

Example Implementation

A self-hosted Node.js demo with Postgres is available here:

Custom Write Checkpoints (Node.js + Postgres)

Implementation Details

This outlines what a Custom Write Checkpoints implementation entails.

Custom Write Checkpoint Table

Create a dedicated checkpoints table, which should contain the following checkpoint payload information in some form:

export type CheckpointPayload = {
    /**
     * The user account id
     */
    user_id: string;
    /**
     * The client id relating to the user account.
     * A single user can have multiple clients.
     * A client is analogous to a device session.
     * Checkpoints are tracked separately for each `user_id` + `client_id`.
     */
    client_id: string;
    /**
     * A strictly increasing Write Checkpoint identifier.
     * This number is generated by the application backend.
     */
    checkpoint: bigint;
}

Replication Requirements

Replication events for the Custom Write Checkpoint table (checkpoints in this example) need to enabled. For Postgres, this involves adding the table to the PowerSync logical replication publication, for example:

create publication powersync for table public.lists, public.todos, public.checkpoints;

Sync Rules Requirements

With storage version 4, each sync configuration can use only one event definition to produce custom checkpoints. Choose the event based on the clients that the configuration supports:

Use checkpoint_requests to support the Checkpoint Requests API. These records are temporary and can expire.
Use write_checkpoints only if every client uses the legacy Custom Write Checkpoints flow. These records are retained because legacy clients do not retry expired checkpoints automatically.

For checkpoint_requests, the is_legacy field is optional. When it is omitted, PowerSync treats the payload as a checkpoint request that can expire. If the configuration supports only Checkpoint Requests, omit it:

# sync-rules.yaml

event_definitions:
  # Note this event is only supported for customers on [Team and Enterprise](https://www.powersync.com/pricing) plans.
  checkpoint_requests:
    payloads:
      # This defines where the replicated custom Write Checkpoints should be extracted from
      - SELECT user_id, checkpoint, client_id FROM checkpoints

For a configuration that supports only legacy clients, use write_checkpoints without an is_legacy field:

# sync-rules.yaml

event_definitions:
  # Note this event is only supported for customers on [Team and Enterprise](https://www.powersync.com/pricing) plans.
  write_checkpoints:
    payloads:
      # This defines where the replicated legacy Custom Write Checkpoints should be extracted from
      - SELECT user_id, checkpoint, client_id FROM checkpoints

Rolling Out Checkpoint Requests

During a rolling rollout, older app versions may continue to create legacy checkpoints while newer versions create checkpoint requests. Configure only the checkpoint_requests event and mark the legacy payloads so the PowerSync Service can apply the correct retention behavior:

Set is_legacy to true for legacy checkpoint records. These records cannot expire because older clients do not retry them automatically.
Omit is_legacy from checkpoint request records. The PowerSync Service can expire and delete these temporary records.

You can store both record types in one table or in separate tables. We recommend separate tables because they keep the different retention behavior explicit. Mark only the legacy table’s payload:

# sync-rules.yaml

event_definitions:
  # Note this event is only supported for customers on [Team and Enterprise](https://www.powersync.com/pricing) plans.
  checkpoint_requests:
    payloads:
      # Legacy Custom Write Checkpoints must be retained
      - SELECT user_id, checkpoint, client_id, true AS is_legacy
        FROM legacy_checkpoints
      # Checkpoint Requests can expire, so is_legacy is omitted
      - SELECT user_id, checkpoint, client_id FROM checkpoint_requests

Application

Your application should handle Custom Write Checkpoints on both the frontend and backend.

Frontend

Your client backend connector should make a call to the application backend to create a Custom Write Checkpoint record after uploading items in the uploadData method. The Write Checkpoint number should be supplied to the CRUD transactions’ complete method.

 async function uploadData(database: CommonPowerSyncDatabase): Promise<void> {
    const transaction = await database.getNextCrudTransaction();
    // Get the unique client ID from the PowerSync Database SQLite storage
    const clientId = await db.getClientId();

      for (const operation of transaction.crud) {
        // Upload the items to application backend
        // ....
      }

      await transaction.complete(await getCheckpoint(clientId));
 }

 async function getCheckpoint(clientId: string): string {
  /**
   * Should perform a request to the application backend which should create the
   * Write Checkpoint record and return the corresponding checkpoint number.
   */
  return "the Write Checkpoint number from the request";
 }

Backend

The backend should create a Write Checkpoint record when the client requests it. The record should automatically increment the Write Checkpoint number for the associated user_id and client_id.

Postgres Example

With the following table defined in the database…

CREATE TABLE checkpoints (
    user_id VARCHAR(255),
    client_id VARCHAR(255),
    checkpoint INTEGER,
    PRIMARY KEY (user_id, client_id)
);

…the backend should have a route which creates checkpoints records:

router.put('/checkpoint', async (req, res) => {
  if (!req.body) {
    res.status(400).send({
      message: 'Invalid body provided'
    });
    return;
  }

  const client = await pool.connect();

// These could be obtained from the session
  const { user_id = 'UserID', client_id = '1' } = req.body;

  const response = await client.query(
    `
    INSERT
      INTO
        checkpoints
          (user_id, client_id, checkpoint)
      VALUES
          ($1, $2, '1')
    ON
      CONFLICT (user_id, client_id)
    DO
      UPDATE
        SET checkpoint = checkpoints.checkpoint + 1
    RETURNING checkpoint;
    `,
    [user_id, client_id]
  );
  client.release();

  // Return the Write Checkpoint number
  res.status(200).send({
    checkpoint: response.rows[0].checkpoint
  });
});

An example implementation can be seen in the Node.js backend demo, including examples for MongoDB and MySQL.

Get Started

Configuration Details

Sync Streams & Rules

Client SDKs

Handling Writes

Maintenance & Ops

Integrations

Tools

Resources

Custom Write Checkpoints

Example Implementation

Custom Write Checkpoints (Node.js + Postgres)

Implementation Details

Custom Write Checkpoint Table

Replication Requirements

Sync Rules Requirements

Rolling Out Checkpoint Requests

Application

Frontend

Backend

Postgres Example

​Custom Write Checkpoints

​Example Implementation

Custom Write Checkpoints (Node.js + Postgres)

​Implementation Details

​Custom Write Checkpoint Table

​Replication Requirements

​Sync Rules Requirements

​Rolling Out Checkpoint Requests

​Application

​Frontend

​Backend

​Postgres Example

Custom Write Checkpoints

Example Implementation

Implementation Details

Custom Write Checkpoint Table

Replication Requirements

Sync Rules Requirements

Rolling Out Checkpoint Requests

Application

Frontend

Backend

Postgres Example