Technical Design TD-0005 — Issue Tracking Workflows and Boards¶

1. Summary¶

• The issue tracking service needs a workflow engine with auto-walk transitions and a board system supporting Kanban and Scrum methodologies
• This document specifies the workflow transition model, auto-walk algorithm, board entities, LexoRank ordering strategy, and sprint lifecycle
• Impact: enables visual project management with drag-and-drop boards and sprint planning

2. Context and Problem Statement¶

The existing workflow_transition table models a directed graph of allowed status transitions. However, there is no board system to visualize issues by status, no sprint management, no drag-and-drop ranking, and no mechanism to handle status changes when board columns skip intermediate workflow steps.

Users expect to drag issues across board columns and have the system determine the correct status transition automatically, even when intermediate statuses exist in the workflow graph.

2.1 Goals¶

• Define the auto-walk transition algorithm for board drag operations
• Specify the board data model with columns, status mappings, and swimlanes
• Design the LexoRank ordering strategy with rebalancing
• Specify the sprint lifecycle model
• Define all related API endpoints

2.2 Out of Scope¶

• Workflow conditions or guards (future: conditional transitions based on field values)
• Board filters and JQL-like query language (future)
• Cross-project boards (future)
• Burndown charts (covered in TDD-0007 reporting)

2.3 Assumptions and Constraints¶

• Workflows are small directed graphs (typically fewer than 20 statuses)
• A single issue has one global rank (no per-board or per-sprint ranking)
• At most one sprint can be active per project at any time
• Board operations are synchronous (no background processing except rank rebalancing)

3. Design Overview¶

3.1 Key Components¶

3.2 Architecture¶

flowchart LR
    subgraph Board["Board System"]
        BC[Board Column] --> BSM[Column-Status Mapping]
        BC --> PS[Primary Drop Status]
        BC --> SL[Swimlane Config]
    end

    subgraph Workflow["Workflow Engine"]
        WT[Workflow Transitions] --> AW[Auto-Walk BFS]
        AW --> TV[Transition Validation]
    end

    subgraph Ranking["Ranking System"]
        LR_[LexoRank] --> RB[Rebalance Job]
    end

    subgraph Sprint["Sprint Management"]
        SP[Sprint] --> BL[Backlog]
        SP --> AS[Active Sprint]
    end

    Board --> Workflow
    Board --> Ranking
    Board --> Sprint

3.3 Interface Impact (Before → After)¶

4. Implementation Details¶

4.1 Workflow Transition Model¶

The existing workflow_transition table already models the directed graph:

workflow_transition(workflow_id, source_status_id, target_status_id)

Two key operations:

• Direct transition check: can_transition(workflow_id, from_status, to_status) — single edge lookup
• Path finding: find_path(workflow_id, from_status, to_status) — BFS for auto-walk

4.2 Auto-Walk Algorithm¶

