SYSTEM DESIGN · DESIGN PROBLEMS · BUSINESS PLATFORMS · MEDIUM · 49 OF 51

Design an Issue Tracker like Jira: Workflows, Permissions, Search and Big Tenants

Design a multi-tenant issue tracker: projects, issues with readable keys, configurable workflows, role-based and issue-level permissions, custom fields, concurrent edits, boards with drag-to-rank, filter search over billions of issues, an activity history, watcher notifications, and how to keep one enormous customer from hurting everyone else.

Reported atAtlassian

Try it on the practice board Drag the blocks, wire them up, and check your design against the reference.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
  42. 42
  43. 43
  44. 44
  45. 45
  46. 46
  47. 47
  48. 48
  49. 49
  50. 50
  51. 51
1 / 19

Press play to hear the narration, or step through at your own pace.

Notes

Listen

Listen. A tenant is a company with projects, and projects hold issues with readable keys like PAY ten forty-two. Issues have standard fields plus custom fields the customer defines. Each project has a workflow: states and the transitions allowed between them. Permissions come from project roles, and some issues are restricted further. Users work on boards, search with filters, comment, see history and get notified.

The qualities. Opening an issue should take under two hundred milliseconds, and search under a second. When someone drags a card, teammates should see it within a couple of seconds. Two people editing must never silently overwrite each other. A search must never reveal an issue the user may not see; that is a security bug, not a glitch. And one huge tenant must not slow down everyone else.

Functional
  • tenants → projects → issues
  • issue key like PAY-1042
  • fields: status, assignee,
  • priority, labels, custom
  • workflows: states + allowed
  • transitions per project
  • permissions by role, plus
  • issue-level security
  • boards, search, comments,
  • history, notifications
Non-functional
  • issue view p99 ≤ 200 ms
  • search p99 ≤ 1 s
  • board changes seen by others
  • within ~2 s
  • no silent lost updates
  • never show an issue the user
  • may not browse
  • tenant isolation, and one big
  • tenant cannot slow others
  • 99.9% availability

Ask scale

Scale. Fifty thousand tenants and ten million daily users. Fifty reads per user per day is five hundred million, about fifty-eight hundred a second, and since work happens in office hours, plan for seventeen thousand at peak. Writes are ten times fewer, around seventeen hundred a second at peak. Two billion issues at five kilobytes is ten terabytes, history adds six more, and attachments are most of the bytes, in object storage. The largest tenant has thirty million issues.

Back of the envelope
50k tenants, 10M daily users
reads: 50/user/day = 500M/day
  ≈ 5,800/s, office-hours ×3
  ≈ 17,000/s
writes: 5/user/day = 50M/day
  ≈ 580/s, peak ≈ 1,700/s
issues 2B × 5 KB ≈ 10 TB
history 20B rows × 300 B ≈ 6 TB
attachments ≈ 800 TB (objects)
largest tenant: 100k users,
  30M issues

Define API

The API. Create an issue in a project and get back its key. Edits are patches that send only the changed fields, with the version the client last saw. Status changes are a separate transition call, because they run workflow rules, not just a field write. Comments, a filter search with a small query language and a cursor, and the activity history for an issue round it out.

API
POST  /v1/projects/PAY/issues
  { type, summary, fields }
  → 201 { key: "PAY-1042" }
PATCH /v1/issues/PAY-1042
  If-Match: 17  { assignee: "u9" }
POST  /v1/issues/PAY-1042/transitions
  { to: "In Review" }
POST  /v1/issues/PAY-1042/comments
GET   /v1/search?q=project = PAY
  AND status = Open&cursor=
GET   /v1/issues/PAY-1042/activity

Data model

The data is relational: projects, issues, comments, workflows and memberships, with every key starting with the tenant id, so a tenant's rows live together and shard together. Custom fields go into a JSON column rather than a table per field, since each tenant defines different ones; the search index handles filtering on them. Every change appends a row to issue history, which powers the activity feed and audit.

Tables (SQL)
projects(tenant, id, key, next_num,
  workflow_id, perm_scheme_id)
issues(tenant, id, project, num,
  type, status, assignee, priority,
  rank, custom JSONB, version)
issue_history(issue, seq, actor,
  field, old, new, at)  append-only
comments(issue, id, author, body)
workflows(id, ver, states, transitions)
members(project, user, role)
PK starts with tenant → co-located

