--- title: "Index on Postgres: typos, meaning and SQL pushdown" description: "The @skaile/forge-editor-index-pg add-on: trigram title search, a vector table kept current on replace, nearest and related Blocks, query pushdown, and the Embedder port with its OpenAI-compatible adapter." canonical_url: "https://skaile-ai.github.io/forge-editor/guides/index-pg" --- # Index on Postgres: typos, meaning and SQL pushdown > The @skaile/forge-editor-index-pg add-on: trigram title search, a vector table kept current on replace, nearest and related Blocks, query pushdown, and the Embedder port with its OpenAI-compatible adapter. The library's drizzle [Block index](https://skaile-ai.github.io/forge-editor/guides/editor-ports) already runs `search` in Postgres on `tsvector`s. `@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.
Piece What it does Where
Typo search search finds a title that holds a word similar to each query word ( pg_trgm ), after the hits for the words as written add-on
Vectors replace embeds the Blocks whose text changed into editor_block_vectors (pgvector); nearest and related answer by cosine similarity add-on + Embedder port
Query pushdown query filters eq , ne , isEmpty , isNotEmpty and hasAny on Properties in SQL add-on
Search by meaning the search route fuses the keyword hits with nearest (reciprocal rank fusion) library
## 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). ```ts 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 } ``` `createOpenAiEmbedder({ endpoint, model, dim, apiKey })` from `@skaile/forge-editor/adapters/embeddings` calls an OpenAI-compatible `POST /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](https://skaile-ai.github.io/forge-editor/guides/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 ```ts 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. ## Sitemap See the full [sitemap](https://skaile-ai.github.io/forge-editor/sitemap.md) for all pages.