Skip to content

Sync spec

A sync reads rows from a source, maps them, and writes them to a destination: source → mapping → destination. It is a job of the built-in type sync, so it is scheduled, run, retried and alerted on like every job. What is particular to it is its spec: configuration, never code, and no secrets, only connections by role.

This page describes every field of the spec. For a walkthrough, see Sync a Google Sheet. For what a sync is, see Syncs.

In the web app, choose Jobs → New sync, pick a template under Start from (Google Sheet → HTTP API or Google Sheet → Postgres table), and edit Spec (JSON).

With the API, the spec is the job’s sync field, next to the job’s other fields (see Job types):

{
"name": "Leads → CRM",
"type": "sync",
"clientId": "0190c3c4-0000-7000-8000-00000000c001",
"connections": {
"source": "0190c3c4-0000-7000-8000-00000000a001",
"destination": "0190c3c4-0000-7000-8000-00000000a002"
},
"runsOn": { "group": "jobs" },
"schedule": { "cron": "15 * * * *", "timezone": "UTC" },
"sync": { "source": { "kind": "google_sheets", "spreadsheetId": "…" }, "…": "…" }
}

When you save, SREInsights checks the spec’s shape and every expression’s syntax. Expressions run only on the runner.

  1. Reads every chosen tab of the source and finds each tab’s header row.
  2. Finds the named columns by their headers, never one whose header has a never-read word, and drops every other cell of each row straight away.
  3. For each row: evaluates the lookups, then the fields in order, then the keep rules, then the key.
  4. Writes the rows to the destination in batches, by key, so a second run of the same rows changes nothing.

A “Leads” sheet with one tab per region, written to an HTTP API:

{
"source": {
"kind": "google_sheets",
"spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789/edit",
"access": "service_account",
"tabs": { "nameContains": ["leads"] },
"header": { "match": { "equals": "lead id" } },
"neverRead": ["phone"]
},
"columns": {
"lead_id": [{ "equals": "lead id" }],
"company": [{ "equals": "company" }, { "contains": ["organisation"] }],
"website": [{ "contains": ["website"] }, { "equals": "url" }],
"stage": [{ "equals": "stage" }],
"added": [{ "contains": ["date", "added"] }],
"deal": [{ "contains": ["deal", "value"] }]
},
"lookups": [
{
"name": "region",
"input": "tab.name",
"match": "contains",
"rows": [
{ "when": "emea", "value": "EMEA" },
{ "when": "apac", "value": "APAC" }
],
"otherwise": "Americas"
}
],
"fields": [
{ "name": "id", "value": "col.lead_id|trim", "output": false },
{ "name": "company", "value": "col.company|trim" },
{ "name": "site.url", "value": "col.website|url" },
{ "name": "site.host", "value": "col.website|host" },
{ "name": "stage", "value": "col.stage|trim|lower" },
{ "name": "addedOn", "value": "col.added|date('%d/%m/%Y', '%Y-%m-%d')" },
{ "name": "dealValue", "value": "col.deal|int" },
{ "name": "region", "value": "lookups.region" }
],
"keep": [
{ "if": "fields.id", "reason": "no lead id" },
{ "if": "fields.company", "reason": "no company" }
],
"key": "fields.id",
"write": { "mode": "upsert", "groupBy": "company" },
"destination": { "kind": "http", "path": "/v1/leads/sync", "batchRows": 200 }
}

Each row reaches the API as:

{
"key": "L-1042",
"position": 5,
"data": {
"company": "Acme",
"site": { "url": "https://acme.example.com", "host": "acme.example.com" },
"stage": "qualified",
"addedOn": "2026-03-14",
"dealValue": 1200,
"region": "EMEA"
}
}

id is a helper (output: false), so it isn’t in data; it is the row’s key instead. The sheet’s “Email” and “Phone” columns are never read.

Field Type Required Default What it does
source object yes What the sync reads. See Source.
columns object yes The columns it reads, by header. 1 to 100. See Columns.
lookups array no [] Small tables. At most 20. See Lookups.
fields array yes What is written. 1 to 100, at least one written. See Fields.
keep array no [] Which rows are written. At most 20. See Keep rules.
key expression yes Each row’s key. See Key.
write object no { "mode": "upsert" } How rows are written. See Write.
destination object yes Where rows go. See Destinations.

Unknown fields are refused anywhere in the spec.

Today the source is a Google Sheet.

