One Row Per Drag

The naive kanban schema orders cards with integers: 1, 2, 3. Then a card moves and you renumber half the column inside a transaction, and your realtime subscribers all reflow, and the drag feels heavier than it should. The fix is old, elegant, and still weirdly obscure: stop numbering, start naming.

Keys with room between them§

Fractional indexing stores position as a string with lexicographic order — 'a0', 'a1', and when a card lands between them, 'a0V'. There is always another string between two strings. A move touches exactly one row, no matter how long the column is.

import { generateKeyBetween } from "fractional-indexing";

const first = generateKeyBetween(null, null); // "a0"
const second = generateKeyBetween(first, null); // "a1"
const between = generateKeyBetween(first, second); // "a0V"

// The drop handler writes ONE row:
await db.from("tasks")
  .update({ column_id: target, position: between })
  .eq("id", taskId);

The Postgres detail that matters§

Declare the column as text COLLATE "C". Linguistic collations will helpfully reorder your keys by locale rules and quietly corrupt the sort; the C collation compares bytes, which is exactly what the algorithm assumes.

Good schema design is mostly finding the representation in which the common operation becomes trivial.