neighbourhoodie-nnh-logo

A real-time multi-user Kanban board with CouchDB and Svelte posted Thursday, December 5, 2024 by Alex

A Kanban Board with a number of cards, apparently used to manage various types of rodents. Two users are online, and are working on different cards.

In this first post of a four-part series, we’ll start building a real-time Kanban board, and discuss the challenges and trade-offs. My usual demo for real-time apps with CouchDB is a simple multi-user sketchpad, because for anything noticable to happen in real time, there needs to be some other user doing it. The sketchpad is a good introductory example since there’s very little code, but also because there’s no updating or deleting of data, just writes and reads. However, this neatly sidesteps the main challenge posed by any proper multi-user app: conflicts.

This time, we want to do this properly, and use an example that not only does all of CRUD in a multi-user context, but takes extra measures to both reduce the probability of conflicts occuring, and to properly handle them when they do pop up1.

So while the real-time bit sounds more exciting, the multi-user bit is actually much more challenging.

Here’s what we’ll cover in this post:

In the next posts, we will:

In short: this post is mainly about using CouchDB to build the basics of a real-time app, with further improvements coverered in later posts.

The app exists on GitHub, and you can follow along as we add features by checking out different tags. This post covers step 1. You’ll need CouchDB running on your machine to try this locally, and the readme covers the installation and setup.

Before we start: This isn’t a straightforward ”here’s the correct way to do this” lecture-type blog series. We’ll be exploring the problem together, step by step, with the thinking that goes into it, the mistakes, the backtracking, and the gradual improvements inherent in software development. CouchDB, distributed apps and their design patterns can be very different than what you’re used to, so we’ll make the thought processes fairly explicit in the writing. This is less about looking at the correct code and more about learning to think with the correct mindset.

The Requirements of a Multi-User Real-Time Kanban Board

Our board will have three fixed columns that each contain zero or more cards. Cards have a description and can be reordered within a column, or moved to any position in any of the other colums. The card descriptions will be editable. It will be possible to add and delete cards. New cards can be added to the end of any column. We will conveniently omit any form of user or session handling for the sake of brevity, but we will allow users to set a username for themselves, which we will keep in localStorage in lieu of an actual session. As for the cards themselves:

  • Any change made to a card should appear on any other device connected to the board as it happens:
    • Added cards pop into existence
    • Deleted cards disappear
    • Edited cards update their description
    • Moved cards move to their new position
  • Users should be able to work simultaneously
  • No data should be lost (no ”last write wins” cop outs here, we take our users’ data seriously)

Challenges of the App

The main challenge is: what happens when two users try to change the same database record at the same time? For example: Alice clicks on a card to edit it, Bob also clicks on the same card to edit it, Alice saves her changes, then Bob saves his changes. This is actually not a challenge imposed by the real-timeyness of the app, this issue exists one way or another, but we do have to deal with it. Variations of this issue abound: two users re-ordering cards, one user deleting a card someone else is currently editing etc. As we stated before, ”last write wins” is not an option, since it silently and permanently destroys user data. We want to:

  • avoid conflicts as far as possible, without locking
  • when they do occur, not lose any data
  • if they can be resolved automatically, do so
  • if not, give the users the agency to resolve the conflicts themselves

We’re dealing with locking in a separate post later because it adds a lot of complexity, introduces a few new problems, and doesn’t work at all in offline-first apps. Yes, this isn’t an offline-first app, but I want to focus on more universal techniques in the beginning, to give you a solid foundation regardless of whether your app is always-online or offline-first. So we’re looking at other means of avoiding conflicts first.

The Initial State of our App

We start with the basic features in place: there’s a board with three fixed columns, you can add, move, update and delete the cards as described in our requirements. We will store and retrieve cards with PouchDB, a JS implementation of CouchDB we can use in the browser. In this case, we just use it as a convenient library to communicate with a remote CouchDB. Before we can store any data, we need to define what it’s going to look like:

Data Design

