hearthforge

hearthforge/src/db/index.ts
index.ts
⎇
Raw
1import { Database as BunDatabase } from "bun:sqlite";
2import { mkdirSync, readFileSync } from "node:fs";
3import path from "node:path";
4import { type Generated, Kysely, type Selectable } from "kysely";
5import { BunSqliteDialect } from "kysely-bun-sqlite";
6
7import { paths } from "../constants.ts";
8
9interface UserTable {
10 id: Generated<number>;
11 username: string;
12 password_hash: string | null;
13 created_at: string;
14 avatar_version: Generated<number>;
15 is_pending: Generated<number>;
16 register_application: string | null;
17}
18
19interface PasskeyTable {
20 id: Generated<number>;
21 user_id: number;
22 credential_id: string;
23 public_key: string;
24 counter: number;
25 created_at: string;
26}
27
28interface SessionTable {
29 id: string;
30 user_id: number;
31 expires_at: string;
32 created_at: string;
33}
34
35interface RepositoryTable {
36 id: Generated<number>;
37 name: string;
38 description: string | null;
39 is_private: number;
40 is_pinned: Generated<number>;
41 default_branch: string;
42 created_at: string;
43 issue_seq: Generated<number>;
44 patch_seq: Generated<number>;
45 issue_template: string | null;
46 patch_template: string | null;
47 allow_user_labels: Generated<number>;
48}
49
50interface IssueTable {
51 id: Generated<number>;
52 repo_id: number;
53 author_id: number | null;
54 number: number;
55 title: string;
56 body: string;
57 status: string;
58 created_at: string;
59 updated_at: string;
60 edited_at: string | null;
61}
62
63interface IssueCommentTable {
64 id: Generated<number>;
65 issue_id: number;
66 author_id: number | null;
67 body: string;
68 created_at: string;
69 edited_at: string | null;
70}
71
72interface IssueReactionTable {
73 id: Generated<number>;
74 issue_id: number;
75 comment_id: number | null;
76 user_id: number;
77 emoji: string;
78}
79
80interface PatchTable {
81 id: Generated<number>;
82 repo_id: number;
83 author_id: number | null;
84 number: number;
85 title: string;
86 description: string;
87 patch_content: string;
88 status: string;
89 author_name: string;
90 author_email: string;
91 created_at: string;
92 updated_at: string;
93 edited_at: string | null;
94 version: string;
95}
96
97interface PatchCommentTable {
98 id: Generated<number>;
99 patch_id: number;
100 author_id: number | null;
101 body: string;
102 created_at: string;
103 edited_at: string | null;
104}
105
106interface PatchReactionTable {
107 id: Generated<number>;
108 patch_id: number;
109 comment_id: number | null;
110 user_id: number;
111 emoji: string;
112}
113
114interface SshKeyTable {
115 id: Generated<number>;
116 user_id: number;
117 name: string;
118 public_key: string;
119 fingerprint: string;
120 created_at: string;
121}
122
123interface ReleaseTable {
124 id: Generated<number>;
125 repo_id: number;
126 tag_name: string | null;
127 name: string;
128 notes: string | null;
129 include_source_code: number;
130 created_at: string;
131}
132
133interface ReleaseAssetTable {
134 id: Generated<number>;
135 release_id: number;
136 filename: string;
137 size: number;
138 created_at: string;
139}
140
141interface LabelTable {
142 id: Generated<number>;
143 repo_id: number;
144 name: string;
145 color: string;
146 created_at: string;
147}
148
149interface IssueLabelTable {
150 issue_id: number;
151 label_id: number;
152}
153
154interface PatchLabelTable {
155 patch_id: number;
156 label_id: number;
157}
158
159interface CiRunTable {
160 id: Generated<number>;
161 repo_id: number;
162 triggered_by: number | null;
163 trigger_source: string;
164 commit_sha: string | null;
165 commit_branch: string | null;
166 commit_tag: string | null;
167 status: string;
168 variable_overrides: string | null;
169 started_at: string | null;
170 finished_at: string | null;
171 created_at: Generated<string>;
172 repo_run_id: number | null;
173}
174
175interface CiStepTable {
176 id: Generated<number>;
177 run_id: number;
178 name: string;
179 status: string;
180 exit_code: number | null;
181 started_at: string | null;
182 finished_at: string | null;
183 log: Generated<string>;
184}
185
186interface CiArtifactTable {
187 id: Generated<number>;
188 run_id: number;
189 filename: string;
190 size: number;
191 created_at: Generated<string>;
192}
193
194interface CiSecretTable {
195 id: Generated<number>;
196 repo_id: number;
197 name: string;
198 value: string;
199 description: string | null;
200 created_at: Generated<string>;
201}
202
203// Per-repo monotonic counter for the human-facing run number (#1, #2, …).
204// Incremented atomically on each trigger so numbers never collide or repeat
205// after history pruning — unlike deriving the number from a live row count.
206interface CiRunCounterTable {
207 repo_id: number;
208 last_run_id: number;
209}
210
211export interface Database {
212 users: UserTable;
213 passkeys: PasskeyTable;
214 sessions: SessionTable;
215 repositories: RepositoryTable;
216 issues: IssueTable;
217 issue_comments: IssueCommentTable;
218 issue_reactions: IssueReactionTable;
219 patches: PatchTable;
220 patch_comments: PatchCommentTable;
221 patch_reactions: PatchReactionTable;
222 ssh_keys: SshKeyTable;
223 releases: ReleaseTable;
224 release_assets: ReleaseAssetTable;
225 labels: LabelTable;
226 issue_labels: IssueLabelTable;
227 patch_labels: PatchLabelTable;
228 ci_runs: CiRunTable;
229 ci_steps: CiStepTable;
230 ci_artifacts: CiArtifactTable;
231 ci_secrets: CiSecretTable;
232 ci_run_counters: CiRunCounterTable;
233}
234
235// Selectable row types (id is plain number, as returned by queries)
236export type RepositoryRow = Selectable<RepositoryTable>;
237export type IssueRow = Selectable<IssueTable>;
238export type IssueCommentRow = Selectable<IssueCommentTable>;
239export type PatchRow = Selectable<PatchTable>;
240export type PatchCommentRow = Selectable<PatchCommentTable>;
241export type ReleaseRow = Selectable<ReleaseTable>;
242export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
243export type LabelRow = Selectable<LabelTable>;
244export type CiStepRow = Selectable<CiStepTable>;
245export type CiArtifactRow = Selectable<CiArtifactTable>;
246
247const SCHEMA = readFileSync(path.join(import.meta.dir, "schema.sql"), "utf-8");
248
249/**
250 * Bring a database file up to date.
251 *
252 * `schema.sql` is the single source of truth: every statement in it is
253 * `IF NOT EXISTS`, so running it on an existing database only fills in
254 * whatever is missing. Column-level changes cannot be expressed that way,
255 * so they follow as explicit `ALTER TABLE` steps — and they run after the
256 * schema, which guarantees the table they alter exists.
257 */
258function migrate(s: InstanceType<typeof BunDatabase>) {
259 s.run("PRAGMA journal_mode=WAL");
260 s.run("PRAGMA foreign_keys=ON");
261 s.run(SCHEMA);
262
263 const hasColumn = (table: string, column: string) =>
264 s
265 .query<{ name: string }, []>(`PRAGMA table_info(${table})`)
266 .all()
267 .some((c) => c.name === column);
268
269 if (!hasColumn("users", "is_pending")) {
270 s.run(
271 "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
272 );
273 }
274 if (!hasColumn("users", "register_application")) {
275 s.run("ALTER TABLE users ADD COLUMN register_application TEXT");
276 }
277 if (!hasColumn("patches", "version")) {
278 s.run(
279 "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
280 );
281 }
282 if (!hasColumn("repositories", "allow_user_labels")) {
283 s.run(
284 "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
285 );
286 }
287 if (!hasColumn("ci_runs", "repo_run_id")) {
288 s.run("ALTER TABLE ci_runs ADD COLUMN repo_run_id INTEGER");
289 }
290
291 // Backfill patch versions left empty by the column migration above.
292 s.run(
293 "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
294 );
295 // Expired sessions serve no purpose after a restart.
296 s.run("DELETE FROM sessions WHERE expires_at < datetime('now')");
297}
298
299/**
300 * Open the database, creating its directory first — SQLite reports a bare
301 * SQLITE_CANTOPEN if the parent directory is missing, which is what a fresh
302 * DATA_DIR looks like before db:init has ever run.
303 */
304function openDb(): InstanceType<typeof BunDatabase> {
305 mkdirSync(path.dirname(paths.DB_PATH), { recursive: true });
306 const s = new BunDatabase(paths.DB_PATH);
307 migrate(s);
308 return s;
309}
310
311let sqlite = openDb();
312
313export let db = new Kysely<Database>({
314 dialect: new BunSqliteDialect({ database: sqlite }),
315});
316
317/** Close the current DB and reopen from disk (used by tests after data wipe). */
318export function resetDb() {
319 try {
320 sqlite.close();
321 } catch {}
322 sqlite = openDb();
323 db = new Kysely<Database>({
324 dialect: new BunSqliteDialect({ database: sqlite }),
325 });
326}
327
328export async function getRepo(name: string, isAdmin: boolean) {
329 const repo = await db
330 .selectFrom("repositories")
331 .selectAll()
332 .where("name", "=", name)
333 .executeTakeFirst();
334 if (!repo) return null;
335 if (repo.is_private && !isAdmin) return null;
336 return repo;
337}
338