When a user drags an issue to a board column, the system determines the target status (the column's primary_status_id) and attempts to transition the issue. If no direct transition exists, BFS finds the shortest valid path.

from collections import deque


def find_path(
    transitions: dict[int, list[int]],
    from_status_id: int,
    to_status_id: int,
) -> list[int] | None:
    """Find shortest path through workflow transition graph using BFS.

    :param transitions: Adjacency list of workflow transitions (status_id → [target_status_ids])
    :param from_status_id: Current status ID
    :param to_status_id: Target status ID
    :return: List of status IDs from current (exclusive) to target (inclusive), or None if no path
    """
    if from_status_id == to_status_id:
        return []

    queue: deque[tuple[int, list[int]]] = deque([(from_status_id, [])])
    visited: set[int] = {from_status_id}

    while queue:
        current, path = queue.popleft()
        for neighbor in transitions.get(current, []):
            if neighbor == to_status_id:
                return path + [neighbor]
            if neighbor not in visited:
                visited.add(neighbor)
                queue.append((neighbor, path + [neighbor]))

    return None

Board drag behavior:

1. User drags issue to column C
2. Resolve target status = C.primary_status_id
3. Load workflow transitions for the issue's type/project
4. Call find_path(transitions, issue.status_id, target_status_id)
5. If path is None → reject with 422 Unprocessable Entity
6. If path exists → set issue.status_id = target_status_id
7. Record one activity log entry: status_changed(from=original, to=target)
8. If target status has category = 'in_progress' and issue.start_date IS NULL → set issue.start_date = NOW()

4.3 Board Data Model¶

Board¶

BoardColumn¶

Constraint: UNIQUE(board_id, position).

BoardColumnStatus¶

To enforce "each status in at most one column per board", a unique index is needed. Since board_id is not directly on this table, enforcement uses a compound approach:

-- Partial unique index via a function or materialized view
-- Simpler approach: application-level validation on column configuration
CREATE UNIQUE INDEX uq_board_column_status
    ON issues.board_column_status (board_column_id, status_id);

Application-level validation ensures a status does not appear in multiple columns of the same board.

BoardSwimlaneConfig¶

4.4 LexoRank Ordering¶

Alphabet and Encoding¶

• Character set: 0123456789abcdefghijklmnopqrstuvwxyz (36 characters)
• Midpoint character: i (index 18 of 36)
• Ranks are compared lexicographically using standard string comparison

Operations¶

Initial rank (first issue in project):

rank = "n"  # midpoint of alphabet

Insert between two issues:

def generate_between(rank_a: str, rank_b: str) -> str:
    """Generate a rank string between rank_a and rank_b.

    :param rank_a: Lower bound rank (exclusive)
    :param rank_b: Upper bound rank (exclusive)
    :return: New rank string that sorts between rank_a and rank_b
    """
    # Pad shorter string with '0' (lowest char) for comparison
    max_len = max(len(rank_a), len(rank_b))
    a = rank_a.ljust(max_len, '0')
    b = rank_b.ljust(max_len, '0')

    # Find first differing position
    for i in range(max_len):
        if a[i] != b[i]:
            mid_char = ALPHABET[(ALPHABET.index(a[i]) + ALPHABET.index(b[i])) // 2]
            if mid_char != a[i]:
                return a[:i] + mid_char
            # Adjacent characters — extend with midpoint
            return a[:i + 1] + 'i'

    # Identical up to max_len — extend rank_a with midpoint
    return rank_a + 'i'

Insert at top (before minimum rank):

If min_rank[0] > '0': new_rank = ALPHABET[ALPHABET.index(min_rank[0]) // 2]
If min_rank[0] == '0': new_rank = '0' + 'i'

Insert at bottom (after maximum rank):

If max_rank[0] < 'z': new_rank = ALPHABET[(ALPHABET.index(max_rank[0]) + 35) // 2 + 1]
If max_rank[0] == 'z': new_rank = 'z' + 'i'

Rebalancing¶

When any rank in a project exceeds 50 characters:

1. Acquire advisory lock: pg_advisory_xact_lock(project_id + REBALANCE_LOCK_OFFSET)
2. Load all issues in the project ordered by current rank
3. Assign evenly-spaced ranks using the alphabet
4. Update all issue ranks in a single transaction
5. Release lock (automatic on transaction commit)

Rebalancing is triggered as a background task after any rank operation produces a string exceeding the threshold.

4.5 Sprint Model¶

Sprint Entity¶

Sprint Lifecycle¶

stateDiagram-v2
    [*] --> Planned: Create sprint
    Planned --> Active: Start sprint
    Active --> Completed: Complete sprint
    Completed --> [*]

Constraints: - At most one active sprint per project: UNIQUE(project_id) WHERE status = 'active' (partial unique index) - Starting a sprint sets start_date = TODAY if not already set - Completing a sprint sets completed_at = NOW()

Sprint completion workflow: 1. All issues with status.category = 'done' remain in the completed sprint 2. Incomplete issues (status category todo or in_progress) are moved to the next planned sprint or set to sprint_id = NULL (backlog) if no planned sprint exists 3. The choice is made by the user at completion time via request parameter

Backlog: Issues with sprint_id IS NULL and deleted_at IS NULL. Displayed in rank order.

4.6 start_date Auto-Set¶

When an issue transitions to a status with category = 'in_progress': - If issue.start_date IS NULL → set issue.start_date = CURRENT_DATE - If issue.start_date is already set → no change (preserves manual overrides)

This applies to all status transitions, whether from direct API calls, board drags, or auto-walk.

4.7 API Endpoints¶

Board Endpoints¶

Issue Movement Endpoints¶

Rank request body:

{
  "before": "PROJ-45",
  "after": "PROJ-46"
}

At least one of before or after must be provided. Both null is invalid. If only before is provided, the issue is placed at the top. If only after is provided, the issue is placed at the bottom.

Move request body:

{
  "board_uuid": "...",
  "column_uuid": "..."
}

Sprint Endpoints¶

Complete sprint request body:

{
  "move_to": "next_sprint | backlog"
}

4.8 Error Handling and Edge Cases¶

5. Semantics¶

• Board columns define a view of issues, not ownership. Deleting a column does not affect issues.
• An issue's rank is global across all views (board, backlog, sprint). Moving an issue on a board does not change its rank unless explicitly reordered.
• Auto-walk is transparent to the user — they see a single status change, not intermediate steps.
• Sprint assignment (sprint_id) is independent of board display. An issue can be in a sprint but not on any board.
• The start_date auto-set only fires once (first transition to "in progress"). Subsequent transitions between "in progress" statuses do not reset it.

6. Examples¶

6.1 Board Drag with Auto-Walk¶

Workflow transitions: Open → In Progress → Code Review → QA → Done
Board columns: [To Do (Open)] [In Progress (In Progress)] [Review (Code Review, QA)] [Done (Done)]
                                                            primary: Code Review

Issue is in "Open" status. User drags to "Review" column.
1. Target status = Code Review (primary_status of Review column)
2. find_path(Open, Code Review) = [In Progress, Code Review]
3. Path exists → set issue.status_id = Code Review
4. Activity log: status_changed(from="Open", to="Code Review")
5. start_date auto-set: Code Review has category='in_progress' → set start_date=TODAY

6.2 Sprint Completion¶

Active sprint "Sprint 5" has 10 issues:
- 6 with status.category = 'done'
- 4 with status.category = 'in_progress' or 'todo'

User completes Sprint 5 with move_to="next_sprint":
1. Sprint 5 status → completed, completed_at = NOW()
2. Find next planned sprint ("Sprint 6")
3. Move 4 incomplete issues: sprint_id = Sprint 6.id
4. 6 completed issues remain: sprint_id = Sprint 5.id (for velocity tracking)

7. Test Matrix¶

7.1 Unit Coverage¶

• BFS pathfinding: direct transition, multi-hop path, no path, self-loop, disconnected graph
• LexoRank generation: between two ranks, at top, at bottom, adjacent characters, long strings
• Sprint lifecycle: valid transitions, invalid transitions (e.g., planned → completed)

7.2 Integration Coverage¶

• Board drag with auto-walk (direct and multi-hop)
• Board drag rejection when no path exists
• Sprint start/complete lifecycle with issue movement
• Rank rebalancing triggered by long rank strings
• start_date auto-set on "in progress" transition
• WIP limit enforcement on board columns
• Concurrent rank updates on same project

7.3 Regression Coverage¶

• Existing can_transition() behavior unchanged
• Direct status change via API (non-board) still validates transitions
• Workflow CRUD operations unaffected

8. Acceptance Criteria¶

• Auto-walk finds shortest path through workflow graph via BFS
• Board drag sets the column's primary status on the issue
• Auto-walk records one activity log entry (not intermediate statuses)
• Invalid transitions (no path) return 422
• LexoRank generates valid between-ranks for all edge cases
• Rank rebalancing normalizes all ranks when threshold exceeded
• At most one active sprint per project (enforced by partial unique index)
• Sprint completion moves incomplete issues per user's choice
• start_date auto-set on first "in progress" transition only

9. Rollout and Migration¶

9.1 Phased Plan¶

Phase 2a: Schema - Create enum types (board_type, swimlane_type, sprint_status) - Create tables: board, board_column, board_column_status, board_swimlane_config, sprint - Add sprint_id FK to issue table (if not already added in Phase 1 migration)

Phase 2b: Workflow engine - Implement find_path() BFS algorithm - Integrate auto-walk into status transition flow - Add start_date auto-set logic

Phase 2c: Board and sprint - Implement board CRUD and column management - Implement LexoRank generation and rebalancing - Implement sprint lifecycle - Build all API endpoints

9.2 Migration Strategy¶

New tables are additive — no existing data is modified. The sprint_id column on issue defaults to NULL (backlog). Boards are created by users after migration; no default boards are auto-generated.

10. Risks and Mitigations¶

11. Progress¶

• Implement auto-walk BFS algorithm
• Create board entity and repository
• Implement board column management
• Implement LexoRank service
• Implement rank rebalancing job
• Create sprint entity and repository
• Implement sprint lifecycle service
• Build board API endpoints
• Build sprint API endpoints
• Write integration tests

12. Changelog¶

13. Glossary¶