File size: 2,028 Bytes
4d67ec7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- Enable the pgvector extension to work with embedding vectors
create extension if not exists vector;
-- Create the knowledge base table to store document chunks and embeddings
create table if not exists ncert_knowledge_chunks (
id uuid primary key default gen_random_uuid(),
chunk_id text not null unique, -- the custom ID from your JSON (e.g., bio_ch1_1_0001)
subject text not null, -- 'Biology'
chapter text not null, -- 'The Living World'
chapter_number text not null, -- '1'
section text, -- '1.1'
section_title text, -- 'What is Living?'
text_content text not null, -- the actual raw text chunk
metadata jsonb default '{}'::jsonb, -- for any extra metadata
embedding vector(384), -- BGE-small-en-v1.5 produces 384-dim vectors
created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
-- HNSW index for fast approximate nearest-neighbour cosine search
create index on ncert_knowledge_chunks using hnsw (embedding vector_cosine_ops);
-- ==========================================
-- Helper function for similarity search
-- ==========================================
create or replace function match_ncert_chunks (
query_embedding vector(384),
match_threshold float,
match_count int,
filter_subject text default null,
filter_chapter text default null
)
returns table (
id uuid,
chunk_id text,
chapter text,
section text,
section_title text,
text_content text,
similarity float
)
language sql stable
as $$
select
id,
chunk_id,
chapter,
section,
section_title,
text_content,
1 - (embedding <=> query_embedding) as similarity
from ncert_knowledge_chunks
where 1 - (embedding <=> query_embedding) > match_threshold
and (filter_subject is null or subject = filter_subject)
and (filter_chapter is null or chapter = filter_chapter)
order by embedding <=> query_embedding
limit match_count;
$$;
|