Integrating ClickHouse with Lemlist

The Lemlist registry item copies a small API reader and raw ClickHouse tables into a chkit project.

Install

bunx chkit add lemlist
bunx chkit check
bunx chkit generate --name add_lemlist
bunx chkit migrate --apply
bunx chkit ingest run --tag provider:lemlist

Set LEMLIST_API_KEY in the runtime environment before the ingestion run. Edit src/integrations/lemlist/index.ts to select activities, campaigns, or both.

Keep each stream ID and destination tied to one account. The reader does not resolve the key’s account identity; switching accounts requires new stream IDs and destinations so previous watermarks cannot skip history.

Resource	Default ClickHouse table	Records synced	API reference
Activities (`activities`)	`lemlist_activities_raw`	Outreach activity events	`GET/activities`
Campaigns (`campaigns`)	`lemlist_campaigns_raw`	Campaign metadata	`GET/campaigns`

Sync behavior

Activities use inclusive minDate/maxDate filters on createdAt, thirty-day intervals, and one day of overlap. Each completed interval checkpoints only after its pages have destination acknowledgement. A failed interval starts at offset zero; an explicit backfill resumes its completed interval frontier in an isolated namespace. Empty intervals also commit progress. Offset pages remain mutable, so their offsets are never persisted as change cursors.

The activities API can backfill metadata on older activities. Periodic historical replay refreshes such changes and late arrivals beyond the overlap. Campaigns remain full scans with creation-time ordering and a successful-scan completedAt checkpoint; creation-only incremental reads would miss campaign updates.

bunx chkit ingest run --tag provider:lemlist --tag resource:activities --backfill reconcile-2026-10-04 --from 2000-01-01
bunx chkit ingest status --tag provider:lemlist --json

Use a new backfill ID for each complete reconciliation; reusing an ID resumes that backfill. Narrowing a reused backfill’s upper bound below its committed frontier is rejected. Removed campaigns and activities remain stored. The raw tables preserve provider fields and require ClickHouse 25.3 or later. See the installed README and registry installation.

Test the reader

bunx chkit add lemlist --with-tests
bun test src/integrations/lemlist/tests/basic.test.ts

Changelog

Version 0.2.0

Sync activities through bounded, overlapping creation-time intervals and advance checkpoints only after all interval pages are acknowledged.
Replay interrupted intervals from offset zero and record successful campaign full scans without treating creation time as a modification cursor.
Preserve raw destinations and include fixtures for empty intervals, backfills, and source or destination failures.

Version 0.1.0

Introduce raw campaigns and activities ingestion.