Skip to content

Saayam Search Implementation Plan

Jump to bottom
Arup Chauhan edited this page Jul 4, 2026 · 3 revisions

Saayam Search Implementation Plan

Overview

This document outlines our finalized two-phase approach to implementing comprehensive search capabilities for the Saayam platform using PostgreSQL native features.

Table of Contents

Phase 1: Full-Text Search (FTS) + pg_trgm

Status: DB implementation complete and locally validated
Implementation: 5 Stages
Cost: $0 additional search infrastructure cost
Next Step: QA/RDS deployment and validation

What We're Implementing

PostgreSQL native Full-Text Search combined with the pg_trgm extension provides:

  • Keyword Search: Find help requests, users, and organizations by searchable text.
  • Fuzzy Matching: Handle typos and similar terms automatically.
  • Ranked Results: Return the most relevant results first.
  • Multi-Field Search: Search across the important fields of each supported entity.
  • Authorization-Aware Filtering: Restrict results using caller context provided by the backend.
  • Real-Time Indexing: Generated search columns remain synchronized when source data changes.
  • Schema-Agnostic Execution: Use the same production SQL for Virginia and Ireland through search_path.

The current DB search scope covers:

  • Help requests
  • Users
  • Organizations
  • Help category names through request-category search

Autocomplete is currently handled as part of the API-side search flow. Volunteer-specific skill matching, multilingual search, advanced analytics, and semantic search are not included in the current DB implementation.

Why This Approach

  • Zero Additional Search Infrastructure Cost: Runs inside the existing PostgreSQL RDS instance.
  • No Data Sync Issues: Search works directly against database records.
  • ACID Compliance: Search data remains consistent with transactional database updates.
  • Battle-Tested: PostgreSQL Full-Text Search and pg_trgm are established PostgreSQL features.
  • Real-Time Updates: Generated search columns update automatically when searchable fields change.
  • Reusable Across Regions: Shared production scripts run against Virginia and Ireland schemas.
  • Defense in Depth: Database functions apply authorization-aware filtering before returning rows.
  • Easy Maintenance: Production SQL and test assets are separated into clear folders.

Phase 1: Implementation Checklist

Stage 1: Setup & Foundation

Objective: Enable fuzzy-search support and prepare the migration structure.

  • Enable the PostgreSQL pg_trgm extension.
  • Make extension setup idempotent using IF NOT EXISTS.
  • Create the production migration directory under ddl/Search/.
  • Separate production SQL from local test assets.
  • Add Virginia and Ireland migration runners.
  • Make production SQL schema-agnostic through search_path.
  • Verify extension-dependent behavior locally.
  • Run extension setup and verification against QA/RDS.
  • Confirm the production backup and rollback procedure before production rollout.

Deliverables:

  • ddl/Search/01_enable_fuzzy_search.sql
  • tests/Search/runners/run_virginia_search_migrations.sql
  • tests/Search/runners/run_ireland_search_migrations.sql
  • tests/Search/QA_VALIDATION.md

Stage 2: Request Search Implementation

Objective: Enable ranked full-text and fuzzy search for help requests.

Target Tables: request, help_categories
Searchable Fields: req_subj, req_desc, req_loc, and cat_name through the request-category join

Tasks:

  • Add a generated and weighted search_vector to request.
  • Add a GIN full-text index on request.search_vector.
  • Add trigram indexes for request subject, description, and location text.
  • Add full-text and trigram indexes for category names.
  • Add category-backed request search.
  • Combine Full-Text Search and fuzzy scores into ranked results.
  • Add empty-query handling and configurable result limits.
  • Add authorization-aware filtering.
  • Validate keyword, location, category, and typo-tolerant search locally.

The request ranking combines:

  • 70% Full-Text Search score
  • 30% fuzzy similarity score

Authorization-aware request search supports:

  • admin-level access
  • requester self-scope
  • allowed request-owner IDs
  • zero results when a non-admin caller has no valid scope

Indexes:

  • idx_request_search_vector
  • idx_request_subj_trgm
  • idx_request_desc_trgm
  • idx_request_loc_trgm
  • idx_help_categories_name_fts
  • idx_help_categories_name_trgm

Search Function:

search_requests(
  query_text,
  limit_results,
  requester_user_id,
  requester_access_level,
  allowed_request_owner_ids
)

Deliverables:

  • ddl/Search/02_add_request_search.sql
  • search_requests(...)
  • Request full-text and trigram indexes
  • Request behavior and function tests
  • Authorization-aware request filtering

