Enable full-text search via PostgreSQL tsvector on page_translations

- Restore search vector migration (was parked in doc/ pending PostgreSQL upgrade)
- Restore Node.search using plainto_tsquery with simple dictionary
- Cross-locale keyword search, no stemming, works for both de and en content

2 files changed, 53 insertions, 4 deletions

diff --git a/app/models/node.rb b/app/models/node.rb
index 41c3867..a3ef23a 100644
--- a/app/models/node.rb
+++ b/app/models/node.rb
@@ -211,11 +211,13 @@ class Node < ApplicationRecord
     self.created_at < new_id_format_date ? unique_path : id
   end
 
-  # TODO: restore full-text search once PostgreSQL is upgraded.
-  # The tsvector/plpgsql approach requires PostgreSQL 10+ with plpgsql available.
-  # For now, search is disabled to unblock the Rails 7.2 upgrade.
+  # Full-text search across all locale translations using PostgreSQL tsvector.
+  # Uses 'simple' dictionary (no stemming, no stopwords) so queries work
+  # across German and English content without language detection.
   def self.search(term, _ = {})
-    none
+    joins(head: :translations)
+      .where("page_translations.search_vector @@ plainto_tsquery('simple', ?)", term)
+      .distinct
   end
 
   protected
diff --git a/db/migrate/20260626025705_add_search_vector_to_page_translations.rb b/db/migrate/20260626025705_add_search_vector_to_page_translations.rb
new file mode 100644
index 0000000..0747637
--- /dev/null
+++ b/db/migrate/20260626025705_add_search_vector_to_page_translations.rb
@@ -0,0 +1,47 @@
+class AddSearchVectorToPageTranslations < ActiveRecord::Migration[7.2]
+  def up
+    add_column :page_translations, :search_vector, :tsvector
+
+    execute <<~SQL
+      UPDATE page_translations
+      SET search_vector = to_tsvector(
+        'simple',
+        coalesce(title, '') || ' ' ||
+        coalesce(abstract, '') || ' ' ||
+        coalesce(body, '')
+      )
+    SQL
+
+    add_index :page_translations, :search_vector,
+              using: :gin,
+              name: 'index_page_translations_on_search_vector'
+
+    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 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
+
+  def down
+    execute <<~SQL
+      DROP TRIGGER IF EXISTS page_translations_search_vector_trigger ON page_translations;
+      DROP FUNCTION IF EXISTS page_translations_search_vector_update();
+    SQL
+    remove_index :page_translations, name: 'index_page_translations_on_search_vector'
+    remove_column :page_translations, :search_vector
+  end
+end