Full-Text Search (FTS) Usage Guide¶

This document describes the implementation and usage patterns for PostgreSQL Full-Text Search in the FarmCove Delta application.

Overview¶

The application uses PostgreSQL's built-in Full-Text Search capabilities for efficient and intelligent searching across various data types. We implement two main patterns:

Direct FTS with buildFTSCondition - For searching within a single table
RPC Functions - For complex searches involving parent-child relationships

Key Features¶

Prefix matching - Allows users to type partial words and get results (e.g., "john" matches "Johnny")
Email handling - Intelligent parsing of email addresses for better matching
Phone normalization - Strips formatting from phone numbers for consistent searching
Multi-format support - Searches multiple representations of the same data (e.g., with/without dashes)
Ranking - Results are ranked by relevance when searching

Implementation Patterns¶

Pattern 1: Direct Table Search with buildFTSCondition¶

Use this pattern when searching within a single table without complex joins.

Database Setup¶

Add a generated search_query column with tsvector:

ALTER TABLE public.your_table
ADD COLUMN IF NOT EXISTS search_query tsvector GENERATED ALWAYS AS (
  to_tsvector('english',
    COALESCE(field1, '') || ' ' ||
    COALESCE(field2, '') || ' ' ||
    -- For phones, remove non-alphanumeric for better matching
    COALESCE(regexp_replace(phone_field, '\D', '', 'g'), '')
  )
) STORED;

-- Create GIN index for performance
CREATE INDEX IF NOT EXISTS idx_your_table_search_query
ON public.your_table USING gin(search_query);

Service Layer Usage¶

import { buildFTSCondition, findMany } from '@/lib/databaseHelper';

export async function searchRecords(searchTerm: string) {
  if (!searchTerm || searchTerm.length < 3) {
    return [];
  }

  const results = await findMany(
    DatabaseTable.YOUR_TABLE,
    [buildFTSCondition(searchTerm)],
    { limit: 50 }
  );

  return results;
}

Example: Address Search¶

// In services/address/base.ts
export async function findCandidateAddresses(
  addressData: CreateAddress,
  client?: DatabaseClient
): Promise<Address[]> {
  const searchConditions = [];

  // Build search from address and city
  const searchTerm = `${addressData.address_line_1} ${addressData.city}`.trim();

  if (searchTerm) {
    // Use FTS for flexible and fast searching
    searchConditions.push(buildFTSCondition(searchTerm));
  }

  const candidates = await findMany<Address>(
    DatabaseTable.ADDRESSES,
    searchConditions,
    { limit: 20, client }
  );

  return candidates || [];
}

Pattern 2: RPC Functions for Complex Searches¶

Use this pattern when you need to:

Search across parent and child tables
Apply complex business logic
Return computed fields or aggregations
Optimize performance for complex queries

Database Function Setup¶

-- Create the RPC function
CREATE OR REPLACE FUNCTION query_with_search(
  p_filter_param UUID,
  search_term TEXT DEFAULT NULL,
  p_limit INTEGER DEFAULT 50,
  p_offset INTEGER DEFAULT 0,
  p_count_only BOOLEAN DEFAULT FALSE
)
RETURNS TABLE (
  -- Define return columns
  id UUID,
  name TEXT,
  search_rank REAL,
  total_count INTEGER
)
AS $$
DECLARE
  formatted_query TEXT;
  has_search BOOLEAN;
  total_record_count INTEGER;
BEGIN
  -- Check if we have a search term
  has_search := search_term IS NOT NULL AND trim(search_term) != '';

  -- Format the query for FTS with prefix matching
  IF has_search THEN
    formatted_query := public.build_fts_query(search_term);
  END IF;

  -- Handle count-only requests efficiently
  IF p_count_only THEN
    -- Return just the count
    SELECT COUNT(*)::INTEGER INTO total_record_count
    FROM your_table t
    LEFT JOIN related_table r ON t.related_id = r.id
    WHERE t.filter_field = p_filter_param
    AND (
      NOT has_search OR
      t.search_query @@ to_tsquery('english', formatted_query) OR
      r.search_query @@ to_tsquery('english', formatted_query)
    );

    RETURN QUERY SELECT
      NULL::UUID, NULL::TEXT, 0::REAL, total_record_count;
    RETURN;
  END IF;

  -- Get total count for pagination
  -- ... (same count query as above)

  -- Return full results with ranking
  RETURN QUERY
  SELECT
    t.id,
    t.name,
    CASE WHEN has_search THEN
      GREATEST(
        COALESCE(ts_rank(t.search_query, to_tsquery('english', formatted_query)), 0),
        COALESCE(ts_rank(r.search_query, to_tsquery('english', formatted_query)), 0)
      )::REAL
    ELSE 0::REAL END as search_rank,
    total_record_count
  FROM your_table t
  LEFT JOIN related_table r ON t.related_id = r.id
  WHERE t.filter_field = p_filter_param
  AND (
    NOT has_search OR
    t.search_query @@ to_tsquery('english', formatted_query) OR
    r.search_query @@ to_tsquery('english', formatted_query)
  )
  ORDER BY
    CASE WHEN has_search THEN search_rank ELSE 0 END DESC,
    t.created_at DESC
  LIMIT p_limit OFFSET p_offset;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant permissions