Field Type Required Default What it does
kind google_sheets yes The kind of source.
spreadsheetId text yes The sheet’s id, or a link to the sheet (the id is taken from it).
access service_account or public_link no service_account How the sheet is read.
tabs object no all tabs Which tabs. See Tabs.
header object no { "row": 1 } Where each tab’s header row is. See Header.
neverRead array of words no [] More words whose columns are never read. At most 20, each up to 80 characters. See Never-read columns.
  • service_account reads through the Sheets API, as a google_service_account connection in the source role, with the read-only scope. Share the sheet with the service account’s address (Viewer). The sheet doesn’t need to be readable by link.
  • public_link reads the sheet’s public CSV export with no sign-in, as a browser opens an “anyone with the link” sheet. It takes no source connection: saving one with it is refused. Only tabs in the sheet’s web view are read, so hidden tabs never are. A tab’s CSV may be at most 20 MB, and the whole sheet 50 MB.

A public_link run fails, rather than reading nothing, when Google’s page lists no tabs or sends something other than CSV.

Field Type Default What it does
names array of tab names Only these tabs, by exact name. 1 to 50.
nameContains array of words Only tabs whose name contains one of these, in any case. 1 to 50.
includeHidden boolean false Also hidden tabs. Service account reads only.

With neither names nor nameContains, every tab is read. With both, names wins. Tabs that aren’t chosen are never fetched.

Give one of:

Field Type What it does
row number, 1 to 1,000 The header is this row (from 1).
match header match The header is the first row with a cell that matches. See Columns for how matching works.

A tab with no header row is reported with “no header row found”, and its rows are not read.

columns names each column the sync reads, and the headers it may have: a list of 1 to 5 header matches, tried in order. The first that matches a header wins, so later ones are fallbacks.

"columns": {
"company": [{ "equals": "company" }, { "contains": ["organisation"] }],
"added": [{ "contains": ["date", "added"], "notContains": "updated" }]
}

Column names use lower-case letters, digits and _, starting with a letter, up to 40 characters.

A header match has contains or equals, not both:

Field Type What it does
contains array of 1 to 5 words The header contains every one of these words.
equals text The header is exactly this.
notContains text And the header doesn’t contain this. Optional.

Headers are compared trimmed and lower-cased, and the words are lower-cased too.

  • Only the named columns are read. Every other cell of a row is dropped on the runner as soon as the named cells are taken.
  • A column whose header isn’t found is empty ("") in every row, and found.<column> is false.

A header that contains any of these words is never a column, whatever the spec says:

pass, user, login, email, e-mail

They are compared as part of the header, so “Username”, “Users” and “Compass” are never read either. A spec whose columns names one of these words is refused when it is saved, so a sheet’s account logins never leave the runner.

source.neverRead adds more words. It can’t take these away. Words are words, not patterns: nothing in a spec runs as a regular expression on SREInsights or on a hosted runner.

Previews and run results show a never-read header with “(never read)” after it.

A lookup is a small table, such as a tab name → a region. For each row, the first table row whose when matches the input gives the value.

Field Type Required Default What it does
name name yes Its name, read as lookups.<name>. Lower-case letters, digits and _, up to 40 characters.
input expression yes What to look up, such as tab.name.
match contains, equals or startsWith no contains How when is compared with the input, in any case.
rows array of { when, value } yes 1 to 200 rows. when is up to 200 characters; value is any JSON value.
otherwise any no null The value when no row matches.

fields are what the destination receives, evaluated in order for each row.

Field Type Required Default What it does
name field name yes Letters, digits and _, starting with a letter, up to 40 characters. A dotted name, such as link.url, makes a nested field (up to four parts). Each name once.
value expression yes A JEXL expression, up to 2,000 characters.
output boolean no true false: a helper for later fields, keep rules and the key, not written.

A field whose value is undefined is left out. A number that isn’t finite becomes null.

Name What it is
col.<column> The cell of a named column, trimmed. "" when the column wasn’t found or the cell is empty.
found.<column> Whether the tab has the column.
row.number The row’s number in the sheet, from 1.
row.first The row’s first cell. Empty when the first column’s header is never read.
tab.name, tab.gid, tab.index The tab’s name, its id, and its position.
sheet.id, sheet.id12, sheet.title The spreadsheet’s id, its first 12 characters, and its title.
lookups.<name> A lookup’s value for this row.
fields.<name> A field defined earlier, including helpers.

