App Vector Search - Semantic Search, RAG, Fuzzy Match

Search an app's own rows by meaning ("cheap places to eat near water" finds "budget seafood shack on the pier"), answer questions from the app's content (RAG), or rank "similar items". Two first-party pieces, no vector database, no API keys:

• embed() - function service: text in, a 1536-number vector out (declare services: ['embed']).
• pgvector - pre-installed in every app database: a vector(1536) column, distance operators, HNSW index. Also pg_trgm for typo-tolerant name matching. Never run CREATE EXTENSION (blocked; both are already on).

1. Migration: a vector column + index

-- migrations/002_docs.sql
CREATE TABLE docs (
  id         VARCHAR(20) PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  embedding  vector(1536) NOT NULL
);
CREATE INDEX docs_embedding_idx ON docs USING hnsw (embedding vector_cosine_ops);

Always vector(1536) (the size embed() returns).

2. Write: embed when you insert

// functions/add-doc.js   (gipity.yaml: auth: member, tables: [docs], services: [embed])
export default async function (ctx, { db, embed, guid }) {
  const { title, body } = ctx.body;
  const v = await embed(title + '\n' + body);          // number[1536]
  await db.query('INSERT INTO docs (id, title, body, embedding) VALUES ($1, $2, $3, $4::vector)',
    [guid('doc'), title, body, JSON.stringify(v)]);      // JSON.stringify + ::vector
  return { ok: true };
}

• Bind a vector as JSON.stringify(v) with $N::vector. A raw JS array is sent as a Postgres array and fails (invalid input syntax for type vector).
• Many at once: embed(['a', 'b', ...]) → number[][] in the same order, up to 256 strings per call. Calls count toward max_service_calls (default 5 per invocation), so batch.
• Long text: each input max 30,000 characters. Split documents into chunks of a few hundred words, one row per chunk (keep a doc_id column to group them).
• Re-embed when the text changes (in the same update function).

3. Search: nearest by cosine distance

// functions/search.js   (gipity.yaml: auth: public, tables: [docs], services: [embed])
export default async function (ctx, { db, embed }) {
  const q = await embed(ctx.body.query);
  const { rows } = await db.query(
    `SELECT id, title, 1 - (embedding <=> $1::vector) AS score
       FROM docs ORDER BY embedding <=> $1::vector LIMIT 5`, [JSON.stringify(q)]);
  return { results: rows };
}

• <=> cosine distance (use this), <-> L2, <#> negative inner product. score = 1 - distance, higher is closer.
• Don't SELECT embedding back: 1536 numbers per row. Select ids and text.
• Mix with plain SQL freely: filters, joins and the vector order in one query.

Who writes the rows

Decide this before the first deploy: a deploy flags any auth: public function that writes (fn-public-write).

• Shared content you load (docs, a catalog, an FAQ): write through an auth: member function (only you and project members can call it); search can stay auth: public. That is the example above.
• Each user's own rows (notes, journal, saved items): every function auth: user, a user_guid column, and WHERE user_guid = $N with ctx.auth.userGuid in every read, write and search (... WHERE user_guid = $2 ORDER BY embedding <=> $1::vector).
• Sample rows for a per-user app: don't seed them (a seed belongs to whoever ran it). Show an empty state with an "Add sample notes" button that sends a starter list to the user's own add function, which embeds them in one embed([...]) call.

RAG: answer from your rows

Search as above, then pass the top chunks to llm() (services: ['embed', 'llm'], see app-llm): put them in the prompt as numbered sources and ask it to answer only from them and cite the numbers.

Fuzzy text match (pg_trgm)

CREATE INDEX people_name_trgm ON people USING gin (name gin_trgm_ops);   -- migration

SELECT name FROM people WHERE name % $1 ORDER BY similarity(name, $1) DESC LIMIT 10 matches "Katharine Jonson" to "Katherine Johnson". Use it for names, titles, typos; use embed() for meaning.

Seed data, tests, cost

• Seed SQL can't call embed(). Load shared starter content through an auth: member function (e.g. functions/ingest.js taking { items: [...] }), then gipity fn call ingest '{"items":[...]}' --env dev after deploy. Per-user samples: see "Who writes the rows".
• gipity test uses real embed() (vectors must be real for search to rank), so a test can insert rows through your function and assert the top hit.
• Owner-billed by tokens: about 24 credits per 1M tokens (a 500-word chunk is under 0.02 credits).