schema.sql

-- Memory System Schema for SQLite
-- Designed for fast read/write operations and efficient search

CREATE TABLE IF NOT EXISTS memories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    category TEXT DEFAULT 'general',
    tags TEXT DEFAULT '',  -- comma-separated tags
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    source TEXT DEFAULT 'manual'  -- manual, import, sync, agent
);

CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
CREATE INDEX IF NOT EXISTS idx_memories_is_active ON memories(is_active);

-- Full-text search: SQLite MATCH operator (no virtual table needed)
-- Use: SELECT * FROM memories WHERE content MATCH 'query' ORDER BY rank DESC

-- GitHub sync metadata table
CREATE TABLE IF NOT EXISTS github_sync (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    memory_id INTEGER REFERENCES memories(id) ON DELETE CASCADE,
    remote_hash TEXT DEFAULT '',
    last_pushed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending'  -- pending, pushed, failed
);

CREATE INDEX IF NOT EXISTS idx_github_sync_memory ON github_sync(memory_id);