| 1 |
|
| 2 |
|
| 3 |
CREATE TABLE issues ( |
| 4 |
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 5 |
repo_id UUID NOT NULL REFERENCES git_repos(id) ON DELETE CASCADE, |
| 6 |
number INT NOT NULL, |
| 7 |
author_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 8 |
title VARCHAR(200) NOT NULL, |
| 9 |
body_markdown TEXT NOT NULL DEFAULT '', |
| 10 |
body_html TEXT NOT NULL DEFAULT '', |
| 11 |
status VARCHAR(20) NOT NULL DEFAULT 'open', |
| 12 |
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 13 |
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 14 |
UNIQUE (repo_id, number) |
| 15 |
); |
| 16 |
|
| 17 |
CREATE TABLE issue_comments ( |
| 18 |
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 19 |
issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 20 |
author_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 21 |
body_markdown TEXT NOT NULL, |
| 22 |
body_html TEXT NOT NULL, |
| 23 |
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() |
| 24 |
); |
| 25 |
|
| 26 |
CREATE TABLE issue_labels ( |
| 27 |
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 28 |
repo_id UUID NOT NULL REFERENCES git_repos(id) ON DELETE CASCADE, |
| 29 |
name VARCHAR(50) NOT NULL, |
| 30 |
color VARCHAR(7) NOT NULL DEFAULT '#6c5ce7', |
| 31 |
UNIQUE (repo_id, name) |
| 32 |
); |
| 33 |
|
| 34 |
CREATE TABLE issue_label_assignments ( |
| 35 |
issue_id UUID NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 36 |
label_id UUID NOT NULL REFERENCES issue_labels(id) ON DELETE CASCADE, |
| 37 |
PRIMARY KEY (issue_id, label_id) |
| 38 |
); |
| 39 |
|
| 40 |
CREATE INDEX idx_issues_repo_status ON issues(repo_id, status); |
| 41 |
CREATE INDEX idx_issues_author ON issues(author_user_id); |
| 42 |
CREATE INDEX idx_issue_comments_issue ON issue_comments(issue_id); |
| 43 |
CREATE INDEX idx_issue_labels_repo ON issue_labels(repo_id); |
| 44 |
|