Design an Issue Tracker (Jira/Linear)

1
mutation MoveIssue($input: MoveIssueInput!) {
2
  moveIssue(input: $input) {
3
    issue {
4
      id
5
      status {
6
        id
7
        name
8
      }
9
      rank
10
      updatedAt
11
    }
12
  }
13
}
14

15
subscription OnBoardUpdate($boardId: ID!) {
16
  boardUpdated(boardId: $boardId) {
17
    issue {
18
      id
19
      status {
20
        id
21
      }
22
      rank
23
    }
24
    action
25
  }
26
}

1
type Issue {
2
  id: ID!
3
  key: String! # e.g., "PROJ-123"
4
  title: String!
5
  description: String
6
  status: Status!
7
  assignee: User
8
  reporter: User!
9
  priority: Priority!
10
  issueType: IssueType!
11
  rank: String! # LexoRank for ordering
12
  version: Int! # Optimistic locking
13
  project: Project!
14
  comments(first: Int, after: String): CommentConnection!
15
  activity(first: Int, after: String): ActivityConnection!
16
  createdAt: DateTime!
17
  updatedAt: DateTime!
18
}
19

20
type Status {
21
  id: ID!
22
  name: String!
23
  category: StatusCategory! # TODO, IN_PROGRESS, DONE
24
  color: String!
25
  position: Int! # Column order
26
}
27

28
type Project {
29
  id: ID!
30
  key: String!
31
  name: String!
32
  workflow: Workflow!
33
  statuses: [Status!]!
34
  issueTypes: [IssueType!]!
35
}
36

37
type Workflow {
38
  id: ID!
39
  name: String!
40
  statuses: [Status!]!
41
  transitions: [Transition!]!
42
}
43

44
type Transition {
45
  id: ID!
46
  name: String!
47
  fromStatus: Status
48
  toStatus: Status!
49
  conditions: [TransitionCondition!]
50
}
51

52
enum StatusCategory {
53
  TODO
54
  IN_PROGRESS
55
  DONE
56
}
57

58
enum Priority {
59
  LOWEST
60
  LOW
61
  MEDIUM
62
  HIGH
63
  HIGHEST
64
}

1
# BAD: Fetches all issues, client groups by status
2
query {
3
  issues(projectId: "proj-1", first: 100) {
4
    nodes {
5
      id
6
      status {
7
        id
8
      }
9
    }
10
  }
11
}
12
# Problem: If 90 issues are in "To Do", other columns appear empty

1
type BoardColumn {
2
  status: Status!
3
  issues(first: Int!, after: String): IssueConnection!
4
  totalCount: Int!
5
}
6

7
type Board {
8
  id: ID!
9
  project: Project!
10
  columns: [BoardColumn!]!
11
}
12

13
query GetBoard($projectId: ID!, $issuesPerColumn: Int!) {
14
  board(projectId: $projectId) {
15
    columns {
16
      status {
17
        id
18
        name
19
        color
20
      }
21
      totalCount
22
      issues(first: $issuesPerColumn) {
23
        nodes {
24
          id
25
          key
26
          title
27
          assignee {
28
            id
29
            name
30
            avatar
31
          }
32
          priority
33
          rank
34
        }
35
        pageInfo {
36
          hasNextPage
37
          endCursor
38
        }
39
      }
40
    }
41
  }
42
}

1
{
2
  "data": {
3
    "board": {
4
      "columns": [
5
        {
6
          "status": { "id": "status-1", "name": "To Do", "color": "#grey" },
7
          "totalCount": 45,
8
          "issues": {
9
            "nodes": [
10
              /* first 20 issues */
11
            ],
12
            "pageInfo": { "hasNextPage": true, "endCursor": "cursor-abc" }
13
          }
14
        },
15
        {
16
          "status": { "id": "status-2", "name": "In Progress", "color": "#blue" },
17
          "totalCount": 12,
18
          "issues": {
19
            "nodes": [
20
              /* first 12 issues - no more pages */
21
            ],
22
            "pageInfo": { "hasNextPage": false, "endCursor": "cursor-xyz" }
23
          }
24
        },
25
        {
26
          "status": { "id": "status-3", "name": "Done", "color": "#green" },
27
          "totalCount": 89,
28
          "issues": {
29
            "nodes": [
30
              /* first 20 issues */
31
            ],
32
            "pageInfo": { "hasNextPage": true, "endCursor": "cursor-def" }
33
          }
34
        }
35
      ]
36
    }
37
  }
38
}