Expressions use the same transforms as checks and flows (see Expressions), such as trim, lower, upper, number, split and join. Syncs add these, which work only in a sync:

Transform Example Result
url col.website|url A full http(s) URL, with https:// added when it has no scheme. null when it isn’t a web address.
pageUrl col.page|pageUrl A page as Google indexes it: https, lower-cased host, no query string or fragment. null when it isn’t a web address.
host col.website|host The host, lower-cased, without www..
netloc col.website|netloc The host exactly as written, www. and port included.
date col.added|date('%d/%m/%Y', '%Y-%m-%d') The first format that fits, as YYYY-MM-DD, or null. Formats use %d, %m, %Y, %y and literal characters, as Python’s strptime. Without a format: %Y-%m-%d.
int col.deal|int Whole digits as a number: "42" → 42, "4.2" → null, "" → null.
digits col.ref|digits Whether the value is all digits.
slug col.link|slug The last part of a URL’s path with - as spaces: …/collections/gold-chains → gold chains.

An expression that fails skips its row, with the reason “an expression failed: …”.

Each rule is an expression that must be truthy for a row to be written. The first rule that isn’t is the reason the row is skipped, shown in previews and results.

Field Type Required What it does
if expression yes The row is kept when this is truthy.
reason text, up to 200 characters yes Why a row was left out, when this rule doesn’t hold.

Empty rows are skipped before any rule runs.

key is an expression that names each row. The destination updates a row by its key, so a re-run, a lost lease or two runs of the same rows never adds a row twice.

  • Make it stable while the row means the same thing: an id column, or a combination such as tab.gid + ':' + fields.name|lower.
  • Never use the row number: it changes when someone inserts a row.
  • The key must be text or a number, and not empty, or the row is skipped with “the key is empty”. It is cut to 1,000 characters.
  • Two rows with one key in a run: the first is written, the second is skipped with “the same key as row N of tab”.
  • Rows deleted from the sheet stay where they were written.
Field Type Default What it does
mode upsert or insert_only upsert upsert updates a row that already has the key. insert_only writes new keys only and leaves existing rows as they are.
groupBy field name A field whose rows belong together. They are always sent in the same batch, in sheet order, so the destination sees a whole group at once. It must name one of fields.
  • The Postgres destination applies mode itself. The HTTP destination isn’t told the mode: your API decides what to do with each row. The lake takes upsert only.
  • A group may hold at most 5,000 rows. The rows of a bigger group are rejected.

The destination connection fills the destination role. Its type must match destination.kind:

kind Connection type Writes to
http http_destination An HTTP API, by the sync protocol
postgres postgres_destination One Postgres table
lake s3_destination The lake, through a client’s S3 destination
"destination": { "kind": "http", "path": "/v1/leads/sync", "batchRows": 500 }
Field Type Required Default What it does
path path starting with / yes Added to the connection’s base URL.
batchRows number, 1 to 500 no 500 Rows per batch.

The http_destination connection has:

Field Secret Default What it does
Base URL (baseUrl) no https://, or http:// for an address only your own runner reaches. No query.
Bearer token (token) yes Sent as Authorization: Bearer …, only to this address.
Extra headers (extraHeaders) yes Optional, one Name: value a line, such as a Cloudflare Access service token. They can’t replace the headers the sync sets.
Timeout per batch (ms) (timeoutMs) no 30000 How long to wait for an answer to one batch.

On a hosted runner group, the base URL must be https://.

Each batch is one request:

POST /v1/leads/sync HTTP/1.1
Host: api.example.com
Authorization: Bearer <token>
Content-Type: application/json
Accept: application/json
Idempotency-Key: 0190c3c4-0000-7000-8000-00000000d001-1-1
X-SREInsights-Sync: 0190c3c4-0000-7000-8000-00000000b001
User-Agent: sreinsights-runner (sync)
{
"protocol": 1,
"sync": { "id": "0190c3c4-0000-7000-8000-00000000b001", "name": "Leads → CRM" },
"run": { "id": "0190c3c4-0000-7000-8000-00000000d001", "attempt": 1 },
"dryRun": false,
"rows": [
{ "key": "L-1042", "position": 5, "data": { "company": "Acme", "stage": "qualified" } }
]
}
Part What it is
Idempotency-Key <run id>-<attempt>-<batch>, with batches counted from 1. The same for every try of a batch. Another attempt of the run has its own, because the sheet may have changed since.
X-SREInsights-Sync The sync’s job id.
protocol 1.
sync The sync’s job id and name.
run The run’s id and attempt.
dryRun true for a preview: say what would happen, and change nothing.
rows[].key The row’s key.
rows[].position The row’s number in the sheet.
rows[].data The row’s written fields, nested by their dotted names.

