SONAR-11325 Create GET api/alm_integration/list_unbound_installations

14 files changed, 290 insertions, 83 deletions

diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl
index cf18feb0316..0f81c976ee7 100644
--- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl
+++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl
@@ -870,6 +870,7 @@ CREATE TABLE "ALM_APP_INSTALLS" (
   "OWNER_ID" VARCHAR(4000) NOT NULL,
   "IS_OWNER_USER" BOOLEAN,
   "INSTALL_ID" VARCHAR(4000) NOT NULL,
+  "USER_EXTERNAL_ID" VARCHAR(255),
   "CREATED_AT" BIGINT NOT NULL,
   "UPDATED_AT" BIGINT NOT NULL,
 
@@ -877,6 +878,7 @@ CREATE TABLE "ALM_APP_INSTALLS" (
 );
 CREATE UNIQUE INDEX "ALM_APP_INSTALLS_OWNER" ON "ALM_APP_INSTALLS" ("ALM_ID", "OWNER_ID");
 CREATE UNIQUE INDEX "ALM_APP_INSTALLS_INSTALL" ON "ALM_APP_INSTALLS" ("ALM_ID", "INSTALL_ID");
+CREATE INDEX "ALM_APP_INSTALLS_EXTERNAL_ID" ON "ALM_APP_INSTALLS" ("USER_EXTERNAL_ID");
 
 CREATE TABLE "PROJECT_ALM_BINDINGS" (
   "UUID" VARCHAR(40) NOT NULL,
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java
index 0b4c5ca42f6..308f827baaf 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDao.java
@@ -66,12 +66,16 @@ public class AlmAppInstallDao implements Dao {
     return getMapper(dbSession).selectAllWithNoOwnerType();
   }
 
+  public List<AlmAppInstallDto> selectUnboundByUserExternalId(DbSession dbSession, String userExternalId) {
+    return getMapper(dbSession).selectUnboundByUserExternalId(userExternalId);
+  }
+
   /**
    * @param alm Unique identifier of the ALM, like 'bitbucketcloud' or 'github', can't be null
    * @param ownerId ALM specific identifier of the owner of the app, like team or user uuid for Bitbucket Cloud or organization id for Github, can't be null
    * @param installId ALM specific identifier of the app installation, can't be null
    */
-  public void insertOrUpdate(DbSession dbSession, ALM alm, String ownerId, @Nullable Boolean isOwnerUser, String installId) {
+  public void insertOrUpdate(DbSession dbSession, ALM alm, String ownerId, @Nullable Boolean isOwnerUser, String installId, @Nullable String userExternalId) {
     checkAlm(alm);
     checkOwnerId(ownerId);
     checkArgument(isNotEmpty(installId), "installId can't be null nor empty");
@@ -79,8 +83,8 @@ public class AlmAppInstallDao implements Dao {
     AlmAppInstallMapper mapper = getMapper(dbSession);
     long now = system2.now();
 
-    if (mapper.update(alm.getId(), ownerId, isOwnerUser, installId, now) == 0) {
-      mapper.insert(uuidFactory.create(), alm.getId(), ownerId, isOwnerUser, installId, now);
+    if (mapper.update(alm.getId(), ownerId, isOwnerUser, installId, userExternalId, now) == 0) {
+      mapper.insert(uuidFactory.create(), alm.getId(), ownerId, isOwnerUser, installId, userExternalId, now);
     }
   }
 
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDto.java
index 2887472c4a5..cbd55953279 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallDto.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.alm;
 
+import javax.annotation.CheckForNull;
 import javax.annotation.Nullable;
 
 public class AlmAppInstallDto {
@@ -43,75 +44,89 @@ public class AlmAppInstallDto {
    * Is owner a user, can be null
    */
   private Boolean isOwnerUser;
+  /**
+   * The user ID provided by the ALM of the user who has installed the ALM installation. Can be null as some ALM doesn't provide this info. Max size is 255.
+   */
+  private String userExternalId;
 
   private long createdAt;
   private long updatedAt;
 
+  public String getUuid() {
+    return uuid;
+  }
+
   public AlmAppInstallDto setUuid(String uuid) {
     this.uuid = uuid;
     return this;
   }
 
+  public String getAlmId() {
+    return almId;
+  }
+
+  public ALM getAlm() {
+    return ALM.fromId(almId);
+  }
+
   public AlmAppInstallDto setAlmId(String almId) {
     this.almId = almId;
     return this;
   }
 
+  public String getOwnerId() {
+    return ownerId;
+  }
+
   public AlmAppInstallDto setOwnerId(String ownerId) {
     this.ownerId = ownerId;
     return this;
   }
 
+  public String getInstallId() {
+    return installId;
+  }
+
   public AlmAppInstallDto setInstallId(String installId) {
     this.installId = installId;
     return this;
   }
 
+  @Nullable
+  public Boolean isOwnerUser() {
+    return isOwnerUser;
+  }
+
   public AlmAppInstallDto setIsOwnerUser(@Nullable Boolean isOwnerUser) {
     this.isOwnerUser = isOwnerUser;
     return this;
   }
 
-  AlmAppInstallDto setCreatedAt(long createdAt) {
-    this.createdAt = createdAt;
-    return this;
+  @CheckForNull
+  public String getUserExternalId() {
+    return userExternalId;
   }
 
-  AlmAppInstallDto setUpdatedAt(long updatedAt) {
-    this.updatedAt = updatedAt;
+  public AlmAppInstallDto setUserExternalId(@Nullable String userExternalId) {
+    this.userExternalId = userExternalId;
     return this;
   }
 
-  public String getUuid() {
-    return uuid;
-  }
-
-  public String getAlmId() {
-    return almId;
-  }
-
-  public ALM getAlm() {
-    return ALM.fromId(almId);
-  }
-
-  public String getOwnerId() {
-    return ownerId;
-  }
-
-  public String getInstallId() {
-    return installId;
-  }
-
-  @Nullable
-  public Boolean isOwnerUser() {
-    return isOwnerUser;
-  }
-
   public long getCreatedAt() {
     return createdAt;
   }
 
+  AlmAppInstallDto setCreatedAt(long createdAt) {
+    this.createdAt = createdAt;
+    return this;
+  }
+
   public long getUpdatedAt() {
     return updatedAt;
   }
+
+  AlmAppInstallDto setUpdatedAt(long updatedAt) {
+    this.updatedAt = updatedAt;
+    return this;
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallMapper.java
index 4c18e9bfa4a..01fab249383 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/AlmAppInstallMapper.java
@@ -37,11 +37,13 @@ public interface AlmAppInstallMapper {
 
   List<AlmAppInstallDto> selectAllWithNoOwnerType();
 
+  List<AlmAppInstallDto> selectUnboundByUserExternalId(@Param("userExternalId") String userExternalId);
+
   void insert(@Param("uuid") String uuid, @Param("almId") String almId, @Param("ownerId") String ownerId,
-              @Nullable @Param("isOwnerUser") Boolean isOwnerUser, @Param("installId") String installId, @Param("now") long now);
+    @Nullable @Param("isOwnerUser") Boolean isOwnerUser, @Param("installId") String installId, @Nullable @Param("userExternalId") String userExternalId, @Param("now") long now);
 
   int update(@Param("almId") String almId, @Param("ownerId") String ownerId,
-             @Nullable @Param("isOwnerUser") Boolean isOwnerUser, @Param("installId") String installId, @Param("now") long now);
+    @Nullable @Param("isOwnerUser") Boolean isOwnerUser, @Param("installId") String installId, @Nullable @Param("userExternalId") String userExternalId, @Param("now") long now);
 
   void delete(@Param("almId") String almId, @Param("ownerId") String ownerId);
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/OrganizationAlmBindingDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/OrganizationAlmBindingDto.java
index 30a2795a280..9ea9a3194f5 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/alm/OrganizationAlmBindingDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/alm/OrganizationAlmBindingDto.java
@@ -49,7 +49,7 @@ public class OrganizationAlmBindingDto {
    */
   private String url;
   /**
-   * The UUID of the user who has created the link between the organization and the ALN installation. Can't be null. Max size is 255.
+   * The UUID of the user who has created the link between the organization and the ALM installation. Can't be null. Max size is 255.
    */
   private String userUuid;
   /**
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml
index 0d539aad6cf..c9512fb9345 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/alm/AlmAppInstallMapper.xml
@@ -4,19 +4,20 @@
 <mapper namespace="org.sonar.db.alm.AlmAppInstallMapper">
 
   <sql id="sqlColumns">
-    uuid,
-    alm_id as almId,
-    owner_id as ownerId,
-    is_owner_user as isOwnerUser,
-    install_id as installId,
-    created_at as createdAt,
-    updated_at as updatedAt
+    aai.uuid,
+    aai.alm_id as almId,
+    aai.owner_id as ownerId,
+    aai.is_owner_user as isOwnerUser,
+    aai.install_id as installId,
+    aai.user_external_id as userExternalId,
+    aai.created_at as createdAt,
+    aai.updated_at as updatedAt
   </sql>
 
   <select id="selectByOwnerId" parameterType="Map" resultType="org.sonar.db.alm.AlmAppInstallDto">
     select <include refid="sqlColumns" />
     from
-      alm_app_installs
+      alm_app_installs aai
     where
       alm_id = #{almId, jdbcType=VARCHAR}
       and owner_id = #{ownerId, jdbcType=VARCHAR}
@@ -25,7 +26,7 @@
   <select id="selectByInstallationId" parameterType="Map" resultType="org.sonar.db.alm.AlmAppInstallDto">
     select <include refid="sqlColumns"/>
     from
-      alm_app_installs
+      alm_app_installs aai
     where
       alm_id = #{almId, jdbcType=VARCHAR}
       and install_id = #{installId, jdbcType=VARCHAR}
@@ -34,7 +35,7 @@
   <select id="selectByUuid" parameterType="Map" resultType="org.sonar.db.alm.AlmAppInstallDto">
     select <include refid="sqlColumns"/>
     from
-      alm_app_installs
+      alm_app_installs aai
     where
       uuid = #{uuid, jdbcType=VARCHAR}
   </select>
@@ -42,11 +43,21 @@
   <select id="selectAllWithNoOwnerType" parameterType="Map" resultType="org.sonar.db.alm.AlmAppInstallDto">
     select <include refid="sqlColumns" />
     from
-      alm_app_installs
+      alm_app_installs aai
     where
       is_owner_user is null
   </select>
 
+  <select id="selectUnboundByUserExternalId" parameterType="Map" resultType="org.sonar.db.alm.AlmAppInstallDto">
+    select <include refid="sqlColumns" />
+    from
+    alm_app_installs aai
+    left outer join organization_alm_bindings bind on bind.alm_app_install_uuid =  aai.uuid
+    where
+    aai.user_external_id = #{userExternalId, jdbcType=VARCHAR}
+    and bind.uuid is null
+  </select>
+
   <insert id="insert" parameterType="Map" useGeneratedKeys="false">
     INSERT INTO alm_app_installs
     (
@@ -55,6 +66,7 @@
       owner_id,
       is_owner_user,
       install_id,
+      user_external_id,
       created_at,
       updated_at
     )
@@ -64,6 +76,7 @@
       #{ownerId, jdbcType=VARCHAR},
       #{isOwnerUser, jdbcType=BOOLEAN},
       #{installId, jdbcType=VARCHAR},
+      #{userExternalId, jdbcType=VARCHAR},
       #{now, jdbcType=BIGINT},
       #{now, jdbcType=BIGINT}
     )
@@ -73,6 +86,7 @@
     update alm_app_installs set
       install_id = #{installId, jdbcType=VARCHAR},
       is_owner_user = #{isOwnerUser, jdbcType=BOOLEAN},
+      user_external_id = #{userExternalId, jdbcType=VARCHAR},
       updated_at = #{now, jdbcType=BIGINT}
     where
       alm_id = #{almId, jdbcType=VARCHAR}
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java
index 0e61ee69324..7cfd08bad92 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmAppInstallDaoTest.java
@@ -27,9 +27,13 @@ import org.junit.Test;
 import org.junit.rules.ExpectedException;
 import org.sonar.api.utils.System2;
 import org.sonar.core.util.UuidFactory;
+import org.sonar.core.util.Uuids;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
+import org.sonar.db.organization.OrganizationDto;
+import org.sonar.db.user.UserDto;
 
+import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
@@ -63,12 +67,13 @@ public class AlmAppInstallDaoTest {
   public void selectByUuid() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
+    String userUuid = Uuids.createFast();
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, userUuid);
 
     assertThat(underTest.selectByUuid(dbSession, A_UUID).get())
-      .extracting(AlmAppInstallDto::getUuid, AlmAppInstallDto::getAlm, AlmAppInstallDto::getInstallId, AlmAppInstallDto::getOwnerId,
+      .extracting(AlmAppInstallDto::getUuid, AlmAppInstallDto::getAlm, AlmAppInstallDto::getInstallId, AlmAppInstallDto::getOwnerId, AlmAppInstallDto::getUserExternalId,
         AlmAppInstallDto::getCreatedAt, AlmAppInstallDto::getUpdatedAt)
-      .contains(A_UUID, GITHUB, A_OWNER, AN_INSTALL, DATE, DATE);
+      .contains(A_UUID, GITHUB, A_OWNER, AN_INSTALL, userUuid, DATE, DATE);
 
     assertThat(underTest.selectByUuid(dbSession, "foo")).isNotPresent();
   }
@@ -77,7 +82,7 @@ public class AlmAppInstallDaoTest {
   public void selectByOwnerId() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, null);
 
     assertThat(underTest.selectByOwnerId(dbSession, GITHUB, A_OWNER).get())
       .extracting(AlmAppInstallDto::getUuid, AlmAppInstallDto::getAlm, AlmAppInstallDto::getInstallId, AlmAppInstallDto::getOwnerId,
@@ -113,7 +118,7 @@ public class AlmAppInstallDaoTest {
   public void selectByInstallationId() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, Uuids.createFast());
 
     assertThat(underTest.selectByInstallationId(dbSession, GITHUB, AN_INSTALL).get())
       .extracting(AlmAppInstallDto::getUuid, AlmAppInstallDto::getAlm, AlmAppInstallDto::getInstallId, AlmAppInstallDto::getOwnerId,
@@ -125,48 +130,70 @@ public class AlmAppInstallDaoTest {
   }
 
   @Test
+  public void selectUnboundByUserExternalId() {
+    when(uuidFactory.create()).thenReturn(A_UUID);
+    when(system2.now()).thenReturn(DATE);
+    UserDto user1 = db.users().insertUser();
+    UserDto user2 = db.users().insertUser();
+    OrganizationDto organization1 = db.organizations().insert();
+    OrganizationDto organization2 = db.organizations().insert();
+    AlmAppInstallDto almAppInstall1 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user1.getExternalId()));
+    AlmAppInstallDto almAppInstall2 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user1.getExternalId()));
+    AlmAppInstallDto almAppInstall3 = db.alm().insertAlmAppInstall(app -> app.setUserExternalId(user2.getExternalId()));
+    db.alm().insertOrganizationAlmBinding(organization1, almAppInstall1);
+    db.alm().insertOrganizationAlmBinding(organization2, almAppInstall3);
+
+    assertThat(underTest.selectUnboundByUserExternalId(dbSession, user1.getExternalId()))
+      .extracting(AlmAppInstallDto::getUuid)
+      .containsExactlyInAnyOrder(almAppInstall2.getUuid());
+    assertThat(underTest.selectUnboundByUserExternalId(dbSession, user2.getExternalId())).isEmpty();
+  }
+
+  @Test
   public void insert_throws_NPE_if_alm_is_null() {
     expectAlmNPE();
 
-    underTest.insertOrUpdate(dbSession, null, A_OWNER, true, AN_INSTALL);
+    underTest.insertOrUpdate(dbSession, null, A_OWNER, true, AN_INSTALL, null);
   }
 
   @Test
   public void insert_throws_IAE_if_owner_id_is_null() {
     expectOwnerIdNullOrEmptyIAE();
 
-    underTest.insertOrUpdate(dbSession, GITHUB, null, true, AN_INSTALL);
+    underTest.insertOrUpdate(dbSession, GITHUB, null, true, AN_INSTALL, null);
   }
 
   @Test
   public void insert_throws_IAE_if_owner_id_is_empty() {
     expectOwnerIdNullOrEmptyIAE();
 
-    underTest.insertOrUpdate(dbSession, GITHUB, EMPTY_STRING, true, AN_INSTALL);
+    underTest.insertOrUpdate(dbSession, GITHUB, EMPTY_STRING, true, AN_INSTALL, null);
   }
 
   @Test
   public void insert_throws_IAE_if_install_id_is_null() {
     expectInstallIdNullOrEmptyIAE();
 
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, null);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, null, null);
   }
 
   @Test
   public void insert_throws_IAE_if_install_id_is_empty() {
     expectInstallIdNullOrEmptyIAE();
 
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, EMPTY_STRING);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, EMPTY_STRING, null);
   }
 
   @Test
   public void insert() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
+    String userUuid = Uuids.createFast();
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, userUuid);
 
     assertThatAlmAppInstall(GITHUB, A_OWNER)
       .hasInstallId(AN_INSTALL)
+      .hasUserExternalId(userUuid)
       .hasCreatedAt(DATE)
       .hasUpdatedAt(DATE);
   }
@@ -175,14 +202,10 @@ public class AlmAppInstallDaoTest {
   public void delete() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
-
-    assertThatAlmAppInstall(GITHUB, A_OWNER)
-      .hasInstallId(AN_INSTALL)
-      .hasCreatedAt(DATE)
-      .hasUpdatedAt(DATE);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, null);
 
     underTest.delete(dbSession, GITHUB, A_OWNER);
+
     assertThatAlmAppInstall(GITHUB, A_OWNER).doesNotExist();
   }
 
@@ -197,13 +220,16 @@ public class AlmAppInstallDaoTest {
   public void update() {
     when(uuidFactory.create()).thenReturn(A_UUID);
     when(system2.now()).thenReturn(DATE);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
+    String userExternalId1 = randomAlphanumeric(10);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, userExternalId1);
 
     when(system2.now()).thenReturn(DATE_LATER);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, OTHER_INSTALL);
+    String userExternalId2 = randomAlphanumeric(10);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, OTHER_INSTALL, userExternalId2);
 
     assertThatAlmAppInstall(GITHUB, A_OWNER)
       .hasInstallId(OTHER_INSTALL)
+      .hasUserExternalId(userExternalId2)
       .hasCreatedAt(DATE)
       .hasUpdatedAt(DATE_LATER);
   }
@@ -214,18 +240,22 @@ public class AlmAppInstallDaoTest {
     when(uuidFactory.create())
       .thenReturn(A_UUID)
       .thenReturn(A_UUID_2);
-    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL);
-    underTest.insertOrUpdate(dbSession, GITHUB, ANOTHER_OWNER, false, OTHER_INSTALL);
+    String userExternalId1 = randomAlphanumeric(10);
+    String userExternalId2 = randomAlphanumeric(10);
+    underTest.insertOrUpdate(dbSession, GITHUB, A_OWNER, true, AN_INSTALL, userExternalId1);
+    underTest.insertOrUpdate(dbSession, GITHUB, ANOTHER_OWNER, false, OTHER_INSTALL, userExternalId2);
 
     assertThatAlmAppInstall(GITHUB, A_OWNER)
       .hasInstallId(AN_INSTALL)
       .hasOwnerUser(true)
+      .hasUserExternalId(userExternalId1)
       .hasCreatedAt(DATE)
       .hasUpdatedAt(DATE);
 
     assertThatAlmAppInstall(GITHUB, ANOTHER_OWNER)
       .hasInstallId(OTHER_INSTALL)
       .hasOwnerUser(false)
+      .hasUserExternalId(userExternalId2)
       .hasCreatedAt(DATE)
       .hasUpdatedAt(DATE);
   }
@@ -282,6 +312,15 @@ public class AlmAppInstallDaoTest {
       return this;
     }
 
+    AlmAppInstallAssert hasUserExternalId(String expected) {
+      isNotNull();
+
+      if (!Objects.equals(actual.getUserExternalId(), expected)) {
+        failWithMessage("Expected ALM App Install to have column USER_EXTERNAL_ID to be <%s> but was <%s>", expected, actual.getUserExternalId());
+      }
+      return this;
+    }
+
     AlmAppInstallAssert hasCreatedAt(long expected) {
       isNotNull();
 
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmDbTester.java b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmDbTester.java
index f129b5923ae..90e7f7c5e78 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmDbTester.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/alm/AlmDbTester.java
@@ -20,6 +20,8 @@
 
 package org.sonar.db.alm;
 
+import java.util.Arrays;
+import java.util.function.Consumer;
 import org.sonar.db.DbTester;
 import org.sonar.db.organization.OrganizationDto;
 import org.sonar.db.user.UserDto;
@@ -39,14 +41,22 @@ public class AlmDbTester {
   public OrganizationAlmBindingDto insertOrganizationAlmBinding(OrganizationDto organization, AlmAppInstallDto almAppInstall) {
     UserDto user = db.users().insertUser();
     db.getDbClient().organizationAlmBindingDao().insert(db.getSession(), organization, almAppInstall, randomAlphabetic(10), user.getUuid());
+    db.commit();
     return db.getDbClient().organizationAlmBindingDao().selectByOrganization(db.getSession(), organization).get();
   }
 
-  public AlmAppInstallDto insertAlmAppInstall() {
-    ALM alm = GITHUB;
-    String ownerId = randomAlphanumeric(10);
-    db.getDbClient().almAppInstallDao().insertOrUpdate(db.getSession(), alm, ownerId, false, randomAlphanumeric(10));
-    return db.getDbClient().almAppInstallDao().selectByOwnerId(db.getSession(), alm, ownerId).get();
+  @SafeVarargs
+  public final AlmAppInstallDto insertAlmAppInstall(Consumer<AlmAppInstallDto>... dtoPopulators) {
+    AlmAppInstallDto dto = new AlmAppInstallDto()
+      .setAlmId(GITHUB.getId())
+      .setInstallId(randomAlphanumeric(10))
+      .setOwnerId(randomAlphanumeric(10))
+      .setIsOwnerUser(false)
+      .setUserExternalId(randomAlphanumeric(10));
+    Arrays.stream(dtoPopulators).forEach(dtoPopulator -> dtoPopulator.accept(dto));
+    db.getDbClient().almAppInstallDao().insertOrUpdate(db.getSession(), dto.getAlm(), dto.getOwnerId(), dto.isOwnerUser(), dto.getInstallId(), dto.getUserExternalId());
+    db.commit();
+    return db.getDbClient().almAppInstallDao().selectByOwnerId(db.getSession(), dto.getAlm(), dto.getOwnerId()).get();
   }
 
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstall.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstall.java
new file mode 100644
index 00000000000..57330a6691f
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstall.java
@@ -0,0 +1,58 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v75;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.SupportsBlueGreen;
+import org.sonar.server.platform.db.migration.def.VarcharColumnDef;
+import org.sonar.server.platform.db.migration.sql.AddColumnsBuilder;
+import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder;
+
+@SupportsBlueGreen
+public class AddUserExternalIdColumnInAlmAppInstall extends DdlChange {
+
+  private static final String ALM_APP_INSTALLS_TABLE = "alm_app_installs";
+  private static final String USER_EXTERNAL_ID_COLUMN = "user_external_id";
+
+  public AddUserExternalIdColumnInAlmAppInstall(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    VarcharColumnDef userExternalIdDef = newVarcharColumnDefBuilder()
+      .setColumnName(USER_EXTERNAL_ID_COLUMN)
+      .setLimit(255)
+      .setIsNullable(true)
+      .build();
+    context.execute(new AddColumnsBuilder(getDialect(), ALM_APP_INSTALLS_TABLE)
+      .addColumn(userExternalIdDef).build());
+    context.execute(new CreateIndexBuilder(getDialect())
+      .setTable(ALM_APP_INSTALLS_TABLE)
+      .addColumn(userExternalIdDef)
+      .setUnique(false)
+      .setName("alm_app_installs_external_id")
+      .build());
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75.java
index 65c33aa424e..4e019df1542 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75.java
@@ -29,6 +29,7 @@ public class DbVersion75 implements DbVersion {
     registry
       .add(2400, "Add column IS_OWNER_USER in ALM_APP_INSTALLS", AddIsOwnerUserColumnInAlmAppInstall.class)
       .add(2401, "Create ORGANIZATION_ALM_BINDINGS table", CreateOrganizationsAlmBindingsTable.class)
+      .add(2402, "Add column USER_EXTERNAL_ID in ALM_APP_INSTALLS", AddUserExternalIdColumnInAlmAppInstall.class)
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest.java
new file mode 100644
index 00000000000..827e794b9d7
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest.java
@@ -0,0 +1,57 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v75;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.sonar.db.CoreDbTester;
+
+import static java.sql.Types.VARCHAR;
+
+public class AddUserExternalIdColumnInAlmAppInstallTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(AddUserExternalIdColumnInAlmAppInstallTest.class, "almAppInstalls.sql");
+
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  private AddUserExternalIdColumnInAlmAppInstall underTest = new AddUserExternalIdColumnInAlmAppInstall(db.database());
+
+  @Test
+  public void column_is_added_to_table() throws SQLException {
+    underTest.execute();
+
+    db.assertColumnDefinition("alm_app_installs", "user_external_id", VARCHAR, 255, true);
+    db.assertIndex("alm_app_installs", "alm_app_installs_external_id", "user_external_id");
+  }
+
+  @Test
+  public void migration_is_not_reentrant() throws SQLException {
+    underTest.execute();
+
+    expectedException.expect(IllegalStateException.class);
+
+    underTest.execute();
+  }
+
+}
\ No newline at end of file
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75Test.java
index 0b940dbcb16..d9734d175cf 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v75/DbVersion75Test.java
@@ -35,6 +35,6 @@ public class DbVersion75Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 2);
+    verifyMigrationCount(underTest, 3);
   }
 }
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest/almAppInstalls.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest/almAppInstalls.sql
new file mode 100644
index 00000000000..d73225bde8c
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v75/AddUserExternalIdColumnInAlmAppInstallTest/almAppInstalls.sql
@@ -0,0 +1,13 @@
+CREATE TABLE "ALM_APP_INSTALLS" (
+  "UUID" VARCHAR(40) NOT NULL,
+  "ALM_ID" VARCHAR(40) NOT NULL,
+  "OWNER_ID" VARCHAR(4000) NOT NULL,
+  "IS_OWNER_USER" BOOLEAN,
+  "INSTALL_ID" VARCHAR(4000) NOT NULL,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL,
+
+  CONSTRAINT "PK_ALM_APP_INSTALLS" PRIMARY KEY ("UUID")
+);
+CREATE UNIQUE INDEX "ALM_APP_INSTALLS_OWNER" ON "ALM_APP_INSTALLS" ("ALM_ID", "OWNER_ID");
+CREATE UNIQUE INDEX "ALM_APP_INSTALLS_INSTALL" ON "ALM_APP_INSTALLS" ("ALM_ID", "INSTALL_ID");
\ No newline at end of file
diff --git a/server/sonar-server/src/test/java/org/sonar/server/organization/ws/DeleteActionTest.java b/server/sonar-server/src/test/java/org/sonar/server/organization/ws/DeleteActionTest.java
index 3acddd3f63c..56031488ff9 100644
--- a/server/sonar-server/src/test/java/org/sonar/server/organization/ws/DeleteActionTest.java
+++ b/server/sonar-server/src/test/java/org/sonar/server/organization/ws/DeleteActionTest.java
@@ -37,8 +37,6 @@ import org.sonar.core.util.UuidFactory;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
-import org.sonar.db.alm.ALM;
-import org.sonar.db.alm.AlmAppInstallDto;
 import org.sonar.db.component.ComponentDto;
 import org.sonar.db.component.ComponentTesting;
 import org.sonar.db.component.ResourceTypesRule;
@@ -78,8 +76,6 @@ import org.sonar.server.ws.WsActionTester;
 import static com.google.common.collect.ImmutableList.of;
 import static java.util.Arrays.asList;
 import static java.util.Collections.emptySet;
-import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic;
-import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.fail;
 import static org.mockito.ArgumentMatchers.any;
@@ -549,11 +545,7 @@ public class DeleteActionTest {
   @Test
   public void delete_organization_alm_binding() {
     OrganizationDto organization = db.organizations().insert();
-    String installationId = randomAlphanumeric(10);
-    db.getDbClient().almAppInstallDao().insertOrUpdate(db.getSession(), ALM.GITHUB, randomAlphabetic(13), false, installationId);
-    AlmAppInstallDto almAppInstall = db.getDbClient().almAppInstallDao().selectByInstallationId(db.getSession(), ALM.GITHUB, installationId).get();
-    db.getDbClient().organizationAlmBindingDao().insert(db.getSession(), organization, almAppInstall, randomAlphabetic(10), db.users().insertUser().getUuid());
-    db.commit();
+    db.alm().insertOrganizationAlmBinding(organization, db.alm().insertAlmAppInstall());
     logInAsAdministrator(organization);
 
     sendRequest(organization);