SQL

CREATE TABLE memories  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'user',
  source_context TEXT,
  tags TEXT DEFAULT '[]',
  importance REAL DEFAULT 0.5,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  frame_id INTEGER REFERENCES frames(id) ON DELETE SET NULL
)

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ read-only
content TEXT read-only
source TEXT read-only
source_context TEXT ✓ read-only
tags TEXT ✓ read-only
importance REAL ✓ read-only
created_at TEXT read-only
updated_at TEXT read-only
frame_id INTEGER ✓ read-only

Foreign Keys

Column Destination
frame_id frames.id

Indexes

Name Columns Unique SQL Drop?
idx_memories_created_at created_at SQL 
CREATE INDEX idx_memories_created_at
ON memories(created_at)
read-only
idx_memories_frame_id frame_id SQL 
CREATE INDEX idx_memories_frame_id
ON memories(frame_id)
read-only
idx_memories_importance importance SQL 
CREATE INDEX idx_memories_importance
ON memories(importance DESC)
read-only
idx_memories_source source SQL 
CREATE INDEX idx_memories_source
ON memories(source)
read-only

Triggers

Name SQL Drop?
memories_ai SQL 
CREATE TRIGGER memories_ai AFTER INSERT ON memories
WHEN NEW.content IS NOT NULL AND NEW.content != ''
BEGIN
    INSERT INTO memories_fts(rowid, content, tags)
    VALUES (NEW.id, NEW.content, COALESCE(NEW.tags, ''));
END
read-only
memories_ad SQL 
CREATE TRIGGER memories_ad AFTER DELETE ON memories
WHEN OLD.content IS NOT NULL AND OLD.content != ''
BEGIN
    INSERT INTO memories_fts(memories_fts, rowid, content, tags)
    VALUES ('delete', OLD.id, OLD.content, COALESCE(OLD.tags, ''));
END
read-only
memories_au SQL 
CREATE TRIGGER memories_au AFTER UPDATE ON memories
WHEN OLD.content IS NOT NULL AND OLD.content != ''
BEGIN
    INSERT INTO memories_fts(memories_fts, rowid, content, tags)
    VALUES ('delete', OLD.id, OLD.content, COALESCE(OLD.tags, ''));
    INSERT INTO memories_fts(rowid, content, tags)
    VALUES (NEW.id, COALESCE(NEW.content, ''), COALESCE(NEW.tags, ''));
END
read-only