shithub Public

Watch 1 Fork 0 Star 0
MySQL · 4423 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S31 — Teams within organizations.
4 --
5 -- One level of nesting is intentional and structurally enforced via
6 -- two CHECKs:
7 -- - parent_team_id != id (no self-parent)
8 -- - if parent_team_id IS NOT NULL then the parent's parent_team_id
9 -- is NULL (validated via a trigger; can't express in a row CHECK)
10 --
11 -- team_repo_access is the per-team repo grant; the per-user grant
12 -- continues to live in `repo_collaborators` (S15). Both contribute to
13 -- the policy aggregator's max-of-sources rule.
14
15 -- +goose Up
16
17 CREATE TYPE team_privacy AS ENUM ('visible', 'secret');
18 CREATE TYPE team_role AS ENUM ('member', 'maintainer');
19 CREATE TYPE team_repo_role AS ENUM ('read', 'triage', 'write', 'maintain', 'admin');
20
21 CREATE TABLE teams (
22 id bigserial PRIMARY KEY,
23 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
24 slug citext NOT NULL,
25 display_name text NOT NULL DEFAULT '',
26 description text NOT NULL DEFAULT '',
27 parent_team_id bigint REFERENCES teams(id) ON DELETE SET NULL,
28 privacy team_privacy NOT NULL DEFAULT 'visible',
29 created_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
30 created_at timestamptz NOT NULL DEFAULT now(),
31 updated_at timestamptz NOT NULL DEFAULT now(),
32
33 CONSTRAINT teams_slug_length CHECK (char_length(slug::text) BETWEEN 1 AND 50),
34 CONSTRAINT teams_no_self_parent CHECK (parent_team_id IS NULL OR parent_team_id <> id),
35 CONSTRAINT teams_org_slug_unique UNIQUE (org_id, slug)
36 );
37
38 CREATE INDEX teams_org_idx ON teams (org_id);
39 CREATE INDEX teams_parent_idx ON teams (parent_team_id) WHERE parent_team_id IS NOT NULL;
40
41 CREATE TRIGGER set_updated_at BEFORE UPDATE ON teams
42 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
43
44 -- One-level-deep nesting enforcement: a team's parent must itself
45 -- have a NULL parent. Implemented as a trigger because the rule
46 -- spans rows.
47 -- +goose StatementBegin
48 CREATE OR REPLACE FUNCTION tg_teams_one_level_nesting() RETURNS trigger AS $$
49 DECLARE
50 grandparent_id bigint;
51 BEGIN
52 IF NEW.parent_team_id IS NULL THEN
53 RETURN NEW;
54 END IF;
55 SELECT parent_team_id INTO grandparent_id FROM teams WHERE id = NEW.parent_team_id;
56 IF grandparent_id IS NOT NULL THEN
57 RAISE EXCEPTION 'teams: nesting limited to one level (parent already has a parent)'
58 USING ERRCODE = '23514';
59 END IF;
60 RETURN NEW;
61 END;
62 $$ LANGUAGE plpgsql;
63 -- +goose StatementEnd
64
65 CREATE TRIGGER tg_teams_one_level_nesting_iu
66 BEFORE INSERT OR UPDATE ON teams
67 FOR EACH ROW EXECUTE FUNCTION tg_teams_one_level_nesting();
68
69 -- ─── team_members ─────────────────────────────────────────────────
70 CREATE TABLE team_members (
71 team_id bigint NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
72 user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
73 role team_role NOT NULL DEFAULT 'member',
74 added_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
75 added_at timestamptz NOT NULL DEFAULT now(),
76
77 PRIMARY KEY (team_id, user_id)
78 );
79
80 CREATE INDEX team_members_user_idx ON team_members (user_id);
81
82 -- ─── team_repo_access ─────────────────────────────────────────────
83 CREATE TABLE team_repo_access (
84 team_id bigint NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
85 repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
86 role team_repo_role NOT NULL DEFAULT 'read',
87 added_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
88 added_at timestamptz NOT NULL DEFAULT now(),
89
90 PRIMARY KEY (team_id, repo_id)
91 );
92
93 CREATE INDEX team_repo_access_repo_idx ON team_repo_access (repo_id);
94
95 -- +goose Down
96 DROP TABLE IF EXISTS team_repo_access;
97 DROP TABLE IF EXISTS team_members;
98 DROP TRIGGER IF EXISTS tg_teams_one_level_nesting_iu ON teams;
99 DROP FUNCTION IF EXISTS tg_teams_one_level_nesting();
100 DROP TABLE IF EXISTS teams;
101 DROP TYPE IF EXISTS team_repo_role;
102 DROP TYPE IF EXISTS team_role;
103 DROP TYPE IF EXISTS team_privacy;
104