1CREATE TABLE IF NOT EXISTS settings (
 2  id INTEGER PRIMARY KEY AUTOINCREMENT,
 3  key TEXT NOT NULL UNIQUE,
 4  value TEXT NOT NULL,
 5  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 6  updated_at DATETIME NOT NULL
 7);
 8
 9CREATE TABLE IF NOT EXISTS user (
10  id INTEGER PRIMARY KEY AUTOINCREMENT,
11  username TEXT UNIQUE,
12  admin BOOLEAN NOT NULL,
13  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
14  updated_at DATETIME NOT NULL
15);
16
17CREATE TABLE IF NOT EXISTS public_key (
18  id INTEGER PRIMARY KEY AUTOINCREMENT,
19  user_id INTEGER NOT NULL,
20  public_key TEXT NOT NULL UNIQUE,
21  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
22  updated_at DATETIME NOT NULL,
23  UNIQUE (user_id, public_key),
24  CONSTRAINT user_id_fk
25    FOREIGN KEY(user_id) REFERENCES user(id)
26    ON DELETE CASCADE
27    ON UPDATE CASCADE
28);
29
30CREATE TABLE IF NOT EXISTS repo (
31  id INTEGER PRIMARY KEY AUTOINCREMENT,
32  name TEXT NOT NULL UNIQUE,
33  project_name TEXT NOT NULL,
34  description TEXT NOT NULL,
35  private BOOLEAN NOT NULL,
36  mirror BOOLEAN NOT NULL,
37  hidden BOOLEAN NOT NULL,
38  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
39  updated_at DATETIME NOT NULL
40);
41
42CREATE TABLE IF NOT EXISTS collab (
43  id INTEGER PRIMARY KEY AUTOINCREMENT,
44  user_id INTEGER NOT NULL,
45  repo_id INTEGER NOT NULL,
46  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
47  updated_at DATETIME NOT NULL,
48  UNIQUE (user_id, repo_id),
49  CONSTRAINT user_id_fk
50    FOREIGN KEY(user_id) REFERENCES user(id)
51    ON DELETE CASCADE
52    ON UPDATE CASCADE,
53  CONSTRAINT repo_id_fk
54    FOREIGN KEY(repo_id) REFERENCES repo(id)
55    ON DELETE CASCADE
56    ON UPDATE CASCADE
57);