GRANT EXECUTE ON FUNCTION query_with_search TO authenticated;

Service Layer Usage¶

// In services/your_service/base.ts
import { callFunction } from '@/lib/databaseHelper';
import { DatabaseFunction } from '@/constants/database';

export async function searchWithRelations(
  filterId: string,
  searchTerm?: string,
  pagination?: { page: number; pageSize: number }
) {
  const limit = pagination?.pageSize ?? 50;
  const offset = ((pagination?.page ?? 1) - 1) * limit;

  const results = await callFunction<YourType[]>(
    DatabaseFunction.QUERY_WITH_SEARCH,
    {
      p_filter_param: filterId,
      search_term: searchTerm,
      p_limit: limit,
      p_offset: offset,
      p_count_only: false,
    }
  );

  return results;
}

// Separate count function for efficient pagination
export async function getSearchCount(filterId: string, searchTerm?: string) {
  const result = await callFunction<{ total_count: number }[]>(
    DatabaseFunction.QUERY_WITH_SEARCH,
    {
      p_filter_param: filterId,
      search_term: searchTerm,
      p_count_only: true,
    }
  );

  return result[0]?.total_count ?? 0;
}

Tables with FTS Support¶

1. Entities Table¶

Column: search_query
Searches: business_name, first_name, last_name, preferred_name, email, phone_number (normalized)
Usage: Direct search via buildFTSCondition or joined in transaction/project relationship searches

2. Transactions Table¶

Column: search_query
Searches: transaction_code (multiple formats), reference_number, description
Usage: RPC function query_transactions for complex filtering with entity joins

3. Addresses Table¶

Column: search_query
Searches: address_line_1, address_line_2, city, state_province, postal_code, country
Usage: Direct search via buildFTSCondition for address deduplication

4. Project Relationships¶

Function: query_project_relationships
Searches: Entity fields (via join), role title, role title override
Usage: Complex search across multiple related tables

Frontend Integration¶

DataTable with Manual Search¶

When using DataTable with server-side FTS, enable manual search mode:

const table = useDataTable({
  columns,
  data: relationships?.data ?? [],
  manualGlobalSearch: true, // Enable manual search
  enableGlobalFilter: true,
  // ... other options
});

// Handle search with minimum 3 characters
const handleSearch = (value: string) => {
  if (value.length >= 3 || value.length === 0) {
    setSearchTerm(value.length >= 3 ? value : undefined);
  }
};

Search Input Component¶

<SearchableInput
  value={searchTerm ?? ''}
  onChange={handleSearch}
  placeholder="Search (min 3 characters)..."
  minLength={3}
/>

Performance Considerations¶

Index Management
Always create GIN indexes on tsvector columns
Monitor index size and rebuild if needed
Consider partial indexes for large tables
Query Optimization
Use p_count_only parameter for efficient pagination counts
Limit search results with reasonable defaults (50-100 items)
Consider caching frequently searched terms
Search Threshold
Require minimum 3 characters to prevent expensive broad searches
This reduces database load and improves user experience
Ranking Weights
Primary search fields (entity names) get full weight (1.0)
Secondary fields (roles, overrides) get reduced weight (0.8)
Adjust weights based on business importance

Filtering on Embedded Resources (IMPORTANT)¶

The `!inner` Requirement¶

When using Supabase/PostgREST with filters on embedded (joined) resources, you MUST use !inner in the select to properly filter parent rows.

The Problem¶

PostgREST has two different join behaviors:

LEFT JOIN (!foreign_key):
Filters on embedded resources do not affect parent rows
Returns all parent rows, but embedded resource is null when filter doesn't match
❌ Wrong for filtering - users see rows they shouldn't
INNER JOIN (!inner):
Filters on embedded resources exclude parent rows
Only returns parent rows where embedded resource matches the filter
✅ Correct for filtering - only matching rows returned

Example Issue¶

// ❌ WRONG: Using !entity_id (left join)
select: `
  *,
  entity:entities!entity_id (id, name, email)
`;

// Filter: entity.name matches "john"
// Result: Returns ALL project_relationships
//         entity = null for non-matching rows
//         User sees empty rows they shouldn't see

