# Sync data to an external database ## Introduction 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: ```sql title="Members and roles by organization" 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](https://github.com/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](https://workos.com/docs/events/data-syncing/database/postgres-quick-start). This page explains what the mirror is, how it stays correct, and what it does and does not guarantee. *** ## The mirror is a read model 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. | Need | Read from | | ----------------------------------- | ------------------------------------------------------ | | An authorization decision | The [session token](https://workos.com/docs/authkit/sessions) | | A change you just made | The API response you already have | | The current state of one object | The WorkOS API, or the mirror if seconds of lag is fine | | Lists, filters, joins, aggregates | The mirror | | Anything joined to your own tables | The mirror | | Dashboards, analytics, reporting | The mirror | | Agent or LLM access | The mirror, through a read-only database role | | Sign-in history | The mirror, because it exists nowhere else | 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. *** ## How it works Keeping a correct copy of another system's data takes three subsystems: | Subsystem | What it does | Why it exists | | ------------- | -------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Backfill** | Pages the REST list endpoints and writes everything that currently exists. | The events API retains 90 days of history. Everything older exists only through the list endpoints. | | **Tail** | Polls the [events API](https://workos.com/docs/events/data-syncing/events-api) from a durable cursor and applies changes. | Events arrive in a consistent order, the stream is replayable from any cursor, and your process sets its own pace. No public endpoint is required. | | **Reconcile** | Compares the set of ids in the mirror against the API. | It is the only mechanism that can find a missed delete. A deleted object is absent from every list endpoint, so re-listing can never reveal that it is gone. | 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 schema 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. | Column | Type | Meaning | | ------------- | ------------- | ---------------------------------------------------------------------------------- | | `_env` | `text` | The environment label from your configuration. Part of every primary key. | | `_raw` | `jsonb` | The object exactly as WorkOS returned it. The only column the engine writes. | | `_updated_at` | `timestamptz` | When the object changed in WorkOS. Used to reject stale writes. | | `_synced_at` | `timestamptz` | When the row was written. Lag is the difference between the two. | | `_deleted_at` | `timestamptz` | Tombstone. `null` means the object is live. Append-only tables omit this column. | Every other column is `GENERATED ALWAYS AS ... STORED`, projected out of `_raw`, including the primary key: ```sql title="workos.users" 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 `_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. *** ## The write path 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. *** ## What it mirrors 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. > **Note:** Authentication events have no REST endpoint anywhere in the WorkOS API. There > is no way to list sign-ins after the fact, so if you do not capture them as > they happen, that history is gone after 90 days. Once mirrored, "which > accounts had five failed sign-ins in the last hour" is a `GROUP BY` over > `workos.auth_attempts`. Credentials never reach the mirror. Invitation acceptance tokens and webhook signing secrets are stripped before any row is written. ### Known gaps Some streams cannot be complete, because of the shape of the upstream API. | Stream | Limitation | | -------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `sessions` | Only active sessions are listable, and only per user. Ended sessions older than the event window are unrecoverable. | | `auth_attempts` | No REST endpoint exists. History begins when you start syncing. | | `organization_domains` | No standalone list endpoint; sourced from the organization payload. `reconcile` cannot check it. | | `group_memberships`, `directory_group_memberships` | Event-only. Memberships that existed before you started syncing are not mirrored, and `reconcile` cannot check them. | | `directories` | No `dsync.updated` event exists. A directory moving from `linked` to `invalid_credentials` emits nothing; only `reconcile` sees the change. | | `fga_resources`, `webhook_endpoints` | No events. Opt-in, and only as fresh as your last `reconcile`. | ### What is not mirrored | Object | Why | | ------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------- | | SSO profiles | No list endpoint, no get-by-id, and no events. Only returned during a live sign-in. | | Audit log events | Not listable, and they emit no events. Use [exports](https://workos.com/docs/audit-logs/exporting-events) or [log streams](https://workos.com/docs/audit-logs/log-streams) instead. | | Radar block and allow lists | Write-only endpoints. | | Vault values | Should not leave Vault. Vault activity events can be archived to `workos.events`. | | Portal links and widget tokens | Ephemeral credentials, not state. | *** ## Guarantees **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. ### What is not guaranteed **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](https://workos.com/docs/authkit/sessions), 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. *** ## Next steps - [Quick start](https://workos.com/docs/events/data-syncing/database/postgres-quick-start): install the engine, create the schema, backfill, and start syncing. - [Operations](https://workos.com/docs/events/data-syncing/database/operations): what to monitor, what to alert on, how to deploy, and how to recover.