004_social.sql · rin/polar

Code Issues Pull requests Releases 9 Activity

004_social.sql · 41 lines · 1.8 KB

1	-- Collaborators: per-repository access grants for other users.
2	CREATE TABLE IF NOT EXISTS collaborators (
3	id INTEGER PRIMARY KEY AUTOINCREMENT,
4	repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
5	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
6	role TEXT NOT NULL DEFAULT 'write',
7	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
8	UNIQUE(repo_id, user_id)
9	);
10	CREATE INDEX IF NOT EXISTS idx_collaborators_user ON collaborators(user_id);
11
12	-- Forks.
13	ALTER TABLE repositories ADD COLUMN forked_from_repo_id INTEGER REFERENCES repositories(id) ON DELETE SET NULL;
14
15	-- Fork-based (cross-repository) pull requests.
16	ALTER TABLE pull_requests ADD COLUMN head_repo_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE;
17
18	-- Pull request reviews (approve / request changes).
19	CREATE TABLE IF NOT EXISTS pull_request_reviews (
20	id INTEGER PRIMARY KEY AUTOINCREMENT,
21	pull_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
22	reviewer_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
23	state TEXT NOT NULL,
24	body TEXT NOT NULL DEFAULT '',
25	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
26	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
27	UNIQUE(pull_id, reviewer_id)
28	);
29
30	-- Device authorization grants (RFC 8628) used by the git credential helper.
31	CREATE TABLE IF NOT EXISTS device_codes (
32	device_code TEXT PRIMARY KEY,
33	user_code TEXT NOT NULL UNIQUE,
34	client_id TEXT NOT NULL DEFAULT '',
35	user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
36	status TEXT NOT NULL DEFAULT 'pending',
37	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
38	expires_at DATETIME NOT NULL,
39	last_polled_at DATETIME
40	);
41	CREATE INDEX IF NOT EXISTS idx_device_codes_expires ON device_codes(expires_at);