9.7.17 — Task Management: Boards, Workflows and Ordering

"Design a task management system — projects, tasks, assignees, statuses, and a board you can drag cards around on."

Everybody has used one of these, which is exactly why the question separates candidates so cleanly. The obvious model comes out in ninety seconds: a Task with a title, an assignee and a status enum, plus a Project holding a list of them. It is not wrong, and it collapses on the first two follow-ups every interviewer asks.

"Our support team needs different statuses from engineering." The enum is in the code, so a new status is a deployment, and now there are eleven statuses on every board because they all share one list.

"Dragging a card between two other cards has to keep that order for everyone." There is no field for it, and the first answer people reach for — a position number per task — requires rewriting every card below the drop point on every single drag.

Those two follow-ups are the page. The first is about making the workflow data instead of code, and the second is about ordering, which is a genuinely interesting problem hiding inside a boring one.

1. Three things that get conflated

Thing	What it holds	Changes when
The task	Title, description, fields	Someone edits it
Its state	Which status, who owns it	Someone moves it
Its position	Where it sits in a list	Someone drags it

Keeping them apart matters because they change at completely different rates and for different reasons. A task's text is edited rarely by one person. Its status changes a handful of times in its life and is subject to rules. Its position changes constantly, by drag, and is subject to no rules at all — you can put a card anywhere.

Fusing position into the task is what makes drag-and-drop expensive. Fusing the workflow into the code is what makes every customer's request a deployment.

2. The workflow is data, not an enum

Figure 1 — A workflow is a graph, and a graph is rows. Because the transitions are data, a support team's workflow and an engineering team's workflow are two sets of rows rather than two branches in a switch statement. Adding a status is an insert.

interface WorkflowState {                  // (1)
  id: StateId;
  name: string;
  category: "todo" | "active" | "done";    // (2)
}

interface Transition {
  from: StateId;
  to: StateId;
  name: string;                            // "submit", "approve" — the button's label
  requires: readonly Condition[];          // (3)
  then: readonly Effect[];                 // (4)
}

interface Workflow {
  states: readonly WorkflowState[];
  transitions: readonly Transition[];
  initial: StateId;
}

(1) A state is a row with a name a human chose. Nothing in the code knows the string "In Review".

(2) The category is the part that makes this design work, and it is the piece candidates miss. If nothing in the code may know the state names, how does a report count finished work? How does a board grey out completed cards? By category. Every custom state declares whether it counts as not-started, in-flight or finished, so the product can reason about progress without knowing anybody's vocabulary. This is the standard escape from "everything is configurable and therefore nothing can be computed".

(3) Conditions are checked before the transition is allowed: the task must have an assignee, the actor must hold a role, a required field must be filled.

(4) Effects run after it succeeds: assign to the person who requested it, clear a field, post a comment, notify watchers.

The transition function is then five lines and never changes:

function apply(task: Task, transitionName: string, actor: User, wf: Workflow): Task {
  const t = wf.transitions.find(               // (1)
    (x) => x.from === task.stateId && x.name === transitionName);
  if (!t) throw new IllegalTransition(task.stateId, transitionName);   // (2)

  for (const c of t.requires)
    if (!c.holds(task, actor)) throw new TransitionBlocked(c);         // (3)

  const moved = { ...task, stateId: t.to };                            // (4)
  return t.then.reduce((acc, effect) => effect.apply(acc, actor), moved);  // (5)
}

(1) Look up the transition by where the task is now and what was asked for. A transition that is not in the table for this state does not exist, which is how illegal moves are rejected without a single if about statuses.

(2) Not finding one is an error rather than a silent no-op, because the caller asked for something impossible and should hear about it.

(3) Every condition must hold. The failure carries which condition blocked it, so the interface can say "you must set an assignee first" rather than "not allowed".

(4) The new task is a copy with a new state. Nothing is mutated, which matters for the version check in section 8.

(5) Effects fold over the result in order. Adding "notify the reporter on approval" is a row, not a code change.

