Database Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• migrations/README.md
• migrations/001_initial_schema.sql
• migrations/002_story_004_ingest.sql
• README.md
• ethos-server/src/subsystems/consolidate.rs
• ethos-server/src/subsystems/decay.rs

Database migrations in Ethos manage the evolving schema of the neuromorphic memory engine. The system relies on PostgreSQL 17 with the pgvector extension to handle high-dimensional vector embeddings, alongside standard relational tables for session tracking, episodic traces, and semantic facts. Migrations are executed as sequential SQL scripts that establish the foundational data structures required for features like Ebbinghaus decay, consolidation, and semantic retrieval.

The migration architecture ensures that raw conversation data (ingested via session_events) can eventually be distilled into long-term knowledge (semantic_facts) while maintaining a high-performance vector index for similarity searches.

Sources: README.md:1-25, migrations/README.md:1-10

Infrastructure Requirements

Ethos requires specific PostgreSQL extensions and versions to function correctly. These must be configured by a superuser before migrations can be successfully applied to the ethos database.

Requirement	Version	Purpose
PostgreSQL	16+ (17 recommended)	Core relational database engine.
pgvector	0.8.0+	Enables vector similarity search using HNSW indexes.
pg_trgm	N/A	Enables fast trigram text search on content columns.

Sources: README.md:120-130, migrations/README.md:1-15

Vector Configuration

A critical constraint of the migration system is that the vector(768) dimension is fixed at creation. Any change to the embedding dimensionality (e.g., switching from Gemini 768-dim embeddings) requires a new migration that rebuilds the affected tables.

Sources: migrations/README.md:73-77, migrations/001_initial_schema.sql:155-163

Schema Architecture

The database schema is organized into distinct layers representing different stages of memory maturity, from raw events to durable facts.

Memory Hierarchy Flow

The following diagram illustrates how data flows between tables defined in the migrations, moving from short-term episodic traces to long-term semantic knowledge.

The diagram shows the lifecycle of memory from raw ingest to semantic promotion.
Sources: README.md:65-85, migrations/001_initial_schema.sql:30-185, ethos-server/src/subsystems/consolidate.rs:15-30

Core Tables Summary

Table	Migration	Purpose
sessions	001	Tracks active agent sessions and metadata.
session_events	002	High-velocity raw ingest log for conversation turns.
episodic_traces	001	Turn-by-turn episodes with salience and importance scores.
semantic_facts	001	Long-term structured facts (Subject-Predicate-Object triples).
memory_vectors	001/002	Stores 768-dim embeddings with HNSW indexing.
memory_graph_links	001	Directed edges for the spreading activation graph.
workflow_memories	001	Long-retention task and project trajectory memories.

Sources: migrations/001_initial_schema.sql:20-210, migrations/002_story_004_ingest.sql:1-15

Evolution of memory_vectors

Migration 002_story_004_ingest.sql significantly modified the memory_vectors table to support an asynchronous ingest pipeline. Initially defined with strict constraints, the table was altered to allow temporary nullability for vector data until background embedding tasks complete.

-- Migration 002: Adjusting memory_vectors for ingest pipeline
ALTER TABLE memory_vectors ALTER COLUMN embedding DROP NOT NULL;
ALTER TABLE memory_vectors RENAME COLUMN embedding TO vector;
ALTER TABLE memory_vectors ADD COLUMN IF NOT EXISTS source TEXT;
ALTER TABLE memory_vectors ADD COLUMN IF NOT EXISTS content TEXT;
ALTER TABLE memory_vectors ADD COLUMN IF NOT EXISTS importance FLOAT DEFAULT 0.5;

Sources: migrations/002_story_004_ingest.sql:16-25

Indexing and Search Performance

The migrations implement advanced indexing strategies to ensure low-latency retrieval across different memory types:

1. HNSW Indexing: Used on memory_vectors(vector) with vector_cosine_ops. Configured with m=16 and ef_construction=64 for approximate nearest neighbor search.
2. Trigram Search: The pg_trgm extension is used to create GIN indexes on content and statement columns, facilitating fast fuzzy text matching.
3. Filtered Indexes: Indexes are optimized for active data, such as idx_facts_active which filters for non-pruned and non-superseded facts.

The diagram represents the different indexing paths available for memory retrieval.
Sources: migrations/001_initial_schema.sql:10-25, migrations/001_initial_schema.sql:170-180

Maintenance and Lifecycles

Migrations establish the schema required for background maintenance tasks like Consolidation and Decay.

Consolidation Schema

The episodic_traces table includes columns like importance, retrieval_count, and consolidated_at. The consolidation engine queries these during idle periods to promote episodic data to the semantic_facts table.

Sources: ethos-server/src/subsystems/consolidate.rs:170-200, migrations/001_initial_schema.sql:45-75

Decay and Pruning

The salience and pruned columns (added in migration 001 and enhanced in 002) support Ebbinghaus decay. The decay.rs subsystem runs sweeps to update salience scores based on the time since creation and retrieval frequency, eventually marking items with pruned = true.

Sources: ethos-server/src/subsystems/decay.rs:20-50, migrations/002_story_004_ingest.sql:20-25

Operational Commands

Migrations are applied manually using the psql command-line tool. A standard workflow involves running the initial schema followed by feature-specific updates.

# Applying migrations in order
psql -U ethos -d ethos -h localhost -f migrations/001_initial_schema.sql
psql -U ethos -d ethos -h localhost -f migrations/002_story_004_ingest.sql

Sources: migrations/README.md:40-50, README.md:155-165

The database state can be verified using:

• \dt: List all tables.
• \d memory_vectors: Confirm vector column and HNSW index status.

Sources: migrations/README.md:52-60