Spaces:

PranilB
/

Question_Answering_Classification

Sleeping

Question_Answering_Classification / pdf_to_embedding /supabase_schema.sql

Pranil Bankar

Deploy Application

4d67ec7 5 months ago

2.03 kB

-- 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;
$$;

	-- 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;
	$$;