Real-time collaboration techniques: ordering, sync and large lists

The engineering behind screens many people change at once: ordering with sparse positions instead of re-numbering, optimistic updates that roll back, row versions, leases and three-way merges for conflicts, granular sync compared with full reloads and CRDTs, virtualized lists, roll-ups over trees and history from soft-deleted rows - step by step, with worked examples.

In short

When several people work on one list at the same time, three hard problems appear. Order: moving one item must not rewrite every other row. Conflicts: two people change the same thing, and nobody's work may silently disappear. Freshness: every open screen should show a change within a moment, without reloading thousands of items. {{brand}} answers them with a small set of reusable techniques - sparse positions, optimistic updates, version checks and merges, granular sync and virtualized lists - and this lesson teaches each one so you can use it in any project.

After this lesson you can Store a user-defined order so that a move writes one row; make a write feel instant and roll it back safely; choose between last write wins, version checks, locks, merges and CRDTs for a given kind of data; and keep a list of thousands of items fast and current on every open screen.

Words you need

• Sparse position and anchor An order number with gaps between neighbours - 0, 1024, 2048 - so a new item fits between two others without moving anyone. A drop is sent as an anchor, the neighbour it landed beside (right before B), not an index, so it means the same on every screen.
• Optimistic update Changing the screen before the server answers, keeping a snapshot to roll back to if the server refuses.
• Row version A number that rises on every save. A save that names an old version is refused instead of overwriting newer work. Also called a concurrency token.
• Isolation level How much overlapping transactions may see of each other. Read committed lets writers interleave; serializable makes the database behave as if they ran one after another.
• Idempotent Safe to run twice. A transaction the database aborts and you retry must give the same result the second time, so keep side effects outside it.
• Three-way merge Comparing two edited copies with the common ancestor both started from, so changes to different parts combine and only real clashes remain.
• Rich text editor (ProseMirror) An editor that keeps text as a document tree - paragraphs, list items, bold marks - changed only by small transactions, never by editing HTML directly. Tiptap is a friendlier layer over ProseMirror, the engine.
• CRDT A conflict-free replicated data type: a structure every copy can change, even offline, built so that all copies always end up equal. Yjs and Automerge are examples.
• Granular sync Telling open screens which items changed, so each one refetches only those instead of reloading everything.
• Virtualized list A list that draws only the rows in view plus a margin, with spacers standing in for the rest. Also called windowing.
• Roll-up A parent's figure - progress, counts - computed from its children and stored on write, so reads never have to compute it.
• Soft-deleted membership The row that puts an item in a group is marked as left instead of deleted, so all its rows together become the item's history.

Ordering a list: positions with gaps

The obvious way stores the order as 1, 2, 3 ... N. Moving the last card to the top then rewrites all N rows: N writes, N row locks held in one transaction, N change records and N items every other screen must fetch again. On a column of 400 cards one drag becomes 400 updates, and two people dragging at once fight over the same rows.

Sparse positions fix this by leaving gaps: cards sit at 0, 1024, 2048. A card dropped between 0 and 1024 takes the midpoint, 512 - one row written, however long the column is. Only when two neighbours have no whole number left between them is the column re-spaced once, and that is rare.

• 1. Name the neighbour The screen sends the target column and an anchor: right before card B, or right after card A, or neither for the end. Never an index like 'position 7' - on a filtered or half-loaded screen that means something else in the database.
• 2. Find the other neighbour The server reads the anchor's position and the nearest card on its other side, in the list's real order: position, then id.
• 3. Already there? If the card already sits between those two, nothing is written - a drop back where it was costs nothing.
• 4. Take the midpoint With room between them, the card takes low + (high - low) / 2. At either end of the column it takes one gap past the end card.
• 5. Spread when full If the neighbours are next to each other, say 1023 and 1024, the whole column is rewritten a gap apart in one go, and every card that moved is reported so other screens refresh it too.

Always order by position, then by id Two items can end up with the same position - two people add an item to one list in the same instant. Sorting by position and then by id makes the order deterministic everywhere, so equal positions are harmless and an insert needs no lock or strict transaction. A move that must never leave an item in two lists is different: run it in a stricter transaction (see the conflicts section).

Optimistic updates: show it now, let the server win

A drag that waits 300 ms for the server feels broken. An optimistic update changes the data on screen at once and sends the request behind it; a pessimistic update waits for the answer first. Optimistic is right when the server almost always says yes and the change is easy to reverse: moves, toggles, pins, marking as read. In a React app the data on screen lives in a query cache - TanStack Query in {{brand}} - so an optimistic update is a careful edit of that cache.

