feat: update jbuilder and query

UNSTABLE

Gemfile

@@ -17,6 +17,7 @@ gem 'jbuilder'
 gem 'kaminari'
 gem 'responders'
 gem 'rest-client'
+gem 'scenic'
 gem 'telephone_number'
 gem 'time_diff'
 gem 'tzinfo-data'

Gemfile.lock

@@ -656,6 +656,9 @@ GEM
       sprockets (> 3.0)
       sprockets-rails
       tilt
+    scenic (1.7.0)
+      activerecord (>= 4.0.0)
+      railties (>= 4.0.0)
     scout_apm (5.3.3)
       parser
     scss_lint (0.60.0)
@@ -881,6 +884,7 @@ DEPENDENCIES
   rubocop-performance
   rubocop-rails
   rubocop-rspec
+  scenic
   scout_apm
   scss_lint
   seed_dump

app/models/contact.rb

@@ -19,7 +19,7 @@
 #  index_contacts_on_account_id                          (account_id)
 #  index_contacts_on_lower_email_account_id              (lower((email)::text), account_id)
 #  index_contacts_on_name_email_phone_number_identifier  (name,email,phone_number,identifier) USING gin
-#  index_contacts_on_identifiable_fields                 (account_id,email,phone_number,identifier) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text)) # rubocop:disable Layout/LineLength
+#  index_contacts_on_nonempty_fields                     (account_id,email,phone_number,identifier) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))  # rubocop:disable Layout/LineLength
 #  index_contacts_on_phone_number_and_account_id         (phone_number,account_id)
 #  uniq_email_per_account_contact                        (email,account_id) UNIQUE
 #  uniq_identifier_per_account_contact                   (identifier,account_id) UNIQUE

app/models/conversation_with_label.rb

@@ -0,0 +1,84 @@
+# == Schema Information
+#
+# Table name: conversation_with_labels
+#
+#  id                     :integer
+#  additional_attributes  :jsonb
+#  agent_last_seen_at     :datetime
+#  assignee_last_seen_at  :datetime
+#  contact_last_seen_at   :datetime
+#  custom_attributes      :jsonb
+#  first_reply_created_at :datetime
+#  identifier             :string
+#  labels_array           :string           is an Array
+#  last_activity_at       :datetime
+#  priority               :integer
+#  snoozed_until          :datetime
+#  status                 :integer
+#  uuid                   :uuid
+#  created_at             :datetime
+#  updated_at             :datetime
+#  account_id             :integer
+#  assignee_id            :integer
+#  campaign_id            :bigint
+#  contact_id             :bigint
+#  contact_inbox_id       :bigint
+#  display_id             :integer
+#  inbox_id               :integer
+#  team_id                :bigint
+#
+# Indexes
+#
+#  idx_conv_labels_view__acc_id__status__last_activity  (account_id,status,last_activity_at)
+#  index_conversation_with_labels_on_custom_attributes  (custom_attributes) USING gin
+#  index_conversation_with_labels_on_id                 (id) UNIQUE
+#  index_conversation_with_labels_on_labels_array       (labels_array) USING gin
+#  index_conversation_with_labels_on_last_activity_at   (last_activity_at)
+#
+class ConversationWithLabel < ApplicationRecord
+  self.primary_key = :id
+
+  belongs_to :account
+  belongs_to :inbox
+  belongs_to :assignee, class_name: 'User', optional: true
+  belongs_to :contact
+  belongs_to :contact_inbox
+  belongs_to :team, optional: true
+  belongs_to :campaign, optional: true
+
+  has_many :mentions
+  has_many :messages
+  has_one :csat_survey_response
+  has_many :conversation_participants
+  has_many :notifications, as: :primary_actor
+  has_many :attachments, through: :messages
+
+  scope :assigned_to, ->(agent) { where(assignee_id: agent.id) }
+  scope :unassigned, -> { where(assignee_id: nil) }
+  scope :assigned, -> { where.not(assignee_id: nil) }
+  scope :latest, -> { order(last_activity_at: :desc) }
+
+  def self.refresh
+    # https://github.com/scenic-views/scenic#what-about-materialized-views
+    # needs atleast one unuque index for concurrent refresh
+    Scenic.database.refresh_materialized_view(table_name, concurrently: true, cascade: false)
+  end
+
+  def label_list
+    labels_array
+  end
+
+  def muted?
+    Redis::Alfred.get(mute_key).present?
+  end
+
+  def readonly?
+    true
+  end
+
+  private
+
+  def mute_key
+    format(Redis::RedisKeys::CONVERSATION_MUTE_KEY, id: id)
+  end
+end

