Skip to content

API.md

Latest commit

 

History

History
949 lines (613 loc) · 46.2 KB

API.md

File metadata and controls

949 lines (613 loc) · 46.2 KB
Raw

API Reference

This document provides a reference for the SQL functions provided by the sqlite-sync extension. Unless noted otherwise, the APIs are available on both SQLite and PostgreSQL builds.

Index

Configuration Functions

cloudsync_set(key, value)

Description: Stores a global CloudSync setting in the current database. Settings persist across database reopens and are loaded automatically by the extension.

The following payload setting is supported:

Key Description Default Minimum Maximum
payload_max_chunk_size Maximum transport payload size generated by cloudsync_payload_chunks(). Values outside the range are clamped. 5242880 (5 MB) 262144 (256 KB) 33554432 (32 MB)

payload_max_chunk_size affects only chunk generation. cloudsync_payload_apply() continues to accept legacy payloads, monolithic payloads, and v3 chunk-fragment payloads even when they are larger than the local setting. This preserves compatibility between peers using different settings.

Parameters:

  • key (TEXT): The setting key.
  • value (TEXT): The setting value. For payload_max_chunk_size, pass the value in bytes.

Returns: SQLite returns no value. PostgreSQL returns true on success.

Example:

-- Use 1 MB transport chunks
SELECT cloudsync_set('payload_max_chunk_size', '1048576');

-- Restore the default 5 MB transport chunks
SELECT cloudsync_set('payload_max_chunk_size', '5242880');

cloudsync_init(table_name, [crdt_algo], [init_flags])

Description: Initializes a table for sqlite-sync synchronization. This function is idempotent and needs to be called only once per table on each site; configurations are stored in the database and automatically loaded with the extension.

Before initialization, cloudsync_init performs schema sanity checks to ensure compatibility with CRDT requirements and best practices. These checks include:

  • Primary keys should not be auto-incrementing integers; GUIDs (UUIDs, ULIDs) are highly recommended to prevent multi-node collisions.
  • All non-primary key NOT NULL columns must have a DEFAULT value.
  • Note: Any write operation that includes a NULL value for a primary key column will be rejected with an error, even if SQLite would normally allow it due to a legacy behavior.

Schema Design Considerations:

When designing your database schema for SQLite Sync, follow these essential requirements:

  • Primary Keys: Use TEXT primary keys with cloudsync_uuid() for globally unique identifiers. Avoid auto-incrementing integers.
  • Column Constraints: All NOT NULL columns (except primary keys) must have DEFAULT values to prevent synchronization errors.
  • UNIQUE Constraints: In multi-tenant scenarios, use composite UNIQUE constraints (e.g., UNIQUE(tenant_id, email)) instead of global uniqueness.
  • Foreign Key Compatibility: Be aware of potential conflicts during CRDT merge operations and RLS policy interactions.
  • Trigger Compatibility: Triggers may cause duplicate operations or be called multiple times due to column-by-column processing.

For comprehensive guidelines, see the Database Schema Recommendations.

The function supports three overloads:

  • cloudsync_init(table_name): Uses the default 'cls' CRDT algorithm.
  • cloudsync_init(table_name, crdt_algo): Specifies a CRDT algorithm ('cls', 'dws', 'aws', 'gos').
  • cloudsync_init(table_name, crdt_algo, init_flags): Specifies an algorithm and a bitmask of initialization flags to control which schema sanity checks are skipped.

Parameters:

  • table_name (TEXT): The name of the table to initialize.
  • crdt_algo (TEXT, optional): The CRDT algorithm to use. Can be "cls", "dws", "aws", "gos". Defaults to "cls".
  • init_flags (INTEGER, optional): A bitmask of flags that control initialization behavior. Defaults to 0 (no flags). Available flags:
    • 0 — No flags; all sanity checks are performed (default).
    • 1 (CLOUDSYNC_INIT_FLAG_SKIP_INT_PK_CHECK) — Skip the check that prevents the use of a single-column INTEGER primary key. Use with caution; globally unique primary keys (UUID/ULID) are strongly recommended.
    • 2 (CLOUDSYNC_INIT_FLAG_SKIP_NOT_NULL_DEFAULT_CHECK) — Skip the check that requires all NOT NULL non-PK columns to have a DEFAULT value.
    • 4 (CLOUDSYNC_INIT_FLAG_SKIP_NOT_NULL_PRIKEYS_CHECK) — Skip the check that rejects NULL primary key values.
    • Flags can be combined with bitwise OR (e.g., 3 skips both the integer PK check and the NOT NULL default check).

Returns: None.

Example:

-- Initialize a table with the default CLS algorithm
SELECT cloudsync_init('my_table');

