| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- ─── teams ───────────────────────────────────────────────────────── |
| 4 | |
| 5 | -- name: CreateTeam :one |
| 6 | INSERT INTO teams (org_id, slug, display_name, description, parent_team_id, privacy, created_by_user_id) |
| 7 | VALUES ($1, $2, $3, $4, sqlc.narg(parent_team_id)::bigint, $5, sqlc.narg(created_by_user_id)::bigint) |
| 8 | RETURNING *; |
| 9 | |
| 10 | -- name: GetTeamByID :one |
| 11 | SELECT * FROM teams WHERE id = $1; |
| 12 | |
| 13 | -- name: GetTeamByOrgAndSlug :one |
| 14 | SELECT * FROM teams WHERE org_id = $1 AND slug = $2; |
| 15 | |
| 16 | -- name: ListTeamsForOrg :many |
| 17 | SELECT * FROM teams WHERE org_id = $1 ORDER BY slug ASC; |
| 18 | |
| 19 | -- name: ListChildTeams :many |
| 20 | SELECT * FROM teams WHERE parent_team_id = $1 ORDER BY slug ASC; |
| 21 | |
| 22 | -- name: UpdateTeamProfile :exec |
| 23 | UPDATE teams SET display_name = $2, description = $3, privacy = $4, updated_at = now() |
| 24 | WHERE id = $1; |
| 25 | |
| 26 | -- name: SetTeamParent :exec |
| 27 | -- The one-level-nesting BEFORE trigger blocks invalid moves; the |
| 28 | -- caller surfaces the SQLSTATE 23514 as a friendly error. |
| 29 | UPDATE teams SET parent_team_id = sqlc.narg(parent_team_id)::bigint, updated_at = now() |
| 30 | WHERE id = $1; |
| 31 | |
| 32 | -- name: DeleteTeam :exec |
| 33 | -- Children's parent_team_id flips to NULL via the FK ON DELETE SET NULL, |
| 34 | -- promoting them to top-level — matches the "deleting parent doesn't |
| 35 | -- delete children" intent in the spec's pitfalls. |
| 36 | DELETE FROM teams WHERE id = $1; |
| 37 | |
| 38 | -- ─── team_members ───────────────────────────────────────────────── |
| 39 | |
| 40 | -- name: AddTeamMember :exec |
| 41 | INSERT INTO team_members (team_id, user_id, role, added_by_user_id) |
| 42 | VALUES ($1, $2, $3, sqlc.narg(added_by_user_id)::bigint) |
| 43 | ON CONFLICT (team_id, user_id) DO NOTHING; |
| 44 | |
| 45 | -- name: SetTeamMemberRole :exec |
| 46 | UPDATE team_members SET role = $3 WHERE team_id = $1 AND user_id = $2; |
| 47 | |
| 48 | -- name: RemoveTeamMember :exec |
| 49 | DELETE FROM team_members WHERE team_id = $1 AND user_id = $2; |
| 50 | |
| 51 | -- name: ListTeamMembers :many |
| 52 | SELECT m.team_id, m.user_id, m.role, m.added_by_user_id, m.added_at, |
| 53 | u.username, u.display_name |
| 54 | FROM team_members m |
| 55 | JOIN users u ON u.id = m.user_id |
| 56 | WHERE m.team_id = $1 AND u.deleted_at IS NULL |
| 57 | ORDER BY m.role ASC, u.username ASC; |
| 58 | |
| 59 | -- name: ListTeamsForUserInOrg :many |
| 60 | -- Returns the teams a user directly belongs to within an org. The |
| 61 | -- policy aggregator unions this with each row's parent_team_id to |
| 62 | -- get the inherited set. |
| 63 | SELECT t.id, t.org_id, t.slug, t.display_name, t.description, |
| 64 | t.parent_team_id, t.privacy, t.created_by_user_id, |
| 65 | t.created_at, t.updated_at, |
| 66 | m.role AS member_role |
| 67 | FROM team_members m |
| 68 | JOIN teams t ON t.id = m.team_id |
| 69 | WHERE t.org_id = $1 AND m.user_id = $2; |
| 70 | |
| 71 | -- ─── team_repo_access ───────────────────────────────────────────── |
| 72 | |
| 73 | -- name: GrantTeamRepoAccess :exec |
| 74 | INSERT INTO team_repo_access (team_id, repo_id, role, added_by_user_id) |
| 75 | VALUES ($1, $2, $3, sqlc.narg(added_by_user_id)::bigint) |
| 76 | ON CONFLICT (team_id, repo_id) DO UPDATE SET role = EXCLUDED.role; |
| 77 | |
| 78 | -- name: RevokeTeamRepoAccess :exec |
| 79 | DELETE FROM team_repo_access WHERE team_id = $1 AND repo_id = $2; |
| 80 | |
| 81 | -- name: GetTeamRepoAccess :one |
| 82 | SELECT * FROM team_repo_access WHERE team_id = $1 AND repo_id = $2; |
| 83 | |
| 84 | -- name: ListTeamRepoAccess :many |
| 85 | -- All repos the team has any grant on; for the team-view page. |
| 86 | SELECT a.team_id, a.repo_id, a.role, a.added_by_user_id, a.added_at, |
| 87 | r.name AS repo_name, r.visibility::text AS visibility |
| 88 | FROM team_repo_access a |
| 89 | JOIN repos r ON r.id = a.repo_id |
| 90 | WHERE a.team_id = $1 AND r.deleted_at IS NULL |
| 91 | ORDER BY r.name ASC; |
| 92 | |
| 93 | -- name: ListRepoTeamGrants :many |
| 94 | -- All teams with grants on a single repo; for repo settings/access page. |
| 95 | SELECT a.team_id, a.repo_id, a.role, a.added_by_user_id, a.added_at, |
| 96 | t.slug AS team_slug, t.display_name AS team_display_name, |
| 97 | t.privacy::text AS team_privacy |
| 98 | FROM team_repo_access a |
| 99 | JOIN teams t ON t.id = a.team_id |
| 100 | WHERE a.repo_id = $1 |
| 101 | ORDER BY t.slug ASC; |
| 102 | |
| 103 | -- name: ListTeamAccessForRepoAndTeams :many |
| 104 | -- Policy hot-path: given a repo and a set of team_ids the actor |
| 105 | -- belongs to (incl. inherited), return the access rows. Caller picks |
| 106 | -- the highest role. |
| 107 | SELECT team_id, repo_id, role |
| 108 | FROM team_repo_access |
| 109 | WHERE repo_id = $1 AND team_id = ANY($2::bigint[]); |
| 110 | |
| 111 | -- name: IsTeamMember :one |
| 112 | -- Replaces the inline EXISTS query in handlers/orgs/teams.go |
| 113 | -- canSeeTeam + filterSecretTeams (SR2 M2). Used by the visibility |
| 114 | -- gate for secret teams. |
| 115 | SELECT EXISTS(SELECT 1 FROM team_members WHERE team_id = $1 AND user_id = $2); |
| 116 |