CouchDB is document-based, so while we don’t have a strict schema, it makes sense to decide on some data structures in advance. We start with two data types: columns and cards. In TypeScript terms, they look like this:

type Column = {
  type: "column"      // - Since we don’t have individual tables per type,
                      //   we specify type in each doc
  _id: string         // - Our primary key
  position: number    // - Where the column is, eg. `0`, `1`, `2`
  label: string       // - What it says at the top of the column
}

type Card = {
  type: "card"
  _id: string
  column: string      // - The `_id` of the column this card is in
  position: number    // - An number that lets us order the cards
  title: string       // - What is says on the card
  createdBy: string   // - We’ll just use the username here,
                      //   instead of a proper ID
  createdAt: string   // - An ISO datetime string
  updatedBy?: string
  updatedAt?: string
}

We store these objects as JSON in our CouchDB, and _id is the primary key of each document3.

Now, we stated that we want to reduce the probability of conflicts, and the requirement of being able to re-order the cards in the columns provides a good opportunity to look at how this can be achieved.

Ordering the Cards in each Column

Let’s look at some traditional options for storing the card positions:

  • Keep an ordered list of all cards in each column in that column’s database entry. This updates a single record when a card’s position is changed, but it means that moving cards between columns causes two writes (add to target column, remove from source column). This approach would also cause a lot of writes to the column documents, and two users performing different tasks on the same column (eg. Alice is adding tasks, Bob is sorting the existing tasks) would lead to conflicts.
  • Store the actual indexed positions of each card in a column in each card’s database entry (eg. 0, 1, 2, 3). This would require us to read and write all cards below the card we’re placing in that column, since they would need to be renumbered (their index shifted by one). This is wasteful and asking for trouble.

What we’re doing instead is writing a bit more code in order to achieve this ordering feature with a single write to the card that is being moved, regardless of the circumstances. Instead of storing the cards’ positions as incremented integers (the way they’d be stored in an array), we use something that initally looks identical: 0, 1, 2. However, when we drop a card onto another card, we give it a position halfway between that of the target card and the one before it in the column (of course accounting for edge cases like first, last and zero cards). This techique is called fractional indexing. Moving the first card between the other two would yield the positions 1, 1.5, 2. Repeating that action yields 1.5, 1.75, 2. This way, we neither need to touch the other cards, nor the column that contains our moved card, nor the column we moved our card out of. This also means another user can move away one of the cards we’re currently moving our card between and everything will still end up where it’s supposed to.

This design gets us down to a single database update per card movement. This is a worthwhile optimisation, because multiple users collaborating on the same data will inevitably lead to situations where multiple users will want to concurrently change data belonging to the same record in the database, which will lead to conflicts. We want as few conflicts as possible. When designing data structures for CouchDB, two core principles for reducing the probability of conflicts are:

  1. Make your data granular. The more documents you spread your data across the less likely it is that multiple users try to write to the same one at the same time.
  2. Update as few documents as possible. Fewer writes means fewer opportunities for conflicts

So we’ve almost fully maxed out principle two here, reducing the database updates to one. The only way to expose even less attack surface for conflicts would be to never update a document, and only write new ones, but that’s a design pattern I’m purposely avoiding here4.

The card data structure we’ve got now is nice because it is quite conflict-averse while still being very straightforward: a <Card> in the UI is a card document in the database. It’s readable, easily understandable and debuggable. We’re not introducing an entirely new abstraction layer between the two.

Finally, we’ll also render the position as part of each card, to better visualise what’s going on when the cards are moved.

The Implementation

The real-time aspect of the app hinges on a CouchDB feature called the changes feed, or _changes. In short, it’s an endpoint that returns events from the database as they happen, or from whichever point in the event sequence (not whichever point in time) you specify. We use the PouchDB library to access it when our board component mounts:

