01_vector_database_tutorial.qmd

---
title: "Vector Database Document Ingestion with PostgreSQL pgvector, Google Embeddings, and Claude Analysis in R"
format: html
execute:
  eval: false
  echo: true
editor: 
  markdown: 
    wrap: 72
---

## Table of Contents

1.  [Introduction to Vector Databases](#introduction)
2.  [Prerequisites and Setup](#prerequisites)
3.  [Understanding the Workflow](#workflow)
4.  [Database Schema Design](#schema)
5.  [Document Processing Pipeline](#processing)
6.  [Embedding Generation](#embeddings)
7.  [Data Insertion](#insertion)
8.  [Semantic Search](#search)
9.  [Complete Code Examples](#examples)
10. [Best Practices](#best-practices)
11. [Troubleshooting](#troubleshooting)

## Introduction to Vector Databases for Retrieval Augmented Generation (RAG) {#introduction}

Think of traditional database search like looking up words in a
dictionary—you need to know the exact term you're searching for. Vector
databases work more like how your brain associates ideas: they
understand that "car" and "automobile" are related concepts, even though
the words are completely different. This tutorial will show you how to
build that kind of intelligent search system using PostgreSQL's pgvector
extension.

Vector databases enable semantic search by storing high-dimensional
vector representations (embeddings) of unstructured data like text
documents. Unlike traditional keyword-based search, vector databases can
find conceptually similar content even when exact words don't match.
This makes them perfect for building systems that need to understand
meaning, not just match keywords—like chatbots that answer questions
about your documentation, or recommendation engines that find similar
content.

### Key Concepts:

-   **Embeddings**: Dense numerical representations of text that capture
    semantic meaning
-   **Vector Similarity**: Mathematical measures (cosine, euclidean) to
    find related content
-   **pgvector**: PostgreSQL extension that adds vector data types and
    operations
-   **Semantic Search**: Finding content based on meaning rather than
    exact keywords

### Use Cases:

-   Document similarity search
-   Recommendation systems
-   Question-answering systems
-   Content discovery
-   Duplicate detection

## Prerequisites and Setup {#prerequisites}

### Required R Packages:

```{r}

packages <- c(
  # Database connectivity
  "DBI",
  "RPostgreSQL",
  "duckdb",

  # API interactions and JSON handling
  "httr",
  "jsonlite",

  # Data manipulation
  "dplyr",
  "readr",
  "stringr",
  
  # Optional: Local embeddings
  "text"
)

installed <- packages %in% rownames(installed.packages())
if (any(!installed)) install.packages(packages[!installed])

invisible(lapply(packages, function(pkg) library(pkg, character.only = TRUE)))

```

### Data

#### Knowledge Base documents and patterns

This tutorial uses two types of knowledge base files in the
`knowledge_base/` directory that demonstrate different aspects of RAG
systems:

**Documents** <br /> `knowledge_base/docs/geographic_descriptions.md`:
This markdown file contains documentation about FIPS codes, RIN
communities, and geographic boundaries—the kind of unstructured text
that's perfect for semantic search. When you ask "What are RIN
communities?" the vector database will find relevant chunks from this
document based on conceptual similarity, not keyword matching. This is
the classic RAG use case: turning prose documentation into searchable
knowledge.

**Patterns** <br /> `knowledge_base/patterns/query_patterns.json`: This
JSON file maps natural language questions to structured database
queries. It shows how Homer (the CORI AI agent) needs to recognize
patterns like "Show me counties in Arizona" and translate them into SQL
filters. In a production RAG system, you'd embed both the user's
question AND these query patterns to find the best matching query
strategy. Think of it as teaching the system how to "think" about data
queries by providing examples.

Together, these files represent the two knowledge types Homer needs:
domain knowledge (what things mean) and procedural knowledge (how to
query for things). The structured data section below shows how to
execute those query patterns against actual county-level data.

#### Structured Data

The `query_patterns.json` file above isn't just documentation—it's
training data for a RAG system. In production, you'd embed those user
queries (like "Show me RIN communities in Arizona") into vectors, then
use semantic search to match new user questions to the closest pattern.
Once you find the matching pattern, you execute its associated query:

``` sql
SELECT *
FROM rin_service_areas
WHERE statefp = '04'
  AND rin_community IS NOT NULL
```

This code demonstrates what happens when you actually execute those four
query patterns against real county-level data from
`rin_service_areas.parquet`. We're using DuckDB here because it can read
Parquet files directly without loading them into R memory first. The
examples below include a critical pattern: RIN community lookup by name
using case-insensitive partial matching (`LIKE '%Dalles%'` in DuckDB,
which is case-insensitive by default). This allows "The Dalles", "the
dalles", or just "Dalles" to all match the same community.

```{r}
#| eval: false

# Load required packages
library(DBI)
library(duckdb)
library(dplyr)

# Create a DuckDB connection
con_duckdb <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")

# Read the parquet file directly into DuckDB
# DuckDB has native parquet support - no need to load into R first
parquet_path <- "data/rin_service_areas.parquet"

# Register the parquet file as a queryable table
dbExecute(con_duckdb, sprintf(
  "CREATE VIEW rin_service_areas AS SELECT * FROM read_parquet('%s')",
  parquet_path
))

# Verify table structure
dbGetQuery(con_duckdb, "DESCRIBE rin_service_areas")

# 1. Execute Query Pattern 1: RIN Community Lookup by Name
# User query: "What is the approximate location of the RIN community known as the Dalles?"
# Filter: rin_community LIKE '%Dalles%' (case-insensitive partial match)
# Note: In production, strip "the/a/an" from user input first

cat("\n=== Query 1: RIN Community Lookup (The Dalles) ===\n")
query1_result <- dbGetQuery(con_duckdb, "
  SELECT *
  FROM rin_service_areas
  WHERE rin_community LIKE '%Dalles%'
")
print(query1_result |> dplyr::select(-c(`centroid`, `geometry`)))

if (nrow(query1_result) > 0) {
  cat(sprintf("\nFound RIN community: %s (FIPS: %s)\n",
              query1_result$rin_community[1],
              query1_result$geoid_co[1]))
  cat(sprintf("Location: %s, Lat/Lon: (%.4f, %.4f)\n",
              query1_result$namelsad[1],
              query1_result$lat[1],
              query1_result$lon[1]))
}

# 2. Execute Query Pattern 2: County Lookup
# User query: "What data do you have for Hood River County?"
# Filter: geoid_co == '41027'

cat("\n=== Query 2: County Lookup (Hood River County, FIPS 41027) ===\n")
query2_result <- dbGetQuery(con_duckdb, "
  SELECT *
  FROM rin_service_areas
  WHERE geoid_co = '41027'
")
print(query2_result |> dplyr::select(-c(`centroid`, `geometry`)))

# 3. Execute Query Pattern 3: State Filter with RIN Communities
# User query: "Show me RIN communities in Arizona"
# Filter: statefp == '04' AND rin_community.notna()

cat("\n=== Query 3: RIN Communities in Arizona (statefp 04) ===\n")
query3_result <- dbGetQuery(con_duckdb, "
  SELECT *
  FROM rin_service_areas
  WHERE statefp = '04'
    AND rin_community IS NOT NULL
")
print(query3_result |> dplyr::select(-c(`centroid`, `geometry`)))

# Display summary
cat(sprintf("\nFound %d RIN communities in Arizona\n", nrow(query3_result)))

# 4. Execute Query Pattern 4: Ranking by Land Area
# User query: "What are the largest counties by land area?"
# Sort: aland DESC

cat("\n=== Query 4: Top 10 Largest Counties by Land Area ===\n")
query4_result <- dbGetQuery(con_duckdb, "
  SELECT
    geoid_co,
    namelsad as county_name,
    statefp,
    aland,
    ROUND(aland / 1000000, 2) as land_area_sq_km
  FROM rin_service_areas
  ORDER BY aland DESC
  LIMIT 10
")
print(query4_result)

# Optional: Clean up connection when done
dbDisconnect(con_duckdb, shutdown = TRUE)

```

### PostgreSQL Setup:

1.  Install PostgreSQL (version 11+)
2.  Install pgvector extension:

```{sql}
#| eval: false
-- Connect as superuser
CREATE EXTENSION vector;
```

### API Keys (Choose One):

-   **Google AI/Vertex AI API**: For high-quality embeddings via
    Google's embedding models
-   **Local Models**: Using Hugging Face transformers via R's `text`
    package

### Google AI Setup:

1.  Create a Google Cloud Project or use Google AI Studio
2.  Enable the Vertex AI API (for production) or use Google AI Studio
    (for development)
3.  Set up authentication:
    -   **Google AI Studio**: Get API key from
        https://makersuite.google.com/app/apikey
    -   **Vertex AI**: Set up service account credentials
4.  Set environment variable: `GOOGLE_API_KEY` or
    `GOOGLE_APPLICATION_CREDENTIALS`

## Understanding the Workflow {#workflow}

The document ingestion pipeline follows these steps:

```         
Raw Documents → Text Processing → Chunking → Embedding Generation → Vector Storage → Indexing
```

### 1. Document Preprocessing

-   Extract text from various formats (TXT, PDF, CSV, etc.)
-   Clean and normalize text
-   Handle encoding issues

### 2. Text Chunking

-   Split large documents into manageable pieces
-   Maintain context with overlapping chunks
-   Optimize chunk size for embedding models

### 3. Embedding Generation

-   Convert text chunks to numerical vectors
-   Use pre-trained models (OpenAI, sentence-transformers)
-   Maintain consistent dimensionality

### 4. Database Storage

-   Store original text alongside vectors
-   Create efficient vector indexes
-   Maintain metadata relationships

### 5. Search Interface

-   Generate query embeddings
-   Perform similarity calculations
-   Return ranked results with scores

## Database Schema Design {#schema}

The schema below stores three types of information: the original text
content (so you can show it to users), metadata about where it came from
(file paths, document types, timestamps), and the vector embeddings
themselves. The `embedding vector(768)` column is the magic—that's where
pgvector stores the 768-dimensional numerical representation of each
text chunk.

Why 768 dimensions? That's what Google's text-embedding-004 model
produces. Different embedding models create different dimensional
vectors (OpenAI's models use 1536, for example), so your schema needs to
match whatever model you're using. The dimensions are like coordinates
in a very high-dimensional space—documents with similar meanings end up
near each other in that space, which is how semantic search works.

### Core Documents Table:

```{sql}
#| eval: false
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    source_file TEXT,
    document_type VARCHAR(50),
    chunk_index INTEGER DEFAULT 0,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    embedding vector(768)  -- Google text-embedding-004 dimensions
);
```

### Vector Index for Performance:

```{sql}
#| eval: false
-- IVFFlat index for approximate nearest neighbor search
CREATE INDEX documents_embedding_idx
ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- HNSW index (alternative, better for high-dimensional data)
-- CREATE INDEX documents_embedding_hnsw_idx
-- ON documents USING hnsw (embedding vector_cosine_ops);
```

### examine embedding values

```{r}
con <- cori.db::connect_to_db(schema = 'public', dbname = 'pgvector-tutorial')

documents <- cori.db::read_db(con, 'documents')

DBI::dbDisconnect(con)

```

### Supporting Tables:

```{sql}
#| eval: false
-- Track document sources
CREATE TABLE document_sources (
    id SERIAL PRIMARY KEY,
    source_path TEXT UNIQUE,
    file_hash VARCHAR(64),
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Store embedding model metadata
CREATE TABLE embedding_models (
    id SERIAL PRIMARY KEY,
    model_name VARCHAR(100),
    dimensions INTEGER,
    provider VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

## Document Processing Pipeline {#processing}

Before we can create embeddings, we need to get text out of whatever
format it's stored in. This extraction function handles the most common
document types you'll encounter: plain text files, CSVs (which we
convert to text representations), and PDFs (if you have the `pdftools`
package installed). The goal is simple: turn any supported file into a
single string of text that we can then chunk and embed.

Think of this as the "loading dock" of your RAG system. You might need
to extend this function to handle Word documents, web pages scraped from
URLs, or database records—whatever sources contain the knowledge you
want to search. The key principle is that by the time you're done with
this step, everything should be plain text, ready for the next stage of
processing.

### Query Preprocessing for RIN Community Names

Before text extraction, let's add a helper function for preprocessing
user queries about RIN communities. When users ask about "The Dalles" or
"the Hood River area," we need to strip leading articles before
matching. This is critical for the partial matching pattern
(`LIKE '%keyword%'`) to work reliably.

```{r}

strip_leading_articles <- function(text) {
  # Remove common leading articles (the, a, an) - case insensitive
  # Preserves articles in the middle of the string
  cleaned <- stringr::str_replace(text, "^(?i)(the|a|an)\\s+", "")
  return(cleaned)
}

# Examples:
# strip_leading_articles("The Dalles") → "Dalles"
# strip_leading_articles("the Hood River area") → "Hood River area"
# strip_leading_articles("A New Beginning") → "New Beginning"
# strip_leading_articles("Northern Colorado") → "Northern Colorado" (no article)

```

This function is used before generating embeddings for queries or before
constructing SQL filters. For example, when matching "What data do you
have for The Dalles?", we'd extract "The Dalles", strip it to "Dalles",
then search with `rin_community ILIKE '%Dalles%'`.

### Text Extraction Function:

```{r}

extract_text <- function(file_path) {
  # Detect file type and extract accordingly
  file_ext <- tools::file_ext(tolower(file_path))

  switch(file_ext,
    "csv" = {
      df <- readr::read_csv(file_path)
      # Convert structured data to searchable text
      paste(capture.output(print(df)), collapse = "\n")
    },
    "md" = readr::read_file(file_path),
    "txt" = readr::read_file(file_path),
    "pdf" = {
      # Requires pdftools package
      if (requireNamespace("pdftools", quietly = TRUE)) {
        pdftools::pdf_text(file_path) %>% paste(collapse = "\n")
      } else {
        stop("pdftools package required for PDF processing")
      }
    },
    stop("Unsupported file type: ", file_ext)
  )
}
```

### Intelligent Text Chunking

Here's where we solve one of the core challenges in RAG systems: how do
you turn a 50-page document into searchable pieces? Embedding models
have token limits (typically 512-8192 tokens), but even when they
support longer sequences, smaller chunks work better for retrieval.
Think of it this way—if your entire document becomes a single vector,
how do you find the specific paragraph that answers a user's question?

The trick is splitting text at natural boundaries (like sentences) while
maintaining some overlap between chunks. That overlap is crucial: if an
important concept spans the chunk boundary, the overlap ensures both
neighboring chunks contain the full idea. This function uses a
1000-character default chunk size with 200 characters of overlap,
meaning the last few sentences of one chunk reappear at the start of the
next. You'll want to tune these parameters based on your
content—technical documentation might work better with smaller chunks
(300-500 chars), while narrative content can handle larger ones
(1500-2000 chars).

### Intelligent Text Chunking:

```{r}

chunk_text <- function(text, chunk_size = 500, chunk_overlap = 100) {
  # Split on sentences to maintain context
  sentences <- stringr::str_split(text, "(?<=[.!?])\\s+")[[1]]

  chunks <- list()
  current_chunk <- ""
  current_length <- 0

  for (sentence in sentences) {
    sentence_length <- nchar(sentence)

    if (current_length + sentence_length > chunk_size && current_length > 0) {
      # Save current chunk
      chunks <- append(chunks, current_chunk)

      # Start new chunk with overlap (last chunk_overlap characters)
      if (chunk_overlap > 0 && nchar(current_chunk) > chunk_overlap) {
        overlap_text <- substr(current_chunk, nchar(current_chunk) - chunk_overlap + 1, nchar(current_chunk))
        current_chunk <- paste(overlap_text, sentence)
      } else {
        current_chunk <- sentence
      }
      current_length <- nchar(current_chunk)
    } else {
      # Add sentence to current chunk
      if (nchar(current_chunk) > 0) {
        current_chunk <- paste(current_chunk, sentence)
      } else {
        current_chunk <- sentence
      }
      current_length <- nchar(current_chunk)
    }
  }

  # Add final chunk
  if (nchar(current_chunk) > 0) {
    chunks <- append(chunks, current_chunk)
  }

  return(unlist(chunks))
}
```

## Embedding Generation {#embeddings}

This is where the real magic happens. An embedding is a dense numerical
vector that captures the semantic meaning of text—think of it as
translating words into a coordinate system where similar concepts
cluster together in space. When you embed "vehicle" and "automobile,"
they end up very close to each other in this high-dimensional space,
even though they share no letters. When you embed "vehicle" and
"broccoli," they're far apart. That's how semantic search works.

The function below uses Google's text-embedding-004 model, which
produces 768-dimensional vectors. It handles batching (processing 100
texts at a time to respect API rate limits), includes fallback logic if
the API fails, and validates that each embedding has the expected 768
dimensions. The `Sys.sleep(0.1)` call prevents you from hitting rate
limits—you can adjust this based on your API tier. In production, you
might cache embeddings so you don't need to regenerate them every time
you restart your application.

### Google Gemini API for Embeddings Generation

```{r}

# General-purpose Gemini API call function
gemini_api_call <- function(prompt,
                             model = "gemini-2.5-flash",
                            #model = "gemini-2.5-pro",
                             max_tokens = 1000,
                             google_api_key = Sys.getenv("GOOGLE_API_KEY")) {

  # Validate API key
  if (google_api_key == "") {
    stop("GOOGLE_API_KEY environment variable not set")
  }

  # Build the API URL with the API key as query parameter
  url <- sprintf(
    "https://generativelanguage.googleapis.com/v1beta/models/%s:generateContent?key=%s",
    model,
    google_api_key
  )

  response <- httr::POST(
    url = url,
    httr::add_headers("Content-Type" = "application/json"),
    body = jsonlite::toJSON(list(
      contents = list(list(
        parts = list(list(
          text = prompt
        ))
      )),
      generationConfig = list(
        maxOutputTokens = max_tokens
      )
    ), auto_unbox = TRUE)
  )

  if (httr::status_code(response) == 200) {
    result <- jsonlite::fromJSON(httr::content(response, "text"))
    message("Gemini API request successful")

    if (!is.null(result$candidates) && length(result$candidates) > 0 && !is.null(result$candidates[[1]])) {

      # Extract text from Gemini response structure
      content <- result$candidates[[1]]
      if (!is.null(content$parts) && !is.null(content$parts[[1]]) && !is.null(content$parts[[1]]$text)) {
        return(content$parts[[1]]$text)
      }

    } else {

      # Fallback if structure is unexpected
      warning("Unexpected Gemini API response structure")
      return(result)

    }

  } else {
    status_code <- httr::status_code(response)
    error_content <- httr::content(response, "text")
    warning(paste("Gemini API error - Status:", status_code, "Response:", error_content))

    # Check specific error cases
    if (status_code == 401 || status_code == 403) {
      warning("Authentication failed - check your GOOGLE_API_KEY")
    } else if (status_code == 400) {
      warning("Bad request - check API request format")
    } else if (status_code == 429) {
      warning("Rate limit exceeded - too many requests")
    } else if (status_code == 500) {
      warning("Internal server error - try again later")
    }

    stop("API call failed - see warnings above")
  }
}

generate_google_embeddings <- function(texts, api_key = Sys.getenv("GOOGLE_API_KEY")) {
  embeddings <- list()
  batch_size <- 100  # API rate limiting

  # Check if API key is available
  if (api_key == "" || is.na(api_key)) {
    warning("Google API key not found, using fallback random embeddings")
    # Generate random embeddings for testing (768 dimensions for text-embedding-004)
    return(lapply(texts, function(x) runif(768, -1, 1)))
  }

  for (i in seq(1, length(texts), batch_size)) {
    batch_end <- min(i + batch_size - 1, length(texts))
    batch_texts <- texts[i:batch_end]
    message(paste("Processing embedding batch", ceiling(i/batch_size), "with", length(batch_texts), "texts"))

    # Prepare requests for batch processing
    requests <- lapply(batch_texts, function(text) {
      list(
        model = "models/text-embedding-004",
        content = list(parts = list(list(text = text)))
      )
    })

    response <- httr::POST(
      url = paste0("https://generativelanguage.googleapis.com/v1beta/models/text-embedding-004:batchEmbedContents?key=", api_key),
      httr::add_headers("Content-Type" = "application/json"),
      body = jsonlite::toJSON(list(requests = requests), auto_unbox = TRUE),
      encode = "raw"
    )

    if (httr::status_code(response) == 200) {
      # Parse with simplifyVector = FALSE to preserve list structure
      result <- jsonlite::fromJSON(httr::content(response, "text"), simplifyVector = FALSE)
      message(paste("Successfully got embeddings for", length(result$embeddings), "texts"))

      # Validate and extract embeddings
      batch_embeddings <- lapply(result$embeddings, function(x) {
        if (is.null(x$values) || length(x$values) == 0) {
          warning("Empty embedding received, using fallback")
          return(runif(768, -1, 1))  # Fallback random embedding
        }
        # Convert list of numbers to numeric vector
        values <- unlist(x$values)
        if (length(values) != 768) {
          warning(paste("Unexpected embedding dimension:", length(values), "- using fallback"))
          return(runif(768, -1, 1))
        }
        return(values)
      })

      embeddings <- c(embeddings, batch_embeddings)
    } else {
      error_msg <- httr::content(response, "text")
      warning(paste("Google API Error:", error_msg, "- Using fallback embeddings"))
      # Generate fallback random embeddings for this batch
      batch_embeddings <- lapply(batch_texts, function(x) runif(768, -1, 1))
      embeddings <- c(embeddings, batch_embeddings)
    }

    Sys.sleep(0.1)  # Rate limiting
  }

  message(paste("Generated", length(embeddings), "total embeddings"))
  return(embeddings)
}

# Alternative: Vertex AI approach (for production use)
generate_vertex_embeddings <- function(texts, project_id = Sys.getenv("GOOGLE_CLOUD_PROJECT")) {
  # Requires googleCloudVertexAIR package or REST API calls
  # This is a simplified example - you'd need proper authentication setup

  embeddings <- list()

  for (text in texts) {
    # Vertex AI REST API call would go here
    # Using text-embedding-004 model through Vertex AI
    # Returns 768-dimensional vectors
  }

  return(embeddings)
}

```

### Local Embedding Models:

```{r}

generate_local_embeddings <- function(texts) {
  # Using sentence-transformers via text package
  # Requires Python environment with sentence-transformers

  # Initialize model (first run downloads model)
  embeddings <- text::textEmbed(
    texts = texts,
    model = "sentence-transformers/all-MiniLM-L6-v2",
    device = "cpu"  # Use "cuda" for GPU acceleration
  )

  # Extract embedding vectors
  return(embeddings$texts$texts)
}
```

### Claude API Integration for Document Analysis:

```{r}

# General-purpose Anthropic API call function
claude_api_call <- function(prompt,
                                model = "claude-sonnet-4-20250514",
                                max_tokens = 1000,
                                claude_api_key = Sys.getenv("ANTHROPIC_API_KEY")) {

  # Validate API key
  if (claude_api_key == "") {
    stop("ANTHROPIC_API_KEY environment variable not set")
  }

  response <- httr::POST(
    url = "https://api.anthropic.com/v1/messages",
    httr::add_headers(
      "x-api-key" = claude_api_key,
      "Content-Type" = "application/json",
      "anthropic-version" = "2023-06-01"
    ),
    body = jsonlite::toJSON(list(
      model = model,
      max_tokens = max_tokens,
      messages = list(list(
        role = "user",
        content = prompt
      ))
    ), auto_unbox = TRUE)
  )

  if (httr::status_code(response) == 200) {
    result <- jsonlite::fromJSON(httr::content(response, "text"))
    message("Claude API request successful")

    # Handle the data.frame structure of content
    if (is.data.frame(result$content)) {
      return(result$content$text[1])
    } else {
      # Fallback for old API structure
      return(result$content[[1]]$text)
    }
  } else {
    status_code <- httr::status_code(response)
    error_content <- httr::content(response, "text")
    warning(paste("Claude API error - Status:", status_code, "Response:", error_content))

    # Check specific error cases
    if (status_code == 401) {
      warning("Authentication failed - check your ANTHROPIC_API_KEY")
    } else if (status_code == 400) {
      warning("Bad request - check API request format")
    } else if (status_code == 429) {
      warning("Rate limit exceeded - too many requests")
    } else if (status_code == 500) {
      warning("Internal server error - try again later")
    }

    stop("API call failed - see warnings above")
  }
}

# Enhanced document processing with Claude for intelligent analysis
enhance_document_with_claude <- function(text, claude_api_key = Sys.getenv("ANTHROPIC_API_KEY")) {
  # Use Claude for document analysis and enhancement
  prompt <- paste(
    "Analyze this document and provide:",
    "1. A concise summary (2-3 sentences)",
    "2. Key topics and themes",
    "3. Important entities (people, places, organizations)",
    "4. Suggested metadata tags",
    "Document text:", text,
    sep = "\n"
  )

  response <- httr::POST(
    url = "https://api.anthropic.com/v1/messages",
    httr::add_headers(
      "x-api-key" = claude_api_key,
      "Content-Type" = "application/json",
      "anthropic-version" = "2023-06-01"
    ),
    body = jsonlite::toJSON(list(
      model = "claude-sonnet-4-20250514",
      max_tokens = 1000,
      messages = list(list(
        role = "user",
        content = prompt
      ))
    ), auto_unbox = TRUE)
  )

  if (httr::status_code(response) == 200) {
    result <- jsonlite::fromJSON(httr::content(response, "text"))
    message("Claude API request successful")
    # Handle the data.frame structure of content
    if (is.data.frame(result$content)) {
      return(result$content$text[1])
    } else {
      # Fallback for old API structure
      return(result$content[[1]]$text)
    }
  } else {
    status_code <- httr::status_code(response)
    error_content <- httr::content(response, "text")
    warning(paste("Claude API error - Status:", status_code, "Response:", error_content))

    # Check specific error cases
    if (status_code == 401) {
      warning("Authentication failed - check your ANTHROPIC_API_KEY")
    } else if (status_code == 400) {
      warning("Bad request - check API request format")
    } else if (status_code == 429) {
      warning("Rate limit exceeded - too many requests")
    }

    return("Analysis unavailable - API connection failed")
  }
}
```

### Embedding Quality Considerations:

-   **Model Selection**: Google's text-embedding-004 is general-purpose
    and high-quality
-   **Dimensionality**: 768 dimensions provide excellent
    precision-performance balance
-   **Consistency**: Always use same model for indexing and querying
-   **Normalization**: Google embeddings are pre-normalized for cosine
    similarity
-   **Multilingual**: Use textembedding-gecko-multilingual for
    non-English content
-   **Claude Enhancement**: Use Claude for intelligent document
    preprocessing and metadata extraction

## Data Insertion {#insertion}

Now we need to get all those embeddings into PostgreSQL efficiently.
Inserting one row at a time would be painfully slow—imagine inserting
10,000 document chunks with 10,000 separate database round trips. Batch
insertion solves this by grouping multiple inserts into a single SQL
statement, dramatically reducing network overhead and transaction costs.

The function below processes documents in batches of 100, converting
each embedding vector to PostgreSQL's vector format (a string like
`[0.123, -0.456, 0.789, ...]`). The parameterized query approach (`$1`,
`$2`, etc.) prevents SQL injection and handles special characters in
your text content. If an embedding somehow ends up empty or malformed,
the fallback logic generates a random vector so the insertion doesn't
fail—you'd want better error handling in production, but this keeps your
pipeline running during development.

**Metadata handling**: The function automatically detects if your data
frame includes a `metadata` column and inserts it as JSONB if present.
This is essential for Example 2 (Query Pattern Matching) where we store
SQL filters and sorting logic in metadata for later retrieval.

### Batch Insertion Strategy:

```{r}

# Clear existing documents (useful during development/testing)
clear_documents <- function(con) {
  DBI::dbExecute(con, "DELETE FROM documents")
  message("Cleared all documents from database")
}

insert_documents_batch <- function(documents_df, embeddings_list, con, batch_size = 100) {
  # Prepare batch insertion for better performance

  for (i in seq(1, nrow(documents_df), batch_size)) {
    batch_end <- min(i + batch_size - 1, nrow(documents_df))
    batch_docs <- documents_df[i:batch_end, ]
    batch_embeddings <- embeddings_list[i:batch_end]

    # Check if metadata column exists (once per batch)
    has_metadata <- "metadata" %in% names(batch_docs)

    # Build VALUES clause for batch insert
    values_parts <- c()
    params <- list()
    param_idx <- 1

    for (j in 1:nrow(batch_docs)) {
      # Validate embedding before converting
      embedding <- batch_embeddings[[j]]
      if (is.null(embedding) || length(embedding) == 0) {
        warning(paste("Empty embedding for row", j, "using fallback"))
        embedding <- runif(768, -1, 1)  # Fallback random embedding
      }

      # Convert embedding to PostgreSQL vector format
      embedding_str <- paste0("[", paste(embedding, collapse = ","), "]")

      # Additional validation for vector string
      if (embedding_str == "[]") {
        warning(paste("Still empty vector for row", j, "after fallback"))
        embedding_str <- paste0("[", paste(runif(768, -1, 1), collapse = ","), "]")
      }

      if (has_metadata) {
        values_parts <- c(values_parts, sprintf("($%d, $%d, $%d, $%d::jsonb, $%d::vector)",
                                              param_idx, param_idx+1, param_idx+2, param_idx+3, param_idx+4))

        params <- c(params, list(
          batch_docs$title[j],
          batch_docs$content[j],
          batch_docs$source_file[j],
          batch_docs$metadata[j],
          embedding_str
        ))
        param_idx <- param_idx + 5
      } else {
        values_parts <- c(values_parts, sprintf("($%d, $%d, $%d, $%d::vector)",
                                              param_idx, param_idx+1, param_idx+2, param_idx+3))

        params <- c(params, list(
          batch_docs$title[j],
          batch_docs$content[j],
          batch_docs$source_file[j],
          embedding_str
        ))
        param_idx <- param_idx + 4
      }
    }

    # Build INSERT statement based on whether metadata is present
    if (has_metadata) {
      insert_sql <- sprintf(
        "INSERT INTO documents (title, content, source_file, metadata, embedding) VALUES %s",
        paste(values_parts, collapse = ", ")
      )
    } else {
      insert_sql <- sprintf(
        "INSERT INTO documents (title, content, source_file, embedding) VALUES %s",
        paste(values_parts, collapse = ", ")
      )
    }

    DBI::dbExecute(con, insert_sql, params)
    cat("Inserted batch", ceiling(i/batch_size), "of", ceiling(nrow(documents_df)/batch_size))
    if (has_metadata) cat(" (with metadata)")
    cat("\n")
  }
}
```

## Semantic Search {#search}

Finally, we get to use all that infrastructure we've built! Semantic
search works by converting your query into an embedding (using the same
model you used for indexing), then finding documents whose embeddings
are closest to it in vector space. The `<=>` operator is pgvector's
cosine distance operator—it measures the angle between two vectors,
where 0 means identical and 2 means completely opposite. We convert this
to a similarity score with `1 - (embedding <=> query)` so higher scores
mean better matches.

The `threshold` parameter (default 0.33) filters out weak matches—only
documents with similarity above 20% are returned. You'll want to tune
this based on your use case: set it too high and you might miss relevant
results, too low and you'll get noise. The `limit` parameter caps how
many results you get back, which is especially important for RAG systems
where you're feeding results into an LLM with context window limits.
Five to ten results is usually a sweet spot for providing enough context
without overwhelming the model.

### Basic Similarity Search:

```{r}

semantic_search <- function(query, con, limit = 10, threshold = 0.33) {
  # Generate query embedding using Google's API
  query_embedding <- generate_google_embeddings(query)[[1]]
  query_vector <- paste0("[", paste(query_embedding, collapse = ","), "]")

  # Perform cosine similarity search
  search_sql <- "
    SELECT
      id,
      title,
      content,
      source_file,
      metadata,
      1 - (embedding <=> $1::vector) as similarity_score
    FROM documents
    WHERE 1 - (embedding <=> $1::vector) > $2
    ORDER BY similarity_score DESC
    LIMIT $3
  "

  results <- DBI::dbGetQuery(con, search_sql, list(query_vector, threshold, limit))
  return(results)
}
```

### Advanced Search with Filters

In production, you'll often want to combine semantic search with
traditional filtering—for example, "find documents similar to this
query, but only from the last month" or "search within policy documents,
not technical docs." This function shows how to build dynamic SQL
queries that layer vector similarity search on top of metadata filters.
The key technique is building the WHERE clause incrementally based on
which parameters are provided.

This hybrid approach is incredibly powerful: you get the semantic
understanding of vector search combined with the precision of structured
filters. It's especially useful for Homer's use case—you might want to
find documents semantically similar to "economic growth" but filter to
only RIN communities in the Pacific Northwest, or only counties with
population under 50,000.

```{r}

filtered_semantic_search <- function(query, con, document_types = NULL,
                                   date_range = NULL, limit = 10) {
  query_embedding <- generate_google_embeddings(query)[[1]]
  query_vector <- paste0("[", paste(query_embedding, collapse = ","), "]")

  # Build dynamic WHERE clause
  where_conditions <- c("1 = 1")
  params <- list(query_vector)
  param_idx <- 2

  if (!is.null(document_types)) {
    where_conditions <- c(where_conditions, sprintf("document_type = ANY($%d)", param_idx))
    params <- c(params, list(document_types))
    param_idx <- param_idx + 1
  }

  if (!is.null(date_range)) {
    where_conditions <- c(where_conditions,
                         sprintf("created_at BETWEEN $%d AND $%d", param_idx, param_idx + 1))
    params <- c(params, as.list(date_range))
    param_idx <- param_idx + 2
  }

  search_sql <- sprintf("
    SELECT
      id, title, content, source_file, document_type, created_at,
      1 - (embedding <=> $1::vector) as similarity_score
    FROM documents
    WHERE %s
    ORDER BY embedding <=> $1::vector ASC
    LIMIT $%d
  ", paste(where_conditions, collapse = " AND "), param_idx)

  params <- c(params, list(limit))

  results <- DBI::dbGetQuery(con, search_sql, params)
  return(results)
}
```

### Retrieval-Augmented Generation (RAG) with Claude

This is where semantic search becomes truly powerful: we're not just
finding relevant documents, we're using them to augment an LLM's
response. The RAG pattern works by (1) using vector search to find
relevant context, (2) combining that context with the user's question,
and (3) sending the enhanced prompt to Claude for a knowledge-grounded
response. This solves the LLM hallucination problem—instead of
generating answers from nothing, Claude bases its response on retrieved
information you control.

The `rag_search()` function below implements Homer's complete
architecture, handling **both unstructured and structured queries**:

-   **Unstructured queries**: Performs semantic search on document
    chunks and sends results to Claude
-   **Structured queries (with patterns)**: Detects query patterns with
    metadata, executes SQL from pattern, sends structured data to Claude
-   **Structured queries (entity extraction)**: Uses Claude to extract
    entities (e.g., "The Dalles"), generates SQL query, retrieves
    records
-   **Hybrid approach**: Combines document chunks + structured data into
    a single context for comprehensive answers

**How it works**: 1. Always performs semantic search for relevant
documents 2. If metadata found → use SQL filter from query pattern 3. If
no metadata but `parquet_path` provided → extract entity and generate
SQL (e.g., `rin_community ILIKE '%Dalles%'`) 4. Builds context from
documents + structured data (not either/or!) 5. Sends hybrid context to
Claude for synthesis

**Key parameters**: - `con`: Database connection (optional, creates one
if NULL) - `parquet_path`: Path to Parquet file (enables structured
query capability) - `limit`, `threshold`: Control semantic search
results - Returns: List with `query`, `found_documents`,
`structured_data`, `response`, and `search_results`

```{r}

# Retrieval-Augmented Generation (RAG) with Claude
rag_search <- function(query,
                      con = NULL,
                      parquet_path = NULL,
                      limit = 5,
                      threshold = 0.1) {

  # Manage database connection
  if (is.null(con)) {
    con <- cori.db::connect_to_db(dbname = "pgvector-tutorial", schema = "public")
    on.exit(DBI::dbDisconnect(con))
  }

  # 1. Perform semantic search to find relevant documents/patterns
  search_results <- semantic_search(query, con, limit = limit, threshold = threshold)

  if (nrow(search_results) == 0) {
    return(list(
      query = query,
      found_documents = 0,
      # structured_data = NULL,
      response = "I couldn't find any relevant information to answer your question."
    ))
  }

#   # 2. Check if top result has metadata (indicating a query pattern)
#   structured_data <- NULL
#   if (!is.na(search_results$metadata[1]) && !is.null(parquet_path)) {
#     # This is a query pattern match - execute structured query
#     tryCatch({
#       matched_metadata <- jsonlite::fromJSON(search_results$metadata[1])

#       if (!is.null(matched_metadata$parquet_filter)) {
#         # Execute DuckDB query
#         library(duckdb)
#         con_duckdb <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")
#         on.exit(dbDisconnect(con_duckdb, shutdown = TRUE), add = TRUE)

#         dbExecute(con_duckdb, sprintf(
#           "CREATE VIEW data_view AS SELECT * FROM read_parquet('%s')",
#           parquet_path
#         ))

#         # Select only relevant columns (exclude large BLOB columns like geometry/centroid)
#         query_sql <- sprintf("
#           SELECT geoid_co, rin_community, county, namelsad, statefp,
#                  lat, lon, aland, awater, intptlat, intptlon
#           FROM data_view WHERE %s", matched_metadata$parquet_filter)
#         structured_data <- dbGetQuery(con_duckdb, query_sql)

#         cat(sprintf("\nExecuted structured query, found %d records\n", nrow(structured_data)))
#       }
#     }, error = function(e) {
#       warning(paste("Could not execute structured query:", e$message))
#     })
#   }

#   # 2b. If no query pattern but parquet_path provided, try to extract entity from query
#   if (is.null(structured_data) && !is.null(parquet_path)) {
#     tryCatch({
#       # Use Claude to extract RIN community name from the query
#       extraction_prompt <- sprintf("
# Extract the RIN community name from this question. Return ONLY the community name, nothing else.
# If no community name is found, return 'NONE'.

# Question: %s

# Community name:", query)

#       print(extraction_prompt)

#       community_name <- claude_api_call(
#         prompt = extraction_prompt,
#         model = "claude-sonnet-4-20250514",
#         max_tokens = 50,
#         claude_api_key = claude_api_key
#       )

#       community_name <- trimws(community_name)

#       if (community_name != "NONE" && nchar(community_name) > 0) {
#         # Execute DuckDB query with extracted name
#         library(duckdb)
#         con_duckdb <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")
#         on.exit(dbDisconnect(con_duckdb, shutdown = TRUE), add = TRUE)

#         dbExecute(con_duckdb, sprintf(
#           "CREATE VIEW data_view AS SELECT * FROM read_parquet('%s')",
#           parquet_path
#         ))

#         # Remove common articles
#         search_term <- gsub("^(the|a|an)\\s+", "", community_name, ignore.case = TRUE)

#         # Select only relevant columns (exclude large BLOB columns like geometry/centroid)
#         query_sql <- sprintf("
#           SELECT geoid_co, rin_community, county, namelsad, statefp,
#                  lat, lon, aland, awater, intptlat, intptlon
#           FROM data_view WHERE rin_community ILIKE '%%%s%%'", search_term)
#         structured_data <- dbGetQuery(con_duckdb, query_sql)

#         cat(sprintf("\nExtracted '%s', found %d matching records\n", search_term, nrow(structured_data)))
#       }
#     }, error = function(e) {
#       warning(paste("Could not extract entity or query data:", e$message))
#     })
#   }

  # 3. Build context for LLM (HYBRID: documents + structured data)
  context_parts <- c()

  # Always include document context from semantic search
  if (nrow(search_results) > 0) {
    doc_context <- paste(
      paste("Document", seq_len(nrow(search_results)), ":",
            search_results$content,
            sprintf("(Similarity: %.3f)", search_results$similarity_score)),
      collapse = "\n\n"
    )
    context_parts <- c(context_parts, paste("=== Relevant Documentation ===\n", doc_context))
  }

  # # Add structured data if available
  # if (!is.null(structured_data) && nrow(structured_data) > 0) {
  #   data_context <- paste(
  #     sprintf("=== Structured Data Query Results (%d records) ===\n", nrow(structured_data)),
  #     paste(capture.output(print(head(structured_data, 10))), collapse = "\n")
  #   )
  #   context_parts <- c(context_parts, data_context)
  # }

  context <- paste(context_parts, collapse = "\n\n")

  # 4. Create enhanced prompt
  enhanced_prompt <- sprintf("
User Question: %s

Relevant Information Retrieved:
%s

Please provide a clear, accurate response to the user's question based on the information above.
Be specific and cite relevant details from the data.
", query, context)

  # 5. Get LLM response
  # claude_response <- tryCatch({
  #   claude_api_call(
  #     prompt = enhanced_prompt,
  #     model = "claude-sonnet-4-20250514",
  #     max_tokens = 2000
  #   )
  # }, error = function(e) {
  #   paste("Error getting LLM response:", e$message, "\n\nRaw data:\n", context)
  # })

  # 5.b Get Gemini response
  gemini_response <- tryCatch({
    gemini_api_call(
      prompt = enhanced_prompt,
      model = "gemini-2.5-pro"
    )
  }, error = function(e) {
    paste("Error getting LLM response:", e$message, "\n\nRaw data:\n", context)
  })
  
  # responses <- c(claude_response, gemini_response)
  # names(responses) <- c('claude', 'gemini')
  
  # 6. Return comprehensive results
  return(list(
    query = query,
    found_documents = nrow(search_results),
    similarity_scores = search_results$similarity_score,
    source_files = unique(search_results$source_file),
    # structured_data = structured_data,
    response = gemini_response,
    search_results = search_results
  ))
}
```

## Complete Code Examples {#examples}

### Example 1: Query Pattern Matching

This is where the tutorial delivers on its promise: using vector search
to match user questions to query patterns, even when the wording
differs. We'll embed the query patterns from `query_patterns.json`, then
show how semantic search recognizes that "What Arizona RIN sites exist?"
should execute the same SQL as "Show me RIN communities in Arizona."

**Important**: If you see `<NA>` in the metadata column when you print
results, it means the patterns were inserted before the metadata
insertion fix was applied. Simply re-run this entire example (starting
from the database clear step) to properly insert metadata.

The workflow is: (1) embed all query patterns into the vector database,
(2) when a user asks a question, find the most similar pattern, (3)
extract that pattern's SQL filter from the original JSON, and (4)
execute it against the structured data. This is how Homer translates
natural language into precise database queries.

```{r}
#| eval: false

# 1. Load and embed query patterns
library(jsonlite)
query_patterns <- jsonlite::fromJSON("knowledge_base/patterns/query_patterns.json")

# Note: parquet_filter must use SQL syntax (DuckDB)
# Example: "column IS NOT NULL"

# Create documents from query patterns
pattern_docs <- data.frame(
  title = paste("Query Pattern:", query_patterns$query_type),
  content = query_patterns$user_query,
  source_file = "knowledge_base/patterns/query_patterns.json",
  # Store the filter/sort logic as JSONB metadata for later retrieval
  metadata = sapply(1:nrow(query_patterns), function(i) {
    jsonlite::toJSON(list(
      query_type = query_patterns$query_type[i],
      parquet_filter = query_patterns$parquet_filter[i],
      parquet_sort = if (!is.null(query_patterns$parquet_sort[i])) query_patterns$parquet_sort[i] else NA
    ), auto_unbox = TRUE)
  }),
  stringsAsFactors = FALSE
)

con <- cori.db::connect_to_db(dbname = "pgvector-tutorial", schema = "public")

# Clear existing documents (this example can run standalone)
clear_documents(con)

# Generate embeddings and insert patterns
pattern_embeddings <- generate_google_embeddings(pattern_docs$content)
insert_documents_batch(pattern_docs, pattern_embeddings, con)

# 2. Now test with a user question that's worded differently
user_question <- "What Arizona RIN sites exist?"

# Find the most similar query pattern
results <- semantic_search(user_question, con, limit = 1, threshold = 0.5)

# Check if we found any results
if (nrow(results) == 0) {
  stop("No matching query patterns found. Try lowering the threshold or adding more patterns.")
}

print(results[c("content", "similarity_score", "metadata")])

# 3. Extract the SQL filter from the matched pattern's metadata
if (is.na(results$metadata[1]) || is.null(results$metadata[1])) {
  stop("Metadata is missing for the matched pattern. Ensure patterns were inserted with metadata.")
}

matched_metadata <- jsonlite::fromJSON(results$metadata[1])
cat("\nMatched query type:", matched_metadata$query_type, "\n")
cat("SQL filter:", matched_metadata$parquet_filter, "\n")

# 4. Execute the filter against structured data using DuckDB
library(duckdb)
con_duckdb <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")
dbExecute(con_duckdb, sprintf(
  "CREATE VIEW rin_service_areas AS SELECT * FROM read_parquet('%s')",
  "data/rin_service_areas.parquet"
))

# Build query from the matched pattern's filter
query_sql <- sprintf("
  SELECT *
  FROM rin_service_areas
  WHERE %s
", matched_metadata$parquet_filter)

query_result <- dbGetQuery(con_duckdb, query_sql)
cat(sprintf("\nFound %d counties matching the pattern\n", nrow(query_result)))
print(query_result |> dplyr::select(geoid_co, namelsad, rin_community))

# 5. Format the structured data results for the LLM
structured_data_context <- if (nrow(query_result) > 0) {
  # Convert results to a readable format
  result_summary <- paste(
    sprintf("Found %d RIN sites in Arizona:", nrow(query_result)),
    "\n\n",
    paste(
      sapply(1:nrow(query_result), function(i) {
        sprintf(
          "- %s (County GEOID: %s, RIN Community: %s)",
          query_result$namelsad[i],
          query_result$geoid_co[i],
          query_result$rin_community[i]
        )
      }),
      collapse = "\n"
    )
  )
  result_summary
} else {
  "No RIN sites found matching the query criteria."
}

# 6. Construct the prompt for the LLM
llm_prompt <- sprintf("
User Question: %s

Relevant Structured Data Retrieved:
%s

Please provide a natural language response to the user's question based on the structured data above.
Include specific details about the RIN sites found, and format your response in a clear, conversational manner.
", user_question, structured_data_context)

cat("\n=== LLM Prompt ===\n")
cat(llm_prompt)
cat("\n==================\n")

# 7. Send to LLM for natural language response
llm_response <- tryCatch({
  claude_api_call(
    prompt = llm_prompt,
    model = "claude-sonnet-4-20250514",
    max_tokens = 1000
  )
}, error = function(e) {
  cat("Note: LLM API call failed or not configured:", e$message, "\n")
  structured_data_context
})

cat("\n=== Final Response ===\n")
cat(llm_response)
cat("\n======================\n")

dbDisconnect(con_duckdb, shutdown = TRUE)
dbDisconnect(con)
```

This example demonstrates the complete query pattern matching pipeline:

1.  **Semantic matching**: User asks "What Arizona RIN sites exist?" →
    Vector search finds the similar pattern "Show me RIN communities in
    Arizona"
2.  **Metadata extraction**: Extract the SQL filter from the matched
    pattern's metadata
3.  **Structured query**: Execute
    `statefp = '04' AND rin_community IS NOT NULL` against the Parquet
    file
4.  **Data formatting**: Convert query results into readable context for
    the LLM
5.  **LLM response**: Send structured data to Claude for a natural
    language answer

This is the core of Homer's architecture: natural language → semantic
pattern matching → structured SQL → data retrieval → LLM synthesis →
conversational response. The vector database acts as the "intent
recognition" layer, translating user questions into precise database
operations.

**Important**: 
Query patterns must use **SQL syntax**: 
- ✅`IS NOT NULL` (SQL)
- ✅ `ILIKE '%pattern%'` (PostgreSQL/DuckDB)
- ✅ `column = 'value'` (standard SQL)
— ⚠️ `column == 'value'` (works in DuckDB but non-standard)

### Example 2: Hybrid RAG with Documents + Structured Data

This example demonstrates the **complete Homer workflow**: combining
unstructured documentation (semantic search) with structured county data
(SQL query) to answer a single question. When you ask "What is the
approximate location of the RIN community known as The Dalles?", the
system:

1.  **Semantic search**: Finds relevant documentation chunks about
    geographic data and RIN communities
2.  **Entity extraction**: Uses Claude to extract "The Dalles" from the
    question
3.  **SQL execution**: Queries the Parquet file for The Dalles county
    record (ILIKE '%Dalles%')
4.  **Hybrid context**: Combines documentation + structured data into a
    single prompt
5.  **LLM synthesis**: Claude answers using both information sources

This is the real power of Homer: it doesn't choose between documents OR
data—it uses **both** to give comprehensive, accurate answers.

**Important**: We call `clear_documents(con)` at the start to remove any
stale data from previous runs. During development, you may run this code
multiple times, and without clearing, you'll accumulate duplicate
documents in the database. This `clear_documents` implementation is not
a feasible strategy for production—see Example 3 for incremental
updates.

```{r}
#| eval: false

# 1. Connect to database
con <- cori.db::connect_to_db(dbname = "pgvector-tutorial", schema = "public")

# 2. Clear existing documents (remove stale data from previous runs)
clear_documents(con)

# 3. Process documents with Claude enhancement
# file_paths <- c("knowledge_base/docs/geographic_descriptions.md")
file_paths <- c("knowledge_base/docs/Private Funding Draft.md")
documents <- data.frame()

for (file_path in file_paths) {
  text <- extract_text(file_path)
  message(paste("Processing:", file_path))

  # Chunk the text
  chunks <- chunk_text(text, chunk_size = 500, chunk_overlap = 50)
  message(paste("Generated", length(chunks), "chunks"))

  file_docs <- data.frame(
    title = paste(basename(file_path), "- Chunk", seq_along(chunks)),
    content = chunks,
    source_file = file_path,
    stringsAsFactors = FALSE
  )

  message(paste("Created data.frame with", nrow(file_docs), "rows"))
  documents <- rbind(documents, file_docs)
}

# 4. Generate embeddings with Google's API and insert
embeddings <- generate_google_embeddings(documents$content)
insert_documents_batch(documents, embeddings, con)

# 5. Semantic search with enhanced results
results <- semantic_search("What is the state of private funding in Rural America?", con, limit = 5, threshold = 0.5)
print(results[c("title", "similarity_score")])

# 6. RAG-powered question answering (hybrid: documents + structured data)
rag_result <- rag_search(
"What is the state of private funding in Rural America?", con, limit = 5, threshold = 0.5
)

cat("\n=== RAG Search Results ===\n")
cat("Query:", rag_result$query, "\n")
cat("Found", rag_result$found_documents, "relevant documents\n")
cat("Sources:", paste(rag_result$source_files, collapse = ", "), "\n")
# if (!is.null(rag_result$structured_data)) {
#   cat("Structured data records:", nrow(rag_result$structured_data), "\n")
# }
cat("\n=== LLM Response ===\n")
cat(rag_result$response)
cat("\n=========================\n")

# Cleanup
dbDisconnect(con)
```

### Example 3: Incremental Updates

As your knowledge base grows, you'll need to add new documents without
reprocessing everything from scratch. This function demonstrates the
incremental update pattern: check if a file has already been processed
(by querying for existing records with that source path), and if not,
extract, chunk, embed, and insert it. This is much more efficient than
rebuilding the entire index every time you add documentation.

In a production system, you might also want to handle updates to
existing files (detect changes with file hashes, delete old chunks, and
insert new ones) and implement a background job that periodically scans
your document directories for new files. For now, this gives you the
foundation for keeping your vector database in sync with your evolving
knowledge base.

```{r}
#| eval: false

update_document_index <- function(new_file_path, con) {
  # Check if file already processed
  existing <- DBI::dbGetQuery(con,
    "SELECT COUNT(*) as count FROM documents WHERE source_file = $1",
    list(new_file_path))

  if (existing$count > 0) {
    cat("File already processed:", new_file_path, "\n")
    return()
  }

  # Process new file
  text <- extract_text(new_file_path)
  chunks <- chunk_text(text)

  new_docs <- data.frame(
    title = paste(basename(new_file_path), "- Chunk", seq_along(chunks)),
    content = chunks,
    source_file = new_file_path
  )

  # Generate embeddings and insert
  embeddings <- generate_google_embeddings(new_docs$content)
  insert_documents_batch(new_docs, embeddings, con)

  cat("Added", nrow(new_docs), "chunks from", new_file_path, "\n")
}
```

## Best Practices {#best-practices}

### Performance Optimization:

1.  **Batch Processing**: Insert multiple documents in single
    transactions
2.  **Index Strategy**: Use appropriate vector index (IVFFlat vs HNSW)
3.  **Connection Pooling**: Reuse database connections
4.  **Embedding Caching**: Store embeddings to avoid regeneration

### Data Quality:

1.  **Text Preprocessing**: Clean text before embedding generation
2.  **Chunk Optimization**: Balance chunk size vs context preservation
3.  **Deduplication**: Remove near-duplicate content
4.  **Metadata Enrichment**: Store relevant document metadata

### Monitoring and Maintenance:

```{r}
#| eval: false
# Monitor index performance
check_index_stats <- function(con) {
  stats <- DBI::dbGetQuery(con, "
    SELECT
      schemaname, relname as tablename, indexrelname as indexname,
      idx_tup_read, idx_tup_fetch
    FROM pg_stat_user_indexes
    WHERE indexrelname LIKE '%embedding%'
  ")
  return(stats)
}

# Analyze embedding distribution
analyze_embeddings <- function(con) {
  stats <- DBI::dbGetQuery(con, "
    SELECT
      COUNT(*) as total_docs,
      AVG(length(content)) as avg_content_length,
      MIN(created_at) as earliest_doc,
      MAX(created_at) as latest_doc
    FROM documents
  ")
  return(stats)
}
```

### Error Handling:

```{r}
#| eval: false
safe_embedding_generation <- function(texts, max_retries = 3) {
  for (attempt in 1:max_retries) {
    tryCatch({
      return(generate_google_embeddings(texts))
    }, error = function(e) {
      if (attempt == max_retries) {
        stop("Failed to generate embeddings after ", max_retries, " attempts: ", e$message)
      }
      cat("Attempt", attempt, "failed, retrying...\n")
      Sys.sleep(2^attempt)  # Exponential backoff
    })
  }
}
```

## Troubleshooting {#troubleshooting}

### Common Issues:

**1. Google API Failures:** - Check Google API key validity
(`GOOGLE_API_KEY` environment variable) - Verify API is enabled in
Google Cloud Console or Google AI Studio - Monitor rate limits (Google
AI Studio: 60 requests/minute) - Implement retry logic with exponential
backoff

**2. PostgreSQL Connection Issues:** - Verify pgvector extension
installation - Check connection parameters - Ensure proper user
permissions

**3. Performance Problems:** - Analyze vector index usage - Consider
index rebuilding with different parameters - Monitor query execution
plans

**4. Memory Issues:** - Process documents in smaller batches - Use
streaming for large files - Clear unused objects from memory

### Debugging Queries:

```{sql}
#| eval: false
-- Check vector dimensions
SELECT embedding <-> '[0,0,0]'::vector as test FROM documents LIMIT 1;

-- Analyze index usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM documents
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

-- Monitor database size
SELECT pg_size_pretty(pg_total_relation_size('documents')) as table_size;
```

### R Session Management:

```{r}
#| eval: false
# Clean up large objects
cleanup_session <- function() {
  # Remove large vectors from memory
  rm(list = ls(pattern = "embeddings|large_"))
  gc()  # Force garbage collection
}

# Monitor memory usage
check_memory <- function() {
  cat("Memory usage:\n")
  print(pryr::mem_used())
  cat("\nLarge objects:\n")
  print(pryr::object_sizes()[1:10])
}
```

------------------------------------------------------------------------

This tutorial provides a comprehensive foundation for implementing
vector database document ingestion using: - **PostgreSQL pgvector** for
vector storage and similarity search - **Google's text-embedding-004**
for high-quality embeddings (768 dimensions) - **Claude 3 Sonnet** for
intelligent document analysis and enhancement - **R** for orchestrating
the entire pipeline

The modular approach allows you to adapt components based on your
specific requirements. You can use Google AI Studio for development or
Vertex AI for production, integrate Claude for document intelligence, or
fall back to local embedding models when needed. This stack provides
enterprise-grade semantic search capabilities with intelligent document
understanding.