| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- ─── orgs ────────────────────────────────────────────────────────── |
| 4 | |
| 5 | -- name: CreateOrg :one |
| 6 | -- Inserts a new org. The trigger on `orgs` populates `principals` for |
| 7 | -- /{slug} resolution; the caller adds the creator as owner in the same |
| 8 | -- tx (separate query so the orchestrator owns ordering). |
| 9 | INSERT INTO orgs (slug, display_name, description, billing_email, created_by_user_id) |
| 10 | VALUES ($1, $2, $3, $4, sqlc.narg(created_by_user_id)::bigint) |
| 11 | RETURNING *; |
| 12 | |
| 13 | -- name: GetOrgByID :one |
| 14 | SELECT * FROM orgs WHERE id = $1; |
| 15 | |
| 16 | -- name: GetOrgBySlug :one |
| 17 | -- Slug is citext so the comparison is case-insensitive. Soft-deleted |
| 18 | -- orgs are filtered out so a slug freed by deletion is invisible to |
| 19 | -- normal lookups (the resolver still sees the row via the deleted_at |
| 20 | -- column for grace-period restore). |
| 21 | SELECT * FROM orgs |
| 22 | WHERE slug = $1 |
| 23 | AND deleted_at IS NULL; |
| 24 | |
| 25 | -- name: GetOrgBySlugIncludingDeleted :one |
| 26 | SELECT * FROM orgs WHERE slug = $1; |
| 27 | |
| 28 | -- name: UpdateOrgProfile :exec |
| 29 | UPDATE orgs |
| 30 | SET display_name = $2, |
| 31 | description = $3, |
| 32 | location = $4, |
| 33 | website = $5, |
| 34 | billing_email = $6, |
| 35 | updated_at = now() |
| 36 | WHERE id = $1; |
| 37 | |
| 38 | -- name: SetOrgAvatarKey :exec |
| 39 | UPDATE orgs SET avatar_object_key = $2, updated_at = now() WHERE id = $1; |
| 40 | |
| 41 | -- name: SetOrgAllowMemberRepoCreate :exec |
| 42 | UPDATE orgs SET allow_member_repo_create = $2, updated_at = now() WHERE id = $1; |
| 43 | |
| 44 | -- name: SetOrgSuspended :exec |
| 45 | UPDATE orgs |
| 46 | SET suspended_at = CASE WHEN $2::boolean THEN now() ELSE NULL END, |
| 47 | suspended_reason = CASE WHEN $2::boolean THEN $3 ELSE NULL END, |
| 48 | updated_at = now() |
| 49 | WHERE id = $1; |
| 50 | |
| 51 | -- name: SoftDeleteOrg :exec |
| 52 | UPDATE orgs SET deleted_at = now(), updated_at = now() WHERE id = $1; |
| 53 | |
| 54 | -- name: RestoreOrg :exec |
| 55 | UPDATE orgs SET deleted_at = NULL, updated_at = now() WHERE id = $1; |
| 56 | |
| 57 | -- name: ListOrgIDsPastSoftDeleteGrace :many |
| 58 | -- Sweep input for the lifecycle worker: every soft-deleted org whose |
| 59 | -- 14-day grace window has elapsed. The interval is intentionally a |
| 60 | -- DB literal (not a parameter) so the policy lives next to the data. |
| 61 | SELECT id FROM orgs |
| 62 | WHERE deleted_at IS NOT NULL |
| 63 | AND deleted_at < (now() - interval '14 days'); |
| 64 | |
| 65 | -- name: ListOrgRepoIDs :many |
| 66 | -- All repo IDs (including soft-deleted) belonging to an org. Used by |
| 67 | -- the org hard-delete cascade to fan out per-repo destruction. |
| 68 | SELECT id FROM repos WHERE owner_org_id = $1; |
| 69 | |
| 70 | -- name: HardDeleteOrgRow :exec |
| 71 | -- Final row removal after the cascade finished. The principals |
| 72 | -- trigger drops the matching principals row in the same tx. |
| 73 | DELETE FROM orgs WHERE id = $1; |
| 74 | |
| 75 | -- ─── principals (read-only from this domain) ─────────────────────── |
| 76 | |
| 77 | -- name: ResolvePrincipal :one |
| 78 | -- Single-query /{slug} resolver. Returns the (kind, id) tuple that |
| 79 | -- /{slug}/* routes use to dispatch to the user-profile or org-profile |
| 80 | -- handler. Both kinds share the slug PK so collisions are |
| 81 | -- impossible at the DB layer. |
| 82 | SELECT slug, kind, id FROM principals WHERE slug = $1; |
| 83 |