// ✅ CORRECT: Using !inner (inner join)
select: `
  *,
  entity:entities!inner (id, name, email)
`;

// Filter: entity.name matches "john"
// Result: Returns ONLY project_relationships where entity.name matches "john"
//         Non-matching rows are excluded entirely
//         User sees only relevant rows

When to Use `!inner`¶

Always use !inner when:

You filter on the embedded resource (e.g., entity.search_query, entity.type)
The foreign key is NOT NULL (relationship must exist)
You want consistent filtering behavior

Example implementation:

// In services/project/base.ts
const result = await findManyWithCount<ProjectRelationshipWithRelations>(
  DatabaseTable.PROJECT_RELATIONSHIPS,
  queryConditions,
  {
    select: `
      *,
      entity:entities!inner (
        id,
        type,
        search_query,
        email
      )
    `,
    // ... other options
  }
);

// Filter on embedded resource
queryConditions.push({
  column: 'entity.type',
  operator: 'eq',
  value: 'Person',
});

// This correctly excludes project_relationships where entity.type !== 'Person'

URL Syntax for Embedded Resource Filters¶

When filtering on embedded resources, use the alias from the select:

❌ Wrong:  entity:entities!entity_id.type=eq.Person
✅ Correct: entity.type=eq.Person

❌ Wrong:  entity:entities!entity_id.search_query=fts.john
✅ Correct: entity.search_query=fts.john

The filter parameter should reference the alias (entity), not the full PostgREST foreign key syntax.

Common Pitfall¶

// User searches for "john"
buildFTSCondition(searchTerm, 'entity.search_query');

// Without !inner: Returns all rows, entity=null for non-matches ❌
// With !inner: Returns only rows where entity matches "john" ✅

Reference¶

PostgREST Resource Embedding
See packages/app/src/services/project/base.ts for implementation example

Best Practices¶

Always normalize search data:
Remove formatting from phones
Include multiple representations of codes
Use COALESCE to handle NULL values
Use appropriate search pattern:
Direct buildFTSCondition for single table
RPC functions for complex joins
Consider performance implications
Handle empty searches gracefully:
Return all results when no search term
Maintain consistent ordering
Provide user feedback:
Show minimum character requirements
Display result counts
Indicate when searching
Use !inner for embedded resource filtering:
Always use !inner when filtering on joined tables
This ensures parent rows are excluded when embedded resource doesn't match
Prevents confusing null results for users
Filter using the alias from select (e.g., entity.type), not PostgREST syntax

Migration Example¶

When adding FTS to an existing table:

-- Add FTS column to existing table
ALTER TABLE public.your_table
ADD COLUMN IF NOT EXISTS search_query tsvector GENERATED ALWAYS AS (
  to_tsvector('english',
    COALESCE(searchable_field1, '') || ' ' ||
    COALESCE(searchable_field2, '') || ' ' ||
    COALESCE(searchable_field3, '')
  )
) STORED;

-- Create index
CREATE INDEX IF NOT EXISTS idx_your_table_search_query
ON public.your_table USING gin(search_query);

-- Add comment
COMMENT ON COLUMN public.your_table.search_query IS
'Full-text search vector for efficient searching. Auto-maintained.';

Troubleshooting¶

Common Issues¶

Search not returning expected results
Check if search term meets minimum length (3 characters)
Verify tsvector column is properly generated
Test the FTS query directly in SQL
Performance issues
Ensure GIN index exists and is being used (check EXPLAIN)
Consider increasing work_mem for complex searches
Review search_query column generation for efficiency
Special characters in search
FTS ignores most punctuation
Phone numbers should have formatting stripped
Email addresses are split on @ for better matching

Full-Text Search (FTS) Usage Guide¶

Overview¶

Key Features¶

Implementation Patterns¶

Pattern 1: Direct Table Search with buildFTSCondition¶

Database Setup¶

Service Layer Usage¶

Example: Address Search¶

Pattern 2: RPC Functions for Complex Searches¶

Database Function Setup¶

Service Layer Usage¶

Tables with FTS Support¶

1. Entities Table¶

2. Transactions Table¶

3. Addresses Table¶

4. Project Relationships¶

Frontend Integration¶

DataTable with Manual Search¶

Search Input Component¶

Performance Considerations¶

Filtering on Embedded Resources (IMPORTANT)¶

The !inner Requirement¶

The Problem¶

Example Issue¶

When to Use !inner¶

URL Syntax for Embedded Resource Filters¶

Common Pitfall¶

Reference¶

Best Practices¶

Migration Example¶

Troubleshooting¶

Common Issues¶

Additional Resources¶

The `!inner` Requirement¶

When to Use `!inner`¶