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.
Where the spec goes
Section titled “Where the spec goes”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.
What a run does
Section titled “What a run does”- Reads every chosen tab of the source and finds each tab’s header row.
- 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.
- For each row: evaluates the lookups, then the fields in order, then the keep rules, then the key.
- Writes the rows to the destination in batches, by key, so a second run of the same rows changes nothing.
A complete example
Section titled “A complete example”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.
The spec
Section titled “The spec”| 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.
Source
Section titled “Source”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. |
Access
Section titled “Access”service_accountreads through the Sheets API, as agoogle_service_accountconnection in thesourcerole, 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_linkreads 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.
Header
Section titled “Header”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
Section titled “Columns”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, andfound.<column>isfalse.
Never-read columns
Section titled “Never-read columns”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.
Lookups
Section titled “Lookups”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
Section titled “Fields”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.
What an expression can read
Section titled “What an expression can read”| 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. |
Transforms
Section titled “Transforms”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: …”.
Keep rules
Section titled “Keep rules”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
modeitself. The HTTP destination isn’t told the mode: your API decides what to do with each row. The lake takesupsertonly. - A group may hold at most 5,000 rows. The rows of a bigger group are rejected.
Destinations
Section titled “Destinations”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 |
HTTP API
Section titled “HTTP API”"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://.
The sync protocol, version 1
Section titled “The sync protocol, version 1”Each batch is one request:
POST /v1/leads/sync HTTP/1.1Host: api.example.comAuthorization: Bearer <token>Content-Type: application/jsonAccept: application/jsonIdempotency-Key: 0190c3c4-0000-7000-8000-00000000d001-1-1X-SREInsights-Sync: 0190c3c4-0000-7000-8000-00000000b001User-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. |
Postgres table
Section titled “Postgres table”"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 elseHow 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. Withinsert_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_keycolumn,sync_keyisn’t unique, a mapped column is missing, or the user may not insert (or update, forupsert). - 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.
The lake
Section titled “The lake”"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
tableis 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.
Limits
Section titled “Limits”| 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 |
Preview, then switch on
Section titled “Preview, then switch on”A sync runs only a version someone has previewed. It is saved paused.
- 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. - 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.
- 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.
With the API
Section titled “With the API”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:
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:
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 a sync
Section titled “Changing a sync”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.
Clients
Section titled “Clients”- 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.
Results
Section titled “Results”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.