init-pgvector.sql

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

-- Create the langchain_pg_embedding table if it doesn't exist
-- This table is used by LangChain's PGVector integration
CREATE TABLE IF NOT EXISTS langchain_pg_embedding (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    collection_id UUID,
    embedding VECTOR,
    document TEXT,
    cmetadata JSONB,
    custom_id TEXT,
    uuid UUID DEFAULT gen_random_uuid()
);

-- Create the langchain_pg_collection table if it doesn't exist  
CREATE TABLE IF NOT EXISTS langchain_pg_collection (
    uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL UNIQUE,
    cmetadata JSONB
);

-- Create the documents table for the main vector store
CREATE TABLE IF NOT EXISTS documents (
    langchain_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT,
    embedding VECTOR,
    lanchaine_metadata JSONB
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS ix_langchain_pg_embedding_collection_id ON langchain_pg_embedding (collection_id);
CREATE INDEX IF NOT EXISTS ix_langchain_pg_collection_name ON langchain_pg_collection (name);
CREATE INDEX IF NOT EXISTS ix_documents_custom_id ON documents (custom_id);
CREATE INDEX IF NOT EXISTS ix_documents_cmetadata ON documents USING GIN (cmetadata);

-- Add index for user_id in metadata for faster filtering
CREATE INDEX IF NOT EXISTS idx_documents_user_id
ON documents USING btree ((langchain_metadata->>'user_id'));

-- Create vector similarity search indexes (using HNSW for better performance)
-- Note: These will be created automatically when embeddings are first added with the correct dimensions
-- Common dimensions: 384 (all-MiniLM-L6-v2), 768 (all-mpnet-base-v2), 1536 (OpenAI)

-- Grant necessary permissions to the lodestone user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO lodestone;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO lodestone;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO lodestone;
GRANT USAGE ON SCHEMA public TO lodestone;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO lodestone;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO lodestone;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO lodestone;

-- Ensure the lodestone user can create extensions and access vector functions
GRANT CREATE ON SCHEMA public TO lodestone;

-- Additional performance settings for vector operations
-- These are optional but can improve performance for vector similarity searches
-- ALTER SYSTEM SET shared_preload_libraries = 'vector';
-- ALTER SYSTEM SET max_connections = 200;
-- ALTER SYSTEM SET shared_buffers = '256MB';
-- ALTER SYSTEM SET effective_cache_size = '1GB';
-- ALTER SYSTEM SET random_page_cost = 1.1;

-- Create a helper function to check vector extension status
CREATE OR REPLACE FUNCTION check_vector_extension()
RETURNS TEXT AS $
BEGIN
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
        RETURN 'vector extension is installed and ready';
    ELSE
        RETURN 'vector extension is not installed';
    END IF;
END;
$ LANGUAGE plpgsql;