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 repo_run_id: number | null;
171}
172
173interface CiStepTable {
174 id: Generated<number>;
175 run_id: number;
176 name: string;
177 status: string;
178 exit_code: number | null;
179 started_at: string | null;
180 finished_at: string | null;
181 log: Generated<string>;
182}
183
184interface CiArtifactTable {
185 id: Generated<number>;
186 run_id: number;
187 filename: string;
188 size: number;
189 created_at: Generated<string>;
190}
191
192interface CiSecretTable {
193 id: Generated<number>;
194 repo_id: number;
195 name: string;
196 value: string;
197 description: string | null;
198 created_at: Generated<string>;
199}
200
201// Per-repo monotonic counter for the human-facing run number (#1, #2, …).
202// Incremented atomically on each trigger so numbers never collide or repeat
203// after history pruning — unlike deriving the number from a live row count.
204interface CiRunCounterTable {
205 repo_id: number;
206 last_run_id: number;
207}
208
209export interface Database {
210 users: UserTable;
211 passkeys: PasskeyTable;
212 sessions: SessionTable;
213 repositories: RepositoryTable;
214 issues: IssueTable;
215 issue_comments: IssueCommentTable;
216 issue_reactions: IssueReactionTable;
217 patches: PatchTable;
218 patch_comments: PatchCommentTable;
219 patch_reactions: PatchReactionTable;
220 ssh_keys: SshKeyTable;
221 releases: ReleaseTable;
222 release_assets: ReleaseAssetTable;
223 labels: LabelTable;
224 issue_labels: IssueLabelTable;
225 patch_labels: PatchLabelTable;
226 ci_runs: CiRunTable;
227 ci_steps: CiStepTable;
228 ci_artifacts: CiArtifactTable;
229 ci_secrets: CiSecretTable;
230 ci_run_counters: CiRunCounterTable;
231}
232
233// Selectable row types (id is plain number, as returned by queries)
234export type UserRow = Selectable<UserTable>;
235export type PasskeyRow = Selectable<PasskeyTable>;
236export type SessionRow = Selectable<SessionTable>;
237export type RepositoryRow = Selectable<RepositoryTable>;
238export type IssueRow = Selectable<IssueTable>;
239export type IssueCommentRow = Selectable<IssueCommentTable>;
240export type IssueReactionRow = Selectable<IssueReactionTable>;
241export type PatchRow = Selectable<PatchTable>;
242export type PatchCommentRow = Selectable<PatchCommentTable>;
243export type PatchReactionRow = Selectable<PatchReactionTable>;
244export type SshKeyRow = Selectable<SshKeyTable>;
245export type ReleaseRow = Selectable<ReleaseTable>;
246export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
247export type LabelRow = Selectable<LabelTable>;
248export type CiRunRow = Selectable<CiRunTable>;
249export type CiStepRow = Selectable<CiStepTable>;
250export type CiArtifactRow = Selectable<CiArtifactTable>;
251export type CiSecretRow = Selectable<CiSecretTable>;
252
253let sqlite = new BunDatabase(paths.DB_PATH);
254sqlite.run("PRAGMA journal_mode=WAL");
255sqlite.run("PRAGMA foreign_keys=ON");
256
257// Migration: add is_pending and register_application columns to users if missing
258const userCols = sqlite
259 .query<{ name: string }, []>("PRAGMA table_info(users)")
260 .all();
261if (!userCols.some((c) => c.name === "is_pending")) {
262 sqlite.run(
263 "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
264 );
265}
266if (!userCols.some((c) => c.name === "register_application")) {
267 sqlite.run("ALTER TABLE users ADD COLUMN register_application TEXT");
268}
269
270// Migration: add version column if missing, then populate any empty values
271const patchCols = sqlite
272 .query<{ name: string }, []>("PRAGMA table_info(patches)")
273 .all();
274if (!patchCols.some((c) => c.name === "version")) {
275 sqlite.run(
276 "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
277 );
278}
279sqlite.run(
280 "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
281);
282
283export let db = new Kysely<Database>({
284 dialect: new BunSqliteDialect({ database: sqlite }),
285});
286
287function runMigrations(s: InstanceType<typeof BunDatabase>) {
288 const ciRunCols = s
289 .query<{ name: string }, []>("PRAGMA table_info(ci_runs)")
290 .all();
291 if (!ciRunCols.some((c) => c.name === "repo_run_id")) {
292 s.run("ALTER TABLE ci_runs ADD COLUMN repo_run_id INTEGER");
293 }
294}
295
296// NOTE: runMigrations() alters ci_runs, so it must run *after* the
297// `CREATE TABLE IF NOT EXISTS ci_runs` block below — otherwise upgrading a
298// database created before the CI tables existed would ALTER a missing table
299// and throw at import. The call is intentionally placed at the end of the
300// migration section, not here.
301
302/** Close the current DB and reopen from disk (used by tests after data wipe). */
303export function resetDb() {
304 try {
305 sqlite.close();
306 } catch {}
307 sqlite = new BunDatabase(paths.DB_PATH);
308 sqlite.run("PRAGMA journal_mode=WAL");
309 sqlite.run("PRAGMA foreign_keys=ON");
310 runMigrations(sqlite);
311 db = new Kysely<Database>({
312 dialect: new BunSqliteDialect({ database: sqlite }),
313 });
314}
315
316// Migration: create CI tables if missing
317sqlite.run(`CREATE TABLE IF NOT EXISTS ci_runs (
318 id INTEGER PRIMARY KEY AUTOINCREMENT,
319 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
320 triggered_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
321 trigger_source TEXT NOT NULL,
322 commit_sha TEXT,
323 commit_branch TEXT,
324 commit_tag TEXT,
325 status TEXT NOT NULL DEFAULT 'pending',
326 variable_overrides TEXT,
327 started_at TEXT,
328 finished_at TEXT,
329 created_at TEXT NOT NULL DEFAULT (datetime('now'))
330)`);
331sqlite.run(`CREATE TABLE IF NOT EXISTS ci_steps (
332 id INTEGER PRIMARY KEY AUTOINCREMENT,
333 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
334 name TEXT NOT NULL,
335 status TEXT NOT NULL DEFAULT 'pending',
336 exit_code INTEGER,
337 started_at TEXT,
338 finished_at TEXT,
339 log TEXT NOT NULL DEFAULT ''
340)`);
341sqlite.run(`CREATE TABLE IF NOT EXISTS ci_artifacts (
342 id INTEGER PRIMARY KEY AUTOINCREMENT,
343 run_id INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
344 filename TEXT NOT NULL,
345 size INTEGER NOT NULL,
346 created_at TEXT NOT NULL DEFAULT (datetime('now'))
347)`);
348sqlite.run(`CREATE TABLE IF NOT EXISTS ci_secrets (
349 id INTEGER PRIMARY KEY AUTOINCREMENT,
350 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
351 name TEXT NOT NULL,
352 value TEXT NOT NULL,
353 description TEXT,
354 created_at TEXT NOT NULL DEFAULT (datetime('now')),
355 UNIQUE(repo_id, name)
356)`);
357sqlite.run(`CREATE TABLE IF NOT EXISTS ci_run_counters (
358 repo_id INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,
359 last_run_id INTEGER NOT NULL DEFAULT 0
360)`);
361
362// Run column-level migrations now that the CI tables are guaranteed to exist
363// (see the note above runMigrations).
364runMigrations(sqlite);
365
366// Migration: add allow_user_labels column to repositories if missing
367const repoCols = sqlite
368 .query<{ name: string }, []>("PRAGMA table_info(repositories)")
369 .all();
370if (!repoCols.some((c) => c.name === "allow_user_labels")) {
371 sqlite.run(
372 "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
373 );
374}
375
376// Migration: create labels tables if missing
377sqlite.run(`CREATE TABLE IF NOT EXISTS labels (
378 id INTEGER PRIMARY KEY AUTOINCREMENT,
379 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
380 name TEXT NOT NULL,
381 color TEXT NOT NULL DEFAULT '#808080',
382 created_at TEXT NOT NULL,
383 UNIQUE(repo_id, name)
384)`);
385sqlite.run(`CREATE TABLE IF NOT EXISTS issue_labels (
386 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
387 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
388 PRIMARY KEY (issue_id, label_id)
389)`);
390sqlite.run(`CREATE TABLE IF NOT EXISTS patch_labels (
391 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
392 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
393 PRIMARY KEY (patch_id, label_id)
394)`);
395
396// Indexes for common query patterns (safe to run repeatedly)
397sqlite.run("CREATE INDEX IF NOT EXISTS idx_issues_repo_id ON issues(repo_id)");
398sqlite.run(
399 "CREATE INDEX IF NOT EXISTS idx_issues_author_id ON issues(author_id)",
400);
401sqlite.run(
402 "CREATE INDEX IF NOT EXISTS idx_patches_repo_id ON patches(repo_id)",
403);
404sqlite.run(
405 "CREATE INDEX IF NOT EXISTS idx_patches_author_id ON patches(author_id)",
406);
407sqlite.run(
408 "CREATE INDEX IF NOT EXISTS idx_ci_runs_repo_id ON ci_runs(repo_id)",
409);
410sqlite.run(
411 "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)",
412);
413sqlite.run(
414 "CREATE INDEX IF NOT EXISTS idx_ssh_keys_user_id ON ssh_keys(user_id)",
415);
416sqlite.run(
417 "CREATE INDEX IF NOT EXISTS idx_issue_labels_label_id ON issue_labels(label_id)",
418);
419sqlite.run(
420 "CREATE INDEX IF NOT EXISTS idx_patch_labels_label_id ON patch_labels(label_id)",
421);
422sqlite.run("CREATE INDEX IF NOT EXISTS idx_labels_repo_id ON labels(repo_id)");
423sqlite.run(
424 "CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at)",
425);
426sqlite.run(
427 "CREATE INDEX IF NOT EXISTS idx_issues_repo_status ON issues(repo_id, status)",
428);
429sqlite.run(
430 "CREATE INDEX IF NOT EXISTS idx_patches_repo_status ON patches(repo_id, status)",
431);
432
433// Clean up expired sessions on startup
434sqlite.run("DELETE FROM sessions WHERE expires_at < datetime('now')");
435
436export async function getRepo(name: string, isAdmin: boolean) {
437 const repo = await db
438 .selectFrom("repositories")
439 .selectAll()
440 .where("name", "=", name)
441 .executeTakeFirst();
442 if (!repo) return null;
443 if (repo.is_private && !isAdmin) return null;
444 return repo;
445}
446