Data Pipelines

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

PowerSync uses write checkpoints to keep client data consistent. After the client uploads its local writes, it obtains a checkpoint that marks the source database position after those writes. The client applies downloaded data only once the sync checkpoint includes that write checkpoint. This is why your write endpoint must be synchronous: the default checkpoint marks the source database position when uploadData() returns, so the uploaded changes must already be in the source database at that moment. Some backends cannot process uploads synchronously. In a chained data pipeline, uploads first go to a queue or an intermediate database and reach the source database later. With the default checkpoints, this makes client data flicker:

The client uploads a change. Your backend accepts it and queues it, and uploadData() returns.
The client obtains a write checkpoint. The PowerSync Service marks the current source database position, which does not include the queued change.
The client receives that checkpoint and applies the server state. The change is missing, so the client reverts it locally.
The pipeline writes the change to the source database. The Service syncs it, and the client applies it again.

Custom checkpoints solve this. Instead of the Service marking the source database position when the client asks, your backend writes a checkpoint record into a table in the source database at the end of the pipeline. The record replicates to the Service through the same replication stream as your data, so the checkpoint always follows the uploaded changes. An event definition in your Sync Config tells the Service how to read the record.

Choosing a Flow

PowerSync supports two custom checkpoint flows. Use custom checkpoint requests for new implementations.

	Custom checkpoint requests	Legacy Custom Write Checkpoints
Checkpoint ID	Generated by the PowerSync Client SDK	Generated by your backend
Client integration	Implement `postCheckpointRequest()` on your connector. The SDK calls it after each upload.	Pass the checkpoint number to `transaction.complete()` in `uploadData()`.
Event definition	`checkpoint_requests`	`write_checkpoints`
Records in the Service	Expire after a retention period	Retained
Status	Alpha. Requires PowerSync Service 1.24.0 or later.	Stable

Custom checkpoint requests are part of the Checkpoint Requests API. See that page for the supported client SDKs. The legacy flow continues to work and is documented in Legacy Custom Write Checkpoints. From PowerSync Service 1.26.0, a Sync Config can define only one of the two events. If you have app versions in production that use the legacy flow, see Migrating to Custom Checkpoint Requests.

Setting Up Custom Checkpoint Requests

With custom checkpoint requests, the PowerSync Client SDK generates an increasing checkpoint request ID and sends it to your backend after each upload. Your backend writes the ID into a checkpoints table in the source database. When the Service replicates the record, the client knows that its uploads are in the source database.

Create a Checkpoints Table

Create a table in your source database that stores the latest checkpoint request ID for each PowerSync client:

CREATE TABLE checkpoints (
  user_id TEXT NOT NULL,
  client_id TEXT NOT NULL,
  checkpoint BIGINT NOT NULL,
  PRIMARY KEY (user_id, client_id)
);

user_id is the authenticated user.
client_id is the PowerSync client ID. Each local database has its own client ID, so one user can have many clients.
checkpoint is the checkpoint request ID. IDs are 64-bit integers, so use a BIGINT or equivalent column.

Column names can differ. The event definition in the next steps maps your columns to these fields.

Replicate the Table

For Postgres, add the table to the PowerSync publication:

CREATE PUBLICATION powersync FOR TABLE lists, todos, checkpoints;

For other source databases, the Service replicates every table that your Sync Config references, including tables in event definitions. Complete the same table setup as for your other tables, such as enabling CDC for a SQL Server table.

Add the Event Definition

Add a checkpoint_requests event definition to your Sync Config. Its payload query must return the fields user_id, client_id, and checkpoint:

config:
  edition: 3

event_definitions:
  checkpoint_requests:
    payloads:
      - SELECT user_id, client_id, checkpoint FROM checkpoints

streams:
  todos:
    query: SELECT * FROM todos WHERE owner_id = auth.user_id()

Use aliases if your column names differ, for example SELECT owner AS user_id, device AS client_id, request_id AS checkpoint FROM checkpoints.

Add a Backend Endpoint

Add an endpoint that receives the client ID and checkpoint request ID from the client. Take the user ID from your session or token. The endpoint must:

Store the greater of the submitted ID and the stored ID for that user and client.
Return that value. If the submitted ID was stale, the client uses the returned ID to continue counting from there.

Write the record through the same pipeline as the uploads, so that it reaches the source database after the changes it confirms. If your backend writes the checkpoint record directly while the uploads are still queued, the client sees the checkpoint before its changes and reverts them.For Postgres, one statement handles both new and existing rows:

INSERT INTO checkpoints (user_id, client_id, checkpoint)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, client_id) DO UPDATE
  SET checkpoint = GREATEST(checkpoints.checkpoint, EXCLUDED.checkpoint)
RETURNING checkpoint;

Return the value as a string in JSON to avoid precision loss in JavaScript clients. See Checkpoint Request IDs for the full reconciliation rules.

