feat: use resolved contacts for filtering

app/models/account.rb

@@ -2,20 +2,19 @@
 #
 # Table name: accounts
 #
-#  id                         :integer          not null, primary key
-#  auto_resolve_duration      :integer
-#  contactable_contacts_count :integer          default(0)
-#  custom_attributes          :jsonb
-#  domain                     :string(100)
-#  feature_flags              :bigint           default(0), not null
-#  internal_attributes        :jsonb            not null
-#  limits                     :jsonb
-#  locale                     :integer          default("en")
-#  name                       :string           not null
-#  status                     :integer          default("active")
-#  support_email              :string(100)
-#  created_at                 :datetime         not null
-#  updated_at                 :datetime         not null
+#  id                    :integer          not null, primary key
+#  auto_resolve_duration :integer
+#  custom_attributes     :jsonb
+#  domain                :string(100)
+#  feature_flags         :bigint           default(0), not null
+#  internal_attributes   :jsonb            not null
+#  limits                :jsonb
+#  locale                :integer          default("en")
+#  name                  :string           not null
+#  status                :integer          default("active")
+#  support_email         :string(100)
+#  created_at            :datetime         not null
+#  updated_at            :datetime         not null
 #
 # Indexes
 #

app/models/contact.rb

@@ -25,13 +25,14 @@
 # Indexes
 #
 #  index_contacts_on_account_id                          (account_id)
-#  index_contacts_on_account_id_and_last_activity_at     (account_id,last_activity_at DESC NULLS LAST)
 #  index_contacts_on_blocked                             (blocked)
 #  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_nonempty_fields                     (account_id,email,phone_number,identifier) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))
 #  index_contacts_on_phone_number_and_account_id         (phone_number,account_id)
+#  index_contacts_searchable_fields_gin                  (name gin_trgm_ops, email gin_trgm_ops, phone_number gin_trgm_ops, identifier gin_trgm_ops, ((additional_attributes ->> 'company_name'::text)) gin_trgm_ops) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text)) USING gin
 #  index_resolved_contact_account_id                     (account_id) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))
+#  index_resolved_contacts_on_account_and_last_activity  (account_id,last_activity_at DESC NULLS LAST) WHERE (((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))
 #  uniq_email_per_account_contact                        (email,account_id) UNIQUE
 #  uniq_identifier_per_account_contact                   (identifier,account_id) UNIQUE
 #
@@ -67,6 +68,8 @@ class Contact < ApplicationRecord
 
   enum contact_type: { visitor: 0, lead: 1, customer: 2 }
 
+  scope :resolved_contacts, -> { where("contacts.email <> '' OR contacts.phone_number <> '' OR contacts.identifier <> ''") }
+
   scope :order_on_last_activity_at, lambda { |direction|
     order(
       Arel::Nodes::SqlLiteral.new(
@@ -163,10 +166,6 @@ class Contact < ApplicationRecord
     }
   end
 
-  def self.resolved_contacts
-    where("contacts.email <> '' OR contacts.phone_number <> '' OR contacts.identifier <> ''")
-  end
-
   def discard_invalid_attrs
     phone_number_format
     email_format

app/services/contacts/filter_service.rb

@@ -10,7 +10,7 @@ class Contacts::FilterService < FilterService
 
   def perform
     validate_query_operator
-    @contacts = query_builder(@filters['contacts'])
+    @contacts = query_builder(@filters['contacts']).resolved_contacts
 
     {
       contacts: @contacts,

db/migrate/20250325125320_update_indexes_for_contacts.rb

@@ -0,0 +1,51 @@
+class UpdateIndexesForContacts < ActiveRecord::Migration[7.0]
+  disable_ddl_transaction!
+
+  def up
+    add_searchable_index
+    remove_old_index
+    add_activity_index
+  end
+
+  def down
+    remove_index :contacts, name: 'index_contacts_searchable_fields_gin', if_exists: true
+    remove_index :contacts, name: 'index_resolved_contacts_on_account_and_last_activity', if_exists: true
+
+    add_index :contacts,
+              [:account_id, :last_activity_at],
+              order: { last_activity_at: 'DESC NULLS LAST' },
+              algorithm: :concurrently,
+              name: 'index_contacts_on_account_id_and_last_activity_at'
+  end
+
+  private
+
+  def add_searchable_index
+    execute "SET statement_timeout = '3600000';"
+    execute 'DROP INDEX IF EXISTS index_contacts_searchable_fields_gin;'
+    execute <<-SQL.squish
+      CREATE INDEX CONCURRENTLY index_contacts_searchable_fields_gin
+      ON contacts USING gin (
+        name gin_trgm_ops,
+        email gin_trgm_ops,
+        phone_number gin_trgm_ops,
+        identifier gin_trgm_ops,
+        (additional_attributes->>'company_name') gin_trgm_ops
+      )
+      WHERE (email <> '' OR phone_number <> '' OR identifier <> '');
+    SQL
+  end
+
+  def remove_old_index
+    remove_index :contacts, name: 'index_contacts_on_account_id_and_last_activity_at', if_exists: true
+  end
+
+  def add_activity_index
+    add_index :contacts,
+              [:account_id, :last_activity_at],
+              name: 'index_resolved_contacts_on_account_and_last_activity',
+              where: "(email <> '' OR phone_number <> '' OR identifier <> '')",
+              order: { last_activity_at: 'DESC NULLS LAST' },
+              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: 2025_03_15_202035) do
+ActiveRecord::Schema[7.0].define(version: 2025_03_25_125320) do
   # These extensions should be enabled to support this database
   enable_extension "pg_stat_statements"
   enable_extension "pg_trgm"
@@ -497,8 +497,9 @@ ActiveRecord::Schema[7.0].define(version: 2025_03_15_202035) do
     t.string "country_code", default: ""
     t.boolean "blocked", default: false, null: false
     t.index "lower((email)::text), account_id", name: "index_contacts_on_lower_email_account_id"
+    t.index "name gin_trgm_ops, email gin_trgm_ops, phone_number gin_trgm_ops, identifier gin_trgm_ops, ((additional_attributes ->> 'company_name'::text)) gin_trgm_ops", name: "index_contacts_searchable_fields_gin", where: "(((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))", using: :gin
     t.index ["account_id", "email", "phone_number", "identifier"], name: "index_contacts_on_nonempty_fields", where: "(((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))"
-    t.index ["account_id", "last_activity_at"], name: "index_contacts_on_account_id_and_last_activity_at", order: { last_activity_at: "DESC NULLS LAST" }
+    t.index ["account_id", "last_activity_at"], name: "index_resolved_contacts_on_account_and_last_activity", order: { last_activity_at: "DESC NULLS LAST" }, where: "(((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))"
     t.index ["account_id"], name: "index_contacts_on_account_id"
     t.index ["account_id"], name: "index_resolved_contact_account_id", where: "(((email)::text <> ''::text) OR ((phone_number)::text <> ''::text) OR ((identifier)::text <> ''::text))"
     t.index ["blocked"], name: "index_contacts_on_blocked"