What this buys, and it is the entire reason to build it this way: a support team's workflow and an engineering team's workflow are two sets of rows. The available buttons on a task come from querying transitions out of its current state, so the interface is generated rather than written. And an illegal move is impossible not because something checks for it but because the transition is not in the table (9.4.14).

3. Ordering: why a position number is the wrong answer

Now the interesting part. Cards on a board have an order that users set by dragging, and it must be the same for everyone.

The obvious model is an integer position: 1, 2, 3, 4. Dragging card 9 to the top means every card that was 1 through 8 becomes 2 through 9. That is eight writes for one drag, on a board of 200 cards it is up to 200 writes, and two people dragging at the same time produce a mess that no ordering of the updates fixes.

The first improvement is to leave gaps: 100, 200, 300, 400. Dropping between 100 and 200 is now a single write of 150, which is a real improvement — one row instead of two hundred. But drop between 100 and 150 and you write 125, then 112, then 106, and after about seven drags in the same place the integers run out of room and you must renumber the whole list anyway.

Fractional numbers do not fix it either. Using floating point and taking the midpoint looks like it solves the problem forever, and it does not: a double has 52 bits of mantissa, so after roughly fifty successive midpoints between the same two cards the values become equal and the order is lost. It takes longer to break, and it still breaks, and it breaks silently.

The answer is a rank that is a string, compared alphabetically. Strings have no fixed precision — you can always make a new one between two others by adding a character.

function rankBetween(before: string | null, after: string | null): string {   // (1)
  const lo = before ?? "";                    // (2) start of the list
  const hi = after ?? null;                   // (3) end of the list
  ...
}

(1) The only operation needed: give me a rank that sorts after before and before after.

(2) Inserting at the top means there is nothing before it, so the lower bound is the empty string, which sorts before everything.

(3) Inserting at the bottom means there is no upper bound, so the new rank is simply the previous last rank plus a character.

How the middle case works, concretely. Suppose the alphabet is a to z, and two neighbouring cards have ranks "n" and "p". The midpoint is "o" — one write, done. Now insert between "n" and "o": there is no letter between them, so the new rank extends by a character, "nn", which sorts after "n" and before "o" because a prefix sorts before any longer string starting with it. Insert again between "n" and "nn" and you get "nf" or similar. The string grows by one character every time someone drops into the same tiny gap, and it never runs out.

Three properties are worth stating because they are the whole reason this design wins:

A drag is one write. Only the moved card's rank changes. Nothing else on the board is touched, so two people dragging different cards never conflict at all.

Two people dropping in the same gap is harmless. They generate two similar ranks and both are valid; the cards end up in some order, both users see a consistent board, and nobody's work is lost. Compare that with position integers, where the same event corrupts the sequence.

Ranks grow slowly and are rebalanced lazily. Repeated inserts in one spot lengthen the string, so a background job occasionally rewrites a list's ranks back to short evenly-spaced values. That is a rare maintenance operation rather than something on the drag path, and it is the honest cost of the design — worth naming before the interviewer asks.

Ties still need a rule. Two cards can end up with the same rank after a rebalance race, so the sort is always by (rank, taskId). The identifier breaks the tie deterministically, so every client renders the same order. Leaving this out gives you a board that flickers between two orders on refresh, which is one of those bugs that takes a week to track down.

4. Hierarchy: epics, stories and subtasks

Tasks nest, and the model is an adjacency list — each task stores its parent.

interface Task {
  id: TaskId;
  parentId: TaskId | null;      // (1)
  level: TaskLevel;             // (2) "epic" | "story" | "subtask"
}

(1) One nullable field. Reading a task's children is one indexed query; reading its ancestors is a walk upward, which is cheap because the depth is small.

(2) The level is stored rather than derived from depth, because the rules attach to the level. Epics may not have parents, subtasks may not have children, and a story's parent must be an epic. Storing it makes those checks a comparison rather than a tree walk.

Two rules the constructor enforces, and they are the entire safety of the structure: depth is limited to three levels, and a task's parent may never be one of its own descendants. That second one is the cycle check, and without it a careless drag makes a subtree that disappears from every query because it has no root.

