Skip to content

Schema

Jump to bottom
Temp edited this page Oct 4, 2025 · 15 revisions

Database Schema

Complete reference for Memory Journal's SQLite database schema.

Overview

Memory Journal uses SQLite with:

  • WAL (Write-Ahead Logging) mode
  • FTS5 full-text search
  • Foreign key constraints
  • Automatic triggers
  • Comprehensive indexes

Core Tables

memory_journal

Main table storing journal entries.

CREATE TABLE memory_journal (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entry_type TEXT NOT NULL,
    content TEXT NOT NULL,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
    is_personal INTEGER DEFAULT 1,
    significance_type TEXT,
    auto_context TEXT,
    deleted_at TEXT
);

Columns:

Column Type Nullable Description
id INTEGER No Primary key (auto-increment)
entry_type TEXT No Type of entry (personal_reflection, technical_achievement, etc.)
content TEXT No Entry content (max 50KB)
timestamp TEXT No ISO 8601 timestamp (auto-set)
is_personal INTEGER No 1 = personal, 0 = project (default: 1)
significance_type TEXT Yes Significance marker (technical_breakthrough, milestone, etc.)
auto_context TEXT Yes JSON string of Git/GitHub context
deleted_at TEXT Yes Soft delete timestamp (NULL = not deleted)

Indexes:

CREATE INDEX idx_memory_journal_timestamp ON memory_journal(timestamp);
CREATE INDEX idx_memory_journal_type ON memory_journal(entry_type);
CREATE INDEX idx_memory_journal_personal ON memory_journal(is_personal);
CREATE INDEX idx_memory_journal_deleted ON memory_journal(deleted_at);

Entry Types:

  • personal_reflection
  • technical_achievement
  • development_note
  • milestone
  • enhancement
  • bug_fix
  • technical_note

Significance Types:

  • technical_breakthrough
  • milestone
  • major_breakthrough
  • project_completion
  • identity_development

tags

Tag definitions with usage tracking.

CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    usage_count INTEGER DEFAULT 0
);

Columns:

Column Type Nullable Description
id INTEGER No Primary key (auto-increment)
name TEXT No Tag name (unique, max 100 chars)
usage_count INTEGER No Number of entries using this tag

Indexes:

CREATE INDEX idx_tags_name ON tags(name);

Tag Management:

  • Auto-created on first use (INSERT OR IGNORE)
  • usage_count updated automatically
  • Lowercase recommended (not enforced)

entry_tags

Junction table linking entries to tags (many-to-many).