• 1. Cancel reads in flight A slow response that started before the change would land after it and undo it on screen, so pending fetches of the same data are cancelled first.
• 2. Snapshot Keep a copy of every cached list the change touches - the old column, the new column, the open card.
• 3. Patch Edit the cache: the card leaves its old column, joins the new one beside its anchor, and both counts change by one. A card moved past the loaded part simply drops out of view until its page loads.
• 4. Send, in order The request goes out. Moves on the same board share one mutation queue, so two quick moves can never reach the server swapped.
• 5. Failed: roll back Put every snapshot back exactly and show the reason with a way to retry.
• 6. Settled: the server wins Success or failure, fetch the server's truth - here only the moved card, not the whole board.

Undo must be a real action An Undo offered after a move should send a real reverse request - the same move back to the neighbour the card sat beside - never just restore the old picture. If there is no way back on the server, offer no Undo. Keep the patch itself a pure function of the old data and the move, so the move and its rollback can be tested without a browser.

When two people change the same thing

Optimistic updates take care of your screen. Concurrency control decides what the database keeps when two people change the same record at the same moment. There is no single right answer - there is a right answer per kind of data, and {{brand}} uses five of them.

ORMs do the same for you: in EF Core a property marked as a concurrency token is added to the WHERE clause of every update, and zero rows updated raises a concurrency exception. In PostgreSQL the hidden system column xmin changes on every update, so it can serve as a free row version.

Over HTTP the same idea is an ETag returned with the document and an If-Match header on the save; a stale tag is answered with 412 Precondition Failed (many APIs use 409 Conflict instead).

• 1. Keep every version Each save stores a revision with its number, so the starting point of any edit can be found later.
• 2. Notice a stale save A save says it started from version 7, but the note is already at 8: someone saved in between.
• 3. Cut into blocks All three versions - ancestor, mine, theirs - are cut into blocks: paragraphs, headings, each item of a list. Two people ticking different checklist items now touch different blocks.
• 4. Diff each side A diff finds the longest run of blocks both versions share (a longest common subsequence); everything outside that run was changed. Ancestor [A, B, C] against mine [A, B2, C]: the shared blocks are A and C, so B was replaced by B2. Doing this for each side shows what it replaced, inserted or deleted.
• 5. Combine or clash Changes to different blocks - even neighbouring ones - both apply, and the same change on both sides is taken once. One block changed differently by both is a clash: nothing is saved, and both versions come back side by side for a person to choose.
• 6. Save the merge Without clashes the merged text becomes the next version. Other open screens are told a new version exists: one with nothing unsaved takes it at once, one being typed in waits and merges on its own save.

Where do the blocks come from? From the editor. A browser's own editable area (contenteditable) produces different HTML in every browser, so serious editors do not trust it. A rich text editor such as Tiptap, built on ProseMirror, keeps its own document: a tree of nodes (document, paragraph, heading, list, list item, text) with marks such as bold on the text, checked against a schema that says what may contain what. Every keystroke or click becomes a transaction - a list of small steps like insert here, delete there, mark this range - which produces a new immutable document; the editor then changes only the page elements that differ, much as React does. Asking for the HTML gives a clean, predictable serialization of the tree, with the same block structure every time, which is exactly what a block-based diff needs.

Because transactions are small and explicit, they are also what live co-editing is built on: ProseMirror's collaboration module sends steps through a central server that puts them in order and rebases the late ones (the idea of operational transformation), and Tiptap can also sit on a CRDT such as Yjs for offline-capable co-editing. {{brand}} does not edit a note live in two places at once. It checks the version on save and merges blocks, as above: far simpler to run, at the price of an occasional clash a person must settle. Stored HTML from an editor must still be sanitized again before it is shown, because the schema protects only what passes through the editor (see the sign-in lesson).

• Tiptap (on ProseMirror) A headless editor: you design the toolbar.; A strict schema and transactions from ProseMirror; Ready-made extensions: task lists, colours, alignment; Works well with React, and the core is MIT; ProseMirror's model takes time to learn; Some add-ons, such as hosted collaboration and comments, are paid
• Lexical Meta's lightweight editor framework.; Small and fast, good React support; Younger, with fewer ready-made extensions; Its own document format to convert
• Slate A very flexible, React-first framework.; Total control over the model; You build most features yourself; Long in a 0.x state, so APIs have shifted
• Quill, CKEditor or TinyMCE Complete editors with a ready toolbar.; Fast to start; polished features; Less control of the schema and look; The richer ones are commercial or heavy