The question interviewers actually ask here: when all subtasks are done, is the parent done?

Both answers are defensible and the point is to say which you chose and why.

Derived — the parent's status is computed from its children. Consistent by construction, and it means a parent can never be marked done with open work under it. The cost is that a parent cannot have a status of its own, which people want: an epic can be in review while one subtask is still open.

Independent, with a nudge — the parent has its own status, and completing the last subtask suggests closing the parent rather than doing it. This is what real systems do, because the parent usually represents work beyond the sum of its children — the release, the sign-off, the announcement.

Say the second, and say that the first is right for a checklist and wrong for a project.

5. Custom fields, and the trap under them

Every customer wants fields nobody else wants: "customer tier", "environment", "story points". Three ways to store them, and the choice has consequences.

Approach	Query speed	Schema changes	Fits when
Column per field	Fast	Migration each time	Fields are few and fixed
JSON column	Good with an index	None	The normal answer
Row per value	Slow, many joins	None	Rarely the right call

A JSON column on the task, holding a map from field identifier to value, is the answer to give. The field definitions — name, type, whether required, which project — live in a proper table, so the system can validate values, render the right control, and know that "story points" is a number. The values live in JSON because they are sparse and per-customer, and modern databases index inside JSON well enough to filter on them.

Row per value — one row per task per field — is the design that looks most flexible and hurts most. Filtering on three fields becomes three joins, sorting is awkward, and types are stringly. Name it and reject it; recognising a classic wrong turn is worth as much as picking the right one.

The rule that keeps this from becoming chaos: a custom field may never carry meaning the system needs. The moment the product must know whether a task is finished, that cannot live in a customer-defined field — which is what the state category in section 2 exists for.

6. Permissions, in one place

Permissions in this kind of product are per project and per role, and the failure mode is not getting the rules wrong. It is scattering them.

interface Permission {
  can(actor: User, action: Action, task: Task, project: Project): boolean;   // (1)
}

type Action =
  | { kind: "view" } | { kind: "edit" }
  | { kind: "transition"; to: StateId }        // (2)
  | { kind: "comment" } | { kind: "delete" };

(1) One question, asked in one place. Every route, every board, every bulk operation calls this and nothing implements its own version.

(2) Transitioning is a distinct action carrying its destination, because "anyone may move a task to In Progress but only a reviewer may move it to Done" is the normal requirement. Modelling transition permission as generic edit permission makes that unexpressible, and it is the most common mistake in this section.

The interaction worth calling out: a transition's requires conditions from section 2 and the permission check are two different things and both must run. The workflow says what is possible from here; permissions say who may do it. Merging them means one project's role model leaks into every project's workflow definition.

And the list endpoint is where permissions get slow. Checking each task individually turns one query into a thousand. Push the rule into the query — filter by the projects the actor can see — and treat the per-task check as the final guard for a single task rather than as the mechanism for lists.

7. Activity, comments and notifications

Store the activity as an event log, not as a set of fields updated in place. Every change appends a record of who did what, when, and from what to what. Two things fall out for free: the task's history is the log read forward, and "what happened while I was away" is the log filtered by time. Reconstructing history from a lastUpdatedBy field is impossible, and adding the log later means everything before that date is gone.

Watchers are subscribers (9.4.13). Anyone can watch a task; the assignee, the reporter and anyone who commented are watchers by default. The event log is the source, and notification is a consumer of it rather than something the transition code calls directly — which is what keeps a slow email service from slowing down a status change.

Three rules that make notifications tolerable, and the third is the one people forget:

Do not notify the actor about their own action. Obvious, and constantly shipped wrong.

Batch. Ten field edits in a minute is one notification, not ten. A short delay before sending, during which further events for the same task collapse into the same message, costs nothing and changes the product from unusable to useful.

A mention is a different priority from a change. Being named in a comment should reach someone who has muted the rest of the project. Treating all notifications as one stream means people turn the whole thing off, and then the important one never arrives either.

8. Concurrency: two people, one task

Two people editing the same task is the standard problem, and the standard answer is a version number on the task. Each update carries the version it read, and the write is conditional on that version still being current. If it is not, the second writer is told the task changed rather than silently overwriting.

