Supabase pgvector Setup Guide for n8n

You need vector search that actually works. Not a proof-of-concept that breaks under load, but a production-grade semantic search system that handles real documents, real queries, and real user expectations.

This guide walks you through building exactly that: a Supabase-backed knowledge base with pgvector for semantic search, integrated with n8n for workflow automation. You'll get specific SQL commands, Python code you can copy-paste, and n8n workflow configurations that work.

A knowledge base system with three core capabilities:

Vector semantic search - Find documents by meaning, not just keywords. "How do we handle client conflicts?" matches "Conflict of interest procedures" even without shared words.

Automatic document chunking - Break 50-page policy documents into searchable 500-character segments. Users find the exact paragraph they need, not a wall of text.

n8n workflow integration - Auto-index new documents, trigger alerts on policy updates, sync search analytics to your dashboard.

You need three things running before you start:

1. Supabase project with PostgreSQL 14+ (free tier works fine for testing)
2. pgvector extension installed (Supabase enables this in one click under Database > Extensions)
3. n8n instance (cloud or self-hosted, doesn't matter)

Missing any of these? Set them up first. The Supabase free tier gives you 500MB storage and 2GB bandwidth monthly. That's enough for 10,000+ document chunks.

Run these SQL commands in your Supabase SQL Editor. Copy-paste the entire block.

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Main knowledge base table
CREATE TABLE knowledge_base (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  chunk_index INTEGER DEFAULT 0,
  parent_doc_id BIGINT,
  embedding VECTOR(1536),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Full-text search column
ALTER TABLE knowledge_base ADD COLUMN search_vector TSVECTOR;

-- Auto-update search vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER knowledge_base_search_trigger
BEFORE INSERT OR UPDATE ON knowledge_base
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Indexes for performance
CREATE INDEX idx_kb_embedding ON knowledge_base USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX idx_kb_search_vector ON knowledge_base USING GIN (search_vector);
CREATE INDEX idx_kb_parent_doc ON knowledge_base (parent_doc_id) WHERE parent_doc_id IS NOT NULL;
CREATE INDEX idx_kb_created ON knowledge_base (created_at DESC);

-- Vector similarity search function
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding VECTOR(1536),
  match_threshold FLOAT DEFAULT 0.7,
  match_count INT DEFAULT 5
)
RETURNS TABLE (
  id BIGINT,
  title TEXT,
  content TEXT,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $
BEGIN
  RETURN QUERY
  SELECT
    knowledge_base.id,
    knowledge_base.title,
    knowledge_base.content,
    1 - (knowledge_base.embedding <=> query_embedding) AS similarity
  FROM knowledge_base
  WHERE 1 - (knowledge_base.embedding <=> query_embedding) > match_threshold
  ORDER BY knowledge_base.embedding <=> query_embedding
  LIMIT match_count;
END;
$;

What this does:

The embedding column stores 1536-dimension vectors (OpenAI's text-embedding-3-small size). The ivfflat index speeds up similarity searches by clustering similar vectors. The match_documents function is your main search interface.

The parent_doc_id field links chunks back to their source document. The metadata JSONB column stores document type, source URL, author, or whatever custom fields you need.

Use OpenAI's embedding API

. It's $0.02 per million tokens. A 500-word document costs about $0.0001 to embed.

Install the required library:

pip install openai supabase

Here's production-ready Python code:

import os
from openai import OpenAI
from supabase import create_client, Client

# Initialize clients
openai_client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
supabase: Client = create_client(
    os.getenv("SUPABASE_URL"),
    os.getenv("SUPABASE_KEY")
)

def generate_embedding(text: str) -> list[float]:
    """Generate embedding using OpenAI's text-embedding-3-small model."""
    response = openai_client.embeddings.create(
        model="text-embedding-3-small",
        input=text,
        encoding_format="float"
    )
    return response.data[0].embedding

def chunk_text(text: str, chunk_size: int = 500, overlap: int = 50) -> list[str]:
    """Split text into overlapping chunks."""
    chunks = []
    start = 0
    
    while start < len(text):
        end = start + chunk_size
        chunk = text[start:end]
        
        # Try to break at sentence boundary
        if end < len(text):
            last_period = chunk.rfind('.')
            last_newline = chunk.rfind('\n')
            break_point = max(last_period, last_newline)
            
            if break_point > chunk_size * 0.5:  # Only break if we're past halfway
                chunk = chunk[:break_point + 1]
                end = start + break_point + 1
        
        chunks.append(chunk.strip())
        start = end - overlap  # Overlap prevents losing context at boundaries
    
    return chunks

def index_document(title: str, content: str, metadata: dict = None) -> list[int]:
    """Chunk document, generate embeddings, and store in Supabase."""
    chunks = chunk_text(content)
    chunk_ids = []
    
    # Insert parent document record
    parent_result = supabase.table('knowledge_base').insert({
        'title': title,
        'content': content[:1000] + '...' if len(content) > 1000 else content,
        'chunk_index': -1,  # -1 indicates parent document
        'metadata': metadata or {}
    }).execute()
    
    parent_id = parent_result.data[0]['id']
    
    # Insert chunks with embeddings
    for idx, chunk in enumerate(chunks):
        embedding = generate_embedding(f"{title}\n\n{chunk}")
        
        result = supabase.table('knowledge_base').insert({
            'title': f"{title} (Part {idx + 1})",
            'content': chunk,
            'chunk_index': idx,
            'parent_doc_id': parent_id,
            'embedding': embedding,
            'metadata': metadata or {}
        }).execute()
        
        chunk_ids.append(result.data[0]['id'])
    
    return chunk_ids

# Example usage
if __name__ == "__main__":
    doc_title = "Client Conflict of Interest Policy"
    doc_content = """
    All attorneys must check for conflicts before accepting new clients.
    The conflict check process involves three steps:
    1. Search the client database for existing relationships
    2. Review adverse party lists from active cases
    3. Submit conflict waiver forms if potential conflicts exist
    
    Conflicts are waived only with written client consent after full disclosure.
    The managing partner must approve all conflict waivers within 48 hours.
    """
    
    chunk_ids = index_document(
        title=doc_title,
        content=doc_content,
        metadata={
            'document_type': 'policy',
            'department': 'legal',
            'last_reviewed': '2024-01-15'
        }
    )
    
    print(f"Indexed document into {len(chunk_ids)} chunks")

Key details:

The 50-character overlap prevents context loss when a sentence spans chunk boundaries. The sentence-boundary breaking logic keeps chunks readable. The parent document record lets you track which chunks came from the same source.

Here's the search function that ties everything together:

def search_knowledge_base(query: str, limit: int = 5, threshold: float = 0.7) -> list[dict]:
    """Search knowledge base using vector similarity."""
    query_embedding = generate_embedding(query)
    
    result = supabase.rpc('match_documents', {
        'query_embedding': query_embedding,
        'match_threshold': threshold,
        'match_count': limit
    }).execute()
    
    return result.data

# Example search
results = search_knowledge_base("How do we handle client conflicts?")

for idx, result in enumerate(results, 1):
    print(f"\n{idx}. {result['title']} (similarity: {result['similarity']:.2f})")
    print(f"   {result['content'][:200]}...")

The threshold parameter (0.7 default) filters out low-quality matches. Adjust it based on your needs: 0.8+ for high precision, 0.6+ for high recall.

Create these two workflows in n8n.

Workflow 1: Auto-Index New Documents

This workflow watches a Google Drive folder and auto-indexes new documents.

Nodes:

1. Google Drive Trigger - Fires when new file added to specific folder
2. HTTP Request - Downloads file content
3. Code - Generates embedding and chunks document
4. Supabase - Inserts chunks into knowledge_base table

Code node configuration:

// Extract text from file (assumes plain text or extracted content)
const title = $input.item.json.name;
const content = $input.item.json.content;

// Chunk the content
function chunkText(text, chunkSize = 500, overlap = 50) {
  const chunks = [];
  let start = 0;
  
  while (start < text.length) {
    const end = Math.min(start + chunkSize, text.length);
    const chunk = text.substring(start, end);
    chunks.push(chunk.trim());
    start = end - overlap;
  }
  
  return chunks;
}

const chunks = chunkText(content);

// Generate embeddings via OpenAI API
const embeddings = [];
for (const chunk of chunks) {
  const response = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${$env.OPENAI_API_KEY}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      model: 'text-embedding-3-small',
      input: `${title}\n\n${chunk}`
    })
  });
  
  const data = await response.json();
  embeddings.push({
    title: `${title} (Part ${embeddings.length + 1})`,
    content: chunk,
    embedding: data.data[0].embedding,
    chunk_index: embeddings.length
  });
}

