| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- ─── org_invitations ─────────────────────────────────────────────── |
| 4 | |
| 5 | -- name: CreateOrgInvitation :one |
| 6 | INSERT INTO org_invitations ( |
| 7 | org_id, invited_by_user_id, target_user_id, target_email, |
| 8 | role, token_hash, expires_at |
| 9 | ) VALUES ( |
| 10 | $1, $2, sqlc.narg(target_user_id)::bigint, sqlc.narg(target_email)::citext, |
| 11 | $3, $4, $5 |
| 12 | ) |
| 13 | RETURNING *; |
| 14 | |
| 15 | -- name: GetOrgInvitationByTokenHash :one |
| 16 | SELECT * FROM org_invitations WHERE token_hash = $1; |
| 17 | |
| 18 | -- name: GetOrgInvitationByID :one |
| 19 | SELECT * FROM org_invitations WHERE id = $1; |
| 20 | |
| 21 | -- name: ListPendingInvitationsForOrg :many |
| 22 | SELECT i.*, u.username AS target_username, ib.username AS invited_by_username |
| 23 | FROM org_invitations i |
| 24 | LEFT JOIN users u ON u.id = i.target_user_id |
| 25 | LEFT JOIN users ib ON ib.id = i.invited_by_user_id |
| 26 | WHERE i.org_id = $1 |
| 27 | AND i.accepted_at IS NULL |
| 28 | AND i.declined_at IS NULL |
| 29 | AND i.canceled_at IS NULL |
| 30 | AND i.expires_at > now() |
| 31 | ORDER BY i.created_at DESC; |
| 32 | |
| 33 | -- name: ListPendingInvitationsForUser :many |
| 34 | -- Two flavors: by user_id (already-claimed invites) and by email |
| 35 | -- (claim-on-signup). Caller unions the two lookups when surfacing |
| 36 | -- to the user. |
| 37 | SELECT i.*, o.slug AS org_slug, o.display_name AS org_display_name |
| 38 | FROM org_invitations i |
| 39 | JOIN orgs o ON o.id = i.org_id |
| 40 | WHERE i.target_user_id = $1 |
| 41 | AND i.accepted_at IS NULL |
| 42 | AND i.declined_at IS NULL |
| 43 | AND i.canceled_at IS NULL |
| 44 | AND i.expires_at > now() |
| 45 | AND o.deleted_at IS NULL |
| 46 | ORDER BY i.created_at DESC; |
| 47 | |
| 48 | -- name: ListPendingInvitationsForEmail :many |
| 49 | SELECT i.*, o.slug AS org_slug, o.display_name AS org_display_name |
| 50 | FROM org_invitations i |
| 51 | JOIN orgs o ON o.id = i.org_id |
| 52 | WHERE i.target_email = $1 |
| 53 | AND i.accepted_at IS NULL |
| 54 | AND i.declined_at IS NULL |
| 55 | AND i.canceled_at IS NULL |
| 56 | AND i.expires_at > now() |
| 57 | AND o.deleted_at IS NULL |
| 58 | ORDER BY i.created_at DESC; |
| 59 | |
| 60 | -- name: AcceptOrgInvitation :exec |
| 61 | UPDATE org_invitations SET accepted_at = now() WHERE id = $1; |
| 62 | |
| 63 | -- name: DeclineOrgInvitation :exec |
| 64 | UPDATE org_invitations SET declined_at = now() WHERE id = $1; |
| 65 | |
| 66 | -- name: CancelOrgInvitation :exec |
| 67 | UPDATE org_invitations SET canceled_at = now() WHERE id = $1; |
| 68 | |
| 69 | -- name: GetExistingPendingInvitation :one |
| 70 | -- Idempotency check before creating a new invite — so a re-issued |
| 71 | -- invite to the same target doesn't accumulate stale rows. |
| 72 | SELECT * FROM org_invitations |
| 73 | WHERE org_id = $1 |
| 74 | AND ( (target_user_id = sqlc.narg(target_user_id)::bigint) |
| 75 | OR (target_email = sqlc.narg(target_email)::citext) ) |
| 76 | AND accepted_at IS NULL |
| 77 | AND declined_at IS NULL |
| 78 | AND canceled_at IS NULL |
| 79 | AND expires_at > now() |
| 80 | LIMIT 1; |
| 81 |