Skip to main content
WorkOS provides authentication and identity APIs for B2B applications: user management and sign-in, single sign-on, directory sync (SCIM), and roles and permissions. An application built on WorkOS keeps its users, organizations, memberships and sign-in activity in WorkOS and reads them through the WorkOS API. The WorkOS Sync Engine is an open-source tool, maintained by WorkOS, that copies that data into a database you run and keeps it current. Its ClickHouse destination, @workos/sync-clickhouse, solves two problems:
  • Sign-in history that outlives WorkOS’s retention. WorkOS keeps events for 90 days, and the authentication.* events (every sign-in, success or failure) have no list endpoint. The engine tails the Events API into append-only ClickHouse tables, so the history is yours for as long as you keep it.
  • Joins with the rest of your analytics. Users, organizations, memberships, directories, SSO connections and roles are mirrored next to the event tables, so a question such as “failed sign-ins per organization per hour” is one query instead of several paginated API calls.
This page covers connecting the engine to ClickHouse Cloud and to self-hosted ClickHouse, authentication, an end-to-end example, and the limits to plan for.

How it works

The engine runs in your infrastructure, as a Node.js command or a Docker container, and connects to ClickHouse over the HTTP interface with the official JavaScript client. It does three things:
  1. Backfill loads current state from the WorkOS REST list endpoints.
  2. Tail polls the WorkOS Events API from a durable cursor and applies each event.
  3. Reconcile compares ids against the API and repairs anything the tail missed.
It creates a workos database containing: The engine writes one String column, _raw, holding the JSON payload. Every other column is a DEFAULT expression over _raw, so a field WorkOS adds later is queryable with JSONExtractString(_raw, 'field') without a schema change.

Prerequisites

  • A WorkOS account and an API key for the environment to mirror.
  • Node.js 22 or later, or Docker. The engine is published as the npm package @workos/sync-clickhouse and as the container image ghcr.io/workos/sync-clickhouse.
  • ClickHouse Cloud, or self-hosted ClickHouse 23.11 or later.
  • For self-hosted ClickHouse, a ClickHouse Keeper or ZooKeeper that the server can reach, with keeper_map_path_prefix set. See Connect to self-hosted ClickHouse. ClickHouse Cloud has both already.
  • Network access from the host running the engine to the ClickHouse HTTP interface (port 8443 on Cloud; 8123, or 8443 with TLS, on self-hosted).

Setup

Connect to ClickHouse Cloud

To connect to ClickHouse with HTTP(S) you need this information: The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console. Select a service and click Connect:
ClickHouse Cloud service connect button
Choose HTTPS. Connection details are displayed in an example curl command.
ClickHouse Cloud HTTPS connection details
If you’re using self-managed ClickHouse, the connection details are set by your ClickHouse administrator. With those details in hand:
  1. Add the address the engine connects from to the service’s IP access list. A refused address looks like a connection timeout, not an authentication error.
  2. Put the credentials in front of the HTTPS endpoint to form the connection string, and export it with your WorkOS API key:
    Percent-encode the password if it contains characters that are reserved in a URL. A path in the URL is ignored: the mirror lives in the workos database, or the one named by WORKOS_SYNC_SCHEMA.
  3. Create the schema, load current state, and start the tail:
There is nothing to enable on the service. init creates its KeeperMap tables like any other.

Connect to self-hosted ClickHouse

  1. Enable KeeperMap. The engine keeps its cursor and lease in KeeperMap tables, which need a Keeper and a path prefix in the server configuration. On a cluster that already uses ClickHouse Keeper or ZooKeeper, add the prefix:
    /etc/clickhouse-server/config.d/keeper_map.xml
    A single node with no Keeper also needs the embedded Keeper turned on:
    /etc/clickhouse-server/config.d/keeper_map.xml
    Restart the server after adding the file.
  2. Point the connection string at the HTTP interface: port 8123 without TLS, or 8443 with it.
  3. Run the same three commands as for Cloud: init, backfill, then listen.

Run it as a container

