Make page_translations' search trigger self-installing

search_vector is populated by a raw Postgres trigger created via
execute() in a migration -- invisible to schema.rb, which only
represents structure Rails understands. Any database rebuilt from
schema.rb rather than replayed migrations (test, a fresh db:setup,
disaster recovery) silently lost full-text search entirely, with no
error -- NULL @@ anything is never true in Postgres.

Page.ensure_search_vector_trigger!, called from an after_initialize
initializer, reinstalls the trigger via CREATE OR REPLACE on every
boot, in every environment. Idempotent and cheap.

2 files changed, 33 insertions, 0 deletions

diff --git a/app/models/page.rb b/app/models/page.rb
index 740d42e..db5b688 100644
--- a/app/models/page.rb
+++ b/app/models/page.rb
@@ -254,6 +254,32 @@ class Page < ApplicationRecord
 
   end
 
+  # Installs (or re-installs) the trigger that keeps page_translations'
+  # search_vector in sync. Idempotent, safe to call on every boot.
+  # search_vector is populated by a raw Postgres trigger, not anything
+  # Rails' schema dumper can represent -- a database rebuilt from
+  # schema.rb rather than replayed migrations silently loses it.
+  def self.ensure_search_vector_trigger!
+    connection.execute(<<~SQL)
+      CREATE OR REPLACE FUNCTION page_translations_search_vector_update()
+      RETURNS trigger AS $$
+      BEGIN
+        NEW.search_vector := to_tsvector(
+          'simple',
+          coalesce(NEW.title, '') || ' ' ||
+          coalesce(NEW.abstract, '') || ' ' ||
+          coalesce(NEW.body, '')
+        );
+        RETURN NEW;
+      END;
+      $$ LANGUAGE plpgsql;
+
+      CREATE OR REPLACE TRIGGER page_translations_search_vector_trigger
+      BEFORE INSERT OR UPDATE ON page_translations
+      FOR EACH ROW EXECUTE PROCEDURE page_translations_search_vector_update();
+    SQL
+  end
+
   private
 
     def set_page_title
diff --git a/config/initializers/ensure_search_vector_trigger.rb b/config/initializers/ensure_search_vector_trigger.rb
new file mode 100644
index 0000000..a750e9c
--- /dev/null
+++ b/config/initializers/ensure_search_vector_trigger.rb
@@ -0,0 +1,7 @@
+Rails.application.config.after_initialize do
+  begin
+    Page.ensure_search_vector_trigger! if Page.connection.table_exists?(:page_translations)
+  rescue ActiveRecord::NoDatabaseError, ActiveRecord::ConnectionNotEstablished, PG::ConnectionBad
+    # Database doesn't exist yet -- e.g. mid `rails db:create`. Nothing to install yet.
+  end
+end