env | grep -i supabase
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 "$SUPABASE_DB_URL" -c "SELECT version();" 2>&1 | head -20
/usr/bin/psql
version
------------------------------------------------------------------------------
PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)
psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;" 2>&1 | head -40 echo "---count---" psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.documents;" 2>&1
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)
---count---
count
-------
6
(1 row)
psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
-- 1) Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
-- 2) Add vector(4) column populated from embedding_json (cast jsonb array -> float4 vector)
ALTER TABLE public.documents
ADD COLUMN IF NOT EXISTS embedding vector(4);
UPDATE public.documents
SET embedding = (
SELECT vector
FROM (
SELECT string_agg((value::text)::float8::text, ',') AS vecstr
FROM jsonb_array_elements(embedding_json)
) sub,
LATERAL (SELECT sub.vecstr::vector(4)) AS vector
)
WHERE embedding IS NULL;
-- sanity check
SELECT id, title, embedding FROM public.documents ORDER BY id;
SQLCREATE EXTENSION
ALTER TABLE
ERROR: column "embedding" is of type vector but expression is of type record
LINE 2: SET embedding = (
^
HINT: You will need to rewrite or cast the expression.
id | title | embedding
----+---------+-----------
1 | alpha |
2 | bravo |
3 | delta |
4 | echo |
5 | foxtrot |
6 | golf |
(6 rows)
psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
-- Populate vector column from jsonb array: build "[a,b,c,d]" text then cast to vector(4)
UPDATE public.documents
SET embedding = (
'[' || string_agg((value::text)::float8::text, ',') || ']'
)::vector(4)
FROM jsonb_array_elements(embedding_json) AS el(value)
WHERE public.documents.id = public.documents.id
AND embedding IS NULL
GROUP BY public.documents.id;
-- simpler, robust per-row update using a correlated approach
UPDATE public.documents d
SET embedding = (
SELECT ('[' || string_agg((v::text)::float8::text, ',') || ']')::vector(4)
FROM jsonb_array_elements(d.embedding_json) AS t(v)
)
WHERE d.embedding IS NULL;
SELECT id, title, embedding FROM public.documents ORDER BY id;
SQLERROR: syntax error at or near "GROUP"
LINE 8: GROUP BY public.documents.id;
^
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' 2>&1 -- 3) HNSW index on embedding using cosine distance CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx ON public.documents USING hnsw (embedding vector_cosine_ops); -- confirm index exists SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents'; SQL
CREATE INDEX
indexname | indexdef
------------------------------+--------------------------------------------------------------------------------------------------------
documents_pkey | CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)
documents_embedding_hnsw_idx | CREATE INDEX documents_embedding_hnsw_idx ON public.documents USING hnsw (embedding vector_cosine_ops)
(2 rows)
psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
-- 4) SQL function for semantic search (cosine distance, nearest first)
CREATE OR REPLACE FUNCTION public.match_documents(
query_embedding vector(4),
match_count int DEFAULT 5
)
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;
$$;
-- Allow anon (and authenticated) to call it via REST RPC
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated;
-- Verify signature
\df+ public.match_documents
SQLCREATE FUNCTION
GRANT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Leakproof? | Access privileges | Language | Internal name | Description
--------+-----------------+----------------------------------------------------------+-------------------------------------------------------+------+------------+----------+----------+----------+------------+--------------------------+----------+---------------+-------------
public | match_documents | TABLE(id integer, title text, distance double precision) | query_embedding vector, match_count integer DEFAULT 5 | func | stable | unsafe | postgres | invoker | no | =X/postgres +| sql | |
| | | | | | | | | | postgres=X/postgres +| | |
| | | | | | | | | | anon=X/postgres +| | |
| | | | | | | | | | authenticated=X/postgres+| | |
| | | | | | | | | | service_role=X/postgres | | |
(1 row)
# Test RPC endpoint with anon key. Query with alpha's embedding [10,0.5,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":[10,0.5,0,0], "match_count":6}' 2>&1
echo
echo "--- HTTP status check ---"[{"id":1,"title":"alpha","distance":0},
{"id":2,"title":"bravo","distance":0.0367087035723488},
{"id":3,"title":"delta","distance":0.636791795933452},
{"id":4,"title":"echo","distance":0.950062383056108},
{"id":6,"title":"golf","distance":0.979034586516748},
{"id":5,"title":"foxtrot","distance":1.99755410633902}]
--- HTTP status check ---
echo "=== RPC with golf embedding (expect golf 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.1,0.1,3,4], "match_count":3}'
echo
echo
echo "=== Query plan (should use HNSW index) ==="
psql "$SUPABASE_DB_URL" -c "EXPLAIN SELECT id, title, embedding <=> '[0.1,0.1,3,4]'::vector(4) AS distance FROM public.documents ORDER BY embedding <=> '[0.1,0.1,3,4]'::vector(4) LIMIT 3;" 2>&1=== RPC with golf embedding (expect golf nearest) ===
[{"id":6,"title":"golf","distance":0},
{"id":3,"title":"delta","distance":0.974711892170972},
{"id":2,"title":"bravo","distance":0.974711892401252}]
=== Query plan (should use HNSW index) ===
QUERY PLAN
----------------------------------------------------------------------
Limit (cost=1.15..1.16 rows=3 width=44)
-> Sort (cost=1.15..1.17 rows=6 width=44)
Sort Key: ((embedding <=> '[0.1,0.1,3,4]'::vector(4)))
-> Seq Scan on documents (cost=0.00..1.07 rows=6 width=44)
(4 rows)
psql "$SUPABASE_DB_URL" <<'SQL' 2>&1 SET enable_seqscan = off; EXPLAIN SELECT id, title, embedding <=> '[0.1,0.1,3,4]'::vector(4) AS distance FROM public.documents ORDER BY embedding <=> '[0.1,0.1,3,4]'::vector(4) LIMIT 3; RESET enable_seqscan; SQL
SET
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=2.25..2.83 rows=3 width=44)
-> Index Scan using documents_embedding_hnsw_idx on documents (cost=2.25..3.42 rows=6 width=44)
Order By: (embedding <=> '[0.1,0.1,3,4]'::vector(4))
(3 rows)
RESET
10 earlier narration turn(s) are in the raw transcript.
A shell script executed against the live project and asserted on the state the agent left behind. No model judged another model.
Raw transcript · Task definition and verifier · Every attempt