The server sync service

Server sync completes the protocol. It accepts pushes idempotently, applies merge rules, gives every change a sequence number in a per-tena…

Part 1 of 5

Basics

What this part of the system is, what it is responsible for, and the vocabulary you need before the mechanics.

1.1Definition

The one-paragraph summary, then where its responsibility starts and stops.

Server sync completes the protocol. It accepts pushes idempotently, applies merge rules, gives every change a sequence number in a per-tenant log, serves pulls by cursor with permission filtering, and builds snapshots for new devices.

Its job
  • Deduplicate pushes by op_id and return original results
  • Validate and merge changes per entity type
  • Write the entity and its change-log entry in one transaction
  • Serve ordered pages of changes after a cursor
  • Filter every pull by what the user may see
  • Publish changes for push hints, search and analytics
Not its job
  • Hold sessions or per-device state in memory
  • Order changes by client timestamps
  • Carry file bytes

1.2Key concepts

Ideas this deep dive relies on, in one sentence each.

01Outbox

Table of pending local writes, committed in the same transaction as the data, drained by the sync engine.

02Cursor

The last server sequence number a client has seen; pulls ask for changes after it.

03Tombstone

A marker row that records a deletion so other devices can delete too.

04WAL

Write-ahead log mode in SQLite: readers do not block the single writer.

05CDC

Change data capture: streaming committed database changes from the log into a queue.

1.3Real-life run

A concrete example first, so the mechanics have something to attach to.

A supervisor and a technician edit job j-81 within the same second. Both pushes reach different API pods. Each push is a transaction: check the op_id table, apply field-level merge against the current row, update the row, allocate the next sequence number for the tenant, and insert into the change log.

The technician’s push changes answers.valve_3 from base version 4. The supervisor changed only instructions. Both apply cleanly. Sequence numbers 88412 and 88413 are assigned. A third push, a retry of the technician’s request after a lost response, finds its op_id already recorded and returns the stored result without writing anything.

Debezium reads the change-log inserts from the Postgres write-ahead log and publishes them to Kafka. A notifier consumer coalesces them per user and sends one ‘changes available’ push to each affected device. A search consumer updates the index.

When the supervisor’s tablet pulls with cursor c_88411, the service returns rows 88412 and 88413 that her role may see, plus next_cursor c_88413. A tablet that has been off for 120 days asks with c_51200; that is older than the 90-day tombstone window, so it receives 410 and a snapshot URL.

Part 2 of 5

Question

Know what you are reading for. These are the questions this deep dive prepares you to answer, and the rounds where they come up.

2.1Target questions

Keep them in mind while you read the detail.

  1. Why can a global sequence make pulls skip data?
  2. Where do you put the idempotency check?
  3. How do you scale pulls?

Model answers are in part 4, after the detail that supports them.

2.2Interview rounds

System design rounds where this component is part of a strong answer.

Part 3 of 5

Detailed answer

How it actually works: the components, the states it moves through, a full run, and the code.

3.1Anatomy

The parts and how they connect.

API (STATELESS)DATAGatewayauth · rate limitsPush handlervalidate · mergePull handlercursor · ACL filterOp ID tableprocessed opsMerge rulesper entity typeSnapshot builderper scope, nightlyPostgresentitiesChange logtenant + seqCDNsnapshotsDebeziumWAL → eventsKafkachange topicsNotifiercoalesced pushesdedupemergeappendrange readpublishWAL
ClientServiceData storeQueue / stream
Boxes are components; arrows show calls and data flow.
Push handler
For each operation: look up the op_id; if present, return the stored result. Otherwise load the row, run the entity’s merge rule, write the row, allocate a sequence and insert a change-log row, all in one transaction, then store the op_id result.
Change log
An append-only table keyed by (tenant_id, seq), partitioned by tenant hash and by time. Each row stores the entity, ID, version, changed fields or a tombstone flag, and the ACL tags needed to filter pulls.
Pull handler
A range read on (tenant_id, seq > cursor) ordered by seq, joined with the user’s permissions, limited by bytes as well as rows.
Snapshot builder
Builds a compressed SQLite file or NDJSON per scope from a consistent read at a known seq, uploads it to the CDN, and returns its seq as the starting cursor.
CDC to Kafka
Debezium streams committed change-log rows from the write-ahead log, so downstream consumers see exactly what was committed, in commit order.

3.2States

