Low-Level Design: Issue Tracker — Workflow State Machine, Search, and Notifications

Core Entities

Issue: issue_id, project_id, title, description (rich text), type (BUG, FEATURE, TASK, EPIC), status, priority (P0-P3), assignee_id, reporter_id, parent_issue_id (for subtasks), sprint_id, story_points, labels (array), created_at, updated_at, resolved_at. Project: project_id, name, slug, workflow_id, board_config (JSON — column order, WIP limits), default_assignee_policy, created_at. WorkflowStatus: status_id, project_id, name (OPEN, IN_PROGRESS, IN_REVIEW, DONE, WONT_FIX), category (TODO, IN_PROGRESS, DONE), transitions (array of allowed next status IDs), is_initial (bool), is_terminal (bool). Comment: comment_id, issue_id, author_id, body, created_at, updated_at, is_deleted. Attachment: attachment_id, issue_id, filename, storage_key, size_bytes, mime_type, uploaded_by, created_at. IssueHistory: history_id, issue_id, changed_by, field_name, old_value, new_value, changed_at. Watcher: issue_id, user_id — users who receive notifications for issue changes.

Workflow State Machine

class WorkflowEngine:
    def transition(self, issue_id: int, new_status_id: int,
                   actor_id: int) -> Issue:
        with db.transaction():
            issue = db.query(
                "SELECT * FROM issues WHERE issue_id = %s FOR UPDATE",
                issue_id
            )
            current_status = db.get_status(issue.status_id)
            new_status = db.get_status(new_status_id)

            # Validate transition is allowed
            if new_status_id not in current_status.transitions:
                raise InvalidTransition(
                    f"Cannot go from {current_status.name} to {new_status.name}"
                )

            # Run transition hooks
            self._run_pre_hooks(issue, current_status, new_status, actor_id)

            # Apply the transition
            old_status_id = issue.status_id
            db.execute(
                "UPDATE issues SET status_id = %s, updated_at = NOW() "
                "WHERE issue_id = %s",
                new_status_id, issue_id
            )

            # Set resolved_at on terminal status
            if new_status.is_terminal:
                db.execute(
                    "UPDATE issues SET resolved_at = NOW() WHERE issue_id = %s",
                    issue_id
                )

            # Record history
            db.insert("issue_history", {
                "issue_id": issue_id,
                "changed_by": actor_id,
                "field_name": "status",
                "old_value": current_status.name,
                "new_value": new_status.name,
                "changed_at": "NOW()"
            })

            self._run_post_hooks(issue, current_status, new_status, actor_id)
            return db.get_issue(issue_id)

Full-Text Search and Filtering

Issue search requirements: full-text search on title and description, filter by status/assignee/labels/sprint/priority, sort by created_at/updated_at/priority. Implementation: PostgreSQL full-text search for moderate scale (< 10M issues): tsvector column on (title || ' ' || description), indexed with GIN. Query: WHERE tsvector @@ to_tsquery('english', $query). Elasticsearch for large scale: index issues as documents with all fields. Use multi-match query on title (boosted) and description. Filter by terms (status, assignee) as filter context (no relevance scoring, cached). Sort: by _score descending, then updated_at. Sync: on issue create/update, publish to a Kafka topic; a consumer indexes into Elasticsearch with a 1-2 second lag. For JQL (Jira Query Language)-style queries: parse the query string into a filter AST and translate to Elasticsearch DSL or SQL.

Notification System

