All posts

3 min read

Your sign-in provider is not your user table

Keeping a hosted identity provider and your own Postgres in step: idempotent webhook handling, events that arrive out of order, and a nightly reconcile.

A hosted identity provider is a good deal. Someone else handles passwords, multi-factor, social sign-in and the security patches. What it is not is the place your app's data lives. Your projects, invoices and audit logs need a user row they can point at, in your own database, with a foreign key.

So you end up with two copies of every person: the provider's and yours. This post is about keeping them in step. The examples use Fieldnote, a made-up SaaS with organizations, teams and projects, and plain Postgres.

Who owns what

The first decision is the one that prevents most of the bugs: each field has exactly one owner.

  • The provider owns identity. The email address, whether it is verified, the display name the person set, and whether the account is blocked.
  • Your database owns everything else. Which organizations someone belongs to, their team memberships, their preferences, and every record that references them.

Your copy of a provider-owned field is a cache. You never edit it in your app and push it back; you change it at the provider and let the change flow down. That one-way rule is what makes the rest of this tractable.

The local table

SQL
create table users (
  id            uuid primary key default gen_random_uuid(),
  provider_id   text not null unique,
  email         text not null,
  email_verified boolean not null default false,
  name          text,
  blocked       boolean not null default false,
  -- The provider's own timestamp for the version of the profile we hold.
  synced_at     timestamptz not null,
  deleted_at    timestamptz
);

Two columns do the heavy lifting. provider_id is the join key to the provider, unique so a duplicate can never be inserted. synced_at records which version of the profile this row reflects, using the provider's clock rather than yours.

Webhooks arrive more than once

Providers deliver change events by webhook, and every one I have used promises at least once, not exactly once. A timeout on your side, a retry on theirs, and the same event lands twice.

The fix is to make handling an event safe to repeat. Record each event's id, and do the work in the same transaction:

SQL
create table processed_events (
  event_id     text primary key,
  processed_at timestamptz not null default now()
);
TypeScript
export async function handleUserEvent(event: ProviderEvent) {
  await db.transaction(async (tx) => {
    const fresh = await tx.query(
      "insert into processed_events (event_id) values ($1) on conflict do nothing returning event_id",
      [event.id],
    );
    if (fresh.rowCount === 0) return; // Seen it already: nothing to do.

    await applyUserEvent(tx, event);
  });
}

If the handler crashes halfway, the transaction rolls back, the event id is never recorded, and the retry does the whole thing again. If it succeeds, the retry finds the id and stops. Either way the result is the same as handling it once.

Answer the webhook with a 2xx as soon as the event is stored, and do slow work afterwards. A handler that takes too long gets retried while it is still running, which is how duplicates are born.

Events arrive out of order

The subtler problem: two updates to the same person can arrive in the wrong order. Someone changes their name, then changes it again a second later, and the deliveries cross. Apply them as they arrive and you keep the older name forever.

This is what synced_at is for. Every event carries the time the provider made the change. Apply an update only if it is newer than what you hold:

SQL
insert into users (provider_id, email, email_verified, name, blocked, synced_at)
values ($1, $2, $3, $4, $5, $6)
on conflict (provider_id) do update
  set email = excluded.email,
      email_verified = excluded.email_verified,
      name = excluded.name,
      blocked = excluded.blocked,
      synced_at = excluded.synced_at
  where users.synced_at < excluded.synced_at;

The where clause makes a stale event a no-op. It also means a create and an update can arrive in either order: whichever lands first inserts the row, and the other only wins if it is newer.

Deletes are tombstones

When someone deletes their account at the provider, you rarely want to delete the row. Their projects and audit entries still reference it, and a hard delete either cascades through data you need to keep or fails on the foreign key.

Set deleted_at instead, clear what you are obliged to clear, and treat a deleted user as signed out everywhere. A tombstone also stops a late, out-of-order update from resurrecting the account:

SQL
update users
   set deleted_at = $2, email = 'deleted+' || id || '@invalid', name = null, synced_at = $2
 where provider_id = $1 and synced_at < $2;

A nightly reconcile catches the rest

Webhooks fail in ways retries do not cover: an endpoint misconfigured for an afternoon, an event type you forgot to subscribe to, a provider incident. Idempotency and ordering guard the events you receive; they do nothing for the ones you never got.

So once a night a job pages through the provider's user list and compares it with the local table. Anything newer at the provider goes through the same upsert as a webhook would. Anything that exists locally but not at the provider gets a tombstone. Because it reuses the event path, it cannot introduce a second way of writing a user row.

The reconcile should usually find nothing. When it finds something, log it loudly: it means a webhook went missing, and you want to know why.

What this buys

The provider keeps doing the part it is good at, and your database stays the one place every query in the app can trust. The rules that make it work are short: one owner per field, every event safe to repeat, newer always wins, deletes are tombstones, and a reconcile for whatever slipped through.

Want the work behind the notes?

The projects show the same habits in code you can open and run.