StateMeaning
appliedOperation accepted and stored with a new sequence number.
duplicateop_id seen before; original result returned.
conflictBase version stale on a field the server also changed; server values returned.
rejectedValidation or permission failure; client must not retry.
gone (pull)Cursor older than the tombstone window; client must rebuild from a snapshot.

3.3Run loop

One run, step by step.

DevicePush handlerPostgresDebezium/KafkaOther devicePOST /push op_id=ABEGIN; op A seen? nomerge + update rowseq = next(tenant) → 88412INSERT change_log; COMMITapplied · seq 88412WAL → change eventpush hint (coalesced)GET /pull?cursor=c_88411[88412] + next cursor
Solid arrows are calls; dashed arrows are responses.
  1. The op_id check, the row update and the change-log insert share one transaction, so a retry can never create a second change.
  2. Downstream systems read committed changes from the WAL rather than being called from the request path.
  3. The other device learns about the change from a hint, but the pull is what guarantees it.

3.4Code

Working code for the core of it.

Change log and op table (PostgreSQL)

change-log-op.sqlSQLCopy
1CREATE TABLE change_log (2  tenant_id  bigint      NOT NULL,3  seq        bigint      NOT NULL,4  entity     text        NOT NULL,5  entity_id  uuid        NOT NULL,6  version    int         NOT NULL,7  fields     jsonb,                     -- changed fields; NULL for a tombstone8  deleted    boolean     NOT NULL DEFAULT false,9  acl        text[]      NOT NULL,      -- groups allowed to see this change10  created_at timestamptz NOT NULL DEFAULT now(),11  PRIMARY KEY (tenant_id, seq)12) PARTITION BY HASH (tenant_id);13 14CREATE TABLE processed_op (15  op_id uuid PRIMARY KEY, tenant_id bigint NOT NULL,16  result jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()17);  -- purged after 14 days18 19CREATE TABLE tenant_seq (tenant_id bigint PRIMARY KEY, seq bigint NOT NULL);
19 linesUTF-8SQL

Push handler (TypeScript)

