Integrating ClickHouse with Lemlist
The Lemlist registry item copies a small API reader and raw ClickHouse tables into a chkit project.
Install
Section titled “Install”bunx chkit add lemlistbunx chkit checkbunx chkit generate --name add_lemlistbunx chkit migrate --applybunx chkit ingest run --tag provider:lemlistSet 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
Section titled “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-01bunx chkit ingest status --tag provider:lemlist --jsonUse 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
Section titled “Test the reader”bunx chkit add lemlist --with-testsbun test src/integrations/lemlist/tests/basic.test.ts