internal/store/migrations/0020_teams.up.sql

 1-- Teams scope repository access inside an organization. The pre-teams
 2-- model (every member writes every org repo) survives as the default via
 3-- orgs.members_role = 'write'; large orgs set it to 'read' or 'none' and
 4-- grant through teams instead.
 5ALTER TABLE orgs ADD COLUMN members_role TEXT NOT NULL DEFAULT 'write'
 6    CHECK (members_role IN ('write', 'read', 'none'));
 7
 8CREATE TABLE teams (
 9    id     INTEGER PRIMARY KEY,
10    org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
11    name   TEXT NOT NULL,
12    UNIQUE (org_id, name)
13);
14CREATE TABLE team_members (
15    team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
16    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
17    PRIMARY KEY (team_id, user_id)
18);
19CREATE TABLE team_repos (
20    team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
21    repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
22    role    TEXT NOT NULL CHECK (role IN ('read', 'write', 'admin')),
23    PRIMARY KEY (team_id, repo_id)
24);
25CREATE INDEX team_repos_repo ON team_repos(repo_id);