[Search] FTS5 Porter tokenizer doesn't support Chinese/CJK search

[GameHoo]

Description

The current full-text search implementation uses the porter tokenizer for SQLite FTS5, which only works for English. This makes searching for Chinese (and other CJK languages)
content impossible.

Steps to Reproduce

1. Create a card with Chinese title or description (e.g., "个人看板" or "测试任务")
2. Use the search functionality
3. Search for Chinese keywords from the card
4. No results are returned

Expected Behavior

Search should return cards that contain the Chinese keywords in their title or description.

Current Behavior

Chinese search returns no results, even when the exact text exists in cards.

Technical Analysis

The issue is in the FTS5 table creation (see db/migrate/20251120110206_add_search_records.rb):

CREATE VIRTUAL TABLE search_records_fts USING fts5(                                                                                                                                
  title,                                                                                                                                                                           
  content,                                                                                                                                                                         
  tokenize='porter'  ← Porter tokenizer only works for English                                                                                                                     
)                                                                                                                                                                                  
                                                                                                                                                                                   
Porter tokenizer:                                                                                                                                                                  
- Designed for English stemming (running → run)                                                                                                                                    
- Relies on space-separated words                                                                                                                                                  
- Cannot tokenize Chinese text (no spaces between characters)                                                                                                                      
                                                                                                                                                                                   
Proposed Solution                                                                                                                                                                  
                                                                                                                                                                                   
Switch to unicode61 tokenizer which supports CJK languages while maintaining English support:                                                                                      
                                                                                                                                                                                   
CREATE VIRTUAL TABLE search_records_fts USING fts5(                                                                                                                                
  title,                                                                                                                                                                           
  content,                                                                                                                                                                         
  tokenize='unicode61 remove_diacritics 0'                                                                                                                                         
)                                                                                                                                                                                  
                                                                                                                                                                                   
Trade-offs:                                                                                                                                                                        
- ✅ Supports Chinese, Japanese, Korean                                                                                                                                            
- ✅ Still works with English (space-separated tokenization)                                                                                                                       
- ❌ Loses English stemming (searching "running" won't find "run")                                                                                                                 
- ❌ CJK tokenization is character-based (less intelligent than proper word segmentation)                                                                                          
                                                                                                                                                                                   
Alternative: Advanced CJK Tokenization                                                                                                                                             
                                                                                                                                                                                   
For better CJK support, consider integrating proper tokenizers:                                                                                                                    
- Chinese: https://github.com/fxsjy/jieba or https://github.com/go-ego/gse                                                                                                         
- Japanese: https://taku910.github.io/mecab/                                                                                                                                       
- Universal: https://www.sqlite.org/fts5.html#icu_tokenizer                                                                                                                        
                                                                                                                                                                                   
This would require more setup but provide better search quality for CJK users.                                                                                                     
                                                                                                                                                                                   
Environment                                                                                                                                                                        
                                                                                                                                                                                   
- Fizzy version: [main branch as of 2026-01-19]                                                                                                                                    
- Database: SQLite                                                                                                                                                                 
- OS: macOS 15.7.1                                                                                                                                                                 
                                                                                                                                                                                   
Impact                                                                                                                                                                             
                                                                                                                                                                                   
This affects all non-English users, particularly those using Chinese, Japanese, or Korean. For international adoption, better i18n search support would be valuable.               
                                                                                                                                                                                   
Workaround                                                                                                                                                                         
                                                                                                                                                                                   
Users can manually run this migration to switch tokenizers:                                                                                                                        
                                                                                                                                                                                   
# db/migrate/YYYYMMDDHHMMSS_support_chinese_search.rb                                                                                                                              
class SupportChineseSearch < ActiveRecord::Migration[8.2]                                                                                                                          
  def up                                                                                                                                                                           
    return unless connection.adapter_name == "SQLite"                                                                                                                              
                                                                                                                                                                                   
    execute "DROP TABLE IF EXISTS search_records_fts"                                                                                                                              
                                                                                                                                                                                   
    execute <<-SQL                                                                                                                                                                 
      CREATE VIRTUAL TABLE search_records_fts USING fts5(                                                                                                                          
        title,                                                                                                                                                                     
        content,                                                                                                                                                                   
        tokenize='unicode61 remove_diacritics 0'                                                                                                                                   
      )                                                                                                                                                                            
    SQL                                                                                                                                                                            
                                                                                                                                                                                   
    execute <<-SQL                                                                                                                                                                 
      INSERT INTO search_records_fts(rowid, title, content)                                                                                                                        
      SELECT id, title, content FROM search_records                                                                                                                                
    SQL                                                                                                                                                                            
  end                                                                                                                                                                              
end