-- issues: squashed from 0600_issues_planning.sql, 0601_projects.sql, 0602_discussions.sql.
-- Workstream 6: milestones, issue types, sub-issues, pins, transfers, reactions, attachments.
CREATE TABLE milestones (
id TEXT PRIMARY KEY,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
title TEXT NOT NULL COLLATE NOCASE,
description TEXT NOT NULL DEFAULT '',
due_on INTEGER,
state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE (repo_id, title)
);
CREATE TABLE issue_types (
id TEXT PRIMARY KEY,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
name TEXT NOT NULL COLLATE NOCASE,
color TEXT NOT NULL,
description TEXT,
UNIQUE (repo_id, name)
);
CREATE TABLE pinned_issues (
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
created_at INTEGER NOT NULL,
PRIMARY KEY (repo_id, issue_id)
);
-- Old (repo, number) addresses of transferred issues so links keep working.
CREATE TABLE issue_redirects (
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
number INTEGER NOT NULL,
issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
PRIMARY KEY (repo_id, number)
);
-- subject_type: issue | comment | review_comment | discussion | discussion_comment