moonshotai/kimi-k3 · d2_pgvector · rep 2

env | grep -i supabase | sed 's/=.*KEY=.*/=<redacted>/' ; echo '---' ; which psql python3 curl jq 2>/dev/null; echo '---'; psql --version 2>/dev/null; python3 --version

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>
---
/usr/bin/psql
/usr/bin/python3
/usr/bin/curl
/usr/bin/jq
---
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
Python 3.11.2

psql "$SUPABASE_DB_URL" -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)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE public.documents ADD COLUMN IF NOT EXISTS embedding vector(4);

UPDATE public.documents
SET embedding = embedding_json::text::vector
WHERE embedding IS NULL;

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" -v ON_ERROR_STOP=1 <<'SQL'
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
ON public.documents
USING hnsw (embedding vector_cosine_ops);

SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND 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" -v ON_ERROR_STOP=1 <<'SQL'
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 ON FUNCTION public.match_documents(vector, int) TO anon, authenticated, service_role;

-- sanity check directly in SQL: query close to 'alpha' [10,0.5,0,0]
SELECT * FROM public.match_documents('[9,1,0,0]'::vector, 3);
SQL

CREATE FUNCTION
GRANT
 id | title |      distance       
----+-------+---------------------
  1 | alpha | 0.00184160817442081
  2 | bravo |  0.0221976173761341
  3 | delta |   0.580941822538253
(3 rows)

echo '=== Query near [9,1,0,0] (should rank alpha, bravo first) ==='
curl -sS -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": [9, 1, 0, 0], "match_count": 4}' | jq .

echo '=== Query near [0,0,0,1] (should rank golf first) ==='
curl -sS -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, 0, 1], "match_count": 3}' | jq .

=== Query near [9,1,0,0] (should rank alpha, bravo first) ===
[
  {
    "id": 1,
    "title": "alpha",
    "distance": 0.00184160817442081
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 0.0221976173761341
  },
  {
    "id": 3,
    "title": "delta",
    "distance": 0.580941822538253
  },
  {
    "id": 4,
    "title": "echo",
    "distance": 0.889568473925153
  }
]
=== Query near [0,0,0,1] (should rank golf first) ===
[
  {
    "id": 6,
    "title": "golf",
    "distance": 0.200319815443349
  },
  {
    "id": 1,
    "title": "alpha",
    "distance": 1
  },
  {
    "id": 2,
    "title": "bravo",
    "distance": 1
  }
]