Some Users With Outstanding Collaboration Invitations Are Unable To Create An Account

[aaronskiba]

Please complete the following fields as applicable:

What version of the DMPRoadmap code are you running? (e.g. v2.2.0)

• 4.0.2+portage-4.0.1

Expected behaviour:

• The recipient of an "Invite Collaborators" e-mail should be able to create an account, either by using the invitation link provided in the email, or by using the "Create Account" feature on the home page.

Actual behaviour:

• Some users with that previously received an invitation email, are unable to create an account using the "Create Account" feature on the home page.

Diagnosis

• Add a new contributor that does not yet exist in the db.
• 
• The saved value of accept_terms in the db for this new User is null:
• 
• Looking atlib/tasks/rewrite_postgres.rake:

  task users: :environment do
  users = JSON.parse(File.read('db/seeds/staging/temp/users.rb'))
  users.each do |x|
    puts "writing back user #{x['id']}"
    active = x['active'] != 0
    accept_terms = x['accept_terms'] != 0
    query = ActiveRecord::Base.sanitize_sql(['INSERT INTO users
                                            VALUES
                                            (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                                             x['id'],
                                             x['firstname'],
                                             x['surname'],
                                             x['email'],
                                             x['created_at'],
                                             x['updated_at'],
                                             x['encrypted_password'],
                                             x['reset_password_token'],
                                             x['reset_password_sent_at'],
                                             x['remember_created_at'],
                                             x['sign_in_count'],
                                             x['current_sign_in_at'],
                                             x['last_sign_in_at'],
                                             x['current_sign_in_ip'],
                                             x['last_sign_in_ip'],
                                             x['confirmation_token'],
                                             x['confirmed_at'],
                                             x['confirmation_sent_at'],
                                             x['invitation_token'],
                                             x['invitation_created_at'],
                                             x['invitation_sent_at'],
                                             x['invitation_accepted_at'],
                                             x['other_organisation'],
                                             x['dmponline3'],
                                             accept_terms,
  ...

  However:

  2.7.6 :001 > User.find_by!(email: 'testing_default_accept_terms@x.com').accept_terms != 0
  User Load (1.4ms)  SELECT "users".* FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "testing_default_accept_terms@x.com"], ["LIMIT", 1]]
  => true 
  2.7.6 :002 > User.find_by!(email: 'testing_default_accept_terms@x.com').accept_terms
  User Load (2.9ms)  SELECT "users".* FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "testing_default_accept_terms@x.com"], ["LIMIT", 1]]
  => nil 

  Testing by changing the default value from nil to true:

  2.7.6 :001 > User.find_by!(email: 'testing_default_accept_terms@x.com').update_attribute(:accept_terms, true)
  User Load (0.8ms)  SELECT "users".* FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "testing_default_accept_terms@x.com"], ["LIMIT", 1]]
  TRANSACTION (1.0ms)  BEGIN
  User Update (0.9ms)  UPDATE "users" SET "updated_at" = $1, "accept_terms" = $2 WHERE "users"."id" = $3  [["updated_at", "2024-02-15 22:22:31.219621"], ["accept_terms", true], ["id", 25197]]
  TRANSACTION (11.6ms)  COMMIT
  => true 
  2.7.6 :002 > 

  Testing the app behaviour when the default_accept_terms value is changed to true:

• Create an account with the testing_default_accept_terms@x.com email
• Result
• Result when attempting to simply log in:

Affected Users (invitation_token appears to be set to null after a user successfully creates an account.)

[aaronskiba]

  def create
    oauth = { provider: nil, uid: nil }
    IdentifierScheme.for_users.each do |scheme|
      oauth = session["devise.#{scheme.name.downcase}_data"] unless session["devise.#{scheme.name.downcase}_data"].nil?
    end

    blank_org = if Rails.configuration.x.application.restrict_orgs
                  sign_up_params[:org_id]['id'].blank?
                else
                  sign_up_params[:org_id].blank?
                end

    if sign_up_params[:accept_terms].to_s == '0'
      redirect_to after_sign_up_error_path_for(resource),
                  alert: _('You must accept the terms and conditions to register.')
    elsif blank_org
      redirect_to after_sign_up_error_path_for(resource),
                  alert: _('Please select an organisation from the list, or choose Other.')
    else
      existing_user = User.where_case_insensitive('email', sign_up_params[:email]).first
      if existing_user.present?
        if existing_user.invitation_token.present? && !existing_user.accept_terms?
          # If the user is creating an account but they have an outstanding invitation, remember
          # any plans that were shared with the invitee so we can attach them to the new User record
          shared_plans = existing_user.roles
                                      .select(&:active?)
                                      .map { |role| { plan_id: role.plan_id, access: role.access } }
          existing_user.destroy
        else
          redirect_to after_sign_up_error_path_for(resource),
                      alert: _('That email address is already registered.')
          return
        end
      end

Because the migration changed the nil accept_terms values to true, we can see how any affected users that also had an existing invitation_token are now unable to create an account (direct_to after_sign_up_error_path_for(resource), alert: _('That email address is already registered.' would be executed).

[aaronskiba]

SELECT created_at, updated_at, invitation_created_at, invitation_sent_at
FROM users
WHERE invitation_token IS NOT null
AND accept_terms = true
ORDER BY invitation_created_at DESC
LIMIT 1;

The most recent occurence of this was on February 21, 2023; the same date as the release that performed the migration from MariaDB. https://github.com/portagenetwork/roadmap/releases/tag/3.1.0%2Bportage-3.1.0

[aaronskiba]

This idea was applied to a single user in the production environment (i.e. A user where accept_terms = TRUE and invitation_token IS NOT NULL was updated in the db so that accept_terms = NULL). The fix was successful.