krz/gitbay
A CLI-first git forge.
clone: git clone https://gitbay.org/krz/gitbay.git
repo-descriptions: internal/store/migrations/0001_init.up.sql · raw
1CREATE TABLE users (
2 id INTEGER PRIMARY KEY,
3 username TEXT NOT NULL UNIQUE,
4 is_admin INTEGER NOT NULL DEFAULT 0,
5 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
6);
7
8CREATE TABLE emails (
9 id INTEGER PRIMARY KEY,
10 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
11 address TEXT NOT NULL UNIQUE,
12 verified_at TEXT,
13 verified_by TEXT CHECK (verified_by IN ('smtp','admin')),
14 is_primary INTEGER NOT NULL DEFAULT 0,
15 CHECK ((verified_at IS NULL) = (verified_by IS NULL))
16);
17CREATE INDEX emails_user ON emails(user_id);
18
19CREATE TABLE ssh_keys (
20 id INTEGER PRIMARY KEY,
21 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
22 fingerprint TEXT NOT NULL UNIQUE,
23 algo TEXT NOT NULL,
24 blob BLOB NOT NULL,
25 scope TEXT NOT NULL DEFAULT 'full',
26 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
27 last_used_at TEXT
28);
29CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
30
31CREATE TABLE pgp_keys (
32 id INTEGER PRIMARY KEY,
33 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
34 fingerprint TEXT NOT NULL UNIQUE,
35 armored TEXT NOT NULL,
36 uids_json TEXT NOT NULL DEFAULT '[]',
37 expires_at TEXT,
38 revoked_at TEXT,
39 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
40);
41CREATE INDEX pgp_keys_user ON pgp_keys(user_id);
42
43CREATE TABLE orgs (
44 id INTEGER PRIMARY KEY,
45 name TEXT NOT NULL UNIQUE,
46 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
47);
48
49CREATE TABLE org_members (
50 org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
51 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
52 role TEXT NOT NULL CHECK (role IN ('member','admin')),
53 PRIMARY KEY (org_id, user_id)
54);
55
56CREATE TABLE repos (
57 id INTEGER PRIMARY KEY,
58 owner_kind TEXT NOT NULL CHECK (owner_kind IN ('user','org')),
59 owner_id INTEGER NOT NULL,
60 name TEXT NOT NULL,
61 visibility TEXT NOT NULL CHECK (visibility IN ('public','private')),
62 default_branch TEXT NOT NULL DEFAULT 'main',
63 fork_of INTEGER REFERENCES repos(id) ON DELETE SET NULL,
64 issue_counter INTEGER NOT NULL DEFAULT 0,
65 mr_counter INTEGER NOT NULL DEFAULT 0,
66 settings_json TEXT NOT NULL DEFAULT '{}',
67 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
68 UNIQUE (owner_kind, owner_id, name)
69);
70
71CREATE TABLE repo_access (
72 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
73 subject_kind TEXT NOT NULL CHECK (subject_kind IN ('user','org')),
74 subject_id INTEGER NOT NULL,
75 role TEXT NOT NULL CHECK (role IN ('read','write','admin')),
76 PRIMARY KEY (repo_id, subject_kind, subject_id)
77);
78
79CREATE TABLE issues (
80 id INTEGER PRIMARY KEY,
81 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
82 number INTEGER NOT NULL,
83 author_id INTEGER NOT NULL REFERENCES users(id),
84 title TEXT NOT NULL,
85 body TEXT NOT NULL DEFAULT '',
86 state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open','closed')),
87 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
88 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
89 UNIQUE (repo_id, number)
90);
91
92CREATE TABLE issue_comments (
93 id INTEGER PRIMARY KEY,
94 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
95 author_id INTEGER NOT NULL REFERENCES users(id),
96 body TEXT NOT NULL,
97 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
98);
99CREATE INDEX issue_comments_issue ON issue_comments(issue_id);
100
101CREATE TABLE labels (
102 id INTEGER PRIMARY KEY,
103 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
104 name TEXT NOT NULL,
105 color TEXT NOT NULL DEFAULT '',
106 UNIQUE (repo_id, name)
107);
108
109CREATE TABLE issue_labels (
110 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
111 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
112 PRIMARY KEY (issue_id, label_id)
113);
114
115CREATE TABLE issue_assignees (
116 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
117 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
118 PRIMARY KEY (issue_id, user_id)
119);
120
121CREATE TABLE merge_requests (
122 id INTEGER PRIMARY KEY,
123 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
124 number INTEGER NOT NULL,
125 author_id INTEGER NOT NULL REFERENCES users(id),
126 source_repo_id INTEGER REFERENCES repos(id) ON DELETE SET NULL,
127 source_ref TEXT NOT NULL,
128 target_ref TEXT NOT NULL,
129 title TEXT NOT NULL,
130 body TEXT NOT NULL DEFAULT '',
131 state TEXT NOT NULL DEFAULT 'open'
132 CHECK (state IN ('open','merged','closed','source_gone')),
133 head_sha TEXT NOT NULL DEFAULT '',
134 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
135 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
136 UNIQUE (repo_id, number)
137);
138
139CREATE TABLE mr_comments (
140 id INTEGER PRIMARY KEY,
141 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
142 author_id INTEGER NOT NULL REFERENCES users(id),
143 body TEXT NOT NULL,
144 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
145);
146CREATE INDEX mr_comments_mr ON mr_comments(mr_id);
147
148CREATE TABLE mr_reviews (
149 id INTEGER PRIMARY KEY,
150 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
151 reviewer_id INTEGER NOT NULL REFERENCES users(id),
152 verdict TEXT NOT NULL CHECK (verdict IN ('approve','request_changes','comment')),
153 head_sha TEXT NOT NULL,
154 stale INTEGER NOT NULL DEFAULT 0,
155 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
156);
157CREATE INDEX mr_reviews_mr ON mr_reviews(mr_id);
158
159CREATE TABLE commit_signatures (
160 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
161 commit_sha TEXT NOT NULL,
162 state TEXT NOT NULL CHECK (state IN (
163 'verified','signed_unknown_key','signed_email_mismatch',
164 'signed_key_expired','signed_key_revoked',
165 'bad_signature','unsigned')),
166 signer_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
167 key_fingerprint TEXT,
168 key_epoch INTEGER NOT NULL,
169 checked_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
170 PRIMARY KEY (repo_id, commit_sha)
171);
172CREATE INDEX commit_signatures_fpr ON commit_signatures(key_fingerprint);
173
174CREATE TABLE events (
175 id INTEGER PRIMARY KEY,
176 repo_id INTEGER REFERENCES repos(id) ON DELETE CASCADE,
177 actor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
178 kind TEXT NOT NULL,
179 data_json TEXT NOT NULL DEFAULT '{}',
180 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
181);
182CREATE INDEX events_repo ON events(repo_id, id);
183
184CREATE TABLE audit_log (
185 id INTEGER PRIMARY KEY,
186 actor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
187 action TEXT NOT NULL,
188 data_json TEXT NOT NULL DEFAULT '{}',
189 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
190);
191
192CREATE TABLE web_sessions (
193 token_hash TEXT PRIMARY KEY,
194 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
195 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
196 expires_at TEXT NOT NULL
197);
198
199CREATE TABLE invites (
200 code_hash TEXT PRIMARY KEY,
201 email TEXT NOT NULL,
202 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
203 used_at TEXT
204);
205
206CREATE TABLE settings (
207 key TEXT PRIMARY KEY,
208 value TEXT NOT NULL
209);
210INSERT INTO settings (key, value) VALUES ('key_epoch', '1');