supabase-postgres-vector-store-yt

KO | EN

gitlite — search

#javascript #hacktoberfest #react #ai #go #golang #deep-learning #java #nodejs #windows #awesome #vue #awesome-list #mcp

supabase-postgres-vector-store-yt

(★ 24)

No description available.

supabase-postgres-vector-store-yt Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

-- Ensure the pgvector extension is enabled.
CREATE EXTENSION IF NOT EXISTS vector;

-- Create the emails table.
CREATE TABLE emails (
id SERIAL PRIMARY KEY, -- Unique ID for each email
subject TEXT NOT NULL, -- Subject of the email
sender TEXT NOT NULL, -- Email address of the sender
recipient TEXT[] NOT NULL, -- Array of recipients
cc TEXT[], -- Optional CC recipients
bcc TEXT[], -- Optional BCC recipients
body TEXT NOT NULL, -- Full body of the email (raw content)
created_at TIMESTAMPTZ DEFAULT NOW() -- Timestamp when the email was sent or received
);

-- Create the email_sections table.
CREATE TABLE email_sections (
id SERIAL PRIMARY KEY, -- Unique ID for each section
email_id INT NOT NULL REFERENCES emails(id) ON DELETE CASCADE, -- Reference to parent email
section_content TEXT NOT NULL, -- Content of the section (chunk)
embedding VECTOR(1536), -- Embedding of the section
section_order INT, -- Order of the section in the original email
created_at TIMESTAMPTZ DEFAULT NOW() -- Timestamp for the section
);

-- Create an HNSW index on the section embeddings using the correct operator class.
CREATE INDEX section_embedding_hnsw_idx
ON email_sections USING hnsw (embedding vector_cosine_ops);

-- Function: match_filtered_email_sections
-- Description: This function retrieves relevant email sections based on a similarity search
-- using a vector embedding and allows filtering by the sender or recipient email address.

create or replace function match_filtered_email_sections(
query_embedding vector(1536), -- Input: The embedding vector generated from the user's question (1536 dimensions for ADA-002).
match_threshold float, -- Input: Minimum similarity threshold (only sections with similarity above this will be returned).
match_count int, -- Input: Maximum number of results to return.
email_address text -- Input: The email address used to filter by sender or recipient.
)
returns table (
id int, -- Output: Unique ID of the email section.
email_id int, -- Output: ID of the parent email (used to reference the full email).
section_content text, -- Output: The content of the matching email section (a chunk of the email).
similarity float -- Output: Similarity score between the query embedding and the section embedding.
)
language sql
as $$
-- Core SQL query to perform the similarity search and filter results.
select
es.id, -- Select the ID of the email section.
es.email_id, -- Select the ID of the parent email to which this section belongs.
es.section_content, -- Select the content of the section (chunk of the email).
1 - (es.embedding <-> query_embedding) as similarity -- Calculate similarity score: 1 minus the cosine distance.

from
email_sections es -- From the email sections table (contains all the email chunks with their embeddings).
join
emails e on es.email_id = e.id -- Join with the emails table to access sender and recipient information.

where
-- Filter by sender or recipient: Only retrieve sections where the sender or recipient matches the given email address.
(e.sender = email_address or e.recipient @> array[email_address])

-- Apply the similarity threshold: Only return sections with a similarity score greater than the threshold.
    and (1 - (es.embedding <-> query_embedding)) > match_threshold

order by
similarity desc -- Order the results by similarity in descending order (most similar first).

limit
least(match_count, 200); -- Limit the number of results to the smaller of match_count or 200 to prevent large queries.

$$
;

SELECT *
FROM pg_proc
WHERE proname = 'match_filtered_email_sections';

SELECT nspname AS schema_name, proname AS function_name
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE proname = 'match_filtered_email_sections';
$$

supabase-postgres-vector-store-yt

supabase-postgres-vector-store-yt Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

supabase-postgres-vector-store-yt

supabase-postgres-vector-store-yt Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README