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
|
drop index if exists memories_embedding_idx;
alter table public.memories
drop column if exists embedding;
alter table public.memories
add column embedding vector,
add column embedding_dimensions integer;
create index memories_embedding_idx on public.memories using hnsw (embedding vector_cosine_ops);
drop function if exists public.search_memories(vector(1536), float, int, uuid, uuid);
create or replace function public.search_memories(
query_embedding vector,
match_threshold float default 0.7,
match_count int default 10,
filter_project_id uuid default null,
filter_folder_id uuid default null,
required_dimensions int default null
)
returns table (
id uuid,
content text,
project_id uuid,
folder_id uuid,
tags jsonb,
metadata jsonb,
similarity float,
created_at timestamptz,
updated_at timestamptz
)
language plpgsql
security definer
as $$
declare
query_dimensions int;
begin
query_dimensions := array_length(query_embedding::float[], 1);
return query
select
m.id,
m.content,
m.project_id,
m.folder_id,
m.tags,
m.metadata,
1 - (m.embedding <=> query_embedding) as similarity,
m.created_at,
m.updated_at
from public.memories m
where m.user_id = auth.uid()
and m.embedding is not null
and m.embedding_dimensions = query_dimensions
and 1 - (m.embedding <=> query_embedding) > match_threshold
and (filter_project_id is null or m.project_id = filter_project_id)
and (filter_folder_id is null or m.folder_id = filter_folder_id)
order by m.embedding <=> query_embedding
limit match_count;
end;
$$;
|