Skip to main content

Writes

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 _sync_write_log.

Diagram of Sequin's write architecture. Sequin-managed components are drawn in pink.

Detecting changes

Each bidirectionally-synced table is initialized with a sync_log_writes trigger after UPDATE, DELETE, and 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: my_namspace."_Contact_sync_write_logger".

All of these Postgres functions --- one for each bidirectionally-synced table --- report changes to a centralized _sync_write_log table in the namespace.

note

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 nameData typeDescription
idint4Incremental unique ID for a _sync_write_log entry.
inserted_attimestampWhen a trigger function generated this row.
inserted_txint8ID of the transaction that modified the origin table.
updated_attimestampWhen this entry was updated: created by a trigger function, claimed by a Sequin writer, or updated with the API response.
processed_attimestampWhen this entry was picked up by a Sequin writer for translation into an API call. Initially null.
processed_txint8ID of the transaction in which a Sequin writer picked up this entry. Initially null.
statesequin_write_log_stateState of the write: new, ignore, pending, or fail. See States.
actionsequin_write_log_actionThe triggering Postgres action: create, update, or delete.
table_nametextName of the originating table.
api_idtextAPI-side ID of a row in the originating table that was updated or deleted. Null for newly-created records.
oldjsonbThe pre-write state of the record. Null for newly-created records.
valuesjsonbThe record values updated in the write. Null for deleted records.
api_resultint4Numeric API status, usually a HTTP response status code. Initially null.
api_messagetextText summarizing the API response. Initially null.
caution

Manipulating the _sync_write_log table directly can cause unintended writes and write failures; we don't recommend it.

States

Each _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 api_id.

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 pending, processed_at and processed_tx are set.
  • failed: either an internal failure prevented the writer from requesting an API-side change, or the API returned a non-OK response. See api_result and api_message for details.
  • succeeded: the API acknowledged this change was written successfully.

A flow diagram of `_sync_write_log` states. `ignore`, `succeeded`, and `failed` are terminal.

note

Consider 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.

Writers

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.

note

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.

Coalescing

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.

  1. Any series of updates coalesce into a single update by merging values.
  2. A create followed by one or more updates coalesces into a single create by merging values.
  3. Any series of writes ending in a delete coalesce into a delete.

Whenever it merges values, the writer prefers those set in the later entries.

Summary of how pairs of `_sync_write_log` entries are coalesced into single equivalent API operations.

Batching

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.

API responses

Writer coalescing and batching mean a single API request/response pair corresponds to several _sync_write_log entries. Those entries' terminal states, api_results, and api_messages all reflect that single response.

note

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 _sync_write_log entry's 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 state.

api_message is a human-readable summary of the API response. When an entry is written successfully, the message is often trivial ("OK").

Awaiting responses

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 _sync_write_log where 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 inserted_tx.

SELECT api_id, action, state, api_result, api_message
FROM _sync_write_log
WHERE inserted_tx = 9505333
ORDER BY action, state;