openai/gpt-5.6-sol · d2_pgvector · rep 2

set -eu
command -v psql || true
printf 'SUPABASE_URL=%s\n' "${SUPABASE_URL:+set}"
printf 'SUPABASE_PROJECT_REF=%s\n' "${SUPABASE_PROJECT_REF:+set}"
printf 'SUPABASE_ANON_KEY=%s\n' "${SUPABASE_ANON_KEY:+set}"
printf 'SUPABASE_SERVICE_ROLE_KEY=%s\n' "${SUPABASE_SERVICE_ROLE_KEY:+set}"
printf 'SUPABASE_DB_URL=%s\n' "${SUPABASE_DB_URL:+set}"
printf 'SUPABASE_DB_PASSWORD=%s\n' "${SUPABASE_DB_PASSWORD:+set}"

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

set -euo pipefail
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select id, title, embedding_json from public.documents order by 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)

set -euo pipefail
export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

ALTER TABLE public.documents
  ADD COLUMN IF NOT EXISTS embedding extensions.vector(4);

UPDATE public.documents
SET embedding = embedding_json::text::extensions.vector
WHERE embedding IS DISTINCT FROM embedding_json::text::extensions.vector;

ALTER TABLE public.documents
  ALTER COLUMN embedding SET NOT NULL;

CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_cosine_idx
  ON public.documents
  USING hnsw (embedding extensions.vector_cosine_ops);

CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding extensions.vector(4),
  match_count integer
)
RETURNS TABLE (
  id integer,
  title text,
  distance double precision
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public, extensions
AS $function$
  SELECT d.id,
         d.title,
         (d.embedding <=> query_embedding)::double precision AS distance
  FROM public.documents AS d
  WHERE d.embedding IS NOT NULL
  ORDER BY d.embedding <=> query_embedding, d.id
  LIMIT GREATEST(COALESCE(match_count, 0), 0);
$function$;

REVOKE ALL ON FUNCTION public.match_documents(extensions.vector, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector, integer) TO anon;
GRANT USAGE ON SCHEMA public TO anon;

NOTIFY pgrst, 'reload schema';
SQL

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "SELECT * FROM public.match_documents('[1,0,0,0]'::extensions.vector, 6);"

CREATE EXTENSION
ALTER TABLE
UPDATE 6
ALTER TABLE
CREATE INDEX
CREATE FUNCTION
REVOKE
GRANT
GRANT
NOTIFY
 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)

set -euo pipefail
url="${SUPABASE_URL%/}/rest/v1/rpc/match_documents"
body='{"query_embedding":"[1,0,0,0]","match_count":3}'
for attempt in 1 2 3 4 5; do
  code=$(curl -sS -o /tmp/match_documents_response.json -w '%{http_code}' \
    -X POST "$url" \
    -H "apikey: $SUPABASE_ANON_KEY" \
    -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
    -H 'Content-Type: application/json' \
    -d "$body")
  if [ "$code" = 200 ]; then break; fi
  sleep 2
done
printf 'HTTP %s\n' "$code"
cat /tmp/match_documents_response.json
printf '\n'
test "$code" = 200

export PGPASSWORD="$SUPABASE_DB_PASSWORD"
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';
SELECT column_name, udt_schema, udt_name, is_nullable
FROM information_schema.columns
WHERE table_schema='public' AND table_name='documents' AND column_name='embedding';
SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname='public' AND tablename='documents' AND indexname='documents_embedding_hnsw_cosine_idx';
SELECT count(*) AS total, count(embedding) AS populated FROM public.documents;
SQL

HTTP 200
[{"id":1,"title":"alpha","distance":0.00124766112215546}, 
 {"id":2,"title":"bravo","distance":0.0513167145152487}, 
 {"id":3,"title":"delta","distance":0.683772233983162}]
 extname | extversion 
---------+------------
 vector  | 0.8.2
(1 row)

 column_name | udt_schema | udt_name | is_nullable 
-------------+------------+----------+-------------
 embedding   | extensions | vector   | NO
(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)

 total | populated 
-------+-----------
     6 |         6
(1 row)