Syncing a connector into a table

Mirror a connected service's records (emails, invoices, contacts) into an AutessaDB table with a polling script tool: paginate, dedup on a stable external id, keep an incremental watermark, drive it on a schedule, and know which actions must round-trip.

The decision: you want a Custom App or a workflow to work off a connected service's data (a Gmail inbox, QuickBooks invoices, a CRM's contacts) as if it were local — list it, filter it, show it fast. App code cannot call a connector directly, and hitting the connector live on every screen is slow and rate-limited. The pattern is: MIRROR the connector's records into an AutessaDB table with a polling sync, and read the table from the app.

There is no managed "sync connector X into table Y" primitive today, and there is no connector-authed generic API tool type either — so ingest is ALWAYS tool-shaped. The poller is an AUTESSA_SCRIPT tool that reads the connector's data through a TOOL: either it calls the connector's own TYPED tools (the Gmail-list / QuickBooks-list / etc. tool types) via tools.invokeById, or — when no typed tool covers the endpoint — it calls the provider's raw API from inside the script with connector.getAuth(connectorId) + api.get/post (the third rung of the capability ladder), which you then publish AS a tool. A DB trigger or a scheduled job does not sync anything by itself; it just EXECUTES that tool on a cadence or an event. It is a small, well-worn shape:

The polling sync

1. A destination table (autessa.autessadb.tables.create) with a column for a STABLE EXTERNAL ID (the provider's own record id — e.g. the Gmail message id) plus the fields you care about. The external-id column is what makes the sync idempotent.
2. A poller script (published as an AUTESSA_SCRIPT tool) that:
   • Reads the connector THROUGH A TOOL. Invoke the connector's typed list tool with tools.invokeById (pass the page token / filters as its arguments), or, if there is no typed tool for it, connector.getAuth + api.get inside the script. App code and triggers can never touch a connector directly — only a tool can — so the read is always tool-shaped.
   • Paginates the connector's list endpoint (follow its page token / cursor until the page is exhausted) so you don't miss records past the first page.
   • Dedups on the external id with db.upsertByExternalId. Do NOT hand-roll the query-then-branch-then-insert-or-update loop — that is exactly where sync scripts go wrong. Map each source record to a row values map (same shape as query.insert values), then hand the whole page to db.upsertByExternalId(tableId, 'external_id', rowsJson): it inserts new external ids and updates existing ones in one call, guarding .ok on every leg, and returns {ok, inserted, updated, ids, errors}. NEVER key dedup on a timestamp or a row position — only the stable external id is safe, which is precisely what this primitive keys on.
   • Keeps an INCREMENTAL WATERMARK. Persist the high-water mark of the last successful sync (the newest record's timestamp / the provider's sync cursor) — in a tiny state row or the tool's own state — and on the next run only pull records NEWER than the watermark. This keeps each run cheap and bounded instead of re-scanning everything.
3. A scheduled event to drive it (autessa.scheduledEvents.create) that invokes the poller tool on a cadence (every 15 min, hourly, ...). Cron is 6-field AWS format. The scheduled job's whole job is to EXECUTE the poller tool — same as a trigger executing a tool — not to sync anything itself. That is what keeps the mirror fresh without a human clicking anything.

Concrete skeleton (paginate -> map -> upsert -> watermark)

// Poller tool body. connectorId + listToolId are baked-in config; 'since' is state.
let pageToken = null, inserted = 0, updated = 0;
do {
  // Read a page THROUGH the connector's typed list tool.
  const page = JSON.parse(tools.invokeById(listToolId, JSON.stringify({
    pageToken: pageToken, since: since   // whatever args the tool declares
  })));
  if (page.error) { console.error(page.errorMessage); break; }

  // Map each source record to a row-values map (same shape as query.insert values).
  const rows = (page.messages || []).map(m => ({
    external_id: { columnType: 'SHORT_TEXT', textValue: m.id },
    subject:     { columnType: 'SHORT_TEXT', textValue: m.subject },
    received_at: { columnType: 'SHORT_TEXT', textValue: m.receivedIso }
  }));

  // Insert-or-update the whole page on the stable external id, in one call.
  const res = JSON.parse(db.upsertByExternalId(tableId, 'external_id', JSON.stringify(rows)));
  if (!res.ok) { console.error('upsert errors', res.errors); }
  inserted += res.inserted; updated += res.updated;

  pageToken = page.nextPageToken;
} while (pageToken);

// Advance the watermark only after the whole run succeeded.
response.setResponse(JSON.stringify({ inserted, updated }));

Which actions round-trip to the connector vs stay table-only

A mirrored table is a CACHE, not the source of truth for everything. Decide per action:

• Table-only (fine to write just the local row): view, filter, sort, tag/annotate for your app's own purposes, mark "seen in app".
• MUST round-trip to the connector (do the real action against the provider, THEN reflect it locally): mark-as-read, apply/remove a label or folder, archive, delete, send/reply. These change provider-side state; writing only the local row silently desyncs the mirror from the real account. Do the connector action through the appropriate tool first, and update the local row to match only after it succeeds.

Classifying / enriching rows with an LLM (the parse-wrap pattern)

A common follow-on is "classify each synced email" or "extract fields from each invoice". Note the shape of an LLM_CALL result: it returns the model's completion as a PLAIN STRING — there is no structured envelope, and even if you prompt for JSON the string only CONTAINS JSON text. So you cannot wire an LLM_CALL trigger to "fill columns" directly. Instead WRAP it:

• An AUTESSA_SCRIPT tool that runs the LLM_CALL, JSON.parses the completion string, and autessa.autessadb.query.updates the target columns with the parsed values.
• Chain it as a TRIGGER, and mind the event timing: the classification lands via an UPDATE, so anything DOWNSTREAM that reads the classified column must fire on that UPDATE, not on the original INSERT (on INSERT the column is still empty). Use autessa.autessadb.tables.analyzeTriggerImpact to check the chain before wiring it.

Verify by EXECUTING the sync, twice

Don't just read the table schema back — RUN the poller tool and watch it work, because the two things most likely to be wrong (does it actually pull records? is the dedup real?) only show up when you execute it:

• Run the sync tool once (execute the AUTESSA_SCRIPT tool, or its scheduled job's tool directly — don't wait for the cron), then autessa.autessadb.query.query the table and confirm real rows landed with populated external ids.
• Run it a SECOND time on the same source window and confirm the row COUNT did not grow — db.upsertByExternalId should report all updated and zero inserted the second time. A second run that duplicates rows means the external-id dedup is not keyed correctly; fix it before the step is done. Clean up any clearly-marked test rows you created just to prove the sync, unless they are real records you actually want mirrored.

See also

• connectors — the auth model and read/post surface of the connector you are syncing.
• automations — scheduled events and triggers in depth.
• data-placement — why the mirror is an AutessaDB table.

Related

• Autessa SDK overview