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

env | grep -i supabase | sed 's/=.*/=<set>/'; echo "---"; which psql || echo "no psql"

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>
---
/usr/bin/psql

psql "$SUPABASE_DB_URL" -c "select id, title, embedding_json from public.documents order by id;" 2>&1 | head -30

 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' 2>&1
-- 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 = (
  ARRAY(SELECT jsonb_array_elements_text(embedding_json)::float4)
)::vector(4);

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

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

CREATE EXTENSION
ALTER TABLE
UPDATE 6
CREATE INDEX
CREATE FUNCTION

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
GRANT EXECUTE ON FUNCTION public.match_documents(vector, int) TO anon, authenticated, service_role;
-- ensure table is readable (for SECURITY INVOKER function)
GRANT SELECT ON public.documents TO anon, authenticated;
-- reload PostgREST schema cache
NOTIFY pgrst, 'reload schema';
SQL
echo "--- verify column populated ---"
psql "$SUPABASE_DB_URL" -c "select id, title, embedding from public.documents order by id;" 2>&1

GRANT
GRANT
NOTIFY
--- verify column populated ---
 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)

echo "=== SQL direct: query [1,0,0,0] (points along x-axis) ==="
psql "$SUPABASE_DB_URL" -c "select * from public.match_documents('[1,0,0,0]'::vector(4), 6);" 2>&1

echo
echo "=== REST RPC with anon key ==="
sleep 2
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":[1,0,0,0],"match_count":3}'
echo

=== SQL direct: query [1,0,0,0] (points along x-axis) ===
 id |  title  |      distance       
----+---------+---------------------
  1 | alpha   | 0.00124766112215546
  2 | bravo   |  0.0513167145152487
  3 | delta   |   0.683772233983162
  6 | golf    |    0.98000799508818
  4 | echo    |                   1
  5 | foxtrot |    1.99980005540512
(6 rows)


=== REST RPC with anon key ===
[{"id":1,"title":"alpha","distance":0.00124766112215546}, 
 {"id":2,"title":"bravo","distance":0.0513167145152487}, 
 {"id":3,"title":"delta","distance":0.683772233983162}]

echo "=== query [0,0,1,1] should rank golf (0.1,0.1,3,4) 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":[0,0,1,1],"match_count":2}'
echo
echo
echo "=== query [-1,0,0,0] should rank foxtrot (negative x) 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":[-1,0,0,0],"match_count":2}'
echo

=== query [0,0,1,1] should rank golf (0.1,0.1,3,4) nearest ===
[{"id":6,"title":"golf","distance":0.0104462577590937}, 
 {"id":2,"title":"bravo","distance":1}]

=== query [-1,0,0,0] should rank foxtrot (negative x) nearest ===
[{"id":5,"title":"foxtrot","distance":0.000199944594884593}, 
 {"id":4,"title":"echo","distance":1}]