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