UPDATE tasks SET title = :title, version = version + 1
 WHERE id = :id AND version = :expectedVersion;

Zero rows affected means someone else got there first. This is optimistic concurrency, and it is right here because genuine conflicts are rare — most simultaneous edits are two people touching different fields of the same task.

Which suggests the refinement worth volunteering: conflicts can be detected per field rather than per task. If one person changed the assignee and another changed the description, there is no real conflict, and a version check on the whole row invents one. Storing the version per field, or comparing which fields actually changed, turns most "someone else edited this" messages into no message at all.

Two people dragging to the same position needs no locking at all, which is the payoff from section 3. Both writes touch only their own card's rank, both succeed, and the tie-break by identifier gives everyone the same order.

Transitions are the one case that needs real care, because a transition's effects can include creating things — a branch, a release entry, a notification to a customer. Running the same transition twice must not run the effects twice, so the transition write is conditional on the current state being the expected one, and the effects run only if that write affected a row. The same conditional-claim shape as everything else in this chapter.

9. What the interviewer will push on

"A customer wants different statuses from everyone else." The workflow is data: states and transitions as rows, per project. The transition function looks up (current state, requested transition) and either finds a row or rejects. Then the piece that makes it actually work — every custom state declares a category of not-started, in-flight or finished, so reports and boards can reason about progress without knowing anybody's status names. Without categories, fully configurable statuses mean nothing in the product can compute anything.

"How do you store the order of cards on a board?" Not integer positions — one drag rewrites every card below the drop. Not gapped integers — they run out after about seven drops in the same place. Not floats — a double loses ordering after roughly fifty midpoints, silently. Use a string rank compared alphabetically, where a new rank between two others is either the midpoint character or the lower rank plus a character. One write per drag, no conflict between people dragging different cards, and a lazy rebalance job when ranks grow long.

"Two people drag cards to the same slot at the same time." Nothing goes wrong. Each write touches only its own card, both ranks are valid, and the sort is by (rank, taskId) so the identifier breaks the tie and every client renders the same order. Compare this to the integer scheme, where the same event corrupts the whole sequence — that comparison is the answer, not just the mechanism.

"When every subtask is done, is the epic done?" Say that both designs are defensible, pick independent-with-a-nudge, and give the reason: a parent usually represents work beyond the sum of its children — the sign-off, the release, the announcement — so completing the last subtask should suggest closing the parent rather than doing it. Derived status is right for a checklist and wrong for a project.

"Custom fields." Definitions in a real table so values can be validated and rendered; values in a JSON column on the task because they are sparse and per-customer. Reject the row-per-value design out loud — filtering on three fields becomes three joins and every value is a string. And state the boundary: a custom field may never hold something the product needs to reason about, which is exactly what state categories are for.

"Two people edit the same task." A version number and a conditional update; zero rows affected means someone else got there first. Then volunteer the improvement: most simultaneous edits touch different fields, so comparing changed fields rather than the whole row turns most conflict messages into no message.

The thing to volunteer that nobody asks for: notification batching and the mention exception. Ten edits in a minute must be one notification, not ten, and being named in a comment has to reach someone who muted the project. Candidates design the data model and treat notifications as a detail; they are the reason people either live in the product or turn it off, and saying so shows you have thought about the thing being used rather than the thing being built.

Recall

• Keep the task, its state and its position separate — they change at completely different rates and for different reasons.
• The workflow is data: states and transitions as rows per project. The transition function looks up (current state, name), checks conditions, applies effects. An illegal move is impossible because the row does not exist.
• Every custom state carries a category (not-started, in-flight, finished) so the product can compute progress without knowing anyone's status names.
• Ordering: integer positions rewrite the whole list; gapped integers run out in about seven drops; floats lose ordering after about fifty midpoints, silently. Use a string rank compared alphabetically — one write per drag, and lengthen the string when there is no character in between.
• Sort by (rank, taskId) so ties break deterministically and every client shows the same order.
• Hierarchy is an adjacency list with a stored level, a depth limit and a cycle check. Parent status is independent with a nudge, not derived — a parent means work beyond the sum of its children.
• Custom fields: definitions in a table, values in a JSON column. Never put something the product must reason about into a customer-defined field.
• One permission function, and transition carries its destination, because "anyone may start, only a reviewer may finish" is the normal rule. For lists, filter in the query rather than checking per task.
• Activity is an event log. Watchers subscribe to it; notifications consume it. Batch them, never notify the actor about their own action, and treat a mention as a different priority from a change.
• Optimistic concurrency with a version and a conditional update. Per-field comparison removes most false conflicts. Rank writes need no locking at all.

