forge
ExploreLog in

Tahinli/forge

public
0 watching
★ 0
3.1 KiB
1-- Milestone 1 schema. Applied by store/migrate.rs, which records the version in
2-- schema_migrations. Timestamps are RFC 3339 UTC strings (see time::now_stamp).
3
4CREATE TABLE users (
5 id TEXT PRIMARY KEY,
6 username TEXT NOT NULL,
7 username_folded TEXT NOT NULL UNIQUE,
8 email TEXT,
9 password_hash TEXT NOT NULL,
10 is_admin INTEGER NOT NULL DEFAULT 0,
11 created_at TEXT NOT NULL
12);
13
14CREATE TABLE sessions (
15 id TEXT PRIMARY KEY,
16 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
17 token_hash TEXT NOT NULL UNIQUE,
18 csrf_token TEXT NOT NULL,
19 created_at TEXT NOT NULL,
20 expires_at TEXT NOT NULL,
21 last_seen TEXT NOT NULL,
22 user_agent TEXT
23);
24CREATE INDEX sessions_user_id ON sessions(user_id);
25CREATE INDEX sessions_expires_at ON sessions(expires_at);
26
27CREATE TABLE ssh_keys (
28 id TEXT PRIMARY KEY,
29 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
30 title TEXT NOT NULL,
31 algorithm TEXT NOT NULL,
32 key_blob BLOB NOT NULL UNIQUE,
33 fingerprint TEXT NOT NULL,
34 created_at TEXT NOT NULL,
35 last_used_at TEXT
36);
37CREATE INDEX ssh_keys_user_id ON ssh_keys(user_id);
38
39CREATE TABLE access_tokens (
40 id TEXT PRIMARY KEY,
41 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
42 name TEXT NOT NULL,
43 token_hash TEXT NOT NULL UNIQUE,
44 scopes TEXT NOT NULL DEFAULT '[]',
45 expires_at TEXT,
46 created_at TEXT NOT NULL,
47 last_used_at TEXT
48);
49CREATE INDEX access_tokens_user_id ON access_tokens(user_id);
50
51CREATE TABLE repos (
52 id TEXT PRIMARY KEY,
53 owner_id TEXT NOT NULL REFERENCES users(id),
54 name TEXT NOT NULL,
55 name_folded TEXT NOT NULL,
56 description TEXT NOT NULL DEFAULT '',
57 visibility TEXT NOT NULL,
58 default_branch TEXT NOT NULL,
59 archived INTEGER NOT NULL DEFAULT 0,
60 created_at TEXT NOT NULL,
61 UNIQUE(owner_id, name_folded)
62);
63
64-- Old owner/name pairs (folded) that now point at a repo, e.g. after a rename.
65CREATE TABLE repo_redirects (
66 owner_folded TEXT NOT NULL,
67 name_folded TEXT NOT NULL,
68 repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
69 created_at TEXT NOT NULL,
70 PRIMARY KEY (owner_folded, name_folded)
71);
72CREATE INDEX repo_redirects_repo_id ON repo_redirects(repo_id);
73
74CREATE TABLE collaborators (
75 repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
76 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
77 role TEXT NOT NULL,
78 PRIMARY KEY (repo_id, user_id)
79);
80CREATE INDEX collaborators_user_id ON collaborators(user_id);
81
82CREATE TABLE push_events (
83 id TEXT PRIMARY KEY,
84 repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
85 actor_id TEXT NOT NULL REFERENCES users(id),
86 ref_name TEXT NOT NULL,
87 old_oid TEXT NOT NULL,
88 new_oid TEXT NOT NULL,
89 protocol TEXT NOT NULL,
90 created_at TEXT NOT NULL
91);
92CREATE INDEX push_events_repo_created ON push_events(repo_id, created_at);