1
query LoadMoreIssues($statusId: ID!, $after: String!) {
2
  column(statusId: $statusId) {
3
    issues(first: 20, after: $after) {
4
      nodes {
5
        id
6
        key
7
        title
8
        rank
9
      }
10
      pageInfo {
11
        hasNextPage
12
        endCursor
13
      }
14
    }
15
  }
16
}

1
input MoveIssueInput {
2
  issueId: ID!
3
  toStatusId: ID!
4
  rankAfterId: ID # Issue to position after (null = top)
5
  rankBeforeId: ID # Issue to position before (null = bottom)
6
  version: Int! # For optimistic locking
7
}
8

9
type MoveIssuePayload {
10
  issue: Issue
11
  error: MoveIssueError
12
}
13

14
type MoveIssueError {
15
  code: MoveIssueErrorCode!
16
  message: String!
17
}
18

19
enum MoveIssueErrorCode {
20
  ISSUE_NOT_FOUND
21
  INVALID_TRANSITION
22
  VERSION_CONFLICT
23
  PERMISSION_DENIED
24
}
25

26
mutation MoveIssue($input: MoveIssueInput!) {
27
  moveIssue(input: $input) {
28
    issue {
29
      id
30
      status {
31
        id
32
        name
33
      }
34
      rank
35
      version
36
      updatedAt
37
    }
38
    error {
39
      code
40
      message
41
    }
42
  }
43
}

1
input UpdateIssueInput {
2
  issueId: ID!
3
  title: String
4
  description: String
5
  assigneeId: ID
6
  priority: Priority
7
  version: Int!
8
}
9

10
mutation UpdateIssue($input: UpdateIssueInput!) {
11
  updateIssue(input: $input) {
12
    issue {
13
      id
14
      title
15
      description
16
      assignee {
17
        id
18
        name
19
      }
20
      priority
21
      version
22
      updatedAt
23
    }
24
    error {
25
      code
26
      message
27
    }
28
  }
29
}

1
type BoardEvent {
2
  issue: Issue!
3
  action: BoardAction!
4
  previousStatusId: ID # For status changes
5
  previousRank: String # For reorders
6
}
7

8
enum BoardAction {
9
  CREATED
10
  UPDATED
11
  MOVED
12
  DELETED
13
}
14

15
subscription OnBoardChange($projectId: ID!) {
16
  boardChanged(projectId: $projectId) {
17
    issue {
18
      id
19
      key
20
      title
21
      status {
22
        id
23
      }
24
      rank
25
      assignee {
26
        id
27
        name
28
      }
29
      version
30
    }
31
    action
32
    previousStatusId
33
  }
34
}

1
PATCH /api/v1/issues/{issueId}/move
2
Content-Type: application/json
3
If-Match: "version-5"
4

5
{
6
  "statusId": "status-3",
7
  "rankAfterId": "issue-456",
8
  "rankBeforeId": null
9
}

1
HTTP/1.1 200 OK
2
ETag: "version-6"
3

4
{
5
  "id": "issue-123",
6
  "key": "PROJ-123",
7
  "status": { "id": "status-3", "name": "Done" },
8
  "rank": "0|i002bc",
9
  "version": 6,
10
  "updatedAt": "2024-02-03T10:00:00Z"
11
}

1
-- Projects with embedded workflow reference
2
CREATE TABLE projects (
3
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4
    key VARCHAR(10) UNIQUE NOT NULL,      -- e.g., "PROJ"
5
    name VARCHAR(255) NOT NULL,
6
    description TEXT,
7
    owner_id UUID NOT NULL REFERENCES users(id),
8
    created_at TIMESTAMPTZ DEFAULT NOW(),
9
    updated_at TIMESTAMPTZ DEFAULT NOW()
10
);
11

