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 content_type: string;
137 created_at: string;
138}
139
140interface LabelTable {
141 id: Generated<number>;
142 repo_id: number;
143 name: string;
144 color: string;
145 created_at: string;
146}
147
148interface IssueLabelTable {
149 issue_id: number;
150 label_id: number;
151}
152
153interface PatchLabelTable {
154 patch_id: number;
155 label_id: number;
156}
157
158export interface Database {
159 users: UserTable;
160 passkeys: PasskeyTable;
161 sessions: SessionTable;
162 repositories: RepositoryTable;
163 issues: IssueTable;
164 issue_comments: IssueCommentTable;
165 issue_reactions: IssueReactionTable;
166 patches: PatchTable;
167 patch_comments: PatchCommentTable;
168 patch_reactions: PatchReactionTable;
169 ssh_keys: SshKeyTable;
170 releases: ReleaseTable;
171 release_assets: ReleaseAssetTable;
172 labels: LabelTable;
173 issue_labels: IssueLabelTable;
174 patch_labels: PatchLabelTable;
175}
176
177// Selectable row types (id is plain number, as returned by queries)
178export type UserRow = Selectable<UserTable>;
179export type PasskeyRow = Selectable<PasskeyTable>;
180export type SessionRow = Selectable<SessionTable>;
181export type RepositoryRow = Selectable<RepositoryTable>;
182export type IssueRow = Selectable<IssueTable>;
183export type IssueCommentRow = Selectable<IssueCommentTable>;
184export type IssueReactionRow = Selectable<IssueReactionTable>;
185export type PatchRow = Selectable<PatchTable>;
186export type PatchCommentRow = Selectable<PatchCommentTable>;
187export type PatchReactionRow = Selectable<PatchReactionTable>;
188export type SshKeyRow = Selectable<SshKeyTable>;
189export type ReleaseRow = Selectable<ReleaseTable>;
190export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
191export type LabelRow = Selectable<LabelTable>;
192
193let sqlite = new BunDatabase(paths.DB_PATH);
194sqlite.run("PRAGMA journal_mode=WAL");
195sqlite.run("PRAGMA foreign_keys=ON");
196
197// Migration: add is_pending and register_application columns to users if missing
198const userCols = sqlite
199 .query<{ name: string }, []>("PRAGMA table_info(users)")
200 .all();
201if (!userCols.some((c) => c.name === "is_pending")) {
202 sqlite.run(
203 "ALTER TABLE users ADD COLUMN is_pending INTEGER NOT NULL DEFAULT 0",
204 );
205}
206if (!userCols.some((c) => c.name === "register_application")) {
207 sqlite.run("ALTER TABLE users ADD COLUMN register_application TEXT");
208}
209
210// Migration: add version column if missing, then populate any empty values
211const patchCols = sqlite
212 .query<{ name: string }, []>("PRAGMA table_info(patches)")
213 .all();
214if (!patchCols.some((c) => c.name === "version")) {
215 sqlite.run(
216 "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
217 );
218}
219sqlite.run(
220 "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
221);
222
223export let db = new Kysely<Database>({
224 dialect: new BunSqliteDialect({ database: sqlite }),
225});
226
227/** Close the current DB and reopen from disk (used by tests after data wipe). */
228export function resetDb() {
229 try {
230 sqlite.close();
231 } catch {}
232 sqlite = new BunDatabase(paths.DB_PATH);
233 sqlite.run("PRAGMA journal_mode=WAL");
234 sqlite.run("PRAGMA foreign_keys=ON");
235 db = new Kysely<Database>({
236 dialect: new BunSqliteDialect({ database: sqlite }),
237 });
238}
239
240// Migration: add allow_user_labels column to repositories if missing
241const repoCols = sqlite
242 .query<{ name: string }, []>("PRAGMA table_info(repositories)")
243 .all();
244if (!repoCols.some((c) => c.name === "allow_user_labels")) {
245 sqlite.run(
246 "ALTER TABLE repositories ADD COLUMN allow_user_labels INTEGER NOT NULL DEFAULT 0",
247 );
248}
249
250// Migration: create labels tables if missing
251sqlite.run(`CREATE TABLE IF NOT EXISTS labels (
252 id INTEGER PRIMARY KEY AUTOINCREMENT,
253 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
254 name TEXT NOT NULL,
255 color TEXT NOT NULL DEFAULT '#808080',
256 created_at TEXT NOT NULL,
257 UNIQUE(repo_id, name)
258)`);
259sqlite.run(`CREATE TABLE IF NOT EXISTS issue_labels (
260 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
261 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
262 PRIMARY KEY (issue_id, label_id)
263)`);
264sqlite.run(`CREATE TABLE IF NOT EXISTS patch_labels (
265 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
266 label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
267 PRIMARY KEY (patch_id, label_id)
268)`);
269
270export async function getRepo(name: string, isAdmin: boolean) {
271 const repo = await db
272 .selectFrom("repositories")
273 .selectAll()
274 .where("name", "=", name)
275 .executeTakeFirst();
276 if (!repo) return null;
277 if (repo.is_private && !isAdmin) return null;
278 return repo;
279}
280