Postgres sync quick start

In this guide, we will walk you through everything needed to keep a Postgres mirror of your WorkOS data current:

• Create the schema in your database
• Backfill the objects that already exist
• Tail the events API to stay current
• Query the mirror and join it to your own tables
• Reconcile the mirror to detect and repair drift

To get the most out of this guide, you’ll need:

• A WorkOS account and an API key for the environment you want to mirror
• A Postgres database, version 12 or newer, that the engine can create a schema in
• Node.js 22 or newer, or a container runtime

npm install @workos/sync-postgres

The package includes both a library and a workos-sync-postgres command; its source is at workos/sync-engine. The same command ships as a container image for linux/amd64 and linux/arm64, if you would rather not install Node:

docker run --rm -e DATABASE_URL -e WORKOS_API_KEY ghcr.io/workos/sync-postgres init

Image tags are latest, a version such as 0.2.0, beta for prereleases, and sha-<commit>.

The command reads its settings from the environment. Provide the connection string for your database and the API key for the environment you want to mirror. Store these values as managed secrets.

DATABASE_URL='postgres://localhost:5432/app'
WORKOS_API_KEY='sk_example_123456789'

Every row is labeled with an environment name in its _env column, which defaults to production. If the key is for a different environment, set WORKOS_ENVIRONMENT_NAME before the first write. The label is part of every primary key and must not change once you have data.

WORKOS_ENVIRONMENT_NAME='staging'

npx workos-sync-postgres init

This creates a workos schema containing:

• one table per synced object
• a _live view beside every mirror table
• four bookkeeping tables: _sync_state for cursors and backfill checkpoints
• _migrations for the applied schema version
• _sync_lease for which process is tailing each environment
• _rejected for events the tail could not apply.

The command is safe to run repeatedly, including on every deploy. The schema is fingerprinted, so a second run with no changes does nothing. To use a different schema name, set WORKOS_SYNC_SCHEMA or pass --schema.

npx workos-sync-postgres backfill

Backfill pages the REST list endpoints and writes every object that currently exists. Before fetching the first page, it records your position in the event stream, so anything that changes while backfill runs is applied afterwards rather than missed.

Backfill checkpoints after every page. If it is interrupted, resume it from the last checkpoint instead of starting over:

npx workos-sync-postgres backfill --resume

Large directories take time. The /directory_users endpoint is limited to 4 requests per second per directory, and that limit does not aggregate across directories, so the engine paces itself to roughly 400 directory users per second at best. Backfill is the only step that issues a large number of API calls, so try it against a staging environment first.

npx workos-sync-postgres listen

The engine now polls the events API from its stored cursor and applies each change as it arrives. Leave it running as a service. It finishes its current batch on SIGINT or SIGTERM, and only one process tails a given environment at a time: a second listen for the same environment waits for the first to release its lease, which is what a rolling deploy needs.

If a few minutes of lag is acceptable, tick does one poll and exits, which suits a cron job instead of a long-running process:

*/5 * * * * workos-sync-postgres tick

Two conventions apply to every query against the mirror.

Read the _live views for live rows. Deleted objects are tombstoned in the base table rather than removed, so workos.users includes rows with _deleted_at set while workos.users_live does not. Read the base table when you want to see what was deleted and when.

Join on _env as well as the id. Object ids are unique within an environment, and one database can mirror several.

SELECT o.name AS organization, u.email, m.role_slug
  FROM workos.organization_memberships_live m
  JOIN workos.users_live u ON u.id = m.user_id AND u._env = m._env
  JOIN workos.organizations_live o ON o.id = m.organization_id AND o._env = m._env
  ORDER BY o.name, u.email;

The mirror is most useful joined to data WorkOS does not have. Set an external ID on your WorkOS users to your own account id, and it becomes the join key:

SELECT u.email,
       u.metadata->>'plan' AS plan,
       COUNT(s.id) AS sessions_30d
  FROM workos.users_live u
  JOIN app.accounts a ON a.id = u.external_id
  LEFT JOIN app.sessions s ON s.account_id = a.id
       AND s.created_at > NOW() - INTERVAL '30 days'
  GROUP BY u.email, u.metadata->>'plan'
  ORDER BY sessions_30d DESC;

Authentication events are worth calling out. They have no REST endpoint anywhere in the WorkOS API, so once mirrored, the sign-in history exists only in your database. The auth_attempts table is append-only, so it has no _live view and nothing to filter:

SELECT email, COUNT(*) AS failures, COUNT(DISTINCT ip_address) AS distinct_ips
  FROM workos.auth_attempts
  WHERE status = 'failed' AND created_at > NOW() - INTERVAL '1 hour'
  GROUP BY email
  HAVING COUNT(*) >= 5
  ORDER BY failures DESC;

To answer “what happened to this object” without an API call, read the event archive, which is an ordered local log:

