# Postgres sync quick start ## What you'll build 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 ## Before getting started To get the most out of this guide, you'll need: - A [WorkOS account](https://dashboard.workos.com/) 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 > Each API key is scoped to a single environment. To mirror both staging and production, you need a key for each. See [Mirror several environments](#mirror-several-environments). *** ## (1) Install the sync engine ```bash title="Install the sync engine" npm install @workos/sync-postgres ``` The package includes both a library and a `workos-sync-postgres` command; its source is at [workos/sync-engine](https://github.com/workos/sync-engine). The same command ships as a container image for `linux/amd64` and `linux/arm64`, if you would rather not install Node: ```bash title="Run from the container image" 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-`. ### Set secrets 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. ```plain title="Environment variables" 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. ```plain title="Label rows for a staging key" WORKOS_ENVIRONMENT_NAME='staging' ``` *** ## (2) Create the schema ```bash title="Terminal" 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`. *** ## (3) Backfill existing data ```bash title="Terminal" 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 is not optional. The events API returns events up to 90 days old, so everything older exists only through the list endpoints. Backfill checkpoints after every page. If it is interrupted, resume it from the last checkpoint instead of starting over: ```bash title="Terminal" 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. *** ## (4) Start syncing ```bash title="Terminal" npx workos-sync-postgres listen ``` The engine now polls the [events API](https://workos.com/docs/reference/events) 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: ```bash title="Poll every five minutes from cron" */5 * * * * workos-sync-postgres tick ``` *** ## (5) Query the mirror 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. ```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 mirror is most useful joined to data WorkOS does not have. Set an [external ID](https://workos.com/docs/authkit/metadata) on your WorkOS users to your own account id, and it becomes the join key: ```sql title="Identity data joined to product usage" 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: ```sql title="Accounts with repeated sign-in failures" 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: ```sql title="Change history for one user" SELECT created_at, event, context->>'actor' AS actor FROM workos.events WHERE object_id = 'user_01EHZNVPK3SFK441A1RGBFSHRT' ORDER BY created_at; ``` *** ## (6) Reconcile 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. ```bash title="Terminal" 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. > A missed delete cannot be found any other way. A deleted object is not returned by any list endpoint, so re-listing will never reveal that it is gone. Only a set difference finds it. To repair what it finds, which tombstones the `localOnly` rows and inserts the `remoteOnly` ones: ```bash title="Terminal" 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](https://workos.com/docs/events/data-syncing/database/operations#reconciliation-schedule) for details. *** ## Embed it in your app The same engine runs as a library, so you can reuse the database connection you already have instead of running a separate process: ```ts title="Embed the sync engine" 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: ```ts title="Reuse a pg pool" 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'`. ```ts title="Promote a field to a typed column" 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`. *** ## Mirror several environments 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: ```bash title="One listener per environment" 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: ```ts title="Several environments in one engine" 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. *** ## Best practices ### Treat the mirror as a read model 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](https://workos.com/docs/authkit/sessions) 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. ### Alert on poll age, not error rate 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](https://workos.com/docs/events/data-syncing/database/operations) covers the full set of signals. ### Use webhooks as a trigger, not a source If you want lower latency than the poll interval, configure a [webhook](https://workos.com/docs/events/data-syncing/webhooks) 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. ```ts title="Webhook as a trigger" 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(); }); ``` ### Handle deletion requests 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: ```bash title="Terminal" npx workos-sync-postgres redact --user user_01EHZNVPK3SFK441A1RGBFSHRT ``` See [deletion requests](https://workos.com/docs/events/data-syncing/database/operations#deletion-requests) for what it does and does not reach.