Self-test: Why does a status enum in code fail? What do state categories rescue? Name the three ordering schemes that break and how each one breaks. What breaks a rank tie? Is a parent done when its children are? Where do custom field definitions live, and where do values?

Quiz Bank

FoundationalA customer needs their own statuses and their own rules about who can move a task where. Design it so this is configuration rather than a deployment.

Start by naming what fails. A status enum in the code means a new status is a code change, and every project shares one list, so after five customers the board has eleven statuses and none of them mean the same thing to everybody. The rules go the same way: a switch over statuses deciding what may follow what is a function that every customer needs to edit.

So the workflow becomes a graph stored as rows.

interface WorkflowState { id: StateId; name: string; category: "todo" | "active" | "done"; }

interface Transition {
  from: StateId; to: StateId; name: string;
  requires: readonly Condition[];
  then: readonly Effect[];
}

A state is a row with a human-chosen name. A transition is a row saying which state it leaves, which it enters, what the button is called, what must be true first, and what happens afterwards.

The transition function then never changes:

const t = wf.transitions.find(x => x.from === task.stateId && x.name === name);
if (!t) throw new IllegalTransition(task.stateId, name);
for (const c of t.requires) if (!c.holds(task, actor)) throw new TransitionBlocked(c);
const moved = { ...task, stateId: t.to };
return t.then.reduce((acc, e) => e.apply(acc, actor), moved);

An illegal move is rejected not because something checks for it but because there is no row for it. The buttons shown on a task are the transitions out of its current state, so the interface is generated from the same data.

Now the part that makes configurable statuses actually workable, and it is the piece most answers are missing. If the code may not know the state names, how does a burndown chart count finished work? How does the board grey out completed cards? By category: every state declares whether it counts as not-started, in-flight or finished. The customer owns the vocabulary and the product owns the meaning, and that is the trade that makes full configurability possible without making the product unable to compute anything.

Permissions stay separate from the workflow, and both run. The workflow says what is possible from this state; permissions say who may do it. The permission check needs the destination state as part of the action, because "anyone may start work, only a reviewer may mark it done" is the normal requirement and it is unexpressible if transitioning is treated as generic editing.

Two things to volunteer.

Conditions and effects are small named objects rather than code in the transition. "Requires an assignee", "requires role reviewer", "assign to the person who requested it", "notify watchers" — each is a row referencing a known behaviour, so a customer builds workflows from a menu instead of writing anything.

Workflows need versioning. Tasks are sitting in states right now, and if someone deletes a state the tasks in it become unreachable. Either forbid deleting a state that is occupied, or version the workflow so in-flight tasks continue under the definition they started with. Raising that before it is asked about shows you thought about the system being changed while it runs.

AppliedDesign the ordering of cards on a board so that dragging one card is a single write and two simultaneous drags do not corrupt anything.

Walk the three schemes that fail, because knowing why they fail is the answer.

Integer positions 1, 2, 3. Dragging the ninth card to the top renumbers eight cards. On a 200-card board a drop at the top is 200 writes for one gesture, and two concurrent drags interleave into a sequence with duplicates and gaps.

Gapped integers 100, 200, 300. A drop between two cards is now one write of the midpoint, which is a real improvement. But repeated drops in the same place halve the gap each time — 150, 125, 112, 106, 103, 101 — and after about seven the integers touch and the list must be renumbered.

