Invite users to organizations and projects

[jeroenrinzema]

Task: Refactor User Membership & Invitation System

🎯 Goal

Enable users to be members of multiple organizations and projects, and introduce a unified invitation flow for both.
Currently, users can only belong to a single organization — this needs to be refactored.

---

🧩 Requirements

Organization Membership

• A user can belong to one or more organizations.
• The admins table currently uses the organization_id column to manage access — this must be updated to support multiple organizations.
• Invitations should allow users to join an organization via email.

Project Membership

• A user can belong to one or more projects, potentially across multiple organizations.
• The project_admins table tracks users and their roles within a project.
• Invitations should allow users to join a specific project directly, even if they’re not yet part of the parent organization (automatically adding them if necessary).

Invitations

• Invitations are stored in an invites table (single source for both org and project invites).
• A user should be able to:
  • Receive an invite via email.
  • See a pop-up banner or notification upon signing in that prompts them to accept pending invitations.
• On acceptance:
  • The user is added to the organization and/or project.
  • The invite is marked as accepted.

---

🧱 Data Model Updates

Table	Change	Description
admins	Deprecate organization_id column	Previously used for single-organization membership — to be replaced by organization_users
organization_users	New table	Many-to-many relation table linking users to organizations, with roles
project_admins	No change or minor updates	Ensure support for cross-organization membership
invites	New table	Centralized storage for organization/project invitations

---

📨 Invitation Flow

sequenceDiagram
    participant Inviter
    participant Backend
    participant Invitee
    Inviter->>Backend: Create organization/project invite (email)
    Backend->>Backend: Store invite in `invites` table
    Backend->>Invitee: Send invitation email with token link
    Invitee->>Backend: Log in or register via Clerk
    Backend->>Backend: Check for pending invites
    Invitee->>Backend: Accept invite
    Backend->>DB: Add user to organization and mark invite accepted

Loading