Jira looks like a CRUD app with extra menus. Create a ticket, assign it, drag it across a board, close it. Say that in an interview and the hard parts arrive in the first follow-up. “Every org defines its own fields - one team has a Story Points number, another has a Severity dropdown and a Customer picker - and users search across all of them with JQL like project = PAY AND status IN (Open, Reopened) AND assignee = currentUser() AND "Story Points" > 5 ORDER BY priority DESC.” “A ticket cannot jump from Open straight to Closed if the workflow says it must pass through In Review first, and the transition may run validators and fire a post-function.” “There are 100K organizations and 1B tickets total, most orgs tiny and a few enormous, and complex queries are the common case, not the exception.”

The system is defined by one tension: the data model is user-defined and different per org, but the query engine must run arbitrary structured predicates over those user-defined fields fast, across a billion tickets, without letting one tenant’s schema or load hurt another. Every hard part - the flexible custom-field storage, the JQL engine, the workflow state machine, the board and sprint aggregations, the multi-tenant isolation - falls out of that. Let me do it properly.

Functional Requirements (FR)

In scope:

  • Tickets (issues). Create, read, update a ticket with a fixed core (key like PAY-1423, project, type, summary, description, reporter, assignee, status, priority, created/updated timestamps) plus an arbitrary set of custom fields the org defines (number, text, single/multi select, date, user picker, etc.).
  • Workflows. Each project/issue-type has a configurable state machine: a set of statuses and allowed transitions between them. A transition can have conditions (who may run it), validators (input must be valid), and post-functions (side effects: set a field, assign, fire a webhook). A ticket can only move along defined edges.
  • Assignments. A ticket has one assignee; reassigning is a common, cheap write that must appear on the assignee’s queue and dashboards.
  • Comments and attachments. Threaded comments on a ticket and file attachments (screenshots, logs). Both feed an activity/history stream.
  • Boards and sprints. Kanban and Scrum boards that group tickets into columns (by status) and swimlanes, and sprints that scope a set of tickets to a time-box with a burndown.
  • JQL search. A structured query language over core and custom fields with boolean logic, ordering, and functions (currentUser(), startOfWeek()). This is the primary way people find work and the hard part.
  • History / audit. Every field change is recorded (who changed what, when) - non-negotiable for an issue tracker.

Explicitly out of scope (say this to control scope):

  • The full permission scheme bureaucracy (project roles, field-level security, permission schemes). I will assume a project-scoped RBAC check on every operation and not re-derive the model.
  • Real-time collaborative editing of a description, notification email/digest delivery, and the marketplace/plugin runtime. I will note where they attach.
  • Reporting/analytics warehouse (velocity charts beyond burndown, cross-project dashboards) - it reads from the same event stream into an OLAP store I am not designing.
  • File storage internals (blob store + CDN); attachments carry object keys to a separate service.

The one decision that drives everything: the schema is per-tenant and dynamic, and the workload is dominated by complex structured queries over that dynamic schema, so the design is about how to store user-defined fields and how to index and query them at scale - not about the ticket CRUD, which is trivial.

Non-Functional Requirements (NFR)

  • Scale: 100K orgs, 1B tickets total. Extremely skewed - most orgs have thousands of tickets, a handful have tens of millions. Assume ~10M DAU across all orgs. The workload is read-heavy but not extremely so (a knowledge worker reads and writes all day); call it ~20:1 read:write. Search/board queries are a large fraction of reads.
  • Latency: ticket load p99 < 200ms. Board render p99 < 500ms (it aggregates many tickets). JQL search p99 < 800ms for typical queries - it is doing real structured-query work and users tolerate a spinner, but it is still interactive. A write (edit a field, transition, comment) should ack < 300ms.
  • Availability: 99.9% (this is internal tooling for paying orgs, not a consumer megasite; a few minutes of downtime is bad but not catastrophic, and orgs are in business hours across time zones). The read path (boards, search) is the most visible.
  • Consistency: read-your-writes within an org is expected - if I transition a ticket and reload the board, it must reflect the change. So the primary ticket store is strongly consistent per ticket. Search indexes and board aggregations may lag by a second or two (near-real-time). Workflow transitions must be atomic and correct - a ticket must never be in an undefined status or skip a required edge.
  • Durability: a ticket, its history, comments, and attachment references must never be lost. History is an audit record. Search indexes and board caches are rebuildable derived state.
  • Isolation (the tenant NFR): one org’s giant reindex, expensive JQL, or bulk update must not degrade another org’s latency. Multi-tenant fairness is a first-class requirement here in a way it is not for a single-tenant consumer app.

Back-of-the-Envelope Estimation (BoE)

