Membership.create!(user_id: 1, organisation_id: 7)
  Membership.create!(user_id: 1, organisation_id: 7)   # rejected

  # => ["User is already a member of this organisation"]

  # Migration
  class AddUniqueIndexToMemberships < ActiveRecord::Migration[7.1]
    disable_ddl_transaction!

    def change
      add_index :memberships, [:organisation_id, :user_id],
                unique: true,
                algorithm: :concurrently,
                name: "idx_memberships_org_user"
    end
  end

  # Model
  class Membership < ApplicationRecord
    belongs_to :user
    belongs_to :organisation

    validates :user_id,
              uniqueness: {
                scope: :organisation_id,
                message: "is already a member of this organisation"
              }
  end

  # Handle the race the validation cannot cover
  def create
    @membership = Membership.new(membership_params)

    if @membership.save
      redirect_to @membership.organisation
    else
      render :new, status: :unprocessable_entity
    end
  rescue ActiveRecord::RecordNotUnique
    @membership.errors.add(:user_id, "is already a member of this organisation")
    render :new, status: :unprocessable_entity
  end

  # Nullable column in the pair — NULLs are distinct, so use a partial index
  # add_index :prices, [:product_id, :currency],
  #           unique: true, where: "discarded_at IS NULL"

  # Case-insensitive pair
  # validates :name, uniqueness: { scope: :account_id, case_sensitive: false }
  # add_index :teams, "account_id, LOWER(name)", unique: true