Skip to main content

Knowledge > Runbooks > Technical Ops > Regenerate Vector Embeddings

Regenerate Vector Embeddings

Regenerate OpenAI vector embeddings for content rows in unified_rag_content when embeddings are missing, stale, or the embedding model has changed.

Prerequisites​

  • Access to embedding generation scripts in sermon-illustrations/scripts/ or ChurchWiseAI scripts
  • OpenAI API key available (this is a legitimate API call — embeddings are not batch content generation)
  • Scope of regeneration identified: specific rows, a content type, or full table

Cost Estimate Before Starting​

text-embedding-3-small costs $0.02 per 1M tokens.

ScopeApprox TokensApprox Cost
100 rows (~500 tokens avg)50K<$0.01
10K rows5M~$0.10
Full table (327K rows)163M~$3.26

Confirm scope with founder before running full-table regeneration.

Steps​

  1. Identify what needs regeneration

    Find rows with null embeddings:

    SELECT count(*) FROM unified_rag_content WHERE embedding IS NULL;

    Find rows where content was updated after embedding was generated (if tracking updated_at):

    SELECT count(*) FROM unified_rag_content
    WHERE updated_at > embedding_generated_at
    AND embedding IS NOT NULL;

    Find rows by content type (e.g., only illustrations):

    SELECT count(*) FROM unified_rag_content
    WHERE content_type = 'illustration' AND embedding IS NULL;

  2. Locate the embedding script

    Check these locations for existing scripts:

    C:\dev\sermon-illustrations\scripts\
    C:\dev\churchwiseai-web\scripts\

    If no script exists for the needed scope, write one following the pattern below.

  3. Run embedding regeneration in batches

    Always process in batches of 100–500 rows to avoid rate limits and allow progress tracking. Do NOT use claude -p for embeddings — the OpenAI Embeddings API is a legitimate real-time infrastructure call (not batch content generation).

    Example batch approach:

    # From the script directory
    node generate-embeddings.js --content-type illustration --batch-size 100 --dry-run
    # Review output, then run without --dry-run
    node generate-embeddings.js --content-type illustration --batch-size 100

  4. Verify embeddings are stored correctly

    After regeneration, confirm the embedding column is populated with a vector of the expected dimension (1536 for text-embedding-3-small):

    SELECT id, length(embedding::text) as embedding_length
    FROM unified_rag_content
    WHERE embedding IS NOT NULL
    LIMIT 5;

  5. Test similarity search returns correct results

    Run a test similarity query to confirm the regenerated embeddings produce sensible results:

    -- Find content similar to a test embedding (run from your app's RAG endpoint)
    -- Or use the chatbot/voice agent in demo mode and verify it retrieves relevant content

    Test via the demo church (00000000-0000-4000-a000-000000000001) — ask the chatbot a question that should retrieve the regenerated content.

  6. Update the vector index if needed

    If the embedding model changed (different dimensions), drop and recreate the vector index:

    -- Check existing index
    SELECT indexname, indexdef FROM pg_indexes
    WHERE tablename = 'unified_rag_content' AND indexname LIKE '%embed%';

    -- Recreate if model changed (requires founder approval — affects 327K rows)
    -- DROP INDEX CONCURRENTLY idx_unified_rag_content_embedding;
    -- CREATE INDEX CONCURRENTLY idx_unified_rag_content_embedding
    --   ON unified_rag_content USING ivfflat (embedding vector_cosine_ops)
    --   WITH (lists = 100);

Verification​

  • SELECT count(*) FROM unified_rag_content WHERE embedding IS NULL returns 0 (or the expected count for excluded rows)
  • Chatbot RAG responses for the demo church return relevant content
  • No new errors in ops_error_reports related to embedding or similarity search

See Also​