onMount(() => {           // Svelte’s `onMount` handler
  const changes = db      // `db` is our PouchDB instance, connected to the remote CouchDB
    .changes<AnyDoc>({    // `AnyDoc` is a union of all possible doc types: `Card | Column`
      since: 0,           // Start at the beginning
      live: true,         // Continually receive changes as they happen
      include_docs: true, // Include the actual documents in the responses
    })
    .on("change", (change) => {
      if (change.doc) {
        switch (change.doc.type) {
          case "card":
            handleChange(cards, change)     // `cards` is an array of cards
            break
          case "column":
            handleChange(columns, change)   // `columns` is an array of columns
            break
          default:
            break
        }
      }
    })
  return () => {
    // Clean up after ourselves
    changes.cancel()
  }
})

For all intents and purposes, consider this a long-running event emitter. Handling the change with handleChange(collection, change) initally boils down to sorting the individual change documents into either an array of cards: Card[] or an array of columns columns: Column[]. These represent our main component state.

After each change is handled, we sort each of the arrays by the previously discussed position and reassign them so Svelte’s reactivity kicks in:

cards = [...cards.sort(sortBy("position"))]
columns = [...columns.sort(sortBy("position"))]

This reassignment will trigger re-rendering, so any change that comes in will automatically be displayed.

Much of the remaining code is concerned with the drag and drop functionality as well as the ability to edit existing cards’ titles, create new cards, or delete existing ones. In all of these cases, we want to store the change that occurred immediately. This can be either:

  • changing the position of an existing card doc
  • changing the title of an existing card doc
  • creating a new card doc
  • deleting an existing card doc (adding _deleted: true to it)

Deleting Documents in CouchDB

Before we look at storing data, we should first make sure we understand deletions. Deletion is slightly special in CouchDB. Since it is designed to be a replicating, distributed database, we can’t just delete a document from one of the CouchDBs, since it might be replicated back into existence from another CouchDB that still has the record.

To ensure that deletions can actually replicate between databases, deleted documents aren’t actually removed, but updated by adding _deleted: true to them. When interacting with the database, the document is now gone, but the deleted document is still replicated and appears in the _changes feed. So, when we let a user delete a card, we will add _deleted: true to it and put() it back into the database.

Now when a deletion gets propagated to our client via the _changes feed, the document will contain _deleted: true. Our change handler can check for the truthiness of this key, and remove the offending card from our local app state by splice()ing it out of our cards array.

Storing Data

The last function in <Board.svelte> is tryToPut(), which will encapsulate all of our attempts to add or update CouchDB documents. This is where the conflict handling code will eventually go. To make that simpler, all writes to cards will go through this function. Within the scope of this post however, we’ll forego handling conflicts properly, so this function is currently short and boring and error handling basically consists of logging sorry to the console. This means that when a conflict occurs, it will simply result in the current change being lost. To understand this better, we need to know how CouchDB handles document updates:

Document Revisions (_rev)

_rev is a deterministic string that exists on every CouchDB document and automatically tracks how many changes the doc has received, plus a hash of the document’s content, eg.:

"_rev": "3-866c4dc2c044a8afd92219a4220861b5"

The 3 denotes that this is the second change after the creation of the document (revisions start at 1, not 0), and the string after the dash is the hash of the content. This _rev lets CouchDB determine whether two revisions of the same document are different, and also whether they are in conflict5.

When updating a document in CouchDB, we must always provide the _rev the document in the CouchDB currently already has, to prove that we’re making the change to the same historical state of the document. If we provide an older _rev than the most recent one in the database, it means our local state was not up to date with the canonical single source of truth, and the change is rejected. This is how CouchDB avoids ”last write wins”. Most other databases do not keep track of revisions, and will blindly accept any write request, even if it destroys user data. This also ensures that requests arriving out of order or more than once don’t have interesting consequences.

To illustrate how this would affect our Kanban board: say Alice edits card 1, and while she is typing, Bob moves card 1 (updating the doc in the CouchDB and incrementing its _rev), so when Alice tries to save her changes, the put() gets rejected with a 409 conflict because her _rev for card 1 is now different than the one in the CouchDB. Accepting this write would constitute ”last write wins”, silently destroying Bob’s change, and so CouchDB will not do it.

