🦞 PGVector Memory System

Hybrid vector search for Clawdie's long-term memory

✅ Complete
📅 10.03.2026 15:00
📊 2,227+ lines added

🎉 Implementation Complete!

All 5 phases finished. TypeScript + Bash pipelines ready.

8 commits pushed to Codeberg on 10.03.2026

Latest Codeberg Commits (10.03.2026)

1a82089 — Memory Pipeline Scripts

+346 lines — Bash scripts for memory operations

File Purpose
scripts/memory/common.sh DB connection, dependency checks, config
scripts/memory/embed.sh Generate 1536d embeddings via OpenRouter
scripts/memory/chunk.sh Split text on sentence boundaries (~500 chars)
scripts/memory/memory-pg.sh Full CLI: store/search/recent/important/count

Pipeline: text → chunk → embed → insert (fully automated)

23f222f — Complete PGVector Memory System

+659 lines — TypeScript integration

File Purpose
src/memory-pg.ts PostgreSQL memory client (275 lines)
src/memory-lifecycle.ts Memory lifecycle management
src/memory-pg.test.ts Unit tests
scripts/memory/memory-hydrate-pg.sh Populate MEMORY.md from PostgreSQL

a81ae2a — Jail Creation Playbook

+189 lines — Ansible automation

39b00d1 — Test Deployment Plan

+425 lines — Step-by-step bootstrap guide

Infrastructure & Documentation

Commit Changes
5d2e605 Jail state tracking, European date format standard, ZFS naming convention
a502bd2 RCTL prerequisites for jail memory limits
dcb3545 bhyve/VMM prerequisites for future VM support
050c93c Core FreeBSD config docs (loader.conf, rc.conf)

Current State

Component Status
PostgreSQL 17 ✅ Running in db jail with pgvector 0.8.1
ai_brain Database ✅ Created with extensions
Schema ✅ memories, memory_chunks, memory_embeddings
Hybrid Search ✅ search_memories() function
Embedding API ✅ OpenRouter text-embedding-3-large @ 1536d
Bash Scripts ✅ memory-pg.sh, embed.sh, chunk.sh, common.sh
TypeScript Module ✅ memory-pg.ts (275 lines)
Unit Tests ✅ memory-pg.test.ts
Deployment Plan ✅ test-deployment-plan.md (425 lines)

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                     Clawdie-AI Memory System                    │
│                                                                 │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐        │
│  │ TypeScript  │     │    Bash     │     │  PostgreSQL │        │
│  │ memory-pg.ts│     │ memory-pg.sh│     │   ai_brain  │        │
│  └──────┬──────┘     └──────┬──────┘     └──────┬──────┘        │
│         │                   │                   │               │
│         └───────────────────┼───────────────────┘               │
│                             │                                   │
│                    ┌────────┴────────┐                          │
│                    │  Pipeline Flow  │                          │
│                    └────────┬────────┘                          │
│                             │                                   │
│         ┌───────────────────┼───────────────────┐               │
│         ▼                   ▼                   ▼               │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐        │
│  │   chunk.sh  │────►│  embed.sh   │────►│   INSERT    │        │
│  │  Split 500c │     │ OpenRouter  │     │  PostgreSQL │        │
│  └─────────────┘     └─────────────┘     └─────────────┘        │
│                                                                 │
│  Search: Hybrid RRF (full-text tsvector + vector cosine)        │
└─────────────────────────────────────────────────────────────────┘

Implementation Progress

✓

Database Schema Setup

DONE
✅ Complete: PostgreSQL schema with pgvector
✓

Embedding Generation

DONE
✅ Complete: OpenRouter integration
Property Value
ProviderOpenRouter
Modelopenai/text-embedding-3-large
Dimensions1536
Cost~$0.13 / 1M tokens
✓

Memory Scripts

DONE
✅ Complete: Bash + TypeScript pipelines 
# Store (auto-chunks + embeds)
./scripts/memory/memory-pg.sh store "Summary text" "topic1,topic2" 3

# Hybrid search
./scripts/memory/memory-pg.sh search "query text" 10

# Recent memories
./scripts/memory/memory-pg.sh recent 5

# Important memories
./scripts/memory/memory-pg.sh important
✓

Hybrid Search

DONE
✅ Complete: RRF function combining full-text + vector 
-- search_memories() function
SELECT * FROM search_memories(
    'query text',
    '<embedding>'::vector,
    10
);
-- Returns: memory_id, chunk_text, summary, importance, topics, combined_score
✓

TypeScript Integration

DONE
✅ Complete: Full TypeScript module with tests

Usage Examples

Bash CLI

# Store a memory
./scripts/memory/memory-pg.sh store \
    "Discussed PGVector implementation with Sam" \
    "postgres,vector,memory" \
    3

# Search (hybrid: full-text + semantic)
./scripts/memory/memory-pg.sh search "database setup" 10

# Get recent memories
./scripts/memory/memory-pg.sh recent 5

# Get high-importance memories
./scripts/memory/memory-pg.sh important

# Count total memories
./scripts/memory/memory-pg.sh count

TypeScript API

import { storeMemory, searchMemories, getRecentMemories } from './memory-pg.js';

// Store a memory (auto-chunks + embeds)
const memoryId = await storeMemory({
  summary: 'Session about deploying PostgreSQL with pgvector',
  topics: ['postgres', 'vector', 'deployment'],
  importance: 3,
});

// Hybrid search
const results = await searchMemories('how did we set up the database', 10);

// Recent memories
const recent = await getRecentMemories(5);

Summary

Phase Task Status
1 Database Schema ✅ Done
2 Embedding Generation ✅ Done
3 Memory Scripts (Bash) ✅ Done
4 Hybrid Search Function ✅ Done
5 TypeScript Integration ✅ Done
Total 5/5 Complete 🎉
🚀 Next Steps:
  1. Run test deployment on OVH VPS (follow docs/test-deployment-plan.md)
  2. Migrate existing Supabase memories to ai_brain
  3. Validate end-to-end with real queries

Files Changed (10.03.2026)

Category Files Lines
Memory Scripts 5 files in scripts/memory/ +480
TypeScript memory-pg.ts, memory-lifecycle.ts, memory-pg.test.ts +410
Ansible jail-create.yaml, host-preflight.yaml, db-memory-bootstrap.yaml +229
Documentation test-deployment-plan.md, loader-conf.md, rc-conf.md, etc. +1,108
Total 27 files +2,227