| 1 | -- Collaboration: per-repo toggles + shared issue/PR number counter, labels, |
| 2 | -- issues (with is_pull discriminator), comments, issue↔label join, and the PR |
| 3 | -- side table. Issues and PRs share the `issues` table and `repos.next_iid`. |
| 4 | ALTER TABLE repos ADD COLUMN issues_enabled BOOLEAN NOT NULL DEFAULT TRUE; |
| 5 | ALTER TABLE repos ADD COLUMN pulls_enabled BOOLEAN NOT NULL DEFAULT TRUE; |
| 6 | ALTER TABLE repos ADD COLUMN next_iid BIGINT NOT NULL DEFAULT 1; |
| 7 | |
| 8 | CREATE TABLE labels ( |
| 9 | id TEXT PRIMARY KEY, |
| 10 | repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 11 | name TEXT NOT NULL, |
| 12 | name_lower TEXT NOT NULL, |
| 13 | color TEXT NOT NULL, |
| 14 | description TEXT, |
| 15 | created_at BIGINT NOT NULL |
| 16 | ); |
| 17 | CREATE UNIQUE INDEX idx_labels_repo_name ON labels (repo_id, name_lower); |
| 18 | |
| 19 | CREATE TABLE issues ( |
| 20 | id TEXT PRIMARY KEY, |
| 21 | repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 22 | number BIGINT NOT NULL, |
| 23 | author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 24 | title TEXT NOT NULL, |
| 25 | body TEXT NOT NULL, |
| 26 | state TEXT NOT NULL DEFAULT 'open', |
| 27 | is_pull BOOLEAN NOT NULL DEFAULT FALSE, |
| 28 | assignee_id TEXT REFERENCES users(id) ON DELETE SET NULL, |
| 29 | created_at BIGINT NOT NULL, |
| 30 | updated_at BIGINT NOT NULL, |
| 31 | closed_at BIGINT |
| 32 | ); |
| 33 | CREATE UNIQUE INDEX idx_issues_repo_number ON issues (repo_id, number); |
| 34 | CREATE INDEX idx_issues_repo_state ON issues (repo_id, is_pull, state); |
| 35 | |
| 36 | CREATE TABLE comments ( |
| 37 | id TEXT PRIMARY KEY, |
| 38 | issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 39 | author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 40 | body TEXT NOT NULL, |
| 41 | created_at BIGINT NOT NULL, |
| 42 | updated_at BIGINT NOT NULL |
| 43 | ); |
| 44 | |
| 45 | CREATE TABLE issue_labels ( |
| 46 | issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 47 | label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE, |
| 48 | PRIMARY KEY (issue_id, label_id) |
| 49 | ); |
| 50 | |
| 51 | CREATE TABLE pull_requests ( |
| 52 | issue_id TEXT PRIMARY KEY REFERENCES issues(id) ON DELETE CASCADE, |
| 53 | base_ref TEXT NOT NULL, |
| 54 | head_ref TEXT NOT NULL, |
| 55 | merged_at BIGINT, |
| 56 | merged_by TEXT REFERENCES users(id) ON DELETE SET NULL, |
| 57 | merge_sha TEXT, |
| 58 | merge_strategy TEXT |
| 59 | ); |