12
-- Statuses are project-scoped
13
CREATE TABLE statuses (
14
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
15
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
16
    name VARCHAR(100) NOT NULL,
17
    category VARCHAR(20) NOT NULL,        -- 'todo', 'in_progress', 'done'
18
    color VARCHAR(7) DEFAULT '#808080',
19
    position INT NOT NULL,                -- Column order
20
    is_initial BOOLEAN DEFAULT FALSE,     -- Default for new issues
21
    UNIQUE (project_id, name)
22
);
23

24
CREATE INDEX idx_statuses_project ON statuses(project_id, position);
25

26
-- Workflow transitions define allowed status changes
27
CREATE TABLE workflow_transitions (
28
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
29
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
30
    from_status_id UUID REFERENCES statuses(id) ON DELETE CASCADE,  -- NULL = any
31
    to_status_id UUID NOT NULL REFERENCES statuses(id) ON DELETE CASCADE,
32
    name VARCHAR(100) NOT NULL,
33
    opsbar_sequence INT DEFAULT 10,       -- UI ordering
34
    UNIQUE (project_id, from_status_id, to_status_id)
35
);
36

37
-- Issue types (Epic, Story, Task, Bug)
38
CREATE TABLE issue_types (
39
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
40
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
41
    name VARCHAR(50) NOT NULL,
42
    icon VARCHAR(50),
43
    color VARCHAR(7),
44
    UNIQUE (project_id, name)
45
);
46

47
-- Issues with LexoRank ordering
48
CREATE TABLE issues (
49
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
50
    project_id UUID NOT NULL REFERENCES projects(id),
51
    issue_type_id UUID NOT NULL REFERENCES issue_types(id),
52
    status_id UUID NOT NULL REFERENCES statuses(id),
53

54
    -- Issue key: computed from project key + sequence
55
    issue_number INT NOT NULL,
56

57
    title VARCHAR(500) NOT NULL,
58
    description TEXT,
59

60
    assignee_id UUID REFERENCES users(id),
61
    reporter_id UUID NOT NULL REFERENCES users(id),
62

63
    priority VARCHAR(20) DEFAULT 'medium',
64

65
    -- LexoRank for ordering within status
66
    -- Format: "0|hzzzzz" (bucket | alphanumeric)
67
    rank VARCHAR(255) NOT NULL,
68

69
    -- Optimistic locking
70
    version INT DEFAULT 1,
71

72
    created_at TIMESTAMPTZ DEFAULT NOW(),
73
    updated_at TIMESTAMPTZ DEFAULT NOW(),
74

75
    UNIQUE (project_id, issue_number)
76
);
77

78
-- Primary query: issues by status, ordered by rank
79
CREATE INDEX idx_issues_board ON issues(project_id, status_id, rank);
80

81
-- Secondary: issues by assignee
82
CREATE INDEX idx_issues_assignee ON issues(assignee_id, updated_at DESC);
83

84
-- Issue key lookup
85
CREATE INDEX idx_issues_key ON issues(project_id, issue_number);
86

87
-- Comments
88
CREATE TABLE comments (
89
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
90
    issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
91
    author_id UUID NOT NULL REFERENCES users(id),
92
    body TEXT NOT NULL,
93
    created_at TIMESTAMPTZ DEFAULT NOW(),
94
    updated_at TIMESTAMPTZ DEFAULT NOW()
95
);
96

97
CREATE INDEX idx_comments_issue ON comments(issue_id, created_at);
98

99
-- Activity log (append-only)
100
CREATE TABLE activity_log (
101
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
102
    issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
103
    user_id UUID NOT NULL REFERENCES users(id),
104
    action_type VARCHAR(50) NOT NULL,     -- 'status_change', 'assignment', etc.
105
    old_value JSONB,
106
    new_value JSONB,
107
    created_at TIMESTAMPTZ DEFAULT NOW()
108
);
109

110
CREATE INDEX idx_activity_issue ON activity_log(issue_id, created_at DESC);

1
# Board metadata
2
HSET board:{project_id}:meta
3
    columns_json "[{\"status_id\":\"s1\",\"name\":\"To Do\"}...]"
