init.sql

-- Initialize database for Mem0 with pgvector support
-- This script sets up the database schema for Gemini embeddings (768 dimensions)

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create the memories table with proper embedding dimensions for Gemini
CREATE TABLE IF NOT EXISTS memories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255),
    agent_id VARCHAR(255),
    run_id VARCHAR(255),
    memory TEXT NOT NULL,
    hash VARCHAR(255) UNIQUE,
    metadata JSONB DEFAULT '{}',
    embedding vector(768), -- Gemini text-embedding-004 uses 768 dimensions
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_memories_user_id ON memories(user_id);
CREATE INDEX IF NOT EXISTS idx_memories_agent_id ON memories(agent_id);
CREATE INDEX IF NOT EXISTS idx_memories_run_id ON memories(run_id);
CREATE INDEX IF NOT EXISTS idx_memories_hash ON memories(hash);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);

-- Create vector similarity index using HNSW for fast similarity search
CREATE INDEX IF NOT EXISTS idx_memories_embedding_hnsw 
ON memories USING hnsw (embedding vector_cosine_ops);

-- Create function to update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to automatically update the updated_at column
DROP TRIGGER IF EXISTS update_memories_updated_at ON memories;
CREATE TRIGGER update_memories_updated_at
    BEFORE UPDATE ON memories
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON TABLE memories TO postgres;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO postgres;

-- Insert a test record to verify the setup
INSERT INTO memories (user_id, memory, hash, metadata) 
VALUES (
    'system', 
    'Database initialized successfully with Gemini embedding support (768 dimensions)', 
    'init_test_' || extract(epoch from now())::text,
    '{"type": "system", "initialization": true}'
) ON CONFLICT (hash) DO NOTHING;

-- Display table info
\d+ memories;

-- Show that pgvector is properly installed
SELECT * FROM pg_extension WHERE extname = 'vector';

PRINT 'Database initialization completed successfully!';
PRINT 'Mem0 is ready to use with Gemini embeddings (768 dimensions)';