Issue Tracking System

The Issue Tracking System is a comprehensive platform designed to streamline the management and resolution of various issues within an organization. The system should allow users to log and categorize different types of issues, including bugs, feature requests, and general tasks. Each issue should have a unique identifier, a detailed description, priority level, and status (e.g., open, in-progress, closed). Users should be able to assign issues to specific team members or individuals responsible for resolution. The system should support the attachment of relevant files, screenshots, or additional notes to provide detailed context for each issue. Additionally, there should be a robust search and filter mechanism to facilitate quick retrieval of specific issues. Notifications and alerts for status changes and updates are crucial to keep stakeholders informed. Integration with version control systems or development tools may be considered for enhanced collaboration. The system should also include reporting and analytics features to track issue resolution times, identify patterns, and improve overall efficiency.

Postgres SQL Schemas

These SQL schemas provide a foundation for building an Issue Tracking System. The Issues table stores essential information about each issue, including its title, description, priority, and status. The Attachments table allows for the association of files with specific issues. The Comments table captures user comments on each issue. The Users table manages user information, and the IssueHistory table logs changes in the status of issues over time. Together, these schemas create a robust structure for tracking, managing, and resolving issues within an organization.

Issues Table:

CREATE TABLE Issues (
  issue_id uuid,
  tenant_id uuid,
  title VARCHAR(255),
  description TEXT,
  priority VARCHAR(20),
  status VARCHAR(20),
  assignee_id uuid,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FK_Members FOREIGN KEY(tenant_id, assignee_id) REFERENCES Members(tenant_id,user_id),
  CONSTRAINT PK_Issues PRIMARY KEY(tenant_id,issue_id));

Attachments Table:


CREATE TABLE Attachments (
  attachment_id uuid,
  tenant_id uuid,
  issue_id uuid,
  file_name VARCHAR(255),
  file_type VARCHAR(50),
  file_data BYTEA,
  CONSTRAINT FK_Issues FOREIGN KEY(tenant_id, issue_id) REFERENCES Issues(tenant_id,issue_id),
  CONSTRAINT PK_Attachments PRIMARY KEY(tenant_id,attachment_id)
);

Comments Table:


CREATE TABLE Comments (
  comment_id uuid,
  tenant_id uuid,
  issue_id uuid,
  user_id uuid,
  comment_text TEXT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FK_Issues FOREIGN KEY(tenant_id, issue_id) REFERENCES Issues(tenant_id,issue_id),
  CONSTRAINT FK_Members FOREIGN KEY(tenant_id, user_id) REFERENCES Members(tenant_id,user_id),
  CONSTRAINT PK_Comments PRIMARY KEY(tenant_id,comment_id)
);

Users Table:


CREATE TABLE Members (
  user_id uuid,
  tenant_id uuid,
  username VARCHAR(50),
  email VARCHAR(255),
  role VARCHAR(20),
  CONSTRAINT FK_Tenants FOREIGN KEY(tenant_id) REFERENCES Tenants(id),
  CONSTRAINT PK_Members PRIMARY KEY(tenant_id,user_id)
);

IssueHistory Table:


CREATE TABLE IssueHistory (
  history_id uuid,
  tenant_id uuid,
  issue_id uuid,
  status_before VARCHAR(20),
  status_after VARCHAR(20),
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FK_Issues FOREIGN KEY(tenant_id, issue_id) REFERENCES Issues(tenant_id,issue_id),
  CONSTRAINT PK_IssueHistory PRIMARY KEY(tenant_id,history_id)
);

Sample data

-- Sample data for Tenants table
INSERT INTO Tenants (id, name) VALUES
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'Customer A'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'Customer B');

-- Sample data for Members table
INSERT INTO Members (user_id, tenant_id, username, email, role) VALUES
  ('8b7f0c1b-c3ab-4870-80fb-704b90dc9783', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', 'john_doe', 'john.doe@example.com', 'Admin'),
  ('b17a1a72-8e4f-4f93-9f47-9c3d25f106b9', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', 'jane_smith', 'jane.smith@example.com', 'User'),
  ('c6d83d16-b8ef-4d4f-ae36-171c53d96a1a', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'bob_jones', 'bob.jones@example.com', 'Admin');

-- Sample data for Issues table
INSERT INTO Issues (issue_id, tenant_id, title, description, priority, status, assignee_id, created_at, updated_at) VALUES
  ('579f0e1a-d5d4-4aeb-b0c6-12d99f7d9c57', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', 'Bug Report', 'Application crashes on startup', 'High', 'Open', 'b17a1a72-8e4f-4f93-9f47-9c3d25f106b9', NOW(), NOW()),
  ('0e10c2a5-7a2a-46c9-b506-53c47128774c', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'Feature Request', 'Add dark mode option', 'Medium', 'In Progress', 'c6d83d16-b8ef-4d4f-ae36-171c53d96a1a', NOW(), NOW());

-- Sample data for Attachments table
INSERT INTO Attachments (attachment_id, tenant_id, issue_id, file_name, file_type, file_data) VALUES
  ('48b72f15-5423-4e2a-9dcb-9a3b0e598188', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', '579f0e1a-d5d4-4aeb-b0c6-12d99f7d9c57', 'screenshot.png', 'image/png', E'\\x89504E470D0A1A0A0000000D49484452000000010000000108020000006E30F9C000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000017352474200AECE1CE90000000467414D410000B18F0BFC61',
  ('a16e6f2a-2554-42a5-9ca3-9c56a6e1e2f8', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '0e10c2a5-7a2a-46c9-b506-53c47128774c', 'requirements.docx', 'application/msword', E'\\x504B03041400080808000000000099AC3C590000000000000000000000100000000000000000000000000000000A48118000000000D00000000000000000000000072656C732F776F7264732F646F63756D656E74732F677261706869635F646F63756D656E742E786D6C504B0506000000000100010039000000230000000000');

-- Sample data for Comments table
INSERT INTO Comments (comment_id, tenant_id, issue_id, user_id, comment_text, timestamp) VALUES
  ('2a2b3d44-77cd-49f8-9f9e-20a45ec3c453', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', '579f0e1a-d5d4-4aeb-b0c6-12d99f7d9c57', 'b17a1a72-8e4f-4f93-9f47-9c3d25f106b9', 'I can replicate this issue.', NOW()),
  ('227ba679-0e3e-4b31-9225-925f9a3a5d8e', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '0e10c2a5-7a2a-46c9-b506-53c47128774c', 'c6d83d16-b8ef-4d4f-ae36-171c53d96a1a', 'Agreed, dark mode would be great!', NOW());

-- Sample data for IssueHistory table
INSERT INTO IssueHistory (history_id, tenant_id, issue_id, status_before, status_after, timestamp) VALUES
  ('1bd3625e-1a64-48aa-a69b-0ebf06d3b0e2', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', '579f0e1a-d5d4-4aeb-b0c6-12