4
    total_issues 156
5
    last_updated 1706886400000
6

7
# Per-column issue list (sorted set by rank)
8
ZADD board:{project_id}:column:{status_id} {rank_score} {issue_id}
9

10
# Issue card data (hash - denormalized for fast read)
11
HSET issue:{issue_id}:card
12
    key "PROJ-123"
13
    title "Implement login"
14
    status_id "status-2"
15
    assignee_id "user-456"
16
    assignee_name "Alice"
17
    priority "high"
18
    rank "0|i000ab"
19
    version 5

1
Before: [A:1, B:2, C:3, D:4]
2
Insert X between B and C:
3
After:  [A:1, B:2, X:3, C:4, D:5]  ← Must update C, D

1
Before: [A:"aaa", B:"bbb", C:"ccc"]
2
Insert X between B and C:
3
After:  [A:"aaa", B:"bbb", X:"bbc", C:"ccc"]  ← Only X updated

1
0|hzzzzz
2
│ └─ Alphanumeric value (base-36)
3
└── Bucket (0, 1, or 2)

15 collapsed lines
1
// Simplified LexoRank implementation
2
const LEXORANK_CHARS = "0123456789abcdefghijklmnopqrstuvwxyz"
3
const BASE = LEXORANK_CHARS.length // 36
4

5
interface LexoRank {
6
  bucket: number
7
  value: string
8
}
9

10
function parseLexoRank(rank: string): LexoRank {
11
  const [bucket, value] = rank.split("|")
12
  return { bucket: parseInt(bucket), value }
13
}
14

15
function formatLexoRank(rank: LexoRank): string {
16
  return `${rank.bucket}|${rank.value}`
17
}
18

19
function getMidpoint(a: string, b: string): string {
20
  // Ensure same length by padding with '0's
21
  const maxLen = Math.max(a.length, b.length)
22
  const aPadded = a.padEnd(maxLen, "0")
23
  const bPadded = b.padEnd(maxLen, "0")
24

25
  // Convert to numbers (treating as base-36)
26
  let result = ""
27
  let carry = 0
28

29
  for (let i = maxLen - 1; i >= 0; i--) {
30
    const aVal = LEXORANK_CHARS.indexOf(aPadded[i])
31
    const bVal = LEXORANK_CHARS.indexOf(bPadded[i])
32
    const sum = aVal + bVal + carry
33
    const mid = Math.floor(sum / 2)
34
    carry = sum % 2
35
    result = LEXORANK_CHARS[mid] + result
36
  }
37

38
  // If a and b are adjacent, extend with midpoint
39
  if (result === aPadded) {
40
    result += LEXORANK_CHARS[Math.floor(BASE / 2)] // 'i'
41
  }
42

43
  return result.replace(/0+$/, "") // Trim trailing zeros
44
}
45

46
function calculateNewRank(before: string | null, after: string | null, bucket: number = 0): string {
47
  if (!before && !after) {
48
    // First item - use middle of range
49
    return formatLexoRank({ bucket, value: "i" })
50
  }
51

52
  if (!before) {
53
    // Insert at top - find value before 'after'
54
    const afterRank = parseLexoRank(after!)
55
    const newValue = getMidpoint("0", afterRank.value)
56
    return formatLexoRank({ bucket, value: newValue })
57
  }
58

59
  if (!after) {
60
    // Insert at bottom - find value after 'before'
61
    const beforeRank = parseLexoRank(before)
62
    const newValue = getMidpoint(beforeRank.value, "z")
63
    return formatLexoRank({ bucket, value: newValue })
64
  }
65

66
  // Insert between two items
67
  const beforeRank = parseLexoRank(before)
68
  const afterRank = parseLexoRank(after)
69
  const newValue = getMidpoint(beforeRank.value, afterRank.value)
70
  return formatLexoRank({ bucket, value: newValue })
71
}

1
Initial:  "i"
2
After 1:  "ii"
3
After 2:  "iii"
4
...
5
After 50: "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"

