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 { DB_PATH } 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}
14
15interface PasskeyTable {
16 id: Generated<number>;
17 user_id: number;
18 credential_id: string;
19 public_key: string;
20 counter: number;
21 created_at: string;
22}
23
24interface SessionTable {
25 id: string;
26 user_id: number;
27 expires_at: string;
28 created_at: string;
29}
30
31interface RepositoryTable {
32 id: Generated<number>;
33 name: string;
34 description: string | null;
35 is_private: number;
36 is_pinned: Generated<number>;
37 default_branch: string;
38 created_at: string;
39 issue_seq: Generated<number>;
40 patch_seq: Generated<number>;
41 issue_template: string | null;
42 patch_template: string | null;
43}
44
45interface IssueTable {
46 id: Generated<number>;
47 repo_id: number;
48 author_id: number | null;
49 number: number;
50 title: string;
51 body: string;
52 status: string;
53 created_at: string;
54 updated_at: string;
55 edited_at: string | null;
56}
57
58interface IssueCommentTable {
59 id: Generated<number>;
60 issue_id: number;
61 author_id: number | null;
62 body: string;
63 created_at: string;
64 edited_at: string | null;
65}
66
67interface IssueReactionTable {
68 id: Generated<number>;
69 issue_id: number;
70 comment_id: number | null;
71 user_id: number;
72 emoji: string;
73}
74
75interface PatchTable {
76 id: Generated<number>;
77 repo_id: number;
78 author_id: number | null;
79 number: number;
80 title: string;
81 description: string;
82 patch_content: string;
83 status: string;
84 author_name: string;
85 author_email: string;
86 created_at: string;
87 updated_at: string;
88 edited_at: string | null;
89 version: string;
90}
91
92interface PatchCommentTable {
93 id: Generated<number>;
94 patch_id: number;
95 author_id: number | null;
96 body: string;
97 created_at: string;
98 edited_at: string | null;
99}
100
101interface PatchReactionTable {
102 id: Generated<number>;
103 patch_id: number;
104 comment_id: number | null;
105 user_id: number;
106 emoji: string;
107}
108
109interface SshKeyTable {
110 id: Generated<number>;
111 user_id: number;
112 name: string;
113 public_key: string;
114 fingerprint: string;
115 created_at: string;
116}
117
118interface ReleaseTable {
119 id: Generated<number>;
120 repo_id: number;
121 tag_name: string | null;
122 name: string;
123 notes: string | null;
124 include_source_code: number;
125 created_at: string;
126}
127
128interface ReleaseAssetTable {
129 id: Generated<number>;
130 release_id: number;
131 filename: string;
132 size: number;
133 content_type: string;
134 created_at: string;
135}
136
137export interface Database {
138 users: UserTable;
139 passkeys: PasskeyTable;
140 sessions: SessionTable;
141 repositories: RepositoryTable;
142 issues: IssueTable;
143 issue_comments: IssueCommentTable;
144 issue_reactions: IssueReactionTable;
145 patches: PatchTable;
146 patch_comments: PatchCommentTable;
147 patch_reactions: PatchReactionTable;
148 ssh_keys: SshKeyTable;
149 releases: ReleaseTable;
150 release_assets: ReleaseAssetTable;
151}
152
153// Selectable row types (id is plain number, as returned by queries)
154export type UserRow = Selectable<UserTable>;
155export type PasskeyRow = Selectable<PasskeyTable>;
156export type SessionRow = Selectable<SessionTable>;
157export type RepositoryRow = Selectable<RepositoryTable>;
158export type IssueRow = Selectable<IssueTable>;
159export type IssueCommentRow = Selectable<IssueCommentTable>;
160export type IssueReactionRow = Selectable<IssueReactionTable>;
161export type PatchRow = Selectable<PatchTable>;
162export type PatchCommentRow = Selectable<PatchCommentTable>;
163export type PatchReactionRow = Selectable<PatchReactionTable>;
164export type SshKeyRow = Selectable<SshKeyTable>;
165export type ReleaseRow = Selectable<ReleaseTable>;
166export type ReleaseAssetRow = Selectable<ReleaseAssetTable>;
167
168const sqlite = new BunDatabase(DB_PATH);
169sqlite.run("PRAGMA journal_mode=WAL");
170sqlite.run("PRAGMA foreign_keys=ON");
171
172// Migration: add version column if missing, then populate any empty values
173const patchCols = sqlite
174 .query<{ name: string }, []>("PRAGMA table_info(patches)")
175 .all();
176if (!patchCols.some((c) => c.name === "version")) {
177 sqlite.run(
178 "ALTER TABLE patches ADD COLUMN version TEXT NOT NULL DEFAULT ''",
179 );
180}
181sqlite.run(
182 "UPDATE patches SET version = lower(hex(randomblob(16))) WHERE version = ''",
183);
184
185export const db = new Kysely<Database>({
186 dialect: new BunSqliteDialect({ database: sqlite }),
187});
188
189export async function getRepo(name: string, isAdmin: boolean) {
190 const repo = await db
191 .selectFrom("repositories")
192 .selectAll()
193 .where("name", "=", name)
194 .executeTakeFirst();
195 if (!repo) return null;
196 if (repo.is_private && !isAdmin) return null;
197 return repo;
198}
199