openai/gpt-5.6-luna · d2_pgvector · rep 1

set -e
printf 'URL=%s\nREF=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF"
command -v psql || true
command -v curl || true
[ -n "$SUPABASE_DB_URL" ] && echo DB_URL_SET || true

URL=https://<ref>.supabase.co
REF=<ref>
/usr/bin/psql
/usr/bin/curl
DB_URL_SET

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select id,title,embedding_json from public.documents order by id;" -c "select extname from pg_extension where extname='vector';"

 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)

 extname 
---------
(0 rows)

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS embedding vector(4);
UPDATE public.documents
SET embedding = (embedding_json::text)::vector(4)
WHERE embedding_json IS NOT NULL;
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_cosine_idx
  ON public.documents USING hnsw (embedding vector_cosine_ops);
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
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT d.id, d.title, (d.embedding <=> query_embedding)::float8 AS distance
  FROM public.documents AS d
  WHERE d.embedding IS NOT NULL
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;
REVOKE ALL ON FUNCTION public.match_documents(vector(4), int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated;
COMMIT;
SQL

echo '--- verify schema/index/function ---'
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select column_name,data_type,udt_name from information_schema.columns where table_schema='public' and table_name='documents' and column_name='embedding';" -c "select indexname,indexdef from pg_indexes where schemaname='public' and tablename='documents' and indexname='documents_embedding_hnsw_cosine_idx';" -c "select routine_name, routine_schema from information_schema.routines where routine_schema='public' and routine_name='match_documents';"

BEGIN
CREATE EXTENSION
ALTER TABLE
UPDATE 6
CREATE INDEX
CREATE FUNCTION
REVOKE
GRANT
COMMIT
--- verify schema/index/function ---
 column_name |  data_type   | udt_name 
-------------+--------------+----------
 embedding   | USER-DEFINED | vector
(1 row)

              indexname              |                                                   indexdef                                                    
-------------------------------------+---------------------------------------------------------------------------------------------------------------
 documents_embedding_hnsw_cosine_idx | CREATE INDEX documents_embedding_hnsw_cosine_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(1 row)

  routine_name   | routine_schema 
-----------------+----------------
 match_documents | public
(1 row)

set -e
endpoint="$SUPABASE_URL/rest/v1/rpc/match_documents"
body='{"query_embedding":"[10,0.5,0,0]","match_count":3}'
resp=$(curl -sS -w '\nHTTP_STATUS:%{http_code}' -X POST "$endpoint" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "$body")
printf '%s\n' "$resp"

[{"id":1,"title":"alpha","distance":0}, 
 {"id":2,"title":"bravo","distance":0.0367087035723488}, 
 {"id":3,"title":"delta","distance":0.636791795933452}]
HTTP_STATUS:200