1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
-- SPDX-License-Identifier: AGPL-3.0-only
-- SPDX-FileCopyrightText: Copyright (c) 2025 Runxi Yu <https://runxiyu.org>
CREATE TABLE groups (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
parent_group INTEGER REFERENCES groups(id) ON DELETE CASCADE,
description TEXT,
UNIQUE NULLS NOT DISTINCT (parent_group, name)
);
CREATE TABLE repos (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT, -- I mean, should be CASCADE but deleting Git repos on disk also needs to be considered
contrib_requirements TEXT NOT NULL CHECK (contrib_requirements IN ('closed', 'registered_user', 'federated', 'ssh_pubkey', 'public')),
name TEXT NOT NULL,
UNIQUE(group_id, name),
description TEXT,
filesystem_path TEXT
);
CREATE TABLE mailing_lists (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT,
name TEXT NOT NULL,
UNIQUE(group_id, name),
description TEXT
);
CREATE TABLE mailing_list_emails (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
list_id INTEGER NOT NULL REFERENCES mailing_lists(id) ON DELETE CASCADE,
title TEXT NOT NULL,
sender TEXT NOT NULL,
date TIMESTAMP NOT NULL,
content BYTEA NOT NULL
);
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username TEXT UNIQUE,
type TEXT NOT NULL CHECK (type IN ('pubkey_only', 'federated', 'registered')),
password TEXT
);
CREATE TABLE ssh_public_keys (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
key_string TEXT NOT NULL,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT unique_key_string EXCLUDE USING HASH (key_string WITH =)
);
CREATE TABLE sessions (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
session_id TEXT PRIMARY KEY NOT NULL,
UNIQUE(user_id, session_id)
);
CREATE TABLE user_group_roles (
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY(user_id, group_id)
);
CREATE TABLE federated_identities (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
service TEXT NOT NULL,
remote_username TEXT NOT NULL,
PRIMARY KEY(user_id, service)
);
-- Ticket tracking
CREATE TABLE ticket_trackers (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE RESTRICT,
name TEXT NOT NULL,
description TEXT,
UNIQUE(group_id, name)
);
CREATE TABLE tickets (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tracker_id INTEGER NOT NULL REFERENCES ticket_trackers(id) ON DELETE CASCADE,
tracker_local_id INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT,
UNIQUE(tracker_id, tracker_local_id)
);
CREATE OR REPLACE FUNCTION create_tracker_ticket_sequence()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'tracker_ticket_seq_' || NEW.id;
BEGIN
EXECUTE format('CREATE SEQUENCE %I', seq_name);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_ticket_tracker
AFTER INSERT ON ticket_trackers
FOR EACH ROW
EXECUTE FUNCTION create_tracker_ticket_sequence();
CREATE OR REPLACE FUNCTION drop_tracker_ticket_sequence()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'tracker_ticket_seq_' || OLD.id;
BEGIN
EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_delete_ticket_tracker
BEFORE DELETE ON ticket_trackers
FOR EACH ROW
EXECUTE FUNCTION drop_tracker_ticket_sequence();
CREATE OR REPLACE FUNCTION assign_tracker_local_id()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'tracker_ticket_seq_' || NEW.tracker_id;
BEGIN
IF NEW.tracker_local_id IS NULL THEN
EXECUTE format('SELECT nextval(%L)', seq_name)
INTO NEW.tracker_local_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_insert_ticket
BEFORE INSERT ON tickets
FOR EACH ROW
EXECUTE FUNCTION assign_tracker_local_id();
-- Merge requests
CREATE TABLE merge_requests (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
repo_local_id INTEGER NOT NULL,
title TEXT,
creator INTEGER REFERENCES users(id) ON DELETE SET NULL,
source_ref TEXT NOT NULL,
destination_branch TEXT,
status TEXT NOT NULL CHECK (status IN ('open', 'merged', 'closed')),
UNIQUE (repo_id, repo_local_id),
UNIQUE (repo_id, source_ref, destination_branch)
);
CREATE OR REPLACE FUNCTION create_repo_mr_sequence()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'repo_mr_seq_' || NEW.id;
BEGIN
EXECUTE format('CREATE SEQUENCE %I', seq_name);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_repo
AFTER INSERT ON repos
FOR EACH ROW
EXECUTE FUNCTION create_repo_mr_sequence();
CREATE OR REPLACE FUNCTION drop_repo_mr_sequence()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'repo_mr_seq_' || OLD.id;
BEGIN
EXECUTE format('DROP SEQUENCE IF EXISTS %I', seq_name);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_delete_repo
BEFORE DELETE ON repos
FOR EACH ROW
EXECUTE FUNCTION drop_repo_mr_sequence();
CREATE OR REPLACE FUNCTION assign_repo_local_id()
RETURNS TRIGGER AS $$
DECLARE
seq_name TEXT := 'repo_mr_seq_' || NEW.repo_id;
BEGIN
IF NEW.repo_local_id IS NULL THEN
EXECUTE format('SELECT nextval(%L)', seq_name)
INTO NEW.repo_local_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_insert_merge_request
BEFORE INSERT ON merge_requests
FOR EACH ROW
EXECUTE FUNCTION assign_repo_local_id();
|
