hearthforge

hearthforge/src/db/schema.sql
schema.sql
⎇
Raw
1CREATE TABLE IF NOT EXISTS users (
2 id INTEGER PRIMARY KEY AUTOINCREMENT,
3 username TEXT UNIQUE NOT NULL,
4 password_hash TEXT,
5 created_at TEXT NOT NULL,
6 avatar_version INTEGER NOT NULL DEFAULT 1
7);
8
9CREATE TABLE IF NOT EXISTS passkeys (
10 id INTEGER PRIMARY KEY AUTOINCREMENT,
11 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
12 credential_id TEXT UNIQUE NOT NULL,
13 public_key TEXT NOT NULL,
14 counter INTEGER NOT NULL DEFAULT 0,
15 created_at TEXT NOT NULL
16);
17
18CREATE TABLE IF NOT EXISTS sessions (
19 id TEXT PRIMARY KEY,
20 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
21 expires_at TEXT NOT NULL,
22 created_at TEXT NOT NULL
23);
24
25CREATE TABLE IF NOT EXISTS repositories (
26 id INTEGER PRIMARY KEY AUTOINCREMENT,
27 name TEXT UNIQUE NOT NULL,
28 description TEXT,
29 is_private INTEGER NOT NULL DEFAULT 0,
30 default_branch TEXT NOT NULL DEFAULT 'main',
31 created_at TEXT NOT NULL,
32 issue_seq INTEGER NOT NULL DEFAULT 0,
33 patch_seq INTEGER NOT NULL DEFAULT 0
34);
35
36CREATE TABLE IF NOT EXISTS issues (
37 id INTEGER PRIMARY KEY AUTOINCREMENT,
38 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
39 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
40 number INTEGER NOT NULL,
41 title TEXT NOT NULL,
42 body TEXT NOT NULL DEFAULT '',
43 status TEXT NOT NULL DEFAULT 'open',
44 created_at TEXT NOT NULL,
45 updated_at TEXT NOT NULL,
46 edited_at TEXT,
47 UNIQUE(repo_id, number)
48);
49
50CREATE TABLE IF NOT EXISTS issue_comments (
51 id INTEGER PRIMARY KEY AUTOINCREMENT,
52 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
53 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
54 body TEXT NOT NULL,
55 created_at TEXT NOT NULL,
56 edited_at TEXT
57);
58
59CREATE TABLE IF NOT EXISTS issue_reactions (
60 id INTEGER PRIMARY KEY AUTOINCREMENT,
61 issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
62 comment_id INTEGER REFERENCES issue_comments(id) ON DELETE CASCADE,
63 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
64 emoji TEXT NOT NULL,
65 UNIQUE(issue_id, comment_id, user_id)
66);
67
68CREATE TABLE IF NOT EXISTS patches (
69 id INTEGER PRIMARY KEY AUTOINCREMENT,
70 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
71 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
72 number INTEGER NOT NULL,
73 title TEXT NOT NULL,
74 description TEXT NOT NULL DEFAULT '',
75 patch_content TEXT NOT NULL,
76 status TEXT NOT NULL DEFAULT 'open',
77 created_at TEXT NOT NULL,
78 updated_at TEXT NOT NULL,
79 edited_at TEXT,
80 UNIQUE(repo_id, number)
81);
82
83CREATE TABLE IF NOT EXISTS patch_comments (
84 id INTEGER PRIMARY KEY AUTOINCREMENT,
85 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
86 author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
87 body TEXT NOT NULL,
88 created_at TEXT NOT NULL,
89 edited_at TEXT
90);
91
92CREATE TABLE IF NOT EXISTS patch_reactions (
93 id INTEGER PRIMARY KEY AUTOINCREMENT,
94 patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
95 comment_id INTEGER REFERENCES patch_comments(id) ON DELETE CASCADE,
96 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
97 emoji TEXT NOT NULL,
98 UNIQUE(patch_id, comment_id, user_id)
99);
100
101CREATE TABLE IF NOT EXISTS ssh_keys (
102 id INTEGER PRIMARY KEY AUTOINCREMENT,
103 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
104 name TEXT NOT NULL,
105 public_key TEXT NOT NULL,
106 fingerprint TEXT NOT NULL UNIQUE,
107 created_at TEXT NOT NULL
108);
109
110CREATE TABLE IF NOT EXISTS releases (
111 id INTEGER PRIMARY KEY AUTOINCREMENT,
112 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
113 tag_name TEXT NOT NULL,
114 name TEXT,
115 notes TEXT,
116 commit_hash TEXT NOT NULL,
117 include_source_code INTEGER NOT NULL DEFAULT 0,
118 created_at TEXT NOT NULL,
119 UNIQUE(repo_id, tag_name)
120);
121
122CREATE TABLE IF NOT EXISTS release_assets (
123 id INTEGER PRIMARY KEY AUTOINCREMENT,
124 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
125 filename TEXT NOT NULL,
126 size INTEGER NOT NULL,
127 content_type TEXT NOT NULL,
128 created_at TEXT NOT NULL
129);
130