> ## Documentation Index > Fetch the complete documentation index at: https://powersync-custom-checkpoints-rollout-docs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Data Pipelines > Use Custom Write Checkpoints to track asynchronous data uploads through chained data pipelines and confirm write completion on the client. **Availability**: Custom Write Checkpoints are available for customers on our [Team and Enterprise](https://www.powersync.com/pricing) plans. The alpha [Checkpoint Requests](/client-sdks/advanced/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](/architecture/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](https://github.com/powersync-ja/powersync-service/blob/main/docs/specs/sync-protocol.md) 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](/configuration/app-backend/client-side-integration) 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: 1. The client makes a change locally and the local database is updated. 2. The client uploads this change to the server. 3. The server resolves the request and writes the change into an intermediate database (not the source database yet). 4. The client thinks the upload is complete (i.e. persisted into the source database). It requests a Write Checkpoint from the PowerSync Service. 5. 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. 6. The PowerSync Service replicates past the previous replication `HEAD` (but the changes are still not present in the source database). 7. 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. 8. Eventually the change is written to the source database, and increments the replication `HEAD`. 9. 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: ## 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: ```TypeScript theme={null} 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](/configuration/source-db/setup), for example: ```SQL theme={null} create publication powersync for table public.lists, public.todos, public.checkpoints; ``` ### Sync Rules Requirements With [storage version 4](/sync/advanced/compatibility#storage-version), 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](/client-sdks/advanced/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: ```yaml theme={null} # 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: ```yaml theme={null} # 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: ```yaml theme={null} # 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. ```TypeScript theme={null} async function uploadData(database: CommonPowerSyncDatabase): Promise { 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... ```SQL theme={null} 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: ```TypeScript theme={null} 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](https://github.com/powersync-ja/powersync-nodejs-backend-todolist-demo/blob/main/src/api/data.js), including examples for [MongoDB](https://github.com/powersync-ja/powersync-nodejs-backend-todolist-demo/blob/main/src/persistance/mongo/mongo-persistance.js) and [MySQL](https://github.com/powersync-ja/powersync-nodejs-backend-todolist-demo/blob/main/src/persistance/mysql/mysql-persistance.js).