claude-sonnet-5 · d2_pgvector · rep 1

env | grep -i supabase | sed 's/=.*KEY.*/=<redacted>/'

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

which psql; psql --version 2>/dev/null; echo "---"; which curl jq

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
/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) Enable pgvector
create extension if not exists vector;

-- 2) Add embedding column of type vector(4), populated from embedding_json
alter table public.documents
  add column if not exists embedding vector(4);

update public.documents
set embedding = (
  select array_agg(x::float4 order by ord)::vector(4)
  from jsonb_array_elements_text(embedding_json) with ordinality as t(x, ord)
)
where embedding is null;

-- verify
select id, title, embedding_json, embedding from public.documents order by id;
SQL

CREATE EXTENSION
ALTER TABLE
UPDATE 6
 id |  title  |  embedding_json  |   embedding   
----+---------+------------------+---------------
  1 | alpha   | [10, 0.5, 0, 0]  | [10,0.5,0,0]
  2 | bravo   | [0.9, 0.3, 0, 0] | [0.9,0.3,0,0]
  3 | delta   | [20, 60, 0, 0]   | [20,60,0,0]
  4 | echo    | [0, 1, 0, 0]     | [0,1,0,0]
  5 | foxtrot | [-5, 0.1, 0, 0]  | [-5,0.1,0,0]
  6 | golf    | [0.1, 0.1, 3, 4] | [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_idx
  on public.documents
  using hnsw (embedding vector_cosine_ops);

-- Make embedding column not null now that it's populated (optional but good practice)
alter table public.documents alter column embedding set not null;

-- 4) match_documents RPC 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;
$$;

-- Grant execute to anon/authenticated roles so PostgREST can expose it via RPC
grant execute on function public.match_documents(vector, int) to anon, authenticated, service_role;
SQL

CREATE INDEX
ALTER TABLE
CREATE FUNCTION
GRANT

echo "--- direct SQL test: query near 'alpha' [10,0.5,0,0] ---"
psql "$SUPABASE_DB_URL" -c "select * from public.match_documents('[10,0.6,0,0]'::vector(4), 3);"

echo "--- direct SQL test: query near 'golf' [0.1,0.1,3,4] ---"
psql "$SUPABASE_DB_URL" -c "select * from public.match_documents('[0,0,3,4]'::vector(4), 3);"

--- direct SQL test: query near 'alpha' [10,0.5,0,0] ---
 id | title |      distance       
----+-------+---------------------
  1 | alpha | 4.9670255464318e-05
  2 | bravo |  0.0340800878074974
  3 | delta |   0.627521096928223
(3 rows)

--- direct SQL test: query near 'golf' [0.1,0.1,3,4] ---
 id | title |       distance       
----+-------+----------------------
  6 | golf  | 0.000399769304185948
  1 | alpha |                    1
  2 | bravo |                    1
(3 rows)

# Ask PostgREST to reload schema cache via NOTIFY (works on Supabase-hosted Postgres)
psql "$SUPABASE_DB_URL" -c "NOTIFY pgrst, 'reload schema';"
sleep 3
echo "done notifying"

NOTIFY
done notifying

echo "=== RPC call: query near alpha ==="
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.6, 0, 0], "match_count": 3}' | jq .

echo "=== RPC call: query near golf ==="
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": 6}' | jq .

=== RPC call: query near alpha ===
[
  {
    "id": 1,
    "title": "alpha",
    "distance": 4.9670255464318e-05
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 0.0340800878074974
  },
  {
    "id": 3,
    "title": "delta",
    "distance": 0.627521096928223
  }
]
=== RPC call: query near golf ===
[
  {
    "id": 6,
    "title": "golf",
    "distance": 0.000399769304185948
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 1
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 1
  },
  {
    "id": 3,
    "title": "delta",
    "distance": 1
  },
  {
    "id": 4,
    "title": "echo",
    "distance": 1
  },
  {
    "id": 5,
    "title": "foxtrot",
    "distance": 1
  }
]

psql "$SUPABASE_DB_URL" -c "\d public.documents"
echo "--- RLS status ---"
psql "$SUPABASE_DB_URL" -c "select relrowsecurity, relforcerowsecurity from pg_class where oid = 'public.documents'::regclass;"
echo "--- index check ---"
psql "$SUPABASE_DB_URL" -c "select indexname, indexdef from pg_indexes where tablename='documents';"
echo "--- explain plan (index usage on larger match_count) ---"
psql "$SUPABASE_DB_URL" -c "explain select * from public.documents order by embedding <=> '[10,0.6,0,0]'::vector(4) limit 3;"

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

--- RLS status ---
 relrowsecurity | relforcerowsecurity 
----------------+---------------------
 f              | f
(1 row)

--- index check ---
              indexname              |                                                   indexdef                                                    
-------------------------------------+---------------------------------------------------------------------------------------------------------------
 documents_pkey                      | CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)
 documents_embedding_hnsw_cosine_idx | CREATE INDEX documents_embedding_hnsw_cosine_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(2 rows)

--- explain plan (index usage on larger match_count) ---
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Limit  (cost=1.15..1.16 rows=3 width=108)
   ->  Sort  (cost=1.15..1.17 rows=6 width=108)
         Sort Key: ((embedding <=> '[10,0.6,0,0]'::vector(4)))
         ->  Seq Scan on documents  (cost=0.00..1.07 rows=6 width=108)
(4 rows)