push-handler-typescript.tsTypeScriptCopy
1export async function applyOp(tx: Tx, tenant: string, user: User, op: Op): Promise<OpResult> {2  const seen = await tx.one('SELECT result FROM processed_op WHERE op_id = $1', [op.opId]);3  if (seen) return { ...seen.result, status: 'duplicate' };4 5  const row = await tx.one('SELECT * FROM job WHERE id = $1 AND tenant_id = $2 FOR UPDATE', [op.id, tenant]);6  authorize(user, 'write', row);7  const outcome = mergeRules[op.entity](row, op);              // field-level: applied or conflict8  if (outcome.kind === 'conflict') return saveResult(tx, op, { status: 'conflict', server: outcome.server });9 10  await tx.none('UPDATE job SET data = $1, version = version + 1 WHERE id = $2', [outcome.data, op.id]);11  const { seq } = await tx.one('UPDATE tenant_seq SET seq = seq + 1 WHERE tenant_id = $1 RETURNING seq', [tenant]);12  await tx.none('INSERT INTO change_log (tenant_id, seq, entity, entity_id, version, fields, acl) VALUES ($1,$2,$3,$4,$5,$6,$7)',13    [tenant, seq, op.entity, op.id, row.version + 1, outcome.changedFields, row.acl]);14  return saveResult(tx, op, { status: 'applied', seq, version: row.version + 1 });15}
15 linesUTF-8TypeScript

Pull query

pull-query.sqlSQLCopy
1SELECT seq, entity, entity_id, version, fields, deleted2FROM change_log3WHERE tenant_id = $14  AND seq > $2                         -- the client's cursor5  AND acl && $3                        -- the user's groups6ORDER BY seq7LIMIT 500;
7 linesUTF-8SQL

3.5Tested code, explained

A working implementation, then what each part does and why.

Applying one op on the server

Each op must apply exactly once, however many times it arrives. In production this is one Postgres transaction per op.

SyncServer.ktKotlinCopy
1/** The server side of sync. Each op is one transaction: check, merge, number, log. */2class SyncServer(startSeq: Long, private val logFloor: Long = 0) : SyncApi {3  private class Row(val fields: MutableMap<String, Stored> = HashMap(), var version: Int = 0)4 5  private val rows = HashMap<String, Row>()6  private val applied = HashMap<String, OpResult>()     // op_id -> the answer we gave7  private val log = ArrayList<Change>()8  private var seq = startSeq9 10  /** Rows written, so a test can prove that a retry writes nothing. */11  var writes = 012    private set13  var offline = false14 15  fun seedJob(jobId: String, version: Int) { rows[jobId] = Row(version = version) }16 17  override suspend fun push(ops: List<Op>): List<OpResult> {18    if (offline) throw TryLater()19    return synchronized(this) { ops.map(::applyOne) }20  }21 22  private fun applyOne(op: Op): OpResult {23    applied[op.opId]?.let { return it }                 // a retry: same answer, no write24    val row = rows.getOrPut(op.jobId) { Row() }25    val result = when (val m = merge(op.field, op.value, op.baseVersion, row.fields[op.field])) {26      is Merge.Refuse -> OpResult.Conflict(op.opId, Theirs(op.field, m.current.value, row.version, m.current.by))27      is Merge.Apply -> {28        row.version += 129        row.fields[op.field] = Stored(m.value, row.version, op.by)30        seq += 131        log += Change(seq, op.jobId, op.field, m.value, row.version)32        writes += 133        OpResult.Applied(op.opId, seq)34      }35    }36    applied[op.opId] = result37    return result38  }39  // pull follows40}
40 linesUTF-8Kotlin
applied[op.opId]?.let { return it }
The first line. A retry returns the saved answer and writes nothing. A conflict counts too, so a retry can't turn it into a success.
merge(op.field, ...)
The same function as the merge lesson.
seq += 1
The next line number in the log. In production a per-customer row lock keeps these in commit order.
log += Change(...)
Every change goes here. Other devices read only this.

Compiled and tested by npm run test:mobile.

The decision for each field

This small function decides what happens when two people edit offline. The product team's table becomes code you can test.

FieldMerge.ktKotlinCopy
1enum class Policy { LAST_WRITE_WINS, ADD_ONLY, ASK_USER }2 3/** A field on the server, and the job version at which it last changed. */4data class Stored(val value: String, val version: Int, val by: String)5 6sealed interface Merge {7  data class Apply(val value: String) : Merge8  data class Refuse(val current: Stored) : Merge9}10 11/** The product team's table, written down as code. */12fun policyFor(field: String): Policy = when {13  field.startsWith("photos") -> Policy.ADD_ONLY14  field == "instructions" || field == "read_marker" -> Policy.LAST_WRITE_WINS15  else -> Policy.ASK_USER                 // the safe default for anything new16}17 18fun merge(field: String, incoming: String, baseVersion: Int, current: Stored?): Merge {19  // Nobody touched this field since the user read it: j-81 takes this path.20  if (current == null || current.version <= baseVersion) return Merge.Apply(incoming)21 22  return when (policyFor(field)) {23    Policy.LAST_WRITE_WINS -> Merge.Apply(incoming)24    Policy.ADD_ONLY -> Merge.Apply("${current.value},$incoming")25    Policy.ASK_USER -> Merge.Refuse(current)            // j-84 takes this path26  }27}
27 linesUTF-8Kotlin
policyFor(field)
The policy table. Anything not listed asks the user, the safe choice for a new field.
current.version <= baseVersion
Nobody changed this field since the user read it, so apply it. That's why j-81 merged with no conflict.
Policy.ADD_ONLY
Photos: both people's additions are kept, because they never disagree.
Merge.Refuse(current)
Valve 3 on j-84 lands here. The server applies nothing, and returns what it holds.

Compiled and tested by npm run test:mobile.

Serving a pull

A device says where it got to, and gets what comes after.

SyncServer.ktKotlinCopy
1override suspend fun pull(cursor: Long, limit: Int): PullPage {2  if (offline) throw TryLater()3  if (cursor < logFloor) throw CursorTooOld("/snapshots/latest")4  return synchronized(this) {5    val after = log.filter { it.seq > cursor }6    val page = after.take(limit)7    PullPage(page, page.lastOrNull()?.seq ?: cursor, hasMore = after.size > limit)8  }9}
9 linesUTF-8Kotlin
cursor < logFloor
This device is older than the log keeps, so answer 410 and send it to a snapshot.
it.seq > cursor
Only what this device hasn't read. Asking twice is harmless.
hasMore = after.size > limit
Tells the phone to keep pulling. Large catch-ups arrive in safe pieces.

Compiled and tested by npm run test:mobile.

Telling other devices to look

A push doesn't carry the change. It just says something changed, so Dev's tablet pulls by cursor like any other device.

SyncServer.ktKotlinCopy
1/** Push says "come and look". It carries no data, and one push covers many changes. */2class ChangeNotifier(private val sendPush: (device: String) -> Unit) {3  fun onCommitted(changes: List<Change>, devicesFor: (jobId: String) -> Set<String>, author: String) {4    changes.flatMap { devicesFor(it.jobId) }5      .toSet()6      .minus(author)7      .forEach(sendPush)8  }9}
9 linesUTF-8Kotlin
flatMap { devicesFor(it.jobId) }
Finds who cares about each change.
.toSet()
Five changes for one tablet still mean one push, which saves battery and provider quota.
.minus(author)
The phone that made the change doesn't need telling.

Compiled and tested by npm run test:mobile.

3.6Platforms

Service runtime

  • Node.js (Fastify or NestJS), Kotlin (Ktor) or Go; stateless pods behind a load balancer.
  • Connection pooling with PgBouncer; every request is one or a few short transactions.

Database

  • PostgreSQL with hash partitioning by tenant; Citus when a single node is no longer enough.
  • Per-tenant sequence rows serialise writers within a tenant, which is what keeps pull order safe.

Streaming

  • Debezium on the Postgres WAL into Kafka (or a managed equivalent); consumers for push hints, search, analytics and snapshots.

Edge

  • API gateway for auth and per-device rate limits; a CDN for snapshots and media.
Part 4 of 5

Q&A

The questions from part 2, now with model answers. Try each one out loud before opening it.

4.1Interview questions

Why can a global sequence make pulls skip data?
A transaction takes its sequence number when it asks, not when it commits. A later number can commit first, a client pulls past it, and the earlier one appears behind its cursor. Per-tenant serialisation or a safe watermark fixes it.
Where do you put the idempotency check?
In the same transaction as the write, so the check and the write cannot be separated by a crash.
How do you scale pulls?
Partition the change log by tenant, read with an index on (tenant_id, seq), cap pages by bytes, serve snapshots from a CDN for new devices, and push hints so devices pull only when something changed.
Part 5 of 5

Extra depth

Practical context for real projects: which technology to pick, what successful teams do, what to measure and the edge cases to plan for.

5.1Technology choices

Show detailsHide details
NeedOptionsWhen to pick
Sequence allocationPer-tenant counter row · global sequence + watermark · WAL position (LSN)Per-tenant counter for most products; a watermark when one tenant is too hot to serialise.
Change storePostgres table · Cassandra/Scylla · DynamoDB streamPostgres until write volume per tenant forces a wide-column store.
Idempotency storeUnique table in the same database · RedisSame database when you need it inside the transaction; Redis as a fast front cache.
Change streamingDebezium + Kafka · logical replication · application outboxDebezium when you already run Kafka; an outbox table otherwise.

5.2How real products do it

Show detailsHide details

Summaries of what these teams and projects have described publicly. Check their own write-ups for detail.

WhoWhat they describe
Replicache (Rocicorp)Documents a pull that returns changes since a client’s cookie and a push that records the last processed mutation ID per client, the same idempotency idea as op_ids.
LinearHas described tracking changes with an increasing sync ID so clients can request everything after the last ID they saw.
DebeziumAn open-source change data capture platform that reads database write-ahead logs and publishes row changes to Kafka.

5.3Metrics to emit

Show detailsHide details
MetricWhy it matters
push latency and status mixDuplicate share shows client retry health; conflict share shows product contention.
pull latency, rows and bytes per pageLarge pages on mobile networks slow first sync.
change-log lag to KafkaDelays push hints and search updates.
cursor age distributionHow far behind devices are; tunes tombstone TTL.
410 rateDevices forced to rebuild; too high means TTL is too short.

5.4Edge cases

Show detailsHide details
CaseHow it is handled
Two transactions allocate seq 10 and 11, 11 commits firstWith a per-tenant counter row the second waits for the first, so commit order matches sequence order. With a global sequence, serve pulls only up to a safe watermark below the oldest in-flight transaction.
Retry after a lost responseop_id lookup returns the stored result; no second change-log row.
User loses access to a recordPull filters by current ACL; a revoke change tells devices to delete local copies.
Hot tenantBatch-allocate sequence ranges or split the tenant into scopes with their own counters.