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

1 files changed, 47 insertions, 0 deletions

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