Cruxly

Cruxly › System design › Design Exercises

Project & Issue Tracking System

a workflow isn't a status field, it's a graph the tenant defines and you enforce

Open the interactive lesson

Prompt: design the core of a project-management tool like Jira — teams create projects, file issues, move them through a workflow (To Do → In Progress → Done, or a custom sequence a team defines itself), assign people, and search/filter across potentially millions of issues, with hundreds of thousands of tenants each seeing only their own data.

45 minutes first. The interesting part isn't storing an issue. It's that "workflow" is per-tenant configurable data, not a fixed enum, and multi-tenant isolation has to hold under both correctness and performance pressure at once.

Applying the four moves: Scope — projects, issues, configurable per-team workflows, assignment, search across millions of issues, strict tenant isolation; integrations and reporting out. Estimate — tenant count and the size distribution across tenants, because a few huge tenants and many tiny ones force different isolation and indexing choices than uniform tenants would. Sketch — a multi-tenant store keyed by tenant, workflow definitions stored as data, and a search index scoped by tenant. Deep dive — workflow as per-tenant data rather than an enum means status values and transitions are validated at runtime against that tenant's definition, and tenant isolation has to hold in the search index too — the leak usually happens there, not in the primary store.

Scope, and why 'status' cannot be a fixed enum

In scope: issue CRUD, tenant-defined workflow states and transitions, assignment, full-text and field search across a tenant's issues, multi-tenant data isolation. Out of scope (say it): billing/subscription management, real-time collaborative editing of one issue's description (the collaborative-document exercise elsewhere in this track covers that mechanism if it comes up), cross-tenant reporting/analytics.

A hardcoded status enum (TODO, IN_PROGRESS, DONE) is the design that fails the moment a second customer wants a different workflow — one team wants Backlog → In Review → In Progress → Done, another wants an approval gate before Done. Because the prompt says workflows are configurable per team, status and the legal transitions between statuses are tenant-owned configuration data, not a fixed set of application-level constants. The system's job is to store and enforce an arbitrary tenant-defined workflow graph generically, the same way an interpreter enforces a program it didn't write.

Multi-tenancy is a correctness requirement here, not a deployment detail. Every single query in the system — list issues, search, load a workflow — must be scoped to exactly one tenant, and a bug that lets tenant A's query somehow return tenant B's issue is a severity-one incident, not a UX bug. This shapes essentially every schema and query decision from here down.

The classes: workflow as data, not code

public class WorkflowState {
    String id, name;             // e.g. "in_review", "In Review"
    boolean isTerminal;          // true for a workflow's "Done"-equivalent state(s)
}

public class WorkflowTransition {
    String fromStateId, toStateId;
    List<String> allowedRoleIds;  // who may perform this transition
}

public class Workflow {
    String tenantId;
    List<WorkflowState> states;
    List<WorkflowTransition> transitions;
    String initialStateId;

    /** The entire enforcement mechanism: is moving from `current` to
     *  `target` a transition this tenant's workflow actually defines? */
    boolean isLegalTransition(String currentStateId, String targetStateId, String actorRoleId) {
        return transitions.stream().anyMatch(t ->
            t.fromStateId.equals(currentStateId)
            && t.toStateId.equals(targetStateId)
            && t.allowedRoleIds.contains(actorRoleId));
    }
}

public class Issue {
    String id, tenantId, projectId;
    String currentStateId;        // references a WorkflowState.id, not a hardcoded enum
    String assigneeId;
    // ... title, description, etc.

    void transitionTo(String targetStateId, String actorRoleId, Workflow workflow) {
        if (!workflow.isLegalTransition(currentStateId, targetStateId, actorRoleId)) {
            throw new IllegalStateException("Illegal transition for this tenant's workflow");
        }
        currentStateId = targetStateId;
    }
}

Every issue references a WorkflowState by ID, never a fixed status string — this is the specific design decision that lets one system serve every tenant's differently-shaped workflow without a single if (tenant.usesCustomWorkflow) branch anywhere in the code. The workflow engine doesn't know or care what "In Review" means; it only knows whether the tenant's configured graph permits this specific edge.

