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

env | grep -i supabase | sed 's/\(KEY=\).*/\1***REDACTED***/; s/\(PASSWORD=\).*/\1***REDACTED***/; s/\(DB_URL=\).*/\1***REDACTED***/'

SUPABASE_DB_PASSWORD=***REDACTED***
SUPABASE_DB_URL=***REDACTED***
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=***REDACTED***
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=***REDACTED***

which psql || echo "no psql"; which curl || echo "no curl"; which jq || echo "no jq"

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

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 (Supabase recommends the 'extensions' schema)
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

-- 2) Add embedding column populated from embedding_json
ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS embedding extensions.vector(4);

UPDATE public.documents
SET embedding = (
  '[' ||
  (embedding_json->>0) || ',' ||
  (embedding_json->>1) || ',' ||
  (embedding_json->>2) || ',' ||
  (embedding_json->>3) ||
  ']'
)::extensions.vector(4);

-- verify populated
SELECT id, title, embedding FROM public.documents ORDER BY id;
SQL

CREATE EXTENSION
ALTER TABLE
UPDATE 6
 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)

psql "$SUPABASE_DB_URL" <<'SQL'
-- 3) HNSW index using cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_cosine
  ON public.documents
  USING hnsw (embedding extensions.vector_cosine_ops);

-- 4) match_documents function
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding extensions.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;
$$;

-- Ensure REST/RPC access
GRANT EXECUTE ON FUNCTION public.match_documents(extensions.vector(4), int) TO anon, authenticated, service_role;
SQL

CREATE INDEX
CREATE FUNCTION
GRANT

psql "$SUPABASE_DB_URL" -c "NOTIFY pgrst, 'reload schema';"
sleep 3
# Query embedding near doc 1 'alpha' [10,0.5,0,0] direction
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":[10,0.5,0,0],"match_count":3}' | jq .

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

# Query near golf [0.1,0.1,3,4] direction
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}' | jq .
echo "--- near echo [0,1,0,0] ---"
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,1,0,0],"match_count":6}' | jq .

[
  {
    "id": 6,
    "title": "golf",
    "distance": 0.000399769304185948
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 1
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 1
  }
]
--- near echo [0,1,0,0] ---
[
  {
    "id": 4,
    "title": "echo",
    "distance": 0
  },
  {
    "id": 3,
    "title": "delta",
    "distance": 0.0513167019494862
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 0.683772217228812
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 0.950062383056108
  },
  {
    "id": 5,
    "title": "foxtrot",
    "distance": 0.980003998593934
  },
  {
    "id": 6,
    "title": "golf",
    "distance": 0.98000799508818
  }
]

psql "$SUPABASE_DB_URL" <<'SQL'
SELECT indexname, indexdef FROM pg_indexes WHERE tablename='documents' AND indexname LIKE '%hnsw%';
SELECT p.proname, pg_get_function_result(p.oid) AS returns
FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname='public' AND p.proname='match_documents';
SQL

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

     proname     |                         returns                          
-----------------+----------------------------------------------------------
 match_documents | TABLE(id integer, title text, distance double precision)
(1 row)