Q: How do you handle large file attachments in an issue tracker?

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.

Q: How do you design the issue history/audit trail for an issue tracker?

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.

Question 1

How does the workflow state machine prevent invalid status transitions?

Accepted Answer

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.

Question 2

How do you implement efficient JQL-style query parsing and execution?

Accepted Answer

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.

Question 3

How does the notification deduplication/batching system work?

Accepted Answer

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

Question 4

How do you handle large file attachments in an issue tracker?

Accepted Answer

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.

Question 5

How do you design the issue history/audit trail for an issue tracker?

Accepted Answer

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.

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

Core Entities

Workflow State Machine

Full-Text Search and Filtering

Notification System