Core of how to implement FTS search with SQLite

create_fts_posts.rb

class CreateFtsPosts < ActiveRecord::Migration[8.0]
  def change
    create_virtual_table :post_documents, :fts5,
      [:title, :body, "content=''", "contentless_delete=1", "tokenize='porter unicode61 remove_diacritics 2'"]
  end
end

post.rb

class Post < ApplicationRecord
  after_commit -> { Post::Document.build_index! }
  
  def self.search(query)
      with(
        fts_matches: Post::Document.matches(query)
      )
      .from('fts_matches')
      .joins(
        'INNER JOIN posts ON posts.rowid = fts_matches.rowid'
      )
      .select(
        posts: [:id, :body, :title, :public_id, :user_id, :created_at, :updated_at, :tags],
        fts_matches: {rank_number: :fts_rank, score: :fts_score}
      )
      .order(fts_rank: :desc)
  end
end

post_document.rb

class Post
  class Document < ApplicationRecord
    def self.build_index!
      connection.execute <<~SQL
        INSERT OR REPLACE INTO #{table_name}(rowid, title, body)
        SELECT rowid, title, body FROM #{module_parent.table_name};
      SQL
      connection.execute <<~SQL
        INSERT INTO #{table_name}(#{table_name}) VALUES('optimize');
      SQL
    end
    def self.matches(query, k: 10)
      select(
        :rowid,
        "row_number() OVER (ORDER BY rank) AS rank_number",
        "rank AS score"
      )
      .where("#{table_name} MATCH ?", query)
      .limit(k)
    end
  end
end