Sync data to an external database

The WorkOS sync engine mirrors your WorkOS data into a Postgres database you control. Users, organizations, memberships, directory data, and more become ordinary tables that you can query with SQL and join to your own application data:

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 engine is open source at workos/sync-engine and ships as the @workos/sync-engine npm package. We also have an existing implementation for Postgres as @workos/sync-postgres, which includes both a library and a workos-sync-postgres command, and as a container image. To set it up, continue to the quick start.

This page explains what the mirror is, how it stays correct, and what it does and does not guarantee.

The mirror is eventually consistent, typically within seconds of a change in WorkOS. It is not the system of record, and it must not be used as one for authorization decisions or for reading back a change you just made.

Writes are one-directional. Send changes to the WorkOS API and let them flow back into the mirror through the event stream. There is no write-back.

Keeping a correct copy of another system’s data takes three subsystems:

The lifecycle runs in this order:

1. Capture the current position in the event stream.
2. Backfill by paging the list endpoints for everything that exists.
3. Drain by replaying events from the captured position.
4. Tail by polling continuously as the sole writer, checkpointing after each durable write.

Steps 2 and 3 overlap on purpose. Anything that changes while backfill runs is replayed afterwards, and the write path decides which version wins. Because every write is idempotent, the overlap costs nothing, while trying to make the two phases meet exactly is how gaps get created.

The engine creates a workos schema with one table per synced object. Every table has the same five columns, and the engine only ever writes one of them.

Every other column is GENERATED ALWAYS AS ... STORED, projected out of _raw, including the primary key:

CREATE TABLE workos.users (
  _env        text NOT NULL,
  _raw        jsonb NOT NULL,
  _updated_at timestamptz NOT NULL,
  _synced_at  timestamptz NOT NULL,
  _deleted_at timestamptz,
  id          text  GENERATED ALWAYS AS ((_raw->>'id')) STORED,
  email       text  GENERATED ALWAYS AS ((_raw->>'email')) STORED,
  external_id text  GENERATED ALWAYS AS ((_raw->>'external_id')) STORED,
  metadata    jsonb GENERATED ALWAYS AS ((_raw->'metadata')) STORED,
  PRIMARY KEY (_env, id)
);

This design has three consequences:

A new field in the WorkOS API cannot break your sync. It is queryable as _raw->>'field' the moment it appears, and promoting it to a typed column is a single ALTER TABLE ... ADD COLUMN, computed from rows already present, with no backfill. The extraColumns option does this without editing the engine.

Deleted objects are tombstoned, not removed. _deleted_at is set and the last known payload is kept, because it is the only remaining record of what was deleted. Every mirror table also gets a generated <table>_live view, such as workos.users_live, that is the table minus its tombstoned rows, so the common query needs no _deleted_at filter.

There are no foreign keys. Events routinely arrive with children before parents: a group.user_added can land before the user.created it refers to. A foreign key would turn that ordering artifact into a stuck stream.

The mirror can hold several WorkOS environments at once. Object ids are unique within an environment, which is why _env is part of every primary key and why queries join on _env as well as the id.

Every write goes through one upsert, and the decisions encoded in it are what make out-of-order and duplicate delivery safe.

Stale writes are rejected. A write whose source timestamp is older than the stored row is skipped. The comparison accepts an equal timestamp, because two events can genuinely carry the same one, and a strict comparison would drop the second permanently.

The timestamp comes from WorkOS, never from the database. _updated_at is the object’s own updated_at, or the event’s created_at when the object has none.

Event writes merge; REST writes replace. Most event payloads are the full API object. Not every one is, though: deprecated fields are left out of event payloads, and a few objects are shaped differently in events than in the API. Replacing the whole row on an event that omits a field would silently null that column, so event writes shallow-merge into _raw, while backfill and refresh writes, which carry the complete API object, replace it.

New objects are complete immediately. A create event carries the full object, so the row is complete as soon as the event is applied. The engine can also re-fetch an object when its event arrives (hydration), which is off by default because it costs a request per event and rarely changes anything.

Idempotency lives in the primary key. A redelivered event is a no-op by construction. There is no event-id table to grow, prune, or get wrong.

Deletes always land, and are never undone. A delete is not subject to the staleness check, and a late updated event after a delete refreshes the payload but does not resurrect the row. WorkOS ids are never reused, so this is safe. Memberships are the one exception, because a member being re-added is a real event.

By default the engine mirrors every object that emits events:

• Identity: users, organizations, organization memberships, organization domains, invitations, and sessions
• Directory Sync and SSO: directories, directory users, directory groups, directory group memberships, and SSO connections
• Authorization: groups, group memberships, roles, organization roles, permissions, and feature flags
• Logs: every event, verbatim, in workos.events, and every authentication.* event in workos.auth_attempts

Two further streams have no events and are opt-in: FGA resources and webhook endpoints. They are refreshed only when you run reconcile, and are exactly that fresh. Run workos-sync-postgres streams to list every stream with its group, mode, and whether it is on by default.

Credentials never reach the mirror. Invitation acceptance tokens and webhook signing secrets are stripped before any row is written.

Some streams cannot be complete, because of the shape of the upstream API.

Convergence. Every write is idempotent and gated on a source-derived timestamp, so the mirror converges on current WorkOS state regardless of the order events arrive, how many times they are delivered, or when the process restarts.

Durability of what it has seen. The cursor is never advanced before the writes it covers are committed. A crash mid-batch replays that batch rather than skipping it.

No silent loss on unknown input. Every event is archived verbatim before it is routed. An event type the engine does not recognize is stored and reported, never dropped and never thrown.

Detectable drift. reconcile compares the mirror against the API and reports differences in both directions.

Read-your-writes. If you create a user through the API and immediately query the mirror, the row may not be there yet.

Authorization decisions. A mirror row can be stale. Roles and permissions belong in the session token, which is signed and current. Use the mirror to analyze authorization, not to enforce it.

Business truth. The mirror faithfully reflects what WorkOS says. A membership with status = 'active' means active in WorkOS. Whether that person should still have access to your product is your question, answered by your data.

• Quick start: install the engine, create the schema, backfill, and start syncing.
• Operations: what to monitor, what to alert on, how to deploy, and how to recover.