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.
- 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
- 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.
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.
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.
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.
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.
- 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
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.
- 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.
- 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.
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 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.
- 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.
- 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.
- 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