The same idea keeps two systems in step Two-way sync with an outside tracker keeps, for every field, the last agreed value of both sides - the base. A side changed a field only when its value differs from its own base, so the sync's own write coming back is never mistaken for a change, which stops echo loops. When both sides changed one field, the later change wins and both values are written into the card's history.

Keeping every open screen current

After a save, other people's screens must catch up. How the message travels - WebSockets through SignalR - is the subject of the real-time lesson. Here the questions are what to send and what each screen does with it, so that the cost grows with the change and not with the size of the board.

• 1. Save first The write commits in the database, the source of truth. If a signal is lost later, it costs one stale moment, never the change.
• 2. Name what changed The server sends the ids of the changed cards to everyone with the board open - including parents whose roll-up moved and cards a re-spacing moved.
• 3. Too many? Reload When one change touches a very large number of cards, fetching the board again is cheaper than naming them all, so a whole-board signal goes instead.
• 4. Gather for a moment The screen collects ids arriving within a few tens of milliseconds of each other and asks once, not ten times.
• 5. Fetch just those One request per set of filters on screen returns those cards as the filters show them now, plus every column's count - checked like any other request.
• 6. Fold into the cache Each changed card leaves wherever it was and is inserted where it now belongs in each list's sort order. A card that now sorts past the loaded part waits for its page; one the filters no longer show disappears.
• 7. Fall back when unsure If every loaded card changed, nothing says where the loaded part now ends, so that list is fetched again. A list nobody is looking at is only marked stale. After a reconnect, everything is fetched again.

In the example, hasMore is true while later pages are not loaded yet; a card that now sorts past the end of the loaded part waits for its page instead of being added.

The browser must sort exactly like the server Folding works only if the browser orders items the same way the database does - every sort, including the final tie-break by id. Otherwise a refreshed card lands one place off and jumps when the next page loads. Keep the client's sort next to a test that mirrors the server's ORDER BY.

The options in the market

• Integer re-numbering 1, 2, 3 ... rewritten on every move.; Simplest to read; Positions always dense; A move rewrites up to every row; Two movers fight over the same rows
• Sparse integers with gaps Gaps of about a thousand; take the midpoint; re-space rarely.; One row per move almost always; Plain integers: easy to index, compare and debug; About ten drops into one gap force a re-space of that list; Equal positions need a tie-break
• Floating-point fractions The midpoint as a double: 0.5, 0.25, 0.125 ...; Around fifty halvings before precision runs out; Still one number column; Precision runs out silently; Rounding makes equal-looking values tricky
• String keys (LexoRank, fractional indexing) Keys such as 'a', 'an', 'b' that can always be extended.; There is always room between two keys; Clients can create keys offline; Keys grow when many inserts hit one spot, and need rebalancing; Harder to read and debug
• Linked list Each item points at the next one.; An insert changes two pointers; Reading in order needs a recursive query; One broken pointer loses the rest of the list

• Reload everything, or poll Fetch the whole list after any change, or every few seconds.; Trivially correct; Cost grows with the list, not the change; Late by up to one polling interval
• Signal the ids, refetch those A tiny message names what changed; screens fetch just that.; Cost grows with the change; Every refetch passes the normal permission checks; A lost signal costs one stale moment; The browser must fold and sort exactly like the server; One extra request per change
• Push the data itself Send the changed records over the socket.; No extra request; Right for small, final messages such as a chat message; Skips the checks a normal request makes; Filtered views must re-filter in the browser

• Last write wins The later save replaces the earlier one.; Nothing to build; Fine for short fields when a history shows every change; A long text can silently lose someone's work
• Version check A save names the version it started from; an old one is refused.; No work is lost silently; Cheap: one extra column and one WHERE clause; The loser must reload and redo or merge by hand
• Edit lock (lease) One editor at a time; the lock expires if its holder disappears.; No merge needed; Right for canvases and diagrams; Others must wait or only watch; A lock without an expiry gets stuck
• Three-way merge Compare both copies with the common ancestor; combine separate changes.; Different parts merge automatically; Real clashes go to a person; Needs every older version kept; Needs a sensible way to cut the data into parts
• CRDT or operational transformation Every change is an operation that merges by construction (CRDT), or a central server rewrites concurrent operations against each other (OT).; Edits merge automatically, even offline (CRDT); Proven for real-time text editing (OT); Ideal for text and whiteboards typed in by many at once; Every client holds the whole document state (CRDT); OT needs one server ordering every operation and is hard to get right; Permissions and server rules are hard to enforce; Heavy for records that live in a database

What {{brand}} chose, and why

