shithub Public

Watch 1 Fork 0 Star 0
MySQL · 8085 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S21 issues subsystem. Eight tables plus the per-repo counter:
4 --
5 -- repo_issue_counter — per-repo monotonic numbering for issues+PRs
6 -- issues — the row; `kind` discriminator covers PRs (S22)
7 -- issue_comments — per-issue threaded comments
8 -- issue_assignees — many-to-many issue ↔ user
9 -- labels — repo-scoped labels (default set seeded on create)
10 -- issue_labels — many-to-many issue ↔ label
11 -- milestones — repo-scoped milestones
12 -- issue_events — generic polymorphic timeline (label/assign/etc.)
13 -- issue_references — cross-reference index (comment/body/commit → issue)
14 --
15 -- The `kind` discriminator on `issues` ('issue'|'pr') is in from day 1
16 -- so PR rows in S22 are first-class and don't require a schema split.
17 -- PR-specific fields live in a `pull_requests` table keyed on issue_id
18 -- (built in S22).
19
20 -- +goose Up
21
22 CREATE TYPE issue_kind AS ENUM ('issue', 'pr');
23 CREATE TYPE issue_state AS ENUM ('open', 'closed');
24 CREATE TYPE issue_state_reason AS ENUM ('completed', 'not_planned', 'reopened', 'duplicate');
25 CREATE TYPE milestone_state AS ENUM ('open', 'closed');
26 CREATE TYPE issue_ref_source AS ENUM ('comment_body', 'issue_body', 'commit_message');
27
28 -- Per-repo monotonic counter. Allocation is one row UPDATE inside the
29 -- creating transaction so concurrent inserts can't collide. Issues +
30 -- PRs share the counter (matches GitHub's #N space).
31 CREATE TABLE repo_issue_counter (
32 repo_id bigint PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
33 next_number bigint NOT NULL DEFAULT 1
34 );
35
36 CREATE TABLE issues (
37 id bigserial PRIMARY KEY,
38 repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
39 number bigint NOT NULL,
40 kind issue_kind NOT NULL DEFAULT 'issue',
41 title text NOT NULL,
42 body text NOT NULL DEFAULT '',
43 body_html_cached text,
44 md_pipeline_version int NOT NULL DEFAULT 1,
45 author_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
46 state issue_state NOT NULL DEFAULT 'open',
47 state_reason issue_state_reason,
48 locked boolean NOT NULL DEFAULT false,
49 lock_reason text,
50 milestone_id bigint, -- FK added below after milestones exists
51 created_at timestamptz NOT NULL DEFAULT now(),
52 updated_at timestamptz NOT NULL DEFAULT now(),
53 edited_at timestamptz,
54 closed_at timestamptz,
55 closed_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
56
57 CONSTRAINT issues_title_length CHECK (char_length(title) BETWEEN 1 AND 256),
58 CONSTRAINT issues_body_length CHECK (char_length(body) <= 65535),
59
60 UNIQUE (repo_id, number)
61 );
62
63 CREATE INDEX issues_repo_state_updated_idx
64 ON issues (repo_id, state, updated_at DESC);
65 CREATE INDEX issues_repo_kind_state_idx
66 ON issues (repo_id, kind, state);
67 CREATE INDEX issues_author_idx ON issues (author_user_id);
68
69 CREATE TRIGGER set_updated_at BEFORE UPDATE ON issues
70 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
71
72
73 CREATE TABLE issue_comments (
74 id bigserial PRIMARY KEY,
75 issue_id bigint NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
76 author_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
77 body text NOT NULL,
78 body_html_cached text,
79 md_pipeline_version int NOT NULL DEFAULT 1,
80 created_at timestamptz NOT NULL DEFAULT now(),
81 updated_at timestamptz NOT NULL DEFAULT now(),
82 edited_at timestamptz,
83
84 CONSTRAINT issue_comments_body_length CHECK (char_length(body) BETWEEN 1 AND 65535)
85 );
86
87 CREATE INDEX issue_comments_issue_idx ON issue_comments (issue_id, created_at);
88
89 CREATE TRIGGER set_updated_at BEFORE UPDATE ON issue_comments
90 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
91
92
93 CREATE TABLE issue_assignees (
94 issue_id bigint NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
95 user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
96 assigned_at timestamptz NOT NULL DEFAULT now(),
97 assigned_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
98
99 PRIMARY KEY (issue_id, user_id)
100 );
101
102 CREATE INDEX issue_assignees_user_idx ON issue_assignees (user_id);
103
104
105 CREATE TABLE labels (
106 id bigserial PRIMARY KEY,
107 repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
108 name citext NOT NULL,
109 color text NOT NULL DEFAULT 'd0d7de',
110 description text NOT NULL DEFAULT '',
111 created_at timestamptz NOT NULL DEFAULT now(),
112
113 CONSTRAINT labels_name_length CHECK (char_length(name::text) BETWEEN 1 AND 50),
114 CONSTRAINT labels_color_format CHECK (color ~ '^[0-9a-fA-F]{6}$'),
115
116 UNIQUE (repo_id, name)
117 );
118
119
120 CREATE TABLE issue_labels (
121 issue_id bigint NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
122 label_id bigint NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
123 applied_at timestamptz NOT NULL DEFAULT now(),
124 applied_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
125
126 PRIMARY KEY (issue_id, label_id)
127 );
128
129 CREATE INDEX issue_labels_label_idx ON issue_labels (label_id);
130
131
132 CREATE TABLE milestones (
133 id bigserial PRIMARY KEY,
134 repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
135 title text NOT NULL,
136 description text NOT NULL DEFAULT '',
137 state milestone_state NOT NULL DEFAULT 'open',
138 due_on timestamptz,
139 created_at timestamptz NOT NULL DEFAULT now(),
140 closed_at timestamptz,
141
142 CONSTRAINT milestones_title_length CHECK (char_length(title) BETWEEN 1 AND 200),
143
144 UNIQUE (repo_id, title)
145 );
146
147 ALTER TABLE issues ADD CONSTRAINT issues_milestone_fk
148 FOREIGN KEY (milestone_id) REFERENCES milestones(id) ON DELETE SET NULL;
149
150
151 CREATE TABLE issue_events (
152 id bigserial PRIMARY KEY,
153 issue_id bigint NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
154 actor_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
155 kind text NOT NULL,
156 meta jsonb NOT NULL DEFAULT '{}'::jsonb,
157 ref_target_id bigint, -- denormalized when the event references another issue
158 created_at timestamptz NOT NULL DEFAULT now()
159 );
160
161 CREATE INDEX issue_events_issue_idx ON issue_events (issue_id, created_at);
162
163
164 CREATE TABLE issue_references (
165 id bigserial PRIMARY KEY,
166 source_issue_id bigint REFERENCES issues(id) ON DELETE SET NULL,
167 target_issue_id bigint NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
168 source_kind issue_ref_source NOT NULL,
169 source_object_id bigint, -- comment_id when source_kind=comment_body; issue_id when issue_body; push_event_id when commit_message
170 created_at timestamptz NOT NULL DEFAULT now()
171 );
172
173 CREATE INDEX issue_references_target_idx ON issue_references (target_issue_id, created_at);
174 CREATE INDEX issue_references_source_idx ON issue_references (source_issue_id, source_kind);
175
176
177 -- +goose Down
178 DROP TABLE IF EXISTS issue_references;
179 DROP TABLE IF EXISTS issue_events;
180 ALTER TABLE issues DROP CONSTRAINT IF EXISTS issues_milestone_fk;
181 DROP TABLE IF EXISTS milestones;
182 DROP TABLE IF EXISTS issue_labels;
183 DROP TABLE IF EXISTS labels;
184 DROP TABLE IF EXISTS issue_assignees;
185 DROP TABLE IF EXISTS issue_comments;
186 DROP TABLE IF EXISTS issues;
187 DROP TABLE IF EXISTS repo_issue_counter;
188 DROP TYPE IF EXISTS issue_ref_source;
189 DROP TYPE IF EXISTS milestone_state;
190 DROP TYPE IF EXISTS issue_state_reason;
191 DROP TYPE IF EXISTS issue_state;
192 DROP TYPE IF EXISTS issue_kind;
193