Vector Search (pgvector)

PIP AI uses the pgvector PostgreSQL extension for semantic similarity search on spec sections.

Setup

CREATE EXTENSION IF NOT EXISTS vector;

Embedding Configuration

Setting	Value
Model	OpenAI text-embedding-3-small
Dimensions	1536
Distance	Cosine
Storage Column	pip_ai_spec_sections.embedding

Index

An HNSW index provides fast approximate nearest-neighbor search:

CREATE INDEX idx_pip_ai_spec_sections_embedding
ON pip_ai_spec_sections
USING hnsw (embedding vector_cosine_ops);

Search Function

The pip_ai_search_specs() function performs hybrid search combining vector similarity with keyword matching:

CREATE FUNCTION pip_ai_search_specs(
  query_embedding vector(1536),
  query_text text,
  filter_brand text,
  filter_areas text[] DEFAULT '{}',
  match_threshold float DEFAULT 0.70,
  match_count int DEFAULT 10
) RETURNS TABLE (
  id uuid,
  spec_number text,
  title text,
  category text,
  brand_name text,
  area text,
  similarity float
)

Search Logic

1. Filter by brand_name (mandatory — brand isolation)
2. Optionally filter by area
3. Rank by cosine similarity: 1 - (embedding <=> query_embedding)
4. Include keyword matches: title ILIKE '%query%' or content ILIKE '%query%'
5. Apply threshold filter (default: 0.70)
6. Return top N results

Usage from Frontend

const { data } = await supabase.rpc('pip_ai_search_specs', {
  query_embedding: embeddingVector,
  query_text: 'window shade system',
  filter_brand: 'Holiday Inn Express',
  filter_areas: ['guest-room'],
  match_threshold: 0.7,
  match_count: 10,
})

Learning Loop

When a user manually assigns a spec to a PIP item, the PIP description is appended to pip_ai_spec_sections.past_pip_requests[]. This improves future matching by enriching the searchable content.

Embedding Generation

Embeddings are generated by N8N during spec processing using the OpenAI API:

const response = await fetch('https://api.openai.com/v1/embeddings', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${OPENAI_API_KEY}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    model: 'text-embedding-3-small',
    input: `${title} ${content} ${keywords.join(' ')}`
  })
})