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

echo "SUPABASE_URL=$SUPABASE_URL"; echo "PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "DB_URL=$SUPABASE_DB_URL"; echo "Anon key set: $([ -n "$SUPABASE_ANON_KEY" ] && echo yes || echo no)"; echo "Service role key set: $([ -n "$SUPABASE_SERVICE_ROLE_KEY" ] && echo yes || echo no)"; echo "DB password set: $([ -n "$SUPABASE_DB_PASSWORD" ] && echo yes || echo no)"

SUPABASE_URL=https://<ref>.supabase.co
PROJECT_REF=<ref>
DB_URL=<redacted:SUPABASE_DB_URL>
Anon key set: yes
Service role key set: yes
DB password set: yes

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

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

export PGPASSWORD="$(echo "$SUPABASE_DB_URL" | sed -n 's/.*:\([^@]*\)@.*/\1/p')"
PSQL_HOST="aws-0-us-east-1.pooler.supabase.com"
PSQL_PORT="5432"
PSQL_DB="postgres"
PSQL_USER="postgres.<ref>"
psql "host=$PSQL_HOST port=$PSQL_PORT dbname=$PSQL_DB user=$PSQL_USER password=$PGPASSWORD" -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)

export PGPASSWORD="$(echo "$SUPABASE_DB_URL" | sed -n 's/.*:\([^@]*\)@.*/\1/p')"
PSQL_HOST="aws-0-us-east-1.pooler.supabase.com"
PSQL_PORT="5432"
PSQL_DB="postgres"
PSQL_USER="postgres.<ref>"
psql "host=$PSQL_HOST port=$PSQL_PORT dbname=$PSQL_DB user=$PSQL_USER password=$PGPASSWORD" <<'SQL'
-- 1. Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

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

-- 3. Create HNSW index on cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
ON public.documents USING hnsw (embedding vector_cosine_ops);

-- 4. Create match_documents 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
    WHERE d.embedding IS NOT NULL
    ORDER BY d.embedding <=> query_embedding
    LIMIT match_count;
$$;

-- Allow anon to call via RPC
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON public.documents TO anon, authenticated;
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated;
SQL

CREATE EXTENSION
ALTER TABLE
UPDATE 6
CREATE INDEX
CREATE FUNCTION
GRANT
GRANT
GRANT

export PGPASSWORD="$(echo "$SUPABASE_DB_URL" | sed -n 's/.*:\([^@]*\)@.*/\1/p')"
PSQL_HOST="aws-0-us-east-1.pooler.supabase.com"
PSQL_PORT="5432"
PSQL_DB="postgres"
PSQL_USER="postgres.<ref>"
psql "host=$PSQL_HOST port=$PSQL_PORT dbname=$PSQL_DB user=$PSQL_USER password=$PGPASSWORD" -c "
SELECT id, title, embedding, embedding_json FROM public.documents ORDER BY id;
"

 id |  title  |   embedding   |  embedding_json  
----+---------+---------------+------------------
  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)

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.9, 0.3, 0, 0], "match_count": 3}' | python3 -m json.tool

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

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

[
    {
        "id": 6,
        "title": "golf",
        "distance": 0
    },
    {
        "id": 3,
        "title": "delta",
        "distance": 0.974711892170972
    },
    {
        "id": 2,
        "title": "bravo",
        "distance": 0.974711892401252
    }
]

export PGPASSWORD="$(echo "$SUPABASE_DB_URL" | sed -n 's/.*:\([^@]*\)@.*/\1/p')"
PSQL_HOST="aws-0-us-east-1.pooler.supabase.com"
PSQL_PORT="5432"
PSQL_DB="postgres"
PSQL_USER="postgres.<ref>"
psql "host=$PSQL_HOST port=$PSQL_PORT dbname=$PSQL_DB user=$PSQL_USER password=$PGPASSWORD" -c "
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents' AND indexname = 'documents_embedding_hnsw_idx';
"

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