z-ai/glm-5.2 · d2_pgvector · rep 3

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "ANON: ${SUPABASE_ANON_KEY:0:10}..."; echo "SERVICE: ${SUPABASE_SERVICE_ROLE_KEY:0:10}..."; echo "DB_URL: $SUPABASE_DB_URL"; echo "DB_PASS: ${SUPABASE_DB_PASSWORD:0:5}..."

URL: https://<ref>.supabase.co
REF: <ref>
ANON: eyJhbGciOi...
SERVICE: eyJhbGciOi...
DB_URL: <redacted:SUPABASE_DB_URL>
DB_PASS: 1a9f7...

which psql 2>/dev/null || echo "no psql"; which curl 2>/dev/null || echo "no curl"

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

echo "SELECT version();" | PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" -t -q 2>&1 | head -5

 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;" 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)

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" -c "SELECT * FROM pg_available_extensions WHERE name IN ('vector');" 2>&1

  name  | default_version | installed_version |                       comment                        
--------+-----------------+-------------------+------------------------------------------------------
 vector | 0.8.2           |                   | vector data type and ivfflat and hnsw access methods
(1 row)

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" 2>&1 <<'EOF'
BEGIN;

-- 1) Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;

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

UPDATE public.documents
SET embedding = (embedding_json::text)::vector(4);

-- Sanity check
SELECT id, title, embedding_json, embedding FROM public.documents ORDER BY id;

COMMIT;
EOF

BEGIN
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)

COMMIT

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" 2>&1 <<'EOF'
-- 3) HNSW index with cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
ON public.documents
USING hnsw (embedding vector_cosine_ops);

-- 4) SQL function for semantic search
CREATE OR REPLACE FUNCTION public.match_documents(
    query_embedding vector(4),
    match_count int DEFAULT 10
)
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 LEAST(match_count, 100);
$$;

-- Allow anon role to execute
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated;

-- Grant select on table for the function to work
GRANT SELECT ON public.documents TO anon, authenticated;
EOF

CREATE INDEX
CREATE FUNCTION
GRANT
GRANT

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" -c "\di public.documents_embedding_hnsw_idx" 2>&1

                           List of indexes
 Schema |             Name             | Type  |  Owner   |   Table   
--------+------------------------------+-------+----------+-----------
 public | documents_embedding_hnsw_idx | index | postgres | documents
(1 row)

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" 2>&1 <<'EOF'
-- Test: query near [10, 0.5, 0, 0] (alpha's own embedding)
SELECT * FROM public.match_documents('[10, 0.5, 0, 0]'::vector(4), 6);
EOF

 id |  title  |      distance      
----+---------+--------------------
  1 | alpha   |                  0
  2 | bravo   | 0.0367087035723488
  3 | delta   |  0.636791795933452
  4 | echo    |  0.950062383056108
  6 | golf    |  0.979034586516748
  5 | foxtrot |   1.99755410633902
(6 rows)

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql "$SUPABASE_DB_URL" 2>&1 <<'EOF'
-- Verify HNSW index uses cosine ops
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents' AND indexname = 'documents_embedding_hnsw_idx';
EOF

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

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}' | python3 -m json.tool 2>&1

[
    {
        "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
    }
]

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": 3}' | python3 -m json.tool 2>&1

[
    {
        "id": 4,
        "title": "echo",
        "distance": 0
    },
    {
        "id": 3,
        "title": "delta",
        "distance": 0.0513167019494862
    },
    {
        "id": 2,
        "title": "bravo",
        "distance": 0.683772217228812
    }
]