pr_01m47d24b0e6n91zwymwxg0vpx/services/work/migrations/0001_init.sql
| 1 | -- Issues, pull requests, comments and sessions. |
| 2 | -- Every timestamp is RFC 3339 UTC text. |
| 3 | |
| 4 | -- Issues and pull requests share one sequence of numbers per repository. |
| 5 | CREATE TABLE counters ( |
| 6 | repo_id TEXT PRIMARY KEY, |
| 7 | last INTEGER NOT NULL |
| 8 | ); |
| 9 | |
| 10 | CREATE TABLE issues ( |
| 11 | id TEXT PRIMARY KEY, |
| 12 | repo_id TEXT NOT NULL, |
| 13 | number INTEGER NOT NULL, |
| 14 | title TEXT NOT NULL, |
| 15 | body TEXT NOT NULL, |
| 16 | -- JSON array of label names. |
| 17 | labels TEXT NOT NULL DEFAULT '[]', |
| 18 | -- JSON array of commands. |
| 19 | checks TEXT NOT NULL DEFAULT '[]', |
| 20 | state TEXT NOT NULL DEFAULT 'open', |
| 21 | -- Why it was closed: completed or not_planned. |
| 22 | reason TEXT, |
| 23 | -- The number of the pull request whose merge closed it. |
| 24 | resolved_by INTEGER, |
| 25 | author_id TEXT NOT NULL, |
| 26 | author_name TEXT NOT NULL, |
| 27 | created_at TEXT NOT NULL, |
| 28 | updated_at TEXT NOT NULL, |
| 29 | closed_at TEXT, |
| 30 | UNIQUE (repo_id, number) |
| 31 | ); |
| 32 | CREATE INDEX issues_by_state ON issues (repo_id, state, number); |
| 33 | |
| 34 | CREATE TABLE pulls ( |
| 35 | id TEXT PRIMARY KEY, |
| 36 | repo_id TEXT NOT NULL, |
| 37 | number INTEGER NOT NULL, |
| 38 | -- The issue it is for, if any, and that issue's number. |
| 39 | issue_id TEXT REFERENCES issues (id), |
| 40 | issue_number INTEGER, |
| 41 | title TEXT NOT NULL, |
| 42 | body TEXT, |
| 43 | agent TEXT NOT NULL, |
| 44 | runtime TEXT NOT NULL, |
| 45 | status TEXT NOT NULL DEFAULT 'draft', |
| 46 | -- Where the change is: a fork made for the pull request, or a branch of |
| 47 | -- the repository itself. |
| 48 | fork_repo_id TEXT UNIQUE, |
| 49 | fork_namespace TEXT, |
| 50 | fork_name TEXT, |
| 51 | source_branch TEXT, |
| 52 | head_commit TEXT, |
| 53 | -- What the branch pointed to before a merged pull request landed. |
| 54 | merge_base TEXT, |
| 55 | merged_by TEXT, |
| 56 | merged_at TEXT, |
| 57 | -- The pull request merged instead of this one. |
| 58 | superseded_by INTEGER, |
| 59 | author_id TEXT NOT NULL, |
| 60 | author_name TEXT NOT NULL, |
| 61 | created_at TEXT NOT NULL, |
| 62 | updated_at TEXT NOT NULL, |
| 63 | UNIQUE (repo_id, number) |
| 64 | ); |
| 65 | CREATE INDEX pulls_by_status ON pulls (repo_id, status, number); |
| 66 | CREATE INDEX pulls_by_issue ON pulls (issue_id); |
| 67 | CREATE INDEX pulls_by_author ON pulls (author_id, status); |
| 68 | |
| 69 | -- On an issue or a pull request: the two share numbers. |
| 70 | CREATE TABLE comments ( |
| 71 | id TEXT PRIMARY KEY, |
| 72 | repo_id TEXT NOT NULL, |
| 73 | number INTEGER NOT NULL, |
| 74 | author_id TEXT NOT NULL, |
| 75 | author_name TEXT NOT NULL, |
| 76 | body TEXT NOT NULL, |
| 77 | created_at TEXT NOT NULL |
| 78 | ); |
| 79 | CREATE INDEX comments_by_subject ON comments (repo_id, number, id); |
| 80 | |
| 81 | CREATE TABLE session_entries ( |
| 82 | pull_id TEXT NOT NULL REFERENCES pulls (id), |
| 83 | seq INTEGER NOT NULL, |
| 84 | kind TEXT NOT NULL, |
| 85 | text TEXT NOT NULL, |
| 86 | tool TEXT, |
| 87 | -- The fork's head when the entry was recorded: links reasoning to code. |
| 88 | "commit" TEXT, |
| 89 | at TEXT NOT NULL, |
| 90 | PRIMARY KEY (pull_id, seq) |
| 91 | ); |