PRD Alignment:

  • ✓ Users can search help requests by keywords.
  • ✓ Search handles common typing errors.
  • ✓ Results are ranked by relevance.
  • ✓ Subject, description, location text, and category are searchable.
  • ✓ Results can be restricted using caller context.
  • ✓ Local p95 latency baseline meets the Phase 1 target.
  • QA/RDS performance confirmation remains pending.

Stage 3: User Search Implementation

Objective: Enable user search by name and email.

Target Table: users
Searchable Fields: full_name, first_name, last_name, primary_email_address

Tasks:

  • Add a generated search_vector to users.
  • Add a GIN full-text index for user search.
  • Add trigram indexes for full name and email.
  • Add a btree index for exact email matching.
  • Add full-text name search and fuzzy name/email matching.
  • Give exact email matches a strong ranking signal.
  • Add authorization-aware user filtering.
  • Add empty-query handling.
  • Validate user search locally.

Authorization-aware user search supports:

  • admin-level access
  • user self-scope
  • allowed user IDs
  • zero results when a non-admin caller has no valid scope

Indexes:

  • idx_users_search_vector
  • idx_users_full_name_trgm
  • idx_users_email_trgm
  • idx_users_email_exact

Search Function:

search_users(
  query_text,
  limit_results,
  requester_user_id,
  requester_access_level,
  allowed_user_ids
)

Deliverables:

  • ddl/Search/03_add_user_and_volunteer_search.sql
  • search_users(...)
  • User full-text, trigram, and exact-email indexes
  • User behavior and function tests
  • Authorization-aware user filtering

PRD Alignment:

  • ✓ Admin-level callers can search users by name or email.
  • ✓ Search handles name variations and typing errors.
  • ✓ Exact email matching is supported.
  • ✓ Results can be restricted using caller context.

Volunteer-specific skill, availability, and location matching are not part of the current DB implementation. They can be added separately if included in a future approved scope.

Stage 4: Organization and Category-Backed Search

Objective: Complete the current DB search scope with organization search and category-backed request matching.

Target Tables: organizations, help_categories
Searchable Organization Fields: org_name, city_name

Tasks:

  • Add trigram search on organization names.
  • Add trigram search on organization city names.
  • Add ranked organization results.
  • Weight organization-name matches above city matches.
  • Add authorization-aware organization filtering.
  • Add category-name search through the request-category join.
  • Add full-text and fuzzy category-name indexes.
  • Validate organization and category-backed request matching locally.

The organization ranking combines:

  • 70% organization-name similarity
  • 30% city-name similarity

Indexes:

  • idx_organizations_name_trgm
  • idx_organizations_city_trgm
  • idx_help_categories_name_fts
  • idx_help_categories_name_trgm

Search Function:

search_organizations(
  query_text,
  limit_results,
  requester_access_level,
  allowed_org_ids
)

Deliverables:

  • ddl/Search/04_add_category_and_advanced_search.sql
  • search_organizations(...)
  • Organization trigram indexes
  • Category-backed request matching
  • Authorization-aware organization filtering

PRD Alignment:

  • ✓ Organizations can be searched by name and city text.
  • ✓ Request search includes category-name matching.
  • ✓ Results are ranked by relevance.
  • ✓ Organization results can be restricted using caller context.

Standalone category search, database-managed autocomplete, multilingual configurations, highlighting, analytics, and advanced filter parameters are not included in the current DB implementation. Autocomplete is currently part of the API-side search flow.

Stage 5: Testing & Optimization

Objective: Validate search behavior, schema compatibility, indexes, authorization filtering, and the local performance baseline.

Completed:

  • Create a separate search test workspace under tests/Search/.
  • Add Virginia and Ireland local schema clones.
  • Add schema-specific migration and validation runners.
  • Add smoke, index, function, and clone-shape checks.
  • Validate authorization-aware filtering.
  • Validate the same production SQL against both schemas.
  • Complete the local p95 latency baseline.
  • Confirm the local p95 baseline meets the Phase 1 target.
  • Document the QA/RDS validation flow.

Test Structure:

tests/Search/
├── README.md
├── QA_VALIDATION.md
├── runners/
├── test_clones/
├── virginia_validation/
└── ireland_validation/

Local Validation Results:

Virginia-style schema:

  • ✓ Migration execution passed.
  • ✓ Smoke test passed.
  • ✓ Index check passed.
  • ✓ Function check passed.
  • ✓ Instance clone check passed.

