Full-Text & Vector Search Integration

Executive Summary

HeliosDB Lite enables knowledge management and documentation platforms to deliver hybrid search combining full-text + semantic ranking in a single database, eliminating the need for separate search infrastructure (Elasticsearch + vector DBs). Key value propositions:

• Unified search experience (keyword + semantic combined in one query)
• Instant relevance ranking using semantic vectors

Problem Being Solved

Enterprise Pain Points

Operational Complexity:

• Maintaining 2 separate databases
• Syncing data between Elasticsearch and vector DB
• Managing two sets of indices
• Handling replication/backup for both systems
• Debugging inconsistencies across systems
• Performance tuning (optimizing both engines)

Search Quality Issues:

• Keyword-only search has low precision (false positives)
• Semantic-only search misses exact phrase matching
• Combining results from 2 systems requires custom logic
• Relevance ranking is difficult (tuning multiple algorithms)
• Synonym handling incomplete (gaps in both systems)

HeliosDB Lite Solution Architecture

Hybrid Search Architecture

┌─────────────────────────────────────────────┐
│  Knowledge Management Application           │
├─────────────────────────────────────────────┤
│                                             │
│  HeliosDB-Lite (Embedded)                   │
│  ┌───────────────────────────────────────┐ │
│  │ Documents Table                       │ │
│  │ ├─ document_id (PRIMARY KEY)          │ │
│  │ ├─ title (TEXT)                       │ │
│  │ ├─ content (TEXT)                     │ │
│  │ ├─ embedding (VECTOR)                 │ │
│  │ ├─ category (VARCHAR)                 │ │
│  │ ├─ created_at (TIMESTAMP)             │ │
│  │ └─ metadata (JSONB)                   │ │
│  ├───────────────────────────────────────┤ │
│  │ Indices                               │ │
│  │ ├─ Full-text index (inverted)         │ │
│  │ ├─ Vector HNSW index (semantic)       │ │
│  │ ├─ Category index (filtering)         │ │
│  │ └─ Composite index (ranking)          │ │
│  ├───────────────────────────────────────┤ │
│  │ Full-Text Engine                      │ │
│  │ ├─ Tokenization & stemming            │ │
│  │ ├─ TF-IDF scoring                     │ │
│  │ ├─ Phrase matching                    │ │
│  │ └─ Fuzzy matching (typos)             │ │
│  ├───────────────────────────────────────┤ │
│  │ Vector Search Engine                  │ │
│  │ ├─ HNSW indexing                      │ │
│  │ ├─ Cosine/Euclidean distance          │ │
│  │ ├─ Semantic similarity ranking        │ │
│  │ └─ Approximate nearest neighbor       │ │
│  └───────────────────────────────────────┘ │
│                                             │
│  Hybrid Search Logic (in SQL)               │
│  ├─ Combine keyword + semantic scores      │
│  ├─ Filter by metadata (category, date)    │
│  ├─ Apply business rules (authority, etc)  │
│  └─ Return ranked results                  │
│                                             │
└─────────────────────────────────────────────┘
             ↓ (HTTP/REST API)
      ┌──────────────────────┐
      │  Search UI           │
      │  ├─ Search box       │
      │  ├─ Filters          │
      │  ├─ Results ranking  │
      │  └─ Faceted search   │
      └──────────────────────┘

Implementation Examples

Example 1: Hybrid Search Schema (SQL)

-- Full-text & vector search setup
CREATE TABLE documents (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding VECTOR(384),  -- OpenAI embeddings
    category VARCHAR(50),
    authority_score FLOAT,
    created_at TIMESTAMP,
    metadata JSONB
);

-- Full-text index (inverted index)
CREATE INDEX idx_content_fulltext ON documents USING FULLTEXT (content);

-- Vector search index (HNSW)
CREATE INDEX idx_embedding_hnsw ON documents USING HNSW (embedding)
WITH (metric='cosine', m=16);

-- Composite index for filtering
CREATE INDEX idx_category_created ON documents(category, created_at DESC);

Example 2: Hybrid Search Query (SQL)

-- Combined full-text + semantic search
SELECT
    id, title,
    (
        -- Full-text relevance (0-1 scale)
        CASE WHEN content @@ plainto_tsquery('english', ?) THEN 0.5 ELSE 0 END
        +
        -- Vector semantic relevance (0-1 scale, cosine distance)
        (1 - (embedding <-> ?)) * 0.5
    ) as combined_score,
    authority_score,
    created_at,
    metadata
FROM documents
WHERE category = ?
    AND created_at > now() - interval '1 year'
ORDER BY combined_score DESC, authority_score DESC
LIMIT 20;

Conclusion

HeliosDB Lite enables knowledge management platforms to deliver superior search experience by combining full-text and semantic search in a single, unified database.