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.
- 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
- 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.
Table of pending local writes, committed in the same transaction as the data, drained by the sync engine.
The last server sequence number a client has seen; pulls ask for changes after it.
A marker row that records a deletion so other devices can delete too.
Write-ahead log mode in SQLite: readers do not block the single writer.
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.
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.
- Why can a global sequence make pulls skip data?
- Where do you put the idempotency check?
- 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.
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.
- 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
| State | Meaning |
|---|---|
| applied | Operation accepted and stored with a new sequence number. |
| duplicate | op_id seen before; original result returned. |
| conflict | Base version stale on a field the server also changed; server values returned. |
| rejected | Validation 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.
- The op_id check, the row update and the change-log insert share one transaction, so a retry can never create a second change.
- Downstream systems read committed changes from the WAL rather than being called from the request path.
- 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)
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);Push handler (TypeScript)
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}Pull query
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;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.
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}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.
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}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.
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}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.
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}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.
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?
Where do you put the idempotency check?
How do you scale pulls?
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
| Need | Options | When to pick |
|---|---|---|
| Sequence allocation | Per-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 store | Postgres table · Cassandra/Scylla · DynamoDB stream | Postgres until write volume per tenant forces a wide-column store. |
| Idempotency store | Unique table in the same database · Redis | Same database when you need it inside the transaction; Redis as a fast front cache. |
| Change streaming | Debezium + Kafka · logical replication · application outbox | Debezium 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.
| Who | What 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. |
| Linear | Has described tracking changes with an increasing sync ID so clients can request everything after the last ID they saw. |
| Debezium | An 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
| Metric | Why it matters |
|---|---|
| push latency and status mix | Duplicate share shows client retry health; conflict share shows product contention. |
| pull latency, rows and bytes per page | Large pages on mobile networks slow first sync. |
| change-log lag to Kafka | Delays push hints and search updates. |
| cursor age distribution | How far behind devices are; tunes tombstone TTL. |
| 410 rate | Devices forced to rebuild; too high means TTL is too short. |
5.4Edge cases
Show detailsHide details
| Case | How it is handled |
|---|---|
| Two transactions allocate seq 10 and 11, 11 commits first | With 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 response | op_id lookup returns the stored result; no second change-log row. |
| User loses access to a record | Pull filters by current ACL; a revoke change tells devices to delete local copies. |
| Hot tenant | Batch-allocate sequence ranges or split the tenant into scopes with their own counters. |