Ireland-style schema:

  • ✓ Migration execution passed.
  • ✓ Smoke test passed.
  • ✓ Index check passed.
  • ✓ Function check passed.
  • ✓ Migrated clone check passed.

Pending in QA:

  • Run production search migrations against QA/RDS.
  • Confirm extension and object-creation permissions.
  • Capture EXPLAIN and EXPLAIN ANALYZE.
  • Confirm index usage with representative data.
  • Confirm the local p95 baseline on RDS.
  • Test concurrent search traffic and production-scale data volumes.
  • Configure CloudWatch monitoring and slow-query alerts.
  • Validate final backend-to-DB authorization context mapping.

Deliverables:

  • Virginia and Ireland migration runners
  • Virginia and Ireland validation runners
  • Local schema clones
  • Smoke, index, function, and authorization-filter validation
  • Local p95 latency baseline
  • QA validation runbook

Phase 1: Expected Outcomes

Performance Metrics

Metric Target Current Status
Query Latency (p95) <100ms Met in local baseline; QA confirmation pending
Fuzzy typo matching Common 1-2 character errors Locally validated
Ranked results Relevant results first Locally validated
Virginia compatibility Required Locally validated
Ireland compatibility Required Locally validated
Authorization filtering Required Locally validated
Concurrent users 100+ QA/load testing pending
Production-scale volume Up to 1M+ records QA/performance testing pending

PRD Requirements Met

Search Functionality:

  • ✓ Full-text keyword search across requests.
  • ✓ Fuzzy matching for common typing errors.
  • ✓ Ranked results by relevance.
  • ✓ Multi-field request search.
  • ✓ User search by name and email.
  • ✓ Organization search by name and city.
  • ✓ Request-category and location-text matching.
  • ✓ Authorization-aware DB filtering.
  • ✓ Shared production SQL across Virginia and Ireland schemas.

User Experience:

  • ✓ Handles misspellings gracefully.
  • ✓ Shows relevant results first.
  • ✓ Supports keyword and fuzzy matching through the DB functions.
  • ✓ Returns no results for empty queries.
  • ✓ Local p95 latency baseline meets the Phase 1 target.

Technical Requirements:

  • ✓ Uses existing PostgreSQL infrastructure.
  • ✓ Uses native PostgreSQL Full-Text Search and pg_trgm.
  • ✓ Uses GIN indexes for Full-Text Search and trigram matching.
  • ✓ Maintains transactional consistency.
  • ✓ Uses generated columns for automatic search-vector updates.
  • ✓ Uses schema-agnostic production SQL.
  • ✓ Passes Virginia and Ireland local validation.

Phase 2: Semantic Search with pgvector

Status: Future Enhancement (6-12 months)
Implementation: 3 Stages
Cost: $20-50/month (OpenAI embeddings)

What Is Semantic Search?

Semantic search understands the meaning of queries, not just exact keywords:

  • Example 1: Search "heart attack" → finds "cardiac arrest", "chest pain", "myocardial infarction"
  • Example 2: Search "feeling alone" → finds "depression", "isolation", "loneliness"
  • Example 3: Search "need doctor" → finds "medical help", "physician", "healthcare"

Why Add pgvector?

Advantages Over FTS + pg_trgm:

  1. Conceptual Understanding
    • FTS: Matches words literally
    • pgvector: Understands synonyms and related concepts
  2. Cross-Language Search
    • Search in English → find Hindi/Spanish results
    • Unified search across languages
  3. Duplicate Detection
    • Find similar requests automatically
    • Prevent duplicate help requests
  4. Smarter Volunteer Matching
    • Match volunteers based on skill semantics
    • "Python developer" matches "software engineer", "programmer"
  5. Better User Experience
    • Users don't need exact keywords
    • More forgiving search
    • Higher result relevance

How pgvector Works

1. User searches: "need help with groceries"
   ↓
2. Convert query to embedding (vector): [0.23, -0.45, 0.67, ...]
   ↓
3. Find requests with similar embeddings (cosine similarity)
   ↓
4. Return: "food assistance", "meal delivery", "grocery shopping help"

Embeddings = Numeric representations of text meaning (1536 dimensions)

Phase 2: Implementation Checklist

Stage 1: pgvector Setup

Objective: Enable pgvector and generate embeddings