High-level design

The write path. The gateway authenticates and routes to the tenant's shard. The issue service asks the workflow and permission module, which reads cached project config, whether this user may make this transition. If so, it updates the issue and appends history in one transaction, and records a change event through an outbox, which lands on Kafka keyed by issue.

Consumers of the change stream do the rest. One updates the search index within about a second. One pushes the change over WebSockets to everyone viewing that board or issue. One notifies watchers and mentioned users. Attachments never pass through the issue service: clients upload directly to object storage with signed URLs, and the issue only stores a reference.

Deep dive: workflows

Deep dive one: workflows. Each workflow is data, not code: states, and transitions that carry conditions, like only reviewers may approve, validators, like a resolution must be set, and post actions, like clearing the assignee. Apply a transition with a conditional update on the current status, so two people clicking at once cannot both succeed. When an admin edits a workflow, save a new version and run a background job that maps old statuses to new ones.

Workflow as data
states: To Do, In Progress,
  In Review, Done
transition In Review → Done:
  condition: role = Reviewer
  validator: resolution is set
  post: clear assignee, notify

UPDATE issues SET status = Done
 WHERE id = :i AND status = Review
edit workflow → new version +
  status mapping job for old issues

Deep dive: permissions

Deep dive two: permissions. A permission scheme maps roles to actions, and memberships map users and groups to roles in each project. Some issues also carry a security level, an extra allow-list for sensitive tickets. Checking this on every request is expensive, so resolve the action set for each user and project once and cache it. When a membership or scheme changes, an event evicts the affected entries immediately rather than waiting for the expiry.

Permissions must also apply to search. Filtering after the search returns broken pages, short or empty, and leaks counts. Instead, add the tenant, the list of projects the user may browse, and their allowed security levels as filters inside the query itself. If a user can browse thousands of projects, index an access key per group on each issue and filter on the user's groups, which keeps the query small.

Permission model
  • scheme: role → actions
  • Developer: browse, edit,
  • transition, comment
  • Viewer: browse only
  • members: user/group → role
  • issue security level: extra
  • allow-list on single issues
  • cache: (user, project) →
  • action set, ~5 min TTL
  • membership change → evict now
Permission-aware search
bad: search, then drop hidden
  hits → empty or short pages

good: add to every query
  tenant = 77
  AND project IN (browsable)
  AND (security IS NULL
       OR security IN (mine))

user sees 2,000 projects?
  index a per-group access key

Deep dive: edits

Deep dive three: concurrent edits. If the client saves the whole issue, the second save silently wipes the first person's change. Send patches of changed fields with the version you saw. If someone else changed different fields since, merge both. If they changed the same field, return a conflict and show both values. Long descriptions are rarer and messier, so use a soft edit lock or a merge view. Live updates keep everyone's view fresh, so conflicts stay rare.

Concurrent edits
  • whole-object save: B wipes
  • A's change (lost update)
  • patch + version per issue:
  • different fields → merge
  • same field, stale → 409,
  • show both values
  • description: long text, use
  • a draft lock or merge view
  • live push keeps views fresh

Deep dive: search

Search. Users combine any fields, custom fields and free text with any sort order, and no set of SQL indexes covers that. So each change is indexed as a document in a search engine, routed by tenant, with the largest tenants in their own indices. The index lags by about a second, so after your own edit the client merges the fresh issue into the results, and you never see your change disappear. If the index is lost, rebuild it from SQL.

Search design
  • why not SQL: ad hoc filters
  • on any custom field + text
  • + sort = no index fits
  • index doc = issue + fields
  • route by tenant; giant
  • tenants get their own index
  • lag ≈ 1 s; after your own
  • edit, merge the fresh row
  • into the results
  • rebuild from SQL any time

Deep dive: boards

Two small but classic details. Issue numbers come from a counter on the project row, incremented in the same transaction as the insert, so a rollback never burns a number. Even a busy project creates a few issues a second, so this row is not a hot spot. Board order uses rank strings: dropping a card between ranks b and c gives it b m, so one row changes instead of renumbering five hundred. When ranks grow long, rebalance in the background.

Keys and ranks
key: in the create transaction
  UPDATE projects
    SET next_num = next_num + 1
  WHERE id = :p RETURNING next_num
  → PAY-1042, no gaps on rollback

drag card between rank b and c:
  new rank = "bm" (string midpoint)
  one row updated, not 500
