Pinecone Vector Database Integration

Pinecone provides the vector search capability that powers the AI Knowledge Assistant. Ticket content is embedded as vectors and stored in Pinecone for semantic retrieval.

---

Overview

Component	Details
Service	Pinecone (managed vector database)
Key File	include/class.ai-assistant.php
Embedding Cron	cron-embeddings.php (every 10 minutes)
Cache Table	ost_ticket_embeddings
Purpose	Semantic search for AI chatbot context retrieval

---

Architecture

Ticket content changes (new entry, status change)
  → cron-embeddings.php detects unprocessed tickets
  → Extracts text from thread entries (M, R, N types)
  → Generates embedding vector via Gemini API
  → Upserts vector to Pinecone with metadata
  → Caches text in ost_ticket_embeddings

Staff asks question via AI Assistant
  → Question text → embedding vector (Gemini)
  → Query Pinecone: find top-K similar vectors
  → Retrieve matching ticket content
  → Build context prompt for Gemini chat
  → Stream response to staff

---

Embedding Generation

Process

The cron-embeddings.php job:

1. Queries for tickets without embeddings or with stale embeddings
2. Extracts text content from thread entries
3. Generates embedding vectors via the Gemini embedding model
4. Upserts vectors to Pinecone with metadata
5. Updates ost_ticket_embeddings with the processed text hash

Text Extraction

For each ticket, the embedding text includes:

• Ticket subject
• All message content (type M)
• All response content (type R)
• All note content (type N)
• AI summary (if available)
• Client name and organization

Vector Metadata

Each vector in Pinecone includes filterable metadata:

Field	Purpose
ticket_id	Reference back to ticket
ticket_number	Display number
client	Client/organization name
department	Department name
status	Current ticket status
created_at	Ticket creation date
ai_urgency	AI urgency level

---

Pinecone Configuration

Configured via Admin → Settings → AI Assistant:

Setting	Purpose
Pinecone API Key	Authentication
Pinecone Environment	Cluster region/environment
Pinecone Index Name	Index to store vectors
Top-K Results	Number of similar tickets to retrieve (default: 5)
Similarity Threshold	Minimum score to include in results

---

Cron Job: cron-embeddings.php

# Runs every 10 minutes
php cron-embeddings.php

Processing Logic

// Find tickets needing embedding
$tickets = Ticket::getUnembeddedTickets($limit);

foreach ($tickets as $ticket) {
    // Extract content
    $text = $ticket->getEmbeddingText();

    // Generate embedding
    $vector = GeminiEmbedding::embed($text);

    // Upsert to Pinecone
    $pinecone->upsert($ticket->getId(), $vector, $metadata);

    // Cache locally
    $ticket->saveEmbedding($text);
}

Batch Processing

• Processes up to 10 tickets per run
• Rate limiting between API calls to avoid quota issues
• Skips tickets that haven't changed since last embedding

---

Database Table

-- ost_ticket_embeddings
CREATE TABLE ost_ticket_embeddings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    text_hash VARCHAR(64) NOT NULL,
    text_content TEXT,
    embedded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_ticket (ticket_id)
);

The text_hash is used to detect content changes — if the hash differs from the stored value, the ticket needs re-embedding.

---

Querying (AI Assistant)

When the AI Assistant receives a question:

// 1. Embed the question
$questionVector = GeminiEmbedding::embed($question);

// 2. Query Pinecone for similar tickets
$results = $pinecone->query($questionVector, [
    'topK' => 5,
    'includeMetadata' => true,
    'filter' => ['status' => ['$ne' => 'deleted']],
]);

// 3. Build context from matched tickets
$context = '';
foreach ($results['matches'] as $match) {
    $ticket = Ticket::lookup($match['id']);
    $context .= $ticket->getContextForAI();
}

// 4. Send to Gemini with context
$response = Gemini::chat($question, $context);

---

Force Re-Embedding

Staff can trigger re-embedding for a specific ticket via the AI Assistant:

Method	Endpoint	Description
POST	/ajax.php/ai-assistant/reembed	Force re-embed a ticket

This is useful after significant ticket updates or corrections.

---

Troubleshooting

Issue	Solution
AI gives outdated answers	Run php cron-embeddings.php or force re-embed
Embedding cron errors	Check Gemini API key is valid; check Pinecone connectivity
No results for queries	Verify Pinecone index has vectors; check similarity threshold
Slow embedding generation	Reduce batch size; check rate limits on Gemini API
Hash mismatch errors	Clear ost_ticket_embeddings row for the ticket

---

Key Files

File	Purpose
include/class.ai-assistant.php	Pinecone query logic, embedding generation, chat context building
cron-embeddings.php	Periodic embedding generation cron
deploy/deploy-ai-assistant.sql	Creates ost_ticket_embeddings + AI conversation tables