[search-service] Exact full name search fails when text contains special characters due to adjacency operator

[kapil371]

Describe the bug

The PsqlQueryBuilder._formatAndSanitize() method strips all special characters from search queries and joins tokens with the <-> (FOLLOWED
BY/adjacency) operator. This causes searches to fail when the indexed text contains special characters (parentheses, hyphens, brackets, etc.)
because:

1. Special characters are stripped from the search query but affect tokenization in the indexed tsvector
2. The <-> operator requires tokens to be immediately adjacent, which fails when the original text has punctuation between words

Code Location: services/search-service/src/classes/psql/query.builder.ts (lines 80-87)

_formatAndSanitize(param: string) {                                                                                                                    
  return param                                                                                                                                         
    .replace(/[^A-Za-z\s0-9]/g, ' ')  // Strips ALL special characters                                                                                 
    .split(' ')                                                                                                                                        
    .filter(p => p)                                                                                                                                    
    .map(p => `${p}:*`)                                                                                                                                
    .join('<->');  // Adjacency operator - too strict                                                                                                  
}                                                                                                                                                      

To Reproduce

Steps to reproduce the behavior:

1. Create a searchable entity with a name containing special characters, e.g., "RCP Project (Phase 1)" or "ABC-123 Test Project"
2. Use the search endpoint to search for the exact full name
3. Observe that no results are returned

Example:

• Entity name: "Project ABC-123 (Test)"
• Search query: "Project ABC-123 (Test)"
• Sanitized tsquery: Project:* <-> ABC:* <-> 123:* <-> Test:*
• Result: No match because <-> requires immediate token adjacency, but the tsvector has different positions due to punctuation

Expected behavior

Searching for an exact or partial name should return matching results regardless of special characters in the text. The search should be more
lenient with token proximity.

Proposed Solution

Replace the strict <-> (FOLLOWED BY) operator with one of:

1. `&` (AND) operator - Tokens must all exist but in any order/position

   .join(' & ');  // Less strict, allows any position                                                                                                  

2. `` (WITHIN N WORDS) operator - Allow some distance between tokens

   .join('<2>');  // Tokens within 2 words of each other                                                                                               

3. Configurable operator - Let users choose the matching strategy

   .join(this.options.tokenJoinOperator ?? '<->');                                                                                                     

Additional context

• Package version: @sourceloop/search-service@9.0.0 (also affects latest 11.0.0)
• Database: PostgreSQL with full-text search
• Impact: Users cannot find entities by their exact names when names contain common characters like hyphens, parentheses, brackets, slashes,
  etc.
• Frequency: Always reproducible (5/5)
• Environments affected: All environments using PostgreSQL full-text search