Bug with concurrent group membership creation

[sentry-io]

Sentry Issue: H-2V3

UniqueViolation: duplicate key value violates unique constraint "uq__user_group__user_id"
DETAIL:  Key (user_id, group_id)=(48869566, 49728675) already exists.

  File "sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "newrelic/hooks/database_psycopg2.py", line 54, in execute
    return super(CursorWrapper, self).execute(sql, parameters, *args, **kwargs)
  File "newrelic/hooks/database_dbapi2.py", line 42, in execute
    return self.__wrapped__.execute(sql, parameters, *args, **kwargs)

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "uq__user_group__user_id"
DETAIL:  Key (user_id, group_id)=(48869566, 49728675) already exists.

[SQL: INSERT INTO user_group (user_id, group_id, roles, created, updated) VALUES (%(user_id)s, %(group_id)s, %(roles)s, %(created)s, %(updated)s) RETURNING user_group.id]
[parameters: {'user_id': 48869566, 'group_id': 49728675, 'roles': '["member"]', 'created': datetime.datetime(2025, 1, 7, 23, 0, 40, 5858), 'updated': datetime.datetime(2025, 1, 7, 23, 0, 40, 5860)}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
(35 additional frame(s) were not displayed)
...
  File "h/tweens.py", line 139, in rollback_db_session_on_exception
    return handler(request)
  File "h/tweens.py", line 20, in conditional_http_tween
    response = handler(request)
  File "h/viewderivers.py", line 30, in wrapper_view
    resp = view(context, request)
  File "h/views/activity.py", line 209, in join
    group_members_service.member_join(self.group, self.request.authenticated_userid)
  File "h/services/group_members.py", line 149, in member_join
    self.db.flush()

https://hypothes-is.slack.com/archives/C0LUWQQJJ/p1736424032451749?thread_ts=1736290846.799929&cid=C0LUWQQJJ