Insights from my ongoing analysis.

Current implementation
NotificationViewModel retrieves ALL notifications to memory to apply a complex logic for state, filtering and sorting:

• sorting by date
• hiding read notifications
• filtering for a search string: case-insensitive matching with the notification's title, header, body, link text
• exclusion of certain type of notifications
• exclusion of certain wikis (based on language codes) using StringUtil.dbNameToLangCode to extract the language code from the wiki db name
• filtering of category ("Mentions")

All this works in realtime (limited by the number of notifications): All computations are done on the UI thread!

Furthermore, there is also a potential race condition because when updating notification state to the API, a parallel request may overwrite the locally stored notification (can be mitigated but not totally avoided with an isProcessing flag which is reset when server request returns success.)

I did not find any test cases for NotificationViewModel (will add them with the PR).

Requirement for using Paging3
The Paging3 (with RemoteMediator) requires a Room database query to retrieve all applicable notifications.
This allows to keep only a fraction of all notifications in memory.

Approach #1: Complex Query
Considering all the above functionality, this query will be quite complex, especially given the string manipulations done on the wiki db name to prepare the matching with the language codes:

@Query("""
    SELECT * FROM Notification 
    WHERE REPLACE(
        CASE WHEN INSTR(wiki, 'wiki') > 0 
        THEN SUBSTR(wiki, 1, INSTR(wiki, 'wiki') - 1) 
        ELSE wiki END, 
        '_', '-'
    ) IN (:includedLanguageCodes)
    ORDER BY timestamp DESC
""")

Above example would only provide the language code - all other filtering would still need to go in there.

Approach #2: Derive wiki db name from language code
Considering the multitude of wikis this might also lead to some strange corner-cases and of course to frequent updates of the logic when the convention changes.
But it could give a significant performance boost for the search because we could inject all wiki db names to be excluded into the query and thereby avoid the string manipulation in the query.

Approach #3: Implement an own Paging Source
Similar to the current approach, we could keep all notifications in memory and use the Room database only to reduce the number of API calls.

Approach #4: Insert additional column to Room database for language code
When loading from the API and before storing in the local database, we could use the existing logic for computing the language code and store that additional information in the database.
Then we could even index the database on that column.
Of course, this requires also a migration scheme.

Which approach do you see not feasible or which approach do you see favourable?

Shall we split this PR into multiple ones for looking into each implementation approach separately or keep it together?

As one of the next steps, I will establish test cases which allow measuring the performance of each approach.
This may give us decision base to exclude certain approaches.

Update:

I created an initial version of a refactored view model/repository which works entirely with a Room-database and does not do any Kotlin-based in-memory manipulation to sort/filter the data.
I ran the tests on a P101-EEA with 3.0GB RAM and Android 10
I created a synthetic dataset of 1000 notifications which filters down to 56 notifications using various exclusions currently implemented.
Each experiment did 100 repetitions (using the same filtering parameters). I repeated each experiment three times.
All data in milliseconds

Of course, I will share all the test cases with the PR.

The query used for selecting the notifications is as follows:

@Query("""
    SELECT * FROM Notification
    WHERE ((:hideReadNotifications = 0) OR (read IS NULL))
    AND ((:searchQuery IS NULL) OR
         (title LIKE '%' || :searchQuery || '%') OR
         (contents LIKE '%' || :searchQuery || '%'))
    AND ((:hasExclusions = 0) OR (category NOT IN (:excludedTypeCodes)))
    AND (REPLACE(
        CASE WHEN wiki LIKE '%wiki' THEN SUBSTR(wiki, 1, LENGTH(wiki)-4) ELSE wiki END,
        '_', '-'
    ) IN (:includedWikiCodes))
    AND ((:hideNotMentioned = 0) OR
         (category IN (:mentionsGroup)) OR
         (((INSTR(category, '-') > 0) AND (SUBSTR(category, 1, INSTR(category, '-') - 1) IN (:mentionsGroup)))))
    ORDER BY timestamp DESC
""")