Update the Client

Connect with checkpoint requests enabled and add postCheckpointRequest() to your backend connector to call your endpoint. The SDK calls this method after each upload, so uploadData() needs no checkpoint handling of its own. See Prerequisites and Connector Changes for how to declare the method in each SDK.

Record Retention

The Service keeps a replicated checkpoint request for checkpoint_request_retention_minutes after it stores the record. The default is 60 minutes. The next compact job then removes it. Clients send their current request ID again when they reconnect, so an expired record is recreated when a client still needs it. On self-hosted instances, you can change the period with api.parameters.checkpoint_request_retention_minutes. Retention applies only to the Service’s copy. Rows in your checkpoints table are yours to keep or delete. While a row exists, return its value from your endpoint so that a reconnecting client can resume from it.

Legacy Custom Write Checkpoints

In the legacy flow, your backend generates an increasing checkpoint number for each client, and the client passes that number to transaction.complete() after each upload. The Service retains these records because legacy clients wait for a specific number and do not request it again.

Create and Replicate a Checkpoints Table

Use the same table and replication setup as for custom checkpoint requests.

Add the Event Definition

Add a write_checkpoints event definition to your Sync Config:

config:
  edition: 3

event_definitions:
  write_checkpoints:
    payloads:
      - SELECT user_id, client_id, checkpoint FROM checkpoints

streams:
  todos:
    query: SELECT * FROM todos WHERE owner_id = auth.user_id()

Add a Backend Endpoint

Add an endpoint that increments and returns the checkpoint number for the user and client. Write the record through the same pipeline as the uploads. For Postgres:

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;

Complete Transactions With the Checkpoint

In uploadData(), request a checkpoint from your backend after uploading the transaction and pass it to complete():

async function uploadData(database: CommonPowerSyncDatabase): Promise<void> {
  const transaction = await database.getNextCrudTransaction();
  if (!transaction) {
    return;
  }

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

  const clientId = await database.getClientId();
  const checkpoint = await requestWriteCheckpoint(clientId);
  await transaction.complete(checkpoint);
}

async function requestWriteCheckpoint(clientId: string): Promise<string> {
  // Call your backend endpoint. It creates the checkpoint record
  // and returns the new checkpoint number as a string.
}

Migrating to Custom Checkpoint Requests

While you roll out an updated app version, older versions that still use the legacy flow write legacy checkpoint numbers while updated versions write checkpoint request IDs. Because a Sync Config cannot define both write_checkpoints and checkpoint_requests, support both kinds of records by defining only checkpoint_requests and adding an is_legacy field to the payload. The field is available since Service version 1.26.0.

Set is_legacy to true for legacy records. The Service retains them.
Omit is_legacy, or set it to false, for checkpoint request records. The Service can expire them.

Keep both record types in separate tables where possible. Separate tables make the difference in retention visible in the Sync Config:

event_definitions:
  checkpoint_requests:
    payloads:
      # Legacy checkpoints must be retained
      - SELECT user_id, client_id, checkpoint, true AS is_legacy FROM legacy_checkpoints
      # Checkpoint requests can expire
      - SELECT user_id, client_id, checkpoint FROM checkpoint_requests

If both flows write to one table, distinguish the rows with a column. For example, your checkpoint request endpoint can set a checkpoint_requested_at timestamp that the legacy endpoint leaves NULL:

event_definitions:
  checkpoint_requests:
    payloads:
      - SELECT user_id, client_id, checkpoint, checkpoint_requested_at IS NULL AS is_legacy FROM checkpoints

Once no clients use the legacy flow, remove the legacy payload or the is_legacy field. With storage version 4, changing an event definition reprocesses only that event’s data.

Example Implementations

Swift custom checkpoint demo: a client that uses custom checkpoint requests with the Node.js backend demo.
Node.js backend demo: implements both a checkpoint request endpoint and a legacy checkpoint endpoint, with Postgres, MongoDB, and MySQL persistence.
Self-hosted custom checkpoints demo: a Docker Compose setup with Postgres that uses the legacy flow.

Get Started

Configuration Details

Sync Streams

Client SDKs

Handling Writes

Maintenance & Ops

Integrations

Tools

Resources

Choosing a Flow

Setting Up Custom Checkpoint Requests

Record Retention

Legacy Custom Write Checkpoints

Migrating to Custom Checkpoint Requests

Example Implementations

​Choosing a Flow

​Setting Up Custom Checkpoint Requests

​Record Retention

​Legacy Custom Write Checkpoints

​Migrating to Custom Checkpoint Requests

​Example Implementations

Choosing a Flow

Setting Up Custom Checkpoint Requests

Record Retention

Legacy Custom Write Checkpoints

Migrating to Custom Checkpoint Requests

Example Implementations