nmode's Git Repositories - signal-cli/blob - lib/src/main/java/org/asamk/signal/manager/storage/recipients/RecipientStore.java

   1 package org.asamk.signal.manager.storage.recipients;
   2
   3 import org.asamk.signal.manager.api.Pair;
   4 import org.asamk.signal.manager.api.UnregisteredRecipientException;
   5 import org.asamk.signal.manager.storage.Database;
   6 import org.asamk.signal.manager.storage.Utils;
   7 import org.asamk.signal.manager.storage.contacts.ContactsStore;
   8 import org.asamk.signal.manager.storage.profiles.ProfileStore;
   9 import org.signal.libsignal.zkgroup.InvalidInputException;
  10 import org.signal.libsignal.zkgroup.profiles.ExpiringProfileKeyCredential;
  11 import org.signal.libsignal.zkgroup.profiles.ProfileKey;
  12 import org.slf4j.Logger;
  13 import org.slf4j.LoggerFactory;
  14 import org.whispersystems.signalservice.api.push.ACI;
  15 import org.whispersystems.signalservice.api.push.SignalServiceAddress;
  16 import org.whispersystems.signalservice.api.util.UuidUtil;
  17
  18 import java.sql.Connection;
  19 import java.sql.ResultSet;
  20 import java.sql.SQLException;
  21 import java.util.ArrayList;
  22 import java.util.Arrays;
  23 import java.util.Collection;
  24 import java.util.HashMap;
  25 import java.util.List;
  26 import java.util.Map;
  27 import java.util.Objects;
  28 import java.util.Optional;
  29 import java.util.Set;
  30 import java.util.UUID;
  31 import java.util.function.Supplier;
  32 import java.util.stream.Collectors;
  33
  34 public class RecipientStore implements RecipientResolver, RecipientTrustedResolver, ContactsStore, ProfileStore {
  35
  36     private final static Logger logger = LoggerFactory.getLogger(RecipientStore.class);
  37     private static final String TABLE_RECIPIENT = "recipient";
  38     private static final String SQL_IS_CONTACT = "r.given_name IS NOT NULL OR r.family_name IS NOT NULL OR r.expiration_time > 0 OR r.profile_sharing = TRUE OR r.color IS NOT NULL OR r.blocked = TRUE OR r.archived = TRUE";
  39
  40     private final RecipientMergeHandler recipientMergeHandler;
  41     private final SelfAddressProvider selfAddressProvider;
  42     private final Database database;
  43
  44     private final Object recipientsLock = new Object();
  45     private final Map<Long, Long> recipientsMerged = new HashMap<>();
  46
  47     public static void createSql(Connection connection) throws SQLException {
  48         // When modifying the CREATE statement here, also add a migration in AccountDatabase.java
  49         try (final var statement = connection.createStatement()) {
  50             statement.executeUpdate("""
  51                                     CREATE TABLE recipient (
  52                                       _id INTEGER PRIMARY KEY AUTOINCREMENT,
  53                                       number TEXT UNIQUE,
  54                                       uuid BLOB UNIQUE,
  55                                       profile_key BLOB,
  56                                       profile_key_credential BLOB,
  57
  58                                       given_name TEXT,
  59                                       family_name TEXT,
  60                                       color TEXT,
  61
  62                                       expiration_time INTEGER NOT NULL DEFAULT 0,
  63                                       blocked BOOLEAN NOT NULL DEFAULT FALSE,
  64                                       archived BOOLEAN NOT NULL DEFAULT FALSE,
  65                                       profile_sharing BOOLEAN NOT NULL DEFAULT FALSE,
  66
  67                                       profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
  68                                       profile_given_name TEXT,
  69                                       profile_family_name TEXT,
  70                                       profile_about TEXT,
  71                                       profile_about_emoji TEXT,
  72                                       profile_avatar_url_path TEXT,
  73                                       profile_mobile_coin_address BLOB,
  74                                       profile_unidentified_access_mode TEXT,
  75                                       profile_capabilities TEXT
  76                                     );
  77                                     """);
  78         }
  79     }
  80
  81     public RecipientStore(
  82             final RecipientMergeHandler recipientMergeHandler,
  83             final SelfAddressProvider selfAddressProvider,
  84             final Database database
  85     ) {
  86         this.recipientMergeHandler = recipientMergeHandler;
  87         this.selfAddressProvider = selfAddressProvider;
  88         this.database = database;
  89     }
  90
  91     public RecipientAddress resolveRecipientAddress(RecipientId recipientId) {
  92         final var sql = (
  93                 """
  94                 SELECT r.number, r.uuid
  95                 FROM %s r
  96                 WHERE r._id = ?
  97                 """
  98         ).formatted(TABLE_RECIPIENT);
  99         try (final var connection = database.getConnection()) {
 100             try (final var statement = connection.prepareStatement(sql)) {
 101                 statement.setLong(1, recipientId.id());
 102                 return Utils.executeQuerySingleRow(statement, this::getRecipientAddressFromResultSet);
 103             }
 104         } catch (SQLException e) {
 105             throw new RuntimeException("Failed read from recipient store", e);
 106         }
 107     }
 108
 109     public Collection<RecipientId> getRecipientIdsWithEnabledProfileSharing() {
 110         final var sql = (
 111                 """
 112                 SELECT r._id
 113                 FROM %s r
 114                 WHERE r.blocked = FALSE AND r.profile_sharing = TRUE
 115                 """
 116         ).formatted(TABLE_RECIPIENT);
 117         try (final var connection = database.getConnection()) {
 118             try (final var statement = connection.prepareStatement(sql)) {
 119                 try (var result = Utils.executeQueryForStream(statement, this::getRecipientIdFromResultSet)) {
 120                     return result.toList();
 121                 }
 122             }
 123         } catch (SQLException e) {
 124             throw new RuntimeException("Failed read from recipient store", e);
 125         }
 126     }
 127
 128     @Override
 129     public RecipientId resolveRecipient(final long rawRecipientId) {
 130         final var sql = (
 131                 """
 132                 SELECT r._id
 133                 FROM %s r
 134                 WHERE r._id = ?
 135                 """
 136         ).formatted(TABLE_RECIPIENT);
 137         try (final var connection = database.getConnection()) {
 138             try (final var statement = connection.prepareStatement(sql)) {
 139                 statement.setLong(1, rawRecipientId);
 140                 return Utils.executeQueryForOptional(statement, this::getRecipientIdFromResultSet).orElse(null);
 141             }
 142         } catch (SQLException e) {
 143             throw new RuntimeException("Failed read from recipient store", e);
 144         }
 145     }
 146
 147     public RecipientId resolveRecipient(
 148             final String number, Supplier<ACI> aciSupplier
 149     ) throws UnregisteredRecipientException {
 150         final Optional<RecipientWithAddress> byNumber;
 151         try (final var connection = database.getConnection()) {
 152             byNumber = findByNumber(connection, number);
 153         } catch (SQLException e) {
 154             throw new RuntimeException("Failed read from recipient store", e);
 155         }
 156         if (byNumber.isEmpty() || byNumber.get().address().uuid().isEmpty()) {
 157             final var aci = aciSupplier.get();
 158             if (aci == null) {
 159                 throw new UnregisteredRecipientException(new RecipientAddress(null, number));
 160             }
 161
 162             return resolveRecipient(new RecipientAddress(aci.uuid(), number), false, false);
 163         }
 164         return byNumber.get().id();
 165     }
 166
 167     public RecipientId resolveRecipient(RecipientAddress address) {
 168         return resolveRecipient(address, false, false);
 169     }
 170
 171     @Override
 172     public RecipientId resolveSelfRecipientTrusted(RecipientAddress address) {
 173         return resolveRecipient(address, true, true);
 174     }
 175
 176     public RecipientId resolveRecipientTrusted(RecipientAddress address) {
 177         return resolveRecipient(address, true, false);
 178     }
 179
 180     @Override
 181     public RecipientId resolveRecipientTrusted(SignalServiceAddress address) {
 182         return resolveRecipient(new RecipientAddress(address), true, false);
 183     }
 184
 185     @Override
 186     public void storeContact(RecipientId recipientId, final Contact contact) {
 187         try (final var connection = database.getConnection()) {
 188             storeContact(connection, recipientId, contact);
 189         } catch (SQLException e) {
 190             throw new RuntimeException("Failed update recipient store", e);
 191         }
 192     }
 193
 194     @Override
 195     public Contact getContact(RecipientId recipientId) {
 196         try (final var connection = database.getConnection()) {
 197             return getContact(connection, recipientId);
 198         } catch (SQLException e) {
 199             throw new RuntimeException("Failed read from recipient store", e);
 200         }
 201     }
 202
 203     @Override
 204     public List<Pair<RecipientId, Contact>> getContacts() {
 205         final var sql = (
 206                 """
 207                 SELECT r._id, r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 208                 FROM %s r
 209                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 210                 """
 211         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 212         try (final var connection = database.getConnection()) {
 213             try (final var statement = connection.prepareStatement(sql)) {
 214                 try (var result = Utils.executeQueryForStream(statement,
 215                         resultSet -> new Pair<>(getRecipientIdFromResultSet(resultSet),
 216                                 getContactFromResultSet(resultSet)))) {
 217                     return result.toList();
 218                 }
 219             }
 220         } catch (SQLException e) {
 221             throw new RuntimeException("Failed read from recipient store", e);
 222         }
 223     }
 224
 225     public List<Recipient> getRecipients(
 226             boolean onlyContacts, Optional<Boolean> blocked, Set<RecipientId> recipientIds, Optional<String> name
 227     ) {
 228         final var sqlWhere = new ArrayList<String>();
 229         if (onlyContacts) {
 230             sqlWhere.add("(" + SQL_IS_CONTACT + ")");
 231         }
 232         if (blocked.isPresent()) {
 233             sqlWhere.add("r.blocked = ?");
 234         }
 235         if (!recipientIds.isEmpty()) {
 236             final var recipientIdsCommaSeparated = recipientIds.stream()
 237                     .map(recipientId -> String.valueOf(recipientId.id()))
 238                     .collect(Collectors.joining(","));
 239             sqlWhere.add("r._id IN (" + recipientIdsCommaSeparated + ")");
 240         }
 241         final var sql = (
 242                 """
 243                 SELECT r._id,
 244                        r.number, r.uuid,
 245                        r.profile_key, r.profile_key_credential,
 246                        r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived,
 247                        r.profile_last_update_timestamp, r.profile_given_name, r.profile_family_name, r.profile_about, r.profile_about_emoji, r.profile_avatar_url_path, r.profile_mobile_coin_address, r.profile_unidentified_access_mode, r.profile_capabilities
 248                 FROM %s r
 249                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 250                 """
 251         ).formatted(TABLE_RECIPIENT, sqlWhere.size() == 0 ? "TRUE" : String.join(" AND ", sqlWhere));
 252         try (final var connection = database.getConnection()) {
 253             try (final var statement = connection.prepareStatement(sql)) {
 254                 if (blocked.isPresent()) {
 255                     statement.setBoolean(1, blocked.get());
 256                 }
 257                 try (var result = Utils.executeQueryForStream(statement, this::getRecipientFromResultSet)) {
 258                     return result.filter(r -> name.isEmpty() || (
 259                             r.getContact() != null && name.get().equals(r.getContact().getName())
 260                     ) || (r.getProfile() != null && name.get().equals(r.getProfile().getDisplayName()))).toList();
 261                 }
 262             }
 263         } catch (SQLException e) {
 264             throw new RuntimeException("Failed read from recipient store", e);
 265         }
 266     }
 267
 268     @Override
 269     public void deleteContact(RecipientId recipientId) {
 270         storeContact(recipientId, null);
 271     }
 272
 273     public void deleteRecipientData(RecipientId recipientId) {
 274         logger.debug("Deleting recipient data for {}", recipientId);
 275         try (final var connection = database.getConnection()) {
 276             connection.setAutoCommit(false);
 277             storeContact(connection, recipientId, null);
 278             storeProfile(connection, recipientId, null);
 279             storeProfileKey(connection, recipientId, null, false);
 280             storeExpiringProfileKeyCredential(connection, recipientId, null);
 281             deleteRecipient(connection, recipientId);
 282             connection.commit();
 283         } catch (SQLException e) {
 284             throw new RuntimeException("Failed update recipient store", e);
 285         }
 286     }
 287
 288     @Override
 289     public Profile getProfile(final RecipientId recipientId) {
 290         try (final var connection = database.getConnection()) {
 291             return getProfile(connection, recipientId);
 292         } catch (SQLException e) {
 293             throw new RuntimeException("Failed read from recipient store", e);
 294         }
 295     }
 296
 297     @Override
 298     public ProfileKey getProfileKey(final RecipientId recipientId) {
 299         try (final var connection = database.getConnection()) {
 300             return getProfileKey(connection, recipientId);
 301         } catch (SQLException e) {
 302             throw new RuntimeException("Failed read from recipient store", e);
 303         }
 304     }
 305
 306     @Override
 307     public ExpiringProfileKeyCredential getExpiringProfileKeyCredential(final RecipientId recipientId) {
 308         try (final var connection = database.getConnection()) {
 309             return getExpiringProfileKeyCredential(connection, recipientId);
 310         } catch (SQLException e) {
 311             throw new RuntimeException("Failed read from recipient store", e);
 312         }
 313     }
 314
 315     @Override
 316     public void storeProfile(RecipientId recipientId, final Profile profile) {
 317         try (final var connection = database.getConnection()) {
 318             storeProfile(connection, recipientId, profile);
 319         } catch (SQLException e) {
 320             throw new RuntimeException("Failed update recipient store", e);
 321         }
 322     }
 323
 324     @Override
 325     public void storeSelfProfileKey(final RecipientId recipientId, final ProfileKey profileKey) {
 326         try (final var connection = database.getConnection()) {
 327             storeProfileKey(connection, recipientId, profileKey, false);
 328         } catch (SQLException e) {
 329             throw new RuntimeException("Failed update recipient store", e);
 330         }
 331     }
 332
 333     @Override
 334     public void storeProfileKey(RecipientId recipientId, final ProfileKey profileKey) {
 335         try (final var connection = database.getConnection()) {
 336             storeProfileKey(connection, recipientId, profileKey, true);
 337         } catch (SQLException e) {
 338             throw new RuntimeException("Failed update recipient store", e);
 339         }
 340     }
 341
 342     @Override
 343     public void storeExpiringProfileKeyCredential(
 344             RecipientId recipientId, final ExpiringProfileKeyCredential profileKeyCredential
 345     ) {
 346         try (final var connection = database.getConnection()) {
 347             storeExpiringProfileKeyCredential(connection, recipientId, profileKeyCredential);
 348         } catch (SQLException e) {
 349             throw new RuntimeException("Failed update recipient store", e);
 350         }
 351     }
 352
 353     void addLegacyRecipients(final Map<RecipientId, Recipient> recipients) {
 354         logger.debug("Migrating legacy recipients to database");
 355         long start = System.nanoTime();
 356         final var sql = (
 357                 """
 358                 INSERT INTO %s (_id, number, uuid)
 359                 VALUES (?, ?, ?)
 360                 """
 361         ).formatted(TABLE_RECIPIENT);
 362         try (final var connection = database.getConnection()) {
 363             connection.setAutoCommit(false);
 364             try (final var statement = connection.prepareStatement("DELETE FROM %s".formatted(TABLE_RECIPIENT))) {
 365                 statement.executeUpdate();
 366             }
 367             try (final var statement = connection.prepareStatement(sql)) {
 368                 for (final var recipient : recipients.values()) {
 369                     statement.setLong(1, recipient.getRecipientId().id());
 370                     statement.setString(2, recipient.getAddress().number().orElse(null));
 371                     statement.setBytes(3, recipient.getAddress().uuid().map(UuidUtil::toByteArray).orElse(null));
 372                     statement.executeUpdate();
 373                 }
 374             }
 375             logger.debug("Initial inserts took {}ms", (System.nanoTime() - start) / 1000000);
 376
 377             for (final var recipient : recipients.values()) {
 378                 if (recipient.getContact() != null) {
 379                     storeContact(connection, recipient.getRecipientId(), recipient.getContact());
 380                 }
 381                 if (recipient.getProfile() != null) {
 382                     storeProfile(connection, recipient.getRecipientId(), recipient.getProfile());
 383                 }
 384                 if (recipient.getProfileKey() != null) {
 385                     storeProfileKey(connection, recipient.getRecipientId(), recipient.getProfileKey(), false);
 386                 }
 387                 if (recipient.getExpiringProfileKeyCredential() != null) {
 388                     storeExpiringProfileKeyCredential(connection,
 389                             recipient.getRecipientId(),
 390                             recipient.getExpiringProfileKeyCredential());
 391                 }
 392             }
 393             connection.commit();
 394         } catch (SQLException e) {
 395             throw new RuntimeException("Failed update recipient store", e);
 396         }
 397         logger.debug("Complete recipients migration took {}ms", (System.nanoTime() - start) / 1000000);
 398     }
 399
 400     long getActualRecipientId(long recipientId) {
 401         while (recipientsMerged.containsKey(recipientId)) {
 402             final var newRecipientId = recipientsMerged.get(recipientId);
 403             logger.debug("Using {} instead of {}, because recipients have been merged", newRecipientId, recipientId);
 404             recipientId = newRecipientId;
 405         }
 406         return recipientId;
 407     }
 408
 409     private void storeContact(
 410             final Connection connection, final RecipientId recipientId, final Contact contact
 411     ) throws SQLException {
 412         final var sql = (
 413                 """
 414                 UPDATE %s
 415                 SET given_name = ?, family_name = ?, expiration_time = ?, profile_sharing = ?, color = ?, blocked = ?, archived = ?
 416                 WHERE _id = ?
 417                 """
 418         ).formatted(TABLE_RECIPIENT);
 419         try (final var statement = connection.prepareStatement(sql)) {
 420             statement.setString(1, contact == null ? null : contact.getGivenName());
 421             statement.setString(2, contact == null ? null : contact.getFamilyName());
 422             statement.setInt(3, contact == null ? 0 : contact.getMessageExpirationTime());
 423             statement.setBoolean(4, contact != null && contact.isProfileSharingEnabled());
 424             statement.setString(5, contact == null ? null : contact.getColor());
 425             statement.setBoolean(6, contact != null && contact.isBlocked());
 426             statement.setBoolean(7, contact != null && contact.isArchived());
 427             statement.setLong(8, recipientId.id());
 428             statement.executeUpdate();
 429         }
 430     }
 431
 432     private void storeExpiringProfileKeyCredential(
 433             final Connection connection,
 434             final RecipientId recipientId,
 435             final ExpiringProfileKeyCredential profileKeyCredential
 436     ) throws SQLException {
 437         final var sql = (
 438                 """
 439                 UPDATE %s
 440                 SET profile_key_credential = ?
 441                 WHERE _id = ?
 442                 """
 443         ).formatted(TABLE_RECIPIENT);
 444         try (final var statement = connection.prepareStatement(sql)) {
 445             statement.setBytes(1, profileKeyCredential == null ? null : profileKeyCredential.serialize());
 446             statement.setLong(2, recipientId.id());
 447             statement.executeUpdate();
 448         }
 449     }
 450
 451     private void storeProfile(
 452             final Connection connection, final RecipientId recipientId, final Profile profile
 453     ) throws SQLException {
 454         final var sql = (
 455                 """
 456                 UPDATE %s
 457                 SET profile_last_update_timestamp = ?, profile_given_name = ?, profile_family_name = ?, profile_about = ?, profile_about_emoji = ?, profile_avatar_url_path = ?, profile_mobile_coin_address = ?, profile_unidentified_access_mode = ?, profile_capabilities = ?
 458                 WHERE _id = ?
 459                 """
 460         ).formatted(TABLE_RECIPIENT);
 461         try (final var statement = connection.prepareStatement(sql)) {
 462             statement.setLong(1, profile == null ? 0 : profile.getLastUpdateTimestamp());
 463             statement.setString(2, profile == null ? null : profile.getGivenName());
 464             statement.setString(3, profile == null ? null : profile.getFamilyName());
 465             statement.setString(4, profile == null ? null : profile.getAbout());
 466             statement.setString(5, profile == null ? null : profile.getAboutEmoji());
 467             statement.setString(6, profile == null ? null : profile.getAvatarUrlPath());
 468             statement.setBytes(7, profile == null ? null : profile.getMobileCoinAddress());
 469             statement.setString(8, profile == null ? null : profile.getUnidentifiedAccessMode().name());
 470             statement.setString(9,
 471                     profile == null
 472                             ? null
 473                             : profile.getCapabilities().stream().map(Enum::name).collect(Collectors.joining(",")));
 474             statement.setLong(10, recipientId.id());
 475             statement.executeUpdate();
 476         }
 477     }
 478
 479     private void storeProfileKey(
 480             Connection connection, RecipientId recipientId, final ProfileKey profileKey, boolean resetProfile
 481     ) throws SQLException {
 482         if (profileKey != null) {
 483             final var recipientProfileKey = getProfileKey(recipientId);
 484             if (profileKey.equals(recipientProfileKey)) {
 485                 final var recipientProfile = getProfile(recipientId);
 486                 if (recipientProfile == null || (
 487                         recipientProfile.getUnidentifiedAccessMode() != Profile.UnidentifiedAccessMode.UNKNOWN
 488                                 && recipientProfile.getUnidentifiedAccessMode()
 489                                 != Profile.UnidentifiedAccessMode.DISABLED
 490                 )) {
 491                     return;
 492                 }
 493             }
 494         }
 495
 496         final var sql = (
 497                 """
 498                 UPDATE %s
 499                 SET profile_key = ?, profile_key_credential = NULL%s
 500                 WHERE _id = ?
 501                 """
 502         ).formatted(TABLE_RECIPIENT, resetProfile ? ", profile_last_update_timestamp = 0" : "");
 503         try (final var statement = connection.prepareStatement(sql)) {
 504             statement.setBytes(1, profileKey == null ? null : profileKey.serialize());
 505             statement.setLong(2, recipientId.id());
 506             statement.executeUpdate();
 507         }
 508     }
 509
 510     /**
 511      * @param isHighTrust true, if the number/uuid connection was obtained from a trusted source.
 512      *                    Has no effect, if the address contains only a number or a uuid.
 513      */
 514     private RecipientId resolveRecipient(RecipientAddress address, boolean isHighTrust, boolean isSelf) {
 515         final Pair<RecipientId, Optional<RecipientId>> pair;
 516         synchronized (recipientsLock) {
 517             try (final var connection = database.getConnection()) {
 518                 connection.setAutoCommit(false);
 519                 pair = resolveRecipientLocked(connection, address, isHighTrust, isSelf);
 520                 connection.commit();
 521             } catch (SQLException e) {
 522                 throw new RuntimeException("Failed update recipient store", e);
 523             }
 524         }
 525
 526         if (pair.second().isPresent()) {
 527             recipientMergeHandler.mergeRecipients(pair.first(), pair.second().get());
 528             try (final var connection = database.getConnection()) {
 529                 deleteRecipient(connection, pair.second().get());
 530             } catch (SQLException e) {
 531                 throw new RuntimeException("Failed update recipient store", e);
 532             }
 533         }
 534         return pair.first();
 535     }
 536
 537     private Pair<RecipientId, Optional<RecipientId>> resolveRecipientLocked(
 538             Connection connection, RecipientAddress address, boolean isHighTrust, boolean isSelf
 539     ) throws SQLException {
 540         if (isHighTrust && !isSelf) {
 541             if (selfAddressProvider.getSelfAddress().matches(address)) {
 542                 isHighTrust = false;
 543             }
 544         }
 545         final var byNumber = address.number().isEmpty()
 546                 ? Optional.<RecipientWithAddress>empty()
 547                 : findByNumber(connection, address.number().get());
 548         final var byUuid = address.uuid().isEmpty()
 549                 ? Optional.<RecipientWithAddress>empty()
 550                 : findByUuid(connection, address.uuid().get());
 551
 552         if (byNumber.isEmpty() && byUuid.isEmpty()) {
 553             logger.debug("Got new recipient, both uuid and number are unknown");
 554
 555             if (isHighTrust || address.uuid().isEmpty() || address.number().isEmpty()) {
 556                 return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 557             }
 558
 559             return new Pair<>(addNewRecipient(connection, new RecipientAddress(address.uuid().get())),
 560                     Optional.empty());
 561         }
 562
 563         if (!isHighTrust || address.uuid().isEmpty() || address.number().isEmpty() || byNumber.equals(byUuid)) {
 564             return new Pair<>(byUuid.or(() -> byNumber).map(RecipientWithAddress::id).get(), Optional.empty());
 565         }
 566
 567         if (byNumber.isEmpty()) {
 568             logger.debug("Got recipient {} existing with uuid, updating with high trust number", byUuid.get().id());
 569             updateRecipientAddress(connection, byUuid.get().id(), address);
 570             return new Pair<>(byUuid.get().id(), Optional.empty());
 571         }
 572
 573         final var byNumberRecipient = byNumber.get();
 574
 575         if (byUuid.isEmpty()) {
 576             if (byNumberRecipient.address().uuid().isPresent()) {
 577                 logger.debug(
 578                         "Got recipient {} existing with number, but different uuid, so stripping its number and adding new recipient",
 579                         byNumberRecipient.id());
 580
 581                 updateRecipientAddress(connection,
 582                         byNumberRecipient.id(),
 583                         new RecipientAddress(byNumberRecipient.address().uuid().get()));
 584                 return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 585             }
 586
 587             logger.debug("Got recipient {} existing with number and no uuid, updating with high trust uuid",
 588                     byNumberRecipient.id());
 589             updateRecipientAddress(connection, byNumberRecipient.id(), address);
 590             return new Pair<>(byNumberRecipient.id(), Optional.empty());
 591         }
 592
 593         final var byUuidRecipient = byUuid.get();
 594
 595         if (byNumberRecipient.address().uuid().isPresent()) {
 596             logger.debug(
 597                     "Got separate recipients for high trust number {} and uuid {}, recipient for number has different uuid, so stripping its number",
 598                     byNumberRecipient.id(),
 599                     byUuidRecipient.id());
 600
 601             updateRecipientAddress(connection,
 602                     byNumberRecipient.id(),
 603                     new RecipientAddress(byNumberRecipient.address().uuid().get()));
 604             updateRecipientAddress(connection, byUuidRecipient.id(), address);
 605             return new Pair<>(byUuidRecipient.id(), Optional.empty());
 606         }
 607
 608         logger.debug("Got separate recipients for high trust number {} and uuid {}, need to merge them",
 609                 byNumberRecipient.id(),
 610                 byUuidRecipient.id());
 611         // Create a fixed RecipientId that won't update its id after merge
 612         final var toBeMergedRecipientId = new RecipientId(byNumberRecipient.id().id(), null);
 613         mergeRecipientsLocked(connection, byUuidRecipient.id(), toBeMergedRecipientId);
 614         removeRecipientAddress(connection, toBeMergedRecipientId);
 615         updateRecipientAddress(connection, byUuidRecipient.id(), address);
 616         return new Pair<>(byUuidRecipient.id(), Optional.of(toBeMergedRecipientId));
 617     }
 618
 619     private RecipientId addNewRecipient(
 620             final Connection connection, final RecipientAddress address
 621     ) throws SQLException {
 622         final var sql = (
 623                 """
 624                 INSERT INTO %s (number, uuid)
 625                 VALUES (?, ?)
 626                 """
 627         ).formatted(TABLE_RECIPIENT);
 628         try (final var statement = connection.prepareStatement(sql)) {
 629             statement.setString(1, address.number().orElse(null));
 630             statement.setBytes(2, address.uuid().map(UuidUtil::toByteArray).orElse(null));
 631             statement.executeUpdate();
 632             final var generatedKeys = statement.getGeneratedKeys();
 633             if (generatedKeys.next()) {
 634                 final var recipientId = new RecipientId(generatedKeys.getLong(1), this);
 635                 logger.debug("Added new recipient {} with address {}", recipientId, address);
 636                 return recipientId;
 637             } else {
 638                 throw new RuntimeException("Failed to add new recipient to database");
 639             }
 640         }
 641     }
 642
 643     private void removeRecipientAddress(Connection connection, RecipientId recipientId) throws SQLException {
 644         final var sql = (
 645                 """
 646                 UPDATE %s
 647                 SET number = NULL, uuid = NULL
 648                 WHERE _id = ?
 649                 """
 650         ).formatted(TABLE_RECIPIENT);
 651         try (final var statement = connection.prepareStatement(sql)) {
 652             statement.setLong(1, recipientId.id());
 653             statement.executeUpdate();
 654         }
 655     }
 656
 657     private void updateRecipientAddress(
 658             Connection connection, RecipientId recipientId, final RecipientAddress address
 659     ) throws SQLException {
 660         final var sql = (
 661                 """
 662                 UPDATE %s
 663                 SET number = ?, uuid = ?
 664                 WHERE _id = ?
 665                 """
 666         ).formatted(TABLE_RECIPIENT);
 667         try (final var statement = connection.prepareStatement(sql)) {
 668             statement.setString(1, address.number().orElse(null));
 669             statement.setBytes(2, address.uuid().map(UuidUtil::toByteArray).orElse(null));
 670             statement.setLong(3, recipientId.id());
 671             statement.executeUpdate();
 672         }
 673     }
 674
 675     private void deleteRecipient(final Connection connection, final RecipientId recipientId) throws SQLException {
 676         final var sql = (
 677                 """
 678                 DELETE FROM %s
 679                 WHERE _id = ?
 680                 """
 681         ).formatted(TABLE_RECIPIENT);
 682         try (final var statement = connection.prepareStatement(sql)) {
 683             statement.setLong(1, recipientId.id());
 684             statement.executeUpdate();
 685         }
 686     }
 687
 688     private void mergeRecipientsLocked(
 689             Connection connection, RecipientId recipientId, RecipientId toBeMergedRecipientId
 690     ) throws SQLException {
 691         final var contact = getContact(connection, recipientId);
 692         if (contact == null) {
 693             final var toBeMergedContact = getContact(connection, toBeMergedRecipientId);
 694             storeContact(connection, recipientId, toBeMergedContact);
 695         }
 696
 697         final var profileKey = getProfileKey(connection, recipientId);
 698         if (profileKey == null) {
 699             final var toBeMergedProfileKey = getProfileKey(connection, toBeMergedRecipientId);
 700             storeProfileKey(connection, recipientId, toBeMergedProfileKey, false);
 701         }
 702
 703         final var profileKeyCredential = getExpiringProfileKeyCredential(connection, recipientId);
 704         if (profileKeyCredential == null) {
 705             final var toBeMergedProfileKeyCredential = getExpiringProfileKeyCredential(connection, toBeMergedRecipientId);
 706             storeExpiringProfileKeyCredential(connection, recipientId, toBeMergedProfileKeyCredential);
 707         }
 708
 709         final var profile = getProfile(connection, recipientId);
 710         if (profile == null) {
 711             final var toBeMergedProfile = getProfile(connection, toBeMergedRecipientId);
 712             storeProfile(connection, recipientId, toBeMergedProfile);
 713         }
 714
 715         recipientsMerged.put(toBeMergedRecipientId.id(), recipientId.id());
 716     }
 717
 718     private Optional<RecipientWithAddress> findByNumber(
 719             final Connection connection, final String number
 720     ) throws SQLException {
 721         final var sql = """
 722                         SELECT r._id, r.number, r.uuid
 723                         FROM %s r
 724                         WHERE r.number = ?
 725                         """.formatted(TABLE_RECIPIENT);
 726         try (final var statement = connection.prepareStatement(sql)) {
 727             statement.setString(1, number);
 728             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 729         }
 730     }
 731
 732     private Optional<RecipientWithAddress> findByUuid(
 733             final Connection connection, final UUID uuid
 734     ) throws SQLException {
 735         final var sql = """
 736                         SELECT r._id, r.number, r.uuid
 737                         FROM %s r
 738                         WHERE r.uuid = ?
 739                         """.formatted(TABLE_RECIPIENT);
 740         try (final var statement = connection.prepareStatement(sql)) {
 741             statement.setBytes(1, UuidUtil.toByteArray(uuid));
 742             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 743         }
 744     }
 745
 746     private Contact getContact(final Connection connection, final RecipientId recipientId) throws SQLException {
 747         final var sql = (
 748                 """
 749                 SELECT r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 750                 FROM %s r
 751                 WHERE r._id = ? AND (%s)
 752                 """
 753         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 754         try (final var statement = connection.prepareStatement(sql)) {
 755             statement.setLong(1, recipientId.id());
 756             return Utils.executeQueryForOptional(statement, this::getContactFromResultSet).orElse(null);
 757         }
 758     }
 759
 760     private ProfileKey getProfileKey(final Connection connection, final RecipientId recipientId) throws SQLException {
 761         final var sql = (
 762                 """
 763                 SELECT r.profile_key
 764                 FROM %s r
 765                 WHERE r._id = ?
 766                 """
 767         ).formatted(TABLE_RECIPIENT);
 768         try (final var statement = connection.prepareStatement(sql)) {
 769             statement.setLong(1, recipientId.id());
 770             return Utils.executeQueryForOptional(statement, this::getProfileKeyFromResultSet).orElse(null);
 771         }
 772     }
 773
 774     private ExpiringProfileKeyCredential getExpiringProfileKeyCredential(
 775             final Connection connection, final RecipientId recipientId
 776     ) throws SQLException {
 777         final var sql = (
 778                 """
 779                 SELECT r.profile_key_credential
 780                 FROM %s r
 781                 WHERE r._id = ?
 782                 """
 783         ).formatted(TABLE_RECIPIENT);
 784         try (final var statement = connection.prepareStatement(sql)) {
 785             statement.setLong(1, recipientId.id());
 786             return Utils.executeQueryForOptional(statement, this::getExpiringProfileKeyCredentialFromResultSet)
 787                     .orElse(null);
 788         }
 789     }
 790
 791     private Profile getProfile(final Connection connection, final RecipientId recipientId) throws SQLException {
 792         final var sql = (
 793                 """
 794                 SELECT r.profile_last_update_timestamp, r.profile_given_name, r.profile_family_name, r.profile_about, r.profile_about_emoji, r.profile_avatar_url_path, r.profile_mobile_coin_address, r.profile_unidentified_access_mode, r.profile_capabilities
 795                 FROM %s r
 796                 WHERE r._id = ? AND r.profile_capabilities IS NOT NULL
 797                 """
 798         ).formatted(TABLE_RECIPIENT);
 799         try (final var statement = connection.prepareStatement(sql)) {
 800             statement.setLong(1, recipientId.id());
 801             return Utils.executeQueryForOptional(statement, this::getProfileFromResultSet).orElse(null);
 802         }
 803     }
 804
 805     private RecipientAddress getRecipientAddressFromResultSet(ResultSet resultSet) throws SQLException {
 806         final var uuid = Optional.ofNullable(resultSet.getBytes("uuid")).map(UuidUtil::parseOrNull);
 807         final var number = Optional.ofNullable(resultSet.getString("number"));
 808         return new RecipientAddress(uuid, number);
 809     }
 810
 811     private RecipientId getRecipientIdFromResultSet(ResultSet resultSet) throws SQLException {
 812         return new RecipientId(resultSet.getLong("_id"), this);
 813     }
 814
 815     private RecipientWithAddress getRecipientWithAddressFromResultSet(final ResultSet resultSet) throws SQLException {
 816         return new RecipientWithAddress(getRecipientIdFromResultSet(resultSet),
 817                 getRecipientAddressFromResultSet(resultSet));
 818     }
 819
 820     private Recipient getRecipientFromResultSet(final ResultSet resultSet) throws SQLException {
 821         return new Recipient(getRecipientIdFromResultSet(resultSet),
 822                 getRecipientAddressFromResultSet(resultSet),
 823                 getContactFromResultSet(resultSet),
 824                 getProfileKeyFromResultSet(resultSet),
 825                 getExpiringProfileKeyCredentialFromResultSet(resultSet),
 826                 getProfileFromResultSet(resultSet));
 827     }
 828
 829     private Contact getContactFromResultSet(ResultSet resultSet) throws SQLException {
 830         return new Contact(resultSet.getString("given_name"),
 831                 resultSet.getString("family_name"),
 832                 resultSet.getString("color"),
 833                 resultSet.getInt("expiration_time"),
 834                 resultSet.getBoolean("blocked"),
 835                 resultSet.getBoolean("archived"),
 836                 resultSet.getBoolean("profile_sharing"));
 837     }
 838
 839     private Profile getProfileFromResultSet(ResultSet resultSet) throws SQLException {
 840         final var profileCapabilities = resultSet.getString("profile_capabilities");
 841         final var profileUnidentifiedAccessMode = resultSet.getString("profile_unidentified_access_mode");
 842         return new Profile(resultSet.getLong("profile_last_update_timestamp"),
 843                 resultSet.getString("profile_given_name"),
 844                 resultSet.getString("profile_family_name"),
 845                 resultSet.getString("profile_about"),
 846                 resultSet.getString("profile_about_emoji"),
 847                 resultSet.getString("profile_avatar_url_path"),
 848                 resultSet.getBytes("profile_mobile_coin_address"),
 849                 profileUnidentifiedAccessMode == null
 850                         ? Profile.UnidentifiedAccessMode.UNKNOWN
 851                         : Profile.UnidentifiedAccessMode.valueOfOrUnknown(profileUnidentifiedAccessMode),
 852                 profileCapabilities == null
 853                         ? Set.of()
 854                         : Arrays.stream(profileCapabilities.split(","))
 855                                 .map(Profile.Capability::valueOfOrNull)
 856                                 .filter(Objects::nonNull)
 857                                 .collect(Collectors.toSet()));
 858     }
 859
 860     private ProfileKey getProfileKeyFromResultSet(ResultSet resultSet) throws SQLException {
 861         final var profileKey = resultSet.getBytes("profile_key");
 862
 863         if (profileKey == null) {
 864             return null;
 865         }
 866         try {
 867             return new ProfileKey(profileKey);
 868         } catch (InvalidInputException ignored) {
 869             return null;
 870         }
 871     }
 872
 873     private ExpiringProfileKeyCredential getExpiringProfileKeyCredentialFromResultSet(ResultSet resultSet) throws SQLException {
 874         final var profileKeyCredential = resultSet.getBytes("profile_key_credential");
 875
 876         if (profileKeyCredential == null) {
 877             return null;
 878         }
 879         try {
 880             return new ExpiringProfileKeyCredential(profileKeyCredential);
 881         } catch (Throwable ignored) {
 882             return null;
 883         }
 884     }
 885
 886     public interface RecipientMergeHandler {
 887
 888         void mergeRecipients(RecipientId recipientId, RecipientId toBeMergedRecipientId);
 889     }
 890
 891     private record RecipientWithAddress(RecipientId id, RecipientAddress address) {}
 892 }