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