Tasks:

  • Enable pgvector extension

    CREATE EXTENSION IF NOT EXISTS vector;

  • Add embedding columns

    ALTER TABLE request ADD COLUMN search_embedding vector(1536);
ALTER TABLE users ADD COLUMN profile_embedding vector(1536);
ALTER TABLE volunteer_details ADD COLUMN skills_embedding vector(1536);

  • Create vector indexes

    CREATE INDEX idx_request_embedding
  ON request USING ivfflat(search_embedding vector_cosine_ops)
  WITH (lists = 100);

  • Setup embedding generation

    • Option A: OpenAI API integration
    • Option B: Self-hosted model (sentence-transformers)
    • Create Lambda/script for batch generation

  • Generate embeddings for existing data

    • Batch process: 1000 records at a time
    • Estimate: ~10K requests × $0.0001 = $1 one-time

Deliverables:

  • 05_pgvector_setup.sql
  • Embedding generation script
  • Cost analysis and monitoring

Stage 2: Hybrid Search Implementation

Objective: Combine FTS + pg_trgm + pgvector for best results

Tasks:

  • Create hybrid search function

    CREATE FUNCTION search_requests_hybrid(
  query_text TEXT,
  query_embedding vector(1536),
  keyword_weight FLOAT DEFAULT 0.4,
  fuzzy_weight FLOAT DEFAULT 0.2,
  semantic_weight FLOAT DEFAULT 0.4
) RETURNS TABLE (...) AS $$
  -- Combine all three search methods
$$ LANGUAGE plpgsql;

  • Implement semantic duplicate detection

    • Find similar requests automatically
    • Alert users about existing requests
    • Reduce duplicate help requests

  • Enhanced volunteer matching

    • Match by skill embeddings
    • Consider semantic similarity
    • Rank volunteers by relevance

  • Cross-language search

    • Generate embeddings in multiple languages
    • Unified search experience

Deliverables:

  • 06_hybrid_search.sql
  • Duplicate detection service
  • Enhanced volunteer matching algorithm

Stage 3: Testing & Integration

Objective: Validate semantic search quality and integrate with API

Tasks:

  • Quality testing
    • Test semantic search accuracy
    • Compare with FTS-only results
    • A/B test with real users
  • Performance tuning
    • Optimize vector index parameters
    • Test query latency (target: <150ms)
    • Cache embeddings for frequent queries
  • API integration
    • Update search endpoints
    • Add hybrid search option
    • Remain backward compatible with Phase 1
  • Cost monitoring
    • Track embedding API usage
    • Optimize batch generation
    • Set budget alerts

Deliverables:

  • Quality comparison report
  • Updated API documentation
  • Cost monitoring dashboard

Phase 2: Expected Outcomes

Enhanced Capabilities

Feature Phase 1 (FTS + pg_trgm) Phase 2 (+ pgvector) Improvement
Keyword match Excellent Excellent Same
Typo tolerance Excellent Excellent Same
Synonym matching None Excellent ✓ New
Conceptual search None Excellent ✓ New
Cross-language Limited Excellent ✓ Major
Duplicate detection Manual Automatic ✓ New
Relevance score Good (85%) Excellent (95%+) ✓ +10%

PRD Requirements Met (Additional)

Enhanced Search:

  • ✓ Understands synonyms and related concepts
  • ✓ Cross-language search capability
  • ✓ Finds conceptually similar requests
  • ✓ Automatic duplicate detection

Smarter Matching:

  • ✓ Semantic volunteer-to-request matching
  • ✓ Skill-based recommendations
  • ✓ Context-aware search results

User Experience:

  • ✓ More forgiving search (no exact keywords needed)
  • ✓ Higher relevance scores
  • ✓ Reduced duplicate requests

Cost Analysis

Component Cost Notes
Infrastructure $0 Uses existing RDS
Embedding generation (new requests) $5-10 ~50-100 requests/day
Embedding generation (users) $1-2 ~10-20 new users/day
API calls (search) $10-20 Cached embeddings, minimal calls
Total Monthly $20-50 Scales with usage

One-time existing-data embedding cost: approximately $1-5 for 10K-50K records.

Success Metrics & Monitoring

Phase 1 Metrics

Metric Target Current Status
Query latency (p95) <100 ms Met in local baseline; QA confirmation pending
Fuzzy typo matching Common 1-2 character errors Locally validated
Ranked results Relevant results first Locally validated
Virginia schema compatibility Required Locally validated
Ireland schema compatibility Required Locally validated
Authorization filtering Required Locally validated
Index availability Required Locally validated
Concurrent users 100+ QA/load testing pending
Production-scale volume Up to 1M+ records QA/performance testing pending
CloudWatch monitoring Required for production Pending QA/RDS integration

