mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-05 13:29:44 +02:00
5c42ed1649
The uniqueness check on workspace invitations only filtered by status='pending', not by expires_at. Combined with the partial unique index idx_invitation_unique_pending (also keyed only on status), a past-due pending row permanently blocked re-inviting the same email. Now, before creating a new invitation, the handler flips any past-due pending row for the same (workspace_id, invitee_email) to 'expired', freeing the unique slot. Also tightens GetPendingInvitationByEmail to require expires_at > now(), matching the existing list queries. Closes multica-ai/multica#2055.
63 lines
2.0 KiB
SQL
63 lines
2.0 KiB
SQL
-- name: CreateInvitation :one
|
|
INSERT INTO workspace_invitation (workspace_id, inviter_id, invitee_email, invitee_user_id, role)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
RETURNING *;
|
|
|
|
-- name: GetInvitation :one
|
|
SELECT * FROM workspace_invitation
|
|
WHERE id = $1;
|
|
|
|
-- name: ListPendingInvitationsByWorkspace :many
|
|
SELECT wi.*,
|
|
u.name AS inviter_name,
|
|
u.email AS inviter_email
|
|
FROM workspace_invitation wi
|
|
JOIN "user" u ON u.id = wi.inviter_id
|
|
WHERE wi.workspace_id = $1 AND wi.status = 'pending' AND wi.expires_at > now()
|
|
ORDER BY wi.created_at DESC;
|
|
|
|
-- name: ListPendingInvitationsForUser :many
|
|
SELECT wi.*,
|
|
w.name AS workspace_name,
|
|
u.name AS inviter_name,
|
|
u.email AS inviter_email
|
|
FROM workspace_invitation wi
|
|
JOIN workspace w ON w.id = wi.workspace_id
|
|
JOIN "user" u ON u.id = wi.inviter_id
|
|
WHERE wi.status = 'pending'
|
|
AND (wi.invitee_user_id = $1 OR wi.invitee_email = $2)
|
|
AND wi.expires_at > now()
|
|
ORDER BY wi.created_at DESC;
|
|
|
|
-- name: AcceptInvitation :one
|
|
UPDATE workspace_invitation
|
|
SET status = 'accepted', updated_at = now()
|
|
WHERE id = $1 AND status = 'pending'
|
|
RETURNING *;
|
|
|
|
-- name: DeclineInvitation :one
|
|
UPDATE workspace_invitation
|
|
SET status = 'declined', updated_at = now()
|
|
WHERE id = $1 AND status = 'pending'
|
|
RETURNING *;
|
|
|
|
-- name: RevokeInvitation :exec
|
|
DELETE FROM workspace_invitation
|
|
WHERE id = $1 AND status = 'pending';
|
|
|
|
-- name: GetPendingInvitationByEmail :one
|
|
SELECT * FROM workspace_invitation
|
|
WHERE workspace_id = $1 AND invitee_email = $2 AND status = 'pending' AND expires_at > now();
|
|
|
|
-- name: ExpireStalePendingInvitations :exec
|
|
-- Mark any past-due pending invitations for (workspace_id, invitee_email) as expired,
|
|
-- so the next CreateInvitation does not collide with the partial unique index
|
|
-- idx_invitation_unique_pending (which is WHERE status = 'pending' and cannot
|
|
-- itself reference now() in its predicate).
|
|
UPDATE workspace_invitation
|
|
SET status = 'expired', updated_at = now()
|
|
WHERE workspace_id = $1
|
|
AND invitee_email = $2
|
|
AND status = 'pending'
|
|
AND expires_at <= now();
|