hearthforge

hearthforge/internal/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 passkey_setup_started_at TEXT
10);
11
12CREATE TABLE IF NOT EXISTS passkeys (
13 id INTEGER PRIMARY KEY AUTOINCREMENT,
14 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
15 credential_id TEXT UNIQUE NOT NULL,
16 public_key TEXT NOT NULL,
17 counter INTEGER NOT NULL DEFAULT 0,
18 created_at TEXT NOT NULL
19);
20
21CREATE TABLE IF NOT EXISTS sessions (
22 id TEXT PRIMARY KEY,
23 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
24 expires_at TEXT NOT NULL,
25 created_at TEXT NOT NULL
26);
27
28CREATE TABLE IF NOT EXISTS repositories (
29 id INTEGER PRIMARY KEY AUTOINCREMENT,
30 name TEXT UNIQUE NOT NULL,
31 description TEXT,
32 is_private INTEGER NOT NULL DEFAULT 0,
33 is_pinned INTEGER NOT NULL DEFAULT 0,
34 default_branch TEXT NOT NULL DEFAULT 'main',
35 created_at TEXT NOT NULL,
36 issue_seq INTEGER NOT NULL DEFAULT 0,
37 patch_seq INTEGER NOT NULL DEFAULT 0,
38 issue_template TEXT,
39 patch_template TEXT,
40 allow_user_labels INTEGER NOT NULL DEFAULT 0
41);
42
43CREATE TABLE IF NOT EXISTS issues (
44 id INTEGER PRIMARY KEY AUTOINCREMENT,
45 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
46 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
47 number INTEGER NOT NULL,
48 title TEXT NOT NULL,
49 body TEXT NOT NULL DEFAULT '',
50 status TEXT NOT NULL DEFAULT 'open',
51 created_at TEXT NOT NULL,
52 updated_at TEXT NOT NULL,
53 edited_at TEXT,
54 UNIQUE(repo_id, number)
55);
56
57CREATE TABLE IF NOT EXISTS issue_comments (
58 id INTEGER PRIMARY KEY AUTOINCREMENT,
59 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
60 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
61 body TEXT NOT NULL,
62 created_at TEXT NOT NULL,
63 edited_at TEXT
64);
65
66CREATE TABLE IF NOT EXISTS issue_reactions (
67 id INTEGER PRIMARY KEY AUTOINCREMENT,
68 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
69 comment_id INTEGER REFERENCES issue_comments(id) ON DELETE CASCADE,
70 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
71 emoji TEXT NOT NULL,
72 UNIQUE(issue_id, comment_id, user_id)
73);
74
75CREATE TABLE IF NOT EXISTS patches (
76 id INTEGER PRIMARY KEY AUTOINCREMENT,
77 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
78 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
79 number INTEGER NOT NULL,
80 title TEXT NOT NULL,
81 description TEXT NOT NULL DEFAULT '',
82 patch_content TEXT NOT NULL,
83 status TEXT NOT NULL DEFAULT 'open',
84 author_name TEXT NOT NULL DEFAULT '',
85 author_email TEXT NOT NULL DEFAULT '',
86 created_at TEXT NOT NULL,
87 updated_at TEXT NOT NULL,
88 edited_at TEXT,
89 version TEXT NOT NULL DEFAULT '',
90 UNIQUE(repo_id, number)
91);
92
93CREATE TABLE IF NOT EXISTS patch_comments (
94 id INTEGER PRIMARY KEY AUTOINCREMENT,
95 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
96 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
97 body TEXT NOT NULL,
98 created_at TEXT NOT NULL,
99 edited_at TEXT
100);
101
102CREATE TABLE IF NOT EXISTS patch_reactions (
103 id INTEGER PRIMARY KEY AUTOINCREMENT,
104 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
105 comment_id INTEGER REFERENCES patch_comments(id) ON DELETE CASCADE,
106 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
107 emoji TEXT NOT NULL,
108 UNIQUE(patch_id, comment_id, user_id)
109);
110
111CREATE TABLE IF NOT EXISTS ssh_keys (
112 id INTEGER PRIMARY KEY AUTOINCREMENT,
113 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
114 name TEXT NOT NULL,
115 public_key TEXT NOT NULL,
116 fingerprint TEXT NOT NULL UNIQUE,
117 created_at TEXT NOT NULL
118);
119
120CREATE TABLE IF NOT EXISTS releases (
121 id INTEGER PRIMARY KEY AUTOINCREMENT,
122 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
123 tag_name TEXT,
124 name TEXT NOT NULL,
125 notes TEXT,
126 include_source_code INTEGER NOT NULL DEFAULT 0,
127 created_at TEXT NOT NULL,
128 UNIQUE(repo_id, tag_name)
129);
130
131CREATE TABLE IF NOT EXISTS release_assets (
132 id INTEGER PRIMARY KEY AUTOINCREMENT,
133 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
134 filename TEXT NOT NULL,
135 size INTEGER NOT NULL,
136 created_at TEXT NOT NULL
137);
138
139CREATE TABLE IF NOT EXISTS labels (
140 id INTEGER PRIMARY KEY AUTOINCREMENT,
141 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
142 name TEXT NOT NULL,
143 color TEXT NOT NULL DEFAULT '#808080',
144 created_at TEXT NOT NULL,
145 UNIQUE(repo_id, name)
146);
147
148CREATE TABLE IF NOT EXISTS issue_labels (
149 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
150 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
151 PRIMARY KEY (issue_id, label_id)
152);
153
154CREATE TABLE IF NOT EXISTS patch_labels (
155 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
156 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
157 PRIMARY KEY (patch_id, label_id)
158);
159
160CREATE TABLE IF NOT EXISTS ci_runs (
161 id INTEGER PRIMARY KEY AUTOINCREMENT,
162 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
163 triggered_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
164 trigger_source TEXT NOT NULL,
165 commit_sha TEXT,
166 commit_branch TEXT,
167 commit_tag TEXT,
168 status TEXT NOT NULL DEFAULT 'pending',
169 variable_overrides TEXT,
170 started_at TEXT,
171 finished_at TEXT,
172 created_at TEXT NOT NULL DEFAULT (datetime('now')),
173 repo_run_id INTEGER
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
205CREATE TABLE IF NOT EXISTS ci_run_counters (
206 repo_id INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,
207 last_run_id INTEGER NOT NULL DEFAULT 0
208);
209
210-- Container registry. Blob and manifest bodies live under DATA_DIR/registry
211-- keyed by digest; these tables hold the per-image index.
212CREATE TABLE IF NOT EXISTS registry_blobs (
213 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
214 image TEXT NOT NULL,
215 digest TEXT NOT NULL,
216 size INTEGER NOT NULL,
217 PRIMARY KEY (repo_id, image, digest)
218);
219
220CREATE TABLE IF NOT EXISTS registry_manifests (
221 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
222 image TEXT NOT NULL,
223 digest TEXT NOT NULL,
224 media_type TEXT NOT NULL,
225 created_at TEXT NOT NULL,
226 PRIMARY KEY (repo_id, image, digest)
227);
228
229CREATE TABLE IF NOT EXISTS registry_tags (
230 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
231 image TEXT NOT NULL,
232 tag TEXT NOT NULL,
233 digest TEXT NOT NULL,
234 updated_at TEXT NOT NULL,
235 PRIMARY KEY (repo_id, image, tag)
236);
237
238-- Indexes for common query patterns
239CREATE INDEX IF NOT EXISTS idx_issues_repo_id ON issues(repo_id);
240CREATE INDEX IF NOT EXISTS idx_issues_author_id ON issues(author_id);
241CREATE INDEX IF NOT EXISTS idx_issues_repo_status ON issues(repo_id, status);
242CREATE INDEX IF NOT EXISTS idx_patches_repo_id ON patches(repo_id);
243CREATE INDEX IF NOT EXISTS idx_patches_author_id ON patches(author_id);
244CREATE INDEX IF NOT EXISTS idx_patches_repo_status ON patches(repo_id, status);
245CREATE INDEX IF NOT EXISTS idx_ci_runs_repo_id ON ci_runs(repo_id);
246CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
247CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
248CREATE INDEX IF NOT EXISTS idx_ssh_keys_user_id ON ssh_keys(user_id);
249CREATE INDEX IF NOT EXISTS idx_labels_repo_id ON labels(repo_id);
250CREATE INDEX IF NOT EXISTS idx_issue_labels_label_id ON issue_labels(label_id);
251CREATE INDEX IF NOT EXISTS idx_patch_labels_label_id ON patch_labels(label_id);
252