//
// Copyright 2019 Signal Messenger, LLC
// SPDX-License-Identifier: AGPL-3.0-only
//

import Foundation
import GRDB
import LibSignalClient

public class GRDBSchemaMigrator {

    public static let migrationSideEffectsCollectionName = "MigrationSideEffects"
    public static let avatarRepairAttemptCount = "Avatar Repair Attempt Count"

    /// Migrate a database to the latest version. Throws if migrations fail.
    ///
    /// - Parameter databaseStorage: The database to migrate.
    /// - Parameter runDataMigrations: A boolean indicating whether to include data migrations. Typically, you want to omit this value or set it to `true`, but we want to skip them when recovering a corrupted database.
    /// - Returns: `true` if incremental migrations were performed, and `false` otherwise.
    @discardableResult
    static func migrateDatabase(
        databaseStorage: SDSDatabaseStorage,
        runDataMigrations: Bool = true,
    ) throws -> Bool {
        return try runIncrementalMigrations(databaseStorage: databaseStorage, runDataMigrations: runDataMigrations)
    }

    private static func runIncrementalMigrations(
        databaseStorage: SDSDatabaseStorage,
        runDataMigrations: Bool,
    ) throws -> Bool {
        return try _runIncrementalMigrations(
            databaseWriter: databaseStorage.grdbStorage.pool,
            runDataMigrations: runDataMigrations,
        )
    }

#if TESTABLE_BUILD
    static func runIncrementalMigrations(databaseWriter: some DatabaseWriter) throws {
        _ = try _runIncrementalMigrations(databaseWriter: databaseWriter, runDataMigrations: false)
    }
#endif

    private static func _runIncrementalMigrations(
        databaseWriter: some DatabaseWriter,
        runDataMigrations: Bool,
    ) throws -> Bool {
        let previouslyAppliedMigrations = try databaseWriter.read { database in
            try DatabaseMigrator().appliedIdentifiers(database)
        }

        // First do the schema migrations. (See the comment within MigrationId for why schema and data
        // migrations are separate.)
        let incrementalMigrator = DatabaseMigratorWrapper()
        registerSchemaMigrations(migrator: incrementalMigrator)
        try incrementalMigrator.migrate(databaseWriter)

        if runDataMigrations {
            // Finally, do data migrations.
            registerDataMigrations(migrator: incrementalMigrator)
            try incrementalMigrator.migrate(databaseWriter)
        }

        let allAppliedMigrations = try databaseWriter.read { database in
            try DatabaseMigrator().appliedIdentifiers(database)
        }

        return allAppliedMigrations != previouslyAppliedMigrations
    }

    // MARK: -

    private enum MigrationId: String, CaseIterable {
        case createInitialSchema
        case signalAccount_add_contactAvatars
        case signalAccount_add_contactAvatars_indices
        case jobRecords_add_attachmentId
        case createMediaGalleryItems
        case createReaction
        case dedupeSignalRecipients
        case unreadThreadInteractions
        case createFamilyName
        case createIndexableFTSTable
        case dropContactQuery
        case indexFailedJob
        case groupsV2MessageJobs
        case addUserInfoToInteractions
        case recreateExperienceUpgradeWithNewColumns
        case recreateExperienceUpgradeIndex
        case indexInfoMessageOnType_v2
        case createPendingReadReceipts
        case createInteractionAttachmentIdsIndex
        case addIsUuidCapableToUserProfiles
        case uploadTimestamp
        case addRemoteDeleteToInteractions
        case cdnKeyAndCdnNumber
        case addGroupIdToGroupsV2IncomingMessageJobs
        case removeEarlyReceiptTables
        case addReadToReactions
        case addIsMarkedUnreadToThreads
        case addIsMediaMessageToMessageSenderJobQueue
        case readdAttachmentIndex
        case addLastVisibleRowIdToThreads
        case addMarkedUnreadIndexToThread
        case fixIncorrectIndexes
        case resetThreadVisibility
        case trackUserProfileFetches
        case addMentions
        case addMentionNotificationMode
        case addOfferTypeToCalls
        case addServerDeliveryTimestamp
        case updateAnimatedStickers
        case updateMarkedUnreadIndex
        case addGroupCallMessage2
        case addGroupCallEraIdIndex
        case addProfileBio
        case addWasIdentityVerified
        case storeMutedUntilDateAsMillisecondTimestamp
        case addPaymentModels15
        case addPaymentModels40
        case fixPaymentModels
        case addGroupMember
        case createPendingViewedReceipts
        case addViewedToInteractions
        case createThreadAssociatedData
        case addServerGuidToInteractions
        case addMessageSendLog
        case updatePendingReadReceipts
        case addSendCompletionToMessageSendLog
        case addExclusiveProcessIdentifierAndHighPriorityToJobRecord
        case updateMessageSendLogColumnTypes
        case addRecordTypeIndex
        case tunedConversationLoadIndices
        case messageDecryptDeduplicationV6
        case createProfileBadgeTable
        case createSubscriptionDurableJob
        case addReceiptPresentationToSubscriptionDurableJob
        case createStoryMessageTable
        case addColumnsForStoryContextRedux
        case addIsStoriesCapableToUserProfiles
        case createDonationReceiptTable
        case addBoostAmountToSubscriptionDurableJob
        case updateConversationLoadInteractionCountIndex
        case updateConversationLoadInteractionDistanceIndex
        case updateConversationUnreadCountIndex
        case addStoryContextIndexToInteractions
        case improvedDisappearingMessageIndices
        case addProfileBadgeDuration
        case addGiftBadges
        case addCanReceiveGiftBadgesToUserProfiles
        case addStoryThreadColumns
        case addUnsavedMessagesToSendToJobRecord
        case addColumnsForSendGiftBadgeDurableJob
        case addDonationReceiptTypeColumn
        case addAudioPlaybackRateColumn
        case addSchemaVersionToAttachments
        case makeAudioPlaybackRateColumnNonNull
        case addLastViewedStoryTimestampToTSThread
        case convertStoryIncomingManifestStorageFormat
        case recreateStoryIncomingViewedTimestampIndex
        case addColumnsForLocalUserLeaveGroupDurableJob
        case addStoriesHiddenStateToThreadAssociatedData
        case addUnregisteredAtTimestampToSignalRecipient
        case addLastReceivedStoryTimestampToTSThread
        case addStoryContextAssociatedDataTable
        case populateStoryContextAssociatedDataTableAndRemoveOldColumns
        case addColumnForExperienceUpgradeManifest
        case addStoryContextAssociatedDataReadTimestampColumn
        case addIsCompleteToContactSyncJob
        case addSnoozeCountToExperienceUpgrade
        case addCancelledGroupRingsTable
        case addPaymentProcessorColumnToJobRecords
        case addCdsPreviousE164
        case addCallRecordTable
        case addColumnsForGiftingWithPaypalToJobRecords
        case addSpamReportingTokenRecordTable
        case addVideoDuration
        case addUsernameLookupRecordsTable
        case dropUsernameColumnFromOWSUserProfile
        case migrateVoiceMessageDrafts
        case addIsPniCapableColumnToOWSUserProfile
        case addStoryMessageReplyCount
        case populateStoryMessageReplyCount
        case addIndexToFindFailedAttachments
        case addEditMessageChanges
        case dropMessageSendLogTriggers
        case threadReplyInfoServiceIds
        case updateEditMessageUnreadIndex
        case updateEditRecordTable
        case threadReplyEditTarget
        case addHiddenRecipientsTable
        case editRecordReadState
        case addPaymentModelInteractionUniqueId
        case addPaymentsActivationRequestModel
        case addRecipientPniColumn
        case deletePhoneNumberAccessStore
        case dropOldAndCreateNewCallRecordTable
        case fixUniqueConstraintOnCallRecord
        case addTimestampToCallRecord
        case addPaymentMethodToJobRecords
        case addIsNewSubscriptionToJobRecords
        case enableFts5SecureDelete
        case addShouldSuppressPaymentAlreadyRedeemedToJobRecords
        case addGroupCallRingerAciToCallRecords
        case renameIsFromLinkedDevice
        case renameAndDeprecateSourceDeviceId
        case addCallRecordQueryIndices
        case addDeletedCallRecordTable
        case addFirstDeletedIndexToDeletedCallRecord
        case addCallRecordDeleteAllColumnsToJobRecord
        case addPhoneNumberSharingAndDiscoverability
        case removeRedundantPhoneNumbers2
        case scheduleFullIntersection
        case addUnreadToCallRecord
        case addSearchableName
        case addCallRecordRowIdColumnToCallRecordDeleteAllJobRecord
        case markAllGroupCallMessagesAsRead
        case addIndexForUnreadByThreadRowIdToCallRecord
        case addNicknamesTable
        case expandSignalAccountContactFields
        case addNicknamesToSearchableName
        case addAttachmentMetadataColumnsToIncomingContactSyncJobRecord
        case removeRedundantPhoneNumbers3
        case addV2AttachmentTable
        case addBulkDeleteInteractionJobRecord
        case cleanUpThreadIndexes
        case addOrphanAttachmentPendingColumn
        case cleanUpUniqueIndexes
        case dropTableTestModel
        case addOriginalAttachmentIdForQuotedReplyColumn
        case addClientUuidToTSAttachment
        case recreateMessageAttachmentReferenceMediaGalleryIndexes
        case addAttachmentDownloadQueue
        case attachmentAddCdnUnencryptedByteCounts
        case addArchivedPaymentInfoColumn
        case createArchivedPaymentTable
        case removeDeadEndGroupThreadIdMappings
        case addTSAttachmentMigrationTable
        case indexMessageAttachmentReferenceByReceivedAtTimestamp
        case addBackupAttachmentDownloadQueue
        case createAttachmentUploadRecordTable
        case addBlockedRecipient
        case addDmTimerVersionColumn
        case addVersionedDMTimer
        case addDMTimerVersionToInteractionTable
        case initializeDMTimerVersion
        case attachmentAddMediaTierDigest
        case removeVoIPToken
        case reorderMediaTierDigestColumn
        case addIncrementalMacParamsToAttachment
        case splitIncrementalMacAttachmentColumns
        case addCallEndedTimestampToCallRecord
        case addIsViewOnceColumnToMessageAttachmentReference
        case backfillIsViewOnceMessageAttachmentReference
        case addAttachmentValidationBackfillTable
        case addIsSmsColumnToTSAttachment
        case addInKnownMessageRequestStateToHiddenRecipient
        case addBackupAttachmentUploadQueue
        case addBackupStickerPackDownloadQueue
        case createOrphanedBackupAttachmentTable
        case addCallLinkTable
        case deleteIncomingGroupSyncJobRecords
        case deleteKnownStickerPackTable
        case addReceiptCredentialColumnToJobRecord
        case dropOrphanedGroupStoryReplies
        case addMessageBackupAvatarFetchQueue
        case addMessageBackupAvatarFetchQueueRetries
        case addEditStateToMessageAttachmentReference
        case removeVersionedDMTimerCapabilities
        case removeJobRecordTSAttachmentColumns
        case deprecateAttachmentIdsColumn
        case dropMediaGalleryItemTable
        case addBackupsReceiptCredentialStateToJobRecord
        case recreateTSAttachment
        case recreateTSAttachmentMigration
        case addBlockedGroup
        case addGroupSendEndorsement
        case deleteLegacyMessageDecryptJobRecords
        case dropMessageContentJobTable
        case deleteMessageRequestInteractionEpoch
        case addAvatarDefaultColorTable
        case populateAvatarDefaultColorTable
        case addStoryRecipient
        case addAttachmentLastFullscreenViewTimestamp
        case addByteCountAndIsFullsizeToBackupAttachmentUpload
        case refactorBackupAttachmentDownload
        case removeAttachmentMediaTierDigestColumn
        case addListMediaTable
        case recomputeAttachmentMediaNames
        case lastDraftInteractionRowID
        case addBackupOversizeText
        case addBackupOversizeTextRedux
        case addRetriesToBackupAttachmentUploadQueue
        case replaceOWSDeviceTable
        case reindexBackupAttachmentUploadQueue
        case dropAllIncrementalMacs
        case addOriginalTransitTierInfoAttachmentColumns
        case rebuildIncompleteViewOnceIndex
        case addIsPollToTSInteraction
        case addBackupAttachmentUploadQueueStateColumn
        case addBackupAttachmentUploadQueueTrigger
        case migrateRecipientDeviceIds
        case fixUniqueConstraintOnPollVotes
        case migrateTSAccountManagerKeyValueStore
        case populateBackupAttachmentUploadProgressKVStore
        case addPollVoteState
        case addPreKey
        case addKyberPreKeyUse
        case uniquifyUsernameLookupRecord
        case fixUpcomingCallLinks
        case uniquifyUsernameLookupRecord2
        case fixRevokedForRestoredCallLinks
        case fixNameForRestoredCallLinks
        case addPinnedMessagesTable
        case addPinnedAtTimestampToPinnedMessageTable
        case dropTSAttachment
        case deprecateStoredShouldStartExpireTimer
        case addSession
        case addRecipientStatus
        case createKeyTransparencyTable
        case addAdminDeleteTable
        case addRecipientStatesToAdminDelete
        case modifyCallLinkRootKeyConstraint
        case addDevice
        case addAttachmentBackfillRequestTable
        case wipeBackupAttachmentUploadQueueForLinkedDevices
        case addLastVerifiedGroupNameHashColumn
        case setAttachmentContentTypeFromMimeType
        case dropAttachmentContentTypeAUTrigger
        case addOrphanedAttachmentTimestamp

        // NOTE: Every time we add a migration id, consider
        // incrementing grdbSchemaVersionLatest.
        // We only need to do this for breaking changes.

        // MARK: Data Migrations

        //
        // Any migration which leverages SDSModel serialization must occur *after* changes to the
        // database schema complete.
        //
        // Otherwise, for example, consider we have these two pending migrations:
        //  - Migration 1: resaves all instances of Foo (Foo is some SDSModel)
        //  - Migration 2: adds a column "new_column" to the "model_Foo" table
        //
        // Migration 1 will fail, because the generated serialization logic for Foo expects
        // "new_column" to already exist before Migration 2 has even run.
        //
        // The solution is to always split logic that leverages SDSModel serialization into a
        // separate migration, and ensure it runs *after* any schema migrations. That is, new schema
        // migrations must be inserted *before* any of these Data Migrations.
        //
        // Note that account state is loaded *before* running data migrations, because many model objects expect
        // to be able to access that without a transaction.
        case dataMigration_enableV2RegistrationLockIfNecessary
        case dataMigration_resetStorageServiceData
        case dataMigration_markAllInteractionsAsNotDeleted
        case dataMigration_turnScreenSecurityOnForExistingUsers
        case dataMigration_groupIdMapping
        case dataMigration_disableSharingSuggestionsForExistingUsers
        case dataMigration_removeOversizedGroupAvatars
        case dataMigration_scheduleStorageServiceUpdateForMutedThreads
        case dataMigration_populateGroupMember
        case dataMigration_cullInvalidIdentityKeySendingErrors
        case dataMigration_moveToThreadAssociatedData
        case dataMigration_senderKeyStoreKeyIdMigration
        case dataMigration_reindexGroupMembershipAndMigrateLegacyAvatarDataFixed
        case dataMigration_repairAvatar
        case dataMigration_dropEmojiAvailabilityStore
        case dataMigration_dropSentStories
        case dataMigration_syncGroupStories
        case dataMigration_deleteOldGroupCapabilities
        case dataMigration_updateStoriesDisabledInAccountRecord
        case dataMigration_removeGroupStoryRepliesFromSearchIndex
        case dataMigration_populateStoryContextAssociatedDataLastReadTimestamp
        case dataMigration_scheduleStorageServiceUpdateForSystemContacts
        case dataMigration_ensureLocalDeviceId
        case dataMigration_indexSearchableNames
        case dataMigration_removeSystemContacts
        case dataMigration_clearLaunchScreenCache2
        case dataMigration_resetLinkedDeviceAuthorMergeBuilder

// We must ensure that we never re-use a MigrationId. It's unlikely that
// we'll happen to pick the same name twice, but it's possible. We keep old
// MigrationIds here (in DEBUG builds) so that the compiler will complain
// if we ever re-use an existing identifier.
#if DEBUG
        case addColumnsForStoryContext
        case addExclusiveProcessIdentifierToJobRecord
        case addGroupCallMessage
        case addHiddenInteractionColumn
        case addOrphanedBackupAttachmentTable
        case addPaymentModels36
        case addPaymentModels37
        case addPaymentModels39
        case dataMigration_clearLaunchScreenCache
        case dataMigration_disableLinkPreviewForExistingUsers
        case dataMigration_fixThreeSixteenDowngraders
        case dataMigration_kbsStateCleanup
        case dataMigration_markAvatarBuilderMegaphoneCompleteIfNecessary
        case dataMigration_markOnboardedUsers
        case dataMigration_markOnboardedUsers_v2
        case dataMigration_migrateGroupAvatarsToDisk
        case dataMigration_populateLastReceivedStoryTimestamp
        case dataMigration_recordMessageRequestInteractionIdEpoch
        case dataMigration_reindexGroupMembershipAndMigrateLegacyAvatarData
        case dataMigration_rotateStorageServiceKeyAndResetLocalData
        case dataMigration_rotateStorageServiceKeyAndResetLocalDataV2
        case dataMigration_rotateStorageServiceKeyAndResetLocalDataV3
        case experienceUpgradeSnooze
        case indexInfoMessageOnType
        case indexMediaGallery2
        case indexSignalRecipients
        case messageDecryptDeduplication
        case messageDecryptDeduplication2
        case messageDecryptDeduplication3
        case messageDecryptDeduplicationV4
        case messageDecryptDeduplicationV5
        case removeRedundantPhoneNumbers
        case signalAccount_add_contactAvatar
        case signalAccount_add_contactAvatarData
        case signalAccount_add_contactAvatarPngData

        // This used to insert `media_gallery_record` rows for every message
        // attachment. This table is now obsolete.
        case dataMigration_populateGalleryItems

        // These were rolled back in a complex dance of rewriting migration
        // history. See `recreateTSAttachment`.
        case tsMessageAttachmentMigration1
        case tsMessageAttachmentMigration2
        case tsMessageAttachmentMigration3
        case dropTSAttachmentTable

        // Obsoleted by dataMigration_indexSearchableNames.
        case dataMigration_indexSignalRecipients
        case dataMigration_indexMultipleNameComponentsForReceipients
        case dataMigration_indexPrivateStoryThreadNames
        case dataMigration_reindexSignalAccounts

        // Obsoleted by dataMigration_removeSystemContacts.
        case dataMigration_removeLinkedDeviceSystemContacts

        // Obsoleted when we removed the TSAttachment migration.
        case threadWallpaperTSAttachmentMigration1
        case threadWallpaperTSAttachmentMigration2
        case threadWallpaperTSAttachmentMigration3
        case migrateStoryMessageTSAttachments1
        case migrateStoryMessageTSAttachments2
#endif
    }

    public static let grdbSchemaVersionDefault: UInt = 0
    public static let grdbSchemaVersionLatest: UInt = 143

    private class DatabaseMigratorWrapper {
        // Run with immediate (or disabled) foreign key checks so that pre-existing
        // dangling rows don't cause unrelated migrations to fail. (When using
        // .deferred, all existing foreign keys must be re-checked, and those may
        // have pre-existing issues uncovered by an unrelated migration.)
        // TODO: Clean up broken foreign key references; stop disabling these checks.
        var migrator = DatabaseMigrator().disablingDeferredForeignKeyChecks()

        /**
         * Registers a database migration to be run asynchronously.
         *
         * In short, migrations get registered, GRDB checks which if any haven't been run yet, and runs those.
         * Each migration is provided a transaction and is allowed to throw exceptions on failures.
         * Migrations MUST use the provided transaction to guarantee correctness. The same transaction
         * is used to check migration eligibility and to mark migrations as completed.
         * Migrations that _do not_ throw an exception will be marked at completed. Failed migrations
         * _must_ throw an exception to be marked as incomplete and be re-run on next app launch.
         * Every exception thrown crashes the app.
         */
        func registerMigration(
            _ identifier: MigrationId,
            foreignKeyChecks: DatabaseMigrator.ForeignKeyChecks = .immediate,
            migrate: @escaping (DBWriteTransaction) throws -> Result<Void, Error>,
        ) {
            // Hold onto a reference to the migrator, so we can use its `appliedIdentifiers` method
            // which is really a static method since it uses no instance state, but needs a reference
            // to an instance (any instance, doesn't matter) anyway.
            // Don't keep a strong reference to self as that would be a retain cycle, and don't rely
            // on a weak reference to self because self is not guaranteed to be retained when
            // the migration actually runs; this class is used primary for migration setup.
            let migrator = self.migrator
            self.migrator.registerMigration(identifier.rawValue, foreignKeyChecks: foreignKeyChecks) { (database: Database) in
                let startTime = CACurrentMediaTime()

                // Create a transaction with this database connection.
                // GRDB creates a database connection for each migration applied; this migration
                // is finalized by GRDB internally. The steps look like:
                // 1. Create Database connection (in a write transaction)
                // 2. Run this closure, the migration itself
                // 3. Write to the grdb_migrations table to mark this migration as done (if we don't throw)
                // 4. Commit the transaction.
                //
                // Notably, it does _not_ check that the migration hasn't been run within the same transaction.
                // We do that here and just say we succeeded by early returning and not throwing an error.
                // This catches situations where the migrations are racing across processes.
                //
                // As long as we do this within the same DB connection we get as input to this
                // method, the check happens within the same transaction that writes that
                // the migration is complete, and we can take advantage of DB locks to enforce
                // migrations are only run once across processes.
                guard
                    try migrator
                        .appliedIdentifiers(database)
                        .contains(identifier.rawValue)
                        .negated
                else {
                    // Already run! Just succeed.
                    Logger.info("Attempting to re-run an already-finished migration, exiting: \(identifier)")
                    return
                }

                Logger.info("Running migration: \(identifier)")
                let transaction = DBWriteTransaction(database: database)
                defer { transaction.finalizeTransaction() }
                let result = try migrate(transaction)
                switch result {
                case .success:
                    let timeElapsed = CACurrentMediaTime() - startTime
                    let formattedTime = String(format: "%0.2fms", timeElapsed * 1000)
                    Logger.info("Migration completed: \(identifier), duration: \(formattedTime)")
                case .failure(let error):
                    throw error
                }
            }
        }

        func migrate(_ database: DatabaseWriter) throws {
            try migrator.migrate(database)
        }
    }

    private static func registerSchemaMigrations(migrator: DatabaseMigratorWrapper) {

        migrator.registerMigration(.createInitialSchema) { tx in
            let sql = """
            CREATE
                TABLE
                    keyvalue (
                        KEY TEXT NOT NULL
                        ,collection TEXT NOT NULL
                        ,VALUE BLOB NOT NULL
                        ,PRIMARY KEY (
                            KEY
                            ,collection
                        )
                    )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_TSThread" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"conversationColorName" TEXT NOT NULL
                        ,"creationDate" DOUBLE
                        ,"isArchived" INTEGER NOT NULL
                        ,"lastInteractionRowId" INTEGER NOT NULL
                        ,"messageDraft" TEXT
                        ,"mutedUntilDate" DOUBLE
                        ,"shouldThreadBeVisible" INTEGER NOT NULL
                        ,"contactPhoneNumber" TEXT
                        ,"contactUUID" TEXT
                        ,"groupModel" BLOB
                        ,"hasDismissedOffers" INTEGER
                    )
            ;

            CREATE
                INDEX "index_model_TSThread_on_uniqueId"
                    ON "model_TSThread"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_TSInteraction" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"receivedAtTimestamp" INTEGER NOT NULL
                        ,"timestamp" INTEGER NOT NULL
                        ,"uniqueThreadId" TEXT NOT NULL
                        ,"attachmentIds" BLOB
                        ,"authorId" TEXT
                        ,"authorPhoneNumber" TEXT
                        ,"authorUUID" TEXT
                        ,"body" TEXT
                        ,"callType" INTEGER
                        ,"configurationDurationSeconds" INTEGER
                        ,"configurationIsEnabled" INTEGER
                        ,"contactShare" BLOB
                        ,"createdByRemoteName" TEXT
                        ,"createdInExistingGroup" INTEGER
                        ,"customMessage" TEXT
                        ,"envelopeData" BLOB
                        ,"errorType" INTEGER
                        ,"expireStartedAt" INTEGER
                        ,"expiresAt" INTEGER
                        ,"expiresInSeconds" INTEGER
                        ,"groupMetaMessage" INTEGER
                        ,"hasLegacyMessageState" INTEGER
                        ,"hasSyncedTranscript" INTEGER
                        ,"isFromLinkedDevice" INTEGER
                        ,"isLocalChange" INTEGER
                        ,"isViewOnceComplete" INTEGER
                        ,"isViewOnceMessage" INTEGER
                        ,"isVoiceMessage" INTEGER
                        ,"legacyMessageState" INTEGER
                        ,"legacyWasDelivered" INTEGER
                        ,"linkPreview" BLOB
                        ,"messageId" TEXT
                        ,"messageSticker" BLOB
                        ,"messageType" INTEGER
                        ,"mostRecentFailureText" TEXT
                        ,"preKeyBundle" BLOB
                        ,"protocolVersion" INTEGER
                        ,"quotedMessage" BLOB
                        ,"read" INTEGER
                        ,"recipientAddress" BLOB
                        ,"recipientAddressStates" BLOB
                        ,"sender" BLOB
                        ,"serverTimestamp" INTEGER
                        ,"sourceDeviceId" INTEGER
                        ,"storedMessageState" INTEGER
                        ,"storedShouldStartExpireTimer" INTEGER
                        ,"unregisteredAddress" BLOB
                        ,"verificationState" INTEGER
                        ,"wasReceivedByUD" INTEGER
                    )
            ;

            CREATE
                INDEX "index_model_TSInteraction_on_uniqueId"
                    ON "model_TSInteraction"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_StickerPack" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"author" TEXT
                        ,"cover" BLOB NOT NULL
                        ,"dateCreated" DOUBLE NOT NULL
                        ,"info" BLOB NOT NULL
                        ,"isInstalled" INTEGER NOT NULL
                        ,"items" BLOB NOT NULL
                        ,"title" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_StickerPack_on_uniqueId"
                    ON "model_StickerPack"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_InstalledSticker" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"emojiString" TEXT
                        ,"info" BLOB NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_InstalledSticker_on_uniqueId"
                    ON "model_InstalledSticker"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_KnownStickerPack" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"dateCreated" DOUBLE NOT NULL
                        ,"info" BLOB NOT NULL
                        ,"referenceCount" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_KnownStickerPack_on_uniqueId"
                    ON "model_KnownStickerPack"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_TSAttachment" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"albumMessageId" TEXT
                        ,"attachmentType" INTEGER NOT NULL
                        ,"blurHash" TEXT
                        ,"byteCount" INTEGER NOT NULL
                        ,"caption" TEXT
                        ,"contentType" TEXT NOT NULL
                        ,"encryptionKey" BLOB
                        ,"serverId" INTEGER NOT NULL
                        ,"sourceFilename" TEXT
                        ,"cachedAudioDurationSeconds" DOUBLE
                        ,"cachedImageHeight" DOUBLE
                        ,"cachedImageWidth" DOUBLE
                        ,"creationTimestamp" DOUBLE
                        ,"digest" BLOB
                        ,"isUploaded" INTEGER
                        ,"isValidImageCached" INTEGER
                        ,"isValidVideoCached" INTEGER
                        ,"lazyRestoreFragmentId" TEXT
                        ,"localRelativeFilePath" TEXT
                        ,"mediaSize" BLOB
                        ,"pointerType" INTEGER
                        ,"state" INTEGER
                    )
            ;

            CREATE
                INDEX "index_model_TSAttachment_on_uniqueId"
                    ON "model_TSAttachment"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_SSKJobRecord" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"failureCount" INTEGER NOT NULL
                        ,"label" TEXT NOT NULL
                        ,"status" INTEGER NOT NULL
                        ,"attachmentIdMap" BLOB
                        ,"contactThreadId" TEXT
                        ,"envelopeData" BLOB
                        ,"invisibleMessage" BLOB
                        ,"messageId" TEXT
                        ,"removeMessageAfterSending" INTEGER
                        ,"threadId" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_SSKJobRecord_on_uniqueId"
                    ON "model_SSKJobRecord"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSMessageContentJob" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"createdAt" DOUBLE NOT NULL
                        ,"envelopeData" BLOB NOT NULL
                        ,"plaintextData" BLOB
                        ,"wasReceivedByUD" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_OWSMessageContentJob_on_uniqueId"
                    ON "model_OWSMessageContentJob"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSRecipientIdentity" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"accountId" TEXT NOT NULL
                        ,"createdAt" DOUBLE NOT NULL
                        ,"identityKey" BLOB NOT NULL
                        ,"isFirstKnownKey" INTEGER NOT NULL
                        ,"verificationState" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_OWSRecipientIdentity_on_uniqueId"
                    ON "model_OWSRecipientIdentity"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_ExperienceUpgrade" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                    )
            ;

            CREATE
                INDEX "index_model_ExperienceUpgrade_on_uniqueId"
                    ON "model_ExperienceUpgrade"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSDisappearingMessagesConfiguration" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"durationSeconds" INTEGER NOT NULL
                        ,"enabled" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_OWSDisappearingMessagesConfiguration_on_uniqueId"
                    ON "model_OWSDisappearingMessagesConfiguration"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_SignalRecipient" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"devices" BLOB NOT NULL
                        ,"recipientPhoneNumber" TEXT
                        ,"recipientUUID" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_SignalRecipient_on_uniqueId"
                    ON "model_SignalRecipient"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_SignalAccount" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"contact" BLOB
                        ,"multipleAccountLabelText" TEXT NOT NULL
                        ,"recipientPhoneNumber" TEXT
                        ,"recipientUUID" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_SignalAccount_on_uniqueId"
                    ON "model_SignalAccount"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSUserProfile" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"avatarFileName" TEXT
                        ,"avatarUrlPath" TEXT
                        ,"profileKey" BLOB
                        ,"profileName" TEXT
                        ,"recipientPhoneNumber" TEXT
                        ,"recipientUUID" TEXT
                        ,"username" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_OWSUserProfile_on_uniqueId"
                    ON "model_OWSUserProfile"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_TSRecipientReadReceipt" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"recipientMap" BLOB NOT NULL
                        ,"sentTimestamp" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_TSRecipientReadReceipt_on_uniqueId"
                    ON "model_TSRecipientReadReceipt"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSLinkedDeviceReadReceipt" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"messageIdTimestamp" INTEGER NOT NULL
                        ,"readTimestamp" INTEGER NOT NULL
                        ,"senderPhoneNumber" TEXT
                        ,"senderUUID" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_OWSLinkedDeviceReadReceipt_on_uniqueId"
                    ON "model_OWSLinkedDeviceReadReceipt"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSDevice" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"createdAt" DOUBLE NOT NULL
                        ,"deviceId" INTEGER NOT NULL
                        ,"lastSeenAt" DOUBLE NOT NULL
                        ,"name" TEXT
                    )
            ;

            CREATE
                INDEX "index_model_OWSDevice_on_uniqueId"
                    ON "model_OWSDevice"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_OWSContactQuery" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"lastQueried" DOUBLE NOT NULL
                        ,"nonce" BLOB NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_OWSContactQuery_on_uniqueId"
                    ON "model_OWSContactQuery"("uniqueId"
            )
            ;

            CREATE
                TABLE
                    IF NOT EXISTS "model_TestModel" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"dateValue" DOUBLE
                        ,"doubleValue" DOUBLE NOT NULL
                        ,"floatValue" DOUBLE NOT NULL
                        ,"int64Value" INTEGER NOT NULL
                        ,"nsIntegerValue" INTEGER NOT NULL
                        ,"nsNumberValueUsingInt64" INTEGER
                        ,"nsNumberValueUsingUInt64" INTEGER
                        ,"nsuIntegerValue" INTEGER NOT NULL
                        ,"uint64Value" INTEGER NOT NULL
                    )
            ;

            CREATE
                INDEX "index_model_TestModel_on_uniqueId"
                    ON "model_TestModel"("uniqueId"
            )
            ;

            CREATE
                INDEX "index_interactions_on_threadUniqueId_and_id"
                    ON "model_TSInteraction"("uniqueThreadId"
                ,"id"
            )
            ;

            CREATE
                INDEX "index_jobs_on_label_and_id"
                    ON "model_SSKJobRecord"("label"
                ,"id"
            )
            ;

            CREATE
                INDEX "index_jobs_on_status_and_label_and_id"
                    ON "model_SSKJobRecord"("label"
                ,"status"
                ,"id"
            )
            ;

            CREATE
                INDEX "index_interactions_on_view_once"
                    ON "model_TSInteraction"("isViewOnceMessage"
                ,"isViewOnceComplete"
            )
            ;

            CREATE
                INDEX "index_key_value_store_on_collection_and_key"
                    ON "keyvalue"("collection"
                ,"key"
            )
            ;

            CREATE
                INDEX "index_interactions_on_recordType_and_threadUniqueId_and_errorType"
                    ON "model_TSInteraction"("recordType"
                ,"uniqueThreadId"
                ,"errorType"
            )
            ;

            CREATE
                INDEX "index_attachments_on_albumMessageId"
                    ON "model_TSAttachment"("albumMessageId"
                ,"recordType"
            )
            ;

            CREATE
                INDEX "index_interactions_on_uniqueId_and_threadUniqueId"
                    ON "model_TSInteraction"("uniqueThreadId"
                ,"uniqueId"
            )
            ;

            CREATE
                INDEX "index_signal_accounts_on_recipientPhoneNumber"
                    ON "model_SignalAccount"("recipientPhoneNumber"
            )
            ;

            CREATE
                INDEX "index_signal_accounts_on_recipientUUID"
                    ON "model_SignalAccount"("recipientUUID"
            )
            ;

            CREATE
                INDEX "index_signal_recipients_on_recipientPhoneNumber"
                    ON "model_SignalRecipient"("recipientPhoneNumber"
            )
            ;

            CREATE
                INDEX "index_signal_recipients_on_recipientUUID"
                    ON "model_SignalRecipient"("recipientUUID"
            )
            ;

            CREATE
                INDEX "index_thread_on_contactPhoneNumber"
                    ON "model_TSThread"("contactPhoneNumber"
            )
            ;

            CREATE
                INDEX "index_thread_on_contactUUID"
                    ON "model_TSThread"("contactUUID"
            )
            ;

            CREATE
                INDEX "index_thread_on_shouldThreadBeVisible"
                    ON "model_TSThread"("shouldThreadBeVisible"
                ,"isArchived"
                ,"lastInteractionRowId"
            )
            ;

            CREATE
                INDEX "index_user_profiles_on_recipientPhoneNumber"
                    ON "model_OWSUserProfile"("recipientPhoneNumber"
            )
            ;

            CREATE
                INDEX "index_user_profiles_on_recipientUUID"
                    ON "model_OWSUserProfile"("recipientUUID"
            )
            ;

            CREATE
                INDEX "index_user_profiles_on_username"
                    ON "model_OWSUserProfile"("username"
            )
            ;

            CREATE
                INDEX "index_linkedDeviceReadReceipt_on_senderPhoneNumberAndTimestamp"
                    ON "model_OWSLinkedDeviceReadReceipt"("senderPhoneNumber"
                ,"messageIdTimestamp"
            )
            ;

            CREATE
                INDEX "index_linkedDeviceReadReceipt_on_senderUUIDAndTimestamp"
                    ON "model_OWSLinkedDeviceReadReceipt"("senderUUID"
                ,"messageIdTimestamp"
            )
            ;

            CREATE
                INDEX "index_interactions_on_timestamp_sourceDeviceId_and_authorUUID"
                    ON "model_TSInteraction"("timestamp"
                ,"sourceDeviceId"
                ,"authorUUID"
            )
            ;

            CREATE
                INDEX "index_interactions_on_timestamp_sourceDeviceId_and_authorPhoneNumber"
                    ON "model_TSInteraction"("timestamp"
                ,"sourceDeviceId"
                ,"authorPhoneNumber"
            )
            ;

            CREATE
                INDEX "index_interactions_unread_counts"
                    ON "model_TSInteraction"("read"
                ,"uniqueThreadId"
                ,"recordType"
            )
            ;

            CREATE
                INDEX "index_interactions_on_expiresInSeconds_and_expiresAt"
                    ON "model_TSInteraction"("expiresAt"
                ,"expiresInSeconds"
            )
            ;

            CREATE
                INDEX "index_interactions_on_threadUniqueId_storedShouldStartExpireTimer_and_expiresAt"
                    ON "model_TSInteraction"("expiresAt"
                ,"expireStartedAt"
                ,"storedShouldStartExpireTimer"
                ,"uniqueThreadId"
            )
            ;

            CREATE
                INDEX "index_contact_queries_on_lastQueried"
                    ON "model_OWSContactQuery"("lastQueried"
            )
            ;

            CREATE
                INDEX "index_attachments_on_lazyRestoreFragmentId"
                    ON "model_TSAttachment"("lazyRestoreFragmentId"
            )
            ;

            CREATE
                VIRTUAL TABLE
                    "signal_grdb_fts"
                        USING fts5 (
                        collection UNINDEXED
                        ,uniqueId UNINDEXED
                        ,ftsIndexableContent
                        ,tokenize = 'unicode61'
                    ) /* signal_grdb_fts(collection,uniqueId,ftsIndexableContent) */
            ;
            """
            try tx.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.signalAccount_add_contactAvatars) { transaction in
            let sql = """
            DROP TABLE "model_SignalAccount";
            CREATE
                TABLE
                    IF NOT EXISTS "model_SignalAccount" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                        ,"recordType" INTEGER NOT NULL
                        ,"uniqueId" TEXT NOT NULL UNIQUE
                            ON CONFLICT FAIL
                        ,"contact" BLOB
                        ,"contactAvatarHash" BLOB
                        ,"contactAvatarJpegData" BLOB
                        ,"multipleAccountLabelText" TEXT NOT NULL
                        ,"recipientPhoneNumber" TEXT
                        ,"recipientUUID" TEXT
                    );
            """
            try transaction.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.signalAccount_add_contactAvatars_indices) { transaction in
            let sql = """
            CREATE
                INDEX IF NOT EXISTS "index_model_SignalAccount_on_uniqueId"
                    ON "model_SignalAccount"("uniqueId"
            )
            ;

            CREATE
                INDEX IF NOT EXISTS "index_signal_accounts_on_recipientPhoneNumber"
                    ON "model_SignalAccount"("recipientPhoneNumber"
            )
            ;

            CREATE
                INDEX IF NOT EXISTS "index_signal_accounts_on_recipientUUID"
                    ON "model_SignalAccount"("recipientUUID"
            )
            ;
            """
            try transaction.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.jobRecords_add_attachmentId) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "attachmentId", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.createMediaGalleryItems) { transaction in
            try transaction.database.create(table: "media_gallery_items") { table in
                table.column("attachmentId", .integer)
                    .notNull()
                    .unique()
                table.column("albumMessageId", .integer)
                    .notNull()
                table.column("threadId", .integer)
                    .notNull()
                table.column("originalAlbumOrder", .integer)
                    .notNull()
            }

            try transaction.database.create(
                index: "index_media_gallery_items_for_gallery",
                on: "media_gallery_items",
                columns: ["threadId", "albumMessageId", "originalAlbumOrder"],
            )

            try transaction.database.create(
                index: "index_media_gallery_items_on_attachmentId",
                on: "media_gallery_items",
                columns: ["attachmentId"],
            )

            return .success(())
        }

