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