A batch is at most 500 rows and 1 MB. The rows of a batch over 1 MB are rejected: lower batchRows, or write fewer fields.

Answer 200 with one result per row, by key:

{
"results": [
{ "key": "L-1042", "status": "created" },
{ "key": "L-1043", "status": "rejected", "error": "stage must be one of new, qualified, won, lost" }
]
}
Field Type What it does
results[].key text, up to 1,000 characters The row’s key.
results[].status created, updated, unchanged or rejected What the API did with the row, or would do in a preview.
results[].error text, up to 500 characters Optional: why a row was rejected.

A row the answer has no result for is rejected.

How the runner treats other answers:

Answer What happens
401, 403 The token is wrong. The run stops; nothing more is sent.
Another 4xx The API refuses what was sent. The run stops.
5xx, 429, no answer Tried twice more, then that batch is left unsent and the next batch is tried.
2xx that isn’t { "results": [...] } The run stops.
A redirect Refused, so the token goes to that address only.
"destination": { "kind": "postgres", "columns": { "company_name": "company", "site_url": "site.url" }, "batchRows": 500 }
Field Type Required Default What it does
columns object: column → field no each written field into the column of its name Maps fields to columns. At most 200. sync_key is written by the sync itself and can’t be mapped. Each field must be a written field.
batchRows number, 1 to 500 no 500 Rows per batch.

Without columns, every written field’s name must be a lower-case column name, so map dotted names.

The postgres_destination connection has Host, Port (default 5432), Database, User, Password, TLS (require, verify-full or disable; default require), Schema (default public) and Table. On a hosted runner group, TLS must not be disable.

Make the table and its user like this. The table needs a sync_key text PRIMARY KEY column. A synced_at timestamptz column, if it has one, is set when a row is created or changed.

CREATE TABLE sales.leads (
sync_key text PRIMARY KEY,
company_name text,
site_url text,
stage text,
added_on date,
deal_value numeric,
synced_at timestamptz
);
CREATE ROLE leads_sync LOGIN PASSWORD '…';
GRANT USAGE ON SCHEMA sales TO leads_sync;
GRANT SELECT, INSERT, UPDATE ON sales.leads TO leads_sync; -- and nothing else

How it writes:

  • Each batch is one INSERT … SELECT … FROM json_populate_recordset(…) ON CONFLICT (sync_key) DO UPDATE … WHERE … IS DISTINCT FROM … in its own transaction. Postgres converts each value to its column’s type, values are never SQL text, and an unchanged row isn’t written. With insert_only, an existing key is left alone.
  • A batch Postgres refuses is tried row by row, so one bad value rejects one row.
  • A run fails when the table doesn’t exist, has no sync_key column, sync_key isn’t unique, a mapped column is missing, or the user may not insert (or update, for upsert).
  • A superuser is refused. A user that may also delete or truncate gets a warning.
  • A preview runs the writes and rolls them back, so the table’s triggers fire and its sequences move on.
"destination": { "kind": "lake", "table": "leads" }
Field Type Required What it does
table name yes The sync’s name for its rows in the lake. Lower-case letters, digits and _, up to 40 characters.

The connection is the client’s s3_destination. A run stages its rows as one Parquet file and a JSON sidecar in the client’s destination, at <prefix>sync_records/<time>-<id>.parquet and .json, the same way the collecting job types stage theirs: every column is text, and the sidecar is written last. The lake ingest then merges them.

Each row has these columns: org_id, client_id, sync_table (the destination’s table), record_key, data (the row’s fields as JSON), sync_id, run_id and synced_at. The ingest keeps rows by organization, client, table and key, a newer run winning. Query them from the sync_records table:

SELECT data->>'company' AS company FROM sync_records WHERE sync_table = 'leads';

