moonshotai/kimi-k3 · d2_pgvector · rep 3

echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_ANON_KEY=${SUPABASE_ANON_KEY:0:20}..."; echo "SUPABASE_SERVICE_ROLE_KEY=${SUPABASE_SERVICE_ROLE_KEY:0:20}..."; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"; echo "SUPABASE_DB_PASSWORD=${SUPABASE_DB_PASSWORD:0:5}..."

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

which psql; psql --version; which curl; curl --version | head -1

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
/usr/bin/curl
curl 7.88.1 (x86_64-pc-linux-gnu) libcurl/7.88.1 OpenSSL/3.0.20 zlib/1.2.13 brotli/1.0.9 zstd/1.5.4 libidn2/2.3.3 libpsl/0.21.2 (+libidn2/2.3.3) libssh2/1.10.0 nghttp2/1.52.0 librtmp/2.3 OpenLDAP/2.5.13

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" -c "SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name='vector';" -c "SELECT extname, extnamespace::regnamespace FROM pg_extension;" -c "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname='public' AND tablename='documents';"

  name  | default_version | installed_version 
--------+-----------------+-------------------
 vector | 0.8.2           | 
(1 row)

      extname       | extnamespace 
--------------------+--------------
 plpgsql            | pg_catalog
 pg_stat_statements | extensions
 uuid-ossp          | extensions
 pgcrypto           | extensions
 supabase_vault     | vault
(5 rows)

 schemaname | tablename | rowsecurity 
------------+-----------+-------------
 public     | documents | f
(1 row)

cat > /tmp/setup.sql <<'EOF'
-- 1) pgvector extension (Supabase convention: extensions schema)
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

-- 2) vector(4) column populated from embedding_json
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;
ALTER TABLE public.documents ALTER COLUMN embedding SET NOT NULL;

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

-- 4) 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
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;

-- Make sure it's callable via the REST RPC endpoint
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, service_role;
EOF
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/setup.sql

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

psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding FROM public.documents ORDER BY id;" -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename='documents';" -c "SELECT * FROM public.match_documents('[10,0.5,0,0]'::vector(4), 3);"

 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)

          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)

 id | title |      distance      
----+-------+--------------------
  1 | alpha |                  0
  2 | bravo | 0.0367087035723488
  3 | delta |  0.636791795933452
(3 rows)

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": [10, 0.5, 0, 0], "match_count": 3}' -w "\nHTTP_STATUS:%{http_code}\n"

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

echo "--- Query near golf [0.1,0.1,3,4], top 2:"; 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.1, 0.1, 3, 4], "match_count": 2}'; echo; echo "--- Query pointing negative-x (like foxtrot), top 2:"; 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": [-4, 0, 0, 0], "match_count": 2}'; echo; echo "--- Query along +y axis (like echo/delta), top 3:"; 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, 2, 0, 0], "match_count": 3}'

--- Query near golf [0.1,0.1,3,4], top 2:
[{"id":6,"title":"golf","distance":0}, 
 {"id":3,"title":"delta","distance":0.974711892170972}]
--- Query pointing negative-x (like foxtrot), top 2:
[{"id":5,"title":"foxtrot","distance":0.000199944594884593}, 
 {"id":4,"title":"echo","distance":1}]
--- Query along +y axis (like echo/delta), top 3:
[{"id":4,"title":"echo","distance":0}, 
 {"id":3,"title":"delta","distance":0.0513167019494862}, 
 {"id":2,"title":"bravo","distance":0.683772217228812}]