app/services/conversations/filter_service.rb

@@ -36,20 +36,20 @@ class Conversations::FilterService < FilterService
 
     case current_filter['attribute_type']
     when 'additional_attributes'
-      " conversations.additional_attributes ->> '#{attribute_key}' #{filter_operator_value} #{query_operator} "
+      " additional_attributes ->> '#{attribute_key}' #{filter_operator_value} #{query_operator} "
     when 'date_attributes'
-      " (conversations.#{attribute_key})::#{current_filter['data_type']} #{filter_operator_value}#{current_filter['data_type']} #{query_operator} "
+      " (#{attribute_key})::#{current_filter['data_type']} #{filter_operator_value}#{current_filter['data_type']} #{query_operator} "
     when 'standard'
       if attribute_key == 'labels'
         " tags.name #{filter_operator_value} #{query_operator} "
       else
-        " conversations.#{attribute_key} #{filter_operator_value} #{query_operator} "
+        " #{attribute_key} #{filter_operator_value} #{query_operator} "
       end
     end
   end
 
   def base_relation
-    Current.account.conversations.left_outer_joins(:labels)
+    ConversationWithLabel.where(account_id: Current.account.id)
   end
 
   def current_page
@@ -58,7 +58,7 @@ class Conversations::FilterService < FilterService
 
   def conversations
     @conversations = @conversations.includes(
-      :taggings, :inbox, { assignee: { avatar_attachment: [:blob] } }, { contact: { avatar_attachment: [:blob] } }, :team, :messages, :contact_inbox
+      :inbox, { assignee: { avatar_attachment: [:blob] } }, { contact: { avatar_attachment: [:blob] } }, :team
     )
 
     @conversations.latest.page(current_page)

app/views/api/v1/conversations/partials/_conversation.json.jbuilder

@@ -16,20 +16,20 @@ json.meta do
   json.hmac_verified conversation.contact_inbox&.hmac_verified
 end
 
-json.id conversation.display_id
+# json.id conversation.display_id
-if conversation.messages.first.blank?
+# if conversation.messages.first.blank?
-  json.messages []
+#   json.messages []
-elsif conversation.unread_incoming_messages.count.zero?
+# elsif conversation.unread_incoming_messages.count.zero?
-  json.messages [conversation.messages.includes([{ attachments: [{ file_attachment: [:blob] }] }]).last.try(:push_event_data)]
+#   json.messages [conversation.messages.includes([{ attachments: [{ file_attachment: [:blob] }] }]).last.try(:push_event_data)]
-else
+# else
-  json.messages conversation.unread_messages.includes([{ attachments: [{ file_attachment: [:blob] }] }]).last(10).map(&:push_event_data)
+#   json.messages conversation.unread_messages.includes([{ attachments: [{ file_attachment: [:blob] }] }]).last(10).map(&:push_event_data)
-end
+# end
 
 json.account_id conversation.account_id
 json.additional_attributes conversation.additional_attributes
 json.agent_last_seen_at conversation.agent_last_seen_at.to_i
 json.assignee_last_seen_at conversation.assignee_last_seen_at.to_i
-json.can_reply conversation.can_reply?
+# json.can_reply conversation.can_reply?
 json.contact_last_seen_at conversation.contact_last_seen_at.to_i
 json.custom_attributes conversation.custom_attributes
 json.inbox_id conversation.inbox_id
@@ -39,8 +39,8 @@ json.snoozed_until conversation.snoozed_until
 json.status conversation.status
 json.created_at conversation.created_at.to_i
 json.timestamp conversation.last_activity_at.to_i
-json.first_reply_created_at conversation.first_reply_created_at.to_i
+# json.first_reply_created_at conversation.first_reply_created_at.to_i
-json.unread_count conversation.unread_incoming_messages.count
+# json.unread_count conversation.unread_incoming_messages.count
-json.last_non_activity_message conversation.messages.non_activity_messages.first.try(:push_event_data)
+# json.last_non_activity_message conversation.messages.non_activity_messages.first.try(:push_event_data)
 json.last_activity_at conversation.last_activity_at.to_i
 json.priority conversation.priority

db/migrate/20230602120613_create_conversation_with_labels.rb