CREATE TABLE entry_tags (
    entry_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (entry_id, tag_id),
    FOREIGN KEY (entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Columns:

Column Type Nullable Description
entry_id INTEGER No Reference to memory_journal.id
tag_id INTEGER No Reference to tags.id

Composite Primary Key: (entry_id, tag_id)

Indexes:

CREATE INDEX idx_entry_tags_entry ON entry_tags(entry_id);
CREATE INDEX idx_entry_tags_tag ON entry_tags(tag_id);

Cascading Deletes:

  • Deleting an entry removes all its tag associations
  • Deleting a tag removes all entry associations

relationships

Typed relationships between entries.

CREATE TABLE relationships (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_entry_id INTEGER NOT NULL,
    to_entry_id INTEGER NOT NULL,
    relationship_type TEXT NOT NULL,
    description TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE,
    FOREIGN KEY (to_entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE
);

Columns:

Column Type Nullable Description
id INTEGER No Primary key (auto-increment)
from_entry_id INTEGER No Source entry ID
to_entry_id INTEGER No Target entry ID
relationship_type TEXT No Type of relationship
description TEXT Yes Optional description
created_at TEXT No ISO 8601 timestamp (auto-set)

Indexes:

CREATE INDEX idx_relationships_from ON relationships(from_entry_id);
CREATE INDEX idx_relationships_to ON relationships(to_entry_id);

Relationship Types:

  • references - General connection
  • implements - Implementation
  • clarifies - Explanation
  • evolves_from - Iteration
  • response_to - Reply

Cascading Deletes:

  • Deleting an entry removes all its relationships (both directions)

embeddings

Vector embeddings for semantic search (optional).

CREATE TABLE embeddings (
    entry_id INTEGER PRIMARY KEY,
    embedding BLOB NOT NULL,
    model_name TEXT NOT NULL,
    FOREIGN KEY (entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE
);

Columns:

Column Type Nullable Description
entry_id INTEGER No Primary key, reference to memory_journal.id
embedding BLOB No Binary vector data (384 dimensions)
model_name TEXT No Model name (e.g., 'all-MiniLM-L6-v2')

Storage:

  • Each embedding: ~1.5KB (384 floats × 4 bytes)
  • 1000 entries: ~1.5MB
  • 10000 entries: ~15MB

Cascading Deletes:

  • Deleting an entry removes its embedding

Full-Text Search

memory_journal_fts

FTS5 virtual table for full-text search.

CREATE VIRTUAL TABLE memory_journal_fts USING fts5(
    content,
    entry_type,
    content='memory_journal',
    content_rowid='id',
    tokenize='porter unicode61'
);

Configuration:

  • content='memory_journal' - External content table
  • content_rowid='id' - Links to memory_journal.id
  • tokenize='porter unicode61' - Porter stemming + Unicode support

Features:

  • Automatic tokenization
  • Stop word filtering
  • Porter stemming (e.g., "optimize" matches "optimization")
  • Rank-ordered results
  • BM25 scoring

Automatic Triggers

FTS5 Synchronization

Keep FTS5 index in sync with main table.

Insert trigger:

CREATE TRIGGER memory_journal_ai AFTER INSERT ON memory_journal BEGIN
    INSERT INTO memory_journal_fts(rowid, content, entry_type)
    VALUES (new.id, new.content, new.entry_type);
END;

Update trigger:

CREATE TRIGGER memory_journal_au AFTER UPDATE ON memory_journal BEGIN
    UPDATE memory_journal_fts 
    SET content = new.content, entry_type = new.entry_type
    WHERE rowid = new.id;
END;

Delete trigger:

CREATE TRIGGER memory_journal_ad AFTER DELETE ON memory_journal BEGIN
    DELETE FROM memory_journal_fts WHERE rowid = old.id;
END;

Note: Soft deletes don't trigger the delete trigger (deleted_at is an UPDATE).

Schema Migrations

Version 1.1.0 Migration

Added soft delete and relationships.

def _run_migrations(self, conn):
    """Apply schema migrations for upgrades"""
    
    # Add deleted_at column if missing
    cursor = conn.execute("PRAGMA table_info(memory_journal)")
    columns = [row[1] for row in cursor.fetchall()]
    
    if 'deleted_at' not in columns:
        print("Running migration: Adding deleted_at column", file=sys.stderr)
        conn.execute("ALTER TABLE memory_journal ADD COLUMN deleted_at TEXT")
    
    # Create relationships table if missing
    cursor = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='relationships'"
    )
    if not cursor.fetchone():
        print("Running migration: Creating relationships table", file=sys.stderr)
        conn.execute("""
            CREATE TABLE relationships (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                from_entry_id INTEGER NOT NULL,
                to_entry_id INTEGER NOT NULL,
                relationship_type TEXT NOT NULL,
                description TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (from_entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE,
                FOREIGN KEY (to_entry_id) REFERENCES memory_journal(id) ON DELETE CASCADE
            )
        """)
        conn.execute("CREATE INDEX idx_relationships_from ON relationships(from_entry_id)")
        conn.execute("CREATE INDEX idx_relationships_to ON relationships(to_entry_id)")

Migration Strategy:

  • Check if columns/tables exist
  • Add if missing
  • Non-destructive (data preserved)
  • Idempotent (safe to run multiple times)

Database Size Estimates

Typical Entry

  • Content: 500 bytes (average)
  • Metadata: 200 bytes
  • Tags: 50 bytes (2-3 tags)
  • Embedding: 1500 bytes (if enabled)
  • Total: ~2250 bytes per entry

Size Projections

Entries With Embeddings Without Embeddings
100 220 KB 75 KB
1,000 2.2 MB 750 KB
10,000 22 MB 7.5 MB
100,000 220 MB 75 MB

Plus:

  • FTS5 index: ~50% of content size
  • Tags table: negligible
  • Relationships: ~100 bytes each

Query Examples

Get Entry with Tags

SELECT 
    e.*,
    GROUP_CONCAT(t.name, ', ') as tags
FROM memory_journal e
LEFT JOIN entry_tags et ON e.id = et.entry_id
LEFT JOIN tags t ON et.tag_id = t.id
WHERE e.id = ?
  AND e.deleted_at IS NULL
GROUP BY e.id;

Full-Text Search

SELECT 
    e.id,
    e.content,
    e.entry_type,
    e.timestamp,
    snippet(memory_journal_fts, 0, '**', '**', '...', 64) as snippet
FROM memory_journal_fts fts
JOIN memory_journal e ON fts.rowid = e.id
WHERE fts MATCH ?
  AND e.deleted_at IS NULL
ORDER BY rank
LIMIT ?;

Entry Relationships (Recursive)

WITH RECURSIVE related_entries(entry_id, depth) AS (
    VALUES(?, 0)
    UNION
    SELECT DISTINCT
        CASE 
            WHEN r.from_entry_id = re.entry_id THEN r.to_entry_id
            ELSE r.from_entry_id
        END,
        re.depth + 1
    FROM relationships r
    JOIN related_entries re ON 
        r.from_entry_id = re.entry_id OR r.to_entry_id = re.entry_id
    WHERE re.depth < ?
)
SELECT DISTINCT e.*, re.depth
FROM related_entries re
JOIN memory_journal e ON e.id = re.entry_id
WHERE e.deleted_at IS NULL
ORDER BY re.depth, e.timestamp DESC;

Maintenance

VACUUM

Reclaim space after deletes:

VACUUM;

When to run:

  • After many permanent deletes
  • Database size is much larger than data
  • Typically monthly or quarterly

ANALYZE

Update query planner statistics:

ANALYZE;

When to run:

  • After bulk inserts
  • After schema changes
  • Typically monthly

Note: v1.1.0 removed automatic ANALYZE from startup (too expensive).

Integrity Check

Verify database integrity:

PRAGMA integrity_check;

Index Usage

Check if indexes are being used:

EXPLAIN QUERY PLAN
SELECT * FROM memory_journal WHERE timestamp > '2025-10-01';

Backup & Restore

Backup

-- SQLite backup API
VACUUM INTO '/path/to/backup.db';

-- Or simple copy (while WAL mode handles consistency)
cp memory_journal.db backup.db

Restore

# Stop server
# Replace database file
cp backup.db memory_journal.db
# Restart server

Schema Evolution

Future Additions

Planned schema changes:

  1. Attachments table - File attachments
  2. Versions table - Entry version history
  3. Metadata table - Schema version tracking
  4. Collections table - User-defined collections

Backward Compatibility

  • Migrations preserve existing data
  • Old clients continue to work (graceful degradation)
  • New columns have defaults

Next: Check Architecture or Performance.

🏠 Home

Getting Started

Core Features

Knowledge Graph

GitHub Integration

Workflows

Deployment

Architecture

Clone this wiki locally