Real numbers, because they dictate the architecture.

Tickets and orgs:

100K orgs, 1B tickets total → 10K tickets/org average,
but power-law: median org ~2K tickets, largest orgs ~30M tickets.
This skew is the whole multi-tenancy problem - you cannot give
every org its own DB, and you cannot put a 30M-ticket org on the
same shard as 5,000 tiny ones without care.

Writes:

Assume each of 10M DAU makes ~20 ticket mutations/day
(edits, transitions, comments, assignments):
  10M * 20 = 200M writes/day
  200M / 86,400 ≈ 2,300 writes/sec average
  Peak (3x, business-hours-clustered) ≈ 7,000 writes/sec

Reads (ticket views + board renders + searches):

Assume each DAU triggers ~200 read operations/day (opening tickets,
refreshing boards, running searches, dashboards):
  10M * 200 = 2B reads/day
  2B / 86,400 ≈ 23,000 reads/sec average
  Peak ≈ 70,000 reads/sec
Of these, roughly 30% are board/search aggregations (the expensive kind):
  ~21,000 aggregate queries/sec at peak.

Storage:

Per ticket core row:

ticket_id      8 bytes  (Snowflake-style 64-bit)
org_id         8 bytes
project_id     8 bytes
key            ~12 bytes ("PAY-1423")
summary        ~120 bytes
description     ~2 KB (markdown)
core metadata  ~200 bytes (status, priority, assignee, reporter, ts)
------------------------------------------------
≈ 2.5 KB/ticket core

1B tickets * 2.5 KB ≈ 2.5 TB of ticket cores.
Custom-field values: assume ~15 custom fields/ticket * ~40 bytes
  = 600 bytes/ticket → 1B * 600 B ≈ 600 GB of custom-field values.
Comments: assume avg 4 comments/ticket * ~300 bytes = 1.2 KB
  → 1B * 1.2 KB ≈ 1.2 TB.
History: avg ~20 change events/ticket * ~80 bytes ≈ 1.6 KB
  → 1B * 1.6 KB ≈ 1.6 TB (append-only, ages to cold storage).
Attachments (blobs) live in object storage, not counted here;
  only the ~100-byte reference rows do. Assume ~2 attachments/ticket.
Total primary ≈ 6 TB, sharded across a cluster. Modest per shard.

Search index size:

Index each ticket as a document with core + custom fields + text.
1B docs. Structured fields dominate (custom fields are the query
target), plus a full-text field over summary+description+comments.
Assume ~400 bytes of indexed structured+text data per doc after
analysis → 1B * 400 B ≈ 400 GB, replicated. Sharded by org across
an Elasticsearch/OpenSearch cluster of tens of nodes.

Cache size (hot working set):

The hot set is "tickets and boards people are actively looking at."
Cache ~20M hot tickets * ~3 KB assembled ≈ 60 GB.
Board/sprint aggregations for ~500K active boards * ~50 KB ≈ 25 GB.
Comfortable in a Redis tier.

The takeaway: writes peak at a manageable ~7K/sec, but ~21K expensive aggregate/search queries per second over a user-defined schema is the load that dictates the design. The custom-field storage and the JQL engine are the whole game.

High-Level Design (HLD)