return embeddings.map(e => ({ json: e }));

Supabase node configuration:

• Operation: Insert
• Table: knowledge_base
• Columns: Map from Code node output

Workflow 2: Search API Endpoint

Expose your knowledge base search as an HTTP endpoint.

Nodes:

1. Webhook - Receives POST requests with {"query": "search text"}
2. Code - Generates query embedding
3. Supabase - Calls match_documents function
4. Respond to Webhook

   Webhook

   Click to read the full definition in our AI & Automation Glossary.

   - Returns search results as JSON

Code node for embedding:

const query = $input.item.json.query;

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

const data = await response.json();

return [{
  json: {
    query_embedding: data.data[0].embedding
  }
}];

Supabase node configuration:

• Operation: Execute SQL
• SQL: SELECT * FROM match_documents($1::vector, 0.7, 5)
• Parameters: [$input.item.json.query_embedding]

Test the webhook

with curl:

curl -X POST https://your-n8n-instance.com/webhook/kb-search \
  -H "Content-Type: application/json" \
  -d '{"query": "conflict of interest procedures"}'

Your search will slow down after 10,000+ documents. Fix it with these optimizations:

Increase ivfflat lists - More lists = faster search, more memory:

DROP INDEX idx_kb_embedding;
CREATE INDEX idx_kb_embedding ON knowledge_base 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 500);  -- Increase from 100 to 500

Add probes for accuracy - Query-time parameter that checks more clusters:

SET ivfflat.probes = 10;  -- Check 10 clusters instead of default 1

Partition by date - If you have time-sensitive documents:

CREATE TABLE knowledge_base_2024 PARTITION OF knowledge_base
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Monitor query performance:

EXPLAIN ANALYZE
SELECT * FROM match_documents('[0.1, 0.2, ...]'::vector, 0.7, 5);

Look for "Index Scan using idx_kb_embedding" in the output. If you see "Seq Scan", your index isn't being used.

Before you go live:

• [ ] Set up Row Level Security (RLS) policies in Supabase to restrict access
• [ ] Create a separate service role key for n8n (don't use the anon key)
• [ ] Add rate limiting to your search webhook

  webhook

  Click to read the full definition in our AI & Automation Glossary.

  (10 requests/minute per IP)
• [ ] Set up monitoring for embedding API

  API

  Click to read the full definition in our AI & Automation Glossary.

  costs (alert if >$10/day)
• [ ] Create a backup workflow that exports knowledge_base to S3 daily
• [ ] Test search quality with 20+ real user queries, adjust threshold if needed
• [ ] Document your chunking strategy (chunk size, overlap) for future reference

You now have a production-grade semantic search system. Users get relevant results. You get automated indexing. The system scales to hundreds of thousands of documents without breaking.