case study 02
01 / 05
context
Kanban looks simple until two people drag cards in the same column at the same moment. Each move is a read followed by writes, and under Postgres's default READ COMMITTED two concurrent moves can both read the same order and then overwrite each other: a 500 from the unique constraint, or a card stranded at a temporary position. Flowboard is a shared board (owner plus invited members) built to get that part right.
decisions
- 01Dense integer positions instead of fractional indexing or LexoRank. Every column is always
0..n-1, enforced by a unique constraint and checkable in a test. The cost is rewriting the column on each move. At Kanban sizes that is cheap (measured below); I would switch to fractional keys for lists with thousands of items or many concurrent writers. - 02A parent-row lock instead of
SERIALIZABLE. Each position-changing write starts withSELECT … FOR UPDATEon its column (or board, for column reordering). Serializable isolation is also correct, but it surfaces conflicts as errors the client has to retry. A row lock makes Postgres do the waiting, so the code stays a plain transaction. - 03Locks taken in id order. A cross-column move locks both columns sorted by id, so A→B and B→A at the same time cannot deadlock.
- 04
409instead of a silent revert. The card's column is re-read after the lock is held. If a teammate moved it first, the API returns409and the board refetches and says so.
evidence
- 01Race tests for crossing moves in both directions, concurrent column reorders, and a same-card stale move. That last test holds both column locks until both requests are queued, so the race happens on every run, and asserts exactly one
200and one409. - 02A
demo/no-locksbranch removes only theFOR UPDATEclauses. Its CI run fails: crossing moves return 500 and both stale moves return 200 (a lost update). no-locks CI run - 03A move on a 30-card column, including the access check, lock and full rewrite: p50 13 ms (p95 17 ms). Between two 30-card columns: p50 27 ms (p95 31 ms). Measured with
pnpm benchover 200 runs, Postgres 16 in Docker. - 04Full reasoning:
docs/ordering.md. writeup
limits
- 01I first claimed the same-column race test proved the locks were needed. It passes without them. The tests that actually fail without locks are the crossing-moves and stale-move tests, and the writeup now says so.
- 02No realtime transport: other members see changes on refetch, not live. That's a deliberate scope choice.
- 03Authorization is per board; there is no workspace or tenant layer above boards.
stack
- NestJS
- PostgreSQL
- Prisma
- Next.js
- TypeScript
- Docker