5 collapsed lines
1
async function rebalanceColumn(projectId: string, statusId: string): Promise<void> {
2
  // 1. Lock column for writes (or use different bucket)
3
  const lockKey = `rebalance:${projectId}:${statusId}`
4
  await redis.set(lockKey, "1", "EX", 300) // 5 min lock
5

6
  try {
7
    // 2. Fetch all issues ordered by current rank
8
    const issues = await db.query(
9
      `
10
      SELECT id, rank
11
      FROM issues
12
      WHERE project_id = $1 AND status_id = $2
13
      ORDER BY rank
14
    `,
15
      [projectId, statusId],
16
    )
17

18
    // 3. Assign evenly-spaced new ranks
19
    const newBucket = (parseInt(issues[0]?.rank?.split("|")[0] || "0") + 1) % 3
20
    const step = Math.floor(BASE / (issues.length + 1))
21

22
    const updates = issues.map((issue, index) => {
23
      const position = step * (index + 1)
24
      const newValue = position.toString(36).padStart(6, "0")
25
      return {
26
        id: issue.id,
27
        newRank: `${newBucket}|${newValue}`,
28
      }
29
    })
30

31
    // 4. Batch update
32
    await db.transaction(async (tx) => {
33
      for (const { id, newRank } of updates) {
34
        await tx.query("UPDATE issues SET rank = $1 WHERE id = $2", [newRank, id])
35
      }
36
    })
37

38
    // 5. Invalidate cache
39
    await invalidateBoardCache(projectId)
40
  } finally {
41
    await redis.del(lockKey)
42
  }
43
}

20 collapsed lines
1
interface UpdateIssueInput {
2
  issueId: string
3
  title?: string
4
  description?: string
5
  assigneeId?: string
6
  version: number
7
}
8

9
interface UpdateResult {
10
  success: boolean
11
  issue?: Issue
12
  error?: { code: string; message: string }
13
}
14

15
async function updateIssue(input: UpdateIssueInput): Promise<UpdateResult> {
16
  const { issueId, version, ...updates } = input
17

18
  // Build dynamic UPDATE query
19
  const setClause = Object.entries(updates)
20
    .filter(([_, v]) => v !== undefined)
21
    .map(([k, _], i) => `${toSnakeCase(k)} = $${i + 3}`)
22
    .join(", ")
23

24
  const values = Object.values(updates).filter((v) => v !== undefined)
25

26
  const result = await db.query(
27
    `
28
    UPDATE issues
29
    SET ${setClause}, version = version + 1, updated_at = NOW()
30
    WHERE id = $1 AND version = $2
31
    RETURNING *
32
  `,
33
    [issueId, version, ...values],
34
  )
35

36
  if (result.rowCount === 0) {
37
    // Check if issue exists
38
    const exists = await db.query("SELECT version FROM issues WHERE id = $1", [issueId])
39

40
    if (exists.rowCount === 0) {
41
      return {
42
        success: false,
43
        error: { code: "NOT_FOUND", message: "Issue not found" },
44
      }
45
    }
46

47
    const currentVersion = exists.rows[0].version
48
    return {
49
      success: false,
50
      error: {
51
        code: "VERSION_CONFLICT",
52
        message: `Version mismatch. Expected ${version}, current is ${currentVersion}`,
53
      },
54
    }
55
  }
56

57
  // Broadcast change
58
  await publishBoardEvent(result.rows[0].project_id, {
59
    action: "UPDATED",
60
    issue: result.rows[0],
61
  })
62

63
  return { success: true, issue: result.rows[0] }
64
}

1
// Client 1 updates title (version 5 → 6)
2
// Client 2 updates assignee (version 5 → conflict)
3
// Instead of rejecting, merge if fields don't overlap
4

5
async function mergeUpdate(input: UpdateIssueInput, currentIssue: Issue): Promise<UpdateResult> {
6
  const { version, ...updates } = input
7

8
  // Find which fields changed since client's version
9
  const changedFields = await getChangedFieldsSince(input.issueId, version, currentIssue.version)
10

11
  // Check for conflicts
12
  const conflictingFields = Object.keys(updates).filter((f) => changedFields.includes(f))
13

14
  if (conflictingFields.length > 0) {
15
    return {
16
      success: false,
17
      error: {
18
        code: "FIELD_CONFLICT",
19
        message: `Conflicting fields: ${conflictingFields.join(", ")}`,
20
      },
21
    }
22
  }
23

24
  // No conflicts - apply update to latest version
25
  return updateIssue({
26
    ...input,
27
    version: currentIssue.version,
28
  })
29
}

