| 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`, so |
| 4 | -- `#N` is unique across both. |
| 5 | ALTER TABLE repos ADD COLUMN issues_enabled INTEGER NOT NULL DEFAULT 1; |
| 6 | ALTER TABLE repos ADD COLUMN pulls_enabled INTEGER NOT NULL DEFAULT 1; |
| 7 | ALTER TABLE repos ADD COLUMN next_iid BIGINT NOT NULL DEFAULT 1; |
| 8 | |
| 9 | CREATE TABLE labels ( |
| 10 | id TEXT PRIMARY KEY, |
| 11 | repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 12 | name TEXT NOT NULL, |
| 13 | name_lower TEXT NOT NULL, |
| 14 | color TEXT NOT NULL, -- '#rrggbb' |
| 15 | description TEXT, |
| 16 | created_at BIGINT NOT NULL |
| 17 | ); |
| 18 | CREATE UNIQUE INDEX idx_labels_repo_name ON labels (repo_id, name_lower); |
| 19 | |
| 20 | CREATE TABLE issues ( |
| 21 | id TEXT PRIMARY KEY, |
| 22 | repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 23 | number BIGINT NOT NULL, |
| 24 | author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 25 | title TEXT NOT NULL, |
| 26 | body TEXT NOT NULL, |
| 27 | state TEXT NOT NULL DEFAULT 'open', -- 'open' | 'closed' |
| 28 | is_pull INTEGER NOT NULL DEFAULT 0, |
| 29 | assignee_id TEXT REFERENCES users(id) ON DELETE SET NULL, |
| 30 | created_at BIGINT NOT NULL, |
| 31 | updated_at BIGINT NOT NULL, |
| 32 | closed_at BIGINT |
| 33 | ); |
| 34 | CREATE UNIQUE INDEX idx_issues_repo_number ON issues (repo_id, number); |
| 35 | CREATE INDEX idx_issues_repo_state ON issues (repo_id, is_pull, state); |
| 36 | |
| 37 | CREATE TABLE comments ( |
| 38 | id TEXT PRIMARY KEY, |
| 39 | issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 40 | author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 41 | body TEXT NOT NULL, |
| 42 | created_at BIGINT NOT NULL, |
| 43 | updated_at BIGINT NOT NULL |
| 44 | ); |
| 45 | |
| 46 | CREATE TABLE issue_labels ( |
| 47 | issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE, |
| 48 | label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE, |
| 49 | PRIMARY KEY (issue_id, label_id) |
| 50 | ); |
| 51 | |
| 52 | CREATE TABLE pull_requests ( |
| 53 | issue_id TEXT PRIMARY KEY REFERENCES issues(id) ON DELETE CASCADE, |
| 54 | base_ref TEXT NOT NULL, |
| 55 | head_ref TEXT NOT NULL, |
| 56 | merged_at BIGINT, |
| 57 | merged_by TEXT REFERENCES users(id) ON DELETE SET NULL, |
| 58 | merge_sha TEXT, |
| 59 | merge_strategy TEXT |
| 60 | ); |