Building a Semantic Memory System: From Postgres to pgvector in One Development Session

It's 2:15 AM, and I've just pushed the final commit that completes our project's semantic memory system. What started as a simple workflow tool has evolved into something much more interesting: a system that can understand and recall insights based on meaning, not just keywords.

The Vision: Beyond Keyword Search

Traditional search is frustrating. You know you saved that insight about "authentication patterns" somewhere, but searching for "auth" returns nothing because you wrote "login flow" instead. Sound familiar?

This is exactly the problem we set out to solve by building a semantic memory system that understands context and meaning. Here's how we went from a basic Postgres setup to a fully functional vector search system in a single development session.

The Architecture: What We Built

Our semantic memory system consists of several key components:

1. The Memory Picker Component

// A React component that lets users search and select relevant memories
<MemoryPicker 
  onMemorySelect={(memories) => setSelectedMemories(memories)}
  searchQuery={query}
  categoryFilter="technical"
/>

The MemoryPicker provides an intuitive interface with:

• Real-time search across stored insights
• Category filtering with visual chips
• Severity badges for quick prioritization
• Expandable details to preview content
• Template preview showing how {{memory}} placeholders will be replaced

2. The Vector Database Migration

The most significant technical leap was migrating from standard Postgres to pgvector. Here's what that looked like:

-- Migrated from postgres:16-alpine to pgvector/pgvector:pg16
CREATE EXTENSION vector;

-- Added vector column to existing table
ALTER TABLE workflow_insights 
ADD COLUMN embedding vector(1536);

-- Created HNSW index for fast similarity search
CREATE INDEX workflow_insights_embedding_idx 
ON workflow_insights 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

3. The Embedding Pipeline

Every insight now gets converted to a 1536-dimensional vector using OpenAI's text-embedding-3-small model:

async function generateEmbedding(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: text,
  });
  
  return response.data[0].embedding;
}

The Implementation Journey

Phase 1: Foundation (Previous Sessions)

• Built the WorkflowInsight data model
• Created insight persistence and search infrastructure
• Implemented {{memory}} template injection
• Added Row Level Security (RLS) and full-text search triggers

Phase 2: Vector Search (This Session)

The real magic happened when we added semantic capabilities:

Docker Migration: Switched from postgres:16-alpine to pgvector/pgvector:pg16 while preserving all existing data through named volumes.

Schema Enhancement: Added the vector column and HNSW index for efficient similarity search.

Backfill Process: Generated embeddings for all 10 existing insights, consuming 680 tokens total.

End-to-End Testing: Created a complete workflow test with 5 selected insights to verify the LLM receives properly injected memory content.

Lessons Learned: The Real Challenges

Challenge 1: The Mysterious Missing Dialog

Problem: The SaveInsightsDialog component wasn't appearing when it should have.

Root Cause: A boolean logic error in our filter condition:

// This failed because action was undefined, not "keep"
kp.action === "keep" 

// Fixed with proper null handling
!kp.action || kp.action === "keep" || kp.action === "edit"

Lesson: Always account for undefined values in your conditionals, especially when dealing with optional fields.

Challenge 2: Module Resolution in Scripts

Problem: Running our embedding backfill script with npx tsx /tmp/backfill-embeddings.ts failed with module resolution errors.

Solution: Move scripts into the project directory structure where they can properly resolve dependencies:

# Instead of /tmp/script.ts
scripts/backfill-embeddings.ts

Challenge 3: Shell Variable Conflicts

Problem: Using status as a variable name in our monitoring script caused "read-only variable" errors in zsh.

Fix: Shell built-ins are reserved. Use descriptive names like step_status instead.

The Results: Semantic Search in Action

With everything connected, our system now supports queries like:

• "Show me insights about user authentication" → finds memories about login flows, OAuth, and security patterns
• "Database performance issues" → surfaces insights about query optimization, indexing, and connection pooling

The cosine similarity search returns sensibly clustered results, proving that our embeddings capture meaningful semantic relationships.

What's Next: The Roadmap

While we've achieved our core goal, there are several exciting improvements on the horizon:

1. Hybrid Search: Combine vector similarity (70%) with traditional text search (30%) for the best of both worlds
2. Auto-embedding: Generate embeddings automatically when new insights are saved
3. Project Scoping: Filter memories by project context
4. Duplicate Prevention: Add safeguards against multiple saves of the same insight

The Bigger Picture

This late-night coding session represents more than just a feature addition—it's a glimpse into how AI-powered tools can enhance human memory and decision-making. By storing not just what we learned, but the semantic meaning behind it, we're building systems that truly understand context.

The combination of pgvector's performance, OpenAI's embedding quality, and careful UX design creates something that feels almost magical: a system that knows what you mean, not just what you said.

The complete implementation is available in our repository at commit 9b924f9. The entire system is operational end-to-end, from the React components down to the vector database layer.

Want to implement something similar? The key insight is starting simple with traditional search, then layering on semantic capabilities. Don't try to build everything at once—let the complexity emerge naturally as your understanding deepens.