-- Initialize a table with the Delete-Wins Set algorithm
SELECT cloudsync_init('my_table', 'dws');

-- Initialize a table with an integer primary key (skip the integer PK check)
SELECT cloudsync_init('my_table', 'cls', 1);

cloudsync_enable(table_name)

Description: Enables synchronization for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to enable.

Returns: None.

Example:

SELECT cloudsync_enable('my_table');

cloudsync_disable(table_name)

Description: Disables synchronization for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to disable.

Returns: None.

Example:

SELECT cloudsync_disable('my_table');

cloudsync_is_enabled(table_name)

Description: Checks if synchronization is enabled for the specified table.

Parameters:

  • table_name (TEXT): The name of the table to check.

Returns: 1 if enabled, 0 otherwise.

Example:

SELECT cloudsync_is_enabled('my_table');

cloudsync_set_filter(table_name, filter_expr)

Description: Sets a row-level filter expression on a synchronized table. Only rows that match the filter are tracked by the sync triggers; changes to rows that do not satisfy the expression are ignored and never replicated.

The filter expression is a standard SQL boolean expression written using bare column names (without a table or alias prefix). The extension automatically rewrites it with NEW. for INSERT/UPDATE triggers and OLD. for DELETE triggers. The expression is evaluated inside the trigger WHEN clause.

This function stores the filter in the table's settings and immediately recreates the sync triggers to apply it. The filter persists across database reopens. Use cloudsync_clear_filter() to remove it.

Parameters:

  • table_name (TEXT): The name of the synchronized table.
  • filter_expr (TEXT): A SQL boolean expression referencing column names of the table. Only rows for which this expression evaluates to true are tracked for sync.

Returns: 1 on success.

Example:

-- Only sync tasks that are not marked as drafts
SELECT cloudsync_set_filter('tasks', "is_draft = 0");

-- Only sync rows belonging to a specific tenant
SELECT cloudsync_set_filter('orders', "tenant_id = 'acme'");

-- Combine conditions
SELECT cloudsync_set_filter('messages', "deleted = 0 AND type != 'ephemeral'");

cloudsync_clear_filter(table_name)

Description: Removes the row-level filter previously set with cloudsync_set_filter(). After clearing, all row changes in the table are tracked and replicated regardless of column values.

This function updates the stored settings and immediately recreates the sync triggers without a filter condition.

Parameters:

  • table_name (TEXT): The name of the synchronized table.

Returns: 1 on success.

Example:

SELECT cloudsync_clear_filter('tasks');

cloudsync_cleanup(table_name)

Description: Removes the sqlite-sync synchronization mechanism from a specified table or all tables. This operation drops the associated _cloudsync metadata table and removes triggers from the target table(s). Use this function when synchronization is no longer desired for a table.

Parameters:

  • table_name (TEXT): The name of the table to clean up.

Returns: None.

Example:

-- Clean up a single table
SELECT cloudsync_cleanup('my_table');

cloudsync_terminate()

Description: Releases all internal resources used by the sqlite-sync extension for the current database connection. This function should be called before closing the database connection to ensure that all prepared statements and allocated memory are freed. Failing to call this function can result in memory leaks or a failed sqlite3_close operation due to pending statements.

Parameters: None.

Returns: None.

Example:

-- Before closing the database connection
SELECT cloudsync_terminate();

Block-Level LWW Functions

cloudsync_set_column(table_name, col_name, key, value)

Description: Configures per-column settings for a synchronized table. This function is primarily used to enable block-level LWW on text columns, allowing fine-grained conflict resolution at the line (or paragraph) level instead of the entire cell.

When block-level LWW is enabled on a column, INSERT and UPDATE operations automatically split the text into blocks using a delimiter (default: newline \n) and track each block independently. During sync, changes are merged block-by-block, so concurrent edits to different parts of the same text are preserved.

Parameters:

  • table_name (TEXT): The name of the synchronized table.
  • col_name (TEXT): The name of the text column to configure.
  • key (TEXT): The setting key. Supported keys:
    • 'algo' — Set the column algorithm. Use value 'block' to enable block-level LWW.
    • 'delimiter' — Set the block delimiter string. Only applies to columns with block-level LWW enabled.
  • value (TEXT): The setting value.

Returns: None.

Example:

-- Enable block-level LWW on a column (splits text by newline by default)
SELECT cloudsync_set_column('notes', 'body', 'algo', 'block');