• Sparse integer positions with anchors: almost every move writes one row, so a busy board has no write storms, and naming a neighbour keeps a drop exact on filtered and half-loaded columns. The trade-off - an occasional whole-column re-space - is cheap and is announced like any other change.
• Optimistic moves with a real Undo: the screen answers instantly, a failure rolls back exactly, and the server always has the last word when the write settles.
• A strategy per kind of data: last write wins plus a history for short fields, a version check and a lease for designs, three-way merges for notes and tracker sync, a strict transaction for moves between lists. The right answer depends on the data, so the choice is made per kind.
• Granular sync instead of CRDTs: board data are records with permissions, rules and roll-ups that are decided on the server. Signals keep the server the only judge; a CRDT would move that judgement into every browser.
• Roll-ups stored on write: boards are read far more often than they change, so the cost is paid once per change. The trade-off is that every write path must trigger the recalculation.
• History from soft-deleted memberships: no second event log to keep in step. The trade-off is that every query of the present must skip rows that have left.
• Virtualization past a threshold: short lists stay simple, long ones stay fast.

Another choice is right in other situations. Pick string keys such as LexoRank when a list is huge and re-spacing it would be slow, or when clients create items offline. Pick a CRDT such as Yjs for a rich-text editor or a whiteboard that many people type into in the same second, or for an offline-first app. Push the data when messages are small, final and addressed to people the server has already chosen.

Thousands of items: virtualized lists

A browser slows down when it holds thousands of complex elements: layout, styles and memory all grow with the count. A virtualized list keeps the data of every loaded item but creates elements only for the rows in view, plus a margin called overscan so a quick scroll never shows a gap. Two spacers above and below take the height of the rows not drawn, so the scrollbar behaves as if every row were there.

• 1. Know each row's height Measure rows as they are drawn - a ResizeObserver reports every change. For a row never drawn, assume the average of those measured so far.
• 2. Add up offsets Row i starts at the sum of the heights and gaps of the rows before it.
• 3. Find the window From the scroll position minus the overscan, to the bottom of the view plus the overscan.
• 4. Draw only that slice Render rows start to end; the top spacer is as tall as everything above start, the bottom one as tall as everything after end.
• 5. Repeat on scroll Read the scroll position at most once per animation frame, never on every scroll event.

Switch it on only when it pays Short lists gain nothing from virtualization and still pay for it - measuring, spacers, harder focus handling. Turn it on past a threshold, and wrap every row in the same element either way, so crossing the threshold does not recreate every row on screen.

A row that is not drawn cannot hold focus The browser's own Find and the keyboard cannot reach a row that has no element. When a keyboard action sends an item out of sight, scroll it into view first, wait for it to be drawn, then move focus to it.

Roll-ups over a hierarchy

A parent card's progress comes from its children, and theirs from their children. Computing that on read means walking the tree every time a board opens - and boards are read far more often than they change. {{brand}} computes it on write instead, a form of denormalization: after a change, the touched card is recalculated, and the walk climbs to its parent only while something changed.

• 1. Start from what changed The cards the write touched go into a queue.
• 2. Compute from owned rows Each item's figure comes from the children it owns, weighted by a share; an item with no children uses its own state.
• 3. Write only differences If the new numbers equal the stored ones, the walk stops here. Most changes stop after one or two levels.
• 4. Climb while changed A changed item puts its parent in the queue, up to a fixed depth and never past the top of the tree.
• 5. Announce Every card whose numbers moved, on any board, is named to the screens showing it.

A roll-up can use weights: here each child has a share, and the parent's figure is the weighted average. Shares can start as an even split that totals exactly 100 - three children read 34, 33 and 33 - so the sum never drifts. Anything that depends on the clock, such as how late a task is, cannot be stored on write: compute it when the item is served.

When a board's calculation rules change, every card is recalculated once, leaves first - a post-order walk of the tree - so each parent reads children that are already up to date.

Guard the tree before you roll it up A roll-up over a tree must refuse cycles - an item placed under its own descendant - and cap the depth, or one bad link loops forever. Refuse cycles when you create a link by walking the full chain of ancestors, and check the stored tree, not only what the current screen shows.

History from soft-deleted memberships

A card is in a column because a membership row joins the two. When the card leaves, {{brand}} does not delete that row; it stamps the time it left - a soft delete. The live rows say where cards are now, and all the rows together are each card's history: entered when the row was created, left when it was deleted. Measures such as the time spent in a stage are read from these rows instead of from a separate event log.

