Database Schema

Relevant source files

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

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

The Ethos database schema is built on PostgreSQL with the pgvector extension to support neuromorphic memory functions. It is designed to handle high-velocity ingestion of conversation turns, their consolidation into long-term semantic knowledge, and the natural decay of memories over time using Ebbinghaus decay algorithms.

The schema facilitates a multi-tiered memory architecture:

• Short-term/Episodic: Raw session events and turn-by-turn traces.
• Long-term/Semantic: Consolidated facts, preferences, and decisions.
• Associative: A graph-based structure linking related memory nodes.
• Vectorized: High-dimensional embeddings (768-dim) for semantic similarity search.

Sources: README.md:14-22, migrations/001_initial_schema.sql:1-12

Core Memory Tables

The system organizes data across several specialized tables to support different stages of the memory lifecycle.

Entity Relationship Overview

The following diagram illustrates the relationships between the core memory entities, highlighting how raw events evolve into durable facts and are linked via graph edges.

The schema uses UUIDs as primary keys across all major tables to ensure uniqueness across distributed components.
Sources: migrations/001_initial_schema.sql:18-185, migrations/002_story_004_ingest.sql:5-15

sessions

Tracks active OpenClaw agent sessions. It serves as the root container for episodic memory.

Field	Type	Description
id	UUID	Primary key.
session_key	TEXT	Unique key (e.g., "agent:neko:main").
agent_id	TEXT	Identifier for the specific agent.
last_active_at	TIMESTAMPTZ	Used for idle detection.

Sources: migrations/001_initial_schema.sql:20-29

session_events

A high-velocity raw log for incoming messages before they are processed into episodic traces.

Field	Type	Description
id	UUID	Primary key.
role	TEXT	One of: 'user', 'assistant', 'system', 'tool'.
content	TEXT	The raw message text.
created_at	TIMESTAMPTZ	Timestamp of ingestion.

Sources: migrations/002_story_004_ingest.sql:5-15

episodic_traces

Contains turn-by-turn conversation episodes. These records include salience scoring and are the primary targets for the consolidation engine.

• Salience Metrics: Includes importance, emotional_tone, and novelty (0.0 to 1.0 scales).
• Lifecycle: The consolidated_at field tracks whether the episode has been promoted to a semantic fact.
• Decay: The salience field is updated by the decay engine; pruned indicates if the memory has faded.

Sources: migrations/001_initial_schema.sql:40-75, ethos-server/src/subsystems/decay.rs:205-215

semantic_facts

Stores durable knowledge extracted from episodes. These are structured as subject-predicate-object triples.

• Kinds: Includes 'fact', 'decision', 'preference', 'entity', or 'relationship'.
• Conflict Resolution: Supports superseded_by (linking to a newer fact) and flagged_for_review.
• Traceability: source_episodes stores an array of UUIDs pointing back to the originating episodic traces.

Sources: migrations/001_initial_schema.sql:87-124, ethos-server/src/subsystems/consolidate.rs:360-380

Vector and Graph Systems

Ethos uses specialized structures for retrieval and associative navigation.

memory_vectors

This table integrates with pgvector to provide semantic search capabilities using 768-dimensional Gemini embeddings.

• Indexing: Utilizes an HNSW (Hierarchical Navigable Small World) index with vector_cosine_ops for approximate nearest neighbor search.
• Dimensions: Fixed at 768 dimensions.
• Optimization: Configured with m = 16 and ef_construction = 64.

Sources: migrations/001_initial_schema.sql:165-188, migrations/README.md:73-75

memory_graph_links

Enables "spreading activation" by defining directed edges between memory nodes (episodes, facts, or workflows).

Field	Type	Description
relation	TEXT	Type of link: 'temporal_next', 'semantic_similar', 'derived_from', etc.
weight	FLOAT	Strength of the association (0.0 to 1.0).

Sources: migrations/001_initial_schema.sql:203-219

Data Lifecycle and Maintenance

The schema is maintained through automated background subsystems that interact with the database tables.

Consolidation Process

The Consolidation Engine scans episodic_traces where consolidated_at is NULL and importance >= 0.8. It promotes qualifying episodes into semantic_facts.

Decay and Long-Term Potentiation (LTP)

The Decay Engine implements Ebbinghaus decay. Frequent access triggers LTP, which boosts salience and extends the memory's half-life.

• Formula: $S(t) = S_0 \times e^{-t/\tau_{eff}}$
• LTP Boost: Each retrieval increments retrieval_count and applies a multiplier to the decay time constant ($\tau_{eff}$).
• Pruning: When salience falls below the prune_threshold (default 0.05), the pruned boolean is set to true.

Sources: ethos-server/src/subsystems/decay.rs:5-15, ethos-server/src/subsystems/decay.rs:100-130

Search and Indexing

To ensure performance across large datasets, the schema includes several specialized indexes:

• GIN Indexes: Used for topics (array search) and content (trigram text search via pg_trgm).
• Conditional Indexes: idx_facts_active filters only non-pruned and non-superseded facts.
• Temporal Indexes: All major tables include descending indexes on created_at or last_active_at for rapid sorting.

Sources: migrations/001_initial_schema.sql:77-83, migrations/001_initial_schema.sql:126-133

The database schema provides a robust foundation for the Ethos neuromorphic memory engine, balancing the need for raw event logging with structured, long-term knowledge retention and associative retrieval.