Three concerns dominate: the ticket store (per-ticket source of truth with a flexible field model and strong per-ticket consistency), the search/query layer (JQL over core + custom fields, plus board aggregations), and the workflow engine (a correct, atomic state machine per transition). Writes go to the ticket store, which emits change events; those events asynchronously update the search index, board caches, and notification stream.

                         ┌───────────────────────────┐
                         │          Clients          │
                         │   (web, mobile, REST API) │
                         └─────────────┬─────────────┘
                                       │
                         ┌─────────────▼─────────────┐
                         │   API Gateway / LB         │
                         │  (authN, tenant routing,   │
                         │   per-org rate limiting)   │
                         └──┬───────┬────────┬────────┘
          WRITE / TRANSITION│  READ │  JQL / │ BOARD
        ┌────────────────▼──┐ ┌─────▼───┐ ┌──▼────────────┐
        │ Ticket Service    │ │ Ticket  │ │ Search / JQL  │
        │ + Workflow Engine │ │ Read    │ │ Service       │
        │ (validate, trans, │ │ Service │ │ (parse→plan→  │
        │  post-functions)  │ └────┬────┘ │  execute)     │
        └───┬───────────────┘      │      └──┬────────────┘
            │ write (strong)       │ hydrate │ query
     ┌──────▼────────────────┐     │    ┌────▼──────────┐
     │ Ticket Store           │◀────┘    │ Search Cluster │
     │ (cores + custom-field  │          │ (per-org index,│
     │  values, sharded by    │          │  ES/OpenSearch,│
     │  org_id, RDBMS shards) │          │  structured +  │
     └───┬────────────────────┘          │  full text)    │
         │ emit ticket_changed (outbox)  └────▲───────────┘
         │                                     │ index
    ┌────▼─────────────────────┐  ┌────────────┴─────────┐
    │        Event Bus          │  │   Indexer Pipeline    │
    │  (Kafka, partitioned      │─▶│  (consume changes,    │
    │   by org_id)              │  │   upsert docs, NRT)   │
    └──┬─────────┬─────────┬────┘  └──────────────────────┘
       │         │         │
 ┌─────▼───┐ ┌───▼──────┐ ┌▼───────────┐   ┌──────────────┐
 │ Board    │ │ Activity │ │ Notify /   │   │ Object Store │
 │ Cache    │ │ / History│ │ Webhook    │   │ (attachments │
 │ Builder  │ │ Writer   │ │ Dispatcher │   │  blobs, CDN) │
 └────┬─────┘ └────┬─────┘ └────────────┘   └──────▲───────┘
      │            │                                │
 ┌────▼─────┐ ┌────▼────────┐              upload   │
 │ Board /   │ │ History Log │           ┌──────────┴───┐
 │ Sprint    │ │ (append-only│           │ Attachment    │
 │ Cache     │ │  audit)     │           │ Service       │
 └───────────┘ └─────────────┘           └───────────────┘

Write / transition flow:

  1. Client POST /issues or PUT /issues/{key} or POST /issues/{key}/transitions. The API Gateway authenticates, resolves org_id, applies per-org rate limits, and routes to the Ticket Service.
  2. The Ticket Service loads the ticket (or allocates a new key), and for a transition invokes the Workflow Engine: check the transition is a valid edge from the current status, run its conditions (RBAC), validators (required fields present), then, inside a single DB transaction on the ticket’s shard, apply the status change and any field mutations. Post-functions that are side effects (assign, set resolution) run in the same transaction; external side effects (webhooks, email) are emitted as events, not run inline.
  3. The change is written to the Ticket Store and, in the same transaction, a ticket_changed event plus a history record are written to an outbox table. A relay publishes the event to Kafka partitioned by org_id.
  4. Consumers fan out: the Indexer upserts the search document, the Board Cache Builder invalidates/updates affected boards, the History Writer persists the audit record, the Notify/Webhook Dispatcher fires notifications and configured webhooks.

Read flow:

  1. GET /issues/{key} → Ticket Read Service. Hydrate the core row plus its custom-field values (see deep dive 1), overlay from cache. Strongly consistent read of the ticket’s shard for read-your-writes.
  2. GET /search?jql=... → Search/JQL Service: parse the JQL, plan it, execute against the per-org search index, return ranked/ordered ticket ids, hydrate summaries.
  3. GET /boards/{id} → served from the Board/Sprint Cache, an aggregation maintained off the event stream (deep dive 4).

The key split: writes are strongly consistent per ticket and emit events; the expensive things - search over custom fields, board aggregations - are derived, near-real-time views built off those events.

Component Deep Dive

The hard parts, naive-first then evolved: (1) storing user-defined custom fields, (2) the JQL query engine, (3) the workflow state machine, (4) boards and sprint aggregation. Multi-tenancy threads through all four.

1. Storing user-defined custom fields

Every org defines its own fields. Org A has Story Points (number) and Sprint (picker); Org B has Severity (select), Customer (text), SLA Due (date). The store must hold arbitrary typed fields per org and let JQL filter and sort on them.

Approach A: one wide table with a column per field (the naive schema)

Add a real column for every custom field: ALTER TABLE issues ADD COLUMN story_points INT.

Where it breaks:

  • Schema explosion. 100K orgs, each with dozens of custom fields, means the union is tens of thousands of columns - impossible in any relational DB (Postgres caps at ~1,600 columns). You cannot have one physical table whose columns are the union of every tenant’s fields.
  • Every field creation is a DDL migration on a giant shared table - an ALTER TABLE that locks or rewrites a billion-row table. Orgs create fields casually; this makes a routine config change an outage.
  • Fields are sparse: any given ticket populates 15 of the 30,000 possible columns, so the table is almost entirely NULLs.

Approach B: Entity-Attribute-Value (EAV) rows

Store each field value as a row: (ticket_id, field_id, value).

Table: custom_field_values
  ticket_id  BIGINT
  field_id   BIGINT     -- references a per-org field definition
  value_str  TEXT NULL
  value_num  NUMERIC NULL
  value_date TIMESTAMP NULL
  PRIMARY KEY (ticket_id, field_id)