10 collapsed lines
1
interface MoveIssueInput {
2
  issueId: string
3
  toStatusId: string
4
  rankAfterId?: string
5
  rankBeforeId?: string
6
  version: number
7
}
8

9
async function moveIssue(input: MoveIssueInput): Promise<UpdateResult> {
10
  const { issueId, toStatusId, rankAfterId, rankBeforeId, version } = input
11

12
  return db.transaction(async (tx) => {
13
    // 1. Lock and fetch current issue
14
    const issue = await tx.query("SELECT * FROM issues WHERE id = $1 FOR UPDATE", [issueId])
15

16
    if (!issue.rows[0]) {
17
      return { success: false, error: { code: "NOT_FOUND", message: "Issue not found" } }
18
    }
19

20
    if (issue.rows[0].version !== version) {
21
      return {
22
        success: false,
23
        error: { code: "VERSION_CONFLICT", message: "Concurrent modification" },
24
      }
25
    }
26

27
    const currentIssue = issue.rows[0]
28

29
    // 2. Validate transition
30
    const transitionValid = await validateTransition(tx, currentIssue.project_id, currentIssue.status_id, toStatusId)
31

32
    if (!transitionValid) {
33
      return {
34
        success: false,
35
        error: { code: "INVALID_TRANSITION", message: "Workflow does not allow this transition" },
36
      }
37
    }
38

39
    // 3. Calculate new rank
40
    let newRank: string
41

42
    if (rankAfterId) {
43
      const afterIssue = await tx.query("SELECT rank FROM issues WHERE id = $1", [rankAfterId])
44
      const beforeIssue = rankBeforeId ? await tx.query("SELECT rank FROM issues WHERE id = $1", [rankBeforeId]) : null
45

46
      newRank = calculateNewRank(afterIssue.rows[0]?.rank, beforeIssue?.rows[0]?.rank)
47
    } else if (rankBeforeId) {
48
      const beforeIssue = await tx.query("SELECT rank FROM issues WHERE id = $1", [rankBeforeId])
49
      newRank = calculateNewRank(null, beforeIssue.rows[0]?.rank)
50
    } else {
51
      // Default: bottom of column
52
      const lastInColumn = await tx.query(
53
        `
54
        SELECT rank FROM issues
55
        WHERE project_id = $1 AND status_id = $2
56
        ORDER BY rank DESC LIMIT 1
57
      `,
58
        [currentIssue.project_id, toStatusId],
59
      )
60

61
      newRank = calculateNewRank(lastInColumn.rows[0]?.rank, null)
62
    }
63

64
    // 4. Update issue
65
    const result = await tx.query(
66
      `
67
      UPDATE issues
68
      SET status_id = $1, rank = $2, version = version + 1, updated_at = NOW()
69
      WHERE id = $3
70
      RETURNING *
71
    `,
72
      [toStatusId, newRank, issueId],
73
    )
74

75
    // 5. Log activity
76
    await tx.query(
77
      `
78
      INSERT INTO activity_log (issue_id, user_id, action_type, old_value, new_value)
79
      VALUES ($1, $2, 'status_change', $3, $4)
80
    `,
81
      [
82
        issueId,
83
        getCurrentUserId(),
84
        JSON.stringify({ status_id: currentIssue.status_id }),
85
        JSON.stringify({ status_id: toStatusId }),
86
      ],
87
    )
88

89
    // 6. Broadcast (after commit)
90
    setImmediate(() => {
91
      publishBoardEvent(currentIssue.project_id, {
92
        action: "MOVED",
93
        issue: result.rows[0],
94
        previousStatusId: currentIssue.status_id,
95
      })
96
    })
97

98
    return { success: true, issue: result.rows[0] }
99
  })
100
}