API Updates to support conversation search

[pranavrajs]

API improvements in conversation search

• Support contact.name, contact.email, contact.phone_number, contact.identifier in search. (enhancement: Include search by email address for message search #5413)
• Support conversation.display_id in search. (Search by conversation number #5406)
• Support email.subject in the search.

[tejaswinichile]

Investigation around multi-model full-text search:

This is mainly for which model will be a good fit for search enhancement around multiple models.

We have joins on contacts and conversations, and having an 'ILIKE' search is not good according to performance until and unless we write a raw query

another approach is to use materialized view - which doesn't get updated on its own, so we will need to write a stored procedure for the same to update the DB views

The third approach is using the pg_search gem, which comes up with a multi-search model I am currently screening this.

[tejaswinichile]

Notes:

The search is working well on the multi-search-model there are some limitations while creating the pg_search_document for contact as we wanted to save the conversation_id associated with the contact.

And conversation_id is not available until and unless contact is created, so after_save callback to create pg_search text with conversation_id was not working, so I moved it to the conversation model.

It was a recommended approach in this issue Casecommons/pg_search#121

While deploying this feature, we would need to rebuild the pg_search_document record for Message, COnversation, and Contact models. I will be writing migration for the same, but there would be downtime considering the number of messages we have in the system. However, pg_search handles this creation of records in the batches.

Now the limitation that I found in UX.

Suppose we search with the name 'XYZ' and then with a conversation card. Should we show any messages? Before, it made sense as we were only searching in the messages table. Need a discussion for this.

cc @pranavrajs

[tejaswinichile]

https://www.loom.com/share/9861f6d9957a40a4b2b3b949a7715242

[pranavrajs]

@tejaswinichile Is it possible to return the model name which matches the query?