@@ -0,0 +1,5 @@
+class CreateConversationWithLabels < ActiveRecord::Migration[7.0]
+  def change
+    create_view :conversation_with_labels, materialized: true
+  end
+end

db/migrate/20230602123133_add_index_to_conversation_with_labels_view.rb

@@ -0,0 +1,13 @@
+class AddIndexToConversationWithLabelsView < ActiveRecord::Migration[7.0]
+  disable_ddl_transaction!
+
+  def change
+    # needs atleast one unuque index for concurrent refresh
+    add_index :conversation_with_labels, :id, unique: true, algorithm: :concurrently
+    add_index :conversation_with_labels, [:account_id, :status, :last_activity_at], algorithm: :concurrently,
+                                                                                    name: 'idx_conv_labels_view__acc_id__status__last_activity'
+    add_index :conversation_with_labels, :custom_attributes, using: :gin, algorithm: :concurrently
+    add_index :conversation_with_labels, :labels_array, using: :gin, algorithm: :concurrently
+    add_index :conversation_with_labels, :last_activity_at, order: { last_activity_at: :desc }, algorithm: :concurrently
+  end
+end

db/schema.rb

@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema[7.0].define(version: 2023_05_23_104139) do
+ActiveRecord::Schema[7.0].define(version: 2023_06_02_123133) do
   # These are extensions that must be enabled in order to support this database
   enable_extension "pg_stat_statements"
   enable_extension "pg_trgm"
@@ -935,6 +935,43 @@ ActiveRecord::Schema[7.0].define(version: 2023_05_23_104139) do
   add_foreign_key "active_storage_attachments", "active_storage_blobs", column: "blob_id"
   add_foreign_key "active_storage_variant_records", "active_storage_blobs", column: "blob_id"
   add_foreign_key "inboxes", "portals"
+
+  create_view "conversation_with_labels", materialized: true, sql_definition: <<-SQL
+      SELECT conversations.id,
+      conversations.account_id,
+      conversations.inbox_id,
+      conversations.status,
+      conversations.assignee_id,
+      conversations.created_at,
+      conversations.updated_at,
+      conversations.contact_id,
+      conversations.display_id,
+      conversations.contact_last_seen_at,
+      conversations.agent_last_seen_at,
+      conversations.additional_attributes,
+      conversations.contact_inbox_id,
+      conversations.uuid,
+      conversations.identifier,
+      conversations.last_activity_at,
+      conversations.team_id,
+      conversations.campaign_id,
+      conversations.snoozed_until,
+      conversations.custom_attributes,
+      conversations.assignee_last_seen_at,
+      conversations.first_reply_created_at,
+      conversations.priority,
+      array_agg(tags.name) AS labels_array
+     FROM ((conversations
+       LEFT JOIN taggings ON (((taggings.taggable_id = conversations.id) AND ((taggings.taggable_type)::text = 'Conversation'::text) AND ((taggings.context)::text = 'labels'::text))))
+       LEFT JOIN tags ON ((tags.id = taggings.tag_id)))
+    GROUP BY conversations.id;
+  SQL
+  add_index "conversation_with_labels", ["account_id", "status", "last_activity_at"], name: "idx_conv_labels_view__acc_id__status__last_activity"
+  add_index "conversation_with_labels", ["custom_attributes"], name: "index_conversation_with_labels_on_custom_attributes", using: :gin
+  add_index "conversation_with_labels", ["id"], name: "index_conversation_with_labels_on_id", unique: true
+  add_index "conversation_with_labels", ["labels_array"], name: "index_conversation_with_labels_on_labels_array", using: :gin
+  add_index "conversation_with_labels", ["last_activity_at"], name: "index_conversation_with_labels_on_last_activity_at", order: :desc
+
   create_trigger("accounts_after_insert_row_tr", :generated => true, :compatibility => 1).
       on("accounts").
       after(:insert).

db/views/conversation_with_labels_v01.sql

@@ -0,0 +1,11 @@
+SELECT
+  conversations.*,
+  array_agg(tags.name) as labels_array
+FROM
+  conversations
+  LEFT JOIN taggings ON taggings.taggable_id = conversations.id
+  AND taggings.taggable_type = 'Conversation'
+  AND taggings.context = 'labels'
+  LEFT JOIN tags ON tags.id = taggings.tag_id
+GROUP BY
+  conversations.id;