Performance data makes me quite optimistic that this approach #1 may be feasible. I will continue with the implementation.

Question: Shall I keep both, the adapted legacy implementation and the new refactored code inside (at least for the first review loop of the PR)? If yes, any idea on naming convention?

Update:
Note to above post: Room-database query was executed with in-memory database. Hence, the data is not valid.

Studying the legacy code in more detail, I discovered that above query does not perfectly recreate the results.
In the function processList of NotificationViewModel, the following code filters out notifications based on the user selection of categories:

if (excludedTypeCodes.find { n.category.startsWith(it) } != null) {
    continue
}

This cannot be recreated using INSTR function inside an SQL query - the INSTR function can only work on data from the database but not on injected data.

So I updated the query as below (Yes, it is ugly.).

I reran the performance tests - this time also using "disk"-based Room database instead of in-memory database.

Although the performance data of the refactored version looks better, one shall bear in mind that the similar Room queries have to be executed additionally for the count of all notifications and the "mentions" notifications.

For the performance testing I used 1000 notifications in 100 iterations with filter setting that filtered down to 28 notifications.

SELECT * FROM Notification
WHERE ((:hideReadNotifications = 0) OR (read IS NULL))
AND ((:searchQuery IS NULL) OR
     (LOWER(title) LIKE '%' || LOWER(:searchQuery) || '%') OR
     (LOWER(contents) LIKE '%' || LOWER(:searchQuery) || '%'))
AND (
    (:excludeSystem = 0 OR category NOT LIKE 'system%') AND
    (:excludeMilestone = 0 OR category NOT LIKE 'thank-you-edit%') AND
    (:excludeUserTalk = 0 OR category NOT LIKE 'edit-user-talk%') AND
    (:excludeEditThank = 0 OR category NOT LIKE 'edit-thank%') AND
    (:excludeReverted = 0 OR category NOT LIKE 'reverted%') AND
    (:excludeLoginFail = 0 OR category NOT LIKE 'login-fail%') AND
    (:excludeMention = 0 OR category NOT LIKE 'mention%') AND
    (:excludeEmailUser = 0 OR category NOT LIKE 'emailuser%') AND
    (:excludeUserRights = 0 OR category NOT LIKE 'user-rights%') AND
    (:excludeArticleLinked = 0 OR category NOT LIKE 'article-linked%') AND
    (:excludeAlpha = 0 OR category NOT LIKE 'alpha-builder-checker%') AND
    (:excludeReadingList = 0 OR category NOT LIKE 'reading-list-syncing%') AND
    (:excludeSyncing = 0 OR category NOT LIKE 'syncing%') AND
    (:excludeRecommended = 0 OR category NOT LIKE 'recommended-reading-lists%') AND
    (:excludeGames = 0 OR category NOT LIKE 'games%')
)
AND (REPLACE(
    CASE WHEN wiki LIKE '%wiki' THEN SUBSTR(wiki, 1, LENGTH(wiki)-4) ELSE wiki END,
    '_', '-'
) IN (:includedWikiCodes))
AND ((:hideNotMentioned = 0) OR
     (category LIKE 'mention%') OR
     (category LIKE 'edit-user-talk%') OR
     (category LIKE 'emailuser%') OR
     (category LIKE 'user-rights%') OR
     (category LIKE 'reverted%'))
ORDER BY timestamp DESC

Some notes as preparation for the pull request:
The approach used in legacy code and the Paging3/RemoteMediator approach have some key differences.
All data from the server is loaded into the database, page by page.
The data from the database is then loaded completely into memory and filtered, sorted.
At least this is my understanding of what the legacy code does.

Paging3/RemoteMediator approach is to load only the data the user can see (and some more extra as buffer) from the server to the database.
The repository and the remote mediator work together to load the right notifications from the server and store them in the database.
The repository provides a reactive PagingData-flow to the view model which captures these database updates and feeds them into the view model.

The most striking difference between the two approaches is that the filtering/sorting logic moves from the in-memory-processing in the view model as done in the legacy code to the database DAO which provides the PagingSource.