Spaces:
Sleeping
Sleeping
File size: 3,298 Bytes
49bd31a | 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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | -- =============================================================================
-- Supabase RPC: match_courses
-- Hybrid Recommender Engine — Vector Similarity Search
-- Searches course_chunks embeddings, JOINs to parent courses table.
-- Supports SBERT, E5, and DistilBERT models via model_type parameter.
-- =============================================================================
-- Drop existing function variants (for idempotent re-runs)
DROP FUNCTION IF EXISTS match_courses(vector, float, int, int, text);
DROP FUNCTION IF EXISTS match_courses(vector, float8, int4, int4, text);
CREATE OR REPLACE FUNCTION match_courses(
query_embedding vector,
match_threshold float DEFAULT 0.3,
match_count int DEFAULT 5,
child_age_months int DEFAULT 48,
model_type text DEFAULT 'sbert'
)
RETURNS TABLE (
course_id uuid,
course_title text,
course_description text,
chunk_id uuid,
chunk_title text,
chunk_content text,
similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
IF model_type = 'e5' THEN
RETURN QUERY
SELECT
c.id AS course_id,
c.title AS course_title,
c.description AS course_description,
cc.id AS chunk_id,
cc.chunk_title,
cc.chunk_content,
(1 - (cc.embedding_e5 <=> query_embedding))::float AS similarity
FROM course_chunks cc
JOIN courses c ON cc.course_id = c.id
WHERE
c.age_min <= child_age_months
AND c.age_max >= child_age_months
AND cc.embedding_e5 IS NOT NULL
AND (1 - (cc.embedding_e5 <=> query_embedding)) > match_threshold
ORDER BY cc.embedding_e5 <=> query_embedding ASC
LIMIT match_count;
ELSIF model_type = 'distilbert' THEN
RETURN QUERY
SELECT
c.id AS course_id,
c.title AS course_title,
c.description AS course_description,
cc.id AS chunk_id,
cc.chunk_title,
cc.chunk_content,
(1 - (cc.embedding_distilbert <=> query_embedding))::float AS similarity
FROM course_chunks cc
JOIN courses c ON cc.course_id = c.id
WHERE
c.age_min <= child_age_months
AND c.age_max >= child_age_months
AND cc.embedding_distilbert IS NOT NULL
AND (1 - (cc.embedding_distilbert <=> query_embedding)) > match_threshold
ORDER BY cc.embedding_distilbert <=> query_embedding ASC
LIMIT match_count;
ELSE
-- Default: SBERT
RETURN QUERY
SELECT
c.id AS course_id,
c.title AS course_title,
c.description AS course_description,
cc.id AS chunk_id,
cc.chunk_title,
cc.chunk_content,
(1 - (cc.embedding_sbert <=> query_embedding))::float AS similarity
FROM course_chunks cc
JOIN courses c ON cc.course_id = c.id
WHERE
c.age_min <= child_age_months
AND c.age_max >= child_age_months
AND cc.embedding_sbert IS NOT NULL
AND (1 - (cc.embedding_sbert <=> query_embedding)) > match_threshold
ORDER BY cc.embedding_sbert <=> query_embedding ASC
LIMIT match_count;
END IF;
END;
$$;
|