claude-opus-4-8 · d2_pgvector · rep 3

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "DB_URL set: ${SUPABASE_DB_URL:+yes}"; which psql || echo "no psql"

URL: https://<ref>.supabase.co
REF: <ref>
DB_URL set: yes
/usr/bin/psql

psql "$SUPABASE_DB_URL" -c "\d public.documents" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;"

                 Table "public.documents"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 id             | integer |           | not null | 
 title          | text    |           | not null | 
 embedding_json | jsonb   |           | not null | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

 id |  title  |  embedding_json  
----+---------+------------------
  1 | alpha   | [10, 0.5, 0, 0]
  2 | bravo   | [0.9, 0.3, 0, 0]
  3 | delta   | [20, 60, 0, 0]
  4 | echo    | [0, 1, 0, 0]
  5 | foxtrot | [-5, 0.1, 0, 0]
  6 | golf    | [0.1, 0.1, 3, 4]
(6 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
-- 1) pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- 2) add embedding column and populate from embedding_json
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS embedding vector(4);
UPDATE public.documents
SET embedding = (
  SELECT ('[' || string_agg(elem::text, ',' ORDER BY ord) || ']')::vector(4)
  FROM jsonb_array_elements_text(embedding_json) WITH ORDINALITY AS t(elem, ord)
);

-- 3) HNSW index using cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_cosine
  ON public.documents USING hnsw (embedding vector_cosine_ops);
SQL

CREATE EXTENSION
ALTER TABLE
UPDATE 6
CREATE INDEX

psql "$SUPABASE_DB_URL" <<'SQL'
SELECT id, title, embedding FROM public.documents ORDER BY id;

-- 4) match_documents function
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding vector(4),
  match_count int
)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
AS $$
  SELECT d.id, d.title, (d.embedding <=> query_embedding)::float8 AS distance
  FROM public.documents d
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;

-- expose to REST/RPC via anon & authenticated roles
GRANT EXECUTE ON FUNCTION public.match_documents(vector, int) TO anon, authenticated, service_role;
SQL

 id |  title  |   embedding   
----+---------+---------------
  1 | alpha   | [10,0.5,0,0]
  2 | bravo   | [0.9,0.3,0,0]
  3 | delta   | [20,60,0,0]
  4 | echo    | [0,1,0,0]
  5 | foxtrot | [-5,0.1,0,0]
  6 | golf    | [0.1,0.1,3,4]
(6 rows)

CREATE FUNCTION
GRANT

psql "$SUPABASE_DB_URL" -c "NOTIFY pgrst, 'reload schema';"
sleep 3
# Test: query near "alpha" [10,0.5,0,0] — expect alpha/bravo (same direction) nearest
curl -s -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding":[9,0.4,0,0],"match_count":3}'
echo
echo "--- query near golf [0.1,0.1,3,4] ---"
curl -s -X POST "$SUPABASE_URL/rest/v1/rpc/match_documents" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding":[0,0,3,4],"match_count":3}'
echo

NOTIFY
[{"id":1,"title":"alpha","distance":1.54197789659083e-05}, 
 {"id":2,"title":"bravo","distance":0.0382116697297589}, 
 {"id":3,"title":"delta","distance":0.641961983273754}]
--- query near golf [0.1,0.1,3,4] ---
[{"id":6,"title":"golf","distance":0.000399769304185948}, 
 {"id":1,"title":"alpha","distance":1}, 
 {"id":2,"title":"bravo","distance":1}]