ranks get long → rebalance
  the column in the background

Deep dive: activity

Activity and notifications. The activity feed for an issue or project is just a query over the history table, newest first; there is no need to fan out to per-user feeds. Notifications go to watchers: the reporter, the assignee, commenters and anyone who clicked watch. Batch them per user, so ten quick edits become one email, send mentions right away, and never notify people about their own changes.

Activity and notify
  • activity feed = query over
  • issue_history, newest first
  • (pull, not fan-out)
  • watchers: reporter, assignee,
  • commenters, explicit watch
  • batch per user: 10 edits in
  • 2 min → 1 email
  • mentions: instant
  • skip the actor themself

Deep dive: big tenants

Large tenants. Shard by tenant into cells: many small tenants share one, and the biggest get a cell of their own, with its own database and indices. Moving a tenant means copying, catching up on changes, then flipping the shard map during a short write freeze. Apply rate limits per tenant. Bulk operations, like editing fifty thousand issues, run as throttled, resumable background jobs, and expensive dashboard filters are cached for a minute.

Large tenants
  • shard map: tenant → cell
  • small tenants share a cell
  • top tenants get their own
  • move tenant: copy, catch up,
  • flip the map, brief freeze
  • per-tenant rate limits
  • bulk edit 50k issues = job,
  • throttled, resumable
  • heavy saved filters: cache
  • results for a minute

Trade-offs

Trade-offs. The issue itself is strongly consistent in SQL. Everything around it, search, live boards, notifications, is eventually consistent, about a second behind, and can be rebuilt from the change stream. If search is down, simple list views fall back to SQL. A notification backlog delays emails but never blocks edits. Stale permission caches are handled with eviction events plus a short expiry, because a stale grant is a security issue.

Trade-offs and failures
  • issue row: SQL, strongly
  • consistent, the truth
  • search, boards, mail: ~1 s
  • behind, rebuilt from events
  • search down → list views from
  • SQL for simple filters
  • notify backlog → delay mail,
  • never block edits
  • config cache stale → evict on
  • change events + short TTL

Interview summary

In the interview: seventeen thousand reads and seventeen hundred writes a second at peak. A relational store with every key starting with the tenant. Workflows as versioned state machines applied with conditional updates. Permissions cached per user and project, and applied inside every search. Field-level patches with versions. An outbox feeding Kafka, which updates search, live boards and notifications. And cells that give the largest tenants their own infrastructure.

Say it like this
  • 17k reads/s, 1.7k writes/s
  • SQL, keys start with tenant
  • workflow = versioned state
  • machine, conditional update
  • perms cached per user+project
  • and applied inside search
  • patch + version, merge fields
  • outbox → Kafka → index, live
  • boards, notifications
  • cells: giants get their own

Interview questions

How do you generate issue keys like PAY-1042?

Keep a next-number counter on the project row and increment it with UPDATE ... RETURNING in the same transaction that inserts the issue, so numbers are sequential per project and a rollback does not leave a gap.

How do you model custom workflows?

As versioned data: a set of states and transitions, each with conditions, validators and post actions. Transitions are applied with a conditional update on the current status, and workflow edits create a new version with a migration job for existing issues.

How do you make search respect permissions?

Add the tenant, the projects the user can browse and their allowed security levels as filters inside the search query, using cached permission sets, instead of filtering results afterwards, which breaks pagination and leaks information.

What happens when two people edit the same issue at the same time?

Clients send patches of only the changed fields with the version they read. Changes to different fields are merged, a stale write to the same field returns a conflict, and live updates keep views fresh so conflicts are rare.

Why use a separate search index instead of SQL queries?

Users combine arbitrary standard and custom fields, full text and sort orders, which no fixed set of relational indexes can serve quickly. The index is fed from change events about a second behind and can be rebuilt from SQL.

How do you let users reorder cards on a board cheaply?

Give each issue a sortable rank string and assign a string between its new neighbours when it is dragged, so only one row changes. Rebalance a column in the background when ranks grow long.

How do you handle a tenant with 100,000 users?

Shard by tenant into cells and give the largest tenants a dedicated cell with their own database and search index, apply per-tenant rate limits, and run bulk operations as throttled background jobs.

How is the activity feed built?

Every change appends a row to an issue history table in the same transaction as the update, and the feed is a query over that table, newest first. Notifications are derived from the same change events and batched per user.