The container image takes the same environment variables and commands:
Run listen as a long-lived process, or run tick (one poll, then exit) from a scheduler. status reports the cursor, the time since the last successful poll and per-stream row counts. The package README lists every command, flag and environment variable.

Authentication

The user the engine connects as needs CREATE DATABASE the first time (or an existing database it may create tables in), plus CREATE TABLE, CREATE VIEW, DROP TABLE, ALTER, INSERT and SELECT on that database. ALTER covers the lightweight DELETE that the redact command runs. The default user is enough for an evaluation. For production, create a dedicated user with those grants; see Cloud access management. On the WorkOS side, the engine authenticates with one API key per environment. Dedicate a key to the sync so that its requests can be told apart from your application’s. The User-Agent header of every connection starts with workos-sync-engine, so the engine’s statements can be found by http_user_agent in system.query_log.

Example: failed sign-ins per organization

The engine’s input is your own WorkOS environment rather than a file, so this example uses that data instead of a sample dataset. Any WorkOS environment with a few users and sign-ins works, including a staging environment.
  1. Create the schema and load current state:
  2. Start the tail and leave it running. Sign-in history begins now: there is no endpoint that lists past sign-ins, so backfill can’t recover them.
  3. Sign in to your application a few times, including at least one failed attempt.
  4. Confirm that the mirror has rows, from the Cloud SQL console or clickhouse client:
  5. Join the sign-in log to the mirror tables:
Every row carries an _env column, production unless WORKOS_ENVIRONMENT_NAME says otherwise, so several WorkOS environments can share one database. Join on it as shown.

Known limits

  • The mirror is a read model. It is eventually consistent, usually within seconds, and one-directional. Keep writes and authorization decisions on the WorkOS API.
  • Sign-in history starts when the tail starts. WorkOS keeps events for 90 days. A tail that was stopped for less than that resumes from its cursor. One stopped for longer needs backfill and reconcile --repair to restore current state, and the sign-ins in the gap are gone.
  • One writer per environment. A second listen waits for the first one’s lease. tick exits with code 3 while a listener holds it.
  • listen keeps a Cloud service awake. It polls every 5 seconds by default, so the service never idles. Run tick on a schedule to let the service idle between runs, at the cost of a wake per run.
  • Small inserts by default. Each poll applies up to 100 events as a few asynchronous inserts that the engine waits for. Raise WORKOS_SYNC_MAX_EVENTS_PER_TICK on a busy environment so that each poll produces fewer, larger inserts.
  • The _live views read with FINAL. They suit analytical queries. For an application that reads single rows by key, the Postgres or MySQL destination is a better fit.
  • The mirror tables keep the newest version of each row only. Older versions survive until the next merge. workos.events is where history is kept.
  • Columns are projected from JSON. A value that a typed column can’t cast is NULL in that column and still present in _raw.
  • No ON CLUSTER DDL. init has been run against ClickHouse Cloud and single-node open source. A self-managed multi-node cluster isn’t part of the test matrix.

Troubleshooting

The first command times out

On ClickHouse Cloud, the address the engine runs from is missing from the IP access list, or the network blocks outbound traffic on port 8443. Check the port from the same host:
Ok. means the service is reachable from that host. A command against an idle service also spends its first seconds waiting for the service to wake. The engine allows two minutes per request, so that isn’t a failure.

KeeperMap is disabled because 'keeper_map_path_prefix' config is not defined

init on a self-hosted server that has no keeper_map_path_prefix. Add the configuration in Connect to self-hosted ClickHouse and restart the server.

unsupported ClickHouse URL scheme

The connection string must start with http:// or https:// and point at the HTTP interface. A clickhouse:// URL, or the native protocol’s port, isn’t accepted.

A statement from init --print-sql fails with Could not find table

init --print-sql prints the DDL instead of running it. Applied by hand to a service with more than one replica, one statement per request, a CREATE VIEW can reach a replica before the CREATE TABLE it selects from. Run the script again: every statement in it is idempotent. The engine’s own init isn’t affected.