Openhub
Sign in Sign up
samjeston / openhub Public
main
Switch branches/tags
Branches
main
Sam Jeston Working vertical 9aa3c45 · History
90 lines· 3.4 KB ·SQL
Raw
1CREATE TABLE users (
2 id INTEGER PRIMARY KEY,
3 username TEXT NOT NULL UNIQUE COLLATE NOCASE,
4 email TEXT NOT NULL UNIQUE COLLATE NOCASE,
5 display_name TEXT NOT NULL DEFAULT '',
6 bio TEXT NOT NULL DEFAULT '',
7 password_hash TEXT NOT NULL,
8 is_admin INTEGER NOT NULL DEFAULT 0,
9 created_at INTEGER NOT NULL
10);
11
12CREATE TABLE sessions (
13 token_hash TEXT PRIMARY KEY,
14 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
15 created_at INTEGER NOT NULL,
16 expires_at INTEGER NOT NULL
17);
18CREATE INDEX sessions_user ON sessions(user_id);
19
20CREATE TABLE ssh_keys (
21 id INTEGER PRIMARY KEY,
22 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
23 title TEXT NOT NULL,
24 fingerprint TEXT NOT NULL UNIQUE,
25 public_key TEXT NOT NULL,
26 key_type TEXT NOT NULL,
27 created_at INTEGER NOT NULL,
28 last_used_at INTEGER
29);
30CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
31
32-- Bare repositories live on disk at <data>/repos/<id>.git, so renames only touch this table.
33CREATE TABLE repositories (
34 id INTEGER PRIMARY KEY,
35 owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
36 name TEXT NOT NULL COLLATE NOCASE,
37 description TEXT NOT NULL DEFAULT '',
38 is_private INTEGER NOT NULL DEFAULT 0,
39 default_branch TEXT NOT NULL DEFAULT 'main',
40 created_at INTEGER NOT NULL,
41 updated_at INTEGER NOT NULL,
42 pushed_at INTEGER,
43 UNIQUE (owner_id, name)
44);
45
46CREATE TABLE collaborators (
47 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
48 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
49 created_at INTEGER NOT NULL,
50 PRIMARY KEY (repo_id, user_id)
51);
52CREATE INDEX collaborators_user ON collaborators(user_id);
53
54CREATE TABLE pull_requests (
55 id INTEGER PRIMARY KEY,
56 repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
57 number INTEGER NOT NULL,
58 title TEXT NOT NULL,
59 body TEXT NOT NULL DEFAULT '',
60 author_id INTEGER NOT NULL REFERENCES users(id),
61 base_branch TEXT NOT NULL,
62 head_branch TEXT NOT NULL,
63 -- 'open' | 'closed' | 'merged'
64 state TEXT NOT NULL DEFAULT 'open',
65 -- Last known tip of the head branch (also kept at refs/pull/<number>/head).
66 head_sha TEXT,
67 -- Merge base of head and base. Tracked while open, frozen once closed or merged.
68 base_sha TEXT,
69 merge_commit_sha TEXT,
70 merged_by INTEGER REFERENCES users(id),
71 merged_at INTEGER,
72 closed_at INTEGER,
73 created_at INTEGER NOT NULL,
74 updated_at INTEGER NOT NULL,
75 UNIQUE (repo_id, number)
76);
77CREATE INDEX pull_requests_repo_state ON pull_requests(repo_id, state);
78
79-- Conversation timeline: comments plus state changes.
80CREATE TABLE pr_events (
81 id INTEGER PRIMARY KEY,
82 pr_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
83 -- NULL when Openhub detected the change itself (e.g. merged by a plain push).
84 author_id INTEGER REFERENCES users(id),
85 -- 'comment' | 'closed' | 'reopened' | 'merged' | 'branch_deleted'
86 kind TEXT NOT NULL,
87 body TEXT NOT NULL DEFAULT '',
88 created_at INTEGER NOT NULL
89);
90CREATE INDEX pr_events_pr ON pr_events(pr_id);