Index on Postgres: typos, meaning and SQL pushdown

The library's drizzle Block index already runs search in Postgres on tsvectors. @skaile/forge-editor-index-pg wraps it (createPgBlockIndex) and adds three things a plain drizzle index lacks. Nothing changes for a host that does not use it.

The Embedder port

EditorPorts.embeddings (@skaile/forge-editor/server) turns text into vectors. Capability id embeddings; the server lists it in GET /api/editor/capabilities, a backend never has it (the client never embeds, the route does).

interface Embedder {
  /** Names the model; a vector is comparable only with vectors of the same model. */
  model: string
  /** The length of its vectors. */
  dim: number
  embed(texts: string[], user?: object): Promise<number[][]>
}

createOpenAiEmbedder({ endpoint, model, dim, apiKey }) from @skaile/forge-editor/adapters/embeddings calls an OpenAI-compatible POST <endpoint>/embeddings. The call goes from the server, the key never reaches a page. OPENAI_EMBEDDING_DIMENSIONS lists the vector lengths of OpenAI's models; a model it does not list needs its dim given.

Search by meaning

When the host passes ports.embeddings and the index has nearest, GET /api/editor/search?q= runs the keyword search, embeds the query, asks the index for the nearest Blocks and fuses both lists with fuseRanks (reciprocal rank fusion: a hit at rank r adds weight / (60 + r); keyword 0.7, vector 0.3). A note titled "Hund" then answers the query "Haustier", which shares no word with it. When the embedder fails the route logs it and answers the keyword hits: search never stops working with the endpoint.

GET /api/editor/blocks/:documentId/:blockId/related (the Explore tab) prefers index.related when the index has it, the Blocks nearest to the Block's own vector, and answers the shared-tags heuristic otherwise, or when the Block has no vector yet.

Set up

import { createOpenAiEmbedder } from '@skaile/forge-editor/adapters/embeddings'
import { bootstrapPgBlockIndex, createPgBlockIndex, pgBlockIndexTables } from '@skaile/forge-editor-index-pg'

const embedder = createOpenAiEmbedder({ endpoint: 'https://api.openai.com/v1', model: 'text-embedding-3-small', dim: 1536, apiKey })
await bootstrapPgBlockIndex(db, { dim: embedder.dim })
const index = createPgBlockIndex({ db, tables: pgBlockIndexTables(embedder.dim), embedder })
// installEditorServer({ ports: { index, embeddings: embedder, ... } })

• db is any drizzle Postgres database whose server has pg_trgm and vector. On PGlite load them: new PGlite({ extensions: { pg_trgm, vector } }) with pg_trgm from @electric-sql/pglite/contrib/pg_trgm and vector from @electric-sql/pglite-pgvector.
• A host with migrations exports pgBlockIndexTables(dim) from its drizzle schema and adds pgBlockIndexStatements(dim) (the extensions, the trigram index, the vector table) to a migration. bootstrapPgBlockIndex is the same without migrations.
• The vector table has the length dim of the embedder. Changing the model to another length needs a new table; changing it to one of the same length makes replace embed everything again (the model is stored with each vector).
• Without embedder, createPgBlockIndex still gives typo search and pushdown, and has no nearest or related.

What replace does with vectors

Embedding is a network call, so replace returns before it is done and index.settled() waits for it. Per Document one job runs at a time, always over the latest rows; a second replace meanwhile replaces the rows the job is yet to see.

• A Block whose title and text (cut at 6000 characters) hash like its stored vector, with the same model, costs nothing.
• A changed Block is embedded in batches of 64, and each batch is stored when it returns, so a failing endpoint keeps what was made. The failure is logged; the rows stay; the next replace of the Document, or the rebuild of the index at server start, makes the vectors that are missing.
• A Block that vanished or has neither title nor text loses its vector; remove(documentId) drops the Document's vectors.

Query pushdown

query keeps the contract of the memory adapter: the same rows in the same order. The add-on narrows the candidate rows in SQL, as a pre-filter, and evaluateQuery runs over the rows that are left for the final answer (the filter again, sorting, grouping, aggregates, paging, issues), so SQL can only drop rows the filter would drop anyway. A condition is pushed when its SQL is the very test of the Property kind: eq, ne, isEmpty, isNotEmpty and hasAny on text, textarea, url, select, status and reference Properties, eq and ne on checkboxes, hasAny on multiselects, and the same on the pseudo-fields ($title, $tags, $type, $id, $doc, $basket). An and pushes the conditions it can and leaves the others to JS; an or is pushed only when every branch is. Dates, numbers, contains and the ordering operators, a query without a Block type, and a query that names a key no definition declares go through the drizzle adapter's JS path unchanged. A row whose JSON Postgres cannot read (U+0000) falls back the same way. The contract test runs every case against the memory adapter and the pg adapter on PGlite.

In the host app

NUXT_BLOCK_INDEX=pg-vector starts the index on PGlite with the add-on (server/utils/block-index.ts). With OPENAI_API_KEY set the host also creates the embedder (server/utils/addons.ts: OPENAI_EMBEDDING_MODEL, default text-embedding-3-small; OPENAI_EMBEDDING_DIMENSIONS for a model OpenAI does not list; OPENAI_BASE_URL for another OpenAI-compatible endpoint) and passes it to the index and to ports.embeddings. Without the key the index has typo search only. PGlite lives in memory: the server rebuilds the index, and with it the vectors, from the Documents on every start, which costs one embedding call per 64 changed Blocks; a host with many Documents uses a Postgres server instead.