MultiTenant PgSearch: A How To

In my previous post I showed you how to set up pg_search to search against ActionText::RichText fields for multisearch. This post takes it a step further and add’s the ability to scope your PgSearch multisearch calls to a tenant in a multitenant based Rails application.

Problem

You’re using PgSearch for search on your Rails application, but your Rails application is a multitenant application. You could be using ActsAsTenant or the Apartment gem or  a home rolled solution to achieve multitenancy in the same database. Unfortunately the records in the pg_search_documents table are not scoped to any tenant and running PgSearch.multisearch("hello world") results in ALL the records coming back, not just the ones scoped for your current tenant.

How to Scope your PgSearch Documents to a Tenant

When insalling PgSerach Multisearch you need to run a migration to create the pg_search_documents table. When this migration is run it will look like this:

class CreatePgSearchDocuments < ActiveRecord::Migration[7.0]
  def up
    say_with_time("Creating table for pg_search multisearch") do
      create_table :pg_search_documents do |t|
        t.text :content
        t.belongs_to :searchable, polymorphic: true, index: true
        t.timestamps null: false
      end
    end
  end

  def down
    say_with_time("Dropping table for pg_search multisearch") do
      drop_table :pg_search_documents
    end
  end
end

You will need to change this migration to include a tenant so the search documents can be scoped to the tenant. I’m using an Account model for my tenants (using the ActsAsTenant gem with JumpstartRails).

I’ve changed the migration to look like this:

class CreatePgSearchDocuments < ActiveRecord::Migration[7.0]
  def up
    say_with_time("Creating table for pg_search multisearch") do
      create_table :pg_search_documents do |t|
        t.text :content
        t.references :account, index: true
        t.belongs_to :searchable, polymorphic: true, index: true
        t.timestamps null: false
      end
    end
  end

  def down
    say_with_time("Dropping table for pg_search multisearch") do
      drop_table :pg_search_documents
    end
  end
end

Notice this line:

t.references :account, index: true

This is the additional scoping column that we need to scope our search documents.

Now run the migration. You’re now ready to set up your models.

Scoping the Models to Use Your Tenant Attribute

In each of your models, you’ll need to tell multisearch about the additional account_id (the tenant scope, so yours might be team_id, etc, so just keep that in mind) in order for it to build the search document correctly.

Here’s an example I have for a Discussion model:

class Discussion < ApplicationRecord
  include PgSearch::Model
  
  belongs_to :account
  acts_as_tenant :account # ActsAsTenant gem
  multisearchable against: [:title],
                  additional_attributes: -> (discussion) { { account_id: discussion.account_id } }
end

Notice the additional_attributes.

This tells PgSearch how to find the account_id for the discussion. All of my models are scoped using ActsAsTenant with an Account model as the tenant (this is from JumpstartRails).

Important caveat …

At the time of this writing, when add these additional attributes you will need to manually call record.update_pg_search_document in order for the additional attribute to be included in the pg_search_documents_table.

Setting Up Models to use .update.pg_search_document

To have each model call record.update_pg_search_document you can override the rebuild_pg_search_documents method like this:

The Discussion model file now looks like this:

class Discussion < ApplicationRecord
  include PgSearch::Model

  belongs_to :account
  acts_as_tenant :account # ActsAsTenant gem

  multisearchable against: [:title], 
                  additional_attributes: -> (discussion) { { account_id: discussion.account_id } } 

  def self.rebuild_pg_search_documents
    find_each { |record| record.update_pg_search_document }
  end
end

Now, when I tell PgSearch to rebuild an index, this method will get called and each item in the index will have an account_id:

PgSearch::Multisearch.rebuild(Discussion)

So there’s two steps you need to perform:

1. Implement the additional_attributes onmultisearchable so PgSearch knows how to find the tenant (account_id in this example).
2. Override rebuild_pg_search_documents in each model as shown above in order to set the tenant when the index is rebuilt.

Scoping ActionText::RichText for Multitenant Searches

In my post about PgSearch and ActionText I showed you how to set up PgSearch to work with ActionText::RichText. You will also need to scope those documents as well.

In the action_text_rich_text.rb initializer (created here) you’ll want to add the additional_attributes and rebuild_pg_search_documents like this:

ActiveSupport.on_load :action_text_rich_text do
  include PgSearch::Model

  multisearchable against: :body, 
                  additional_attributes: -> (rich_text) { { account_id: rich_text.record.account_id } }

  def self.rebuild_pg_search_documents
    find_each { |record| record.update_pg_search_document }
  end
end

Notice that in the additional_attributes you are accessing the RichText record object, and then it’s account_id. Please note this assumes that every RichText in your app has an account_id.

If this is not the case, you’ll want to provide an if: index clause like this:

ActiveSupport.on_load :action_text_rich_text do
  include PgSearch::Model

  multisearchable against: :body, if: :should_index?,
                  additional_attributes: -> (rich_text) { { account_id: rich_text.record.account_id } }

  def should_index?
    record.is_a?(Discussion) || record.is_a?(Comment)
  end

  def self.rebuild_pg_search_documents
    find_each { |record| record.update_pg_search_document }
  end
end

Using the example above, the only ActionText::RichText that will be indexed will be those that are part of the Discussion model or Comment model. Modify as you see fit.

Querying the Multisearch with a Tenant

To perform a multisearch with PgSearch your command will look like this:

PgSearch.multisearch("hello world").where(account_id: 1)

Your PgSearch multisearch results will now be scoped to the tenant.

If there are results that match your search and that are in that account, you’ll get those results, otherwise you’ll get an empty array.

Enjoy!