init-vector.sql

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

-- Create the table if it doesn't exist
CREATE TABLE IF NOT EXISTS "km-default" (
    id TEXT PRIMARY KEY,
    embedding VECTOR(768),
    tags TEXT[] NOT NULL DEFAULT '{}',
    content TEXT NOT NULL DEFAULT '',
    payload JSONB NOT NULL DEFAULT '{}'
);

-- GIN index on tags for fast filtering
CREATE INDEX IF NOT EXISTS idx_tags ON "km-default" USING gin (tags);

-- HNSW index on embedding for fast similarity search (works well on small and large datasets)
CREATE INDEX IF NOT EXISTS km_default_embedding_idx
ON "km-default"
USING hnsw (embedding vector_l2_ops);

-- Optional: JSONB indexes for faster filtering
CREATE INDEX IF NOT EXISTS km_default_payload_userid_idx
ON "km-default" ((payload->>'userId'));

CREATE INDEX IF NOT EXISTS km_default_payload_collection_idx
ON "km-default" ((payload->>'collectionName'));