SELECT created_at, event, context->>'actor' AS actor
  FROM workos.events
  WHERE object_id = 'user_01EHZNVPK3SFK441A1RGBFSHRT'
  ORDER BY created_at;

Even with an ordered, replayable event stream, a mirror can drift: a bug in a release, an incident on either side, or an object whose delete event was never emitted. Reconciliation compares the set of ids in the mirror against the WorkOS API and reports the differences in both directions.

npx workos-sync-postgres reconcile

The report has two numbers per stream, and they mean different things. localOnly is rows you have that no longer exist upstream: missed deletes. remoteOnly is rows upstream that you do not have: missed creates, or a backfill that did not finish. The command exits with status 2 when it finds drift, so it works as a scheduled check.

To repair what it finds, which tombstones the localOnly rows and inserts the remoteOnly ones:

npx workos-sync-postgres reconcile --repair

Daily is a reasonable cadence for the report and weekly for --repair, plus a run after any incident that affected the sync. See the reconciliation schedule for details.

The same engine runs as a library, so you can reuse the database connection you already have instead of running a separate process:

import { SyncEngine, PostgresDestination } from '@workos/sync-postgres';

const engine = new SyncEngine({
  destination: new PostgresDestination({
    databaseUrl: process.env.DATABASE_URL,
  }),
  environments: [{ name: 'production', apiKey: process.env.WORKOS_API_KEY! }],
});

await engine.backfill();
await engine.listen(); // resolves when you call engine.stop()

Every method applies the schema before it runs, so there is no separate init() call to remember. It exists if you want to create the schema ahead of time, for example during a deploy.

To reuse an existing pg pool instead of a connection string:

import { fromPgPool, PostgresDestination } from '@workos/sync-postgres';

new PostgresDestination({ db: fromPgPool(pool) });

To project a field that is not yet a typed column, such as a custom directory attribute, pass it as an extra column. The next schema apply adds it as a generated column computed from data already in _raw, with no backfill and no downtime. Until then, the field is still queryable as _raw->'custom_attributes'->>'department'.

const engine = new SyncEngine({
  // ...
  extraColumns: {
    directory_users: [
      {
        name: 'department',
        type: 'text',
        path: ['custom_attributes', 'department'],
        index: true,
      },
    ],
  },
});

Column types are text, boolean, bigint, numeric, jsonb, and timestamptz.

A workspace can hold many environments, and their display names repeat across projects, so give each one a stable label of your own. It is written to every row as _env and must not change once you have data.

On the command line, run one process per environment into the same database. Each process keeps its own cursor under its label, so they never collide:

WORKOS_API_KEY=sk_live_... WORKOS_ENVIRONMENT_NAME=production npx workos-sync-postgres listen
WORKOS_API_KEY=sk_test_... WORKOS_ENVIRONMENT_NAME=staging    npx workos-sync-postgres listen

In the library, one engine can hold several:

const engine = new SyncEngine({
  // ...
  environments: [
    { name: 'production', apiKey: process.env.WORKOS_PROD_KEY! },
    { name: 'staging', apiKey: process.env.WORKOS_STAGING_KEY! },
  ],
});

Cursors are not comparable across environments, and neither are object ids, which is why _env is part of every primary key.

The mirror is eventually consistent, typically within seconds. Read from it for lists, joins, aggregates, dashboards, and analytics. Continue to read from the session token for authorization decisions, and from the API response you already have for an object you just created, because a new user may not have reached the mirror yet. The sync is one-directional: send writes to the WorkOS API and let them flow back through the event stream.

A stalled sync produces no errors. The tables simply stop changing while every query continues to return plausible results, and once the cursor falls outside the 90-day event retention window, the gap can no longer be replayed. Alert when pollAgeSeconds from status or /healthz climbs past a few minutes. Operations covers the full set of signals.

If you want lower latency than the poll interval, configure a webhook and use a verified delivery to prompt an immediate poll. State still flows through the ordered events API, so you keep the ordering and replay guarantees while giving up nothing on latency.

import { verifyWebhook, WebhookVerificationError } from '@workos/sync-postgres';

app.post('/workos/webhook', async (req, res) => {
  try {
    verifyWebhook({
      payload: req.rawBody,
      signature: req.header('WorkOS-Signature'),
      secret: process.env.WORKOS_WEBHOOK_SECRET!,
    });
  } catch (err) {
    if (err instanceof WebhookVerificationError) return res.sendStatus(400);
    throw err;
  }
  res.sendStatus(200);
  await engine.processWebhook();
});

A mirror carries the same deletion obligations as any other copy of the data, including the archived event payloads. redact hard-deletes a user’s rows across every mirrored table and removes matching rows from the event archive, rather than leaving a tombstone:

npx workos-sync-postgres redact --user user_01EHZNVPK3SFK441A1RGBFSHRT

See deletion requests for what it does and does not reach.