> ## Documentation Index
> Fetch the complete documentation index at: https://powersync-update-diagrams.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Pipelines

> Use custom checkpoints to keep client data stable when your backend applies uploads to the source database asynchronously.

<Note>
  **Availability**: Custom checkpoints are available for customers on our [Team and Enterprise](https://www.powersync.com/pricing) plans.
</Note>

PowerSync uses write checkpoints to keep client data [consistent](/architecture/consistency). 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](/handling-writes/writing-client-changes): 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:

1. The client uploads a change. Your backend accepts it and queues it, and `uploadData()` returns.
2. The client obtains a write checkpoint. The PowerSync Service marks the current source database position, which does not include the queued change.
3. The client receives that checkpoint and applies the server state. The change is missing, so the client reverts it locally.
4. 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](/client-sdks/advanced/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](#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](#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.

<Steps>
  <Step title="Create a Checkpoints Table">
    Create a table in your source database that stores the latest checkpoint request ID for each PowerSync client:

    ```sql theme={null}
    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.
  </Step>

  <Step title="Replicate the Table">
    For Postgres, add the table to the PowerSync [publication](/configuration/source-db/setup):

    ```sql theme={null}
    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](/configuration/source-db/setup) as for your other tables, such as enabling CDC for a SQL Server table.
  </Step>

  <Step title="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`:

    ```yaml theme={null}
    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`.
  </Step>

  <Step title="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:

    1. Store the greater of the submitted ID and the stored ID for that user and client.
    2. 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:

    ```sql theme={null}
    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](/client-sdks/advanced/checkpoint-requests#checkpoint-request-ids) for the full reconciliation rules.
  </Step>

  <Step title="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](/client-sdks/advanced/checkpoint-requests#prerequisites) and [Connector Changes](/client-sdks/advanced/checkpoint-requests#connector-changes) for how to declare the method in each SDK.
  </Step>
</Steps>

### 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`](/configuration/powersync-service/self-hosted-instances#param-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.

<Steps>
  <Step title="Create and Replicate a Checkpoints Table">
    Use the same table and replication setup as for [custom checkpoint requests](#setting-up-custom-checkpoint-requests).
  </Step>

  <Step title="Add the Event Definition">
    Add a `write_checkpoints` event definition to your Sync Config:

    ```yaml theme={null}
    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()
    ```
  </Step>

  <Step title="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:

    ```sql theme={null}
    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;
    ```
  </Step>

  <Step title="Complete Transactions With the Checkpoint">
    In `uploadData()`, request a checkpoint from your backend after uploading the transaction and pass it to `complete()`:

    ```typescript theme={null}
    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.
    }
    ```
  </Step>
</Steps>

## 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:

```yaml theme={null}
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`:

```yaml theme={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](/sync/advanced/storage-version-4#incremental-reprocessing), changing an event definition reprocesses only that event's data.

## Example Implementations

* [Swift custom checkpoint demo](https://github.com/powersync-ja/powersync-swift/tree/main/Demos/CustomCheckpointDemo): a client that uses custom checkpoint requests with the Node.js backend demo.
* [Node.js backend demo](https://github.com/powersync-ja/powersync-nodejs-backend-todolist-demo): implements both a checkpoint request endpoint and a legacy checkpoint endpoint, with Postgres, MongoDB, and MySQL persistence.
* [Self-hosted custom checkpoints demo](https://github.com/powersync-ja/self-host-demo/tree/main/demos/nodejs-custom-checkpoints): a Docker Compose setup with Postgres that uses the legacy flow.