        migrator.registerMigration(.createReaction) { transaction in
            try transaction.database.create(table: "model_OWSReaction") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("emoji", .text)
                    .notNull()
                table.column("reactorE164", .text)
                table.column("reactorUUID", .text)
                table.column("receivedAtTimestamp", .integer)
                    .notNull()
                table.column("sentAtTimestamp", .integer)
                    .notNull()
                table.column("uniqueMessageId", .text)
                    .notNull()
            }
            try transaction.database.create(
                index: "index_model_OWSReaction_on_uniqueId",
                on: "model_OWSReaction",
                columns: ["uniqueId"],
            )
            try transaction.database.create(
                index: "index_model_OWSReaction_on_uniqueMessageId_and_reactorE164",
                on: "model_OWSReaction",
                columns: ["uniqueMessageId", "reactorE164"],
            )
            try transaction.database.create(
                index: "index_model_OWSReaction_on_uniqueMessageId_and_reactorUUID",
                on: "model_OWSReaction",
                columns: ["uniqueMessageId", "reactorUUID"],
            )
            return .success(())
        }

        migrator.registerMigration(.dedupeSignalRecipients) { transaction in
            try dedupeSignalRecipients(tx: transaction)

            try transaction.database.drop(index: "index_signal_recipients_on_recipientPhoneNumber")
            try transaction.database.drop(index: "index_signal_recipients_on_recipientUUID")

            try transaction.database.create(
                index: "index_signal_recipients_on_recipientPhoneNumber",
                on: "model_SignalRecipient",
                columns: ["recipientPhoneNumber"],
                unique: true,
            )

            try transaction.database.create(
                index: "index_signal_recipients_on_recipientUUID",
                on: "model_SignalRecipient",
                columns: ["recipientUUID"],
                unique: true,
            )
            return .success(())
        }

        migrator.registerMigration(.unreadThreadInteractions) { transaction in
            try transaction.database.create(
                index: "index_interactions_on_threadId_read_and_id",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "read", "id"],
                unique: true,
            )
            return .success(())
        }

        migrator.registerMigration(.createFamilyName) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile", body: { alteration in
                alteration.add(column: "familyName", .text)
            })
            return .success(())
        }

        migrator.registerMigration(.createIndexableFTSTable) { transaction in
            try Bench(title: MigrationId.createIndexableFTSTable.rawValue, logInProduction: true) {
                try transaction.database.create(table: "indexable_text") { table in
                    table.autoIncrementedPrimaryKey("id")
                        .notNull()
                    table.column("collection", .text)
                        .notNull()
                    table.column("uniqueId", .text)
                        .notNull()
                    table.column("ftsIndexableContent", .text)
                        .notNull()
                }

                try transaction.database.create(
                    index: "index_indexable_text_on_collection_and_uniqueId",
                    on: "indexable_text",
                    columns: ["collection", "uniqueId"],
                    unique: true,
                )

                try transaction.database.create(virtualTable: "indexable_text_fts", using: FTS5()) { table in
                    // We could use FTS5TokenizerDescriptor.porter(wrapping: FTS5TokenizerDescriptor.unicode61())
                    //
                    // Porter does stemming (e.g. "hunting" will match "hunter").
                    // unicode61 will remove diacritics (e.g. "senor" will match "señor").
                    //
                    // GRDB TODO: Should we do stemming?
                    let tokenizer = FTS5TokenizerDescriptor.unicode61()
                    table.tokenizer = tokenizer

                    table.synchronize(withTable: "indexable_text")

                    // I thought leveraging the prefix-index feature would speed up as-you-type
                    // searching, but my measurements showed no substantive change.
                    // table.prefixes = [2, 4]

                    table.column("ftsIndexableContent")
                }

                // Copy over existing indexable content so we don't have to regenerate content from every indexed object.
                try transaction.database.execute(sql: "INSERT INTO indexable_text (collection, uniqueId, ftsIndexableContent) SELECT collection, uniqueId, ftsIndexableContent FROM signal_grdb_fts")
                try transaction.database.drop(table: "signal_grdb_fts")
            }
            return .success(())
        }

        migrator.registerMigration(.dropContactQuery) { transaction in
            try transaction.database.drop(table: "model_OWSContactQuery")
            return .success(())
        }

        migrator.registerMigration(.indexFailedJob) { transaction in
            try transaction.database.create(
                index: "index_interaction_on_storedMessageState",
                on: "model_TSInteraction",
                columns: ["storedMessageState"],
            )
            try transaction.database.create(
                index: "index_interaction_on_recordType_and_callType",
                on: "model_TSInteraction",
                columns: ["recordType", "callType"],
            )
            return .success(())
        }

        migrator.registerMigration(.groupsV2MessageJobs) { transaction in
            try transaction.database.create(table: "model_IncomingGroupsV2MessageJob") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("createdAt", .double)
                    .notNull()
                table.column("envelopeData", .blob)
                    .notNull()
                table.column("plaintextData", .blob)
                table.column("wasReceivedByUD", .integer)
                    .notNull()
            }
            try transaction.database.create(index: "index_model_IncomingGroupsV2MessageJob_on_uniqueId", on: "model_IncomingGroupsV2MessageJob", columns: ["uniqueId"])
            return .success(())
        }

        migrator.registerMigration(.addUserInfoToInteractions) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "infoMessageUserInfo", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.recreateExperienceUpgradeWithNewColumns) { transaction in
            // It's safe to just throw away old experience upgrade data since
            // there are no campaigns actively running that we need to preserve
            try transaction.database.drop(table: "model_ExperienceUpgrade")
            try transaction.database.create(table: "model_ExperienceUpgrade", body: { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("firstViewedTimestamp", .double)
                    .notNull()
                table.column("lastSnoozedTimestamp", .double)
                    .notNull()
                table.column("isComplete", .boolean)
                    .notNull()
            })
            return .success(())
        }

        migrator.registerMigration(.recreateExperienceUpgradeIndex) { transaction in
            try transaction.database.create(index: "index_model_ExperienceUpgrade_on_uniqueId", on: "model_ExperienceUpgrade", columns: ["uniqueId"])
            return .success(())
        }

        migrator.registerMigration(.indexInfoMessageOnType_v2) { transaction in
            // cleanup typo in index name that was released to a small number of internal testflight users
            try transaction.database.execute(sql: "DROP INDEX IF EXISTS index_model_TSInteraction_on_threadUniqueId_recordType_messagType")

            try transaction.database.create(
                index: "index_model_TSInteraction_on_threadUniqueId_recordType_messageType",
                on: "model_TSInteraction",
                columns: ["threadUniqueId", "recordType", "messageType"],
            )
            return .success(())
        }

        migrator.registerMigration(.createPendingReadReceipts) { transaction in
            try transaction.database.create(table: "pending_read_receipts") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("threadId", .integer).notNull()
                table.column("messageTimestamp", .integer).notNull()
                table.column("authorPhoneNumber", .text)
                table.column("authorUuid", .text)
            }
            try transaction.database.create(
                index: "index_pending_read_receipts_on_threadId",
                on: "pending_read_receipts",
                columns: ["threadId"],
            )
            return .success(())
        }

        migrator.registerMigration(.createInteractionAttachmentIdsIndex) { transaction in
            try transaction.database.create(
                index: "index_model_TSInteraction_on_threadUniqueId_and_attachmentIds",
                on: "model_TSInteraction",
                columns: ["threadUniqueId", "attachmentIds"],
            )
            return .success(())
        }

        migrator.registerMigration(.addIsUuidCapableToUserProfiles) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { (table: TableAlteration) -> Void in
                table.add(column: "isUuidCapable", .boolean).notNull().defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.uploadTimestamp) { transaction in
            try transaction.database.alter(table: "model_TSAttachment") { (table: TableAlteration) -> Void in
                table.add(column: "uploadTimestamp", .integer).notNull().defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.addRemoteDeleteToInteractions) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "wasRemotelyDeleted", .boolean)
            }
            return .success(())
        }

        migrator.registerMigration(.cdnKeyAndCdnNumber) { transaction in
            try transaction.database.alter(table: "model_TSAttachment") { (table: TableAlteration) -> Void in
                table.add(column: "cdnKey", .text).notNull().defaults(to: "")
                table.add(column: "cdnNumber", .integer).notNull().defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.addGroupIdToGroupsV2IncomingMessageJobs) { transaction in
            try transaction.database.alter(table: "model_IncomingGroupsV2MessageJob") { (table: TableAlteration) -> Void in
                table.add(column: "groupId", .blob)
            }
            try transaction.database.create(
                index: "index_model_IncomingGroupsV2MessageJob_on_groupId_and_id",
                on: "model_IncomingGroupsV2MessageJob",
                columns: ["groupId", "id"],
            )
            return .success(())
        }

        migrator.registerMigration(.removeEarlyReceiptTables) { transaction in
            try transaction.database.drop(table: "model_TSRecipientReadReceipt")
            try transaction.database.drop(table: "model_OWSLinkedDeviceReadReceipt")
            try transaction.database.execute(sql: "DELETE FROM keyvalue WHERE collection = ?", arguments: ["viewOnceMessages"])
            return .success(())
        }

        migrator.registerMigration(.addReadToReactions) { transaction in
            try transaction.database.alter(table: "model_OWSReaction") { (table: TableAlteration) -> Void in
                table.add(column: "read", .boolean).notNull().defaults(to: false)
            }

            try transaction.database.create(
                index: "index_model_OWSReaction_on_uniqueMessageId_and_read",
                on: "model_OWSReaction",
                columns: ["uniqueMessageId", "read"],
            )

            // Mark existing reactions as read
            try transaction.database.execute(sql: "UPDATE model_OWSReaction SET read = 1")
            return .success(())
        }

        migrator.registerMigration(.addIsMarkedUnreadToThreads) { transaction in
            try transaction.database.alter(table: "model_TSThread") { (table: TableAlteration) -> Void in
                table.add(column: "isMarkedUnread", .boolean).notNull().defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.addIsMediaMessageToMessageSenderJobQueue) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "isMediaMessage", .boolean)
            }

            try transaction.database.drop(index: "index_model_TSAttachment_on_uniqueId")

            try transaction.database.create(
                index: "index_model_TSAttachment_on_uniqueId_and_contentType",
                on: "model_TSAttachment",
                columns: ["uniqueId", "contentType"],
            )
            return .success(())
        }

        migrator.registerMigration(.readdAttachmentIndex) { transaction in
            try transaction.database.create(
                index: "index_model_TSAttachment_on_uniqueId",
                on: "model_TSAttachment",
                columns: ["uniqueId"],
            )

            try transaction.database.execute(sql: "UPDATE model_SSKJobRecord SET isMediaMessage = 0")
            return .success(())
        }

        migrator.registerMigration(.addLastVisibleRowIdToThreads) { transaction in
            try transaction.database.alter(table: "model_TSThread") { (table: TableAlteration) -> Void in
                table.add(column: "lastVisibleSortIdOnScreenPercentage", .double).notNull().defaults(to: 0)
                table.add(column: "lastVisibleSortId", .integer).notNull().defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.addMarkedUnreadIndexToThread) { transaction in
            try transaction.database.create(
                index: "index_model_TSThread_on_isMarkedUnread",
                on: "model_TSThread",
                columns: ["isMarkedUnread"],
            )
            return .success(())
        }

        migrator.registerMigration(.fixIncorrectIndexes) { transaction in
            try transaction.database.drop(index: "index_model_TSInteraction_on_threadUniqueId_recordType_messageType")
            try transaction.database.create(
                index: "index_model_TSInteraction_on_uniqueThreadId_recordType_messageType",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "recordType", "messageType"],
            )

            try transaction.database.drop(index: "index_model_TSInteraction_on_threadUniqueId_and_attachmentIds")
            try transaction.database.create(
                index: "index_model_TSInteraction_on_uniqueThreadId_and_attachmentIds",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "attachmentIds"],
            )
            return .success(())
        }

        migrator.registerMigration(.resetThreadVisibility) { transaction in
            try transaction.database.execute(sql: "UPDATE model_TSThread SET lastVisibleSortIdOnScreenPercentage = 0, lastVisibleSortId = 0")
            return .success(())
        }

        migrator.registerMigration(.trackUserProfileFetches) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { (table: TableAlteration) -> Void in
                table.add(column: "lastFetchDate", .double)
                table.add(column: "lastMessagingDate", .double)
            }
            try transaction.database.create(
                index: "index_model_OWSUserProfile_on_lastFetchDate_and_lastMessagingDate",
                on: "model_OWSUserProfile",
                columns: ["lastFetchDate", "lastMessagingDate"],
            )
            return .success(())
        }

        migrator.registerMigration(.addMentions) { transaction in
            try transaction.database.create(table: "model_TSMention") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("uniqueMessageId", .text)
                    .notNull()
                table.column("uniqueThreadId", .text)
                    .notNull()
                table.column("uuidString", .text)
                    .notNull()
                table.column("creationTimestamp", .double)
                    .notNull()
            }
            try transaction.database.create(
                index: "index_model_TSMention_on_uniqueId",
                on: "model_TSMention",
                columns: ["uniqueId"],
            )
            try transaction.database.create(
                index: "index_model_TSMention_on_uuidString_and_uniqueThreadId",
                on: "model_TSMention",
                columns: ["uuidString", "uniqueThreadId"],
            )
            try transaction.database.create(
                index: "index_model_TSMention_on_uniqueMessageId_and_uuidString",
                on: "model_TSMention",
                columns: ["uniqueMessageId", "uuidString"],
                unique: true,
            )

            try transaction.database.alter(table: "model_TSThread") { (table: TableAlteration) -> Void in
                table.add(column: "messageDraftBodyRanges", .blob)
            }

            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "bodyRanges", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addMentionNotificationMode) { transaction in
            try transaction.database.alter(table: "model_TSThread") { (table: TableAlteration) -> Void in
                table.add(column: "mentionNotificationMode", .integer)
                    .notNull()
                    .defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.addOfferTypeToCalls) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "offerType", .integer)
            }

            // Backfill all existing calls as "audio" calls.
            try transaction.database.execute(sql: "UPDATE model_TSInteraction SET offerType = 0 WHERE recordType IS \(SDSRecordType.call.rawValue)")
            return .success(())
        }

        migrator.registerMigration(.addServerDeliveryTimestamp) { transaction in
            try transaction.database.alter(table: "model_IncomingGroupsV2MessageJob") { (table: TableAlteration) -> Void in
                table.add(column: "serverDeliveryTimestamp", .integer).notNull().defaults(to: 0)
            }

            try transaction.database.alter(table: "model_OWSMessageContentJob") { (table: TableAlteration) -> Void in
                table.add(column: "serverDeliveryTimestamp", .integer).notNull().defaults(to: 0)
            }

            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "serverDeliveryTimestamp", .integer)
            }

            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "serverDeliveryTimestamp", .integer)
            }

            // Backfill all incoming messages with "0" as their timestamp
            try transaction.database.execute(sql: "UPDATE model_TSInteraction SET serverDeliveryTimestamp = 0 WHERE recordType IS \(SDSRecordType.incomingMessage.rawValue)")

            // Backfill all jobs with "0" as their timestamp
            try transaction.database.execute(sql: "UPDATE model_SSKJobRecord SET serverDeliveryTimestamp = 0 WHERE recordType IS \(SDSRecordType.messageDecryptJobRecord.rawValue)")
            return .success(())
        }

        migrator.registerMigration(.updateAnimatedStickers) { transaction in
            try transaction.database.alter(table: "model_TSAttachment") { (table: TableAlteration) -> Void in
                table.add(column: "isAnimatedCached", .integer)
            }
            try transaction.database.alter(table: "model_InstalledSticker") { (table: TableAlteration) -> Void in
                table.add(column: "contentType", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.updateMarkedUnreadIndex) { transaction in
            try transaction.database.drop(index: "index_model_TSThread_on_isMarkedUnread")
            try transaction.database.create(
                index: "index_model_TSThread_on_isMarkedUnread_and_shouldThreadBeVisible",
                on: "model_TSThread",
                columns: ["isMarkedUnread", "shouldThreadBeVisible"],
            )
            return .success(())
        }

        migrator.registerMigration(.addGroupCallMessage2) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { table in
                table.add(column: "eraId", .text)
                table.add(column: "hasEnded", .boolean)
                table.add(column: "creatorUuid", .text)
                table.add(column: "joinedMemberUuids", .blob)
            }

            try transaction.database.create(
                index: "index_model_TSInteraction_on_uniqueThreadId_and_hasEnded_and_recordType",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "hasEnded", "recordType"],
            )
            return .success(())
        }

        migrator.registerMigration(.addGroupCallEraIdIndex) { transaction in
            try transaction.database.create(
                index: "index_model_TSInteraction_on_uniqueThreadId_and_eraId_and_recordType",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "eraId", "recordType"],
            )
            return .success(())
        }

        migrator.registerMigration(.addProfileBio) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { table in
                table.add(column: "bio", .text)
                table.add(column: "bioEmoji", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addWasIdentityVerified) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { table in
                table.add(column: "wasIdentityVerified", .boolean)
            }

            try transaction.database.execute(sql: "UPDATE model_TSInteraction SET wasIdentityVerified = 0")
            return .success(())
        }

        migrator.registerMigration(.storeMutedUntilDateAsMillisecondTimestamp) { transaction in
            try transaction.database.alter(table: "model_TSThread") { table in
                table.add(column: "mutedUntilTimestamp", .integer).notNull().defaults(to: 0)
            }

            // Convert any existing mutedUntilDate (seconds) into mutedUntilTimestamp (milliseconds)
            try transaction.database.execute(sql: "UPDATE model_TSThread SET mutedUntilTimestamp = CAST(mutedUntilDate * 1000 AS INT) WHERE mutedUntilDate IS NOT NULL")
            try transaction.database.execute(sql: "UPDATE model_TSThread SET mutedUntilDate = NULL")
            return .success(())
        }

        migrator.registerMigration(.addPaymentModels15) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "paymentCancellation", .blob)
                table.add(column: "paymentNotification", .blob)
                table.add(column: "paymentRequest", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addPaymentModels40) { transaction in
            // PAYMENTS TODO: Remove.
            try transaction.database.execute(sql: "DROP TABLE IF EXISTS model_TSPaymentModel")
            try transaction.database.execute(sql: "DROP TABLE IF EXISTS model_TSPaymentRequestModel")

            try transaction.database.create(table: "model_TSPaymentModel") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("addressUuidString", .text)
                table.column("createdTimestamp", .integer)
                    .notNull()
                table.column("isUnread", .boolean)
                    .notNull()
                table.column("mcLedgerBlockIndex", .integer)
                    .notNull()
                table.column("mcReceiptData", .blob)
                table.column("mcTransactionData", .blob)
                table.column("memoMessage", .text)
                table.column("mobileCoin", .blob)
                table.column("paymentAmount", .blob)
                table.column("paymentFailure", .integer)
                    .notNull()
                table.column("paymentState", .integer)
                    .notNull()
                table.column("paymentType", .integer)
                    .notNull()
                table.column("requestUuidString", .text)
            }

            try transaction.database.create(index: "index_model_TSPaymentModel_on_uniqueId", on: "model_TSPaymentModel", columns: ["uniqueId"])
            try transaction.database.create(index: "index_model_TSPaymentModel_on_paymentState", on: "model_TSPaymentModel", columns: ["paymentState"])
            try transaction.database.create(index: "index_model_TSPaymentModel_on_mcLedgerBlockIndex", on: "model_TSPaymentModel", columns: ["mcLedgerBlockIndex"])
            try transaction.database.create(index: "index_model_TSPaymentModel_on_mcReceiptData", on: "model_TSPaymentModel", columns: ["mcReceiptData"])
            try transaction.database.create(index: "index_model_TSPaymentModel_on_mcTransactionData", on: "model_TSPaymentModel", columns: ["mcTransactionData"])
            try transaction.database.create(index: "index_model_TSPaymentModel_on_isUnread", on: "model_TSPaymentModel", columns: ["isUnread"])
            return .success(())
        }

        migrator.registerMigration(.fixPaymentModels) { transaction in
            // We released a build with an out-of-date schema that didn't reflect
            // `addPaymentModels15`. To fix this, we need to run the column adds
            // again to get all users in a consistent state. We can safely skip
            // this migration if it fails.
            do {
                try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                    table.add(column: "paymentCancellation", .blob)
                    table.add(column: "paymentNotification", .blob)
                    table.add(column: "paymentRequest", .blob)
                }
            } catch {
                // We can safely skip this if it fails.
                Logger.info("Skipping re-add of interaction payment columns.")
            }
            return .success(())
        }

        migrator.registerMigration(.addGroupMember) { transaction in
            try transaction.database.create(table: "model_TSGroupMember") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("groupThreadId", .text)
                    .notNull()
                table.column("phoneNumber", .text)
                table.column("uuidString", .text)
                table.column("lastInteractionTimestamp", .integer)
                    .notNull().defaults(to: 0)
            }

            try transaction.database.create(
                index: "index_model_TSGroupMember_on_uniqueId",
                on: "model_TSGroupMember",
                columns: ["uniqueId"],
            )
            try transaction.database.create(
                index: "index_model_TSGroupMember_on_groupThreadId",
                on: "model_TSGroupMember",
                columns: ["groupThreadId"],
            )
            try transaction.database.create(
                index: "index_model_TSGroupMember_on_uuidString_and_groupThreadId",
                on: "model_TSGroupMember",
                columns: ["uuidString", "groupThreadId"],
                unique: true,
            )
            try transaction.database.create(
                index: "index_model_TSGroupMember_on_phoneNumber_and_groupThreadId",
                on: "model_TSGroupMember",
                columns: ["phoneNumber", "groupThreadId"],
                unique: true,
            )
            return .success(())
        }

        migrator.registerMigration(.createPendingViewedReceipts) { transaction in
            try transaction.database.create(table: "pending_viewed_receipts") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("threadId", .integer).notNull()
                table.column("messageTimestamp", .integer).notNull()
                table.column("authorPhoneNumber", .text)
                table.column("authorUuid", .text)
            }
            try transaction.database.create(
                index: "index_pending_viewed_receipts_on_threadId",
                on: "pending_viewed_receipts",
                columns: ["threadId"],
            )
            return .success(())
        }

        migrator.registerMigration(.addViewedToInteractions) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "viewed", .boolean)
            }

            try transaction.database.execute(sql: "UPDATE model_TSInteraction SET viewed = 0")
            return .success(())
        }

        migrator.registerMigration(.createThreadAssociatedData) { transaction in
            try transaction.database.create(table: "thread_associated_data") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("threadUniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("isArchived", .boolean)
                    .notNull()
                    .defaults(to: false)
                table.column("isMarkedUnread", .boolean)
                    .notNull()
                    .defaults(to: false)
                table.column("mutedUntilTimestamp", .integer)
                    .notNull()
                    .defaults(to: 0)
            }

            try transaction.database.create(
                index: "index_thread_associated_data_on_threadUniqueId",
                on: "thread_associated_data",
                columns: ["threadUniqueId"],
                unique: true,
            )
            try transaction.database.create(
                index: "index_thread_associated_data_on_threadUniqueId_and_isMarkedUnread",
                on: "thread_associated_data",
                columns: ["threadUniqueId", "isMarkedUnread"],
            )
            try transaction.database.create(
                index: "index_thread_associated_data_on_threadUniqueId_and_isArchived",
                on: "thread_associated_data",
                columns: ["threadUniqueId", "isArchived"],
            )
            return .success(())
        }

        migrator.registerMigration(.addServerGuidToInteractions) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "serverGuid", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addMessageSendLog) { transaction in
            // Records all sent payloads
            // The sentTimestamp is the timestamp of the outgoing payload
            try transaction.database.create(table: "MessageSendLog_Payload") { table in
                table.autoIncrementedPrimaryKey("payloadId")
                    .notNull()
                table.column("plaintextContent", .blob)
                    .notNull()
                table.column("contentHint", .integer)
                    .notNull()
                table.column("sentTimestamp", .date)
                    .notNull()
                table.column("uniqueThreadId", .text)
                    .notNull()
            }

            // This table tracks a many-to-many relationship mapping
            // TSInteractions to related payloads. This is tracked so
            // when a given interaction is deleted, all related payloads
            // can be queried and deleted.
            //
            // An interaction can have multiple payloads (e.g. the message,
            // reactions, read receipts).
            // A payload can have multiple associated interactions (e.g.
            // a single receipt message marking multiple messages as read).
            try transaction.database.create(table: "MessageSendLog_Message") { table in
                table.column("payloadId", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()

                table.primaryKey(["payloadId", "uniqueId"])
                table.foreignKey(
                    ["payloadId"],
                    references: "MessageSendLog_Payload",
                    columns: ["payloadId"],
                    onDelete: .cascade,
                    onUpdate: .cascade,
                )
            }

            // Records all intended recipients for an intended payload
            // A trigger will ensure that once all recipients have acked,
            // the corresponding payload is deleted.
            try transaction.database.create(table: "MessageSendLog_Recipient") { table in
                table.column("payloadId", .integer)
                    .notNull()
                table.column("recipientUUID", .text)
                    .notNull()
                table.column("recipientDeviceId", .integer)
                    .notNull()

                table.primaryKey(["payloadId", "recipientUUID", "recipientDeviceId"])
                table.foreignKey(
                    ["payloadId"],
                    references: "MessageSendLog_Payload",
                    columns: ["payloadId"],
                    onDelete: .cascade,
                    onUpdate: .cascade,
                )
            }

            // This trigger ensures that once every intended recipient of
            // a payload has responded with a delivery receipt that the
            // payload is deleted.
            try transaction.database.execute(sql: """
                CREATE TRIGGER MSLRecipient_deliveryReceiptCleanup
                AFTER DELETE ON MessageSendLog_Recipient
                WHEN 0 = (
                    SELECT COUNT(*) FROM MessageSendLog_Recipient
                    WHERE payloadId = old.payloadId
                )
                BEGIN
                    DELETE FROM MessageSendLog_Payload
                    WHERE payloadId = old.payloadId;
                END;
            """)

            // This trigger ensures that if a given interaction is deleted,
            // all associated payloads are also deleted.
            try transaction.database.execute(sql: """
                CREATE TRIGGER MSLMessage_payloadCleanup
                AFTER DELETE ON MessageSendLog_Message
                BEGIN
                    DELETE FROM MessageSendLog_Payload WHERE payloadId = old.payloadId;
                END;
            """)

            // When we receive a decryption failure message, we need to look up
            // the content proto based on the date sent
            try transaction.database.create(
                index: "MSLPayload_sentTimestampIndex",
                on: "MessageSendLog_Payload",
                columns: ["sentTimestamp"],
            )

            // When deleting an interaction, we'll need to be able to lookup all
            // payloads associated with that interaction.
            try transaction.database.create(
                index: "MSLMessage_relatedMessageId",
                on: "MessageSendLog_Message",
                columns: ["uniqueId"],
            )
            return .success(())
        }

        migrator.registerMigration(.updatePendingReadReceipts) { transaction in
            try transaction.database.alter(table: "pending_read_receipts") { (table: TableAlteration) -> Void in
                table.add(column: "messageUniqueId", .text)
            }
            try transaction.database.alter(table: "pending_viewed_receipts") { (table: TableAlteration) -> Void in
                table.add(column: "messageUniqueId", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addSendCompletionToMessageSendLog) { transaction in
            try transaction.database.alter(table: "MessageSendLog_Payload") { (table: TableAlteration) -> Void in
                table.add(column: "sendComplete", .boolean).notNull().defaults(to: false)
            }

            // All existing entries are assumed to have completed.
            try transaction.database.execute(sql: "UPDATE MessageSendLog_Payload SET sendComplete = 1")

            // Update the trigger to include the new column: "AND sendComplete = true"
            try transaction.database.execute(sql: """
                DROP TRIGGER MSLRecipient_deliveryReceiptCleanup;

                CREATE TRIGGER MSLRecipient_deliveryReceiptCleanup
                AFTER DELETE ON MessageSendLog_Recipient
                WHEN 0 = (
                    SELECT COUNT(*) FROM MessageSendLog_Recipient
                    WHERE payloadId = old.payloadId
                )
                BEGIN
                    DELETE FROM MessageSendLog_Payload
                    WHERE payloadId = old.payloadId AND sendComplete = true;
                END;
            """)
            return .success(())
        }

        migrator.registerMigration(.addExclusiveProcessIdentifierAndHighPriorityToJobRecord) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "exclusiveProcessIdentifier", .text)
                table.add(column: "isHighPriority", .boolean)
            }
            try transaction.database.execute(sql: "UPDATE model_SSKJobRecord SET isHighPriority = 0")
            return .success(())
        }

        migrator.registerMigration(.updateMessageSendLogColumnTypes) { transaction in
            // Since the MessageSendLog hasn't shipped yet, we can get away with just dropping and rebuilding
            // the tables instead of performing a more expensive migration.
            try transaction.database.drop(table: "MessageSendLog_Payload")
            try transaction.database.drop(table: "MessageSendLog_Message")
            try transaction.database.drop(table: "MessageSendLog_Recipient")

            try transaction.database.create(table: "MessageSendLog_Payload") { table in
                table.autoIncrementedPrimaryKey("payloadId")
                    .notNull()
                table.column("plaintextContent", .blob)
                    .notNull()
                table.column("contentHint", .integer)
                    .notNull()
                table.column("sentTimestamp", .integer)
                    .notNull()
                table.column("uniqueThreadId", .text)
                    .notNull()
                table.column("sendComplete", .boolean)
                    .notNull().defaults(to: false)
            }

            try transaction.database.create(table: "MessageSendLog_Message") { table in
                table.column("payloadId", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()

                table.primaryKey(["payloadId", "uniqueId"])
                table.foreignKey(
                    ["payloadId"],
                    references: "MessageSendLog_Payload",
                    columns: ["payloadId"],
                    onDelete: .cascade,
                    onUpdate: .cascade,
                )
            }

            try transaction.database.create(table: "MessageSendLog_Recipient") { table in
                table.column("payloadId", .integer)
                    .notNull()
                table.column("recipientUUID", .text)
                    .notNull()
                table.column("recipientDeviceId", .integer)
                    .notNull()

                table.primaryKey(["payloadId", "recipientUUID", "recipientDeviceId"])
                table.foreignKey(
                    ["payloadId"],
                    references: "MessageSendLog_Payload",
                    columns: ["payloadId"],
                    onDelete: .cascade,
                    onUpdate: .cascade,
                )
            }

            try transaction.database.execute(sql: """
                CREATE TRIGGER MSLRecipient_deliveryReceiptCleanup
                AFTER DELETE ON MessageSendLog_Recipient
                WHEN 0 = (
                    SELECT COUNT(*) FROM MessageSendLog_Recipient
                    WHERE payloadId = old.payloadId
                )
                BEGIN
                    DELETE FROM MessageSendLog_Payload
                    WHERE payloadId = old.payloadId AND sendComplete = true;
                END;

                CREATE TRIGGER MSLMessage_payloadCleanup
                AFTER DELETE ON MessageSendLog_Message
                BEGIN
                    DELETE FROM MessageSendLog_Payload WHERE payloadId = old.payloadId;
                END;
            """)

            try transaction.database.create(
                index: "MSLPayload_sentTimestampIndex",
                on: "MessageSendLog_Payload",
                columns: ["sentTimestamp"],
            )
            try transaction.database.create(
                index: "MSLMessage_relatedMessageId",
                on: "MessageSendLog_Message",
                columns: ["uniqueId"],
            )
            return .success(())
        }

        migrator.registerMigration(.addRecordTypeIndex) { transaction in
            try transaction.database.create(
                index: "index_model_TSInteraction_on_nonPlaceholders_uniqueThreadId_id",
                on: "model_TSInteraction",
                columns: ["uniqueThreadId", "id"],
                condition: "\(interactionColumn: .recordType) IS NOT \(SDSRecordType.recoverableDecryptionPlaceholder.rawValue)",
            )
            return .success(())
        }

        migrator.registerMigration(.tunedConversationLoadIndices) { transaction in
            // These two indices are hyper-tuned for queries used to fetch the conversation load window. Specifically:
            // - InteractionFinder.count(excludingPlaceholders:transaction:)
            // - InteractionFinder.distanceFromLatest(interactionUniqueId:excludingPlaceholders:transaction:)
            // - InteractionFinder.enumerateInteractions(range:excludingPlaceholders:transaction:block:)
            //
            // These indices are partial, covering and as small as possible. The columns selected appear
            // redundant, but this is to avoid the SQLite query planner from selecting a less-optimal,
            // non-covering index that it thinks may be more optimal since it's less bytes/row.
            // More detailed info is included in the commit message.
            //
            // Note: These are not generated using the GRDB index creation syntax. In my testing it seems that
            // placing quotes around the column name in the WHERE clause will trick the SQLite query planner
            // into thinking these indices can't be applied to the queries we're optimizing for.
            try transaction.database.execute(sql: """
                DROP INDEX IF EXISTS index_model_TSInteraction_on_nonPlaceholders_uniqueThreadId_id;

                CREATE INDEX index_model_TSInteraction_ConversationLoadInteractionCount
                ON model_TSInteraction(uniqueThreadId, recordType)
                WHERE recordType IS NOT \(SDSRecordType.recoverableDecryptionPlaceholder.rawValue);

                CREATE INDEX index_model_TSInteraction_ConversationLoadInteractionDistance
                ON model_TSInteraction(uniqueThreadId, id, recordType, uniqueId)
                WHERE recordType IS NOT \(SDSRecordType.recoverableDecryptionPlaceholder.rawValue);
            """)
            return .success(())
        }

        migrator.registerMigration(.messageDecryptDeduplicationV6) { transaction in
            if try transaction.database.tableExists("MessageDecryptDeduplication") {
                try transaction.database.drop(table: "MessageDecryptDeduplication")
            }
            return .success(())
        }

        migrator.registerMigration(.createProfileBadgeTable) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile", body: { alteration in
                alteration.add(column: "profileBadgeInfo", .blob)
            })

            try transaction.database.create(table: "model_ProfileBadgeTable") { table in
                table.column("id", .text).primaryKey()
                table.column("rawCategory", .text).notNull()
                table.column("localizedName", .text).notNull()
                table.column("localizedDescriptionFormatString", .text).notNull()
                table.column("resourcePath", .text).notNull()

                table.column("badgeVariant", .text).notNull()
                table.column("localization", .text).notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.createSubscriptionDurableJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "receiptCredentailRequest", .blob)
                table.add(column: "receiptCredentailRequestContext", .blob)
                table.add(column: "priorSubscriptionLevel", .integer)
                table.add(column: "subscriberID", .blob)
                table.add(column: "targetSubscriptionLevel", .integer)
                table.add(column: "boostPaymentIntentID", .text)
                table.add(column: "isBoost", .boolean)
            }
            return .success(())
        }

        migrator.registerMigration(.addReceiptPresentationToSubscriptionDurableJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "receiptCredentialPresentation", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.createStoryMessageTable) { transaction in
            try transaction.database.create(table: "model_StoryMessage") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("recordType", .integer)
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("timestamp", .integer)
                    .notNull()
                table.column("authorUuid", .text)
                    .notNull()
                table.column("groupId", .blob)
                table.column("direction", .integer)
                    .notNull()
                table.column("manifest", .blob)
                    .notNull()
                table.column("attachment", .blob)
                    .notNull()
            }

            try transaction.database.create(index: "index_model_StoryMessage_on_uniqueId", on: "model_StoryMessage", columns: ["uniqueId"])

            try transaction.database.create(
                index: "index_model_StoryMessage_on_timestamp_and_authorUuid",
                on: "model_StoryMessage",
                columns: ["timestamp", "authorUuid"],
            )
            try transaction.database.create(
                index: "index_model_StoryMessage_on_direction",
                on: "model_StoryMessage",
                columns: ["direction"],
            )
            try transaction.database.execute(sql: """
                CREATE
                    INDEX index_model_StoryMessage_on_incoming_viewedTimestamp
                        ON model_StoryMessage (
                        json_extract (
                            manifest
                            ,'$.incoming.viewedTimestamp'
                        )
                    )
                ;
            """)
            return .success(())
        }

        migrator.registerMigration(.addColumnsForStoryContextRedux) { transaction in
            guard !hasRunMigration("addColumnsForStoryContext", transaction: transaction) else {
                return .success(())
            }

            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "storyAuthorUuidString", .text)
                table.add(column: "storyTimestamp", .integer)
                table.add(column: "isGroupStoryReply", .boolean).defaults(to: false)
                table.add(column: "storyReactionEmoji", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addIsStoriesCapableToUserProfiles) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { (table: TableAlteration) -> Void in
                table.add(column: "isStoriesCapable", .boolean).notNull().defaults(to: false)
            }

            try transaction.database.execute(sql: "ALTER TABLE model_OWSUserProfile DROP COLUMN isUuidCapable")
            return .success(())
        }

        migrator.registerMigration(.createDonationReceiptTable) { transaction in
            try transaction.database.create(table: "model_DonationReceipt") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("uniqueId", .text)
                    .notNull()
                    .unique(onConflict: .fail)
                table.column("timestamp", .integer)
                    .notNull()
                table.column("subscriptionLevel", .integer)
                table.column("amount", .numeric)
                    .notNull()
                table.column("currencyCode", .text)
                    .notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.addBoostAmountToSubscriptionDurableJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "amount", .numeric)
                table.add(column: "currencyCode", .text)
            }
            return .success(())
        }

        // These index migrations are *expensive* for users with large interaction tables. For external
        // users who don't yet have access to stories and don't have need for the indices, we will perform
        // one migration per release to keep the blocking time low (ideally one 5-7s migration per release).
        migrator.registerMigration(.updateConversationLoadInteractionCountIndex) { transaction in
            guard !hasRunMigration("addColumnsForStoryContext", transaction: transaction) else {
                return .success(())
            }

            try transaction.database.execute(sql: """
                DROP INDEX index_model_TSInteraction_ConversationLoadInteractionCount;

                CREATE INDEX index_model_TSInteraction_ConversationLoadInteractionCount
                ON model_TSInteraction(uniqueThreadId, isGroupStoryReply, recordType)
                WHERE recordType IS NOT \(SDSRecordType.recoverableDecryptionPlaceholder.rawValue);
            """)
            return .success(())
        }

        migrator.registerMigration(.updateConversationLoadInteractionDistanceIndex) { transaction in
            guard !hasRunMigration("addColumnsForStoryContext", transaction: transaction) else {
                return .success(())
            }

            try transaction.database.execute(sql: """
                DROP INDEX index_model_TSInteraction_ConversationLoadInteractionDistance;

                CREATE INDEX index_model_TSInteraction_ConversationLoadInteractionDistance
                ON model_TSInteraction(uniqueThreadId, id, isGroupStoryReply, recordType, uniqueId)
                WHERE recordType IS NOT \(SDSRecordType.recoverableDecryptionPlaceholder.rawValue);
            """)
            return .success(())
        }

        migrator.registerMigration(.updateConversationUnreadCountIndex) { transaction in
            guard !hasRunMigration("addColumnsForStoryContext", transaction: transaction) else {
                return .success(())
            }

            try transaction.database.execute(sql: """
                DROP INDEX IF EXISTS index_interactions_unread_counts;
                DROP INDEX IF EXISTS index_model_TSInteraction_UnreadCount;

                CREATE INDEX index_model_TSInteraction_UnreadCount
                ON model_TSInteraction(read, isGroupStoryReply, uniqueThreadId, recordType);
            """)
            return .success(())
        }

        migrator.registerMigration(.addStoryContextIndexToInteractions) { transaction in
            try transaction.database.create(
                index: "index_model_TSInteraction_on_StoryContext",
                on: "model_TSInteraction",
                columns: ["storyTimestamp", "storyAuthorUuidString", "isGroupStoryReply"],
            )
            return .success(())
        }

        migrator.registerMigration(.improvedDisappearingMessageIndices) { transaction in
            // The old index was created in an order that made it practically useless for the query
            // we needed it for. This rebuilds it as a partial index.
            try transaction.database.execute(sql: """
                DROP INDEX index_interactions_on_threadUniqueId_storedShouldStartExpireTimer_and_expiresAt;

                CREATE INDEX index_interactions_on_threadUniqueId_storedShouldStartExpireTimer_and_expiresAt
                ON model_TSInteraction(uniqueThreadId, uniqueId)
                WHERE
                    storedShouldStartExpireTimer IS TRUE
                AND
                    (expiresAt IS 0 OR expireStartedAt IS 0)
                ;
            """)
            return .success(())
        }

        migrator.registerMigration(.addProfileBadgeDuration) { transaction in
            try transaction.database.alter(table: "model_ProfileBadgeTable") { (table: TableAlteration) -> Void in
                table.add(column: "duration", .numeric)
            }
            return .success(())
        }

        migrator.registerMigration(.addGiftBadges) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "giftBadge", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addCanReceiveGiftBadgesToUserProfiles) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { (table: TableAlteration) -> Void in
                table.add(column: "canReceiveGiftBadges", .boolean).notNull().defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.addStoryThreadColumns) { transaction in
            try transaction.database.alter(table: "model_TSThread") { (table: TableAlteration) -> Void in
                table.add(column: "allowsReplies", .boolean).defaults(to: false)
                table.add(column: "lastSentStoryTimestamp", .integer)
                table.add(column: "name", .text)
                table.add(column: "addresses", .blob)
                table.add(column: "storyViewMode", .integer).defaults(to: 0)
            }

            try transaction.database.create(index: "index_model_TSThread_on_storyViewMode", on: "model_TSThread", columns: ["storyViewMode", "lastSentStoryTimestamp", "allowsReplies"])
            return .success(())
        }

        migrator.registerMigration(.addUnsavedMessagesToSendToJobRecord) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "unsavedMessagesToSend", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addColumnsForSendGiftBadgeDurableJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "messageText", .text)
                table.add(column: "paymentIntentClientSecret", .text)
                table.add(column: "paymentMethodId", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addDonationReceiptTypeColumn) { transaction in
            try transaction.database.alter(table: "model_DonationReceipt") { (table: TableAlteration) -> Void in
                table.add(column: "receiptType", .numeric)
            }
            return .success(())
        }

        migrator.registerMigration(.addAudioPlaybackRateColumn) { transaction in
            try transaction.database.alter(table: "thread_associated_data") { table in
                table.add(column: "audioPlaybackRate", .double)
            }
            return .success(())
        }

        migrator.registerMigration(.addSchemaVersionToAttachments) { transaction in
            try transaction.database.alter(table: "model_TSAttachment") { table in
                table.add(column: "attachmentSchemaVersion", .integer).defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.makeAudioPlaybackRateColumnNonNull) { transaction in
            // Up until when this is merged, there has been no way for users
            // to actually set an audio playback rate, so its okay to drop the column
            // just to reset the schema constraints to non-null.
            try transaction.database.alter(table: "thread_associated_data") { table in
                table.drop(column: "audioPlaybackRate")
                table.add(column: "audioPlaybackRate", .double).notNull().defaults(to: 1)
            }
            return .success(())
        }

        migrator.registerMigration(.addLastViewedStoryTimestampToTSThread) { transaction in
            try transaction.database.alter(table: "model_TSThread") { table in
                table.add(column: "lastViewedStoryTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.convertStoryIncomingManifestStorageFormat) { transaction in
            // Nest the "incoming" state under the "receivedState" key to make
            // future migrations more future proof.
            try transaction.database.execute(sql: """
                UPDATE model_StoryMessage
                SET manifest = json_replace(
                    manifest,
                    '$.incoming',
                    json_object(
                        'receivedState',
                        json_extract(
                            manifest,
                            '$.incoming'
                        )
                    )
                )
                WHERE json_extract(manifest, '$.incoming') IS NOT NULL;
            """)
            return .success(())
        }

        migrator.registerMigration(.recreateStoryIncomingViewedTimestampIndex) { transaction in
            try transaction.database.drop(index: "index_model_StoryMessage_on_incoming_viewedTimestamp")
            try transaction.database.execute(sql: """
                CREATE
                    INDEX index_model_StoryMessage_on_incoming_receivedState_viewedTimestamp
                        ON model_StoryMessage (
                        json_extract (
                            manifest
                            ,'$.incoming.receivedState.viewedTimestamp'
                        )
                    )
                ;
            """)
            return .success(())
        }

        migrator.registerMigration(.addColumnsForLocalUserLeaveGroupDurableJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "replacementAdminUuid", .text)
                table.add(column: "waitForMessageProcessing", .boolean)
            }
            return .success(())
        }

        migrator.registerMigration(.addStoriesHiddenStateToThreadAssociatedData) { transaction in
            try transaction.database.alter(table: "thread_associated_data") { table in
                table.add(column: "hideStory", .boolean).notNull().defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.addUnregisteredAtTimestampToSignalRecipient) { transaction in
            try transaction.database.alter(table: "model_SignalRecipient") { table in
                table.add(column: "unregisteredAtTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addLastReceivedStoryTimestampToTSThread) { transaction in
            try transaction.database.alter(table: "model_TSThread") { table in
                table.add(column: "lastReceivedStoryTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addStoryContextAssociatedDataTable) { transaction in
            try createStoryContextAssociatedData(tx: transaction)
            return .success(())
        }

        migrator.registerMigration(.populateStoryContextAssociatedDataTableAndRemoveOldColumns) { transaction in
            try populateStoryContextAssociatedData(tx: transaction)
            try dropColumnsMigratedToStoryContextAssociatedData(tx: transaction)
            return .success(())
        }

        migrator.registerMigration(.addColumnForExperienceUpgradeManifest) { transaction in
            try transaction.database.alter(table: "model_ExperienceUpgrade") { table in
                table.add(column: "manifest", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addStoryContextAssociatedDataReadTimestampColumn) { transaction in
            try transaction.database.alter(table: "model_StoryContextAssociatedData") { table in
                table.add(column: "lastReadTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addIsCompleteToContactSyncJob) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) -> Void in
                table.add(column: "isCompleteContactSync", .boolean).defaults(to: false)
            }
            return .success(())
        } // end: .addIsCompleteToContactSyncJob

        migrator.registerMigration(.addSnoozeCountToExperienceUpgrade) { transaction in
            try transaction.database.alter(table: "model_ExperienceUpgrade") { (table: TableAlteration) in
                table.add(column: "snoozeCount", .integer)
                    .notNull()
                    .defaults(to: 0)
            }

            let populateSql = """
                UPDATE model_ExperienceUpgrade
                SET snoozeCount = 1
                WHERE lastSnoozedTimestamp > 0
            """
            try transaction.database.execute(sql: populateSql)
            return .success(())
        }

        migrator.registerMigration(.addCancelledGroupRingsTable) { transaction in
            try transaction.database.create(table: CancelledGroupRing.databaseTableName) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("timestamp", .integer).notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.addPaymentProcessorColumnToJobRecords) { transaction in
            // Add a column to job records for "payment processor", which is
            // used by gift badge and receipt credential redemption jobs.
            //
            // Any old jobs should specify Stripe as their processor.
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) in
                table.add(column: "paymentProcessor", .text)
            }

            let populateSql = """
                UPDATE model_SSKJobRecord
                SET \(JobRecord.columnName(.paymentProcessor)) = 'STRIPE'
                WHERE \(JobRecord.columnName(.recordType)) = \(SendGiftBadgeJobRecord.recordType)
                OR \(JobRecord.columnName(.recordType)) = \(DonationReceiptCredentialRedemptionJobRecord.recordType)
            """
            try transaction.database.execute(sql: populateSql)

            return .success(())
        }

        migrator.registerMigration(.addCdsPreviousE164) { transaction in
            try transaction.database.create(table: "CdsPreviousE164") { table in
                table.column("id", .integer).notNull().primaryKey()
                table.column("e164", .text).notNull()
            }
            return .success(())
        } // end: .addCdsPreviousE164

        migrator.registerMigration(.addCallRecordTable) { transaction in
            /// Add the CallRecord table which from here on out is used to track when calls
            /// are accepted/declined, missed, etc, across linked devices.
            /// See `CallRecord`.
            try transaction.database.create(table: "model_CallRecord") { (table: TableDefinition) in
                table.column("id", .integer).primaryKey().notNull()
                table.column("uniqueId", .text).unique(onConflict: .fail).notNull()
                table.column("callId", .text).unique(onConflict: .ignore).notNull()
                table.column("interactionUniqueId", .text)
                    .notNull()
                    .references("model_TSInteraction", column: "uniqueId", onDelete: .cascade)
                table.column("peerUuid", .text).notNull()
                table.column("type", .integer).notNull()
                table.column("direction", .integer).notNull()
                table.column("status", .integer).notNull()
            }
            try transaction.database.create(
                index: "index_call_record_on_interaction_unique_id",
                on: "model_CallRecord",
                columns: ["interactionUniqueId"],
            )
            return .success(())
        }

        migrator.registerMigration(.addColumnsForGiftingWithPaypalToJobRecords) { transaction in
            try transaction.database.alter(table: "model_SSKJobRecord") { (table: TableAlteration) in
                table.add(column: "paypalPayerId", .text)
                table.add(column: "paypalPaymentId", .text)
                table.add(column: "paypalPaymentToken", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addSpamReportingTokenRecordTable) { transaction in
            try transaction.database.create(table: SpamReportingTokenRecord.databaseTableName) { table in
                // This is a BLOB column [by default][0]. We should've been explicit but don't want
                // to go back and change existing migrations, even if the change should be a no-op.
                // [0]: https://www.sqlite.org/datatype3.html#determination_of_column_affinity
                table.column("sourceUuid").primaryKey().notNull()
                table.column("spamReportingToken", .blob).notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.addVideoDuration) { transaction in
            try transaction.database.alter(table: "model_TSAttachment") { (table: TableAlteration) in
                table.add(column: "videoDuration", .double)
            }

            return .success(())
        }

        migrator.registerMigration(.addUsernameLookupRecordsTable) { transaction in
            try transaction.database.create(table: UsernameLookupRecord.databaseTableName) { table in
                table.column("aci", .blob).primaryKey().notNull()
                table.column("username", .text).notNull()
            }

            return .success(())
        }

        migrator.registerMigration(.dropUsernameColumnFromOWSUserProfile) { transaction in
            try transaction.database.drop(index: "index_user_profiles_on_username")
            try transaction.database.alter(table: "model_OWSUserProfile") { table in
                table.drop(column: "username")
            }

            return .success(())
        }

        migrator.registerMigration(.migrateVoiceMessageDrafts) { transaction in
            try migrateVoiceMessageDrafts(
                transaction: transaction,
                appSharedDataUrl: URL(fileURLWithPath: CurrentAppContext().appSharedDataDirectoryPath()),
                copyItem: FileManager.default.copyItem(at:to:),
            )
            return .success(())
        }

        migrator.registerMigration(.addIsPniCapableColumnToOWSUserProfile) { transaction in
            try transaction.database.alter(table: "model_OWSUserProfile") { table in
                table.add(column: "isPniCapable", .boolean).notNull().defaults(to: false)
            }

            return .success(())
        }

        migrator.registerMigration(.addStoryMessageReplyCount) { transaction in
            try transaction.database.alter(table: "model_StoryMessage") { table in
                table.add(column: "replyCount", .integer).notNull().defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.populateStoryMessageReplyCount) { transaction in
            try populateStoryMessageReplyCount(tx: transaction)
            return .success(())
        }

        migrator.registerMigration(.addIndexToFindFailedAttachments) { tx in
            let sql = """
                CREATE INDEX "index_attachments_toMarkAsFailed" ON "model_TSAttachment"(
                    "recordType", "state"
                ) WHERE "recordType" = 3 AND "state" IN (0, 1)
            """
            try tx.database.execute(sql: sql)

            return .success(())
        }

        migrator.registerMigration(.addEditMessageChanges) { transaction in
            try transaction.database.alter(table: "model_TSInteraction") { table in
                table.add(column: "editState", .integer).defaults(to: 0)
            }

            try Self.createEditRecordTable(tx: transaction)

            return .success(())
        }

        migrator.registerMigration(.dropMessageSendLogTriggers) { tx in
            try tx.database.execute(sql: """
                DROP TRIGGER IF EXISTS MSLRecipient_deliveryReceiptCleanup;
                DROP TRIGGER IF EXISTS MSLMessage_payloadCleanup;
            """)
            return .success(())
        }

        migrator.registerMigration(.threadReplyInfoServiceIds) { tx in
            try Self.migrateThreadReplyInfos(transaction: tx)
            return .success(())
        }

        migrator.registerMigration(.updateEditMessageUnreadIndex) { tx in
            try tx.database.execute(sql: "DROP INDEX IF EXISTS index_interactions_on_threadId_read_and_id")
            try tx.database.execute(sql: "DROP INDEX IF EXISTS index_model_TSInteraction_UnreadCount")

            try tx.database.create(
                index: "index_model_TSInteraction_UnreadMessages",
                on: "\(InteractionRecord.databaseTableName)",
                columns: [
                    "read",
                    "uniqueThreadId",
                    "id",
                    "isGroupStoryReply",
                    "editState",
                    "recordType",
                ],
            )
            return .success(())
        }

        migrator.registerMigration(.updateEditRecordTable) { tx in
            try Self.migrateEditRecordTable(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.threadReplyEditTarget) { tx in
            try tx.database.alter(table: "model_TSThread") { table in
                table.add(column: "editTargetTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addHiddenRecipientsTable) { transaction in
            try transaction.database.create(table: HiddenRecipient.databaseTableName) { table in
                table.column("recipientId", .integer)
                    .primaryKey()
                    .notNull()
                table.foreignKey(
                    ["recipientId"],
                    references: "model_SignalRecipient",
                    columns: ["id"],
                    onDelete: .cascade,
                )
            }
            return .success(())
        }

        migrator.registerMigration(.editRecordReadState) { tx in
            try tx.database.alter(table: "EditRecord") { table in
                table.add(column: "read", .boolean).notNull().defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.addPaymentModelInteractionUniqueId) { tx in
            try tx.database.alter(table: "model_TSPaymentModel") { table in
                table.add(column: "interactionUniqueId", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.addPaymentsActivationRequestModel) { tx in
            try tx.database.create(table: "TSPaymentsActivationRequestModel") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("threadUniqueId", .text)
                    .notNull()
                table.column("senderAci", .blob)
                    .notNull()
            }
            try tx.database.create(
                index: "index_TSPaymentsActivationRequestModel_on_threadUniqueId",
                on: "TSPaymentsActivationRequestModel",
                columns: ["threadUniqueId"],
            )
            return .success(())
        }

        migrator.registerMigration(.addRecipientPniColumn) { transaction in
            try transaction.database.alter(table: "model_SignalRecipient") { table in
                table.add(column: "pni", .text)
            }
            try transaction.database.create(
                index: "index_signal_recipients_on_pni",
                on: "model_SignalRecipient",
                columns: ["pni"],
                options: [.unique],
            )
            return .success(())
        }

        migrator.registerMigration(.deletePhoneNumberAccessStore) { tx in
            try tx.database.execute(sql: """
                DELETE FROM "keyvalue" WHERE "collection" = 'kUnidentifiedAccessCollection'
            """)
            return .success(())
        }

        /// Create the "Call Record" table.
        ///
        /// We had a table for this previously, which we dropped in favor of
        /// this newer version.
        migrator.registerMigration(.dropOldAndCreateNewCallRecordTable) { tx in
            try tx.database.drop(index: "index_call_record_on_interaction_unique_id")
            try tx.database.drop(table: "model_CallRecord")

            try tx.database.create(table: "CallRecord") { (table: TableDefinition) in
                table.column("id", .integer).primaryKey().notNull()
                table.column("callId", .text).notNull().unique()
                table.column("interactionRowId", .integer).notNull().unique()
                    .references("model_TSInteraction", column: "id", onDelete: .cascade)
                table.column("threadRowId", .integer).notNull()
                    .references("model_TSThread", column: "id", onDelete: .restrict)
                table.column("type", .integer).notNull()
                table.column("direction", .integer).notNull()
                table.column("status", .integer).notNull()
            }

            // Note that because `callId` and `interactionRowId` are UNIQUE
            // SQLite will automatically create an index on each of them.

            return .success(())
        }

        /// Fix a UNIQUE constraint on the "Call Record" table.
        ///
        /// We previously had a UNIQUE constraint on the `callId` column, which
        /// wasn't quite right. Instead, we want a UNIQUE constraint on
        /// `(callId, threadRowId)`, to mitigate against call ID collision.
        ///
        /// Since the call record table was just added recently, we can drop it
        /// and recreate it with the new UNIQUE constraint, via an explicit
        /// index.
        migrator.registerMigration(.fixUniqueConstraintOnCallRecord) { tx in
            try tx.database.drop(table: "CallRecord")

            try tx.database.create(table: "CallRecord") { (table: TableDefinition) in
                table.column("id", .integer).primaryKey().notNull()
                table.column("callId", .text).notNull()
                table.column("interactionRowId", .integer).notNull().unique()
                    .references("model_TSInteraction", column: "id", onDelete: .cascade)
                table.column("threadRowId", .integer).notNull()
                    .references("model_TSThread", column: "id", onDelete: .restrict)
                table.column("type", .integer).notNull()
                table.column("direction", .integer).notNull()
                table.column("status", .integer).notNull()
            }

            try tx.database.create(
                index: "index_call_record_on_callId_and_threadId",
                on: "CallRecord",
                columns: ["callId", "threadRowId"],
                options: [.unique],
            )

            // Note that because `interactionRowId` is UNIQUE, SQLite will
            // automatically create an index on it.

            return .success(())
        }

        /// Add a timestamp column to call records. Delete any records created
        /// prior, since we don't need them at the time of migration and we
        /// really want timestamps to be populated in the future.
        migrator.registerMigration(.addTimestampToCallRecord) { tx in
            try tx.database.execute(sql: """
                DELETE FROM CallRecord
            """)

            try tx.database.alter(table: "CallRecord") { table in
                table.add(column: "timestamp", .integer).notNull()
            }

            return .success(())
        }

        /// During subscription receipt credential redemption, we now need to
        /// know the payment method used, if possible.
        migrator.registerMigration(.addPaymentMethodToJobRecords) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "paymentMethod", .text)
            }

            return .success(())
        }

        migrator.registerMigration(.addIsNewSubscriptionToJobRecords) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "isNewSubscription", .boolean)
            }

            return .success(())
        }

        migrator.registerMigration(.enableFts5SecureDelete) { tx in
            try enableFts5SecureDelete(for: "indexable_text_fts", db: tx.database)
            return .success(())
        }

        migrator.registerMigration(.addShouldSuppressPaymentAlreadyRedeemedToJobRecords) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "shouldSuppressPaymentAlreadyRedeemed", .boolean)
            }

            return .success(())
        }

        migrator.registerMigration(.addGroupCallRingerAciToCallRecords) { tx in
            try tx.database.alter(table: "CallRecord") { table in
                table.add(column: "groupCallRingerAci", .blob)
            }

            return .success(())
        }

        migrator.registerMigration(.renameIsFromLinkedDevice) { tx in
            try tx.database.alter(table: "model_TSInteraction") { table in
                table.rename(column: "isFromLinkedDevice", to: "wasNotCreatedLocally")
            }
            return .success(())
        }

        migrator.registerMigration(.renameAndDeprecateSourceDeviceId) { tx in
            try tx.database.alter(table: "model_TSInteraction") { table in
                table.rename(column: "sourceDeviceId", to: "deprecated_sourceDeviceId")
            }
            return .success(())
        }

        migrator.registerMigration(.addCallRecordQueryIndices) { tx in
            /// This powers ``CallRecordQuerier/fetchCursor(tx:)``.
            try tx.database.create(
                index: "index_call_record_on_timestamp",
                on: "CallRecord",
                columns: [
                    "timestamp",
                ],
            )

            /// This powers ``CallRecordQuerier/fetchCursor(callStatus:tx:)``.
            try tx.database.create(
                index: "index_call_record_on_status_and_timestamp",
                on: "CallRecord",
                columns: [
                    "status",
                    "timestamp",
                ],
            )

            /// This powers ``CallRecordQuerier/fetchCursor(threadRowId:tx:)``.
            try tx.database.create(
                index: "index_call_record_on_threadRowId_and_timestamp",
                on: "CallRecord",
                columns: [
                    "threadRowId",
                    "timestamp",
                ],
            )

            /// This powers ``CallRecordQuerier/fetchCursor(threadRowId:callStatus:tx:)``.
            try tx.database.create(
                index: "index_call_record_on_threadRowId_and_status_and_timestamp",
                on: "CallRecord",
                columns: [
                    "threadRowId",
                    "status",
                    "timestamp",
                ],
            )

            return .success(())
        }

        migrator.registerMigration(.addDeletedCallRecordTable) { tx in
            try tx.database.create(table: "DeletedCallRecord") { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("callId", .text).notNull()
                table.column("threadRowId", .integer).notNull()
                    .references("model_TSThread", column: "id", onDelete: .restrict)
                table.column("deletedAtTimestamp", .integer).notNull()
            }

            try tx.database.create(
                index: "index_deleted_call_record_on_threadRowId_and_callId",
                on: "DeletedCallRecord",
                columns: ["threadRowId", "callId"],
                options: [.unique],
            )

            return .success(())
        }

        migrator.registerMigration(.addFirstDeletedIndexToDeletedCallRecord) { tx in
            try tx.database.create(
                index: "index_deleted_call_record_on_deletedAtTimestamp",
                on: "DeletedCallRecord",
                columns: ["deletedAtTimestamp"],
            )

            return .success(())
        }

        migrator.registerMigration(.addCallRecordDeleteAllColumnsToJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "CRDAJR_sendDeleteAllSyncMessage", .boolean)
                table.add(column: "CRDAJR_deleteAllBeforeTimestamp", .integer)
            }

            return .success(())
        }

        migrator.registerMigration(.addPhoneNumberSharingAndDiscoverability) { tx in
            try tx.database.alter(table: "model_SignalRecipient") { table in
                table.add(column: "isPhoneNumberDiscoverable", .boolean)
            }
            try tx.database.alter(table: "model_OWSUserProfile") { table in
                table.add(column: "isPhoneNumberShared", .boolean)
            }
            return .success(())
        }

        migrator.registerMigration(.removeRedundantPhoneNumbers2) { tx in
            removeMigration("removeRedundantPhoneNumbers", db: tx.database)
            try removeLocalProfileSignalRecipient(in: tx.database)
            // The OWSUserProfile migration was obsoleted by removeRedundantPhoneNumbers3.
            try removeRedundantPhoneNumbers(
                in: tx.database,
                tableName: "model_TSThread",
                serviceIdColumn: "contactUUID",
                phoneNumberColumn: "contactPhoneNumber",
            )
            try removeRedundantPhoneNumbers(
                in: tx.database,
                tableName: "model_TSGroupMember",
                serviceIdColumn: "uuidString",
                phoneNumberColumn: "phoneNumber",
            )
            return .success(())
        }

        // Perform a full sync to ensure isDiscoverable values are correct.
        migrator.registerMigration(.scheduleFullIntersection) { tx in
            try tx.database.execute(sql: """
            DELETE FROM "keyvalue" WHERE (
                "collection" = 'OWSContactsManagerCollection'
                AND "key" = 'OWSContactsManagerKeyNextFullIntersectionDate2'
            )
            """)
            return .success(())
        }

        migrator.registerMigration(.addUnreadToCallRecord) { tx in
            /// Annoyingly, we need to provide a DEFAULT value in this migration
            /// to cover all existing rows. I'd prefer that we make the column
            /// not have a default value that applies going forward, since all
            /// records inserted after this migration runs will provide a value
            /// for this column.
            ///
            /// However, SQLite doesn't know that, and consequently won't allow
            /// us to create a NOT NULL column without a default – even if we
            /// were to run a separate SQL statement after creating the column
            /// to populate it for existing rows.
            try tx.database.alter(table: "CallRecord") { table in
                table.add(column: "unreadStatus", .integer)
                    .notNull()
                    .defaults(to: CallRecord.CallUnreadStatus.read.rawValue)
            }

            try tx.database.create(
                index: "index_call_record_on_callStatus_and_unreadStatus_and_timestamp",
                on: "CallRecord",
                columns: [
                    "status",
                    "unreadStatus",
                    "timestamp",
                ],
            )

            return .success(())
        }

        migrator.registerMigration(.addSearchableName) { tx in
            try tx.database.execute(
                sql: """
                DELETE FROM "indexable_text" WHERE "collection" IN (
                    'SignalAccount',
                    'SignalRecipient',
                    'TSGroupMember',
                    'TSThread'
                )
                """,
            )

            // If you change the constant, you need to add a new migration. Simply
            // changing this one won't work for existing users.
            assert(SearchableNameIndexerImpl.Constants.databaseTableName == "SearchableName")

            try tx.database.create(table: "SearchableName") { table in
                table.autoIncrementedPrimaryKey("id").notNull()
                table.column("threadId", .integer).unique()
                table.column("signalAccountId", .integer).unique()
                table.column("userProfileId", .integer).unique()
                table.column("signalRecipientId", .integer).unique()
                table.column("usernameLookupRecordId", .blob).unique()
                table.column("value", .text).notNull()

                // Create foreign keys.
                table.foreignKey(["threadId"], references: "model_TSThread", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
                table.foreignKey(["signalAccountId"], references: "model_SignalAccount", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
                table.foreignKey(["userProfileId"], references: "model_OWSUserProfile", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
                table.foreignKey(["signalRecipientId"], references: "model_SignalRecipient", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
                table.foreignKey(["usernameLookupRecordId"], references: "UsernameLookupRecord", columns: ["aci"], onDelete: .cascade, onUpdate: .cascade)
            }

            try tx.database.create(virtualTable: "SearchableNameFTS", using: FTS5()) { table in
                table.tokenizer = FTS5TokenizerDescriptor.unicode61()
                table.synchronize(withTable: "SearchableName")
                table.column("value")
            }

            try enableFts5SecureDelete(for: "SearchableNameFTS", db: tx.database)

            return .success(())
        }

        migrator.registerMigration(.addCallRecordRowIdColumnToCallRecordDeleteAllJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "CRDAJR_deleteAllBeforeCallId", .text)
                table.add(column: "CRDAJR_deleteAllBeforeConversationId", .blob)
            }

            return .success(())
        }

        migrator.registerMigration(.markAllGroupCallMessagesAsRead) { tx in
            try tx.database.execute(sql: """
                UPDATE model_TSInteraction
                SET read = 1
                WHERE recordType = \(SDSRecordType.groupCallMessage.rawValue)
            """)

            return .success(())
        }

        migrator.registerMigration(.addIndexForUnreadByThreadRowIdToCallRecord) { tx in
            try tx.database.create(
                index: "index_call_record_on_threadRowId_and_callStatus_and_unreadStatus_and_timestamp",
                on: "CallRecord",
                columns: [
                    "threadRowId",
                    "status",
                    "unreadStatus",
                    "timestamp",
                ],
            )

            return .success(())
        }

        migrator.registerMigration(.addNicknamesTable) { tx in
            try tx.database.create(table: "NicknameRecord") { table in
                table.column("recipientRowID", .integer).primaryKey().notNull()
                    .references("model_SignalRecipient", column: "id", onDelete: .cascade)
                table.column("givenName", .text)
                table.column("familyName", .text)
                table.column("note", .text)
            }

            return .success(())
        }

        migrator.registerMigration(.expandSignalAccountContactFields) { tx in
            // To match iOS system behavior, these fields are NONNULL, so `contact !=
            // NULL` should be used to differentiate modern/legacy encodings.
            try tx.database.alter(table: "model_SignalAccount") { table in
                table.add(column: "cnContactId", .text)
                table.add(column: "givenName", .text).notNull().defaults(to: "")
                table.add(column: "familyName", .text).notNull().defaults(to: "")
                table.add(column: "nickname", .text).notNull().defaults(to: "")
                table.add(column: "fullName", .text).notNull().defaults(to: "")
            }

            return .success(())
        }

        migrator.registerMigration(.addNicknamesToSearchableName) { tx in
            try tx.database.alter(table: "SearchableName") { table in
                table.add(column: "nicknameRecordRecipientId", .integer)
                    .references("NicknameRecord", column: "recipientRowID", onDelete: .cascade)
            }

            return .success(())
        }

        migrator.registerMigration(.addAttachmentMetadataColumnsToIncomingContactSyncJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "ICSJR_cdnNumber", .integer)
                table.add(column: "ICSJR_cdnKey", .text)
                table.add(column: "ICSJR_encryptionKey", .blob)
                table.add(column: "ICSJR_digest", .blob)
                table.add(column: "ICSJR_plaintextLength", .integer)
            }

            return .success(())
        }

        migrator.registerMigration(.removeRedundantPhoneNumbers3) { tx in
            try removeLocalProfileSignalRecipient(in: tx.database)
            try removeRedundantPhoneNumbers(
                in: tx.database,
                tableName: "model_OWSUserProfile",
                serviceIdColumn: "recipientUUID",
                phoneNumberColumn: "recipientPhoneNumber",
            )
            return .success(())
        }

        migrator.registerMigration(.addV2AttachmentTable) { tx in
            return try Self.createV2AttachmentTables(tx)
        }

        migrator.registerMigration(.addBulkDeleteInteractionJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "BDIJR_anchorMessageRowId", .integer)
                table.add(column: "BDIJR_fullThreadDeletionAnchorMessageRowId", .integer)
                table.add(column: "BDIJR_threadUniqueId", .text)
            }

            return .success(())
        }

        migrator.registerMigration(.cleanUpThreadIndexes) { tx in
            try tx.database.execute(sql: """
            DROP INDEX IF EXISTS "index_model_TSThread_on_isMarkedUnread_and_shouldThreadBeVisible";
            DROP INDEX IF EXISTS "index_thread_on_shouldThreadBeVisible";
            CREATE INDEX "index_thread_on_shouldThreadBeVisible" ON "model_TSThread" ("shouldThreadBeVisible", "lastInteractionRowId" DESC);
            """)
            return .success(())
        }

        migrator.registerMigration(.addOrphanAttachmentPendingColumn) { tx in
            try tx.database.alter(table: "OrphanedAttachment") { table in
                // When we create an attachment, we first insert the new files
                // into the orphan table, so they get cleaned up if we fail.
                // We don't want to clean them up immediately, so track when
                // we do this so for these cases (and not others) we wait a bit
                // before deleting.
                table.add(column: "isPendingAttachment", .boolean)
                    .notNull()
                    .defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.cleanUpUniqueIndexes) { tx in
            try tx.database.execute(sql: """
            DROP INDEX IF EXISTS "index_model_ExperienceUpgrade_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_IncomingGroupsV2MessageJob_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_InstalledSticker_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_KnownStickerPack_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSDevice_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSDisappearingMessagesConfiguration_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSMessageContentJob_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSReaction_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSRecipientIdentity_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_OWSUserProfile_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_SSKJobRecord_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_SignalAccount_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_SignalRecipient_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_StickerPack_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_StoryMessage_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSAttachment_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSGroupMember_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSInteraction_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSMention_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSPaymentModel_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TSThread_on_uniqueId";
            DROP INDEX IF EXISTS "index_model_TestModel_on_uniqueId";
            DROP INDEX IF EXISTS "index_media_gallery_items_on_attachmentId";
            DROP INDEX IF EXISTS "index_thread_associated_data_on_threadUniqueId";
            """)
            return .success(())
        }

        migrator.registerMigration(.dropTableTestModel) { tx in
            try tx.database.execute(sql: """
            DROP TABLE IF EXISTS "model_TestModel"
            """)
            return .success(())
        }

        migrator.registerMigration(.addOriginalAttachmentIdForQuotedReplyColumn) { tx in
            return try Self.addOriginalAttachmentIdForQuotedReplyColumn(tx)
        }

        migrator.registerMigration(.addClientUuidToTSAttachment) { tx in
            try tx.database.alter(table: "model_TSAttachment") { table in
                table.add(column: "clientUuid", .text)
            }
            return .success(())
        }

        migrator.registerMigration(.recreateMessageAttachmentReferenceMediaGalleryIndexes) { tx in
            // Drop the original index; by putting renderingFlag before the three ordering columns
            // we force all queries to filter to one rendering flag value, which most do not.
            try tx.database.drop(
                index:
                "index_message_attachment_reference_on"
                    + "_threadRowId"
                    + "_and_ownerType"
                    + "_and_contentType"
                    + "_and_renderingFlag"
                    + "_and_receivedAtTimestamp"
                    + "_and_ownerRowId"
                    + "_and_orderInMessage",
            )
            try tx.database.alter(table: "MessageAttachmentReference") { table in
                // We want to be able to filter by
                // (contentType = image OR contentType = video OR contentType = animatedImage)
                // To do this efficiently with the index while applying an ORDER BY, we need
                // that OR to collapse into a single constraint.
                // Do this by creating a generated column. It can be virtual, no need to store it.
                table.addColumn(
                    literal: "isVisualMediaContentType AS (contentType = 2 OR contentType = 3 OR contentType = 4) VIRTUAL",
                )
                // Ditto for
                // (contentType = file OR contentType = invalid)
                // Not used to today, but will be in the future.
                table.addColumn(
                    literal: "isInvalidOrFileContentType AS (contentType = 0 OR contentType = 1) VIRTUAL",
                )
            }

            /// Create three indexes which vary only by contentType/isVisualMediaContentType/isInvalidOrFileContentType
            /// Each media gallery query will either filter to a single content type or one of these pre-defined composit ones.
            try tx.database.create(
                index:
                "message_attachment_reference_media_gallery_single_content_type_index",
                on: "MessageAttachmentReference",
                columns: [
                    "threadRowId",
                    "ownerType",
                    "contentType",
                    "receivedAtTimestamp",
                    "ownerRowId",
                    "orderInMessage",
                ],
            )
            try tx.database.create(
                index:
                "message_attachment_reference_media_gallery_visualMedia_content_type_index",
                on: "MessageAttachmentReference",
                columns: [
                    "threadRowId",
                    "ownerType",
                    "isVisualMediaContentType",
                    "receivedAtTimestamp",
                    "ownerRowId",
                    "orderInMessage",
                ],
            )
            try tx.database.create(
                index:
                "message_attachment_reference_media_gallery_fileOrInvalid_content_type_index",
                on: "MessageAttachmentReference",
                columns: [
                    "threadRowId",
                    "ownerType",
                    "isInvalidOrFileContentType",
                    "receivedAtTimestamp",
                    "ownerRowId",
                    "orderInMessage",
                ],
            )
            return .success(())
        }

        migrator.registerMigration(.addAttachmentDownloadQueue) { tx in
            try tx.database.create(table: "AttachmentDownloadQueue") { table in
                table.autoIncrementedPrimaryKey("id").notNull()
                table.column("sourceType", .integer).notNull()
                table.column("attachmentId", .integer)
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                table.column("priority", .integer).notNull()
                table.column("minRetryTimestamp", .integer)
                table.column("retryAttempts", .integer).notNull()
                table.column("localRelativeFilePath", .text).notNull()
            }

            // When we enqueue a download (from an attachment in a particular source)
            // we want to see if there's an existing download enqueued.
            try tx.database.create(
                index: "index_AttachmentDownloadQueue_on_attachmentId_and_sourceType",
                on: "AttachmentDownloadQueue",
                columns: [
                    "attachmentId",
                    "sourceType",
                ],
            )

            // We only allow N downloads of priority "default", so if we exceed this
            // limit we need to drop the oldest one. This helps us count them.
            try tx.database.create(
                index: "index_AttachmentDownloadQueue_on_priority",
                on: "AttachmentDownloadQueue",
                columns: [
                    "priority",
                ],
            )

            // The index we use to pop the next download off the queue.
            // Only index where minRetryTimestamp == nil; we eliminate non-retryable rows.
            // Then we sort by priority (DESC).
            // Last, we break ties by row id (ASC, FIFO order).
            // GRDB utilities don't let you specify a sort order for columns
            // so just do raw SQL.
            try tx.database.execute(sql: """
                CREATE INDEX
                    "partial_index_AttachmentDownloadQueue_on_priority_DESC_and_id_where_minRetryTimestamp_isNull"
                ON
                    "AttachmentDownloadQueue"
                (
                    "priority" DESC
                    ,"id"
                )
                WHERE minRetryTimestamp IS NULL
            """)

            // We want to get the lowest minRetryTimestamp so we can nil out the column
            // and mark that row as ready to retry when we reach that time.
            try tx.database.execute(sql: """
                CREATE INDEX
                    "partial_index_AttachmentDownloadQueue_on_minRetryTimestamp_where_isNotNull"
                ON
                    "AttachmentDownloadQueue"
                (
                    "minRetryTimestamp"
                )
                WHERE minRetryTimestamp IS NOT NULL
            """)

            /// When we delete an attachment download row in the database, insert the partial download
            /// into the orphan table so we can clean up the file on disk.
            try tx.database.execute(sql: """
                CREATE TRIGGER
                    "__AttachmentDownloadQueue_ad"
                AFTER DELETE ON
                    "AttachmentDownloadQueue"
                BEGIN
                    INSERT INTO OrphanedAttachment (
                        localRelativeFilePath
                    ) VALUES (
                        OLD.localRelativeFilePath
                    );
                END;
            """)

            return .success(())
        }

        migrator.registerMigration(.attachmentAddCdnUnencryptedByteCounts) { tx in

            try tx.database.alter(table: "Attachment") { table in
                table.drop(column: "transitEncryptedByteCount")
                table.add(column: "transitUnencryptedByteCount", .integer)
                table.add(column: "mediaTierUnencryptedByteCount", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addArchivedPaymentInfoColumn) { tx in
            try tx.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "archivedPaymentInfo", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.createArchivedPaymentTable) { tx in
            try tx.database.execute(sql: "DROP TABLE IF EXISTS ArchivedPayment")
            try tx.database.create(table: "ArchivedPayment") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("amount", .text)
                table.column("fee", .text)
                table.column("note", .text)
                table.column("mobileCoinIdentification", .blob)
                table.column("status", .integer)
                table.column("failureReason", .integer)
                table.column("timestamp", .integer)
                table.column("blockIndex", .integer)
                table.column("blockTimestamp", .integer)
                table.column("transaction", .blob)
                table.column("receipt", .blob)
                table.column("direction", .integer)
                table.column("senderOrRecipientAci", .blob)
                table.column("interactionUniqueId", .text)
            }

            try tx.database.create(
                index: "index_archived_payment_on_interaction_unique_id",
                on: "ArchivedPayment",
                columns: ["interactionUniqueId"],
            )

            return .success(())
        }

        migrator.registerMigration(.removeDeadEndGroupThreadIdMappings) { tx in
            try removeDeadEndGroupThreadIdMappings(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addTSAttachmentMigrationTable) { tx in
            try tx.database.create(table: "TSAttachmentMigration") { table in
                table.column("tsAttachmentUniqueId", .text).notNull()
                // No benefit from making these foreign keys; we don't want cascade
                // delete behavior and don't need existence guarantees.
                table.column("interactionRowId", .integer)
                table.column("storyMessageRowId", .integer)
                table.column("reservedV2AttachmentPrimaryFileId", .blob).notNull()
                table.column("reservedV2AttachmentAudioWaveformFileId", .blob).notNull()
                table.column("reservedV2AttachmentVideoStillFrameFileId", .blob).notNull()
            }

            try tx.database.execute(sql: """
            CREATE INDEX "index_TSAttachmentMigration_on_interactionRowId"
            ON "TSAttachmentMigration"
            ("interactionRowId")
            WHERE "interactionRowId" IS NOT NULL;
            """)
            try tx.database.execute(sql: """
            CREATE INDEX "index_TSAttachmentMigration_on_storyMessageRowId"
            ON "TSAttachmentMigration"
            ("storyMessageRowId")
            WHERE "storyMessageRowId" IS NOT NULL;
            """)

            return .success(())
        }

        migrator.registerMigration(.indexMessageAttachmentReferenceByReceivedAtTimestamp) { tx in
            try tx.database.create(
                index: "index_message_attachment_reference_on_receivedAtTimestamp",
                on: "MessageAttachmentReference",
                columns: ["receivedAtTimestamp"],
            )
            return .success(())
        }

        migrator.registerMigration(.addBackupAttachmentDownloadQueue) { tx in
            try tx.database.create(table: "BackupAttachmentDownloadQueue") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("attachmentRowId", .integer)
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                    .unique()
                table.column("timestamp", .integer)
            }

            return .success(())
        }

        migrator.registerMigration(.createAttachmentUploadRecordTable) { tx in
            try tx.database.execute(sql: "DROP TABLE IF EXISTS AttachmentUploadRecord")
            try tx.database.create(table: "AttachmentUploadRecord") { table in
                table.autoIncrementedPrimaryKey("id")
                    .notNull()
                table.column("sourceType", .integer)
                    .notNull()
                table.column("attachmentId", .integer)
                    .notNull()
                table.column("uploadForm", .blob)
                table.column("uploadFormTimestamp", .integer)
                table.column("localMetadata", .blob)
                table.column("uploadSessionUrl", .blob)
                table.column("attempt", .integer)
            }

            try tx.database.create(
                index: "index_attachment_upload_record_on_attachment_id",
                on: "AttachmentUploadRecord",
                columns: ["attachmentId"],
            )

            return .success(())
        }

        migrator.registerMigration(.addBlockedRecipient) { tx in
            try migrateBlockedRecipients(tx: tx)

            return .success(())
        }

        migrator.registerMigration(.addDmTimerVersionColumn) { tx in
            try tx.database.alter(table: "model_OWSDisappearingMessagesConfiguration") { table in
                table.add(column: "timerVersion", .integer)
                    .defaults(to: 1)
                    .notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.addVersionedDMTimer) { tx in
            // This table can be deleted 90 days after all clients ship capability
            // support; the capability can be assumed true then.
            try tx.database.create(table: "VersionedDMTimerCapabilities") { table in
                table.column("serviceId", .blob).unique().notNull()
                table.column("isEnabled", .boolean).notNull()
            }
            return .success(())
        }

        migrator.registerMigration(.addDMTimerVersionToInteractionTable) { tx in
            try tx.database.alter(table: "model_TSInteraction") { (table: TableAlteration) -> Void in
                table.add(column: "expireTimerVersion", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.initializeDMTimerVersion) { tx in
            // One time, we update all timers to an initial value of 2.
            // This prevents the following edge case:
            //
            // 1. Alice and Bob have a chat with DM timer set to some value.
            // 2. Both Alice and Bob update their primaries. If not for this migration,
            //    their clock value would be 1 (the default).
            // 3. Alice links a new iPad/Desktop; it doesn't get a contact sync yet
            //    It sets the chat's clock to 1 (the default).
            // 4. Alice's iPad sends a message with expireTimer=nil clock=1
            // 5. Bob (and Alice's primary) accept and set the timer to nil
            //
            // So we initialize all _existing_ timers to 2, so the default 1 value
            // will be rejected in step 5.
            // (Chats that have never set a timer have no configuration row and are
            // fine to leave as-is. Also this will update the universal and group
            // thread timer versions, but those are never used so it doesn't matter.)
            try tx.database.execute(sql: """
            UPDATE model_OWSDisappearingMessagesConfiguration SET timerVersion = 2;
            """)
            return .success(())
        }

        migrator.registerMigration(.attachmentAddMediaTierDigest) { tx in
            try tx.database.alter(table: "Attachment") { table in
                table.add(column: "mediaTierDigestSHA256Ciphertext", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.removeVoIPToken) { tx in
            try tx.database.execute(sql: """
            DELETE FROM "keyvalue" WHERE "collection" = 'SignalPreferences' AND "key" = 'LastRecordedVoipToken'
            """)
            return .success(())
        }

        /// The migration that adds `Attachment.mediaTierDigestSHA256Ciphertext`
        /// will add that column to the end of the `Attachment` table's columns.
        /// However, in `schema.sql` that column was added in the middle of the
        /// existing columns. That means that users who did a fresh install with
        /// that `schema.sql` will have a different column order than those who
        /// migrated an existing install.
        ///
        /// This migration drops the column, which is not yet used (it will
        /// eventually be used for Backups-related attachment business), and
        /// re-adds it so that all users will have the column in the same
        /// location. To avoid rewriting data for users whose column is already
        /// at the end of the column list, we check first that the user has the
        /// column in the middle of the list.
        migrator.registerMigration(.reorderMediaTierDigestColumn) { tx in
            let digestColumn = "mediaTierDigestSHA256Ciphertext"

            let existingColumns = try tx.database.columns(in: "Attachment")
            guard let lastColumn = existingColumns.last else {
                throw OWSAssertionError("Missing columns for Attachment table!")
            }

            if lastColumn.name == digestColumn {
                // No need to drop and re-add! The column is at the end, so this
                // must be a migrated (not fresh-installed) database.
                return .success(())
            }

            try tx.database.alter(table: "Attachment") { table in
                table.drop(column: digestColumn)
                table.add(column: digestColumn, .blob)
            }

            return .success(())
        }

        migrator.registerMigration(.addIncrementalMacParamsToAttachment) { tx in
            try tx.database.alter(table: "Attachment") { table in
                table.add(column: "incrementalMac", .blob)
                table.add(column: "incrementalMacChunkSize", .integer)
            }

            return .success(())
        }

        migrator.registerMigration(.splitIncrementalMacAttachmentColumns) { tx in

            try tx.database.alter(table: "Attachment") { table in
                table.rename(column: "incrementalMac", to: "mediaTierIncrementalMac")
                table.rename(column: "incrementalMacChunkSize", to: "mediaTierIncrementalMacChunkSize")
                table.add(column: "transitTierIncrementalMac", .blob)
                table.add(column: "transitTierIncrementalMacChunkSize", .integer)
            }

            return .success(())
        }

        migrator.registerMigration(.addCallEndedTimestampToCallRecord) { tx in
            try tx.database.alter(table: "CallRecord") { table in
                table.add(column: "callEndedTimestamp", .integer)
                    .notNull()
                    .defaults(to: 0)
            }

            return .success(())
        }

        migrator.registerMigration(.addIsViewOnceColumnToMessageAttachmentReference) { tx in
            try tx.database.alter(table: "MessageAttachmentReference") { table in
                table.add(column: "isViewOnce", .boolean)
                    .notNull()
                    .defaults(to: false)
            }
            return .success(())
        }

        migrator.registerMigration(.backfillIsViewOnceMessageAttachmentReference) { tx in
            let cursor = try UInt64.fetchCursor(
                tx.database,
                sql: "SELECT id from model_TSInteraction where isViewOnceMessage = 1;",
            )
            while let nextInteractionId = try cursor.next() {
                try tx.database.execute(
                    sql: """
                    UPDATE MessageAttachmentReference
                    SET isViewOnce = 1
                    WHERE ownerRowId = ?
                    """,
                    arguments: [nextInteractionId],
                )
            }

            return .success(())
        }

        migrator.registerMigration(.addAttachmentValidationBackfillTable) { tx in
            try tx.database.create(table: "AttachmentValidationBackfillQueue") { table in
                table.column("attachmentId", .integer)
                    .notNull()
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .primaryKey(onConflict: .ignore)
            }
            return .success(())
        }

        migrator.registerMigration(.addIsSmsColumnToTSAttachment) { tx in
            try tx.database.alter(table: "model_TSInteraction") { table in
                table.add(column: "isSmsMessageRestoredFromBackup", .boolean)
                    .defaults(to: false)
            }

            return .success(())
        }

        migrator.registerMigration(.addInKnownMessageRequestStateToHiddenRecipient) { tx in
            try tx.database.alter(table: "HiddenRecipient") { table in
                table.add(column: "inKnownMessageRequestState", .boolean)
                    .notNull()
                    .defaults(to: false)
            }

            return .success(())
        }

        migrator.registerMigration(.addBackupAttachmentUploadQueue) { tx in
            try tx.database.create(table: "BackupAttachmentUploadQueue") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("attachmentRowId", .integer)
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                    .unique()
                table.column("sourceType", .integer)
                    .notNull()
                table.column("timestamp", .integer)
            }

            try tx.database.create(
                index: "index_BackupAttachmentUploadQueue_on_sourceType_timestamp",
                on: "BackupAttachmentUploadQueue",
                columns: ["sourceType", "timestamp"],
            )

            return .success(())
        }

        migrator.registerMigration(.addBackupStickerPackDownloadQueue) { tx in
            try tx.database.create(table: "BackupStickerPackDownloadQueue") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("packId", .blob)
                    .notNull()
                table.column("packKey", .blob)
                    .notNull()
            }

            return .success(())
        }

        migrator.registerMigration(.createOrphanedBackupAttachmentTable) { tx in
            // A prior version of this migration is being reverted.
            try tx.database.execute(sql: "DROP TABLE IF EXISTS OrphanedBackupAttachment")
            try tx.database.execute(sql: "DROP TRIGGER IF EXISTS __Attachment_ad_backup_fullsize")
            try tx.database.execute(sql: "DROP TRIGGER IF EXISTS __Attachment_ad_backup_thumbnail")

            /// Rows are written into here to enqueue attachments for deletion from the media tier cdn.
            try tx.database.create(table: "OrphanedBackupAttachment") { table in
                table.autoIncrementedPrimaryKey("id").notNull()
                table.column("cdnNumber", .integer).notNull()
                table.column("mediaName", .text)
                table.column("mediaId", .blob)
                table.column("type", .integer)
            }

            try tx.database.create(
                index: "index_OrphanedBackupAttachment_on_mediaName",
                on: "OrphanedBackupAttachment",
                columns: ["mediaName"],
            )
            try tx.database.create(
                index: "index_OrphanedBackupAttachment_on_mediaId",
                on: "OrphanedBackupAttachment",
                columns: ["mediaId"],
            )

            /// When we delete an attachment row in the database, insert into the orphan backup table
            /// so we can clean up the cdn upload later.
            /// Note this doesn't cover if we start an upload/cdn copy and are interrupted. The attachment
            /// could exist on the cdn but we don't know about it locally, and it later gets deleted locally.
            /// We will pick up on this the next time we query the server list endpoint; this trigger doesn't handle it.
            try tx.database.execute(sql: """
                CREATE TRIGGER "__Attachment_ad_backup_fullsize" AFTER DELETE ON "Attachment"
                    WHEN (
                        OLD.mediaTierCdnNumber IS NOT NULL
                        AND OLD.mediaName IS NOT NULL
                    )
                    BEGIN
                    INSERT INTO OrphanedBackupAttachment (
                      cdnNumber
                      ,mediaName
                      ,mediaId
                      ,type
                    ) VALUES (
                      OLD.mediaTierCdnNumber
                      ,OLD.mediaName
                      ,NULL
                      ,0
                    );
                  END;
            """)
            try tx.database.execute(sql: """
                CREATE TRIGGER "__Attachment_ad_backup_thumbnail" AFTER DELETE ON "Attachment"
                    WHEN (
                        OLD.thumbnailCdnNumber IS NOT NULL
                        AND OLD.mediaName IS NOT NULL
                    )
                    BEGIN
                    INSERT INTO OrphanedBackupAttachment (
                      cdnNumber
                      ,mediaName
                      ,mediaId
                      ,type
                    ) VALUES (
                      OLD.thumbnailCdnNumber
                      ,OLD.mediaName
                      ,NULL
                      ,1
                    );
                  END;
            """)

            return .success(())
        }

        migrator.registerMigration(.addCallLinkTable) { tx in
            try addCallLinkTable(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.deleteIncomingGroupSyncJobRecords) { tx in
            try tx.database.execute(sql: "DELETE FROM model_SSKJobRecord WHERE label = ?", arguments: ["IncomingGroupSync"])
            return .success(())
        }

        migrator.registerMigration(.deleteKnownStickerPackTable) { tx in
            try tx.database.execute(sql: "DROP TABLE IF EXISTS model_KnownStickerPack")
            return .success(())
        }

        migrator.registerMigration(.addReceiptCredentialColumnToJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "receiptCredential", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.dropOrphanedGroupStoryReplies) { tx in
            let groupThreadUniqueIdCursor = try String.fetchCursor(
                tx.database,
                sql: """
                SELECT uniqueId
                FROM model_TSThread
                WHERE groupModel IS NOT NULL;
                """,
            )

            while let threadUniqueId = try groupThreadUniqueIdCursor.next() {
                try tx.database.execute(
                    sql: """
                    DELETE FROM model_TSInteraction
                    WHERE (
                        uniqueThreadId = ?
                        AND isGroupStoryReply = 1
                        AND recordType IS NOT 70
                        AND (storyTimestamp, storyAuthorUuidString) NOT IN (
                            SELECT timestamp, authorUuid
                            FROM model_StoryMessage
                        )
                    );
                    """,
                    arguments: [threadUniqueId],
                )
            }
            return .success(())
        }

        migrator.registerMigration(.addMessageBackupAvatarFetchQueue) { tx in
            try tx.database.create(table: "MessageBackupAvatarFetchQueue") { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("groupThreadRowId", .integer)
                    .references("model_TSThread", column: "id", onDelete: .cascade)
                table.column("groupAvatarUrl", .text)
                table.column("serviceId", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.addMessageBackupAvatarFetchQueueRetries) { tx in
            try tx.database.alter(table: "MessageBackupAvatarFetchQueue") { table in
                table.add(column: "numRetries", .integer).notNull().defaults(to: 0)
                table.add(column: "nextRetryTimestamp", .integer).notNull().defaults(to: 0)
            }
            try tx.database.create(
                index: "index_MessageBackupAvatarFetchQueue_on_nextRetryTimestamp",
                on: "MessageBackupAvatarFetchQueue",
                columns: ["nextRetryTimestamp"],
            )
            return .success(())
        }

        migrator.registerMigration(.addEditStateToMessageAttachmentReference) { tx in
            try tx.database.alter(table: "MessageAttachmentReference") { table in
                table.add(column: "ownerIsPastEditRevision", .boolean)
                    .defaults(to: false)
            }
            // TSEditState.pastRevision rawValue is 2
            try tx.database.execute(sql: """
            UPDATE MessageAttachmentReference
            SET ownerIsPastEditRevision = (
              SELECT model_TSInteraction.editState = 2
              FROM model_TSInteraction
              WHERE MessageAttachmentReference.ownerRowId = model_TSInteraction.id
            );
            """)
            return .success(())
        }

        migrator.registerMigration(.removeVersionedDMTimerCapabilities) { tx in
            try tx.database.drop(table: "VersionedDMTimerCapabilities")
            return .success(())
        }

        migrator.registerMigration(.removeJobRecordTSAttachmentColumns) { tx in
            // Remove TSAttachmentMultisend records.
            try tx.database.execute(sql: """
            DELETE FROM model_SSKJobRecord WHERE recordType = 58;
            """)
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.drop(column: "attachmentId")
                table.drop(column: "attachmentIdMap")
                table.drop(column: "unsavedMessagesToSend")
            }
            return .success(())
        }

        migrator.registerMigration(.deprecateAttachmentIdsColumn) { tx in
            try tx.database.alter(table: "model_TSInteraction") { table in
                table.rename(column: "attachmentIds", to: "deprecated_attachmentIds")
            }
            return .success(())
        }

        migrator.registerMigration(.dropMediaGalleryItemTable) { tx in
            try tx.database.drop(table: "media_gallery_items")
            return .success(())
        }

        migrator.registerMigration(.addBackupsReceiptCredentialStateToJobRecord) { tx in
            try tx.database.alter(table: "model_SSKJobRecord") { table in
                table.add(column: "BRCRJR_state", .blob)
            }

            return .success(())
        }

        migrator.registerMigration(.recreateTSAttachment) { tx in
            try tx.database.execute(sql: """
            CREATE
                 TABLE
                     IF NOT EXISTS "model_TSAttachment" (
                         "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                         ,"recordType" INTEGER NOT NULL
                         ,"uniqueId" TEXT NOT NULL UNIQUE
                             ON CONFLICT FAIL
                         ,"albumMessageId" TEXT
                         ,"attachmentType" INTEGER NOT NULL
                         ,"blurHash" TEXT
                         ,"byteCount" INTEGER NOT NULL
                         ,"caption" TEXT
                         ,"contentType" TEXT NOT NULL
                         ,"encryptionKey" BLOB
                         ,"serverId" INTEGER NOT NULL
                         ,"sourceFilename" TEXT
                         ,"cachedAudioDurationSeconds" DOUBLE
                         ,"cachedImageHeight" DOUBLE
                         ,"cachedImageWidth" DOUBLE
                         ,"creationTimestamp" DOUBLE
                         ,"digest" BLOB
                         ,"isUploaded" INTEGER
                         ,"isValidImageCached" INTEGER
                         ,"isValidVideoCached" INTEGER
                         ,"lazyRestoreFragmentId" TEXT
                         ,"localRelativeFilePath" TEXT
                         ,"mediaSize" BLOB
                         ,"pointerType" INTEGER
                         ,"state" INTEGER
                         ,"uploadTimestamp" INTEGER NOT NULL DEFAULT 0
                         ,"cdnKey" TEXT NOT NULL DEFAULT ''
                         ,"cdnNumber" INTEGER NOT NULL DEFAULT 0
                         ,"isAnimatedCached" INTEGER
                         ,"attachmentSchemaVersion" INTEGER DEFAULT 0
                         ,"videoDuration" DOUBLE
                         ,"clientUuid" TEXT
                     )
            ;
            """)

            try tx.database.execute(sql: """
            CREATE
                 INDEX IF NOT EXISTS "index_model_TSAttachment_on_uniqueId_and_contentType"
                     ON "model_TSAttachment"("uniqueId"
                 ,"contentType"
             )
             ;
            """)
            return .success(())
        }

        migrator.registerMigration(.recreateTSAttachmentMigration) { tx in
            try tx.database.execute(sql: """
            CREATE
                 TABLE
                     IF NOT EXISTS "TSAttachmentMigration" (
                         "tsAttachmentUniqueId" TEXT NOT NULL
                         ,"interactionRowId" INTEGER
                         ,"storyMessageRowId" INTEGER
                         ,"reservedV2AttachmentPrimaryFileId" BLOB NOT NULL
                         ,"reservedV2AttachmentAudioWaveformFileId" BLOB NOT NULL
                         ,"reservedV2AttachmentVideoStillFrameFileId" BLOB NOT NULL
                     )
            ;
            """)
            try tx.database.execute(sql: """
            CREATE
                 INDEX IF NOT EXISTS "index_TSAttachmentMigration_on_interactionRowId"
                     ON "TSAttachmentMigration" ("interactionRowId")
             WHERE
                 "interactionRowId" IS NOT NULL
             ;
            """)
            try tx.database.execute(sql: """
            CREATE
                 INDEX IF NOT EXISTS "index_TSAttachmentMigration_on_storyMessageRowId"
                     ON "TSAttachmentMigration" ("storyMessageRowId")
             WHERE
                 "storyMessageRowId" IS NOT NULL
             ;
            """)
            return .success(())
        }

        migrator.registerMigration(.addBlockedGroup) { tx in
            try tx.database.create(table: "BlockedGroup", options: [.withoutRowID]) { table in
                table.column("groupId", .blob).notNull().primaryKey()
            }

            let groupIds = try fetchAndClearBlockedGroupIds(tx: tx)

            for groupId in groupIds {
                try tx.database.execute(sql: "INSERT INTO BlockedGroup VALUES (?)", arguments: [groupId])
            }

            return .success(())
        }

        migrator.registerMigration(.addGroupSendEndorsement) { tx in
            try tx.database.create(table: "CombinedGroupSendEndorsement") { table in
                table.column("threadId", .integer).primaryKey()
                    .references("model_TSThread", column: "id", onDelete: .cascade, onUpdate: .cascade)
                table.column("endorsement", .blob).notNull()
                table.column("expiration", .integer).notNull()
            }
            try tx.database.create(table: "IndividualGroupSendEndorsement") { table in
                table.primaryKey(["threadId", "recipientId"])
                table.column("threadId", .integer).notNull()
                    .references("CombinedGroupSendEndorsement", column: "threadId", onDelete: .cascade, onUpdate: .cascade)
                table.column("recipientId", .integer).notNull()
                    .references("model_SignalRecipient", column: "id", onDelete: .cascade, onUpdate: .cascade)
                table.column("endorsement", .blob).notNull()
            }
            try tx.database.create(
                index: "IndividualGroupSendEndorsement_recipientId",
                on: "IndividualGroupSendEndorsement",
                columns: ["recipientId"],
            )
            return .success(())
        }

        migrator.registerMigration(.deleteLegacyMessageDecryptJobRecords) { tx in
            try tx.database.execute(sql: "DELETE FROM model_SSKJobRecord WHERE label = ?", arguments: ["SSKMessageDecrypt"])
            return .success(())
        }

        migrator.registerMigration(.dropMessageContentJobTable) { tx in
            try tx.database.execute(sql: "DROP TABLE IF EXISTS model_OWSMessageContentJob")
            return .success(())
        }

        migrator.registerMigration(.deleteMessageRequestInteractionEpoch) { tx in
            try tx.database.execute(
                sql: """
                DELETE FROM "keyvalue" WHERE "collection" = ? AND "key" = ?
                """,
                arguments: ["SSKPreferences", "messageRequestInteractionIdEpoch"],
            )
            return .success(())
        }

        migrator.registerMigration(.addAvatarDefaultColorTable) { tx in
            try Self.createDefaultAvatarColorTable(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.populateAvatarDefaultColorTable) { tx in
            try Self.populateDefaultAvatarColorTable(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addStoryRecipient) { tx in
            try createStoryRecipients(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addAttachmentLastFullscreenViewTimestamp) { tx in
            try tx.database.alter(table: "Attachment") { table in
                table.add(column: "lastFullscreenViewTimestamp", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.addByteCountAndIsFullsizeToBackupAttachmentUpload) { tx in
            // At the time of this migration, no user could have backups
            // and therefore its fine to just drop the table and recreate.
            try tx.database.drop(table: "BackupAttachmentUploadQueue")

            try tx.database.create(table: "BackupAttachmentUploadQueue") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("attachmentRowId", .integer)
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                table.column("maxOwnerTimestamp", .integer)
                table.column("estimatedByteCount", .integer)
                    .notNull()
                table.column("isFullsize", .boolean)
                    .notNull()
            }

            // For efficient cascade deletes and lookups
            try tx.database.create(
                index: "index_BackupAttachmentUploadQueue_on_attachmentRowId",
                on: "BackupAttachmentUploadQueue",
                columns: ["attachmentRowId"],
            )
            // For efficient sorting by timestamp
            try tx.database.create(
                index: "index_BackupAttachmentUploadQueue_on_maxOwnerTimestamp_isFullsize",
                on: "BackupAttachmentUploadQueue",
                columns: ["maxOwnerTimestamp", "isFullsize"],
            )

            return .success(())
        }

        migrator.registerMigration(.refactorBackupAttachmentDownload) { tx in
            // We want to migrate existing rows but the alterations we want to make aren't
            // allowed (removing UNIQUE constraint) so keep both tables then copy.
            try tx.database.rename(table: "BackupAttachmentDownloadQueue", to: "tmp_BackupAttachmentDownloadQueue")

            try tx.database.create(table: "BackupAttachmentDownloadQueue") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("attachmentRowId", .integer)
                    .notNull()
                    .references("Attachment", column: "id", onDelete: .cascade)
                table.column("isThumbnail", .boolean).notNull()
                table.column("maxOwnerTimestamp", .integer)
                table.column("canDownloadFromMediaTier", .boolean).notNull()
                table.column("minRetryTimestamp", .integer).notNull()
                table.column("numRetries", .integer).notNull().defaults(to: 0)
                table.column("state", .integer)
                table.column("estimatedByteCount", .integer).notNull()
            }
            // For efficient cascade deletes and lookups
            try tx.database.create(
                index: "index_BackupAttachmentDownloadQueue_on_attachmentRowId",
                on: "BackupAttachmentDownloadQueue",
                columns: ["attachmentRowId"],
            )
            // For efficient sorting when downloading and popping off the queue,
            // which only applies to those in the Ready state.
            // We download recent thumbnails first, then recent fullsize, then
            // old thumbnails, then old fullsize.
            try tx.database.create(
                index: "index_BackupAttachmentDownloadQueue_on_state_isThumbnail_minRetryTimestamp",
                on: "BackupAttachmentDownloadQueue",
                columns: ["state", "isThumbnail", "minRetryTimestamp"],
            )
            // When we enable storage optimization with paid backups, we want to mark
            // ineligible any fullsize rows which are on the media tier and are old
            // enough that they'd be offloaded if we did download them; we mark these
            // ineligible up front in an UPDATE so they're evicted from the byte count.
            try tx.database.create(
                index: "index_BackupAttachmentDownloadQueue_on_isThumbnail_canDownloadFromMediaTier_state_maxOwnerTimestamp",
                on: "BackupAttachmentDownloadQueue",
                columns: ["isThumbnail", "canDownloadFromMediaTier", "state", "maxOwnerTimestamp"],
            )
            // This lets us quickly sum total estimated byte count per-state, so we can count
            // remaining bytes vs already-downloaded bytes.
            try tx.database.create(
                index: "index_BackupAttachmentDownloadQueue_on_state_estimatedByteCount",
                on: "BackupAttachmentDownloadQueue",
                columns: ["state", "estimatedByteCount"],
            )

            let nowMs = Date().ows_millisecondsSince1970

            // Now copy over from the old table to the new table.
            // At time of writing backups is not launched so the only
            // rows in this table are fullsize transit tier downloads enqueued
            // from a link'n'sync, which were removed when done so they're all
            // in the ready state.
            try tx.database.execute(
                sql: """
                INSERT INTO BackupAttachmentDownloadQueue (
                    attachmentRowId,
                    isThumbnail,
                    maxOwnerTimestamp,
                    canDownloadFromMediaTier,
                    minRetryTimestamp,
                    state,
                    estimatedByteCount
                )
                SELECT
                    attachmentRowId,
                    0, -- no thumbnails; see comment above
                    timestamp,
                    0, -- no media tier downloads; see comment above
                    CASE WHEN timestamp IS NULL
                        THEN 0             -- NULL timestamps first
                        ELSE ? - timestamp -- then newest first
                    END,
                    1, -- ready; see comment above
                    0  -- just set estimated byte count to 0; existing link'n'syncs won't get progress.
                FROM tmp_BackupAttachmentDownloadQueue;
                """,
                arguments: [nowMs],
            )

            try tx.database.drop(table: "tmp_BackupAttachmentDownloadQueue")

            return .success(())
        }

        migrator.registerMigration(.removeAttachmentMediaTierDigestColumn) { tx in
            try tx.database.alter(table: "Attachment") { table in
                table.drop(column: "mediaTierDigestSHA256Ciphertext")
            }
            return .success(())
        }

        migrator.registerMigration(.addListMediaTable) { tx in
            try tx.database.create(table: "ListedBackupMediaObject") { table in
                table.column("id", .integer).primaryKey(autoincrement: true)
                table.column("mediaId", .blob).notNull()
                table.column("cdnNumber", .integer).notNull()
                table.column("objectLength", .integer).notNull()
            }

            try tx.database.create(
                index: "index_ListedBackupMediaObject_on_mediaId",
                on: "ListedBackupMediaObject",
                columns: ["mediaId"],
            )

            return .success(())
        }

        migrator.registerMigration(.recomputeAttachmentMediaNames) { tx in
            try tx.database.execute(
                sql: """
                UPDATE Attachment
                SET mediaName = CASE
                    WHEN sha256ContentHash IS NOT NULL AND encryptionKey IS NOT NULL
                        THEN lower(hex(sha256ContentHash || encryptionKey))
                    ELSE NULL
                END;
                """,
            )

            return .success(())
        }

        migrator.registerMigration(.lastDraftInteractionRowID) { tx in
            try tx.database.alter(table: "model_TSThread") { table in
                table.add(column: "lastDraftInteractionRowId", .integer).defaults(to: 0)
            }

            try tx.database.alter(table: "model_TSThread") { table in
                table.add(column: "lastDraftUpdateTimestamp", .integer).defaults(to: 0)
            }

            return .success(())
        }

        migrator.registerMigration(.addBackupOversizeText) { tx in
            try tx.database.create(table: "BackupOversizeTextCache") { table in
                table.autoIncrementedPrimaryKey("id")
                // Row id of the associated Attachment.
                table.column("attachmentRowId", .integer)
                    .unique()
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                table.column("text", .text)
                    .notNull()
                    // Text length is limited to 128 kibibytes.
                    // enforce at SQL level to prevent ambiguity.
                    .check({ length($0) < (128 * 1024) })
            }

            // NOTE: recreated below because of < vs <=

            return .success(())
        }

        /// Ensure the migration value and the live app value are identical; if we change the live app
        /// value we need a new migration to update the CHECK clause below.
        owsAssertDebug(BackupOversizeTextCache.maxTextLengthBytes == 128 * 1024)

        migrator.registerMigration(.addBackupOversizeTextRedux) { tx in
            // NOTE: recreated because of < vs <=; okay to drop existing table
            try tx.database.drop(table: "BackupOversizeTextCache")

            try tx.database.create(table: "BackupOversizeTextCache") { table in
                table.autoIncrementedPrimaryKey("id")
                // Row id of the associated Attachment.
                table.column("attachmentRowId", .integer)
                    .unique()
                    .references("Attachment", column: "id", onDelete: .cascade)
                    .notNull()
                table.column("text", .text)
                    .notNull()
                    // Text length is limited to 128 kibibytes.
                    // enforce at SQL level to prevent ambiguity.
                    .check({ length($0) <= (128 * 1024) })
            }

            return .success(())
        }

        migrator.registerMigration(.addRetriesToBackupAttachmentUploadQueue) { tx in
            try tx.database.alter(table: "BackupAttachmentUploadQueue") { table in
                table.add(column: "numRetries", .integer).notNull().defaults(to: 0)
                table.add(column: "minRetryTimestamp", .integer).notNull().defaults(to: 0)
            }

            return .success(())
        }

        migrator.registerMigration(.replaceOWSDeviceTable) { tx in
            try tx.database.drop(table: "model_OWSDevice")

            try tx.database.create(table: "OWSDevice") { table in
                table.autoIncrementedPrimaryKey("id")
                table.column("deviceId", .integer).notNull()
                table.column("createdAt", .double).notNull()
                table.column("lastSeenAt", .double).notNull()
                table.column("name", .text)
            }

            return .success(())
        }

        migrator.registerMigration(.reindexBackupAttachmentUploadQueue) { tx in
            try tx.database.drop(index: "index_BackupAttachmentUploadQueue_on_maxOwnerTimestamp_isFullsize")
            // For efficient sorting by timestamp
            try tx.database.create(
                index: "index_BackupAttachmentUploadQueue_on_isFullsize_maxOwnerTimestamp",
                on: "BackupAttachmentUploadQueue",
                columns: ["isFullsize", "maxOwnerTimestamp"],
            )
            return .success(())
        }

        migrator.registerMigration(.dropAllIncrementalMacs) { tx in
            try tx.database.execute(sql: """
                UPDATE Attachment
                SET
                    mediaTierIncrementalMac = NULL,
                    mediaTierIncrementalMacChunkSize = NULL,
                    transitTierIncrementalMac = NULL,
                    transitTierIncrementalMacChunkSize = NULL;
            """)
            return .success(())
        }

        migrator.registerMigration(.addOriginalTransitTierInfoAttachmentColumns) { tx in
            try tx.database.alter(table: "Attachment") { table in
                table.add(column: "originalTransitCdnNumber", .integer)
                table.add(column: "originalTransitCdnKey", .text)
                table.add(column: "originalTransitUploadTimestamp", .integer)
                table.add(column: "originalTransitUnencryptedByteCount", .integer)
                table.add(column: "originalTransitDigestSHA256Ciphertext", .blob)
                table.add(column: "originalTransitTierIncrementalMac", .blob)
                table.add(column: "originalTransitTierIncrementalMacChunkSize", .integer)
            }
            return .success(())
        }

        migrator.registerMigration(.rebuildIncompleteViewOnceIndex) { tx in
            try self.rebuildIncompleteViewOnceIndex(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addIsPollToTSInteraction) { tx in
            try tx.database.alter(table: "model_TSInteraction") { table in
                table.add(column: "isPoll", .boolean)
                    .defaults(to: false)
            }

            try tx.database.create(
                table: "Poll",
            ) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("interactionId", .integer)
                    .notNull()
                    .references(
                        "model_TSInteraction",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("isEnded", .boolean)
                table.column("allowsMultiSelect", .boolean)
            }

            try tx.database.create(
                table: "PollOption",
            ) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("pollId", .integer)
                    .notNull()
                    .references(
                        "Poll",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("option", .text)
                table.column("optionIndex", .integer)
            }

            try tx.database.create(
                table: "PollVote",
            ) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("optionId", .integer)
                    .notNull()
                    .references(
                        "PollOption",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("voteAuthorId", .integer)
                    .unique()
                    .references(
                        "model_SignalRecipient",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("voteCount", .integer)
            }

            try tx.database.create(
                index: "index_poll_on_interactionId",
                on: "Poll",
                columns: ["interactionId"],
            )

            try tx.database.create(
                index: "index_polloption_on_pollId",
                on: "PollOption",
                columns: ["pollId"],
            )

            try tx.database.create(
                index: "index_pollvote_on_optionId",
                on: "PollVote",
                columns: ["optionId"],
            )

            return .success(())
        }

        migrator.registerMigration(.addBackupAttachmentUploadQueueStateColumn) { tx in

            try tx.database.alter(table: "BackupAttachmentUploadQueue") { table in
                table.add(column: "state", .integer)
                    .defaults(to: 0)
            }

            try tx.database.drop(index: "index_BackupAttachmentUploadQueue_on_isFullsize_maxOwnerTimestamp")

            try tx.database.create(
                index: "index_BackupAttachmentUploadQueue_on_state_isFullsize_maxOwnerTimestamp",
                on: "BackupAttachmentUploadQueue",
                columns: ["state", "isFullsize", "maxOwnerTimestamp"],
            )

            return .success(())
        }

        migrator.registerMigration(.addBackupAttachmentUploadQueueTrigger) { tx in
            try tx.database.execute(sql: """
                CREATE TRIGGER __BackupAttachmentUploadQueue_au
                AFTER UPDATE OF state ON BackupAttachmentUploadQueue
                BEGIN
                    DELETE FROM BackupAttachmentUploadQueue
                        WHERE state = 1
                        AND NOT EXISTS (
                            SELECT id FROM BackupAttachmentUploadQueue WHERE state = 0
                        );
                END;
            """)
            return .success(())
        }

        migrator.registerMigration(.migrateRecipientDeviceIds) { tx in
            try migrateRecipientDeviceIds(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.fixUniqueConstraintOnPollVotes) { tx in
            try tx.database.drop(table: "PollVote")

            try tx.database.create(
                table: "PollVote",
            ) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("optionId", .integer)
                    .notNull()
                    .references(
                        "PollOption",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("voteAuthorId", .integer)
                    .references(
                        "model_SignalRecipient",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("voteCount", .integer)
            }

            try tx.database.create(
                index: "index_pollVote_on_voteAuthorId_and_optionId",
                on: "PollVote",
                columns: ["voteAuthorId", "optionId"],
                options: [.unique],
            )
            return .success(())
        }

        migrator.registerMigration(.migrateTSAccountManagerKeyValueStore) { tx in
            let migrator = KeyValueStoreMigrator(collection: "TSStorageUserAccountCollection")
            try migrator.migrateUInt32("TSStorageLocalRegistrationId", tx: tx)
            try migrator.migrateUInt32("TSStorageLocalPniRegistrationId", tx: tx)
            try migrator.migrateBool("TSAccountManager_ManualMessageFetchKey", tx: tx)
            try migrator.migrateBool("TSAccountManager_IsDiscoverableByPhoneNumber", tx: tx)
            try migrator.migrateDate("TSAccountManager_LastSetIsDiscoverableByPhoneNumberKey", tx: tx)
            try migrator.migrateString("TSStorageRegisteredNumberKey", tx: tx)
            try migrator.migrateString("TSStorageRegisteredUUIDKey", tx: tx)
            try migrator.migrateString("TSAccountManager_RegisteredPNIKey", tx: tx)
            try migrator.migrateUInt32("TSAccountManager_DeviceId", tx: tx)
            try migrator.migrateString("TSStorageServerAuthToken", tx: tx)
            try migrator.migrateDate("TSAccountManager_RegistrationDateKey", tx: tx)
            try migrator.migrateBool("TSAccountManager_IsDeregisteredKey", tx: tx)
            try migrator.migrateString("TSAccountManager_ReregisteringPhoneNumberKey", tx: tx)
            try migrator.migrateString("TSAccountManager_ReregisteringUUIDKey", tx: tx)
            try migrator.migrateBool("TSAccountManager_ReregisteringWasPrimaryDeviceKey", tx: tx)
            try migrator.migrateBool("TSAccountManager_IsTransferInProgressKey", tx: tx)
            try migrator.migrateBool("TSAccountManager_WasTransferredKey", tx: tx)
            return .success(())
        }

        migrator.registerMigration(.populateBackupAttachmentUploadProgressKVStore) { tx in
            let maxAttachmentRowId = try Int64.fetchOne(
                tx.database,
                sql: "SELECT MAX(id) FROM Attachment",
            ) ?? 0

            try tx.database.execute(
                sql: """
                    INSERT INTO keyvalue (collection, key, value)
                    VALUES (?, ?, ?)
                """,
                arguments: ["BackupAttachmentUploadProgress", "maxAttachmentRowId", maxAttachmentRowId],
            )

            return .success(())
        }

        // This hasn't launched yet, so its OK to drop and re-create the index.
        // The constraints need to be updated in order to support
        // a history of vote states.
        migrator.registerMigration(.addPollVoteState) { tx in
            try tx.database.alter(table: "PollVote") { table in
                table.add(column: "voteState", .integer)
                    .defaults(to: 0)
            }

            try tx.database.drop(index: "index_pollVote_on_voteAuthorId_and_optionId")
            try tx.database.create(
                index: "index_pollVote_on_voteAuthorId_and_optionId_voteCount",
                on: "PollVote",
                columns: ["voteAuthorId", "optionId", "voteCount"],
                options: [.unique],
            )
            return .success(())
        }

        migrator.registerMigration(.addPreKey) { tx in
            try createPreKey(tx: tx)
            if BuildFlags.decodeDeprecatedPreKeys {
                try migratePreKeys(tx: tx)
            }
            try dropOldPreKeys(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addKyberPreKeyUse) { tx in
            try tx.database.create(table: "KyberPreKeyUse", options: [.withoutRowID]) {
                $0.column("kyberRowId", .integer).notNull()
                    .references("PreKey", column: "rowId", onDelete: .cascade, onUpdate: .cascade)
                $0.column("signedPreKeyIdentity", .integer).notNull()
                $0.column("signedPreKeyId", .integer).notNull()
                $0.column("baseKey", .blob).notNull()
                $0.primaryKey(["kyberRowId", "signedPreKeyIdentity", "signedPreKeyId", "baseKey"])
            }
            return .success(())
        }

        migrator.registerMigration(.uniquifyUsernameLookupRecord) { tx in
            try uniquifyUsernameLookupRecord(caseInsensitive: false, tx: tx)
            return .success(())
        }

        migrator.registerMigration(.fixUpcomingCallLinks) { tx in
            try fixUpcomingCallLinks(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.uniquifyUsernameLookupRecord2) { tx in
            // UsernameLookupRecord_UniqueUsernames enforces UNIQUE-ness for the
            // "username" column of UsernameLookupRecord...but when added was
            // case-sensitive, whereas usernames should be case-insensitive.
            //
            // To that end, replay the uniquification, case-insensitive.
            try tx.database.drop(index: "UsernameLookupRecord_UniqueUsernames")
            try uniquifyUsernameLookupRecord(caseInsensitive: true, tx: tx)
            return .success(())
        }

        migrator.registerMigration(.fixRevokedForRestoredCallLinks) { tx in
            try fixRevokedForRestoredCallLinks(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.fixNameForRestoredCallLinks) { tx in
            try fixNameForRestoredCallLinks(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addPinnedMessagesTable) { tx in
            try tx.database.create(
                table: "PinnedMessage",
            ) { table in
                table.column("id", .integer).primaryKey().notNull()
                table.column("interactionId", .integer)
                    .notNull()
                    .unique()
                    .references(
                        "model_TSInteraction",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("threadId", .integer)
                    .notNull()
                    .references(
                        "model_TSThread",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("expiresAt", .integer)
            }

            try tx.database.create(
                index: "index_PinnedMessage_on_threadId",
                on: "PinnedMessage",
                columns: ["threadId"],
            )

            try tx.database.create(
                index: "partial_index_PinnedMessage_on_expiresAt",
                on: "PinnedMessage",
                columns: ["expiresAt"],
                condition: Column("expiresAt") != nil,
            )

            return .success(())
        }

        migrator.registerMigration(.addPinnedAtTimestampToPinnedMessageTable) { tx in
            try tx.database.alter(table: "PinnedMessage") { table in
                table.add(column: "sentTimestamp", .integer).notNull().defaults(to: 0)
                table.add(column: "receivedTimestamp", .integer).notNull().defaults(to: 0)
            }
            return .success(())
        }

        migrator.registerMigration(.dropTSAttachment) { tx in
            // Delete the legacy attachments folder, which is hopefully already
            // deleted.
            if
                let containerURL = FileManager.default.containerURL(
                    forSecurityApplicationGroupIdentifier: TSConstants.applicationGroup,
                )
            {
                let tsAttachmentsDir = containerURL.path.appendingPathComponent("Attachments")
                try? FileManager.default.removeItem(atPath: tsAttachmentsDir)
            }

            try tx.database.drop(table: "TSAttachmentMigration")
            try tx.database.drop(table: "model_TSAttachment")

            return .success(())
        }

        migrator.registerMigration(.deprecateStoredShouldStartExpireTimer) { tx in
            // In the distant past, there may have been messages with
            // storedShouldStartExpireTimer set that failed to set their
            // expiresAt, which is what drives message expiration. If any still
            // somehow exist, make them expire immediately.
            try tx.database.execute(sql: """
                UPDATE model_TSInteraction
                SET expireStartedAt = 1, expiresAt = 2
                WHERE storedShouldStartExpireTimer IS TRUE
                    AND (expiresAt IS 0 OR expireStartedAt IS 0)
            """)

            // Since storedShouldStartExpireTimer isn't used in any queries, we
            // can drop this index.
            try tx.database.drop(index: "index_interactions_on_threadUniqueId_storedShouldStartExpireTimer_and_expiresAt")
            return .success(())
        }

        migrator.registerMigration(.addSession) { tx in
            try createSession(tx: tx)
            if BuildFlags.migrateDeprecatedSessions {
                try migrateSessions(tx: tx)
            }
            try dropOldSessions(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addRecipientStatus) { tx in
            try addRecipientStatus(tx: tx)
            try migrateRecipientWhitelist(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.createKeyTransparencyTable) { tx in
            try createKeyTransparencyTable(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addAdminDeleteTable) { tx in
            try tx.database.create(
                table: "AdminDelete",
            ) { table in
                table.column("interactionId", .integer)
                    .primaryKey()
                    .notNull()
                    .unique()
                    .references(
                        "model_TSInteraction",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
                table.column("deleteAuthorId", .integer)
                    .references(
                        "model_SignalRecipient",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
            }
            return .success(())
        }

        migrator.registerMigration(.addRecipientStatesToAdminDelete) { tx in
            try tx.database.alter(table: "AdminDelete") { table in
                table.add(column: "recipientAddressStates", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.modifyCallLinkRootKeyConstraint, foreignKeyChecks: .deferred) { tx in
            try modifyCallLinkRootKeyConstraint(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addDevice) { tx in
            try addDevice(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addAttachmentBackfillRequestTable) { tx in
            try tx.database.create(
                table: "AttachmentBackfillInboundRequest",
            ) { table in
                table.autoIncrementedPrimaryKey("id").notNull()
                table.column("interactionId", .integer)
                    .notNull()
                    .unique()
                    .references(
                        "model_TSInteraction",
                        column: "id",
                        onDelete: .cascade,
                        onUpdate: .cascade,
                    )
            }
            return .success(())
        }

        migrator.registerMigration(.wipeBackupAttachmentUploadQueueForLinkedDevices) { tx in
            // We used to save Backup attachment upload records on iPads, but
            // iPads won't ever do uploads. We no longer save those records, so
            // we can wipe out any existing records.
            try wipeBackupAttachmentUploadQueueForLinkedDevices(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.addLastVerifiedGroupNameHashColumn) { transaction in
            try transaction.database.alter(table: "thread_associated_data") { table in
                table.add(column: "lastVerifiedGroupNameHash", .blob)
            }
            return .success(())
        }

        migrator.registerMigration(.setAttachmentContentTypeFromMimeType) { tx in
            // The __Attachment_contentType_au trigger mirrors contentType
            // to MessageAttachmentReference, so it'll be updated as well.
            try tx.database.execute(sql: """
            UPDATE Attachment
            SET contentType = CASE
                WHEN mimeType IN (
                    'video/3gpp',
                    'video/3gpp2',
                    'video/mp4',
                    'video/quicktime',
                    'video/x-m4v',
                    'video/mpeg'
                ) THEN 3
                WHEN mimeType IN (
                    'audio/aac',
                    'audio/x-m4p',
                    'audio/x-m4b',
                    'audio/x-m4a',
                    'audio/wav',
                    'audio/x-wav',
                    'audio/x-mpeg',
                    'audio/mpeg',
                    'audio/mp4',
                    'audio/mp3',
                    'audio/mpeg3',
                    'audio/x-mp3',
                    'audio/x-mpeg3',
                    'audio/aiff',
                    'audio/x-aiff',
                    'audio/3gpp2',
                    'audio/3gpp'
                ) THEN 5
                WHEN mimeType IN (
                    'image/jpeg',
                    'image/pjpeg',
                    'image/png',
                    'image/tiff',
                    'image/x-tiff',
                    'image/bmp',
                    'image/x-windows-bmp',
                    'image/heic',
                    'image/heif',
                    'image/webp',
                    'image/gif',
                    'image/apng',
                    'image/vnd.mozilla.apng'
                ) THEN 2
                ELSE 1
            END
            """)

            return .success(())
        }

        migrator.registerMigration(.dropAttachmentContentTypeAUTrigger) { tx in
            try tx.database.execute(sql: """
            DROP TRIGGER __Attachment_contentType_au
            """)

            return .success(())
        }

        migrator.registerMigration(.addOrphanedAttachmentTimestamp) { tx in
            try tx.database.alter(table: "OrphanedAttachment") { table in
                table.add(column: "timestamp", .integer).defaults(to: 0)
            }

            try tx.database.create(
                index: "index_orphaned_attachment_on_pending_and_timestamp",
                on: "OrphanedAttachment",
                columns: [
                    "isPendingAttachment",
                    "timestamp",
                ],
            )

            return .success(())
        }

        // MARK: - Schema Migration Insertion Point
    }

    private static func registerDataMigrations(migrator: DatabaseMigratorWrapper) {

        // The migration blocks should never throw. If we introduce a crashing
        // migration, we want the crash logs reflect where it occurred.

        migrator.registerMigration(.dataMigration_enableV2RegistrationLockIfNecessary) { transaction in
            if DependenciesBridge.shared.svr.hasMasterKey(transaction: transaction) {
                KeyValueStore(collection: "kOWS2FAManager_Collection")
                    .setBool(true, key: "isRegistrationLockV2Enabled", transaction: transaction)
            }

            return .success(())
        }

        migrator.registerMigration(.dataMigration_resetStorageServiceData) { transaction in
            SSKEnvironment.shared.storageServiceManagerRef.resetLocalData(transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_markAllInteractionsAsNotDeleted) { transaction in
            try transaction.database.execute(sql: "UPDATE model_TSInteraction SET wasRemotelyDeleted = 0")
            return .success(())
        }

        migrator.registerMigration(.dataMigration_turnScreenSecurityOnForExistingUsers) { transaction in
            guard try hasAnyAccountManagerState(tx: transaction) else {
                return .success(())
            }
            // Declare the key value store here, since it's normally only
            // available in SignalMessaging.Preferences.
            let preferencesKeyValueStore = KeyValueStore(collection: "SignalPreferences")
            let screenSecurityKey = "Screen Security Key"
            guard
                !preferencesKeyValueStore.hasValue(
                    screenSecurityKey,
                    transaction: transaction,
                )
            else {
                return .success(())
            }

            preferencesKeyValueStore.setBool(true, key: screenSecurityKey, transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_groupIdMapping) { transaction in
            TSThread.anyEnumerate(transaction: transaction) { thread, _ in
                guard let groupThread = thread as? TSGroupThread else {
                    return
                }
                TSGroupThread.setGroupIdMappingForLegacyThread(
                    threadUniqueId: groupThread.uniqueId,
                    groupId: groupThread.groupId,
                    tx: transaction,
                )
            }
            return .success(())
        }

        migrator.registerMigration(.dataMigration_disableSharingSuggestionsForExistingUsers) { transaction in
            guard try hasAnyAccountManagerState(tx: transaction) else {
                return .success(())
            }
            SSKPreferences.setAreIntentDonationsEnabled(false, transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_removeOversizedGroupAvatars) { transaction in
            var thrownError: Error?
            TSThread.anyEnumerate(transaction: transaction) { thread, stop in
                guard let groupThread = thread as? TSGroupThread else { return }
                guard let avatarData = groupThread.groupModel.legacyAvatarData else { return }
                guard !TSGroupModel.isValidGroupAvatarData(avatarData) else { return }

                var builder = groupThread.groupModel.asBuilder
                builder.avatarDataState = .missing
                builder.avatarUrlPath = nil

                do {
                    let newGroupModel = try builder.build()
                    groupThread.update(with: newGroupModel, transaction: transaction)
                } catch {
                    thrownError = error
                    stop = true
                }
            }
            return thrownError.map { .failure($0) } ?? .success(())
        }

        migrator.registerMigration(.dataMigration_scheduleStorageServiceUpdateForMutedThreads) { transaction in
            TSThread.anyEnumerate(
                transaction: transaction,
                sql: "SELECT * FROM \(TSThread.databaseTableName) WHERE \(threadColumn: .mutedUntilTimestampObsolete) > 0",
                arguments: [],
            ) { thread, _ in
                if let thread = thread as? TSContactThread {
                    SSKEnvironment.shared.storageServiceManagerRef.recordPendingUpdates(updatedAddresses: [thread.contactAddress])
                } else if let thread = thread as? TSGroupThread {
                    SSKEnvironment.shared.storageServiceManagerRef.recordPendingUpdates(groupModel: thread.groupModel)
                } else {
                    owsFail("Unexpected thread type \(thread)")
                }
            }
            return .success(())
        }

        migrator.registerMigration(.dataMigration_populateGroupMember) { transaction in
            TSThread.anyEnumerate(
                transaction: transaction,
                sql: """
                    SELECT *
                    FROM \(TSThread.databaseTableName)
                    WHERE \(threadColumn: .recordType) = \(SDSRecordType.groupThread.rawValue)
                """,
                arguments: [],
            ) { thread, _ in
                // [SDS] TODO: Fetch TSGroupThreads directly.
                let groupThread = thread as! TSGroupThread
                let groupThreadId = groupThread.uniqueId
                let interactionFinder = InteractionFinder(threadUniqueId: groupThreadId)

                groupThread.groupMembership.fullMembers.forEach { address in
                    // Group member addresses are low-trust, and the address cache has
                    // not been populated yet at this point in time. We want to record
                    // as close to a fully qualified address as we can in the database,
                    // so defer to the address from the signal recipient (if one exists)
                    let recipient = DependenciesBridge.shared.recipientDatabaseTable
                        .fetchRecipient(address: address, tx: transaction)
                    let memberAddress = recipient?.address ?? address

                    guard let newAddress = NormalizedDatabaseRecordAddress(address: memberAddress) else {
                        return
                    }

                    guard
                        TSGroupMember.groupMember(
                            for: memberAddress,
                            in: groupThreadId,
                            transaction: transaction,
                        ) == nil
                    else {
                        // If we already have a group member populated, for
                        // example from an earlier data migration, we should
                        // _not_ try and insert.
                        return
                    }

                    let latestInteraction = interactionFinder.latestInteraction(
                        from: memberAddress,
                        transaction: transaction,
                    )
                    let memberRecord = TSGroupMember(
                        address: newAddress,
                        groupThreadId: groupThread.uniqueId,
                        lastInteractionTimestamp: latestInteraction?.timestamp ?? 0,
                    )
                    memberRecord.anyInsert(transaction: transaction)
                }
            }
            return .success(())
        }

        migrator.registerMigration(.dataMigration_cullInvalidIdentityKeySendingErrors) { transaction in
            let sql = """
                DELETE FROM \(InteractionRecord.databaseTableName)
                WHERE \(interactionColumn: .recordType) = ?
            """
            try transaction.database.execute(
                sql: sql,
                arguments: [SDSRecordType.invalidIdentityKeySendingErrorMessage.rawValue],
            )
            return .success(())
        }

        migrator.registerMigration(.dataMigration_moveToThreadAssociatedData) { transaction in
            var thrownError: Error?
            TSThread.anyEnumerate(transaction: transaction) { thread, stop in
                do {
                    try ThreadAssociatedData(
                        threadUniqueId: thread.uniqueId,
                        isArchived: thread.isArchivedObsolete,
                        isMarkedUnread: thread.isMarkedUnreadObsolete,
                        mutedUntilTimestamp: thread.mutedUntilTimestampObsolete,
                        // audioPlaybackRate and lastVerifiedGroupNameHash didn't exist pre-migration,
                        // just write the default
                        audioPlaybackRate: 1,
                        lastVerifiedGroupNameHash: nil,
                    ).insert(transaction.database)
                } catch {
                    thrownError = error
                    stop = true
                }
            }
            return thrownError.map { .failure($0) } ?? .success(())
        }

        migrator.registerMigration(.dataMigration_senderKeyStoreKeyIdMigration) { transaction in
            OldSenderKeyStore.performKeyIdMigration(transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_reindexGroupMembershipAndMigrateLegacyAvatarDataFixed) { transaction in
            TSThread.anyEnumerate(
                transaction: transaction,
                sql: "SELECT * FROM \(TSThread.databaseTableName) WHERE \(threadColumn: .recordType) = \(SDSRecordType.groupThread.rawValue)",
                arguments: [],
            ) { thread, _ in
                // [SDS] TODO: Fetch TSGroupThreads directly.
                let thread = thread as! TSGroupThread
                autoreleasepool {
                    let groupModel = thread.groupModel

                    guard
                        let legacyAvatarData = groupModel.legacyAvatarData,
                        !legacyAvatarData.isEmpty,
                        TSGroupModel.isValidGroupAvatarData(legacyAvatarData)
                    else {
                        groupModel.avatarHash = nil
                        groupModel.legacyAvatarData = nil
                        return
                    }

                    failIfThrows {
                        try groupModel.persistAvatarData(legacyAvatarData)
                    }
                    groupModel.legacyAvatarData = nil

                    thread.anyUpsert(transaction: transaction)
                }
            }

            // We previously re-indexed threads and group members here, but those
            // migrations are obsoleted by dataMigration_indexSearchableNames.

            return .success(())
        }

        migrator.registerMigration(.dataMigration_repairAvatar) { transaction in
            guard try hasAnyAccountManagerState(tx: transaction) else {
                return .success(())
            }
            // Declare the key value store here, since it's normally only
            // available in SignalMessaging.Preferences.
            let preferencesKeyValueStore = KeyValueStore(collection: Self.migrationSideEffectsCollectionName)
            let key = Self.avatarRepairAttemptCount
            preferencesKeyValueStore.setInt(0, key: key, transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_dropEmojiAvailabilityStore) { transaction in
            // This is a bit of a layering violation, since these tables were previously managed in the app layer.
            // In the long run we'll have a general "unused KeyValueStore cleaner" migration,
            // but for now this should drop 2000 or so rows for free.
            KeyValueStore(collection: "Emoji+availableStore").removeAll(transaction: transaction)
            KeyValueStore(collection: "Emoji+metadataStore").removeAll(transaction: transaction)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_dropSentStories) { transaction in
            let sql = """
                DELETE FROM \(StoryMessage.databaseTableName)
                WHERE \(StoryMessage.columnName(.direction)) = \(StoryMessage.Direction.outgoing.rawValue)
            """
            try transaction.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_syncGroupStories) { transaction in
            for thread in ThreadFinder().storyThreads(includeImplicitGroupThreads: false, transaction: transaction) {
                guard let thread = thread as? TSGroupThread else { continue }
                SSKEnvironment.shared.storageServiceManagerRef.recordPendingUpdates(groupModel: thread.groupModel)
            }
            return .success(())
        }

        migrator.registerMigration(.dataMigration_deleteOldGroupCapabilities) { transaction in
            let sql = """
                DELETE FROM \(KeyValueStore.tableName)
                WHERE \(KeyValueStore.collectionColumnName)
                IN ("GroupManager.senderKeyCapability", "GroupManager.announcementOnlyGroupsCapability", "GroupManager.groupsV2MigrationCapability")
            """
            try transaction.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_updateStoriesDisabledInAccountRecord) { transaction in
            SSKEnvironment.shared.storageServiceManagerRef.recordPendingLocalAccountUpdates()
            return .success(())
        }

        migrator.registerMigration(.dataMigration_removeGroupStoryRepliesFromSearchIndex) { transaction in
            let uniqueIdSql = """
                SELECT \(interactionColumn: .uniqueId)
                FROM \(InteractionRecord.databaseTableName)
                WHERE \(interactionColumn: .isGroupStoryReply) = 1
            """
            let uniqueIds = try String.fetchAll(transaction.database, sql: uniqueIdSql)

            guard !uniqueIds.isEmpty else {
                return .success(())
            }

            let indexUpdateSql = """
                DELETE FROM \(FullTextSearchIndexer.contentTableName)
                WHERE \(FullTextSearchIndexer.uniqueIdColumn) IN (\(uniqueIds.map { "\"\($0)\"" }.joined(separator: ", ")))
                AND \(FullTextSearchIndexer.collectionColumn) = 'TSInteraction'
            """
            try transaction.database.execute(sql: indexUpdateSql)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_populateStoryContextAssociatedDataLastReadTimestamp) { transaction in
            let sql = """
                UPDATE model_StoryContextAssociatedData
                SET lastReadTimestamp = lastViewedTimestamp
            """
            try transaction.database.execute(sql: sql)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_scheduleStorageServiceUpdateForSystemContacts) { transaction in
            // We've added fields on the StorageService ContactRecord proto for
            // their "system name", or the name of their associated system
            // contact, if present. Consequently, for all Signal contacts with
            // a system contact, we should schedule a StorageService update.
            //
            // We only want to do this if we are the primary device, since only
            // the primary device's system contacts are synced.

            let tsAccountManager = DependenciesBridge.shared.tsAccountManager
            guard tsAccountManager.registrationState(tx: transaction).isPrimaryDevice ?? false else {
                return .success(())
            }

            var accountsToRemove: Set<SignalAccount> = []

            SignalAccount.anyEnumerate(transaction: transaction) { account, _ in
                guard account.isFromLocalAddressBook else {
                    // Skip any accounts that do not have a system contact
                    return
                }

                accountsToRemove.insert(account)
            }

            SSKEnvironment.shared.storageServiceManagerRef.recordPendingUpdates(updatedAddresses: accountsToRemove.map { $0.recipientAddress })
            return .success(())
        }

        migrator.registerMigration(.dataMigration_ensureLocalDeviceId) { tx in
            let store = NewKeyValueStore(collection: "TSStorageUserAccountCollection")
            let localAci = try store.fetchValueOrThrow(String.self, forKey: "TSStorageRegisteredUUIDKey", tx: tx)
            if localAci != nil {
                let localDeviceId = try store.fetchValueOrThrow(Int64.self, forKey: "TSAccountManager_DeviceId", tx: tx)
                if localDeviceId == nil {
                    // If we don't have a device id written, put the primary device id.
                    try store.writeValueOrThrow(1, forKey: "TSAccountManager_DeviceId", tx: tx)
                }
            }
            return .success(())
        }

        migrator.registerMigration(.dataMigration_indexSearchableNames) { tx in
            // Drop everything in case somebody adds an unrelated migration that
            // populates this table. (This has happened in the past.)
            try tx.database.execute(sql: """
            DELETE FROM "\(SearchableNameIndexerImpl.Constants.databaseTableName)"
            """)
            let searchableNameIndexer = DependenciesBridge.shared.searchableNameIndexer
            searchableNameIndexer.indexEverything(tx: tx)
            return .success(())
        }

        migrator.registerMigration(.dataMigration_removeSystemContacts) { transaction in
            let keyValueCollections = [
                "ContactsManagerCache.uniqueIdStore",
                "ContactsManagerCache.phoneNumberStore",
                "ContactsManagerCache.allContacts",
            ]

            for collection in keyValueCollections {
                KeyValueStore(collection: collection).removeAll(transaction: transaction)
            }

            return .success(())
        }

        migrator.registerMigration(.dataMigration_clearLaunchScreenCache2) { _ in
            OWSFileSystem.deleteFileIfExists(NSHomeDirectory() + "/Library/SplashBoard")
            return .success(())
        }

        migrator.registerMigration(.dataMigration_resetLinkedDeviceAuthorMergeBuilder) { tx in
            let tsAccountManager = DependenciesBridge.shared.tsAccountManager
            guard tsAccountManager.registrationState(tx: tx).isPrimaryDevice != true else {
                return .success(())
            }

            let keyValueCollections = [
                "AuthorMergeMetadata",
                "AuthorMergeNextRowId",
            ]

            for collection in keyValueCollections {
                KeyValueStore(collection: collection).removeAll(transaction: tx)
            }
            return .success(())
        }

        // MARK: - Data Migration Insertion Point
    }

    // MARK: - Migrations

    static func createStoryContextAssociatedData(tx transaction: DBWriteTransaction) throws {
        try transaction.database.create(table: StoryContextAssociatedData.databaseTableName) { table in
            table.autoIncrementedPrimaryKey(StoryContextAssociatedData.columnName(.id))
                .notNull()
            table.column(StoryContextAssociatedData.columnName(.recordType), .integer)
                .notNull()
            table.column(StoryContextAssociatedData.columnName(.uniqueId))
                .notNull()
                .unique(onConflict: .fail)
            table.column(StoryContextAssociatedData.columnName(.contactAci), .text)
            table.column(StoryContextAssociatedData.columnName(.groupId), .blob)
            table.column(StoryContextAssociatedData.columnName(.isHidden), .boolean)
                .notNull()
                .defaults(to: false)
            table.column(StoryContextAssociatedData.columnName(.latestUnexpiredTimestamp), .integer)
            table.column(StoryContextAssociatedData.columnName(.lastReceivedTimestamp), .integer)
            table.column(StoryContextAssociatedData.columnName(.lastViewedTimestamp), .integer)
        }
        try transaction.database.create(
            index: "index_story_context_associated_data_contact_on_contact_uuid",
            on: StoryContextAssociatedData.databaseTableName,
            columns: [StoryContextAssociatedData.columnName(.contactAci)],
        )
        try transaction.database.create(
            index: "index_story_context_associated_data_contact_on_group_id",
            on: StoryContextAssociatedData.databaseTableName,
            columns: [StoryContextAssociatedData.columnName(.groupId)],
        )
    }

    static func populateStoryContextAssociatedData(tx transaction: DBWriteTransaction) throws {
        // All we need to do is iterate over ThreadAssociatedData; one exists for every
        // thread, so we can pull hidden state from the associated data and received/viewed
        // timestamps from their threads and have a copy of everything we need.
        try Row.fetchCursor(transaction.database, sql: """
            SELECT * FROM thread_associated_data
        """).forEach { threadAssociatedDataRow in
            guard
                let hideStory = (threadAssociatedDataRow["hideStory"] as? NSNumber)?.boolValue,
                let threadUniqueId = threadAssociatedDataRow["threadUniqueId"] as? String
            else {
                owsFailDebug("Did not find hideStory or threadUniqueId columnds on ThreadAssociatedData table")
                return
            }
            let insertSQL = """
            INSERT INTO model_StoryContextAssociatedData (
                recordType,
                uniqueId,
                contactUuid,
                groupId,
                isHidden,
                latestUnexpiredTimestamp,
                lastReceivedTimestamp,
                lastViewedTimestamp
            )
            VALUES ('0', ?, ?, ?, ?, ?, ?, ?)
            """

            if
                let threadRow = try? Row.fetchOne(
                    transaction.database,
                    sql: """
                        SELECT * FROM model_TSThread
                        WHERE uniqueId = ?
                    """,
                    arguments: [threadUniqueId],
                )
            {
                let lastReceivedStoryTimestamp = (threadRow["lastReceivedStoryTimestamp"] as? NSNumber)?.uint64Value
                let latestUnexpiredTimestamp = (lastReceivedStoryTimestamp ?? 0) > Date().ows_millisecondsSince1970 - UInt64.dayInMs
                    ? lastReceivedStoryTimestamp : nil
                let lastViewedStoryTimestamp = (threadRow["lastViewedStoryTimestamp"] as? NSNumber)?.uint64Value
                if
                    let groupModelData = threadRow["groupModel"] as? Data,
                    let groupId = try decodeGroupIdFromGroupModelData(groupModelData)
                {
                    try transaction.database.execute(
                        sql: insertSQL,
                        arguments: [
                            UUID().uuidString,
                            nil,
                            groupId,
                            hideStory,
                            latestUnexpiredTimestamp,
                            lastReceivedStoryTimestamp,
                            lastViewedStoryTimestamp,
                        ],
                    )
                } else if
                    let contactUuidString = threadRow["contactUUID"] as? String
                {
                    // Okay to ignore e164 addresses because we can't have updated story metadata
                    // for those contact threads anyway.
                    try transaction.database.execute(
                        sql: insertSQL,
                        arguments: [
                            UUID().uuidString,
                            contactUuidString,
                            nil,
                            hideStory,
                            latestUnexpiredTimestamp,
                            lastReceivedStoryTimestamp,
                            lastViewedStoryTimestamp,
                        ],
                    )
                }
            } else {
                // If we couldn't find a thread, that means this associated data was
                // created for a group we don't know about yet.
                // Stories is in beta at the time of this migration, so we will just drop it.
                Logger.info("Dropping StoryContextAssociatedData migration for ThreadAssociatedData without a TSThread")
            }
        }
    }

    static func dropColumnsMigratedToStoryContextAssociatedData(tx transaction: DBWriteTransaction) throws {
        // Drop the old columns since they are no longer needed.
        try transaction.database.alter(table: "model_TSThread") { alteration in
            alteration.drop(column: "lastViewedStoryTimestamp")
            alteration.drop(column: "lastReceivedStoryTimestamp")
        }
        try transaction.database.alter(table: "thread_associated_data") { alteration in
            alteration.drop(column: "hideStory")
        }
    }

    static func populateStoryMessageReplyCount(tx transaction: DBWriteTransaction) throws {
        let storyMessagesSql = """
            SELECT id, timestamp, authorUuid, groupId
            FROM model_StoryMessage
        """
        let storyMessages = try Row.fetchAll(transaction.database, sql: storyMessagesSql)
        for storyMessage in storyMessages {
            guard
                let id = storyMessage["id"] as? Int64,
                let timestamp = storyMessage["timestamp"] as? Int64,
                let authorUuid = storyMessage["authorUuid"] as? String
            else {
                continue
            }
            guard authorUuid != "00000000-0000-0000-0000-000000000001" else {
                // Skip the system story
                continue
            }
            let groupId = storyMessage["groupId"] as? Data
            let isGroupStoryMessage = groupId != nil
            // Use the index we have on storyTimestamp, storyAuthorUuidString, isGroupStoryReply
            let replyCountSql = """
                SELECT COUNT(*)
                FROM model_TSInteraction
                WHERE (
                    storyTimestamp = ?
                    AND storyAuthorUuidString = ?
                    AND isGroupStoryReply = ?
                )
            """
            let replyCount = try Int.fetchOne(
                transaction.database,
                sql: replyCountSql,
                arguments: [timestamp, authorUuid, isGroupStoryMessage],
            ) ?? 0

            try transaction.database.execute(
                sql: """
                    UPDATE model_StoryMessage
                    SET replyCount = ?
                    WHERE id = ?
                """,
                arguments: [replyCount, id],
            )
        }
    }

    static func migrateThreadReplyInfos(transaction: DBWriteTransaction) throws {
        let collection = "TSThreadReplyInfo"
        try transaction.database.execute(
            sql: """
                UPDATE "keyvalue" SET
                    "value" = json_replace("value", '$.author', json_extract("value", '$.author.backingUuid'))
                WHERE
                    "collection" IS ?
                    AND json_valid("value")
            """,
            arguments: [collection],
        )
        try transaction.database.execute(
            sql: """
                DELETE FROM "keyvalue" WHERE
                    "collection" IS ?
                    AND json_valid("value")
                    AND json_extract("value", '$.author') IS NULL
            """,
            arguments: [collection],
        )
    }

    static func migrateVoiceMessageDrafts(
        transaction: DBWriteTransaction,
        appSharedDataUrl: URL,
        copyItem: (URL, URL) throws -> Void,
    ) throws {
        // In the future, this entire migration could be safely replaced by the
        // `DELETE FROM…` query. The impact of that change would be to delete old
        // voice memo drafts rather than migrate them to the current version.

        let collection = "DraftVoiceMessage"
        let baseUrl = URL(fileURLWithPath: "draft-voice-messages", isDirectory: true, relativeTo: appSharedDataUrl)

        let oldRows = try Row.fetchAll(
            transaction.database,
            sql: "SELECT key, value FROM keyvalue WHERE collection IS ?",
            arguments: [collection],
        )
        try transaction.database.execute(sql: "DELETE FROM keyvalue WHERE collection IS ?", arguments: [collection])
        for oldRow in oldRows {
            let uniqueThreadId: String = oldRow[0]
            let hasDraft = try? NSKeyedUnarchiver.unarchivedObject(ofClass: NSNumber.self, from: oldRow[1])?.boolValue
            guard hasDraft == true else {
                continue
            }
            let oldRelativePath = uniqueThreadId.addingPercentEncoding(withAllowedCharacters: .alphanumerics)!
            let newRelativePath = UUID().uuidString
            do {
                try copyItem(
                    baseUrl.appendingPathComponent(oldRelativePath, isDirectory: true),
                    baseUrl.appendingPathComponent(newRelativePath, isDirectory: true),
                )
            } catch {
                Logger.warn("Couldn't migrate voice message draft \(error.shortDescription)")
                continue
            }
            try transaction.database.execute(
                sql: "INSERT INTO keyvalue (collection, key, value) VALUES (?, ?, ?)",
                arguments: [
                    collection,
                    uniqueThreadId,
                    try NSKeyedArchiver.archivedData(withRootObject: newRelativePath, requiringSecureCoding: true),
                ],
            )
        }
    }

    static func createEditRecordTable(tx: DBWriteTransaction) throws {
        try tx.database.create(
            table: "EditRecord",
        ) { table in
            table.autoIncrementedPrimaryKey("id")
                .notNull()
            table.column("latestRevisionId", .text)
                .notNull()
                .references(
                    "model_TSInteraction",
                    column: "id",
                    onDelete: .cascade,
                )
            table.column("pastRevisionId", .text)
                .notNull()
                .references(
                    "model_TSInteraction",
                    column: "id",
                    onDelete: .cascade,
                )
        }

        try tx.database.create(
            index: "index_edit_record_on_latest_revision_id",
            on: "EditRecord",
            columns: ["latestRevisionId"],
        )

        try tx.database.create(
            index: "index_edit_record_on_past_revision_id",
            on: "EditRecord",
            columns: ["pastRevisionId"],
        )
    }

    static func migrateEditRecordTable(tx: DBWriteTransaction) throws {
        let finalTableName = EditRecord.databaseTableName
        let tempTableName = "\(finalTableName)_temp"

        // Create a temporary EditRecord with correct constraints/types
        try tx.database.create(
            table: tempTableName,
        ) { table in
            table.autoIncrementedPrimaryKey("id")
                .notNull()
            table.column("latestRevisionId", .integer)
                .notNull()
                .references(
                    "model_TSInteraction",
                    column: "id",
                    onDelete: .restrict,
                )
            table.column("pastRevisionId", .integer)
                .notNull()
                .references(
                    "model_TSInteraction",
                    column: "id",
                    onDelete: .restrict,
                )
        }

        // Migrate edit records from old table to new
        try tx.database.execute(sql: """
        INSERT INTO \(tempTableName)
            (id, latestRevisionId, pastRevisionId)
            SELECT id, latestRevisionId, pastRevisionId
            FROM \(finalTableName);
        """)

        // Remove the old Edit record table & indexes
        try tx.database.execute(sql: "DROP TABLE IF EXISTS \(finalTableName);")
        try tx.database.execute(sql: "DROP INDEX IF EXISTS index_edit_record_on_latest_revision_id;")
        try tx.database.execute(sql: "DROP INDEX IF EXISTS index_edit_record_on_past_revision_id;")

        // Rename the new table to the correct name
        try tx.database.execute(sql: "ALTER TABLE \(tempTableName) RENAME TO \(finalTableName);")

        // Rebuild the indexes
        try tx.database.create(
            index: "index_edit_record_on_latest_revision_id",
            on: "\(finalTableName)",
            columns: ["latestRevisionId"],
        )

        try tx.database.create(
            index: "index_edit_record_on_past_revision_id",
            on: "\(finalTableName)",
            columns: ["pastRevisionId"],
        )
    }

    private static func enableFts5SecureDelete(for tableName: StaticString, db: Database) throws {
        try db.execute(sql: """
            INSERT INTO "\(tableName)" ("\(tableName)", "rank") VALUES ('secure-delete', 1)
        """)
    }

    static func removeLocalProfileSignalRecipient(in db: Database) throws {
        try db.execute(sql: """
        DELETE FROM "model_SignalRecipient" WHERE "recipientPhoneNumber" = 'kLocalProfileUniqueId'
        """)
    }

    static func removeRedundantPhoneNumbers(
        in db: Database,
        tableName: StaticString,
        serviceIdColumn: StaticString,
        phoneNumberColumn: StaticString,
    ) throws {
        // If kLocalProfileUniqueId has a ServiceId, remove it. This should only
        // exist for OWSUserProfile (and it shouldn't have a ServiceId), but it
        // unfortunately exists for threads as well.
        try db.execute(sql: """
        UPDATE "\(tableName)"
        SET
            "\(serviceIdColumn)" = NULL
        WHERE
            "\(phoneNumberColumn)" = 'kLocalProfileUniqueId'
        """)

        // If there are any rows with an ACI & phone number, remove the latter.
        try db.execute(sql: """
        UPDATE "\(tableName)"
        SET
            "\(phoneNumberColumn)" = NULL
        WHERE
            "\(serviceIdColumn)" < 'PNI:'
            AND "\(phoneNumberColumn)" IS NOT NULL;
        """)

        // If there are any rows with just a phone number, try to replace it with the ACI.
        try db.execute(sql: """
        UPDATE "\(tableName)"
        SET
            "\(phoneNumberColumn)" = NULL,
            "\(serviceIdColumn)" = "signalRecipientAciString"
        FROM (
            SELECT
                "recipientUUID" AS "signalRecipientAciString",
                "recipientPhoneNumber" AS "signalRecipientPhoneNumber"
            FROM "model_SignalRecipient"
        )
        WHERE
            "\(serviceIdColumn)" IS NULL
            AND "\(phoneNumberColumn)" = "signalRecipientPhoneNumber"
            AND "signalRecipientAciString" IS NOT NULL;
        """)
    }

    static func createV2AttachmentTables(_ tx: DBWriteTransaction) throws -> Result<Void, Error> {

        // MARK: Attachment table

        try tx.database.create(table: "Attachment") { table in
            table.autoIncrementedPrimaryKey("id").notNull()
            table.column("blurHash", .text)
            // Sha256 hash of the plaintext contents.
            // Non-null for downloaded attachments.
            table.column("sha256ContentHash", .blob).unique(onConflict: .abort)
            // Used for addressing in the media tier.
            // Non-null for downloaded attachments even if media tier unavailable.
            table.column("mediaName", .text).unique(onConflict: .abort)
            // Byte count of the encrypted file on disk, including cryptography overhead and padding.
            // Non-null for downloaded attachments.
            table.column("encryptedByteCount", .integer)
            // Byte count of the decrypted plaintext contents, excluding padding.
            // Non-null for downloaded attachments.
            table.column("unencryptedByteCount", .integer)
            // MIME type for the attachment, from the sender.
            table.column("mimeType", .text).notNull()
            // Key used to encrypt the file on disk. Composed of { AES encryption key | HMAC key }
            table.column("encryptionKey", .blob).notNull()
            // Sha256 digest of the encrypted ciphertext on disk, including iv prefix and hmac suffix.
            // Non-null for downloaded attachments.
            table.column("digestSHA256Ciphertext", .blob)
            // Path to the encrypted fullsize file on disk, if downloaded.
            table.column("localRelativeFilePath", .text)
            // Validated type of attachment.
            // null - undownloaded
            // 0 - invalid (failed validation for the MIME type)
            // 1 - arbitrary file
            // 2 - image (from known image MIME types)
            // 3 - video (from known video MIME types)
            // 4 - animated image (from known animated image MIME types)
            // 5 - audio (from known audio MIME types)
            table.column("contentType", .integer)
            // "Transit tier" CDN info used for sending/receiving attachments.
            // Non-null if uploaded.
            table.column("transitCdnNumber", .integer)
            table.column("transitCdnKey", .text)
            table.column("transitUploadTimestamp", .integer)
            // Key used for the encrypted blob uploaded to transit tier CDN and used for sending.
            // May or may not be the same as `encryptionKey`.
            table.column("transitEncryptionKey", .blob)
            table.column("transitEncryptedByteCount", .integer)
            table.column("transitDigestSHA256Ciphertext", .blob)
            // Local timestamp when we last failed a download from transit tier CDN.
            // Set _after_ the download fails; nil if downloaded or if no attempt has been made.
            table.column("lastTransitDownloadAttemptTimestamp", .integer)
            // "Media tier" CDN info used for backing up full size attachments.
            // Non-null if uploaded to the media tier.
            table.column("mediaTierCdnNumber", .integer)
            table.column("mediaTierUploadEra", .text)
            // Local timestamp when we last failed a download from media tier CDN.
            // Set _after_ the download fails; nil if downloaded or if no attempt has been made.
            table.column("lastMediaTierDownloadAttemptTimestamp", .integer)
            // "Media tier" CDN info used for backing up attachment _thumbnails_.
            // Thumbnails are only used for visual media content types.
            // Non-null if uploaded to the media tier.
            table.column("thumbnailCdnNumber", .integer)
            table.column("thumbnailUploadEra", .text)
            // Local timestamp when we last failed a thumbnail download from media tier CDN.
            // Set _after_ the download fails; nil if downloaded or if no attempt has been made.
            table.column("lastThumbnailDownloadAttemptTimestamp", .integer)
            // Path to the encrypted thumbnail file on disk, if downloaded.
            // Encrypted using the `encryptionKey` column.
            table.column("localRelativeFilePathThumbnail", .text)
            // Cached pre-computed attributes set for downloaded media on a per-type basis.
            // Non-null for rows with respective contentTypes.
            table.column("cachedAudioDurationSeconds", .double)
            table.column("cachedMediaHeightPixels", .integer)
            table.column("cachedMediaWidthPixels", .integer)
            table.column("cachedVideoDurationSeconds", .double)
            // Path to the encrypted serialized audio waveform representation on disk.
            // Nullable even for audio attachments.
            // Encrypted using the `encryptionKey` column.
            table.column("audioWaveformRelativeFilePath", .text)
            // Path to the encrypted video still frame on disk.
            // Nullable even for video attachments.
            // Encrypted using the `encryptionKey` column.
            table.column("videoStillFrameRelativeFilePath", .text)
        }

        // MARK: Attachment indexes

        // Note: sha256ContentHash, mediaName indexes implicit from UNIQUE constraint.

        // For finding attachments by type.
        try tx.database.create(
            index: "index_attachment_on_contentType_and_mimeType",
            on: "Attachment",
            columns: [
                "contentType",
                "mimeType",
            ],
        )

        // MARK: MessageAttachmentReference table

        try tx.database.create(table: "MessageAttachmentReference") { table in
            // The type of message owner reference represented by this row.
            // 0 - message attachment
            // 1 - Long message body (text)
            // 2 - message link preview
            // 3 - quoted reply attachment
            // 4 - message sticker
            table.column("ownerType", .integer).notNull()
            // Row id of the owning message.
            table.column("ownerRowId", .integer)
                .references("model_TSInteraction", column: "id", onDelete: .cascade)
                .notNull()
            // Row id of the associated Attachment.
            table.column("attachmentRowId", .integer)
                .references("Attachment", column: "id", onDelete: .cascade)
                .notNull()
            // Local timestamp the message was received, or created if outgoing.
            table.column("receivedAtTimestamp", .integer).notNull()
            // Mirrored from `Attachment` table's `contentType`.
            table.column("contentType", .integer)
            // Rendering hint from the sender.
            // 0 - default
            // 1 - voice message
            // 2 - borderless image
            // 3 - looping video
            table.column("renderingFlag", .integer).notNull()
            // Uniquely identifies the attachment among other attachments on the same owning TSMessage.
            // Optional for message attachment owner type, null for other types.
            table.column("idInMessage", .text)
            // Ordering of the attachment on the same owning TSMessage.
            // Non-null for message attachment owner type, null for other types.
            table.column("orderInMessage", .integer)
            // Row id of the TSThread the owning message belongs to.
            table.column("threadRowId", .integer)
                .references("model_TSThread", column: "id", onDelete: .cascade)
                .notNull()
            // Unused for contemporary messages but may be non-null for legacy instances.
            table.column("caption", .text)
            // File name from sender for display purposes only.
            table.column("sourceFilename", .text)
            // Byte count of the decrypted plaintext contents excluding padding, according to the sender.
            // Will match `Attachment.unencryptedByteCount` once downloaded for well-behaving senders;
            // a mismatch results in a rejected download.
            table.column("sourceUnencryptedByteCount", .integer)
            // Pixel height/width of visual media according to the sender.
            table.column("sourceMediaHeightPixels", .integer)
            table.column("sourceMediaWidthPixels", .integer)
            // Non-null for message sticker owners, null for other types.
            table.column("stickerPackId", .blob)
            table.column("stickerId", .integer)
        }

        // MARK: MessageAttachmentReference indexes

        // For finding attachments associated with a given message owner.
        try tx.database.create(
            index: "index_message_attachment_reference_on_ownerRowId_and_ownerType",
            on: "MessageAttachmentReference",
            columns: [
                "ownerRowId",
                "ownerType",
            ],
        )

        // For getting all owners of a given attachment.
        try tx.database.create(
            index: "index_message_attachment_reference_on_attachmentRowId",
            on: "MessageAttachmentReference",
            columns: [
                "attachmentRowId",
            ],
        )

        // For finding specific attachments on a given message.
        try tx.database.create(
            index: "index_message_attachment_reference_on_ownerRowId_and_idInMessage",
            on: "MessageAttachmentReference",
            columns: [
                "ownerRowId",
                "idInMessage",
            ],
        )

        // For finding attachments associated with a given sticker.
        // Sticker messages attach the sticker image source itself. We might later acquire the actual
        // sticker data from its source pack; in this case we might want to find existing references
        // to the sticker and replace them with the new canonical reference.
        try tx.database.create(
            index: "index_message_attachment_reference_on_stickerPackId_and_stickerId",
            on: "MessageAttachmentReference",
            columns: [
                "stickerPackId",
                "stickerId",
            ],
        )

        // For the all media view; it shows message body media for a given thread,
        // filtered by content type + rendering flag, sorted by the timestamp and then
        // rowId of the owning message, and finally sorted by orderInMessage on that message.
        try tx.database.create(
            index:
            "index_message_attachment_reference_on"
                + "_threadRowId"
                + "_and_ownerType"
                + "_and_contentType"
                + "_and_renderingFlag"
                + "_and_receivedAtTimestamp"
                + "_and_ownerRowId"
                + "_and_orderInMessage",
            on: "MessageAttachmentReference",
            columns: [
                "threadRowId",
                "ownerType",
                "contentType",
                "renderingFlag",
                "receivedAtTimestamp",
                "ownerRowId",
                "orderInMessage",
            ],
        )

        // MARK: StoryMessageAttachmentReference table

        try tx.database.create(table: "StoryMessageAttachmentReference") { table in
            // The type of owner reference represented by this row.
            // 0 - media story message
            // 1 - text story link preview
            table.column("ownerType", .integer).notNull()
            // Row id of the owning story message.
            table.column("ownerRowId", .integer)
                .references("model_StoryMessage", column: "id", onDelete: .cascade)
                .notNull()
            // Row id of the associated Attachment.
            table.column("attachmentRowId", .integer)
                .references("Attachment", column: "id", onDelete: .cascade)
                .notNull()
            // Rendering hint from the sender.
            // Equivalent to `loop` or `gif` rendering flag.
            table.column("shouldLoop", .boolean).notNull()
            // Optional for media owner types. Null for text story owner types.
            table.column("caption", .text)
            // Serialized `Array<NSRangedValue<MessageBodyRanges.CollapsedStyle>>`
            // Optional for media owner types. Null for text story owner types.
            table.column("captionBodyRanges", .blob)
            // File name from sender for display purposes only.
            table.column("sourceFilename", .text)
            // Byte count of the decrypted plaintext contents excluding padding, according to the sender.
            // Will match `Attachment`'s `unencryptedByteCount` once downloaded for
            // well-behaving senders, a mismatch results in a rejected download.
            table.column("sourceUnencryptedByteCount", .integer)
            // Pixel height/width of visual media according to the sender.
            table.column("sourceMediaHeightPixels", .integer)
            table.column("sourceMediaWidthPixels", .integer)
        }

        // MARK: StoryMessageAttachmentReference indexes

        // For finding attachments associated with a given story message owner.
        try tx.database.create(
            index: "index_story_message_attachment_reference_on_ownerRowId_and_ownerType",
            on: "StoryMessageAttachmentReference",
            columns: [
                "ownerRowId",
                "ownerType",
            ],
        )

        // For getting all owners of a given attachment.
        try tx.database.create(
            index: "index_story_message_attachment_reference_on_attachmentRowId",
            on: "StoryMessageAttachmentReference",
            columns: [
                "attachmentRowId",
            ],
        )

        // MARK: ThreadAttachmentReference table/index

        try tx.database.create(table: "ThreadAttachmentReference") { table in
            // Row id of the owning thread. Each thread has just one wallpaper attachment.
            // If NULL, it's the global thread background image.
            table.column("ownerRowId", .integer)
                .references("model_TSThread", column: "id", onDelete: .cascade)
                .unique()
            // Row id of the associated Attachment.
            table.column("attachmentRowId", .integer)
                .references("Attachment", column: "id", onDelete: .cascade)
                .notNull()
            // Local timestamp this ownership reference was created.
            table.column("creationTimestamp", .integer).notNull()
        }

        // Note: ownerRowId index implicit from UNIQUE constraint.

        // For getting all owners of a given attachment.
        try tx.database.create(
            index: "index_thread_attachment_reference_on_attachmentRowId",
            on: "ThreadAttachmentReference",
            columns: [
                "attachmentRowId",
            ],
        )

        // MARK: OrphanedAttachment table

        /// Double-commit the actual file on disk for deletion; we mark it for deletion
        /// by inserting into this table, another job comes around and deletes for real.
        try tx.database.create(table: "OrphanedAttachment") { table in
            table.autoIncrementedPrimaryKey("id").notNull()
            table.column("localRelativeFilePath", .text)
            table.column("localRelativeFilePathThumbnail", .text)
            table.column("localRelativeFilePathAudioWaveform", .text)
            table.column("localRelativeFilePathVideoStillFrame", .text)
        }

        // MARK: Triggers

        /// Make sure the contentType column is mirrored on MessageAttachmentReference.
        try tx.database.execute(sql: """
            CREATE TRIGGER __Attachment_contentType_au AFTER UPDATE OF contentType ON Attachment
              BEGIN
                UPDATE MessageAttachmentReference
                  SET contentType = NEW.contentType
                  WHERE attachmentRowId = OLD.id;
              END;
        """)

        /// When we delete any owners (on all three tables), check if the associated attachment has any owners left.
        /// If it has no owners remaining, delete it.
        /// Note that application layer is responsible for ensuring we never insert unowned attachments to begin with,
        /// and that we always insert an attachment and at least one owner in the same transaction.
        /// This is unenforceable in SQL.
        for referenceTableName in [
            "MessageAttachmentReference",
            "StoryMessageAttachmentReference",
            "ThreadAttachmentReference",
        ] {
            try tx.database.execute(sql: """
                CREATE TRIGGER "__\(referenceTableName)_ad" AFTER DELETE ON "\(referenceTableName)"
                  BEGIN
                    DELETE FROM Attachment WHERE id = OLD.attachmentRowId
                      AND NOT EXISTS (SELECT 1 FROM MessageAttachmentReference WHERE attachmentRowId = OLD.attachmentRowId)
                      AND NOT EXISTS (SELECT 1 FROM StoryMessageAttachmentReference WHERE attachmentRowId = OLD.attachmentRowId)
                      AND NOT EXISTS (SELECT 1 FROM ThreadAttachmentReference WHERE attachmentRowId = OLD.attachmentRowId);
                  END;
            """)
        }

        /// When we delete an attachment row in the database, insert into the orphan table
        /// so we can clean up the files on disk.
        try tx.database.execute(sql: """
            CREATE TRIGGER "__Attachment_ad" AFTER DELETE ON "Attachment"
              BEGIN
                INSERT INTO OrphanedAttachment (
                  localRelativeFilePath
                  ,localRelativeFilePathThumbnail
                  ,localRelativeFilePathAudioWaveform
                  ,localRelativeFilePathVideoStillFrame
                ) VALUES (
                  OLD.localRelativeFilePath
                  ,OLD.localRelativeFilePathThumbnail
                  ,OLD.audioWaveformRelativeFilePath
                  ,OLD.videoStillFrameRelativeFilePath
                );
              END;
        """)

        return .success(())
    }

    static func addOriginalAttachmentIdForQuotedReplyColumn(_ tx: DBWriteTransaction) throws -> Result<Void, Error> {
        try tx.database.alter(table: "Attachment") { table in
            table.add(column: "originalAttachmentIdForQuotedReply", .integer)
                .references("Attachment", column: "id", onDelete: .setNull)
        }

        // For finding quoted reply attachments that reference an original attachment.
        try tx.database.create(
            index: "index_attachment_on_originalAttachmentIdForQuotedReply",
            on: "Attachment",
            columns: ["originalAttachmentIdForQuotedReply"],
        )

        return .success(())
    }

    /// Historically, we persisted a map of `[GroupId: ThreadUniqueId]` for
    /// all group threads. For V1 groups that were migrated to V2 groups
    /// this map would hold entries for both the V1 and V2 group ID to the
    /// same group thread; this allowed us to find the same logical group
    /// thread if we encountered either ID.
    ///
    /// However, it's possible that we could have persisted an entry for a
    /// given group ID without having actually created a `TSGroupThread`,
    /// since both the V2 group ID and the eventual `TSGroupThread/uniqueId`
    /// were derivable from the V1 group ID. For example, code that was
    /// removed in `72345f1` would have created a mapping when restoring a
    /// record of a V1 group from Storage Service, but not actually have
    /// created the `TSGroupThread`. A user who had run this code, but who
    /// never had reason to create the `TSGroupThread` (e.g., because the
    /// migrated group was inactive), would have a "dead-end" mapping of a
    /// V1 group ID and its derived V2 group ID to a `uniqueId` that did not
    /// actually belong to a `TSGroupThread`.
    ///
    /// Later, `f1f4e69` stopped checking for mappings when instantiating a
    /// new `TSGroupThread` for a V2 group. However, other sites such as (at
    /// the time of writing) `GroupManager.tryToUpsertExistingGroupThreadInDatabaseAndCreateInfoMessage`
    /// would consult the mapping to get a `uniqueId` for a given group ID,
    /// then check if a `TSGroupThread` exists for that `uniqueId`, and if
    /// not create a new one. This is problematic, since that new
    /// `TSGroupThread` will give itself a `uniqueId` derived from its V2
    /// group ID, rather than using the `uniqueId` persisted in the mapping
    /// that's based on the original V1 group ID. Phew.
    ///
    /// This in turn means that every time `GroupManager.tryToUpsert...` is
    /// called it will fail to find the `TSGroupThread` that was previously
    /// created, and will instead attempt to create a new `TSGroupThread`
    /// each time (with the same derived `uniqueId`), which we believe is at
    /// the root of an issue reported in the wild.
    ///
    /// This migration iterates through our persisted mappings and deletes
    /// any of these "dead-end" mappings, since V1 group IDs are no longer
    /// used anywhere and those mappings are therefore now useless.
    static func removeDeadEndGroupThreadIdMappings(tx: DBWriteTransaction) throws {
        let mappingStoreCollection = "TSGroupThread.uniqueIdMappingStore"

        let rows = try Row.fetchAll(
            tx.database,
            sql: "SELECT * FROM keyvalue WHERE collection = ?",
            arguments: [mappingStoreCollection],
        )

        /// Group IDs that have a mapping to a thread ID, but for which the
        /// thread ID has no actual thread.
        var deadEndGroupIds = [String]()

        for row in rows {
            guard
                let groupIdKey = row["key"] as? String,
                let targetThreadIdData = row["value"] as? Data,
                let targetThreadId = (try? NSKeyedUnarchiver.unarchivedObject(ofClass: NSString.self, from: targetThreadIdData)) as String?
            else {
                continue
            }

            if
                try Bool.fetchOne(
                    tx.database,
                    sql: """
                        SELECT EXISTS(
                            SELECT 1
                            FROM model_TSThread
                            WHERE uniqueId = ?
                            LIMIT 1
                        )
                    """,
                    arguments: [targetThreadId],
                ) != true
            {
                deadEndGroupIds.append(groupIdKey)
            }
        }

        for deadEndGroupId in deadEndGroupIds {
            try tx.database.execute(
                sql: """
                    DELETE FROM keyvalue
                    WHERE collection = ? AND key = ?
                """,
                arguments: [mappingStoreCollection, deadEndGroupId],
            )

            Logger.warn("Deleting dead-end group ID mapping: \(deadEndGroupId)")
        }
    }

    static func migrateBlockedRecipients(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "BlockedRecipient") { table in
            table.column("recipientId", .integer)
                .primaryKey()
                .references("model_SignalRecipient", column: "id", onDelete: .cascade, onUpdate: .cascade)
        }

        let blockedAciStrings = try fetchAndClearBlockedIdentifiers(key: "kOWSBlockingManager_BlockedUUIDsKey", tx: tx).compactMap {
            return UUID(uuidString: $0)?.uuidString
        }
        let blockedPhoneNumbers = Set(try fetchAndClearBlockedIdentifiers(key: "kOWSBlockingManager_BlockedPhoneNumbersKey", tx: tx)).filter {
            return !$0.isEmpty
        }
        var blockedRecipientIds = Set<Int64>()
        var outdatedRecipientIds = Set<Int64>()

        for blockedAciString in blockedAciStrings {
            let recipientId = try fetchOrCreateRecipient(.V1, aciString: blockedAciString, tx: tx)
            blockedRecipientIds.insert(recipientId)
        }

        for blockedPhoneNumber in blockedPhoneNumbers {
            let recipientId = try fetchOrCreateRecipient(.V1, phoneNumber: blockedPhoneNumber, tx: tx)
            if blockedRecipientIds.contains(recipientId) {
                // They're already blocked by their ACI.
                continue
            }
            let isBlocked = try { () -> Bool in
                if let aciString = try fetchRecipientAciString(recipientId: recipientId, tx: tx) {
                    return try isPhoneNumberVisible(phoneNumber: blockedPhoneNumber, aciString: aciString, tx: tx)
                } else {
                    return true
                }
            }()
            guard isBlocked else {
                // They're only blocked by phone number.
                outdatedRecipientIds.insert(recipientId)
                continue
            }
            blockedRecipientIds.insert(recipientId)
        }

        for blockedRecipientId in blockedRecipientIds {
            try tx.database.execute(
                sql: "INSERT INTO BlockedRecipient (recipientId) VALUES (?)",
                arguments: [blockedRecipientId],
            )
        }

        for outdatedRecipientId in outdatedRecipientIds {
            guard let recipientUniqueId = try fetchRecipientUniqueId(recipientId: outdatedRecipientId, tx: tx) else {
                Logger.warn("Couldn't fetch uniqueId for just-fetched recipient.")
                continue
            }
            guard UUID(uuidString: recipientUniqueId) != nil else {
                Logger.warn("Couldn't validate uniqueId for just-fetched recipient.")
                continue
            }
            do {
                // This assertion should never fail. If `.updated.rawValue` ever changes,
                // don't change `updatedValue`.
                let updatedValue = 1
                assert(updatedValue == StorageServiceOperation.State.ChangeState.updated.rawValue)
                try tx.database.execute(
                    sql: """
                    UPDATE "keyvalue" SET "value" = json_set("value", ?, ?) WHERE "collection" IS 'kOWSStorageServiceOperation_IdentifierMap' AND "key" IS 'state'
                    """,
                    arguments: ["$.accountIdChangeMap.\(recipientUniqueId)", updatedValue],
                )
            } catch DatabaseError.SQLITE_ERROR {
                // This likely means that we've never used Storage Service. In that case,
                // we don't need to apply these changes.
            }
        }
    }

    private static func isPhoneNumberVisible(phoneNumber: String, aciString: String, tx: DBWriteTransaction) throws -> Bool {
        let isSystemContact = try Int.fetchOne(
            tx.database,
            sql: "SELECT 1 FROM model_SignalAccount WHERE recipientPhoneNumber IS ?",
            arguments: [phoneNumber],
        ) != nil
        if isSystemContact {
            return true
        }
        let isPhoneNumberHidden = try Int.fetchOne(
            tx.database,
            sql: """
            SELECT 1 FROM model_OWSUserProfile WHERE recipientUUID IS ? AND (
                isPhoneNumberShared IS FALSE
                OR (isPhoneNumberShared IS NULL AND profileName IS NOT NULL)
            )
            """,
            arguments: [aciString],
        ) != nil
        return !isPhoneNumberHidden
    }

    private static func fetchAndClearBlockedIdentifiers(key: String, tx: DBWriteTransaction) throws -> [String] {
        let collection = "kOWSBlockingManager_BlockedPhoneNumbersCollection"
        let dataValue = try Data.fetchOne(
            tx.database,
            sql: "SELECT value FROM keyvalue WHERE collection IS ? AND key IS ?",
            arguments: [collection, key],
        )
        try tx.database.execute(sql: "DELETE FROM keyvalue WHERE collection IS ? AND key IS ?", arguments: [collection, key])
        guard let dataValue else {
            return []
        }
        do {
            let blockedIdentifiers = try NSKeyedUnarchiver.unarchivedObject(ofClasses: [NSArray.self, NSString.self], from: dataValue)
            return (blockedIdentifiers as? [String]) ?? []
        } catch {
            Logger.warn("Couldn't decode blocked identifiers.")
            return []
        }
    }

    /// The representation of recipients has changed over time. Old migrations
    /// need to continue writing in the old format; new migrations need to write
    /// in the new format; when adding a migraton, you may need to define a new
    /// version if the latest version isn't compatible with the current schema.
    private enum RecipientVersion {
        case V1
        /// See migrateRecipientDeviceIds.
        case V2
    }

    private static func fetchOrCreateRecipient(_ version: RecipientVersion, aciString: String, tx: DBWriteTransaction) throws -> SignalRecipient.RowId {
        let db = tx.database
        let existingRecipientId = try Int64.fetchOne(db, sql: "SELECT id FROM model_SignalRecipient WHERE recipientUUID IS ?", arguments: [aciString])
        if let existingRecipientId {
            return existingRecipientId
        }
        return try createRecipient(version, aciString: aciString, phoneNumber: nil, pniString: nil, tx: tx)
    }

    private static func fetchOrCreateRecipient(_ version: RecipientVersion, phoneNumber: String, tx: DBWriteTransaction) throws -> SignalRecipient.RowId {
        let db = tx.database
        let existingRecipientId = try Int64.fetchOne(db, sql: "SELECT id FROM model_SignalRecipient WHERE recipientPhoneNumber IS ?", arguments: [phoneNumber])
        if let existingRecipientId {
            return existingRecipientId
        }
        return try createRecipient(version, aciString: nil, phoneNumber: phoneNumber, pniString: nil, tx: tx)
    }

    private static func fetchOrCreateRecipient(_ version: RecipientVersion, pniString: String, tx: DBWriteTransaction) throws -> SignalRecipient.RowId {
        let db = tx.database
        let existingRecipientId = try Int64.fetchOne(db, sql: "SELECT id FROM model_SignalRecipient WHERE pni IS ?", arguments: [pniString])
        if let existingRecipientId {
            return existingRecipientId
        }
        return try createRecipient(version, aciString: nil, phoneNumber: nil, pniString: pniString, tx: tx)
    }

    private static func fetchOrCreateRecipient(_ version: RecipientVersion, address: FrozenSignalServiceAddress, tx: DBWriteTransaction) throws -> SignalRecipient.RowId? {
        if let aci = address.serviceId as? Aci {
            let aciString = aci.serviceIdUppercaseString
            return try fetchOrCreateRecipient(version, aciString: aciString, tx: tx)
        }
        if let phoneNumber = address.phoneNumber {
            return try fetchOrCreateRecipient(version, phoneNumber: phoneNumber, tx: tx)
        }
        if let pni = address.serviceId as? Pni {
            let pniString = pni.serviceIdUppercaseString
            return try fetchOrCreateRecipient(version, pniString: pniString, tx: tx)
        }
        return nil
    }

    private static func createRecipient(_ version: RecipientVersion, aciString: String?, phoneNumber: String?, pniString: String?, tx: DBWriteTransaction) throws -> SignalRecipient.RowId {
        let emptyDevices: Data
        switch version {
        case .V1:
            emptyDevices = try NSKeyedArchiver.archivedData(withRootObject: NSOrderedSet(array: [] as [NSNumber]), requiringSecureCoding: true)
        case .V2:
            emptyDevices = Data()
        }
        try tx.database.execute(
            sql: """
            INSERT INTO "model_SignalRecipient" ("recordType", "uniqueId", "devices", "recipientPhoneNumber", "recipientUUID", "pni") VALUES (31, ?, ?, ?, ?, ?)
            """,
            arguments: [
                UUID().uuidString,
                emptyDevices,
                phoneNumber,
                aciString,
                pniString,
            ],
        )
        return tx.database.lastInsertedRowID
    }

    private static func fetchRecipientAciString(recipientId: SignalRecipient.RowId, tx: DBWriteTransaction) throws -> String? {
        return try String.fetchOne(tx.database, sql: "SELECT recipientUUID FROM model_SignalRecipient WHERE id = ?", arguments: [recipientId])
    }

    private static func fetchRecipientUniqueId(recipientId: SignalRecipient.RowId, tx: DBWriteTransaction) throws -> String? {
        return try String.fetchOne(tx.database, sql: "SELECT uniqueId FROM model_SignalRecipient WHERE id = ?", arguments: [recipientId])
    }

    static func createCallLinkTable(
        tableName: String,
        rootKeyConstraint: StaticString,
        tx: DBWriteTransaction,
    ) throws {
        try tx.database.create(table: tableName) { table in
            table.column("id", .integer).primaryKey()
            table.column("roomId", .blob).notNull().unique()
            table.column("rootKey", .blob).notNull()
            table.column("adminPasskey", .blob)
            table.column("adminDeletedAtTimestampMs", .integer)
            table.column("activeCallId", .integer)
            table.column("isUpcoming", .boolean)
            table.column("pendingActionCounter", .integer).notNull().defaults(to: 0)
            table.column("name", .text)
            table.column("restrictions", .integer)
            table.column("revoked", .boolean)
            table.column("expiration", .integer)
            table.check(sql: #"LENGTH("roomId") IS 32"#)
            table.check(sql: "LENGTH(\"rootKey\") \(rootKeyConstraint)")
            table.check(sql: #"LENGTH("adminPasskey") > 0 OR "adminPasskey" IS NULL"#)
            table.check(sql: #"NOT("isUpcoming" IS TRUE AND "expiration" IS NULL)"#)
        }
    }

    static func createCallLinkIndexes(tx: DBWriteTransaction) throws {
        try tx.database.create(
            index: "CallLink_Upcoming",
            on: "CallLink",
            columns: ["expiration"],
            condition: Column("isUpcoming") == true,
        )

        try tx.database.create(
            index: "CallLink_Pending",
            on: "CallLink",
            columns: ["pendingActionCounter"],
            condition: Column("pendingActionCounter") > 0,
        )

        try tx.database.create(
            index: "CallLink_AdminDeleted",
            on: "CallLink",
            columns: ["adminDeletedAtTimestampMs"],
            condition: Column("adminDeletedAtTimestampMs") != nil,
        )
    }

    static func addCallLinkTable(tx: DBWriteTransaction) throws {
        try createCallLinkTable(
            tableName: "CallLink",
            rootKeyConstraint: "IS 16",
            tx: tx,
        )

        try createCallLinkIndexes(tx: tx)

        let indexesToDrop = [
            "index_call_record_on_callId_and_threadId",
            "index_call_record_on_timestamp",
            "index_call_record_on_status_and_timestamp",
            "index_call_record_on_threadRowId_and_timestamp",
            "index_call_record_on_threadRowId_and_status_and_timestamp",
            "index_call_record_on_callStatus_and_unreadStatus_and_timestamp",
            "index_call_record_on_threadRowId_and_callStatus_and_unreadStatus_and_timestamp",
            "index_deleted_call_record_on_threadRowId_and_callId",
            "index_deleted_call_record_on_deletedAtTimestamp",
        ]
        for indexName in indexesToDrop {
            try tx.database.drop(index: indexName)
        }

        try tx.database.create(table: "new_CallRecord") { (table: TableDefinition) in
            table.column("id", .integer).primaryKey().notNull()
            table.column("callId", .text).notNull()
            table.column("interactionRowId", .integer).unique()
                .references("model_TSInteraction", column: "id", onDelete: .restrict, onUpdate: .cascade)
            table.column("threadRowId", .integer)
                .references("model_TSThread", column: "id", onDelete: .restrict, onUpdate: .cascade)
            table.column("callLinkRowId", .integer)
                .references("CallLink", column: "id", onDelete: .restrict, onUpdate: .cascade)
            table.column("type", .integer).notNull()
            table.column("direction", .integer).notNull()
            table.column("status", .integer).notNull()
            table.column("unreadStatus", .integer).notNull()
            table.column("callBeganTimestamp", .integer).notNull()
            table.column("callEndedTimestamp", .integer).notNull()
            table.column("groupCallRingerAci", .blob)
            table.check(sql: #"IIF("threadRowId" IS NOT NULL, "callLinkRowId" IS NULL, "callLinkRowId" IS NOT NULL)"#)
            table.check(sql: #"IIF("threadRowId" IS NOT NULL, "interactionRowId" IS NOT NULL, "interactionRowId" IS NULL)"#)
        }
        try tx.database.execute(sql: """
        INSERT INTO "new_CallRecord" (
            "id", "callId", "interactionRowId", "threadRowId", "type", "direction", "status", "unreadStatus", "callBeganTimestamp", "callEndedTimestamp", "groupCallRingerAci"
        ) SELECT "id", "callId", "interactionRowId", "threadRowId", "type", "direction", "status", "unreadStatus", "timestamp", "callEndedTimestamp", "groupCallRingerAci" FROM "CallRecord";
        """)
        try tx.database.drop(table: "CallRecord")
        try tx.database.rename(table: "new_CallRecord", to: "CallRecord")

        try tx.database.create(table: "new_DeletedCallRecord") { table in
            table.column("id", .integer).primaryKey().notNull()
            table.column("callId", .text).notNull()
            table.column("threadRowId", .integer)
                .references("model_TSThread", column: "id", onDelete: .restrict, onUpdate: .cascade)
            table.column("callLinkRowId", .integer)
                .references("CallLink", column: "id", onDelete: .restrict, onUpdate: .cascade)
            table.column("deletedAtTimestamp", .integer).notNull()
            table.check(sql: #"IIF("threadRowId" IS NOT NULL, "callLinkRowId" IS NULL, "callLinkRowId" IS NOT NULL)"#)
        }
        try tx.database.execute(sql: """
        INSERT INTO "new_DeletedCallRecord" (
            "id", "callId", "threadRowId", "deletedAtTimestamp"
        ) SELECT "id", "callId", "threadRowId", "deletedAtTimestamp" FROM "DeletedCallRecord";
        """)
        try tx.database.drop(table: "DeletedCallRecord")
        try tx.database.rename(table: "new_DeletedCallRecord", to: "DeletedCallRecord")

        try tx.database.create(
            index: "CallRecord_threadRowId_callId",
            on: "CallRecord",
            columns: ["threadRowId", "callId"],
            options: [.unique],
            condition: Column("threadRowId") != nil,
        )

        try tx.database.create(
            index: "CallRecord_callLinkRowId_callId",
            on: "CallRecord",
            columns: ["callLinkRowId", "callId"],
            options: [.unique],
            condition: Column("callLinkRowId") != nil,
        )

        try tx.database.create(
            index: "CallRecord_callBeganTimestamp",
            on: "CallRecord",
            columns: ["callBeganTimestamp"],
        )

        try tx.database.create(
            index: "CallRecord_status_callBeganTimestamp",
            on: "CallRecord",
            columns: ["status", "callBeganTimestamp"],
        )

        try tx.database.create(
            index: "CallRecord_threadRowId_callBeganTimestamp",
            on: "CallRecord",
            columns: ["threadRowId", "callBeganTimestamp"],
            condition: Column("threadRowId") != nil,
        )

        try tx.database.create(
            index: "CallRecord_callLinkRowId_callBeganTimestamp",
            on: "CallRecord",
            columns: ["callLinkRowId", "callBeganTimestamp"],
            condition: Column("callLinkRowId") != nil,
        )

        try tx.database.create(
            index: "CallRecord_threadRowId_status_callBeganTimestamp",
            on: "CallRecord",
            columns: ["threadRowId", "status", "callBeganTimestamp"],
            condition: Column("threadRowId") != nil,
        )

        try tx.database.create(
            index: "CallRecord_callStatus_unreadStatus_callBeganTimestamp",
            on: "CallRecord",
            columns: ["status", "unreadStatus", "callBeganTimestamp"],
        )

        try tx.database.create(
            index: "CallRecord_threadRowId_callStatus_unreadStatus_callBeganTimestamp",
            on: "CallRecord",
            columns: ["threadRowId", "status", "unreadStatus", "callBeganTimestamp"],
            condition: Column("threadRowId") != nil,
        )

        try tx.database.create(
            index: "DeletedCallRecord_threadRowId_callId",
            on: "DeletedCallRecord",
            columns: ["threadRowId", "callId"],
            options: [.unique],
            condition: Column("threadRowId") != nil,
        )

        try tx.database.create(
            index: "DeletedCallRecord_callLinkRowId_callId",
            on: "DeletedCallRecord",
            columns: ["callLinkRowId", "callId"],
            options: [.unique],
            condition: Column("callLinkRowId") != nil,
        )

        try tx.database.create(
            index: "DeletedCallRecord_deletedAtTimestamp",
            on: "DeletedCallRecord",
            columns: ["deletedAtTimestamp"],
        )
    }

    static func modifyCallLinkRootKeyConstraint(tx: DBWriteTransaction) throws {
        try createCallLinkTable(
            tableName: "CallLink_new",
            rootKeyConstraint: ">= 16",
            tx: tx,
        )

        try tx.database.execute(
            sql: """
            INSERT INTO "CallLink_new" (
                "id",
                "roomId",
                "rootKey",
                "adminPasskey",
                "adminDeletedAtTimestampMs",
                "activeCallId",
                "isUpcoming",
                "pendingActionCounter",
                "name",
                "restrictions",
                "revoked",
                "expiration"
            ) SELECT
                "id",
                "roomId",
                "rootKey",
                "adminPasskey",
                "adminDeletedAtTimestampMs",
                "activeCallId",
                "isUpcoming",
                "pendingActionCounter",
                "name",
                "restrictions",
                "revoked",
                "expiration"
            FROM "CallLink"
            """,
        )

        try tx.database.drop(table: "CallLink")
        try tx.database.rename(table: "CallLink_new", to: "CallLink")
        try createCallLinkIndexes(tx: tx)
    }

    private static func fetchAndClearBlockedGroupIds(tx: DBWriteTransaction) throws -> [Data] {
        let collection = "kOWSBlockingManager_BlockedPhoneNumbersCollection"
        let key = "kOWSBlockingManager_BlockedGroupMapKey"
        let dataValue = try Data.fetchOne(
            tx.database,
            sql: "SELECT value FROM keyvalue WHERE collection IS ? AND key IS ?",
            arguments: [collection, key],
        )
        try tx.database.execute(sql: "DELETE FROM keyvalue WHERE collection IS ? AND key IS ?", arguments: [collection, key])
        guard let dataValue else {
            return []
        }
        do {
            return try decodeBlockedGroupIds(dataValue: dataValue)
        } catch {
            Logger.warn("Couldn't decode blocked identifiers.")
            return []
        }
    }

    static func decodeBlockedGroupIds(dataValue: Data) throws -> [Data] {
        @objc(TSBlockedGroupModel)
        class TSBlockedGroupModel: NSObject, NSSecureCoding {
            static var supportsSecureCoding: Bool { true }
            required init?(coder: NSCoder) {}
            func encode(with coder: NSCoder) {}
        }
        let coder = try NSKeyedUnarchiver(forReadingFrom: dataValue)
        coder.requiresSecureCoding = true
        coder.setClass(TSBlockedGroupModel.self, forClassName: "TSGroupModel")
        coder.setClass(TSBlockedGroupModel.self, forClassName: "SignalServiceKit.TSGroupModelV2")
        let groupIdMap = try coder.decodeTopLevelObject(of: [
            NSDictionary.self,
            NSData.self,
            TSBlockedGroupModel.self,
        ], forKey: NSKeyedArchiveRootObjectKey)
        return Array(((groupIdMap as? [Data: TSBlockedGroupModel]) ?? [:]).keys)
    }

    private static func rebuildIncompleteViewOnceIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_interactions_on_view_once"
            """,
        )
        try tx.database.create(
            index: "Interaction_incompleteViewOnce_partial",
            on: "model_TSInteraction",
            columns: ["isViewOnceMessage", "isViewOnceComplete"],
            options: [.ifNotExists],
            condition: Column("isViewOnceMessage") == 1 && Column("isViewOnceComplete") == 0,
        )
    }

    public static func removeInteractionThreadUniqueIdUniqueIdIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_interactions_on_uniqueId_and_threadUniqueId"
            """,
        )
    }

    public static func rebuildDisappearingMessagesIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_interactions_on_expiresInSeconds_and_expiresAt"
            """,
        )
        try tx.database.create(
            index: "Interaction_disappearingMessages_partial",
            on: "model_TSInteraction",
            columns: ["expiresAt"],
            options: [.ifNotExists],
            condition: Column("expiresAt") > 0,
        )
    }

    public static func removeInteractionAttachmentIdsIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_on_uniqueThreadId_and_attachmentIds"
            """,
        )
    }

    public static func rebuildInteractionTimestampIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_interactions_on_timestamp_sourceDeviceId_and_authorUUID"
            """,
        )
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_interactions_on_timestamp_sourceDeviceId_and_authorPhoneNumber"
            """,
        )
        try tx.database.create(
            index: "Interaction_timestamp",
            on: "model_TSInteraction",
            columns: ["timestamp"],
            options: [.ifNotExists],
        )
    }

    public static func rebuildInteractionUnendedGroupCallIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_on_uniqueThreadId_and_hasEnded_and_recordType"
            """,
        )
        // This recordType constant can't ever change.
        assert(SDSRecordType.groupCallMessage.rawValue == 65)
        try tx.database.create(
            index: "Interaction_unendedGroupCall_partial",
            on: "model_TSInteraction",
            columns: ["recordType", "hasEnded", "uniqueThreadId"],
            options: [.ifNotExists],
            condition: Column("recordType") == 65 && Column("hasEnded") == 0,
        )
    }

    public static func rebuildInteractionGroupCallEraIdIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_on_uniqueThreadId_and_eraId_and_recordType"
            """,
        )
        try tx.database.create(
            index: "Interaction_groupCallEraId_partial",
            on: "model_TSInteraction",
            columns: ["uniqueThreadId", "recordType", "eraId"],
            options: [.ifNotExists],
            condition: Column("eraId") != nil,
        )
    }

    public static func rebuildInteractionStoryReplyIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_on_StoryContext"
            """,
        )
        try tx.database.create(
            index: "Interaction_storyReply_partial",
            on: "model_TSInteraction",
            columns: ["storyAuthorUuidString", "storyTimestamp", "isGroupStoryReply"],
            options: [.ifNotExists],
            condition: Column("storyAuthorUuidString") != nil && Column("storyTimestamp") != nil,
        )
    }

    public static func removeInteractionConversationLoadCountIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_ConversationLoadInteractionCount"
            """,
        )
    }

    public static func removeInteractionConversationLoadDistanceIndex(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            DROP INDEX IF EXISTS "index_model_TSInteraction_ConversationLoadInteractionDistance"
            """,
        )
    }

    public static func createDefaultAvatarColorTable(tx: DBWriteTransaction) throws {
        try tx.database.create(
            table: "AvatarDefaultColor",
            options: [.ifNotExists],
        ) { table in
            table.column("recipientRowId", .integer)
                .unique()
                .references(
                    "model_SignalRecipient",
                    column: "id",
                    onDelete: .cascade,
                    onUpdate: .cascade,
                )
            table.column("groupId", .blob).unique()
            table.column("defaultColorIndex", .integer).notNull()
        }
    }

    public static func populateDefaultAvatarColorTable(tx: DBWriteTransaction) throws {
        /// This is the hashing algorithm historically used to compute the
        /// default avatar color index.
        func computeAvatarColorIndex(seedData: Data) -> Int {
            func rotateLeft(_ uint: UInt64, _ count: Int) -> UInt64 {
                let count = count % UInt64.bitWidth
                return (uint << count) | (uint >> (UInt64.bitWidth - count))
            }

            var hash: UInt64 = 0
            for value in seedData {
                hash = rotateLeft(hash, 3) ^ UInt64(value)
            }
            return Int(hash % 12)
        }

        func insertDefaultColorIndex(
            _ defaultColorIndex: Int,
            groupId: Data?,
            recipientRowId: Int64?,
        ) throws {
            try tx.database.execute(
                sql: """
                    INSERT INTO AvatarDefaultColor
                    VALUES (?, ?, ?)
                """,
                arguments: [recipientRowId, groupId, defaultColorIndex],
            )
        }

        /// The group ID is buried inside of an `NSKeyedArchiver`-serialized
        /// `TSGroupModel`. We'll grab the serialized blobs, then selectively
        /// decode the group ID from them. That avoids referencing production
        /// types here, and also avoids deserializing the rest of the group
        /// model, such as the membership (which can be very slow).
        let groupModelDataCursor = try Data.fetchCursor(tx.database, sql: """
            SELECT groupModel
            FROM model_TSThread
            WHERE groupModel IS NOT NULL
        """)
        while let groupModelData = try groupModelDataCursor.next() {
            if
                let groupId = try decodeGroupIdFromGroupModelData(groupModelData),
                groupId.count == 32
            {
                try insertDefaultColorIndex(
                    computeAvatarColorIndex(seedData: groupId),
                    groupId: groupId,
                    recipientRowId: nil,
                )
            }
        }

        var visitedRecipientIds = Set<Int64>()

        let aciRowCursor = try Row.fetchCursor(tx.database, sql: """
            SELECT id, recipientUUID
            FROM model_SignalRecipient
            WHERE recipientUUID IS NOT NULL
        """)
        while let row = try aciRowCursor.next() {
            let recipientRowId: Int64 = row["id"]
            let aciString: String = row["recipientUUID"]

            let (inserted, _) = visitedRecipientIds.insert(recipientRowId)
            if !inserted { continue }

            try insertDefaultColorIndex(
                computeAvatarColorIndex(seedData: Data(aciString.uppercased().utf8)),
                groupId: nil,
                recipientRowId: recipientRowId,
            )
        }

        let pniRowCursor = try Row.fetchCursor(tx.database, sql: """
            SELECT id, pni
            FROM model_SignalRecipient
            WHERE pni IS NOT NULL
        """)
        while let row = try pniRowCursor.next() {
            let recipientRowId: Int64 = row["id"]
            let pniString: String = row["pni"]

            let (inserted, _) = visitedRecipientIds.insert(recipientRowId)
            if !inserted { continue }

            try insertDefaultColorIndex(
                computeAvatarColorIndex(seedData: Data(pniString.uppercased().utf8)),
                groupId: nil,
                recipientRowId: recipientRowId,
            )
        }

        let phoneNumberRowCursor = try Row.fetchCursor(tx.database, sql: """
            SELECT id, recipientPhoneNumber
            FROM model_SignalRecipient
            WHERE recipientPhoneNumber IS NOT NULL
        """)
        while let row = try phoneNumberRowCursor.next() {
            let recipientRowId: Int64 = row["id"]
            let phoneNumber: String = row["recipientPhoneNumber"]

            let (inserted, _) = visitedRecipientIds.insert(recipientRowId)
            if !inserted { continue }

            try insertDefaultColorIndex(
                computeAvatarColorIndex(seedData: Data(phoneNumber.utf8)),
                groupId: nil,
                recipientRowId: recipientRowId,
            )
        }
    }

    private static func decodeGroupIdFromGroupModelData(
        _ groupModelData: Data,
    ) throws -> Data? {
        @objc(TSGroupModelForMigrations)
        class TSGroupModelForMigrations: NSObject, NSSecureCoding {
            static var supportsSecureCoding: Bool { true }
            let groupId: NSData?
            required init?(coder: NSCoder) {
                groupId = coder.decodeObject(of: NSData.self, forKey: "groupId")
            }

            func encode(with coder: NSCoder) { owsFail("Don't encode these!") }
        }

        let coder = try NSKeyedUnarchiver(forReadingFrom: groupModelData)
        coder.requiresSecureCoding = true
        coder.setClass(TSGroupModelForMigrations.self, forClassName: "TSGroupModel")
        coder.setClass(TSGroupModelForMigrations.self, forClassName: "SignalServiceKit.TSGroupModelV2")

        let groupModel = try coder.decodeTopLevelObject(
            of: TSGroupModelForMigrations.self,
            forKey: NSKeyedArchiveRootObjectKey,
        )
        return groupModel?.groupId as Data?
    }

    static func createStoryRecipients(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "StoryRecipient", options: [.withoutRowID]) { table in
            table.primaryKey(["threadId", "recipientId"])
            table.column("threadId", .integer).notNull()
            table.foreignKey(["threadId"], references: "model_TSThread", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
            table.column("recipientId", .integer).notNull().indexed()
            table.foreignKey(["recipientId"], references: "model_SignalRecipient", columns: ["id"], onDelete: .cascade, onUpdate: .cascade)
        }
        try migrateStoryRecipients(tx: tx)
    }

    private static func migrateStoryRecipients(tx: DBWriteTransaction) throws {
        let storyThreads = try Row.fetchAll(
            tx.database,
            sql: "SELECT id, addresses FROM model_TSThread WHERE recordType = 72",
        )
        for storyThread in storyThreads {
            let storyThreadId: Int64 = storyThread[0]
            let addressesData: Data? = storyThread[1]
            guard let addressesData else {
                continue
            }
            let addresses: [FrozenSignalServiceAddress]
            do {
                addresses = try decodeSignalServiceAddresses(dataValue: addressesData)
            } catch {
                owsFailDebug("Couldn't decode story recipients: \(error)")
                continue
            }
            for address in addresses {
                guard let recipientId = try fetchOrCreateRecipient(.V1, address: address, tx: tx) else {
                    owsFailDebug("Couldn't include empty story recipient address")
                    continue
                }
                do {
                    try tx.database.execute(
                        sql: "INSERT INTO StoryRecipient (threadId, recipientId) VALUES (?, ?)",
                        arguments: [storyThreadId, recipientId],
                    )
                } catch DatabaseError.SQLITE_CONSTRAINT {
                    // This is fine.
                }
            }
        }
        try tx.database.execute(
            sql: "UPDATE model_TSThread SET addresses = NULL WHERE recordType = 72",
        )
    }

    /// A SignalServiceAddress without global magic; useful in migrations.
    @objc(FrozenSignalServiceAddress)
    class FrozenSignalServiceAddress: NSObject, NSSecureCoding {
        let serviceId: ServiceId?
        let phoneNumber: String?

        static var supportsSecureCoding: Bool { true }

        required init?(coder: NSCoder) {
            let serviceId: ServiceId?
            switch coder.decodeObject(of: [NSUUID.self, NSData.self], forKey: "backingUuid") {
            case nil:
                serviceId = nil
            case let serviceIdBinary as Data:
                do {
                    serviceId = try ServiceId.parseFrom(serviceIdBinary: serviceIdBinary)
                } catch {
                    owsFailDebug("Couldn't parse serviceIdBinary.")
                    return nil
                }
            case let deprecatedUuid as NSUUID:
                serviceId = Aci(fromUUID: deprecatedUuid as UUID)
            default:
                return nil
            }
            let phoneNumber = coder.decodeObject(of: NSString.self, forKey: "backingPhoneNumber") as String?
            self.serviceId = serviceId
            self.phoneNumber = phoneNumber
        }

        func encode(with coder: NSCoder) {
            owsFail("Not supported.")
        }
    }

    static func decodeSignalServiceAddresses(dataValue: Data) throws -> [FrozenSignalServiceAddress] {
        let coder = try NSKeyedUnarchiver(forReadingFrom: dataValue)
        coder.requiresSecureCoding = true
        coder.setClass(FrozenSignalServiceAddress.self, forClassName: "SignalServiceKit.SignalServiceAddress")
        let decodedValue = try coder.decodeTopLevelObject(
            of: [NSArray.self, FrozenSignalServiceAddress.self],
            forKey: NSKeyedArchiveRootObjectKey,
        )
        guard let result = decodedValue as? [FrozenSignalServiceAddress] else {
            throw OWSGenericError("Couldn't parse result as an array of addresses.")
        }
        return result
    }

    static func migrateRecipientDeviceIds(tx: DBWriteTransaction) throws {
        let rowIds = try Int64.fetchAll(tx.database, sql: "SELECT id FROM model_SignalRecipient")
        for rowId in rowIds {
            let oldEncodedValue = try Data.fetchOne(tx.database, sql: "SELECT devices FROM model_SignalRecipient WHERE id = ?", arguments: [rowId])
            guard let oldEncodedValue else { throw OWSGenericError("Missing oldEncodedValue") }
            let deviceIdsObjC = try NSKeyedUnarchiver.unarchivedObject(ofClasses: [NSOrderedSet.self, NSNumber.self], from: oldEncodedValue) as? NSOrderedSet
            guard let deviceIdsObjC else { throw OWSGenericError("Missing deviceIdsObjC") }
            let deviceIds = (deviceIdsObjC.array as? [NSNumber])?.map { $0.uint32Value }
            let validDeviceIds = (deviceIds ?? []).compactMap(UInt8.init(exactly:)).filter({ 1 <= $0 && $0 <= 127 })
            let newEncodedValue = Data(validDeviceIds.sorted())
            try tx.database.execute(sql: "UPDATE model_SignalRecipient SET devices = ? WHERE id = ?", arguments: [newEncodedValue, rowId])
        }
    }

    static func createPreKey(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "PreKey") {
            $0.column("rowId", .integer).primaryKey().notNull()
            $0.column("identity", .integer).notNull()
            $0.column("namespace", .integer).notNull()
            $0.column("keyId", .integer).notNull()
            $0.column("isOneTime", .boolean).notNull()
            $0.column("replacedAt", .date)
            $0.column("serializedRecord", .blob)
            $0.check(sql: #"CASE "namespace" WHEN 0 THEN "isOneTime"=1 WHEN 2 THEN "isOneTime"=0 ELSE TRUE END"#)
        }
        // For fetching keys when asked by LibSignal.
        try tx.database.create(
            index: "PreKey_Unique",
            on: "PreKey",
            columns: ["identity", "namespace", "keyId"],
            options: [.unique],
        )
        // For deleting keys that have been replaced and expired.
        try tx.database.create(
            index: "PreKey_Obsolete",
            on: "PreKey",
            columns: ["replacedAt"],
            condition: Column("replacedAt") != nil,
        )
        // For marking keys that have been replaced.
        try tx.database.create(
            index: "PreKey_Replaced",
            on: "PreKey",
            columns: ["identity", "namespace", "isOneTime", "keyId"],
            condition: Column("replacedAt") == nil,
        )
    }

    static func migratePreKeys(tx: DBWriteTransaction) throws {
        // If these ever change, you'll need to add a new migration to update the
        // PreKey table and replace the old constants with the new constants.
        assert(PreKey.Namespace.oneTime.rawValue == 0)
        assert(PreKey.Namespace.kyber.rawValue == 1)
        assert(PreKey.Namespace.signed.rawValue == 2)

        try migratePreKeys(
            in: (aci: "TSStorageManagerPreKeyStoreCollection", pni: "TSStorageManagerPNIPreKeyStoreCollection"),
            namespace: 0,
            tx: tx,
        ) { encodedValue in
            guard let decodedValue = try NSKeyedUnarchiver.unarchivedObject(ofClass: SignalServiceKit.PreKeyRecord.self, from: encodedValue) else {
                throw OWSGenericError("missing decoded value")
            }
            let keyId = UInt32(bitPattern: decodedValue.id)
            return (
                keyId: keyId,
                isOneTime: true,
                replacedAt: decodedValue.replacedAt,
                serializedRecord: try LibSignalClient.PreKeyRecord(
                    id: keyId,
                    privateKey: decodedValue.keyPair.keyPair.privateKey,
                ).serialize(),
            )
        }

        try migratePreKeys(
            in: (aci: "TSStorageManagerSignedPreKeyStoreCollection", pni: "TSStorageManagerPNISignedPreKeyStoreCollection"),
            namespace: 2,
            tx: tx,
        ) { encodedValue in
            guard let decodedValue = try NSKeyedUnarchiver.unarchivedObject(ofClass: SignalServiceKit.SignedPreKeyRecord.self, from: encodedValue) else {
                throw OWSGenericError("missing decoded value")
            }
            let keyId = UInt32(bitPattern: decodedValue.id)
            return (
                keyId: keyId,
                isOneTime: false,
                replacedAt: decodedValue.replacedAt,
                serializedRecord: try LibSignalClient.SignedPreKeyRecord(
                    id: keyId,
                    timestamp: decodedValue.createdAt?.ows_millisecondsSince1970 ?? 0,
                    privateKey: decodedValue.keyPair.keyPair.privateKey,
                    signature: decodedValue.signature,
                ).serialize(),
            )
        }

        try migratePreKeys(
            in: (aci: "SSKKyberPreKeyStoreACIKeyStore", pni: "SSKKyberPreKeyStorePNIKeyStore"),
            namespace: 1,
            tx: tx,
        ) { encodedValue in
            let decodedValue = try JSONDecoder().decode(SignalServiceKit.KyberPreKeyRecord.self, from: encodedValue)
            return (
                keyId: decodedValue.libSignalRecord.id,
                isOneTime: !decodedValue.isLastResort,
                replacedAt: decodedValue.replacedAt,
                serializedRecord: decodedValue.libSignalRecord.serialize(),
            )
        }
    }

    static func migratePreKeys(
        in collections: (aci: String, pni: String),
        namespace: Int64,
        tx: DBWriteTransaction,
        decodeValue: (Data) throws -> (keyId: UInt32, isOneTime: Bool, replacedAt: Date?, serializedRecord: Data),
    ) throws {
        // If these ever change, you'll need to add a new migration to update the
        // PreKey table and replace the old constants with the new constants.
        assert(OWSIdentity.aci.rawValue == 0)
        assert(OWSIdentity.pni.rawValue == 1)
        for (collection, identity) in [(collections.aci, 0), (collections.pni, 1)] {
            let keys = try String.fetchAll(
                tx.database,
                sql: "SELECT key FROM keyvalue WHERE collection = ?",
                arguments: [collection],
            )
            for key in keys { try autoreleasepool {
                let dataValue = try Data.fetchOne(
                    tx.database,
                    sql: "SELECT value FROM keyvalue WHERE collection = ? AND key = ?",
                    arguments: [collection, key],
                )!
                guard let decodedValue = try? decodeValue(dataValue) else {
                    // We don't expect any failures, but if there are failures, it's safe to
                    // throw away the malformed values. We'll eventually recover organically.
                    Logger.warn("Skipping \(key) in \(collection) that couldn't be decoded")
                    return
                }
                try tx.database.execute(
                    sql: "INSERT INTO PreKey (identity, namespace, keyId, isOneTime, replacedAt, serializedRecord) VALUES (?, ?, ?, ?, ?, ?)",
                    arguments: [
                        identity,
                        namespace,
                        decodedValue.keyId,
                        decodedValue.isOneTime,
                        decodedValue.replacedAt.map { Int64($0.timeIntervalSince1970) },
                        decodedValue.serializedRecord,
                    ],
                )
            }}
        }
    }

    static func dropOldPreKeys(tx: DBWriteTransaction) throws {
        let collections = [
            "TSStorageManagerPreKeyStoreCollection",
            "TSStorageManagerPNIPreKeyStoreCollection",
            "TSStorageManagerSignedPreKeyStoreCollection",
            "TSStorageManagerPNISignedPreKeyStoreCollection",
            "SSKKyberPreKeyStoreACIKeyStore",
            "SSKKyberPreKeyStorePNIKeyStore",
        ]
        for collection in collections {
            try tx.database.execute(sql: "DELETE FROM keyvalue WHERE collection = ?", arguments: [collection])
        }
    }

    static func uniquifyUsernameLookupRecord(
        caseInsensitive: Bool,
        tx: DBWriteTransaction,
    ) throws {
        // Iterate UsernameLookupRecord newest -> oldest, and record the ACIs of
        // any duplicate usernames.
        var seenUsernames: Set<String> = []
        var acisWithDupeUsernames: Set<Data> = []
        let cursor = try Row.fetchCursor(
            tx.database,
            sql: "SELECT * FROM UsernameLookupRecord ORDER BY rowid DESC",
        )
        while let row = try cursor.next() {
            let aci: Data = row["aci"]
            var username: String = row["username"]

            if caseInsensitive {
                username = username.lowercased()
            }

            if !seenUsernames.insert(username).inserted {
                acisWithDupeUsernames.insert(aci)
            }
        }

        // Delete duplicates.
        for aci in acisWithDupeUsernames {
            try tx.database.execute(
                sql: "DELETE FROM UsernameLookupRecord WHERE aci = ?",
                arguments: [aci],
            )
        }

        let usernameColumnExpr = if caseInsensitive {
            "\"username\" COLLATE NOCASE"
        } else {
            "\"username\""
        }

        try tx.database.execute(sql: """
            CREATE UNIQUE INDEX "UsernameLookupRecord_UniqueUsernames"
            ON "UsernameLookupRecord"(\(usernameColumnExpr))
        """)
    }

    static func fixUpcomingCallLinks(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            UPDATE "CallLink" SET "isUpcoming"=0 WHERE "isUpcoming"=1 AND "adminPasskey" IS NULL
            """,
        )
    }

    static func fixRevokedForRestoredCallLinks(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            UPDATE "CallLink" SET "revoked"=0 WHERE "revoked" IS NULL AND "expiration" IS NOT NULL
            """,
        )
    }

    static func fixNameForRestoredCallLinks(tx: DBWriteTransaction) throws {
        try tx.database.execute(
            sql: """
            UPDATE "CallLink" SET "name"=NULL WHERE "name" IS ''
            """,
        )
    }

    /// A very rough check for "is this a brand new installation?"
    private static func hasAnyAccountManagerState(tx: DBReadTransaction) throws -> Bool {
        return try Bool.fetchOne(
            tx.database,
            sql: "SELECT 1 FROM keyvalue WHERE collection = ?",
            arguments: ["TSStorageUserAccountCollection"],
        ) ?? false
    }

    static func createSession(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "Session") {
            $0.column("id", .integer).primaryKey().notNull()
            $0.column("recipientId", .integer).notNull()
                .references("model_SignalRecipient", column: "id", onDelete: .cascade, onUpdate: .cascade)
            $0.column("localIdentity", .integer).notNull()
            $0.column("deviceId", .integer).notNull()
            $0.column("serializedRecord", .blob)
            $0.check(sql: #"1 <= "deviceId" AND "deviceId" <= 127"#)
        }
        // For fetching session(s) for a recipient.
        try tx.database.create(
            index: "Session_Unique",
            on: "Session",
            columns: ["recipientId", "localIdentity", "deviceId"],
            options: [.unique],
        )
    }

    static func migrateSessions(tx: DBWriteTransaction) throws {
        // If these ever change, you'll need to add a new migration to update the
        // Session table and replace the old constants with the new constants.
        assert(OWSIdentity.aci.rawValue == 0)
        assert(OWSIdentity.pni.rawValue == 1)

        try migrateSessions(in: "TSStorageManagerSessionStoreCollection", identity: 0, tx: tx)
        try migrateSessions(in: "TSStorageManagerPNISessionStoreCollection", identity: 1, tx: tx)
    }

    static func migrateSessions(
        in collection: String,
        identity: Int64,
        tx: DBWriteTransaction,
    ) throws {
        let keys = try String.fetchAll(
            tx.database,
            sql: "SELECT key FROM keyvalue WHERE collection = ?",
            arguments: [collection],
        )
        for key in keys { try autoreleasepool {
            let dataValue = try Data.fetchOne(
                tx.database,
                sql: "SELECT value FROM keyvalue WHERE collection = ? AND key = ?",
                arguments: [collection, key],
            )!
            let sessionDictionary: [Int32: Data?]
            let decodedValue = try? NSKeyedUnarchiver.unarchivedObject(
                ofClasses: [NSDictionary.self, NSNumber.self, NSData.self],
                from: dataValue,
            ) as? [Int32: Data]
            if let decodedValue {
                sessionDictionary = decodedValue
            } else {
                // We expect some failures (for legacy data), and if there are failures, we
                // want to remember that there was a session, even though we can't do
                // anything with that session. (See also `hasSessionRecords`).
                Logger.warn("Storing nil for \(key) in \(collection) that couldn't be decoded")
                sessionDictionary = [1: nil]
            }
            let recipientId = try Int64.fetchOne(
                tx.database,
                sql: "SELECT id FROM model_SignalRecipient WHERE uniqueId = ?",
                arguments: [key],
            )
            guard let recipientId else {
                // If we can't find the SignalRecipient, these sessions aren't reachable,
                // so we don't need to keep them. (Foreign key constraints will enforce
                // this moving forward.)
                Logger.warn("Skipping \(key) in \(collection) that's been orphaned")
                return
            }
            for (deviceId, serializedRecord) in sessionDictionary {
                guard deviceId >= 1, deviceId <= 127 else {
                    Logger.warn("Skipping \(deviceId) for \(key) in \(collection) that's not valid")
                    continue
                }
                try tx.database.execute(
                    sql: "INSERT INTO Session (recipientId, localIdentity, deviceId, serializedRecord) VALUES (?, ?, ?, ?)",
                    arguments: [
                        recipientId,
                        identity,
                        deviceId,
                        serializedRecord,
                    ],
                )
            }
        }}
    }

    static func dropOldSessions(tx: DBWriteTransaction) throws {
        let collections = [
            "TSStorageManagerSessionStoreCollection",
            "TSStorageManagerPNISessionStoreCollection",
        ]
        for collection in collections {
            try tx.database.execute(sql: "DELETE FROM keyvalue WHERE collection = ?", arguments: [collection])
        }
    }

    static func addRecipientStatus(tx: DBWriteTransaction) throws {
        try tx.database.alter(table: "model_SignalRecipient") {
            $0.add(column: "status", .integer).notNull().defaults(to: 0)
        }
        // For fetching whitelisted recipients.
        try tx.database.create(index: "Recipient_Status", on: "model_SignalRecipient", columns: ["status"])
    }

    static func migrateRecipientWhitelist(tx: DBWriteTransaction) throws {
        let serviceIdCollection = "kOWSProfileManager_UserUUIDWhitelistCollection"
        let phoneNumberCollection = "kOWSProfileManager_UserWhitelistCollection"

        var recipientIds = Set<SignalRecipient.RowId>()

        let serviceIdStrings = try String.fetchAll(
            tx.database,
            sql: "SELECT key FROM keyvalue WHERE collection = ?",
            arguments: [serviceIdCollection],
        )
        for serviceIdString in serviceIdStrings {
            guard let serviceId = try? ServiceId.parseFrom(serviceIdString: serviceIdString) else {
                continue
            }
            switch serviceId.concreteType {
            case .aci(let aci):
                recipientIds.insert(try fetchOrCreateRecipient(.V2, aciString: aci.serviceIdUppercaseString, tx: tx))
            case .pni(let pni):
                recipientIds.insert(try fetchOrCreateRecipient(.V2, pniString: pni.serviceIdUppercaseString, tx: tx))
            }
        }

        let phoneNumberStrings = try String.fetchAll(
            tx.database,
            sql: "SELECT key FROM keyvalue WHERE collection = ?",
            arguments: [phoneNumberCollection],
        )
        for phoneNumberString in phoneNumberStrings {
            guard let phoneNumber = E164(phoneNumberString) else {
                continue
            }
            recipientIds.insert(try fetchOrCreateRecipient(.V2, phoneNumber: phoneNumber.stringValue, tx: tx))
        }

        if !recipientIds.isEmpty {
            Logger.info("adding \(recipientIds.count) recipients to the whitelist (from \(serviceIdStrings.count) service ids & \(phoneNumberStrings.count) phone numbers)")
        }

        for recipientId in recipientIds {
            try tx.database.execute(sql: "UPDATE model_SignalRecipient SET status = 1 WHERE id = ?", arguments: [recipientId])
        }

        for collection in [serviceIdCollection, phoneNumberCollection] {
            try tx.database.execute(sql: "DELETE FROM keyvalue WHERE collection = ?", arguments: [collection])
        }
    }

    static func createKeyTransparencyTable(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "KeyTransparency") { table in
            table.column("aci", .blob).primaryKey().notNull()
            table.column("libsignalBlob", .blob).notNull()
        }
    }

    static func addDevice(tx: DBWriteTransaction) throws {
        try tx.database.create(table: "Device") { table in
            table.column("deviceId", .integer).primaryKey().notNull()
            table.column("createdAt", .double).notNull()
            table.column("lastSeenAt", .double).notNull()
            table.column("name", .text)
        }
        try tx.database.execute(sql: """
        DELETE FROM "OWSDevice" WHERE NOT ("deviceId" >= 1 AND "deviceId" <= 127)
        """)
        try tx.database.execute(sql: """
        INSERT OR IGNORE INTO "Device" ("deviceId", "createdAt", "lastSeenAt", "name") SELECT "deviceId", unixepoch("createdAt", 'subsec'), unixepoch("lastSeenAt", 'subsec'), "name" FROM "OWSDevice"
        """)
        try tx.database.drop(table: "OWSDevice")
    }

    static func wipeBackupAttachmentUploadQueueForLinkedDevices(tx: DBWriteTransaction) throws {
        let localDeviceId = try Int64.fetchOne(
            tx.database,
            sql: """
            SELECT value FROM keyvalue WHERE
            collection = 'TSStorageUserAccountCollection' AND key = 'TSAccountManager_DeviceId'
            """,
        )

        guard let localDeviceId, localDeviceId != DeviceId.primary.rawValue else {
            return
        }

        try tx.database.execute(
            sql: """
            DELETE FROM BackupAttachmentUploadQueue
            """,
        )
    }

    static func dedupeSignalRecipients(tx: DBWriteTransaction) throws {
        struct Recipient: FetchableRecord, Decodable {
            var id: Int64
            var aciString: String?
            var phoneNumber: String?
        }
        let fetchAllRecipients = "SELECT id, recipientUUID aciString, recipientPhoneNumber phoneNumber FROM model_SignalRecipient"

        let recipientCursor = try Recipient.fetchCursor(tx.database, sql: fetchAllRecipients)
        var recipientsByAciString = [String: [Int64]]()
        var recipientsByPhoneNumber = [String: [Recipient]]()
        while let recipient = try recipientCursor.next() {
            if let aciString = recipient.aciString {
                recipientsByAciString[aciString, default: []].append(recipient.id)
            }
            if let phoneNumber = recipient.phoneNumber {
                recipientsByPhoneNumber[phoneNumber, default: []].append(recipient)
            }
        }

        // First, ensure there are no duplicate ACIs. If there are, we pick the one
        // with the lowest rowID because that's the one that would be returned when
        // fetching by ACI.
        for (_, rowIds) in recipientsByAciString {
            for duplicateRowId in rowIds.sorted().dropFirst() {
                try tx.database.execute(sql: "DELETE FROM model_SignalRecipient WHERE id = ?", arguments: [duplicateRowId])
            }
        }

        // Next, ensure there are no duplicate phone numbers. If there are, we
        // similarly keep the first one, and then we clear (if there's an ACI) or
        // delete (if there's not an ACI) the others.
        for (_, recipients) in recipientsByPhoneNumber {
            for recipient in recipients.sorted(by: { $0.id < $1.id }).dropFirst() {
                let mutationSql: String
                if recipient.aciString != nil {
                    mutationSql = "UPDATE model_SignalRecipient SET recipientPhoneNumber = NULL WHERE id = ?"
                } else {
                    mutationSql = "DELETE FROM model_SignalRecipient WHERE id = ?"
                }
                try tx.database.execute(sql: mutationSql, arguments: [recipient.id])
            }
        }
    }
}

// MARK: -

private func hasRunMigration(_ identifier: String, transaction: DBReadTransaction) -> Bool {
    do {
        return try String.fetchOne(
            transaction.database,
            sql: "SELECT identifier FROM grdb_migrations WHERE identifier = ?",
            arguments: [identifier],
        ) != nil
    } catch {
        owsFail("Error: \(error.grdbErrorForLogging)")
    }
}

private func insertMigration(_ identifier: String, db: Database) {
    do {
        try db.execute(sql: "INSERT INTO grdb_migrations (identifier) VALUES (?)", arguments: [identifier])
    } catch {
        owsFail("Error: \(error.grdbErrorForLogging)")
    }
}

private func removeMigration(_ identifier: String, db: Database) {
    do {
        try db.execute(sql: "DELETE FROM grdb_migrations WHERE identifier = ?", arguments: [identifier])
    } catch {
        owsFail("Error: \(error.grdbErrorForLogging)")
    }
}