Rules:

  • Only a client’s sync, through the client’s own destination, writing under the client’s own prefix, orgs/<orgId>/clients/<clientId>/. This is checked when you save and again before anything is written.
  • Only upsert.
  • One lake table per client per sync: a second sync of the same client into the same table is refused, because rows with the same key would overwrite each other.
  • A file holds at most 50,000 rows, and one run at most 1,000,000. A row whose fields are over 64,000 characters as JSON is rejected on its own.
  • Every row of a run is staged and counts as created. Whether it changed is the ingest’s to find out.
  • Rows a source no longer has stay in the lake.
  • A preview stages nothing.
  • The ingest takes files only from the lake’s own bucket, and only when every row names the organization and client that the file’s path names.
Limit Value
Rows read from one tab 50,000. A tab with more says “only the first 50,000 rows were read”.
Rows per batch 500 (batchRows, HTTP and Postgres)
One HTTP batch 1 MB
Rows in one groupBy group 5,000
Rows in one lake file 50,000
One row’s fields in the lake 64,000 characters as JSON
Rows a preview returns 50
Expression length 2,000 characters

A sync runs only a version someone has previewed. It is saved paused.

  1. Preview. On the sync’s page, under Preview, press Preview. The runner reads the sheet, maps the rows, and asks the destination what it would do with each row, writing nothing. The HTTP destination is sent dryRun: true; Postgres runs the writes and rolls them back; the lake stages nothing.
  2. Check. The preview shows the tabs with their headers and the columns found, the rows left out and why, and the first 50 rows it would write, with what the destination would do with each.
  3. Switch on. Press Switch on. The sync runs on its next slot, and Run now works.

Before a preview of the current version has run, switching on and Run now answer 409 SYNC_NOT_PREVIEWED, and creating a sync with enabled: true does too.

A preview runs as a task on one of the sync’s runners. It holds none of the sync’s slots and shows in none of its run history or alerts. It waits up to 120 seconds for a runner.

Terminal window
curl -X POST "https://console.sreinsights.com/api/v1/jobs/<job id>/preview" \
-H "Authorization: Bearer $SREINSIGHTS_TOKEN"

The answer is 202 with taskId, state (queued), version and queueExpiresAt. A job that isn’t a sync answers 400 NOT_A_SYNC.

Poll the preview:

Terminal window
curl "https://console.sreinsights.com/api/v1/jobs/<job id>/previews/<task id>" \
-H "Authorization: Bearer $SREINSIGHTS_TOKEN"
Field What it is
state queued, running, done or failed.
version The sync’s version the preview ran on.
current Whether that is still the sync’s current version.
summary One line, such as completed · would write 120 (118 new, 2 changed), 0 unchanged, 3 skipped.
result The run’s result. See Results.
preview tabs, counts, problems, sample (the rows it would write, each with tab, row, key, data, status and reason), serviceAccount and any warnings.
error When failed: why.

Then switch it on:

Terminal window
curl -X PATCH "https://console.sreinsights.com/api/v1/jobs/<job id>" \
-H "Authorization: Bearer $SREINSIGHTS_TOKEN" \
-H "Content-Type: application/json" \
-d '{ "enabled": true }'

A preview counts only when it finished with rows to show and landed something. One where every row was rejected, or the destination refused, is failed.

Changing the spec or choosing other connections makes a new version of the sync, which needs its own preview. Send enabled: false with the change (the form does), preview it, and switch it on again. Sending the change to a sync that is on answers 409 SYNC_NOT_PREVIEWED.

Editing a connection’s own settings, such as a destination’s base URL or table, doesn’t make a new version.

  • A client’s sync reads through the client’s own source connection and writes to the client’s own destination, never shared ones: a shared Google service account opens every sheet shared with it, any client’s.
  • A sync with no client (the organization’s own) uses shared connections.
  • Previews carry the sync’s client and move with the sync to another client.

See Clients.

A sync run’s result is an ordinary job result:

Field What it holds
status completed; partial when some rows were rejected or unsent but some landed; failed when none landed, or the source couldn’t be read.
rows Rows created or changed.
tables Counts: created, updated, unchanged, rejected (including unsent) and skipped.
error What went wrong, such as how many rows were rejected and an example.
details.sync dryRun; tabs (each with name, rows, kept, skipped, header, columns and any error); counts (read, skipped, duplicates, unsent, created, updated, unchanged, rejected); problems, the first 20 rows skipped or rejected as tab, row, key and reason; and any warnings. Never cell values.

A sync is alerted on like any job: Job “…” failed and, with a freshness, Job “…” has no new data. See Job types.