Unified Context Architecture for StoryFlow

Current Flow:
User Message → Regex extraction → JSON summary → LLM

Problems:
├── Regex patterns miss semantic meaning
├── Only last 6 messages in context
├── No cross-session memory
├── No story/recording retrieval
├── Max 2000 characters (loses detail)
└── Every API call sends full context (inefficient)

┌─────────────────────────────────────────────────────────────────────────┐
│                        UNIFIED CONTEXT LAYER                            │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  ┌──────────────────────────────────────────────────────────────────┐   │
│  │                    CONTENT EMBEDDINGS                             │   │
│  │                    (pgvector + HNSW)                              │   │
│  ├──────────────────────────────────────────────────────────────────┤   │
│  │                                                                   │   │
│  │  Stories          Recordings        Conversations     User        │   │
│  │  ┌─────────┐     ┌─────────┐       ┌─────────┐       Memory      │   │
│  │  │ content │     │transcript│       │messages │       ┌─────┐    │   │
│  │  │ themes  │     │ai_summary│       │overview │       │facts│    │   │
│  │  │ tags    │     │ tags    │       │key_point│       │prefs│    │   │
│  │  └────┬────┘     └────┬────┘       └────┬────┘       └──┬──┘    │   │
│  │       │               │                 │               │        │   │
│  │       └───────────────┴─────────────────┴───────────────┘        │   │
│  │                            │                                      │   │
│  │                   ┌────────▼────────┐                            │   │
│  │                   │ Hybrid Search   │                            │   │
│  │                   │ (BM25 + Vector) │                            │   │
│  │                   └────────┬────────┘                            │   │
│  └────────────────────────────┼──────────────────────────────────────┘   │
│                               │                                          │
│  ┌────────────────────────────▼──────────────────────────────────────┐   │
│  │                    CONTEXT RETRIEVAL                              │   │
│  ├──────────────────────────────────────────────────────────────────┤   │
│  │                                                                   │   │
│  │  ┌─────────────────┐    ┌─────────────────┐    ┌──────────────┐  │   │
│  │  │ Conversation    │    │    Editor       │    │  Background  │  │   │
│  │  │ Context         │    │    Context      │    │  Context     │  │   │
│  │  │                 │    │                 │    │              │  │   │
│  │  │ • Active topic  │    │ • Story content │    │ • User prefs │  │   │
│  │  │ • Recent turns  │    │ • Related stories│   │ • Family tree│  │   │
│  │  │ • User emotions │    │ • Source artifacts│  │ • Past themes│  │   │
│  │  │ • Memory cues   │    │ • Selection ctx  │   │ • Writing style│ │   │
│  │  └─────────────────┘    └─────────────────┘    └──────────────┘  │   │
│  │                                                                   │   │
│  └───────────────────────────────────────────────────────────────────┘   │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

-- Run once via Supabase SQL Editor or MCP
CREATE EXTENSION IF NOT EXISTS vector;

-- Add embedding columns to existing tables
ALTER TABLE stories ADD COLUMN IF NOT EXISTS content_embedding vector(1536);
ALTER TABLE stories ADD COLUMN IF NOT EXISTS embedding_updated_at timestamptz;
 
ALTER TABLE recordings ADD COLUMN IF NOT EXISTS transcript_embedding vector(1536);
 
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS context_embedding vector(1536);
 