Where it improves and where it breaks: field creation is now just inserting a row into a field_definitions catalog - no DDL, no migration. Sparse data is natural. But querying is painful: WHERE story_points > 5 AND severity = 'High' requires one self-join of custom_field_values per predicate, and JQL routinely has 5-10 predicates. Ten self-joins over a multi-billion-row EAV table per query, at 21K queries/sec, is a non-starter in the transactional DB. EAV is a fine storage model but a terrible query model.

Approach C: EAV/JSONB for storage, a dedicated search index for querying (the answer)

Split the two concerns. Store custom fields flexibly next to the ticket; query them in a purpose-built index.

Storage: keep custom-field values in a semi-structured column on the ticket row (Postgres JSONB, or an EAV side table). A JSONB blob keyed by field_id is the pragmatic choice - it travels with the ticket, hydrates in one read, and needs no join for a plain ticket load:

Table: issues  (Ticket Store, sharded by org_id)
  ticket_id   BIGINT   PRIMARY KEY
  org_id      BIGINT
  project_id  BIGINT
  key         TEXT                 -- "PAY-1423"
  type_id     BIGINT
  status_id   BIGINT               -- current workflow state
  assignee_id BIGINT
  reporter_id BIGINT
  priority    SMALLINT
  summary     TEXT
  description TEXT
  custom      JSONB                -- {"10001": 8, "10023": "High", ...}
  created_at  TIMESTAMP
  updated_at  TIMESTAMP

Field metadata lives in a per-org catalog so the system knows each field’s type, name, and options:

Table: field_definitions
  field_id    BIGINT   PRIMARY KEY   -- "10001"
  org_id      BIGINT
  name        TEXT                   -- "Story Points"
  type        ENUM(number, text, select, multiselect, date, user, ...)
  options     JSONB NULL             -- for select fields
  project_ids BIGINT[]               -- where the field applies

Query: the ticket, with its custom map flattened into typed fields, is indexed as a document in a search cluster. Custom fields become indexed fields (custom.10001 as a numeric field, custom.10023 as a keyword), so story_points > 5 AND severity = 'High' is an indexed structured query - milliseconds - not ten self-joins. This is deep dive 2.

ApproachField creationTicket loadComplex filterVerdict
Column-per-fieldDDL migration, locksone row readindexed but impossible schemaBreaks at column limit
EAV rowsinsert a catalog rowN-row gatherN self-joins per queryStorage ok, query dies
JSONB + search indexinsert a catalog rowone row readindexed query in the clusterThe answer

Mapping the dynamic schema into the index. Since fields are typed in the catalog, the Indexer maps each field_id to a typed index field using its declared type. A select becomes a keyword, a number becomes a double, a date becomes a date. Two orgs both having a field named Severity do not collide because indexing is per-org (deep dive on multi-tenancy) and keyed by field_id, not by name. This keeps mapping explosion bounded per index.

2. The JQL query engine

JQL is a real structured query language: boolean logic, IN/NOT IN, ordering, and functions over core and custom fields. project = PAY AND status IN (Open, Reopened) AND assignee = currentUser() AND "Story Points" > 5 ORDER BY priority DESC. It is the primary discovery path and the distinctive hard part.

Approach A: translate JQL to SQL against the transactional store (the naive engine)

Parse JQL, generate a SELECT ... WHERE ... with joins into custom_field_values, run it on the Ticket Store.

Where it breaks:

  • Every custom-field predicate is a join (Approach B above). A 7-predicate JQL becomes a 7-way self-join over billions of rows.
  • Full-text predicates (text ~ "null pointer") cannot be served by a relational index at all - it is a LIKE '%...%' full scan.
  • These heavy analytical queries run on the same primary that serves strongly-consistent ticket writes, so a few expensive JQLs starve the write path. Mixing OLTP writes and ad-hoc analytical scans on one store is the classic mistake.

Approach B: a dedicated search index with a JQL-to-query compiler (the answer)

Serve JQL from the search cluster, not the transactional DB. The pipeline is a small query compiler:

JQL string
   │  1. Lexer + parser  → Abstract Syntax Tree
   ▼
  AST:  AND( project=PAY,
             status IN (Open,Reopened),
             assignee = FN:currentUser,
             custom.10001 > 5 )
   │  2. Semantic pass: resolve field names → field_ids via the
   │     per-org catalog, resolve functions (currentUser → user_id),
   │     type-check literals against field types, RBAC-scope
   │     (rewrite to only projects the user can see).
   ▼
   │  3. Compile AST → search-engine query DSL:
   ▼
  { bool: { filter: [
      { term:  { project_id: 88 } },
      { terms: { status_id: [1, 4] } },
      { term:  { assignee_id: 42 } },
      { range: { "custom.10001": { gt: 5 } } }
  ] }, sort: [ { priority: "desc" } ] }
   │  4. Execute against the per-org index (scatter-gather over
   │     that org's shards), page with search_after cursors.
   ▼
  ticket_ids → hydrate summaries from Ticket Store / cache

Why this works:

  • Structured predicates are indexed lookups. term, terms, and range queries over keyword/numeric fields are posting-list intersections - milliseconds even over a large org’s millions of tickets. No joins.
  • Full-text predicates use the inverted index. text ~ "null pointer" is a real tokenized/stemmed full-text query over the analyzed summary+description+comments field, with relevance scoring - not a LIKE scan.
  • Ordering and cursors. ORDER BY priority DESC is a sort on a doc-value field; deep paging uses search_after (cursor on the last sort tuple), not offset paging, which degrades at depth.
  • RBAC is compiled in. The semantic pass rewrites the query to restrict to the projects the caller can read, so security is a filter clause, not a post-filter that leaks counts.

Correctness vs freshness. The index lags the write by a second or two (near-real-time). For search that is fine - JQL is a discovery tool, not a read-your-writes surface. The exception is a user editing a ticket and expecting the ticket page to reflect it; that read comes from the strongly-consistent Ticket Store, not from search. We route read-your-writes to the source of truth and analytical breadth to the index.

Guardrails against expensive queries. JQL is user-authored and can be pathological (unbounded ORDER BY over a 30M-ticket org, wildcard leading terms). The compiler enforces limits: a max clause count, a required org/project scope, a per-query timeout, and a per-org concurrency budget so one tenant’s runaway query cannot exhaust the cluster (deep dive on multi-tenancy). Saved filters that back dashboards are executed on a schedule and cached, not re-run per viewer.

3. The workflow state machine

A ticket’s status is governed by a per-project, per-issue-type workflow: a directed graph of statuses and the transitions between them. Open → In Progress → In Review → Done, with maybe In Review → Reopened → In Progress. A transition can carry conditions, validators, and post-functions. The requirement: a ticket can only ever be in a defined status and can only move along a defined edge, atomically.

Approach A: a status column and unconstrained updates (the naive engine)

UPDATE issues SET status = 'Done' WHERE key = 'PAY-1423'.

Where it breaks:

  • Illegal transitions. Nothing stops Open → Done when the workflow requires passing through In Review. The status field becomes meaningless because any value can be written from any value.
  • Races. Two users transition the same ticket concurrently (→ In Progress and → Closed); last-write-wins leaves the ticket in a state that neither transition’s validators approved, and post-functions may both fire.
  • Side effects diverge. A post-function fires a webhook, but the DB write is later rolled back - now the external world thinks the ticket moved and the DB disagrees.

Approach B: an explicit state machine with a guarded, transactional transition (the answer)

Model the workflow as data, and make every transition a guarded, atomic operation.

Table: workflows
  workflow_id  BIGINT PRIMARY KEY
  org_id       BIGINT
  scheme       JSONB   -- statuses + transitions definition

  scheme example:
  {
    "statuses": [1:"Open", 2:"In Progress", 3:"In Review", 4:"Done"],
    "transitions": [
      { "id":11, "name":"Start",   "from":[1],   "to":2,
        "conditions":["role:developer"], "validators":[], "post":[] },
      { "id":12, "name":"Review",  "from":[2],   "to":3,
        "conditions":["assignee==currentUser"],
        "validators":["field:reviewer required"], "post":["set:reviewed_at"] },
      { "id":13, "name":"Done",    "from":[3],   "to":4,
        "conditions":["role:reviewer"], "validators":[],
        "post":["set:resolution=Fixed","fire:webhook"] }
    ]
  }

The transition procedure:

Transition(ticket, transition_id, actor, inputs):
  BEGIN TRANSACTION on ticket's shard
    t = SELECT * FROM issues WHERE ticket_id = ? FOR UPDATE   -- row lock
    tr = lookup transition_id in the ticket's workflow scheme
    if t.status_id NOT IN tr.from:  ABORT 409  -- illegal edge
    for c in tr.conditions:  if not c(actor, t):  ABORT 403
    for v in tr.validators:  if not v(inputs, t): ABORT 400
    t.status_id = tr.to
    apply internal post-functions (set fields, assign) to t
    UPDATE issues SET ... WHERE ticket_id = ?
    INSERT history row (from, to, actor, ts)
    INSERT outbox row: ticket_changed + external post-functions
                       (webhooks, notifications) to fire AFTER commit
  COMMIT
  relay publishes outbox events → webhook/notify dispatcher

Why this is correct:

  • Only legal edges. The t.status_id NOT IN tr.from guard, evaluated against the workflow definition, structurally prevents illegal transitions. Status is never an arbitrary value.
  • No races. SELECT ... FOR UPDATE serializes concurrent transitions on the same ticket. The second transition re-reads the now-updated status_id and either follows a valid edge from the new state or is rejected. Exactly one transition’s post-functions fire per state change.
  • Side effects cannot diverge. Internal effects (field sets, assignment) are in the same transaction as the status change. External effects (webhooks, email) are written to the outbox in that transaction and only published after commit, so we never fire a webhook for a transition that rolled back. The dispatcher is idempotent (keyed by transition event id) so an at-least-once relay is safe.

The workflow scheme itself is versioned; changing a workflow does not rewrite existing tickets, and a ticket carries the workflow version it is operating under so an in-flight ticket is not stranded on a deleted edge.

4. Boards and sprint aggregation

A board shows tickets grouped into columns (by status) and swimlanes (by assignee/epic), scoped to a project or a JQL filter and, for Scrum, an active sprint. Rendering a board is an aggregation over potentially thousands of tickets, and boards are refreshed constantly.

Approach A: query all matching tickets and group on every board load (the naive view)

On each board GET, run the board’s JQL, fetch every matching ticket, group by status and swimlane in the app.

Where it breaks: a busy board matches thousands of tickets; grouping them per load, at thousands of board loads/sec, is expensive, and a board is polled/refreshed far more often than its tickets change. You recompute the same grouping repeatedly. A large org’s board (tens of thousands of in-sprint tickets) blows the 500ms budget.

Approach B: a maintained board projection updated off the event stream (the answer)

Treat a board as a materialized projection keyed by board_id, maintained incrementally.

  • The board is defined by its JQL filter and, for Scrum, a sprint id. The Board Cache Builder consumes ticket_changed events; for each event it checks which boards the ticket belongs to (by project/sprint membership) and updates those boards’ column/swimlane buckets - move a ticket from the In Progress bucket to In Review, adjust the sum of story points in each column.
Board projection (Redis, per board):
  board:{id}:column:{status_id}  → sorted set of ticket_ids (by rank)
  board:{id}:swimlane:{key}      → set membership
  board:{id}:stats               → {points_todo, points_inprogress, points_done}
  sprint:{id}:burndown           → time-series of remaining points
  • A board render is then reading a handful of small precomputed sets, not re-running a JQL over thousands of tickets. p99 well under 500ms.
  • Sprint burndown is a time series appended to as tickets close: each ticket_changed that moves a ticket to/from Done within the sprint emits a point (timestamp, remaining_points). The burndown chart reads the series directly.
  • Membership changes (a ticket added to a sprint, or its status filter no longer matches) are handled by the same consumer re-evaluating the ticket against the board’s filter on each change and adding/removing it from buckets.

For orgs with enormous boards, the projection is bounded (a board with 30M matching tickets is a misconfiguration; boards are scoped to a sprint or a recent window). Ranking within a column (drag-to-reorder) is stored as a LexoRank-style fractional rank string per ticket so reordering one card is a single field write, not a renumber of the column.

Board projections are rebuildable derived state: lose the Redis tier and the Board Cache Builder replays recent events or re-runs each board’s JQL once to repopulate.

Multi-tenancy (threads through all four)

100K orgs on shared infrastructure with a first-class isolation requirement:

  • Sharding by org_id. The Ticket Store is sharded by org_id, so an org’s tickets, custom fields, history, and comments co-locate on one shard and every JQL/board query for that org is single-org (no cross-shard scatter). Huge orgs get a dedicated shard; thousands of tiny orgs pack onto shared shards. A directory service maps org_id → shard, so a hot org can be migrated to its own shard without a data-model change.
  • Per-org indexes / routing. The search cluster routes each org to a shard by org_id, so a query touches only that org’s data and one org’s giant reindex does not scan another’s docs. Very large orgs get dedicated index shards.
  • Fairness. Per-org rate limits at the gateway, per-org query concurrency budgets and timeouts in the JQL service, and bulk operations (bulk edit, CSV import, reindex) run on a throttled background queue with per-org quotas. This is the mechanism that stops a noisy tenant from degrading everyone - the explicit isolation NFR.

API Design & Data Schema

API

POST /api/v3/issues
Body:
{ "project": "PAY", "type": "Bug", "summary": "NPE on checkout",
  "description": "...markdown...", "assignee": "u_42",
  "fields": { "10001": 5, "10023": "High" } }   // custom fields by id
Response 201:
{ "key": "PAY-1423", "id": "1789...", "status": "Open", "created": "..." }
Errors: 400 invalid field/type, 401, 403 no create perm, 429 rate limited

PUT /api/v3/issues/{key}
Body: { "summary": "...", "fields": { "10001": 8 } }    // partial update
Response 200: { "key": "PAY-1423", "updated": "..." }

POST /api/v3/issues/{key}/transitions
Body: { "transition": 12, "fields": { "reviewer": "u_77" } }
Response 200: { "key": "PAY-1423", "status": "In Review" }
Errors: 400 validator failed, 403 condition failed, 409 illegal transition

POST /api/v3/issues/{key}/comments   Body: { "body": "...markdown..." }
POST /api/v3/issues/{key}/attachments  → returns a pre-signed upload URL
GET  /api/v3/issues/{key}            → core + custom + comments + history

GET /api/v3/search?jql=<url-encoded>&startAt=0&maxResults=50&nextPageToken=...
   jql: project = PAY AND status IN (Open,Reopened)
        AND "Story Points" > 5 ORDER BY priority DESC
Response 200:
{ "issues": [ { "key": "PAY-1423", "summary": "...", "status": "Open",
                "assignee": {...}, "fields": {...} }, ... ],
  "total": 3391, "nextPageToken": "..." }        // search_after cursor
Latency target: p99 < 800ms

GET /api/v3/boards/{id}          → columns, swimlanes, cards (from projection)
GET /api/v3/sprints/{id}/burndown
POST /api/v3/fields              → create a custom field (catalog insert)

Search paging is cursor-based (nextPageToken encoding the last sort tuple) so deep pages are stable and cheap; ticket lists use it too. Writes are idempotent via a client-supplied Idempotency-Key header so a retried create does not make two tickets.

Data store choices

Different access patterns, so more than one store. Be explicit.

1. Ticket Store - relational, sharded by org_id (Postgres / a NewSQL like CockroachDB/Spanner). Tickets need per-ticket strong consistency (read-your-writes, transactional transitions with FOR UPDATE), an audit history, and a flexible custom JSONB column. Relational gives the transaction and row-lock semantics the workflow engine depends on; sharding by org_id gives horizontal scale and single-org locality. NewSQL is attractive because it gives transactions across shards without hand-rolled sharding, at the cost of latency.

issues        (above)          -- sharded by org_id, PK ticket_id
field_definitions              -- per-org custom field catalog
workflows                      -- per-org/project workflow schemes
comments   (ticket_id, comment_id, author, body, created_at)
                               -- clustered by ticket_id
attachments (ticket_id, att_id, object_key, filename, size, uploaded_by)
history    (ticket_id, seq, field, from_val, to_val, actor, ts)
                               -- append-only, clustered by ticket_id
outbox     (event_id, org_id, payload, published_at NULL)
                               -- transactional outbox for events

2. Search cluster - Elasticsearch / OpenSearch. The JQL engine and full-text search. Each ticket is a document with core fields, flattened custom fields (typed from the catalog), and an analyzed text field over summary+description+comments. Routed by org_id; large orgs get dedicated shards. Derived, rebuildable by reindexing the Ticket Store.

3. Board / sprint cache - Redis. Materialized board projections (per-column sorted sets, swimlane membership, stats) and sprint burndown series (deep dive 4). Rebuildable derived state.

4. Ticket / hot-read cache - Redis. Assembled hot tickets (core + custom + comment count) for the ticket-view path. Invalidated by ticket_changed.

5. Attachment blobs - object storage (S3/GCS) + CDN. Files themselves; the Ticket Store holds only reference rows with the object key. Uploads use pre-signed URLs so bytes never transit the app tier.

6. Event bus - Kafka, partitioned by org_id. Carries ticket_changed for indexing, board updates, history, and notifications. Partitioning by org_id preserves per-org ordering (a ticket’s events are processed in order).

Why relational (sharded) for tickets, a search cluster for JQL, Redis for boards: the transactional, consistency-sensitive, per-org-scoped ticket lifecycle wants a relational store with row locks and transactions; arbitrary structured + full-text queries over a dynamic schema want an inverted-index engine; hot aggregations want a maintained in-memory projection. Sharding everything by org_id keeps each org’s hot path single-tenant and is the foundation of the isolation NFR. Sharding by ticket_id instead would scatter an org’s board and JQL across every shard - the classic mistake.

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each:

1. Querying user-defined fields (the primary hard part). Column-per-field is impossible; EAV needs N joins per query; both die on the transactional store. Fix: store custom fields as JSONB/EAV next to the ticket, but serve all JQL and full-text from a dedicated search index where each custom field is a typed indexed field. Structured predicates become indexed lookups, not joins. Covered in deep dives 1-2.

2. Expensive JQL starving the write path. Analytical scans on the OLTP store block strongly-consistent writes. Fix: physically separate them - writes and read-your-writes hit the Ticket Store; all analytical breadth hits the search cluster (a read-optimized replica of the same data via the event stream). Never run ad-hoc JQL on the transactional primary.

3. Illegal or racing workflow transitions. Arbitrary status writes and last-write-wins. Fix: an explicit state machine, transitions guarded by the workflow definition, applied under SELECT ... FOR UPDATE in one transaction, with external side effects via a transactional outbox. Covered in deep dive 3.

4. Board render cost at thousands of loads/sec. Re-running a board’s JQL and grouping per load. Fix: maintain board projections (per-column sorted sets, stats, burndown) incrementally off the event stream; a render reads small precomputed sets. Covered in deep dive 4.

5. The noisy-tenant problem (the isolation NFR). One org’s bulk import, giant reindex, or runaway JQL degrades everyone. Fix: shard by org_id so load is naturally partitioned; per-org rate limits, query concurrency budgets, and timeouts; bulk/reindex jobs on a throttled per-org background queue; dedicated shards/index for the largest orgs. This is first-class here, not an afterthought.

6. Skewed org sizes / hot shards. A 30M-ticket org on a shared shard swamps its neighbors. Fix: a org_id → shard directory allows moving a hot org to a dedicated shard without a schema change; pack small orgs densely, isolate large ones. The same for the search cluster (dedicated index shards for whales).

7. Index freshness vs write cost. Reindexing on every field edit thrashes the cluster. Fix: near-real-time indexing off the event stream with small batched partial updates (change one field → update one doc field, no full reanalysis unless text changed). Search lags writes by ~1-2s, which is acceptable for discovery; read-your-writes uses the source store.

8. Attachment upload load on the app tier. Streaming file bytes through application servers. Fix: pre-signed direct-to-object-store uploads; the app tier only issues the URL and records the reference. Bytes go client → object store → CDN, never through the ticket path.

9. Comment/history write amplification on a hot ticket. A heavily-discussed incident ticket takes many concurrent comments. Fix: comments and history are append-only rows clustered by ticket_id, so concurrent appends do not contend on a shared counter; the ticket’s own updated_at bump is the only contended field and is a cheap single-row update. The activity stream is built asynchronously off events.

10. Single points of failure and multi-region. Services must not have a single stateful choke point; global orgs want low latency. Fix: Ticket/Read/Search/Board services are stateless behind load balancers, scaled horizontally. Ticket Store shards, Kafka, Redis, and the search cluster run RF≥3. For geography, each org is homed in a region (data residency is also a compliance requirement for issue trackers); the ticket store, index, and caches for that org live in its home region, so there is no cross-region hot path. Cross-region is for DR replication, not live serving.

Wrap-Up

The trade-offs that define this design:

  • The schema is user-defined, so storage and query are split. Custom fields are stored flexibly (JSONB/EAV) next to the ticket for cheap single-read hydration and transactional writes, but all filtering happens in a dedicated search index where each custom field is a typed indexed field. Neither store alone works: the relational store cannot query dynamic fields cheaply, and the index is not the source of truth.
  • JQL is a compiler, not a database query. Parse to an AST, resolve field names and functions against the per-org catalog, compile RBAC scope in as a filter, and execute as indexed structured + full-text queries with cursor paging. Running it as generated SQL against the OLTP store cannot scale and starves writes.
  • The workflow is an explicit, guarded state machine. Transitions are legal edges only, serialized per ticket by a row lock, with internal effects in-transaction and external effects via a transactional outbox - so a ticket is never in an undefined status and a webhook never fires for a rolled-back move.
  • Boards are maintained projections, not per-load aggregations. Incrementally updated off the event stream and read as small precomputed sets, with fractional ranks for cheap reordering and an event-appended burndown.
  • Multi-tenancy is the spine. Everything shards by org_id, keeping each org’s hot path single-tenant, and per-org rate limits, concurrency budgets, throttled bulk jobs, and dedicated shards for whales enforce the isolation that keeps 100K orgs from hurting each other.

One-line summary: a per-tenant, strongly-consistent ticket store (sharded by org_id, custom fields in JSONB, workflow transitions guarded under a row lock with an outbox) that emits change events into a dedicated search index serving compiled JQL over user-defined fields and into maintained board/sprint projections - isolated per org so complex queries over a billion tickets across 100K organizations stay fast and fair.