Floating point midpoints. This looks like it never runs out and it does. A double has 52 bits of mantissa, so after roughly fifty successive midpoints between the same pair the two values become equal and the order is simply lost, with no error and no warning.

The answer is a rank stored as a string and compared alphabetically, because strings have no fixed precision. There is always a string between two different strings — if not by changing a character, then by adding one.

function rankBetween(before: string | null, after: string | null): string

Inserting at the top — nothing before it, so the lower bound is the empty string, which sorts before everything.

Inserting at the bottom — nothing after it, so the new rank is the last rank plus a character.

Inserting between "n" and "p" — take the middle character, "o". One write.

Inserting between "n" and "o" — no character sits between them, so extend: "nn" sorts after "n" because a prefix sorts before any longer string beginning with it, and before "o" because the first character decides. The string grows by one character, and it can keep growing indefinitely.

Why this satisfies the requirement exactly.

One write per drag. Only the moved card's rank changes, so a drag is a single update regardless of board size.

No conflict between different cards. Two people dragging different cards touch different rows and never interact.

Two people dropping into the same gap is harmless. Both compute a valid rank, both write, and both cards land in that gap in some order. Nothing is corrupted and nobody's action is lost — which is the exact scenario that destroys the integer scheme.

Two details that finish the design.

Sort by (rank, taskId). Equal ranks are possible after a rebalance race, and without a deterministic tie-break different clients render different orders, which shows up as a board that flickers on refresh.

Rebalance lazily. Repeated inserts in one spot lengthen ranks, so a background job occasionally rewrites a list's ranks to short evenly-spaced values. That is the honest cost of this design, it is off the drag path, and naming it before the interviewer does is better than being caught by it.

One extra worth mentioning. The same rank belongs to a list, and a card moving between columns is changing both its state and its rank in the destination list. Those are two different fields changing in one operation, which is a good reason for the drag endpoint to be one request rather than two — otherwise a card can briefly exist in the new column at the old position, and users see it jump.

InterviewTwo people open the same task and edit it. What happens, and what should happen?

Name the default failure first: last write wins, silently. Both load the task, both save, and the second save overwrites the first with no indication that anything was lost. The person whose edit vanished usually assumes they forgot to press save, so this bug is invisible in support tickets and constant in reality.

The standard fix is optimistic concurrency: a version number on the task. Every read returns the version, every write carries the version it was based on, and the write is conditional:

UPDATE tasks SET title = :title, description = :description, version = version + 1
 WHERE id = :id AND version = :expectedVersion;

Zero rows affected means the version moved, which means someone else wrote first. The second writer is told the task changed and shown what changed, rather than being allowed to overwrite.

Why optimistic rather than locking. Genuine conflicts on a task are rare — most simultaneous editing is two people touching different parts of the same task. Pessimistic locking would make everyone wait for a lock that is almost never contended, and it introduces the problem of a lock held by someone who closed their laptop. Optimistic control costs one integer and one condition, and it only interferes in the rare case it exists for.

Then the refinement worth volunteering, because it turns a correct answer into a good product. A version on the whole row treats every simultaneous edit as a conflict, including the very common case where one person changed the assignee and another changed the description. Comparing which fields actually changed — or keeping a version per field — means those two saves both succeed and nobody sees a warning. Conflicts then only surface when two people really did change the same field, which is when a human genuinely has to choose.

Transitions need stronger treatment than edits, and this is the part interviewers push on. A transition can have effects: create a release entry, notify a customer, start a build. Running it twice runs the effects twice. So the state change is a conditional write on the current state being the expected one, and the effects run only if that write affected a row. Same shape as every conditional claim in this chapter — the write decides, and everything else follows from whether it succeeded.

Comments are the case that needs none of this. They are appends, not edits, so two people commenting at once is not a conflict in any sense. Recognising which parts of a model need concurrency control and which do not is worth saying out loud, because applying version checks uniformly across a system is a real and common over-correction.

And the ordering case needs nothing either. A drag writes only the moved card's rank, so two simultaneous drags never touch the same row. That is a property bought by the rank design, and it is a good closing observation: the concurrency story of a system is decided by its data model long before any locks are chosen.