Notification triggers: issue assigned to you, comment on a watched issue, status transition, mention (@username) in a comment. Architecture: on each issue mutation, publish an IssueEvent to Kafka. A notification worker consumes events and: (1) identifies relevant users (assignee, reporter, watchers, mentioned users). (2) Applies user notification preferences (which events they want, which channels: email, in-app, Slack). (3) Deduplicates: if 10 comments arrive in 5 minutes, batch them into one email digest instead of 10 separate emails. (4) Sends via the appropriate channel: SES for email, WebSocket push for in-app, Slack webhook. Notification preferences: stored per user per event type (ISSUE_ASSIGNED, ISSUE_COMMENTED, ISSUE_TRANSITIONED, ISSUE_MENTIONED). Respect “do not disturb” hours (no emails between 10pm-7am user’s local time).

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How does the workflow state machine prevent invalid status transitions?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each WorkflowStatus record has a transitions field (array of allowed next status IDs). The WorkflowEngine validates: is new_status_id in current_status.transitions? If not, raise InvalidTransition. This is enforced at the service layer, not the database — the database stores the raw status_id without referential transition constraints. The workflow is configurable per project: projects can define their own statuses and allowed transitions (e.g., some projects require IN_REVIEW before DONE; others allow direct OPEN → DONE). The transition matrix is cached in memory (it changes rarely) to avoid per-transition database lookups. Row-level lock (SELECT FOR UPDATE) on the issue during transition prevents concurrent transitions to inconsistent states.”}},{“@type”:”Question”,”name”:”How do you implement efficient JQL-style query parsing and execution?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”JQL (Jira Query Language) example: "project = BACKEND AND status in (OPEN, IN_PROGRESS) AND assignee = currentUser() ORDER BY updated DESC". Implementation: (1) Lexer: tokenize the string into tokens (field name, operator, value, AND/OR/NOT, ORDER BY). (2) Parser: build an AST (Abstract Syntax Tree) from tokens using recursive descent parsing. (3) Translator: traverse the AST and emit either SQL (for PostgreSQL) or an Elasticsearch DSL query. Special functions (currentUser(), startOfWeek()) are resolved before translation. (4) Executor: run the query and paginate results. Field-to-column mapping is defined in a schema config. Security: parameterize all values — never interpolate user input directly into SQL.”}},{“@type”:”Question”,”name”:”How does the notification deduplication/batching system work?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Without batching, a flurry of rapid changes (10 comments in 5 minutes) would send 10 emails — annoying and likely to cause unsubscribes. Batching: when an IssueEvent is published, instead of immediately sending a notification, schedule a "notification batch" job for the (user, issue) pair with a 5-minute delay. If another event arrives for the same (user, issue) within 5 minutes, update the existing batch job (add the new event) rather than scheduling a new one. After 5 minutes of quiet: send one batched notification summarizing all events. Implementation: use a Redis sorted set as a delay queue. ZADD delay_queue (now + 300) (user_id, issue_id). A worker polls for entries with score <= now, processes the batch, and deletes the entry. If a new event arrives mid-wait: update the score to now+300 (reset the timer).”}},{“@type”:”Question”,”name”:”How do you handle large file attachments in an issue tracker?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Never upload attachments to the application server or store them in the database. Flow: (1) Client requests a pre-signed S3 upload URL from the API (POST /issues/{id}/attachments/upload-url). The API generates a pre-signed PUT URL valid for 15 minutes, with constraints on file size and MIME type. (2) Client uploads directly to S3 using the pre-signed URL — bypasses the application server entirely, so large files (100MB+) do not consume server memory or bandwidth. (3) Client notifies the API that the upload is complete (POST /issues/{id}/attachments with the storage_key). (4) API creates the Attachment record in the database. (5) For downloads: generate a pre-signed GET URL on demand (never expose the raw S3 URL — keep the bucket private). Set Content-Disposition header to trigger browser download.”}},{“@type”:”Question”,”name”:”How do you design the issue history/audit trail for an issue tracker?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The IssueHistory table records every field change: (issue_id, changed_by, field_name, old_value, new_value, changed_at). Write on every update: in the same database transaction as the issue update, insert a history row for each changed field. Comparing old vs. new: load the issue before the update, diff all fields, insert one history row per changed field. For complex fields (labels array, custom fields JSON): serialize the before/after state as strings. Display: the history feed shows a timeline of changes sorted by changed_at. Performance: the history table is append-only and insert-heavy. Partition by issue_id or changed_at for large installations. For compliance (SOX, HIPAA): ensure the history table is immutable — no DELETE or UPDATE permissions for application users; only INSERT.”}}]}