Project — Workspace Invite System

Features to Build

1. Database — `workspace_invites` Table

Schema

Constraints

UNIQUE (workspace_id, email, status)  -- prevents duplicate pending invites for same email
CHECK (role IN ('admin', 'member'))
CHECK (status IN ('pending', 'accepted', 'revoked', 'expired'))

Indexes

CREATE UNIQUE INDEX idx_invites_token ON workspace_invites (token);
CREATE INDEX idx_invites_workspace_status ON workspace_invites (workspace_id, status);
CREATE INDEX idx_invites_email ON workspace_invites (email);

RLS Policies

SELECT:
  — Admins/owners of the workspace can see all invites for their workspace
  — A user can see their own pending invite (WHERE email = auth.email())

INSERT:
  — Only workspace admins/owners can create invites

UPDATE:
  — Only workspace admins/owners can update (for revoke)
  — The Edge Function and RPC use SECURITY DEFINER to bypass RLS

DELETE:
  — Not allowed — use status = 'revoked' instead (soft delete pattern)

Run checklist: New Table · RLS Policies

2. Edge Function: `send-workspace-invite`

Endpoint: POST /functions/v1/send-workspace-invite

Logic (10 steps)

1.  Validate JWT — reject if no valid auth token
2.  Parse body: { workspace_id, email, role }
3.  Validate: email is valid format
4.  Validate: role is 'admin' or 'member'
5.  Check caller is admin/owner of workspace (query workspace_members)
6.  Check email is not already an active member of the workspace
7.  Check: no pending invite for this email in this workspace already exists
    (if exists: return 409 with existing invite details so frontend can offer "Resend")
8.  Create invite record: insert into workspace_invites with a generated token + 7-day expiry
9.  Send invite email via Resend:
    Subject: "[Admin Name] invited you to join [Workspace Name]"
    Body: includes workspace name, role, and the invite link:
    https://yourapp.com/accept-invite?token={token}
10. Return: { "data": { invite_id, email, role, expires_at } }

Environment Variables Required

RESEND_API_KEY
APP_BASE_URL

Error Responses

Run checklist: Edge Function

3. RPC: `accept_workspace_invite`

CREATE OR REPLACE FUNCTION accept_workspace_invite(
  p_token  text
)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER  -- needs to write workspace_members even if RLS blocks it
AS $$
DECLARE
  v_user_id   uuid := auth.uid();
  v_invite    workspace_invites%ROWTYPE;
BEGIN
  -- 1. Auth check
  IF v_user_id IS NULL THEN
    RAISE EXCEPTION 'AUTH_REQUIRED: authentication required';
  END IF;

  -- 2. Find invite by token
  SELECT * INTO v_invite FROM workspace_invites WHERE token = p_token;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'NOT_FOUND: invite not found or already used';
  END IF;

  -- 3. Check status is pending
  IF v_invite.status != 'pending' THEN
    RAISE EXCEPTION 'BUSINESS_RULE_VIOLATION: this invite has already been % ', v_invite.status;
  END IF;

  -- 4. Check not expired
  IF v_invite.expires_at < now() THEN
    UPDATE workspace_invites SET status = 'expired' WHERE id = v_invite.id;
    RAISE EXCEPTION 'BUSINESS_RULE_VIOLATION: this invite has expired';
  END IF;

  -- 5. Check caller email matches invite email
  IF (SELECT email FROM auth.users WHERE id = v_user_id) != v_invite.email THEN
    RAISE EXCEPTION 'FORBIDDEN: this invite was sent to a different email address';
  END IF;

  -- 6. Check not already a member
  IF EXISTS (SELECT 1 FROM workspace_members WHERE workspace_id = v_invite.workspace_id AND user_id = v_user_id) THEN
    RAISE EXCEPTION 'DUPLICATE: you are already a member of this workspace';
  END IF;

  -- 7. Create workspace_member record
  INSERT INTO workspace_members (workspace_id, user_id, role, status)
  VALUES (v_invite.workspace_id, v_user_id, v_invite.role, 'active');

  -- 8. Mark invite as accepted
  UPDATE workspace_invites
  SET status = 'accepted', accepted_at = now()
  WHERE id = v_invite.id;

  RETURN json_build_object(
    'data', json_build_object('workspace_id', v_invite.workspace_id, 'role', v_invite.role),
    'message', 'Invite accepted. Welcome to the workspace!'
  );
END;
$$;

Run checklist: RPC Function

4. React: Members & Invites Page

URL: /workspaces/{id}/members

Two Tabs

Tab 1: Members

Tab 2: Pending Invites (admin/owner only — tab hidden for members)

5. React: Send Invite Modal

Fields:
[ ] Email: email input, required
[ ] Role: dropdown — Member / Admin

Behavior:
[ ] Submit "Send Invite" → calls Edge Function
[ ] Loading state during call
[ ] On success: "Invite sent to [email]." toast, invites list refreshes
[ ] If already pending: "An invite to this email is already pending. Resend it?" with Resend button
[ ] If already a member: "This email is already a member of this workspace."
[ ] API error: banner above submit

6. React: Accept Invite Page

URL: /accept-invite?token={token}

LOADING STATE:
[ ] Page loads, token extracted from URL
[ ] API call to validate the invite (GET /invites?token=xxx or check via RPC)
[ ] While loading: spinner + "Verifying your invite..."

VALID INVITE:
[ ] Show workspace name + invited role
[ ] Show invited email (user must be logged in with this email)
[ ] "Accept Invite" button → calls accept_workspace_invite RPC
[ ] On success: redirect to workspace dashboard with "Welcome to [Workspace]!" toast
[ ] If user not logged in: "Please sign in with [email] to accept this invite." + sign-in link

INVALID INVITE:
[ ] Token not found: "This invite link is invalid or has already been used."
[ ] Expired: "This invite has expired. Ask your admin to send a new one."
[ ] Wrong email: "This invite was sent to a different email address."
[ ] Already a member: "You are already a member of this workspace." + link to workspace

What You Should NOT Do

× Store the invite token as a sequential ID (use UUID for tokens)
× Use SECURITY INVOKER for accept_workspace_invite — it needs to write workspace_members
× Let the Edge Function skip the "already a member" check
× Let accept_workspace_invite succeed for an expired or already-accepted token
× Show the Pending Invites tab to member-role users
× Generate signed URLs for the invite link — it should be a plain token URL
× Skip the email match check (user must accept with the same email the invite was sent to)

Checklists to Run (in order)

Done When