SQLite Memory System - Documentation

Overview

A structured memory storage system using SQLite for persistent, queryable memory across sessions.

Why SQLite?

Advantages over file-based memory

• Queryable: Run complex queries (SQL) instead of grep/grep
• Structured: Type-safe columns with indexes
• Fast: O(log n) lookups vs O(n) file scanning
• Atomic: Single file, transactional consistency
• Portable: Works anywhere SQLite is available

Disadvantages

• Less human-readable: Requires SQLite CLI/query knowledge
• Migration risk: Converting existing memories
• Single point of failure: One file vs distributed files

Database Schema

memory Table

Stores episodic, semantic, and procedural memories.

CREATE TABLE memory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,           -- 'episodic', 'semantic', 'procedural'
    'key' TEXT NOT NULL,          -- Memory identifier
    value TEXT,                    -- Memory content (JSON or plain text)
    context TEXT,                  -- Additional context
    tags TEXT,                     -- Comma-separated tags
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• idx_memory_type - Filter by memory type
• idx_memory_key - Fast key lookups
• idx_memory_created - Sort by date

portfolio Table

Track holdings and PnL.

CREATE TABLE portfolio (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    symbol TEXT NOT NULL UNIQUE,
    amount REAL DEFAULT 0,
    avg_cost REAL DEFAULT 0,
    pnl_pct REAL DEFAULT 0,
    updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

trades Table

Record all trades.

CREATE TABLE trades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    symbol TEXT NOT NULL,
    side TEXT NOT NULL,           -- 'buy' or 'sell'
    amount REAL NOT NULL,
    price REAL NOT NULL,
    total REAL NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    notes TEXT
);

moltbook_engagement Table

Track Moltbook activity.

CREATE TABLE moltbook_engagement (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id TEXT,
    action TEXT NOT NULL,         -- 'post', 'reply', 'vote'
    content TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

state Table

Runtime key-value state.

CREATE TABLE state (
    'key' TEXT PRIMARY KEY,
    value TEXT,
    updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CLI Usage

# Initialize database
./scripts/init_db.sh

# Add memory
./memory.sh add episodic "2024-01-15-crypto" "Bought 0.5 BTC" "From Coinbase"

# Query by type
./memory.sh query episodic --limit 10

# Search memories
./memory.sh search "bitcoin"

# Get specific memory
./memory.sh get "core_principles"

# Record trade
./memory.sh trade buy BTC 0.5 45000 "Average down"

# Show portfolio
./memory.sh portfolio

# Backup database
./memory.sh backup

Direct SQLite Queries

# List all memories
sqlite3 data/agent.db "SELECT type, key, value FROM memory;"

# Search memories
sqlite3 data/agent.db "SELECT * FROM memory WHERE value LIKE '%team%';"

# Get recent memories
sqlite3 data/agent.db "SELECT * FROM memory ORDER BY created DESC LIMIT 10;"

# Count by type
sqlite3 data/agent.db "SELECT type, COUNT(*) FROM memory GROUP BY type;"

Migration from File-Based

To migrate existing memories:

# For each .md file in memory/semantic/
sqlite3 data/agent.db "INSERT INTO memory (type, key, value, context) 
VALUES ('semantic', 'filename', file_content, 'description');"

# For each .md file in memory/episodic/
sqlite3 data/agent.db "INSERT INTO memory (type, key, value, context) 
VALUES ('episodic', 'date-notes', file_content, 'session log');"

Best Practices

1. Use transactions for bulk operations
2. Index frequently queried columns
3. Backup before schema changes: ./backup.sh
4. Use JSON for complex values (stored as TEXT)
5. UTC timestamps for consistency
6. Quote 'key' column - it's a reserved word

File Locations

• Database: data/agent.db
• Skill: skills/sqlite-memory/
• Scripts: skills/sqlite-memory/scripts/
• Schema docs: skills/sqlite-memory/references/schema.md

Current Status (2026-02-04)

• ✅ Database created and initialized
• ✅ Schema created (5 tables)
• ✅ CLI tools working
• ⚠️ Migration pending (testing phase)