• 1. Read the visits For the items being measured, read every membership row - left ones included - in time order.
• 2. Entry and exit A row's created time is when the item entered; its deleted time, or now if it is still there, is when it left.
• 3. Subtract Exit minus entry is the time spent on that visit.
• 4. Group Sum the visits per item, or per stage, depending on the question.
• 5. Summarize Report a median or a percentile rather than an average, and ignore tiny samples. Cycle time from start to finish is one example of such a metric.

Two rules keep it correct A unique index must cover live rows only, otherwise an item could never join the same group twice. And when you read deleted rows on purpose, never widen who may see them: first find the records the person may see with the normal query, then read the history of those ids only.

Try it yourself

You need PostgreSQL (or SQLite) and, for the second half, a small React app with TanStack Query. Each step lets you meet one problem from this lesson.

• 1. Create the table items(id, title, position integer), with ten rows at positions 0, 1024, 2048 ...
• 2. Write move(id, beforeId) Read the anchor's position and the nearest position above it, take the midpoint, update one row.
• 3. Run out of room Drop eleven items into the same gap and watch the midpoint stop working; then run the re-space statement below.
• 4. Make it optimistic In React, patch the cached list in onMutate and restore the snapshot in onError. Make the server fail every third call with the stub below to see the rollback.
• 5. Open two tabs Start with polling every two seconds. Then use the browser's BroadcastChannel to send the moved id from one tab to the other, and refetch only that item - granular sync without a server.

Common mistakes

• Storing order as 1, 2, 3: every move rewrites the list and two movers block each other. Leave gaps, or use string keys.
• Sending an index instead of a neighbour: position 7 on a filtered or half-loaded screen is not position 7 in the database. Send 'before' or 'after' which item.
• Optimistic updates without cancel and rollback: a late response overwrites the new state, or a refused change stays on screen. Cancel in-flight reads, snapshot, roll back, refetch on settle.
• Silent last write wins on long text: one person's paragraph vanishes. Use a version check, a lease lock or a merge - and keep a history either way.
• Reloading the whole board on every change: the cost grows with the board, not the change. Name the changed items; reload only past a threshold or after a reconnect.
• Virtualizing every list: short lists only pay the cost. Switch it on past a threshold, and scroll a row into view before focusing it.

Interview questions

• How would you store a user-defined order so that moving an item is cheap? Give items sparse positions with gaps, such as 1024 apart, and put a moved item at the midpoint of its new neighbours, so one row is written. When two neighbours run out of room, re-space that list once. String keys such as LexoRank avoid even that, at the cost of keys that grow.
• What is an optimistic update, and what can go wrong? The client changes its cache before the server answers. A slower, older read can overwrite it, so cancel in-flight reads; the server can refuse, so snapshot and roll back; and the server must win in the end, so refetch when the write settles.
• Optimistic concurrency or pessimistic locking? Optimistic lets everyone edit and refuses a save made from an old version - cheap when conflicts are rare. Pessimistic takes a lock first so only one person edits - right for canvases where a merge makes no sense, as long as the lock is a lease that expires when its holder disappears.
• How does a three-way merge decide what is a conflict? It compares each side with their common ancestor. A part only one side changed takes that change, and a part both changed the same way is taken once. Only a part both sides changed differently is a conflict for a person to resolve.
• CRDT or operational transformation, and when would you choose either? Both let many people edit one document at once. OT needs a central server to order and rewrite operations; a CRDT is built so that copies converge without one, at the cost of extra metadata. Choose either for shared text, canvases and offline editing; for database records with permissions and server rules, a server-authoritative model with change signals is simpler and safer.
• Why signal ids instead of pushing the changed data? The refetch passes the same permission and filter logic as any request, the message stays tiny, and a lost signal costs one stale moment because the database stays the truth.
• How do you render 5,000 rows smoothly? Page from the server, then virtualize: draw only the rows in view plus an overscan, with spacers for the rest. Measure real row heights and estimate unseen ones from the average.
• How can you get history without an event store? Keep the rows that place an item and soft-delete them when it leaves; their created and deleted times are its visits. Scope unique indexes to live rows so an item can come back.
• Read committed or serializable for a move, and what must the code do if the database aborts? Serializable makes overlapping transactions behave as if they ran one after another and aborts one of them. Wrap the whole unit of work in a retry and keep side effects, such as emails and signals, outside it, because the block may run twice.
• A client was offline for an hour. How does it catch up? On reconnect it has missed signals it cannot count, so it refetches the whole view instead of trusting deltas. The database stays the truth and the signals are only a hint about what to refetch.

Related

• /help/technology/realtime-events
• /help/technology/data
• /help/technology/frontend