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