hearthforge

hearthforge/src/db/index.ts
index.ts
⎇
Raw
1import { Database as BunDatabase } from "bun:sqlite";
2import { type Generated, Kysely, type Selectable } from "kysely";
3import { BunSqliteDialect } from "kysely-bun-sqlite";
4
5import { paths } from "../constants.ts";
6
7interface UserTable {
8 id: Generated<number>;
9 username: string;
10 password_hash: string | null;
11 created_at: string;
12 avatar_version: Generated<number>;
13 is_pending: Generated<number>;
14 register_application: string | null;
15}
16
17interface PasskeyTable {
18 id: Generated<number>;
19 user_id: number;
20 credential_id: string;
21 public_key: string;
22 counter: number;
23 created_at: string;
24}
25
26interface SessionTable {
27 id: string;
28 user_id: number;
29 expires_at: string;
30 created_at: string;
31}
32
33interface RepositoryTable {
34 id: Generated<number>;
35 name: string;
36 description: string | null;
37 is_private: number;
38 is_pinned: Generated<number>;
39 default_branch: string;
40 created_at: string;
41 issue_seq: Generated<number>;
42 patch_seq: Generated<number>;
43 issue_template: string | null;
44 patch_template: string | null;
45 allow_user_labels: Generated<number>;
46}
47
48interface IssueTable {
49 id: Generated<number>;
50 repo_id: number;
51 author_id: number | null;
52 number: number;
53 title: string;
54 body: string;
55 status: string;
56 created_at: string;
57 updated_at: string;
58 edited_at: string | null;
59}
60
61interface IssueCommentTable {
62 id: Generated<number>;
63 issue_id: number;
64 author_id: number | null;
65 body: string;
66 created_at: string;
67 edited_at: string | null;
68}
69
70interface IssueReactionTable {
71 id: Generated<number>;
72 issue_id: number;
73 comment_id: number | null;
74 user_id: number;
75 emoji: string;
76}
77
78interface PatchTable {
79 id: Generated<number>;
80 repo_id: number;
81 author_id: number | null;
82 number: number;
83 title: string;
84 description: string;
85 patch_content: string;
86 status: string;
87 author_name: string;
88 author_email: string;
89 created_at: string;
90 updated_at: string;
91 edited_at: string | null;
92 version: string;
93}
94
95interface PatchCommentTable {
96 id: Generated<number>;
97 patch_id: number;
98 author_id: number | null;
99 body: string;
100 created_at: string;
101 edited_at: string | null;
102}
103
104interface PatchReactionTable {
105 id: Generated<number>;
106 patch_id: number;
107 comment_id: number | null;
108 user_id: number;
109 emoji: string;
110}
111
112interface SshKeyTable {
113 id: Generated<number>;
114 user_id: number;
115 name: string;
116 public_key: string;
117 fingerprint: string;
118 created_at: string;
119}
120
121interface ReleaseTable {
122 id: Generated<number>;
123 repo_id: number;
124 tag_name: string | null;
125 name: string;
126 notes: string | null;
127 include_source_code: number;
128 created_at: string;
129}
130
131interface ReleaseAssetTable {
132 id: Generated<number>;
133 release_id: number;
134 filename: string;
135 size: number;
136 created_at: string;
137}
138
139interface LabelTable {
140 id: Generated<number>;
141 repo_id: number;
142 name: string;
143 color: string;
144 created_at: string;
145}
146
147interface IssueLabelTable {
148 issue_id: number;
149 label_id: number;
150}
151
152interface PatchLabelTable {
153 patch_id: number;
154 label_id: number;
155}
156
157interface CiRunTable {
158 id: Generated<number>;
159 repo_id: number;
160 triggered_by: number | null;
161 trigger_source: string;
162 commit_sha: string | null;
163 commit_branch: string | null;
164 commit_tag: string | null;
165 status: string;
166 variable_overrides: string | null;
167 started_at: string | null;
168 finished_at: string | null;
169 created_at: Generated<string>;
170}
171
172interface CiStepTable {
173 id: Generated<number>;
174 run_id: number;
175 name: string;
176 status: string;
177 exit_code: number | null;
178 started_at: string | null;
179 finished_at: string | null;
180 log: Generated<string>;
181}
182
183interface CiArtifactTable {
184 id: Generated<number>;
185 run_id: number;
186 filename: string;
187 size: number;
188 created_at: Generated<string>;
189}
190
191interface CiSecretTable {
192 id: Generated<number>;
193 repo_id: number;
194 name: string;
195 value: string;
196 description: string | null;
197 created_at: Generated<string>;
198}
199
200export interface Database {
201 users: UserTable;
202 passkeys: PasskeyTable;
203 sessions: SessionTable;
204 repositories: RepositoryTable;
205 issues: IssueTable;
206 issue_comments: IssueCommentTable;
207 issue_reactions: IssueReactionTable;
208 patches: PatchTable;
209 patch_comments: PatchCommentTable;
210 patch_reactions: PatchReactionTable;
211 ssh_keys: SshKeyTable;
212 releases: ReleaseTable;
213 release_assets: ReleaseAssetTable;
214 labels: LabelTable;
215 issue_labels: IssueLabelTable;
216 patch_labels: PatchLabelTable;
217 ci_runs: CiRunTable;
218 ci_steps: CiStepTable;
219 ci_artifacts: CiArtifactTable;
220 ci_secrets: CiSecretTable;
221}
222
223// Selectable row types (id is plain number, as returned by queries)
224export type UserRow = Selectable<UserTable>;
225export type PasskeyRow = Selectable<PasskeyTable>;
226export type SessionRow = Selectable<SessionTable>;
227export type RepositoryRow = Selectable<RepositoryTable>;
228export type IssueRow = Selectable<IssueTable>;
229export type IssueCommentRow = Selectable<IssueCommentTable>;
230export type IssueReactionRow = Selectable<IssueReactionTable>;
231export type PatchRow = Selectable<PatchTable>;
232export type PatchCommentRow = Selectable<PatchCommentTable>;
233export type PatchReactionRow = Selectable<PatchReactionTable>;
234export type SshKeyRow = Selectable<SshKeyTable>;
235export type ReleaseRow = Selectable<ReleaseTable>;
236export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
237export type LabelRow = Selectable<LabelTable>;
238export type CiRunRow = Selectable<CiRunTable>;
239export type CiStepRow = Selectable<CiStepTable>;
240export type CiArtifactRow = Selectable<CiArtifactTable>;
241export type CiSecretRow = Selectable<CiSecretTable>;
242
243let sqlite = new BunDatabase(paths.DB_PATH);
244sqlite.run("PRAGMA journal_mode=WAL");
245sqlite.run("PRAGMA foreign_keys=ON");
246
247// Migration: add is_pending and register_application columns to users if missing
248const userCols = sqlite
249 .query<{ name: string }, []>("PRAGMA table_info(users)")
250 .all();
251if (!userCols.some((c) => c.name === "is_pending")) {
252 sqlite.run(
253 "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
254 );
255}
256if (!userCols.some((c) => c.name === "register_application")) {
257 sqlite.run("ALTER TABLE users ADD COLUMN register_application TEXT");
258}
259
260// Migration: add version column if missing, then populate any empty values
261const patchCols = sqlite
262 .query<{ name: string }, []>("PRAGMA table_info(patches)")
263 .all();
264if (!patchCols.some((c) => c.name === "version")) {
265 sqlite.run(
266 "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
267 );
268}
269sqlite.run(
270 "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
271);
272
273export let db = new Kysely<Database>({
274 dialect: new BunSqliteDialect({ database: sqlite }),
275});
276
277/** Close the current DB and reopen from disk (used by tests after data wipe). */
278export function resetDb() {
279 try {
280 sqlite.close();
281 } catch {}
282 sqlite = new BunDatabase(paths.DB_PATH);
283 sqlite.run("PRAGMA journal_mode=WAL");
284 sqlite.run("PRAGMA foreign_keys=ON");
285 db = new Kysely<Database>({
286 dialect: new BunSqliteDialect({ database: sqlite }),
287 });
288}
289
290// Migration: create CI tables if missing
291sqlite.run(`CREATE TABLE IF NOT EXISTS ci_runs (
292 id INTEGER PRIMARY KEY AUTOINCREMENT,
293 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
294 triggered_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
295 trigger_source TEXT NOT NULL,
296 commit_sha TEXT,
297 commit_branch TEXT,
298 commit_tag TEXT,
299 status TEXT NOT NULL DEFAULT 'pending',
300 variable_overrides TEXT,
301 started_at TEXT,
302 finished_at TEXT,
303 created_at TEXT NOT NULL DEFAULT (datetime('now'))
304)`);
305sqlite.run(`CREATE TABLE IF NOT EXISTS ci_steps (
306 id INTEGER PRIMARY KEY AUTOINCREMENT,
307 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
308 name TEXT NOT NULL,
309 status TEXT NOT NULL DEFAULT 'pending',
310 exit_code INTEGER,
311 started_at TEXT,
312 finished_at TEXT,
313 log TEXT NOT NULL DEFAULT ''
314)`);
315sqlite.run(`CREATE TABLE IF NOT EXISTS ci_artifacts (
316 id INTEGER PRIMARY KEY AUTOINCREMENT,
317 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
318 filename TEXT NOT NULL,
319 size INTEGER NOT NULL,
320 created_at TEXT NOT NULL DEFAULT (datetime('now'))
321)`);
322sqlite.run(`CREATE TABLE IF NOT EXISTS ci_secrets (
323 id INTEGER PRIMARY KEY AUTOINCREMENT,
324 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
325 name TEXT NOT NULL,
326 value TEXT NOT NULL,
327 description TEXT,
328 created_at TEXT NOT NULL DEFAULT (datetime('now')),
329 UNIQUE(repo_id, name)
330)`);
331
332// Migration: add allow_user_labels column to repositories if missing
333const repoCols = sqlite
334 .query<{ name: string }, []>("PRAGMA table_info(repositories)")
335 .all();
336if (!repoCols.some((c) => c.name === "allow_user_labels")) {
337 sqlite.run(
338 "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
339 );
340}
341
342// Migration: create labels tables if missing
343sqlite.run(`CREATE TABLE IF NOT EXISTS labels (
344 id INTEGER PRIMARY KEY AUTOINCREMENT,
345 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
346 name TEXT NOT NULL,
347 color TEXT NOT NULL DEFAULT '#808080',
348 created_at TEXT NOT NULL,
349 UNIQUE(repo_id, name)
350)`);
351sqlite.run(`CREATE TABLE IF NOT EXISTS issue_labels (
352 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
353 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
354 PRIMARY KEY (issue_id, label_id)
355)`);
356sqlite.run(`CREATE TABLE IF NOT EXISTS patch_labels (
357 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
358 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
359 PRIMARY KEY (patch_id, label_id)
360)`);
361
362export async function getRepo(name: string, isAdmin: boolean) {
363 const repo = await db
364 .selectFrom("repositories")
365 .selectAll()
366 .where("name", "=", name)
367 .executeTakeFirst();
368 if (!repo) return null;
369 if (repo.is_private && !isAdmin) return null;
370 return repo;
371}
372