Phase 2 Metrics

-- Semantic search quality
SELECT
  query_text,
  search_type,
  AVG(click_through_rate) as ctr,
  AVG(time_to_first_click) as engagement
FROM search_analytics
GROUP BY query_text, search_type;

-- Duplicate detection effectiveness
SELECT
  COUNT(*) as duplicates_prevented,
  SUM(estimated_time_saved_hours) as hours_saved
FROM duplicate_detections
WHERE detected_at > NOW() - INTERVAL '30 days';

Quick Start Guide

Running Phase 1 Implementation

The migration runners set the target schema through search_path and execute all four production SQL files in order.

Virginia-Style Schema:

psql -d <database_name> \
  -v ON_ERROR_STOP=1 \
  -f tests/Search/runners/run_virginia_search_migrations.sql

psql -d <database_name> \
  -v ON_ERROR_STOP=1 \
  -f tests/Search/runners/run_virginia_search_validation.sql

Ireland-Style Schema:

psql -d <database_name> \
  -v ON_ERROR_STOP=1 \
  -f tests/Search/runners/run_ireland_search_migrations.sql

psql -d <database_name> \
  -v ON_ERROR_STOP=1 \
  -f tests/Search/runners/run_ireland_search_validation.sql

The implemented DB entry points are:

search_requests(...)
search_users(...)
search_organizations(...)

See tests/Search/README.md for complete local clone setup and tests/Search/QA_VALIDATION.md for the QA/RDS validation flow.

Running Phase 2 Implementation

# 1. Enable pgvector
\i database/ddl/Search/05_pgvector_setup.sql

# 2. Generate embeddings
python scripts/generate_embeddings.py --batch-size 1000

# 3. Implement hybrid search
\i database/ddl/Search/06_hybrid_search.sql

Resources & Documentation

PostgreSQL Documentation

Implementation Guides

  • tests/Search/README.md - Local search setup and validation flow
  • tests/Search/QA_VALIDATION.md - QA/RDS validation runbook
  • ddl/Search/ - Phase 1 production search migrations
  • API integration documentation remains part of the backend/API search work

Support

  • PostgreSQL community forums
  • pgvector GitHub issues
  • OpenAI API documentation for future embeddings work

Security & DevSecOps Action Plan

The Phase 1 DB search functions include authorization-aware filtering.

Implemented:

  • Search functions accept caller identity and access context.
  • Admin-level callers can receive broader results.
  • Non-admin results can be restricted to self-scope or allowed IDs.
  • Missing non-admin scope returns zero rows.
  • Authorization filtering is validated in the local test suite.

The backend remains responsible for authenticating the caller and securely passing trusted context into the DB functions.

Production hardening pending with Backend and DevSecOps:

  1. Implement Row-Level Security
    • Evaluate RLS policies for core tables such as request and users.
  2. Standardize User Context Passing
    • Finalize a trusted method such as SET LOCAL app.user_id.
    • Ensure caller context cannot be supplied directly by an untrusted client.
  3. Use IAM Database Authentication
    • Use short-lived AWS IAM authentication instead of static database credentials where applicable.
  4. Enable pgAudit and Monitoring
    • Configure the required RDS parameter group settings.
    • Send relevant database and security events to CloudWatch.
  5. Enforce Encryption in Transit
    • Confirm rds.force_ssl = 1 for the deployed RDS environment.

Summary of Responsibility

The DB layer provides full-text search, fuzzy matching, ranking, indexes, and authorization-aware result filtering.

The backend/API layer is responsible for authentication, API routing, trusted caller-context mapping, and formatting or combining search results.

Implementation Summary

Phase 1 (FTS + pg_trgm):

  • Stage 1: Setup and Foundation
  • Stage 2: Request Search
  • Stage 3: User Search
  • Stage 4: Organization and Category-Backed Search
  • Stage 5: Testing and Optimization
  • DB implementation complete and locally validated
  • Local p95 latency baseline meets the documented target
  • Ready for QA/RDS deployment and validation

Phase 2 (+ pgvector):

  • Stage 1: pgvector Setup
  • Stage 2: Hybrid Search Implementation
  • Stage 3: Testing & Integration
  • Total: 3 Stages, Enhanced capabilities

Clone this wiki locally