Contextual Memory & RAG Architecture Research

ADR-001: Vector Search Infrastructure

Status: APPROVED
Date: November 2024

Context:
StoryFlow needs semantic search across stories, recordings, and user memories
to provide contextual AI assistance during conversations and editing.

Decision:
Use pgvector (PostgreSQL extension) with OpenAI text-embedding-3-small.

Rationale:
1. Zero additional infrastructure cost (included in Supabase)
2. Data stays in PostgreSQL (no sync issues)
3. Sufficient performance for less than 1M vectors
4. Simpler architecture (one less service)
5. Easy migration path to dedicated DB if needed later

Consequences:
- Must monitor vector count and query latency
- Limited to ~1M vectors before performance degrades
- No multi-region vector search (acceptable for now)

Review Triggers:
- Vector count exceeds 800K
- P95 query latency exceeds 150ms
- Need for multi-region deployment

Current Flow:
User Message → Rule-based extraction → JSON summary → LLM

┌─────────────────────────────────────────────────────────────┐
│                    MEMORY SYSTEM                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌──────────────────┐    ┌──────────────────────────────┐  │
│  │   SHORT-TERM     │    │       LONG-TERM              │  │
│  │   MEMORY (STM)   │    │       MEMORY (LTM)           │  │
│  │                  │    │                              │  │
│  │ • Last 6 turns   │    │ • Story embeddings          │  │
│  │ • Current topic  │    │ • Family member graph       │  │
│  │ • Active emotion │    │ • User preferences          │  │
│  │                  │    │ • Historical themes         │  │
│  │ [In-Memory/      │    │                              │  │
│  │  Redis Cache]    │    │ [pgvector + PostgreSQL]     │  │
│  └──────────────────┘    └──────────────────────────────┘  │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │              USER PROFILE (Persistent)               │   │
│  │                                                      │   │
│  │ • Writing style preferences                          │   │
│  │ • Family tree structure                              │   │
│  │ • Dialect/language patterns                          │   │
│  │ • Topic interests & sensitivities                    │   │
│  │ • Conversation patterns (talker vs. brief)           │   │
│  │                                                      │   │
│  │ [profiles table + JSONB enrichment]                  │   │
│  └──────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘

// lib/ai/embedding-service.ts
import { OpenAI } from 'openai'
import { createTypedClient } from '@/lib/supabase/typed-client'
 
const openai = new OpenAI()
 
export class EmbeddingService {
  private model = 'text-embedding-3-small' // 1536 dimensions, $0.00002/1K tokens
 
  async generateEmbedding(text: string): Promise<number[]> {
    const response = await openai.embeddings.create({
      model: this.model,
      input: text.slice(0, 8000), // Max input limit
    })
    return response.data[0].embedding
  }
 
  async embedStory(storyId: string): Promise<void> {
    const supabase = await createTypedClient()
 
    const { data: story } = await supabase.raw
      .from('stories')
      .select('id, title, content')
      .eq('id', storyId)
      .single()
 
    if (!story) return
 
    // Combine title and content for richer embedding
    const textToEmbed = `${story.title}\n\n${story.content}`
    const embedding = await this.generateEmbedding(textToEmbed)
 
    await supabase.raw
      .from('stories')
      .update({
        content_embedding: embedding,
        embedding_updated_at: new Date().toISOString()
      })
      .eq('id', storyId)
  }
 
  async findSimilarStories(
    userId: string,
    query: string,
    limit: number = 5
  ): Promise<Array<{ id: string; title: string; similarity: number }>> {
    const queryEmbedding = await this.generateEmbedding(query)
    const supabase = await createTypedClient()
 
    const { data } = await supabase.raw.rpc('search_stories_hybrid', {
      query_text: query,
      query_embedding: queryEmbedding,
      user_id: userId,
      match_count: limit
    })
 
    return data || []
  }
}

StoryFlow Expected Scale (Year 1):
├── Users: ~1,000-5,000
├── Stories: ~50,000-250,000
├── Recordings: ~20,000-100,000
├── User Memories: ~100,000-500,000
└── Total Vectors: ~170,000-850,000

pgvector Comfortable Limit: ~1,000,000 vectors
Pinecone Advantage Threshold: More than 1,000,000 vectors with less than 20ms latency requirement

# Run from project root
npx supabase gen types typescript --project-id qrlygafaejovxxlnkpxa > apps/web-app/types/database.ts

-- Test with a sample embedding (1536 zeros for testing)
SELECT * FROM search_unified_context(
  array_fill(0::real, ARRAY[1536])::vector(1536),
  'your-user-id-here'::uuid,
  true, true, true, 5, 0.5
);