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