isLegalTransition is the entire enforcement surface, and it must run on every state change, with no client-side-only check trusted. A UI that only shows legal transition buttons is a usability feature; the server-side check is the actual security and data-integrity boundary — an API call attempting an illegal transition directly must be rejected identically to one attempted through the UI.

Multi-tenant isolation: the schema decision that shapes everything else

Three real strategies, each a different point on the isolation-versus-efficiency spectrum, and the exercise is arguing the choice, not picking one by default.

Naming that the pool model's real risk is a forgotten WHERE tenant_id, and that row-level security is specifically the mitigation for that exact failure mode, is the answer that shows this was reasoned through rather than defaulted to "just add a tenant_id column and move on."

Search and scale: indexing millions of issues per tenant, cheaply

A relational query with a dozen WHERE clauses (assignee, status, label, text match, date range) degrades badly once a tenant has hundreds of thousands of issues — this is a search problem wearing a database's clothes, and the honest answer is a dedicated search index (Elasticsearch or equivalent) kept in sync with the source-of-truth database via change-data-capture or an outbox pattern, not an attempt to make the relational database itself fast at arbitrary multi-field filtering plus full-text search simultaneously.

The source of truth stays the relational store; the search index is a derived, eventually-consistent view. A write goes to the database first, then propagates to the search index asynchronously — which means a search result can be milliseconds stale immediately after a write, a trade-off worth naming explicitly rather than implying search is always instantly current. This is the same "index is a derived, rebuildable structure, never an independent authority" discipline as the payment ledger's balance cache elsewhere in this track.

Sharding, when a single tenant's issue volume or the aggregate multi-tenant volume outgrows one database, should be keyed by tenant_id. This keeps every query for one tenant (which is the overwhelming majority of real queries — a user is almost always working within their own tenant) on a single shard, avoiding a cross-shard fan-out for the common case, at the cost of a genuinely large tenant eventually outgrowing what one shard can hold — a real, statable limit rather than a problem this sharding key solves for every tenant size.

Follow-ups they will ask

"A workflow transition needs to trigger a side effect — send a notification, update a linked issue in another project." This is the same "one owner of a subsystem's real shape" motivation as the API gateway's protocol translation elsewhere in this track: workflow transitions publish an event (IssueTransitioned), and side-effecting subscribers (the notification system, cross-project link updater) react to it independently, rather than the core issue-tracking write path knowing about and calling every downstream consumer directly. Adding a new side effect becomes a new subscriber, not a change to the transition code.

"Support bulk operations — move 500 issues to a new status at once." Each individual transition still goes through isLegalTransition — bulk is a loop over the same single-issue operation, not a separate, less-checked code path, because a bulk operation that skips validation is exactly how a workflow's invariants get silently violated at scale. The interesting design question is failure handling: if issue 300 of 500 fails validation, does the whole batch roll back, or do the first 299 succeed and the failure gets reported per-issue — and this needs to be a stated, deliberate choice.

"A tenant wants to change their workflow after 10,000 issues already exist in it." Existing issues referencing now-removed or renamed states need an explicit migration story — either the old states remain valid destinations forever (workflow versions are additive-only), or a migration step remaps every existing issue's currentStateId to a state in the new graph, and silently leaving issues pointing at a state that no longer exists in the tenant's current workflow definition is a data-integrity bug waiting to surface the next time someone tries to load that issue's legal transitions.

How to say it in the interview

"The tempting shortcut is a status enum on the issue row - To Do, In Progress, Done - but the prompt says teams define their own workflow, which means status and the transitions between statuses are tenant-owned data, not application code. The actual exercise is modelling a workflow as a graph the tenant configures and the system enforces generically, plus making sure one tenant's data and one tenant's load can never leak into another's."

Checklist

Practice this interactively
Share this lessonWhatsAppLinkedInX

Related lessons