1CREATE TABLE IF NOT EXISTS settings (
 2    id SERIAL PRIMARY KEY,
 3    key TEXT NOT NULL UNIQUE,
 4    value TEXT NOT NULL,
 5    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 6    updated_at TIMESTAMP NOT NULL
 7);
 8
 9CREATE TABLE IF NOT EXISTS users (
10    id SERIAL PRIMARY KEY,
11    username TEXT NOT NULL UNIQUE,
12    admin BOOLEAN NOT NULL,
13    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
14    updated_at TIMESTAMP NOT NULL
15);
16
17CREATE TABLE IF NOT EXISTS public_keys (
18    id SERIAL PRIMARY KEY,
19    user_id INTEGER NOT NULL,
20    public_key TEXT NOT NULL UNIQUE,
21    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
22    updated_at TIMESTAMP NOT NULL,
23    UNIQUE (user_id, public_key),
24    CONSTRAINT user_id_fk
25    FOREIGN KEY(user_id) REFERENCES users(id)
26    ON DELETE CASCADE
27    ON UPDATE CASCADE
28);
29
30CREATE TABLE IF NOT EXISTS repos (
31    id SERIAL PRIMARY KEY,
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 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
39    updated_at TIMESTAMP NOT NULL
40);
41
42CREATE TABLE IF NOT EXISTS collabs (
43    id SERIAL PRIMARY KEY,
44    user_id INTEGER NOT NULL,
45    repo_id INTEGER NOT NULL,
46    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
47    updated_at TIMESTAMP NOT NULL,
48    UNIQUE (user_id, repo_id),
49    CONSTRAINT user_id_fk
50    FOREIGN KEY(user_id) REFERENCES users(id)
51    ON DELETE CASCADE
52    ON UPDATE CASCADE,
53    CONSTRAINT repo_id_fk
54    FOREIGN KEY(repo_id) REFERENCES repos(id)
55    ON DELETE CASCADE
56    ON UPDATE CASCADE
57);
58
59