hearthforge

hearthforge/src/db/schema.sql
schema.sql
⎇
Raw
1CREATE TABLE IF NOT EXISTS users (
2 id INTEGER PRIMARY KEY AUTOINCREMENT,
3 username TEXT UNIQUE NOT NULL,
4 password_hash TEXT,
5 created_at TEXT NOT NULL,
6 avatar_version INTEGER NOT NULL DEFAULT 1,
7 git_name TEXT,
8 git_email TEXT,
9 is_pending INTEGER NOT NULL DEFAULT 0,
10 register_application TEXT
11);
12
13CREATE TABLE IF NOT EXISTS passkeys (
14 id INTEGER PRIMARY KEY AUTOINCREMENT,
15 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16 credential_id TEXT UNIQUE NOT NULL,
17 public_key TEXT NOT NULL,
18 counter INTEGER NOT NULL DEFAULT 0,
19 created_at TEXT NOT NULL
20);
21
22CREATE TABLE IF NOT EXISTS sessions (
23 id TEXT PRIMARY KEY,
24 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
25 expires_at TEXT NOT NULL,
26 created_at TEXT NOT NULL
27);
28
29CREATE TABLE IF NOT EXISTS repositories (
30 id INTEGER PRIMARY KEY AUTOINCREMENT,
31 name TEXT UNIQUE NOT NULL,
32 description TEXT,
33 is_private INTEGER NOT NULL DEFAULT 0,
34 is_pinned INTEGER NOT NULL DEFAULT 0,
35 default_branch TEXT NOT NULL DEFAULT 'main',
36 created_at TEXT NOT NULL,
37 issue_seq INTEGER NOT NULL DEFAULT 0,
38 patch_seq INTEGER NOT NULL DEFAULT 0,
39 issue_template TEXT,
40 patch_template TEXT,
41 allow_user_labels INTEGER NOT NULL DEFAULT 0
42);
43
44CREATE TABLE IF NOT EXISTS issues (
45 id INTEGER PRIMARY KEY AUTOINCREMENT,
46 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
47 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
48 number INTEGER NOT NULL,
49 title TEXT NOT NULL,
50 body TEXT NOT NULL DEFAULT '',
51 status TEXT NOT NULL DEFAULT 'open',
52 created_at TEXT NOT NULL,
53 updated_at TEXT NOT NULL,
54 edited_at TEXT,
55 UNIQUE(repo_id, number)
56);
57
58CREATE TABLE IF NOT EXISTS issue_comments (
59 id INTEGER PRIMARY KEY AUTOINCREMENT,
60 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
61 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
62 body TEXT NOT NULL,
63 created_at TEXT NOT NULL,
64 edited_at TEXT
65);
66
67CREATE TABLE IF NOT EXISTS issue_reactions (
68 id INTEGER PRIMARY KEY AUTOINCREMENT,
69 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
70 comment_id INTEGER REFERENCES issue_comments(id) ON DELETE CASCADE,
71 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
72 emoji TEXT NOT NULL,
73 UNIQUE(issue_id, comment_id, user_id)
74);
75
76CREATE TABLE IF NOT EXISTS patches (
77 id INTEGER PRIMARY KEY AUTOINCREMENT,
78 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
79 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
80 number INTEGER NOT NULL,
81 title TEXT NOT NULL,
82 description TEXT NOT NULL DEFAULT '',
83 patch_content TEXT NOT NULL,
84 status TEXT NOT NULL DEFAULT 'open',
85 author_name TEXT NOT NULL DEFAULT '',
86 author_email TEXT NOT NULL DEFAULT '',
87 created_at TEXT NOT NULL,
88 updated_at TEXT NOT NULL,
89 edited_at TEXT,
90 version TEXT NOT NULL DEFAULT '',
91 UNIQUE(repo_id, number)
92);
93
94CREATE TABLE IF NOT EXISTS patch_comments (
95 id INTEGER PRIMARY KEY AUTOINCREMENT,
96 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
97 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
98 body TEXT NOT NULL,
99 created_at TEXT NOT NULL,
100 edited_at TEXT
101);
102
103CREATE TABLE IF NOT EXISTS patch_reactions (
104 id INTEGER PRIMARY KEY AUTOINCREMENT,
105 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
106 comment_id INTEGER REFERENCES patch_comments(id) ON DELETE CASCADE,
107 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
108 emoji TEXT NOT NULL,
109 UNIQUE(patch_id, comment_id, user_id)
110);
111
112CREATE TABLE IF NOT EXISTS ssh_keys (
113 id INTEGER PRIMARY KEY AUTOINCREMENT,
114 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
115 name TEXT NOT NULL,
116 public_key TEXT NOT NULL,
117 fingerprint TEXT NOT NULL UNIQUE,
118 created_at TEXT NOT NULL
119);
120
121CREATE TABLE IF NOT EXISTS releases (
122 id INTEGER PRIMARY KEY AUTOINCREMENT,
123 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
124 tag_name TEXT,
125 name TEXT NOT NULL,
126 notes TEXT,
127 include_source_code INTEGER NOT NULL DEFAULT 0,
128 created_at TEXT NOT NULL,
129 UNIQUE(repo_id, tag_name)
130);
131
132CREATE TABLE IF NOT EXISTS release_assets (
133 id INTEGER PRIMARY KEY AUTOINCREMENT,
134 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
135 filename TEXT NOT NULL,
136 size INTEGER NOT NULL,
137 created_at TEXT NOT NULL
138);
139
140CREATE TABLE IF NOT EXISTS labels (
141 id INTEGER PRIMARY KEY AUTOINCREMENT,
142 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
143 name TEXT NOT NULL,
144 color TEXT NOT NULL DEFAULT '#808080',
145 created_at TEXT NOT NULL,
146 UNIQUE(repo_id, name)
147);
148
149CREATE TABLE IF NOT EXISTS issue_labels (
150 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
151 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
152 PRIMARY KEY (issue_id, label_id)
153);
154
155CREATE TABLE IF NOT EXISTS patch_labels (
156 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
157 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
158 PRIMARY KEY (patch_id, label_id)
159);
160
161CREATE TABLE IF NOT EXISTS ci_runs (
162 id INTEGER PRIMARY KEY AUTOINCREMENT,
163 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
164 triggered_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
165 trigger_source TEXT NOT NULL,
166 commit_sha TEXT,
167 commit_branch TEXT,
168 commit_tag TEXT,
169 status TEXT NOT NULL DEFAULT 'pending',
170 variable_overrides TEXT,
171 started_at TEXT,
172 finished_at TEXT,
173 created_at TEXT NOT NULL DEFAULT (datetime('now'))
174);
175
176CREATE TABLE IF NOT EXISTS ci_steps (
177 id INTEGER PRIMARY KEY AUTOINCREMENT,
178 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
179 name TEXT NOT NULL,
180 status TEXT NOT NULL DEFAULT 'pending',
181 exit_code INTEGER,
182 started_at TEXT,
183 finished_at TEXT,
184 log TEXT NOT NULL DEFAULT ''
185);
186
187CREATE TABLE IF NOT EXISTS ci_artifacts (
188 id INTEGER PRIMARY KEY AUTOINCREMENT,
189 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
190 filename TEXT NOT NULL,
191 size INTEGER NOT NULL,
192 created_at TEXT NOT NULL DEFAULT (datetime('now'))
193);
194
195CREATE TABLE IF NOT EXISTS ci_secrets (
196 id INTEGER PRIMARY KEY AUTOINCREMENT,
197 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
198 name TEXT NOT NULL,
199 value TEXT NOT NULL,
200 description TEXT,
201 created_at TEXT NOT NULL DEFAULT (datetime('now')),
202 UNIQUE(repo_id, name)
203);
204