clawdie-ai/docs/internal/POSTGRES-HYBRID-MEMORY.md

PostgreSQL Hybrid Memory

This document defines the next schema step for Clawdie memory on PostgreSQL.

Decision

Use hybrid retrieval:

• structured relational memory in memories
• keyword and full-text retrieval over chunks
• semantic retrieval with pgvector

Do not replace relational memory with vectors. Add vectors as a second retrieval path and merge results.

Why

• structured fields still matter for topics, decisions, participants, and filters
• exact keyword search is still useful for names, commands, and rare strings
• vector similarity improves semantic recall
• hybrid ranking is more robust than either keyword-only or vector-only search

Recommended schema layers

1. memories

   • canonical memory object
   • session summary, metadata, timestamps, importance, topics

2. memory_chunks

   • chunked text derived from each memory
   • one memory can have multiple chunks
   • full-text search lives here

3. memory_embeddings

   • one embedding row per chunk
   • stores vector, provider, model, and version metadata

Retrieval flow

1. apply metadata filters first
2. run full-text search over memory_chunks
3. run vector similarity search over memory_embeddings
4. merge results with reciprocal rank fusion (RRF)
5. return top chunks and their parent memories

Versioning rules

Store enough metadata to re-embed later:

• embedding_provider
• embedding_model
• chunking_version
• content_hash
• created_at

This lets Clawdie migrate from one embedding model to another without losing track of what was stored.

Dimension choice

Older drafts used vector(1536) as a placeholder baseline. Current repo migrations and defaults use vector(1024) for bge-m3.

If the model changes, create a new migration or a new embedding table/version.

First milestone

1. ✅ commit the base relational schema
2. ✅ add chunk and embedding tables
3. ✅ populate chunks (src/memory-pg.ts writes into memory_chunks)
4. ✅ generate embeddings outside PostgreSQL (OpenRouter or local llama-server)
5. ✅ store embeddings in memory_embeddings
6. ✅ test one hybrid query with RRF (docs/internal/sql/search-memories.sql)

Current recommendation

• root/shared installs use system_brain for the memory database and role
• additive tenants derive memory DB names from dbSlug(tenantId):
  • <slug>_brain
• use PostgreSQL 18 with pgcrypto, uuid-ossp, and vector
• start with hybrid retrieval, not vector-only retrieval
• embedding model default: BAAI/bge-m3
• embedding transport default:
  • OpenRouter when OPENROUTER_API_KEY is configured
  • otherwise local http://localhost:8080/v1
• embeddings can be disabled entirely by setting EMBED_BASE_URL=''

Dimension choice (resolved)

Using vector(1024) — bge-m3 outputs 1024 dimensions natively. Configured via EMBED_BASE_URL, EMBED_MODEL, and EMBED_DIMENSIONS in .env. Current defaults in src/config.ts are:

• EMBED_MODEL=BAAI/bge-m3
• EMBED_DIMENSIONS=1024

OpenRouter is the default remote fallback when a local embedding endpoint is not configured.

Related files

• POSTGRES-MEMORY.md
• brain-hybrid-upgrade.sql
• pgvector-install-log.md