Additionally, when multiple users make the same change to a document, they will all generate the exact same updated document with the same _rev, and CouchDB will not create any conflicts if these documents are synced to the same database. Since we’re not replicating (syncing) but only writing to the database directly, this doesn’t actually factor in to our application. It’s really neat though, and worth mentioning.

Current App Behaviour in Conflict Situations

In this first post, we don‘t deal with conflicts at all, beyond trying to cause as few of them as we can. CouchDB will prevent us from unwittingly destroying other users’ data, and our app state still has the change our user was trying to make, so we have access to both conflicting versions, and no data is irretrievably lost. While this is already better than what most other databases are able to do, it’s not a great user experience yet.

We’re not currently doing anything useful with the local conflicting revision. To the user, their change simply doesn’t happen, which is at least slightly better than causing someone else’s change to not have happened, because our non-handling of this issue is immediately observable to the user, so they could at least try again. But there’s room for improvement here, so improve we shall.

Recap and Next Steps

Alright, that’s a good start and a good basis for future posts. Let’s recap:

  • We have a real-time, multi-user Kanban board. Big yay, confetti!
  • It doesn't handle conflicts properly. Small boo, tiny pitchforks.
  • CouchDB takes a lot of complexity off our hands in terms of making our app real-timey: the _changes feed is very practical and easy to use, and _rev lets us avoid destroying other users’ data without any extra effort.
  • We did’t have to jump through many hoops to make this possible, data-wise. The document structures closely reflect the UI elements, and we didn’t have to add any additional data, abstractions or infrastructure to manage the real-timeyness of the app. We had a bit of a think how to best structure everything, and were a bit clever about storing the card positions, but otherwise, we didn’t do anything special. We didn’t even write any server code!
  • Even in this naïve implementation and without a much extra effort, this is already better than naïve implementations with other databases that default to ”last write wins” and lose user data.

This puts us in a good position for the next steps. Join us for the next posts, where we’ll deal with:

  1. Automatic conflict resolution and its limitations
  2. Manual conflict resolution, aka. asking a human to do it
  3. UI locking and its trade offs

Thanks for your interest and time!

Footnotes

  1. If you’re weary of learning this new concept and thinking: ”My relational database of choice doesn’t have conflicts”, you’re right. Your relational database of choice will not complain when two users write to the same record simultaneously. It will simply lose the first user’s change, silently and permanently, causing confusion and frustration. In many circumstances, that might work fine, because the situation simply doesn’t occur frequently enough to warrant the effort required to prevent it, but in a collaborative multi-user environment, you must deal with this properly. ↩

  2. Offline-capable/offline-first means: we treat the network as an optional resource; the web app can be fully functional without a network connection. Read more about the concept in the A List Apart post in which we introduced Offline First to the world in 2013. ↩

  3. Once stored, the documents will also receive another internal key, _rev, to track revisions. The PouchDB library will provide type safety for that as well, but since we never write _rev values ourselves before passing documents to PouchDB, they’re omitted here. PouchDB handles keys like _id and _rev in its generics, and we’re only typing the _id key because we want to define it before we hand the documents off to the PouchDB library. We could avoid this by always wrapping our docs in a PouchDB.Core.ExistingDocument or PouchDB.Core.PutDocument generic, which includes _id, but this way is a bit easier to understand and read, and works just as well. ↩

  4. In the ”write-once-update-never” approach, documents or records are never updated, all changes are completely new documents. These can either be entire documents, or diffs of changes, where the current state is constructed by applying all the changes in order. The latter is a bit like a ledger system, or how CRDTs work. This avoids conflicts occuring at the database level, but can still introduce branching and parallel realities that eventually need to be reconciled by a human. But if you absolutely need a stored copy of every change anyone has ever made, this is a way to go. We don’t, so we’ll do something simpler. ↩

  5. The _rev being a hash of the content of the document means that CouchDB can immediately determine whether two documents are identical or not without actually looking at the documents’ contents, which is great if the documents are huge. ↩

« Back to the blog post overview