StaffThis is now a product with ten thousand customer organisations, some with a million tasks. What breaks first, and how do you keep the board and the search fast?

The first thing to say is which dimension actually hurts. It is not the total number of tasks; it is the largest single organisation and the largest single board. Systems like this are naturally partitioned by organisation, so a million small customers is easy and one enormous customer is the whole problem. Design for the biggest tenant, not for the average.

What breaks first, in order.

Board reads on huge lists. A column with 20,000 cards cannot be sent to a browser. The board is paginated by rank — fetch the first hundred cards of each column ordered by (rank, id), then fetch more as the user scrolls. Because rank ordering is stable and total, that pagination is a simple range scan rather than an offset, which is what keeps page fifty as fast as page one.

Search and filter. "All open tasks assigned to me, with label X, ordered by priority" across a million rows and custom fields is not a query the primary database should serve at interactive speed for every user. Push search into a dedicated index, updated from the activity log, and accept that it is a second or two behind. That staleness is safe here for the same reason as everywhere else in this book: search is a hint, and opening the task reads the truth.

Notification fan-out. A task on a busy project can have fifty watchers, and a bulk edit of five hundred tasks becomes twenty-five thousand notifications. This is the piece that takes down the email path first. Batch per recipient over a short window, collapse multiple events on the same task into one message, and treat bulk operations as a single notification about the operation rather than one per task.

The activity log itself. It grows without limit and it is the most-written table in the system. Partition it by organisation and by time, keep recent activity hot, and move older activity to cheaper storage that a rarely-used history view can read.

Two decisions that matter more than any of the individual fixes.

Partition by organisation, everywhere. Every query carries the organisation, every index leads with it, and no request ever scans across tenants. This is what makes one customer's enormous board a problem confined to that customer, and it is also the strongest protection against a bug leaking data between organisations, because the boundary is in the shape of the data rather than in a filter someone might forget (10.6).

Keep derived data derived. Board counts, progress bars, "tasks in this state" figures — every one of them is computed from tasks and is tempting to store on the project. Cache them if they are slow, but the tasks remain the truth and the cache is rebuildable. A stored counter that drifts is a support ticket that nobody can explain, and this system will have many opportunities to drift.

The scaling behaviour of the rank design is worth a sentence, since it is the unusual part of this model. Long ranks are the cost of many inserts in one place, and the rebalance job that shortens them must run per list, take the list briefly, and be safe to interrupt. It never blocks a drag; it only makes future ranks shorter. That is the right shape for maintenance work — off the user's path, resumable, and with no correctness role.

What I would monitor, because these are the signals that decay quietly rather than failing loudly: the p99 time to render the largest boards, which is the number the biggest customer actually feels; the lag between an edit and it appearing in search, since that is when users start saying the product is broken; the rank length distribution, which tells you whether rebalancing is keeping up; and notifications sent per user per day, because the moment that number gets uncomfortable people mute everything and the product quietly stops working for them.

Flashcards

FlashWorkflow as data

States and transitions are rows per project. The transition function looks up (current state, name), checks conditions, applies effects. An illegal move is impossible because the row does not exist.

FlashState categories

Every custom state declares not-started, in-flight or finished. The customer owns the vocabulary, the product owns the meaning — without this, configurable statuses make reports impossible.

FlashWhy not integer positions

One drag rewrites every card below it. Gapped integers run out after about seven drops in the same place. Floats lose ordering after about fifty midpoints, silently.

FlashString ranks

Compared alphabetically, so there is always a rank between two others — take the middle character, or extend by one. One write per drag, no cross-card conflicts, lazy rebalancing when strings grow long.

FlashThe tie-break

Sort by (rank, taskId). Equal ranks are possible, and without a deterministic tie-break different clients render different orders — a board that flickers on refresh.

FlashCustom fields

Definitions in a real table so values can be validated and rendered; values in a JSON column because they are sparse. Never put something the product must reason about into a customer-defined field.

Next: 9.7.18 — the community platform family, where the hard part is not storing a post but deciding what a million people see.