database_schema.sql

-- sessions.sqlite
-- SQLite Schema for MVP Persistence Layer

CREATE TABLE sessions (
    session_id TEXT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    context_data BLOB,  -- Compressed JSON
    user_metadata TEXT
);

CREATE TABLE interactions (
    interaction_id TEXT PRIMARY KEY,
    session_id TEXT REFERENCES sessions(session_id),
    user_input TEXT NOT NULL,
    agent_trace TEXT,  -- JSON array of agent executions
    final_response TEXT,
    processing_time INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE embeddings (
    embedding_id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT,
    content_text TEXT,
    embedding_vector BLOB,  -- FAISS-compatible
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);