-- User memory table for persistent facts
CREATE TABLE IF NOT EXISTS user_memory (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  memory_type text NOT NULL
    CHECK (memory_type IN ('fact', 'preference', 'relationship', 'event', 'theme')),
  content text NOT NULL,
  embedding vector(1536),
  source_type text,  -- 'conversation', 'story', 'recording', 'explicit'
  source_id uuid,
  importance_score numeric(3,2) DEFAULT 0.5,
  confidence numeric(3,2) DEFAULT 0.8,
  last_referenced_at timestamptz DEFAULT now(),
  reference_count int DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
 
-- Indexes for performance
CREATE INDEX idx_stories_embedding ON stories
  USING hnsw (content_embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
CREATE INDEX idx_recordings_embedding ON recordings
  USING hnsw (transcript_embedding vector_cosine_ops);
 
CREATE INDEX idx_user_memory_embedding ON user_memory
  USING hnsw (embedding vector_cosine_ops);

-- Multi-source hybrid search
CREATE OR REPLACE FUNCTION search_unified_context(
  query_text text,
  query_embedding vector(1536),
  p_user_id uuid,
  search_stories boolean DEFAULT true,
  search_recordings boolean DEFAULT true,
  search_memories boolean DEFAULT true,
  match_count int DEFAULT 5,
  similarity_threshold numeric DEFAULT 0.7
)
RETURNS TABLE (
  source_type text,
  source_id uuid,
  title text,
  content_snippet text,
  similarity_score numeric,
  metadata jsonb
) AS $$
BEGIN
  RETURN QUERY
  WITH
  -- Story matches
  story_matches AS (
    SELECT
      'story'::text as source_type,
      s.id as source_id,
      s.title,
      LEFT(s.content, 300) as content_snippet,
      (1 - (s.content_embedding <=> query_embedding))::numeric as similarity_score,
      jsonb_build_object(
        'themes', s.themes,
        'tags', s.tags,
        'word_count', s.word_count,
        'source_recordings', s.source_recording_ids,
        'source_conversations', s.source_conversation_ids
      ) as metadata
    FROM stories s
    WHERE s.user_id = p_user_id
      AND s.content_embedding IS NOT NULL
      AND search_stories = true
      AND (1 - (s.content_embedding <=> query_embedding)) > similarity_threshold
    ORDER BY s.content_embedding <=> query_embedding
    LIMIT match_count
  ),
  -- Recording matches
  recording_matches AS (
    SELECT
      'recording'::text as source_type,
      r.id as source_id,
      r.title,
      LEFT(r.transcript, 300) as content_snippet,
      (1 - (r.transcript_embedding <=> query_embedding))::numeric as similarity_score,
      jsonb_build_object(
        'duration_seconds', r.duration_seconds,
        'ai_summary', r.ai_summary,
        'tags', r.tags,
        'audio_url', r.audio_url
      ) as metadata
    FROM recordings r
    WHERE r.user_id = p_user_id
      AND r.transcript_embedding IS NOT NULL
      AND search_recordings = true
      AND (1 - (r.transcript_embedding <=> query_embedding)) > similarity_threshold
    ORDER BY r.transcript_embedding <=> query_embedding
    LIMIT match_count
  ),
  -- Memory matches
  memory_matches AS (
    SELECT
      'memory'::text as source_type,
      m.id as source_id,
      m.memory_type as title,
      m.content as content_snippet,
      (1 - (m.embedding <=> query_embedding))::numeric as similarity_score,
      jsonb_build_object(
        'importance', m.importance_score,
        'confidence', m.confidence,
        'source_type', m.source_type,
        'reference_count', m.reference_count
      ) as metadata
    FROM user_memory m
    WHERE m.user_id = p_user_id
      AND m.embedding IS NOT NULL
      AND search_memories = true
      AND (1 - (m.embedding <=> query_embedding)) > similarity_threshold
    ORDER BY m.embedding <=> query_embedding
    LIMIT match_count
  )
  -- Combine and rank all results
  SELECT * FROM story_matches
  UNION ALL
  SELECT * FROM recording_matches
  UNION ALL
  SELECT * FROM memory_matches
  ORDER BY similarity_score DESC
  LIMIT match_count;
END;
$$ LANGUAGE plpgsql;

// lib/context/unified-context-manager.ts
 
import { createTypedClient } from '@/lib/supabase/typed-client'
import { EmbeddingService } from '@/lib/ai/embedding-service'
 
interface UnifiedContext {
  // Immediate context
  recentMessages: ConversationMessage[]
  currentTopic: string | null
  emotionalState: string | null
 
  // Retrieved context
  relevantStories: StoryContext[]
  relevantRecordings: RecordingContext[]
  userMemories: MemoryContext[]
 
  // Background context
  userProfile: UserProfile
  activeProject: ProjectContext | null
}
 
export class UnifiedContextManager {
  private embeddingService: EmbeddingService
  private maxRecentMessages = 6
  private maxRetrievedItems = 5
 
  constructor() {
    this.embeddingService = new EmbeddingService()
  }
 
  /**
   * Build comprehensive context for any AI interaction
   * Used by: Conversations, Editor, Suggestions
   */
  async buildUnifiedContext(
    userId: string,
    query: string,
    options: {
      sessionId?: string
      includeStories?: boolean
      includeRecordings?: boolean
      includeMemories?: boolean
      currentStoryId?: string
      conversationHistory?: ConversationMessage[]
    } = {}
  ): Promise<UnifiedContext> {
    const supabase = await createTypedClient()
 
    // 1. Generate query embedding
    const queryEmbedding = await this.embeddingService.generateEmbedding(query)
 
    // 2. Retrieve relevant content from all sources
    const [searchResults, userProfile, sessionContext] = await Promise.all([
      this.searchUnifiedContent(userId, query, queryEmbedding, options),
      this.getUserProfile(userId),
      options.sessionId ? this.getSessionContext(options.sessionId) : null
    ])
 
    // 3. Separate results by type
    const relevantStories = searchResults
      .filter(r => r.source_type === 'story')
      .map(this.mapToStoryContext)
 
    const relevantRecordings = searchResults
      .filter(r => r.source_type === 'recording')
      .map(this.mapToRecordingContext)
 
    const userMemories = searchResults
      .filter(r => r.source_type === 'memory')
      .map(this.mapToMemoryContext)
 
    // 4. Get recent messages if available
    const recentMessages = options.conversationHistory?.slice(-this.maxRecentMessages) || []
 
    // 5. Build unified context
    return {
      recentMessages,
      currentTopic: sessionContext?.current_topic || this.inferTopic(query),
      emotionalState: sessionContext?.emotional_state || null,
      relevantStories,
      relevantRecordings,
      userMemories,
      userProfile,
      activeProject: await this.getActiveProject(userId)
    }
  }
}

Tier 1: In-Memory (Redis)
├── Current session context
├── Recent query embeddings (5-min TTL)
├── Active user profile
└── Session-specific memories

Tier 2: Database + pgvector
├── Story embeddings (permanent)
├── Recording embeddings (permanent)
├── User memories (permanent)
└── Conversation summaries (30-day TTL)