PTENES
MODULE 2.3

🧠 Memory and Knowledge

SQLite FTS5 for full-text search, BM25 for relevance ranking, 2-layer system, memory categories, and automatic compaction.

6
Topics
60
Minutes
Interm.
Level
Technical
Type
1

🗃 SQLite FTS5 — Full-Text Search

FTS5 (Full-Text Search version 5) is a SQLite module that turns the database into a full-text search engine. Zero configuration, zero servers, zero cost — just SQLite with supercharged search.

🏗 Memory Schema

CREATE VIRTUAL TABLE memories USING fts5(
content, -- Fact/memory text
category, -- fact | conversation | solution
created_at, -- ISO timestamp
metadata, -- JSON with extra context
tokenize = 'unicode61'
);

💡 Why not embeddings + vector DB?

Vector databases are more accurate for semantic search, but add complexity and cost (embedding model + separate database). For a personal assistant with a consistent vocabulary, FTS5 + BM25 delivers excellent performance with zero complexity.

2

📊 BM25 — Relevance Ranking

BM25 (Best Match 25) is the ranking algorithm that SQLite FTS5 uses by default. It's the same algorithm used by Elasticsearch and Solr. Results in memories ranked by actual relevance, not by creation date.

🔍 Query with BM25

-- Search memories about Python, sorted by relevance
SELECT content, category, bm25(memories) as score
FROM memories
WHERE memories MATCH 'Python FastAPI'
ORDER BY score
LIMIT 5;
3

🏗 Two-Layer Memory System

The memory architecture solves the problem of context window: you can't put all the memory in the system prompt. The solution is 2 layers with distinct purposes.

📄 Layer 1: MEMORY.md

Static bootstrap facts. Always in the system prompt. Example: user name, tech stack, company, time zone.

Recommended size: < 500 tokens

🗄 Layer 2: memory.db

Dynamic facts and history. Retrieved by relevance when needed. Can grow indefinitely—only the top 5 relevant items go into the context.

Size: unlimited (relevance-based search)
4

🏷 Memory Categories

Each entry in memory.db has a category that enables more precise searches. When Jarvis needs context, it filters by the category most relevant to the current question.

fact

Facts About the User and the World

Preferences, settings, project information. E.g.: "User uses PostgreSQL 16 in production"

conversation

Important Conversation Excerpts

Decisions made and context for projects discussed. E.g.: "In 2026-04, we decided to use Redis for sessions"

solution

Solutions That Worked

How problems were solved. Jarvis checks this before suggesting solutions. Example: "Slow Docker build → adding .dockerignore fixed it"

5

🔍 Automatic Deduplication

Without deduplication, memory accumulates variations of the same fact. The store.py implements intelligent upsert: before saving, searches for similar facts and updates the existing one if the similarity is high.

🔄 Deduplication Algorithm

1
A new fact arrives to be saved
2
FTS5 searches for similar facts in the same category
3
If BM25 score > threshold (0.7): UPDATE the existing fact
4
If score < threshold: INSERT a new fact
6

🗜 Automatic Compaction

Memory that grows without limits becomes slow to search. Automatic compaction periodically summarizes old entries into denser facts, preserving relevance without consuming space uncontrollably.

✓ What to Compact

  • ✓Conversations older than 30 days
  • ✓Redundant facts about the same topic
  • ✓History of old decisions

✗ What to Preserve

  • ✗Solutions That Worked (High Value)
  • ✗Fundamental user preferences
  • ✗Frequently accessed facts

✅ Module 2.3 Summary

✓
SQLite FTS5 — Native full-text search, zero external dependencies
✓
BM25 — Ranking by actual relevance, not by date
✓
2 Layers — MEMORY.md (static bootstrap) + memory.db (dynamic history)
✓
Categories — fact, conversation, solution for precise searches
✓
Deduplication — Smart upsert avoids duplicates using BM25 similarity
✓
Compaction — Summarizing old memories keeps search efficient

Next Learning Path:

Track 3 — Zero-Trust Security: the most important track in the course

← Module 2.2 Track 3: Security →