Keeping a local copy of users from another service: versioned push sync done right

The problem

The configurator engine doesn't own users. Identity lives in a central auth service: who exists, who can log in, who is an admin. The engine only receives a user id inside each token.

That was enough at first. Then the engine grew features that need to know about people:

• showing owner names and emails next to every configurator,
• managing teams (groups of users that own configurators together),
• showing "no access" for owners who were removed from the application,
• lists and admin screens that display dozens of users at once.

Calling the auth service for every screen meant N network calls to render a list, slower pages, and a hard dependency: if the auth service was slow, every screen with a name on it was slow too.

The engine needed its own copy of the users allowed into the application. But a copy is only useful if it stays correct, and copies have a long history of going subtly wrong.

The details

What can go wrong with a copy

• Out-of-order updates. Update v5 is sent, then v6. Because of retries or slow networks, v6 arrives first, then v5 overwrites it with older data.
• Missed updates. The engine is restarting when v7 is sent. v8 arrives later. Was something lost in between?
• Concurrent updates. Two updates arrive at the same moment and both apply. The final state depends on timing.
• Deletes that destroy history. A user leaves, the copy deletes them, and every ownership row pointing to them breaks or disappears.
• Silent staleness. Updates stop arriving and nobody notices for weeks.

Two ways to receive changes

• Push: the auth service calls the engine whenever its user list changes. It's fast and real-time, but it can be lost or arrive out of order.
• Pull: the engine asks the auth service for the current list. It's reliable on demand, but only as fresh as the last pull.

The design uses both: push for freshness, pull for repair.

The challenges

• Partial updates are fragile. "Add user X", "rename user Y", "remove user Z" events must all be applied, in order, with none missing, or the copy drifts forever.
• Versions must be compared atomically. Checking "is this newer than what I have?" and then writing is a race if two updates do it at the same time.
• Ownership must survive user removal. Removing a person from the application must never delete or block the records of what they owned.
• Operators need a health signal. If pushes stop, someone should be able to see it.

The solution

1. Always send the full list

The most important decision: every push contains the complete list of users, plus a version number:

{
  "version": 42,
  "users": [
    { "id": 7,  "name": "Sara", "email": "sara@example.com", "isRootAdmin": false },
    { "id": 12, "name": "Omar", "email": "omar@example.com", "isRootAdmin": true }
  ]
}

That removes a whole class of bugs. There are no partial events to apply in order. Each push is the truth at version 42. And a gap (receiving v8 after v5) is harmless: v8's full list already contains everything v6 and v7 changed.

The user lists here are in the hundreds, not millions, so sending everything costs almost nothing. That trade-off makes the system dramatically simpler.

2. One function applies every change

All writes to the copy go through a single replaceAll(version, users, source) method, used by both push and pull. There is no other way to modify the copy, so there's no second path to get wrong.

3. A row lock makes version checks atomic

The current version is stored in a single-row table (id = 1, with a CHECK constraint so a second row can never exist). Every apply starts by locking that row:

return db.$transaction(async (tx) => {
  await tx.$executeRaw`INSERT INTO users_sync (id) VALUES (1) ON CONFLICT DO NOTHING`;

  const [state] = await tx.$queryRaw<{ last_version: number }[]>`
    SELECT last_version FROM users_sync WHERE id = 1 FOR UPDATE`;

  const accepted = source === 'push'
    ? version >  state.last_version   // push: strictly newer only
    : version >= state.last_version;  // pull: equal is allowed, to repair

  if (!accepted) return { applied: false, previousVersion: state.last_version };

  // …apply the list (next steps)…
});

SELECT … FOR UPDATE means two pushes arriving together are applied one after the other. The second one waits, then reads the committed version. A slow, older push can never overwrite a newer list.

The acceptance rules are different on purpose:

And if a pull returns a lower version than stored, a newer push landed while we were pulling, so the copy is already fresher and is kept.

4. Upsert everyone, soft-remove the rest

Inside the same transaction:

for (const user of users) {
  const fields = { name: user.name, email: user.email, isRootAdmin: user.isRootAdmin, removedAt: null };
  await tx.appUser.upsert({
    where: { id: user.id },
    create: { id: user.id, ...fields },
    update: fields,
  });
}

// Anyone not in the list anymore is marked removed, never deleted
const { count: removed } = await tx.appUser.updateMany({
  where: { id: { notIn: users.map((u) => u.id) }, removedAt: null },
  data: { removedAt: new Date() },
});

await tx.usersSync.update({ where: { id: 1 }, data: { lastVersion: version, lastPushAt: new Date() } });

Three details:

• The copy uses the auth service's id as its primary key. No mapping table, and the same id is used everywhere: tokens, ownership rows, locks.
• removedAt instead of DELETE. A user who leaves keeps their row. Their ownership rows stay intact and are shown as "no access" in admin screens, so admins can reassign them deliberately.
• A returning user is restored automatically: removedAt is set back to null by the upsert.

5. No foreign keys from ownership to users, on purpose

Ownership rows store the user id without a foreign key to the user copy. That sounds wrong at first, so here's why: the copy is a cache of another service's data. A sync glitch, a temporary removal or a re-ordering of events must never cascade into deleting or blocking ownership. The owner row is the source of truth for "who owns this"; the copy only provides names and emails for display.

Team membership is different: group members do reference the copy, because teams are managed inside this application and must only contain users who actually exist here.

6. A health signal for operators

The sync row also stores lastPushAt and lastSyncAt. Even an ignored push (an old or duplicate version) updates lastPushAt, because it still proves the auth service can reach us. An admin screen shows:

Users copy at version 42 · last push 3 minutes ago · last sync yesterday

If "last push" says three weeks ago, pushes have stopped, and someone can click Sync now (the pull path) to repair the copy immediately.

7. Clear logs for every case

Every outcome is logged in plain words:

Applied push v42: 118 user(s), 1 removed
Ignored push v41: copy already at v42
Version gap: v38 -> v42, applied the full list
Sync v42 not applied: copy already at v43

When something looks wrong, the log tells the story without opening a debugger.

Lessons learned

• Copy only what you need, and know why. The copy exists for names, emails and teams; identity and permissions stay in the auth service.
• Full snapshots beat event streams for small datasets. Sending the whole list makes gaps, retries and ordering problems disappear.
• Version everything, compare atomically. A version check without a row lock is a race; SELECT … FOR UPDATE turns concurrent updates into a queue.
• Push for freshness, pull for repair. Each covers the other's weakness.
• Soft-delete data you don't own. Marking users removed keeps history and relationships intact and makes mistakes reversible.
• Be deliberate about foreign keys to cached data. Don't let a cache glitch cascade into your real data.
• Give operators a heartbeat. "Last push 3 minutes ago" is the cheapest monitoring you'll ever build.

FAQ

Should a microservice keep a copy of another service's data?

Often yes, for data it reads frequently and doesn't own, like user names for display. Keep the owning service as the source of truth, keep the copy read-only, and design how it's updated and repaired.

How do you prevent out-of-order updates when syncing data?

Attach a version number to every update, store the last applied version, and only apply newer versions. Do the comparison and the write in one transaction with a row lock so concurrent updates can't both pass the check.

Why send the full list instead of individual change events?

For small datasets, a full snapshot removes ordering and missed-event problems: each update is the complete truth. Events are better when data is large or you need a history of individual changes.

What is the difference between push and pull synchronization?

Push means the source service sends changes when they happen (fast, but messages can be lost). Pull means the consumer asks for the current state (reliable, but only as fresh as the last request). Combining both gives freshness with a repair path.