-- Set a custom delimiter (e.g., double newline for paragraph-level tracking)
SELECT cloudsync_set_column('notes', 'body', 'delimiter', '

');

cloudsync_text_materialize(table_name, col_name, pk_values...)

Description: Reconstructs the full text of a block-level LWW column from its individual blocks and writes the result back to the base table column. This is useful after a merge operation to ensure the column contains the up-to-date materialized text.

After a sync/merge, the column is updated automatically. This function is primarily useful for manual materialization or debugging.

Parameters:

  • table_name (TEXT): The name of the table.
  • col_name (TEXT): The name of the block-level LWW column.
  • pk_values... (variadic): The primary key values identifying the row. For composite primary keys, pass each key value as a separate argument in declaration order.

Returns: 1 on success.

Example:

-- Materialize the body column for a specific row
SELECT cloudsync_text_materialize('notes', 'body', 'note-001');

-- With a composite primary key (e.g., PRIMARY KEY (tenant_id, doc_id))
SELECT cloudsync_text_materialize('docs', 'body', 'tenant-1', 'doc-001');

-- Read the materialized text
SELECT body FROM notes WHERE id = 'note-001';

Helper Functions

cloudsync_version()

Description: Returns the version of the sqlite-sync library.

Parameters: None.

Returns: The library version as a string.

Example:

SELECT cloudsync_version();
-- e.g., '1.0.0'

cloudsync_siteid()

Description: Returns the unique ID of the local site.

Parameters: None.

Returns: The site ID as a BLOB.

Example:

SELECT cloudsync_siteid();

cloudsync_db_version()

Description: Returns the current database version.

Parameters: None.

Returns: The database version as an INTEGER.

Example:

SELECT cloudsync_db_version();

cloudsync_uuid()

Description: Generates a new universally unique identifier (UUIDv7). This is useful for creating globally unique primary keys for new records, which is a best practice for CRDTs.

Parameters: None.

Returns: A new UUID as a TEXT value.

Example:

INSERT INTO products (id, name) VALUES (cloudsync_uuid(), 'New Product');

cloudsync_uuid_text(uuid, [dash_format])

Description: Converts a 16-byte binary UUID (such as the site_id stored in cloudsync_changes, or the value returned by cloudsync_siteid()) into its canonical string form.

Parameters:

  • uuid (BLOB/BYTEA): The 16-byte UUID. Returns NULL if uuid is NULL; raises an error if it is not exactly 16 bytes.
  • dash_format (BOOLEAN, optional, default true): When true, returns the canonical 36-character dashed form (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx); when false, returns the bare 32-character hex form.

Returns: The UUID as a TEXT value (lowercase hex).

Example:

SELECT cloudsync_uuid_text(cloudsync_siteid());          -- 0190a1b2-c3d4-7e5f-8a9b-001122334455
SELECT cloudsync_uuid_text(cloudsync_siteid(), false);   -- 0190a1b2c3d47e5f8a9b001122334455

cloudsync_uuid_blob(uuid)

Description: Converts a UUID string into its 16-byte binary form. This is the inverse of cloudsync_uuid_text() and lets string-based callers (for example, an HTTP /check endpoint holding a stringified site_id) pass a site_id to cloudsync_payload_chunks().

Parameters:

  • uuid (TEXT): A UUID string. Tolerant: accepts the canonical dashed form and the bare 32-hex form, case-insensitive. Returns NULL if uuid is NULL; raises an error on malformed input.

Returns: The 16-byte UUID as a BLOB/BYTEA.

Example:

SELECT cloudsync_uuid_blob('0190a1b2-c3d4-7e5f-8a9b-001122334455');
SELECT cloudsync_uuid_blob('0190A1B2C3D47E5F8A9B001122334455');

Schema Alteration Functions

cloudsync_begin_alter(table_name)

Description: Prepares a synchronized table for schema changes. This function must be called before altering the table. Failure to use cloudsync_begin_alter and cloudsync_commit_alter can lead to synchronization errors and data divergence.

Parameters:

  • table_name (TEXT): The name of the table that will be altered.

Returns: None.

Example:

SELECT cloudsync_init('my_table');
-- ... later
SELECT cloudsync_begin_alter('my_table');
ALTER TABLE my_table ADD COLUMN new_column TEXT;
SELECT cloudsync_commit_alter('my_table');

cloudsync_commit_alter(table_name)

Description: Finalizes schema changes for a synchronized table. This function must be called after altering the table's schema, completing the process initiated by cloudsync_begin_alter and ensuring CRDT data consistency.

Parameters:

  • table_name (TEXT): The name of the table that was altered.

Returns: None.

Example:

SELECT cloudsync_init('my_table');
-- ... later
SELECT cloudsync_begin_alter('my_type');
ALTER TABLE my_table ADD COLUMN new_column TEXT;
SELECT cloudsync_commit_alter('my_table');

Payload Functions

cloudsync_payload_encode(tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq)

Description: Encodes rows from cloudsync_changes into a single monolithic payload. This is the legacy payload API and remains fully supported for backward compatibility.

Use this API when the expected payload size is modest or when you need to interoperate with callers that expect a single BLOB. For large rowsets or large individual BLOB/TEXT values, prefer cloudsync_payload_chunks(), which splits transport payloads according to payload_max_chunk_size.

Parameters: The function is an aggregate over the columns returned by cloudsync_changes:

  • tbl (TEXT): Source table name.
  • pk (BLOB): Encoded primary key.
  • col_name (TEXT): Changed column name.
  • col_value (BLOB): Encoded column value.
  • col_version (INTEGER/BIGINT): Column version.
  • db_version (INTEGER/BIGINT): Source database version.
  • site_id (BLOB): Source site identifier.
  • cl (INTEGER/BIGINT): Causal length.
  • seq (INTEGER/BIGINT): Sequence number within the source database version.

Returns: A single payload BLOB.

Example:

SELECT cloudsync_payload_encode(
  tbl, pk, col_name, col_value, col_version, db_version, site_id, cl, seq
) AS payload
FROM cloudsync_changes;

cloudsync_payload_blob_checked(since_db_version, since_seq, filter_site_id, exclude_filter_site_id, max_estimated_payload_size)

Description: Generates one monolithic payload BLOB for a selected change window, but only after an internal conservative size check passes.

This helper is intended for servers that still need to support old clients on a /check path that expects one monolithic payload. It keeps the server to a single SQL round trip while avoiding payload allocation when the selected window is too large.

Internally, CloudSync first estimates the uncompressed payload body plus header. If that estimate exceeds max_estimated_payload_size, the function raises a limit-exceeded error and does not materialize the payload. Empty windows return NULL.

When the check passes, this function scans the selected change window twice: once to estimate and once to encode. This avoids unsafe monolithic allocation, but successful calls are more I/O-expensive than a direct single-pass cloudsync_payload_encode() over the same rows.

Parameters:

  • since_db_version (INTEGER/BIGINT): Start after this source database version, except rows at the same version with seq > since_seq are still included.
  • since_seq (INTEGER/BIGINT): Sequence cursor within since_db_version.
  • filter_site_id (BLOB): Site ID to filter on. With exclude_filter_site_id unset/false it selects changes from this site; with exclude_filter_site_id true it selects changes from every site except this one. If NULL and not excluding, CloudSync uses the local site ID.
  • exclude_filter_site_id (BOOLEAN): When true, encode changes from all sites except filter_site_id. Setting it true without a filter_site_id is an error.
  • max_estimated_payload_size (INTEGER/BIGINT): Maximum allowed conservative payload-size estimate, in bytes. Must be positive.

Returns: A monolithic payload BLOB, or NULL when no changes match.

Examples:

-- /check download guard: all changes EXCEPT the requesting peer's site
SELECT cloudsync_payload_blob_checked(
  100,
  0,
  cloudsync_uuid_blob('0190a1b2-c3d4-7e5f-8a9b-001122334455'),
  true,
  10485760
);

cloudsync_payload_chunks([since_db_version], [filter_site_id], [until_db_version], [exclude_filter_site_id])

Description: Generates sync payloads as a stream of transport-sized chunks. It is the chunk-aware evolution of cloudsync_payload_encode(), designed for large rowsets and for single BLOB/TEXT values that are larger than the configured chunk size.

The maximum generated chunk size is controlled by the global payload_max_chunk_size setting. The default is 5 MB, the technical minimum is 256 KB, and the technical maximum is 32 MB (values outside this range are clamped):

SELECT cloudsync_set('payload_max_chunk_size', '5242880');

When a single encoded column value does not fit in one chunk, CloudSync transparently emits v3 payload fragments for that value. The receiver stages fragments internally and applies the value when all parts arrive. Fragments can arrive out of order; incomplete stale fragment groups are cleaned up automatically.

cloudsync_payload_chunks() does not change the apply contract: cloudsync_payload_apply() accepts legacy payloads, monolithic payloads, and v3 chunk-fragment payloads. The local payload_max_chunk_size setting is not used to reject incoming payloads.

Important memory note: chunking limits the size of each transport payload that CloudSync generates. It does not remove the database engine's need to materialize a single final cell value when applying a very large BLOB/TEXT column. In other words, a 500 MB BLOB can be transported in smaller chunks, but the receiving database must still be able to store and bind the completed 500 MB value when that row is applied.

Parameters:

  • since_db_version (INTEGER/BIGINT, optional): Start after this source database version. If omitted, CloudSync uses the stored send checkpoint.
  • filter_site_id (BLOB, optional): Site ID to filter on. With exclude_filter_site_id unset/false it selects changes from this site; with exclude_filter_site_id true it selects changes from every site except this one. If omitted (and not excluding), CloudSync uses the local site ID.
  • until_db_version (INTEGER/BIGINT, optional): Upper watermark to include. If omitted or 0, CloudSync captures the current maximum source database version before streaming chunks.
  • exclude_filter_site_id (BOOLEAN, optional, default false): When true, stream changes from all sites except filter_site_id. This is what the /check download path needs — a peer must not receive its own changes back. Setting it true without a filter_site_id is an error. The site_id stored in cloudsync_changes is the 16-byte binary UUID; string callers can convert with cloudsync_uuid_blob().

Returns: A rowset with one row per chunk:

Column Description
payload Payload BLOB to pass to cloudsync_payload_apply().
chunk_index Zero-based chunk index for this stream.
payload_size Payload size in bytes.
rows Number of encoded payload rows in this chunk. Fragment chunks usually contain one fragment row.
db_version_min Minimum source db_version represented by this chunk.
db_version_max Maximum source db_version represented by this chunk.
watermark_db_version Stable upper watermark captured for this chunk stream. Store this after all chunks are durably transferred/applied.

SQLite usage: cloudsync_payload_chunks is exposed as a virtual table with hidden constraint columns:

-- Default: uses the stored send checkpoint and local site id
SELECT payload, chunk_index, payload_size, watermark_db_version
FROM cloudsync_payload_chunks
ORDER BY chunk_index;

-- Explicit arguments through hidden columns
SELECT payload, chunk_index, payload_size, watermark_db_version
FROM cloudsync_payload_chunks
WHERE since_db_version = 100
  AND site_id = cloudsync_siteid()
  AND until_db_version = 200
ORDER BY chunk_index;

-- /check download: all changes EXCEPT the requesting peer's site
SELECT payload, chunk_index, watermark_db_version
FROM cloudsync_payload_chunks
WHERE since_db_version = 100
  AND site_id = cloudsync_uuid_blob('0190a1b2-c3d4-7e5f-8a9b-001122334455')
  AND exclude_filter_site_id = 1
ORDER BY chunk_index;

PostgreSQL usage: cloudsync_payload_chunks is exposed as a set-returning function with optional arguments:

-- Default: uses the stored send checkpoint and local site id
SELECT *
FROM cloudsync_payload_chunks();

-- Explicit arguments
SELECT *
FROM cloudsync_payload_chunks(100, cloudsync_siteid(), 200);

-- /check download: all changes EXCEPT the requesting peer's site
SELECT *
FROM cloudsync_payload_chunks(100, cloudsync_uuid_blob('0190a1b2-c3d4-7e5f-8a9b-001122334455'), NULL, true);

Apply example:

-- Apply chunks on a receiving peer. Chunks may be applied one at a time.
SELECT cloudsync_payload_apply(?);

On PostgreSQL, apply chunks as individual statements from the transport/client layer. Do not use a set-based statement such as SELECT cloudsync_payload_apply(payload) FROM chunks_table; while reading payloads from a table in the same database session. cloudsync_payload_apply() performs writes through SPI, and applying while the same statement is still scanning a payload table can conflict with PostgreSQL executor resource ownership. Fetch each payload into the client (or into a local procedural variable after the read completes) and then call cloudsync_payload_apply() for that single payload.

cloudsync_payload_apply(payload)

Description: Applies a sync payload to the current database. The function accepts all supported payload formats:

When a v3 fragment payload is received, CloudSync stores the fragment in an internal table and returns after applying zero or more completed values. Once the final fragment for a value is received, the completed value is validated and applied. Duplicate fragment delivery is idempotent.

Parameters:

  • payload (BLOB/BYTEA): Payload BLOB to apply.

Returns: Number of payload rows applied. Fragment payloads that are staged but not yet complete can return 0.

Example:

SELECT cloudsync_payload_apply(:payload);

Network Functions

cloudsync_network_init(managedDatabaseId)

Description: Initializes the sqlite-sync network component. This function configures the endpoints for the CloudSync service and initializes the cURL library.

Parameters:

  • managedDatabaseId (TEXT): The managed database identifier returned by the CloudSync service when a new database is registered for sync. For SQLiteCloud projects, this value can be obtained from the project's OffSync page on the dashboard.

Returns: None.

Example:

SELECT cloudsync_network_init('your-managed-database-id');

cloudsync_network_cleanup()

Description: Cleans up the sqlite-sync network component, releasing all resources allocated by cloudsync_network_init (memory, cURL handles).

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_cleanup();

cloudsync_network_set_token(token)

Description: Sets the authentication token to be used for network requests. This token will be included in the Authorization header of all subsequent requests. For more information, refer to the Access Tokens documentation.

Parameters:

  • token (TEXT): The authentication token.

Returns: None.

Example:

SELECT cloudsync_network_set_token('your_auth_token');

cloudsync_network_set_apikey(apikey)

Description: Sets the API key for network requests. This key is included in the Authorization header of all subsequent requests.

Parameters:

  • apikey (TEXT): The API key.

Returns: None.

Example:

SELECT cloudsync_network_set_apikey('your_api_key');

Error handling

The sync functions follow a consistent error-handling contract:

Error type Behavior
Endpoint/network errors (server unreachable, auth failure, bad URL) SQL error — the function could not execute.
Apply errors (cloudsync_payload_apply failures — unknown schema hash, invalid checksum, decompression error) Structured JSON — a receive.error string field is included in the response.
Server-reported apply job failures (the server processed the request but its own apply job failed) Structured JSON — a send.lastFailure object is included in the response.
Server-reported check job failures (the server failed to encode a changeset for the client) Structured JSON — a receive.lastFailure object is included in the response.

This means: if you get JSON back, the server was reachable and the network protocol ran. If you get a SQL error, connectivity or configuration is broken.

cloudsync_network_send_changes()

Description: Sends all unsent local changes to the remote server.

The send path streams payloads through cloudsync_payload_chunks(), so payload_max_chunk_size also limits the payloads generated for network transport. Each generated chunk is uploaded/applied independently; the local send checkpoint is advanced only after the chunk stream completes successfully.

Chunk transport is transparent to the CloudSync backend. Each chunk is sent as a normal /apply payload, either inline as a base64 blob or through the upload url path. There is no separate chunk flag: old payloads, monolithic payloads, and v3 fragment payloads are distinguished by the payload format itself.

Parameters: None.

Returns: A JSON string with the send result:

{"send": {"status": "synced|syncing|out-of-sync|error", "localVersion": N, "serverVersion": N, "chunks": C, "bytes": B, "lastFailure": {...}}}
  • send.status: The current sync state — "synced" (all changes confirmed), "syncing" (changes sent but not yet confirmed), "out-of-sync" (local changes pending or gaps detected), or "error".
  • send.localVersion: The latest local database version.
  • send.serverVersion: The latest version confirmed by the server.
  • send.chunks: The number of payload chunks sent this call (a large push is split into multiple transport chunks bounded by payload_max_chunk_size). 0 when there were no local changes to send.
  • send.bytes: The total serialized payload bytes sent this call (uncompressed cloudsync payload size, summed across chunks; not the compressed wire size).
  • send.lastFailure (optional): Present only when the server reports a failed apply job. Forwarded verbatim from the server's failures.apply and typically includes jobId, code, stage, message, retryable, and failedAt. It is emitted regardless of status so callers can detect server-side failures during "syncing" or even after the state has nominally recovered. This function is send/apply-scoped: server-reported check-job failures (failures.check) are not surfaced here — see cloudsync_network_receive_changes() and cloudsync_network_sync().

Example:

SELECT cloudsync_network_send_changes();
-- '{"send":{"status":"synced","localVersion":5,"serverVersion":5,"chunks":1,"bytes":2048}}'

-- With a server-reported failure (e.g. unknown schema hash on the server side):
-- '{"send":{"status":"out-of-sync","localVersion":1,"serverVersion":0,"chunks":1,"bytes":512,"lastFailure":{"jobId":44961,"code":"internal_error","stage":"apply_payload","message":"cloudsync operation failed: Cannot apply the received payload because the schema hash is unknown 4288148391734624266.","retryable":true,"failedAt":"2026-04-15T22:21:09.018606Z"}}}'

cloudsync_network_receive_changes([max_chunks])

Description: Receives new changes from the remote server and applies them to the local database. (Formerly cloudsync_network_check_changes(), which remains available as a deprecated alias — see below.)

If changes are already prepared for the local site, they are downloaded and applied. If nothing is ready yet, the server starts preparing a package asynchronously and this call returns having applied nothing; a later call retrieves it. This function does not wait/poll for preparation to finish — it applies what is available now. To force an update and wait for not-yet-ready changes, use cloudsync_network_sync(wait_ms, max_retries).

By default this function drains all currently-available chunks in one call. Pass max_chunks to cap how many chunks are applied per call, for caller-driven progress or traffic control:

-- Drain at most 5 chunks, loop until the stream is complete
SELECT cloudsync_network_receive_changes(5) ->> '$.receive.complete';

The drain position (the per-stream page cursor) is held in memory on the network context, so a capped drain resumes where it left off on the next call — the caller does not manage any cursor; it just loops while receive.complete is false. If the connection is closed or the process restarts mid-drain, the cursor is lost and the next call safely restarts the drain from the beginning of the stream: already-applied chunks are re-downloaded and re-applied idempotently, so no rows are skipped — only redundant download is incurred. This is safe because the durable receive checkpoint (check_dbversion/check_seq) only advances after a stream has been fully applied, never in the middle of a source db_version.

If the network is misconfigured or the remote server is unreachable, the function raises a SQL error. If the received payload cannot be applied locally (for example because of an unknown schema hash), the error is returned as a receive.error field in the JSON response. If the server reports an unresolved failed check job (e.g. an encode_changes failure), that failure is forwarded as a receive.lastFailure object.

Parameters:

  • max_chunks (INTEGER, optional): Maximum number of chunks to apply this call. Omit or pass 0 (or negative) to drain everything available. A positive value caps the drain; receive.complete will be false when the cap stops a drain that still has pending chunks.

Returns: A JSON string with the receive result:

{"receive": {"rows": N, "tables": ["table1", "table2"], "chunks": C, "bytes": B, "complete": true, "error": "...", "lastFailure": {...}}}
  • receive.rows: The total number of rows received and applied to the local database, summed across all chunks drained this call. 0 when the receive phase failed, when nothing was available, or when only intermediate fragments were staged without completing a value.
  • receive.tables: An array of table names that received changes (the union across all drained chunks). Empty ([]) if no changes were applied or the receive phase failed.
  • receive.chunks: The number of payload chunks applied by this call. 0 when nothing was ready, 1 for a single monolithic/inline page, and N for a drained N-chunk stream (bounded by max_chunks if given).
  • receive.bytes: The total serialized payload bytes received this call (uncompressed cloudsync payload size, summed across chunks; transport-independent, not the compressed wire size). Useful for byte-budgeted draining together with max_chunks.
  • receive.complete (boolean): true when the receive stream is fully drained (nothing pending), false when more chunks remain — because max_chunks capped the drain, or it stopped early. When false, call this function again to continue.
  • receive.error (optional, string): Present when client-side cloudsync_payload_apply failed. Contains a human-readable error message describing why the received payload could not be applied.
  • receive.lastFailure (optional, object): Present only when the server reports a failed check job. Forwarded verbatim from the server's failures.check and typically includes jobId, dbVersion, seq, code, stage, message, retryable, and failedAt. Distinct from receive.error: receive.error describes a client-side apply failure (string), while receive.lastFailure describes a server-side check-job failure (object). Both can coexist in the same response. This function is check-scoped: server-reported apply-job failures (failures.apply) are not surfaced here — see cloudsync_network_send_changes() and cloudsync_network_sync().

Example:

SELECT cloudsync_network_receive_changes();
-- '{"receive":{"rows":3,"tables":["tasks"],"chunks":1,"bytes":820,"complete":true}}'

-- Capped drain with more pending (call again to continue):
-- '{"receive":{"rows":40,"tables":["docs"],"chunks":5,"bytes":1310720,"complete":false}}'

-- With a client-side apply error:
-- '{"receive":{"rows":0,"tables":[],"chunks":0,"bytes":0,"complete":true,"error":"Cannot apply the received payload because the schema hash is unknown 7218827471400075525."}}'

-- With a server-reported check-job failure:
-- '{"receive":{"rows":0,"tables":[],"chunks":0,"bytes":0,"complete":true,"lastFailure":{"jobId":456,"dbVersion":15,"seq":1,"code":"tenant_unreachable","stage":"encode_changes","message":"tenant check failed","retryable":true,"failedAt":"2026-04-24T10:22:00Z"}}}'

cloudsync_network_check_changes([max_chunks])

Deprecated: use cloudsync_network_receive_changes(). This name is retained as a thin alias for backward compatibility and will be removed in a future major version. It behaves identically, including the optional max_chunks argument and all returned fields.

cloudsync_network_sync([wait_ms], [max_retries])

Description: Performs a full synchronization cycle. This function has two overloads:

  • cloudsync_network_sync(): Performs one send operation and one check operation.
  • cloudsync_network_sync(wait_ms, max_retries): Performs one send operation and then downloads remote changes.

When the server delivers changes as a stream of chunks, this function drains the whole stream in a single call: as long as the next chunk is already available it is fetched back-to-back with no delay. wait_ms and max_retries are spent only while the server payload is not yet ready (the server is still preparing a package): in that case the function waits wait_ms and retries up to max_retries times. They are not consumed while paging through chunks that are already available.

Parameters:

  • wait_ms (INTEGER, optional): The time to wait in milliseconds between retries while the server payload is not yet ready. Defaults to 100.
  • max_retries (INTEGER, optional): The maximum number of poll attempts while the server payload is not yet ready. Defaults to 1.

Returns: A JSON string with the full sync result, combining send and receive:

{
  "send": {"status": "synced|syncing|out-of-sync|error", "localVersion": N, "serverVersion": N, "chunks": C, "bytes": B, "lastFailure": {...}},
  "receive": {"rows": N, "tables": ["table1", "table2"], "chunks": C, "bytes": B, "complete": true, "error": "...", "lastFailure": {...}}
}
  • send.status: The current sync state — "synced", "syncing", "out-of-sync", or "error".
  • send.localVersion: The latest local database version.
  • send.serverVersion: The latest version confirmed by the server.
  • send.chunks / send.bytes: Number of payload chunks sent and total serialized payload bytes sent during the send phase. Same semantics as in cloudsync_network_send_changes().
  • send.lastFailure (optional): Same semantics as in cloudsync_network_send_changes() — forwarded verbatim from the server's failures.apply whenever a failed apply job is reported, regardless of status.
  • receive.rows: The total number of rows received and applied during the receive phase, summed across all chunks drained in this call. 0 when the receive phase failed.
  • receive.tables: An array of table names that received changes (the union across all drained chunks). Empty ([]) if no changes were applied or the receive phase failed.
  • receive.chunks: The number of payload chunks applied in this call. 0 when nothing was ready, 1 for a single monolithic/inline page, and N for a fully drained N-chunk stream. cloudsync_network_sync() always drains the whole stream (it does not cap chunks).
  • receive.bytes: The total serialized payload bytes received this call (uncompressed cloudsync payload size, summed across chunks; not the compressed wire size). Same semantics as in cloudsync_network_receive_changes().
  • receive.complete (boolean): true when the server stream was fully drained, false when the download stopped before the final chunk (an error occurred, or an internal safety bound was reached). When false, call cloudsync_network_sync() again to resume; re-delivered rows are idempotent.
  • receive.error (optional, string): Present when client-side cloudsync_payload_apply failed (for example "Cannot apply the received payload because the schema hash is unknown 7218827471400075525."). The send result is always preserved so the caller can tell that local changes reached the server even when applying incoming changes failed. The receive drain stops immediately on apply errors, since failures like schema-hash mismatches do not heal across retries. Endpoint/network errors during the receive phase raise a SQL error instead.
  • receive.lastFailure (optional, object): Same semantics as in cloudsync_network_receive_changes() — forwarded verbatim from the server's failures.check whenever a failed check job is reported. Distinct from receive.error. cloudsync_network_sync() reports both send.lastFailure and receive.lastFailure when present.

Example:

-- Perform a single synchronization cycle
SELECT cloudsync_network_sync();
-- '{"send":{"status":"synced","localVersion":5,"serverVersion":5,"chunks":1,"bytes":2048},"receive":{"rows":3,"tables":["tasks"],"chunks":1,"bytes":820,"complete":true}}'

-- Perform a synchronization cycle with custom retry settings
SELECT cloudsync_network_sync(500, 3);
-- A large download drained as a multi-chunk stream in a single call:
-- '{"send":{"status":"synced","localVersion":42,"serverVersion":42,"chunks":0,"bytes":0},"receive":{"rows":1200,"tables":["docs"],"chunks":7,"bytes":1835008,"complete":true}}'

-- Receive phase failed but send phase completed — the error is surfaced in JSON, not as a SQL error:
-- '{"send":{"status":"synced","localVersion":5,"serverVersion":5,"chunks":1,"bytes":512},"receive":{"rows":0,"tables":[],"chunks":0,"bytes":0,"complete":false,"error":"Cannot apply the received payload because the schema hash is unknown 7218827471400075525."}}'

cloudsync_network_reset_sync_version()

Description: Resets local synchronization version numbers, forcing the next sync to fetch all changes from the server.

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_reset_sync_version();

cloudsync_network_has_unsent_changes()

Description: Checks if there are any local changes that have not yet been sent to the remote server.

Parameters: None.

Returns: 1 if there are unsent changes, 0 otherwise.

Example:

SELECT cloudsync_network_has_unsent_changes();

cloudsync_network_logout()

Description: Logs out the current user and cleans up all local data from synchronized tables. This function deletes and then re-initializes synchronized tables, useful for switching users or resetting the local database. Warning: This function deletes all data from synchronized tables. Use with caution. Consider calling cloudsync_network_has_unsent_changes() before logout to check for unsent local changes and warn the user before data that has not been fully synchronized to the remote server is deleted.

Parameters: None.

Returns: None.

Example:

SELECT cloudsync_network_logout();