Sequin's bidirectional syncs let you create, update, and delete Salesforce and HubSpot entities by interacting with your synced database tables. Sequin translates database writes into corresponding API changes and applies those changes using your stored API credential.
A Postgres trigger records changes in your database tables into the Sequin-managed
_sync_write_log. A Sequin process asynchronously consolidates those rows, packs them into batch HTTP requests, and writes the API responses back into the
Each bidirectionally-synced table is initialized with a
sync_log_writes trigger after
INSERT events. That trigger executes a dedicated function for that table. For example, the synced
contact table in
my_namespace will have a corresponding Postgres function:
All of these Postgres functions --- one for each bidirectionally-synced table --- report changes to a centralized
_sync_write_log table in the namespace.
To avoid redundantly writing API-originating changes to the API, these trigger functions ignore changes to the database made by Sequin's sync worker. They use a Postgres session variable,
sync.user, to identify the sync worker's changes.
We do not recommend setting this session variable when writing to your database.
Sync write log
Sequin manages the
_sync_write_log table in your namespace, a manifest for bidirectional sync writes (creates, deletes, and updates).
|Column name||Data type||Description|
|Incremental unique ID for a |
|When a trigger function generated this row.|
|ID of the transaction that modified the origin table.|
|When this entry was updated: created by a trigger function, claimed by a Sequin writer, or updated with the API response.|
|When this entry was picked up by a Sequin writer for translation into an API call. Initially null.|
|ID of the transaction in which a Sequin writer picked up this entry. Initially null.|
|State of the write: |
|The triggering Postgres action: |
|Name of the originating table.|
|API-side ID of a row in the originating table that was updated or deleted. Null for newly-created records.|
|The pre-write state of the record. Null for newly-created records.|
|The record values updated in the write. Null for deleted records.|
|Numeric API status, usually a HTTP response status code. Initially null.|
|Text summarizing the API response. Initially null.|
_sync_write_log table directly can cause unintended writes and write failures; we don't recommend it.
_sync_write_log entry is initialized in one of two states, depending on whether it seems plausibly writable.
new: this entry hasn't been claimed by a writer process.
ignore: this entry represents a write to the database table, but it shouldn't be translated into an API request. For example, an API can't delete a record without an
Sequin's writer manages the state field after it's initialized.
pending: a Sequin writer has claimed this entry; it's being translated into an API call. When an entry becomes
failed: either an internal failure prevented the writer from requesting an API-side change, or the API returned a non-OK response. See
succeeded: the API acknowledged this change was written successfully.
ignore a warning state. The propagation latency between database and API states means some database writes should validly not be written to the API.
If many entries are in the
ignore state, your system might be updating or deleting newly-created records before they're propagated to the API.
Sequin runs a platform-specific writer process for each of your bidirectional syncs. Like the read path replicating API data in your database, the writer uses your API credential and its API calls count against your quota.
Writers obey the sync-wide rate limit you configure. In some circumstances a high write volume will increase sync latency in both directions.
To minimize their API usage and prevent race conditions, writers coalesce and batch
_sync_write_log entries into tightly-packed API calls.
In some cases, several
_sync_write_log entries reflecting changes to the same record can be consolidated into a single API operation. Often this is necessary; for example, it can prevent racing an update to some record against that record's deletion.
- Any series of
updates coalesce into a single
createfollowed by one or more
updates coalesces into a single
- Any series of writes ending in a
deletecoalesce into a
Whenever it merges
values, the writer prefers those set in the later entries.
Sequin's writer processes use API batch endpoints for creating, updating, or deleting several records at once. There's an inherent tradeoff between write latency and batch efficiency; to keep write latency predictable, writers may issue a batch request before the batch is full.
Writer coalescing and batching mean a single API request/response pair corresponds to several
_sync_write_log entries. Those entries' terminal
api_messages all reflect that single response.
An issue with a single
_sync_write_log entry can cause a whole batch request to fail. See
api_message to find the root cause. If the
api_message is ambiguous, reach out to Sequin support so we can improve it.
Different APIs may use different numeric response codes --- e.g. HTTP status codes or gRPC status codes. A
api_result uses the code native to that API. Because each sync has its own
_sync_write_log table, the codes should be consistent. For a generic indication of whether a given entry was written successfully, use the entry
api_message is a human-readable summary of the API response. When an entry is written successfully, the message is often trivial ("OK").
Sometimes you need to know the API has accepted a write. The
_sync_write_log includes two values useful for polling to detect when a write is completed.
api_id indicates the source record. If you updated 100 Contacts at once, but you just care about the Contact with ID
abc123, select entries in the
api_id = abc123.
SELECT state, api_result, api_message FROM _sync_write_log
WHERE api_id = '0038b00002lcB0AAAU'
ORDER BY inserted_at DESC LIMIT 1;
inserted_tx records the unique ID of the triggering transaction. If you create ten contacts, update ten others, and delete ten more in a single transaction, you can get the status of all downstream writes by selecting all entries in the
_sync_write_log with that
SELECT api_id, action, state, api_result, api_message
WHERE inserted_tx = 9505333
ORDER BY action, state;