Search

Built-in hybrid search on SQL tables — BM25 full-text, LSH vectors, RRF fusion — plus optional external index engines.

Built-in hybrid search ranks ordinary SQL rows. Mark text columns with .searchable() for BM25, chain .embed() when you also want semantic LSH, then call fx.store(db).search from a Flow.

It runs on PostgreSQL 15+ (postgres / pglite) with GIN + B-tree — no extra extensions. store.index (Meilisearch / pgvector) stays for engines you host separately.

For developers ranking rows on okengine — mark columns, query with fx.store(db).search, read data + meta.

The one rule

.searchable() is free BM25 math on the table. .embed() is a separate chain that starts an async, costed AI pipeline — never a boolean next to weight. Bare .searchable() needs no ai element.

Smallest Example

Mark columns and bind a route

src/db/schema.decl.ts

import { field, store } from "okengine";

export const articles = store.schema.table("articles", {
  id: field.text().primaryKey(),
  title: field.text().searchable({ weight: 2 }).notNull(),
  body: field.text().searchable(),
});

export const db = store.sql("app", { schema: { articles } });

src/flows/articles/search.ts

import { on, flow, http } from "okengine";
import { z } from "zod";
import { articles, db } from "@/schema";

export const search = on(
  http.get({
    in: z.object({ q: z.string() }),
  }),
  flow({
    do: async ({ q }, fx) => {
      const result = await fx.store(db).search(articles, {
        query: q,
        limit: 20,
      });
      return fx.json.ok(result.data, { meta: result.meta });
    },
  }),
);

Push schema and call

oke db push
curl -X GET "http://localhost:6530/articles/search?q=refund" \
  -H "accept: application/json"

Response:

{
  "data": [{ "id": "a1", "title": "Refund policy", "body": "…" }],
  "error": null,
  "meta": { "engine": ["bm25"], "limit": 20 }
}

BM25 needs no AI

The smallest loop is full-text only. Chain .embed() when you want semantic neighbors — see Progressive Patterns. .embed() without a resolvable model + dims fails loud (SearchConfigError).

Progressive Patterns

From BM25-only ranking to hybrid LSH, fusion, and opt-in rerank:

Title carries twice the BM25F field weight of body. No ai element, no .embed():

src/db/schema.decl.ts

import { field, store } from "okengine";

export const articles = store.schema.table("articles", {
  id: field.text().primaryKey(),
  title: field.text().searchable({ weight: 2 }).notNull(),
  body: field.text().searchable(),
});

weight must be a finite number > 0 (default 1). Only text / varchar / char.

Field Reference

Chain	Signature	Default	AI?	Meaning
`.searchable()`	`.searchable({ weight? })`	`weight: 1`	No	BM25F field weight (applied to term frequency before saturation / IDF)
`.embed()`	`.embed({ model?, dims? })`	inherit project default	Yes	Async embedding + LSH on that searchable column

.embed() without a prior .searchable() throws:

.embed() requires a prior .searchable() on the same field — weight is free SQL math; embed is an async AI pipeline

`oke({ store: { search: { embed } } })`	Type	Meaning
`embed.model`	`ai.model` handle or name string	Required when the block is present
`embed.dims`	positive integer	Required when the block is present

Per-field values win when set. Bare .embed() with neither a field option nor a project default throws SearchConfigError:

SearchConfigError: articles.body: .embed() needs model and dims — set oke({ store: { search: { embed: { model, dims } } } }) or pass them on .embed({ model, dims })

Query Options

fx.store(db).search(table, options) — query / fuse / rerank are search-specific. The rest is the same list grammar used by store.resource lists and liveQuery.

Option	Type	Default	Meaning
`query`	`string`	(required)	Relevance string (BM25 ± LSH). Not list-grammar `?search=`
`fuse`	`{ strategy?, k?, weights? }`	RRF, `k: 60`	Rank fusion when LSH hits exist
`rerank`	`false` \| `{ model }`	`false`	Opt-in `fx.ask` after fusion
`limit`	`number`	`20`	Page size (capped by `maxLimit`)
`maxLimit`	`number`	`100`	Cap on `limit` / `?limit=`
`filter`	`"all"` \| columns \| `"none"`	`"none"`	Whitelist for `filterInput` column filters
`filterInput`	object	`{}`	PostgREST-shaped filters (`status: "eq.active"`, `limit`, `cursor`, …)
`mode`	`"cursor"` \| `"offset"`	`"offset"` unless `cursor` is set	Pagination
`cursor`	columns	`[]`	Keyset columns
`order`	column scope	cursor columns, else `"all"`	`?order=`
`search`	column scope	`"none"`	List-grammar `?search=` / `?q=` LIKE — unused by hybrid `query`

Result:

Field	Meaning
`data`	Ranked rows (PK + searchable text + stored embeddings when present)
`meta.engine`	`["bm25"]` or `["bm25", "lsh"]` — from schema (`.embed()` columns), not from whether fusion ran
`meta.fusedBy`	`"rrf"` or `"weighted"` — omitted when there are no vector hits
`meta.rrfK`	RRF damping constant — omitted unless RRF ran
`meta.limit`	Effective page size

src/flows/articles/search.ts

const { data, meta } = await fx.store(db).search(articles, {
  query: "refund policy",
  filter: [articles.status],
  filterInput: { status: "eq.active" },
  limit: 20,
});

Consequence: filter: "none" (the default) rejects unknown column keys in filterInput — unknown list param "status". Pass filter: [articles.status] or filter: "all" before sending column filters.

Two Surfaces

These share English words and are not the same API. Mixing them up ranks the wrong way (or does not rank at all).

Surface	Call	Parameter	Physics
List / live grammar	`store.resource` lists, `liveQuery`	`?search=` or `?q=`	Substring `LIKE %term%` on a column whitelist (default `"none"`)
Hybrid SQL search	`fx.store(db).search(table, { query })`	`query`	BM25 (± LSH) relevance ranking
Index / embed helper	`fx.search(embed, query)` / `fx.store(indexDecl).search`	vector or text	External `store.index` engine — not this table

Side by side:

// 1) List grammar — substring filter (NOT BM25)
// GET /articles?search=refund&status=eq.active
await liveQuery(fx, articles, input, {
  search: [articles.title],
  filter: [articles.status],
});

// 2) Hybrid search — BM25 / LSH relevance (NOT LIKE)
await fx.store(db).search(articles, {
  query: "refund policy",
  filter: [articles.status],
  filterInput: { status: "eq.active", limit: "20" },
});

search() reuses the list grammar for ordinary column filters, limit, and cursor pagination. Only query, fuse, and rerank are search-specific.

Declaring Columns

Each searchable column binds with field.text().searchable(…) (optionally .embed()):

Mark the text fields you want ranked. No AI, no shadow vector columns:

src/db/schema.decl.ts

import { field, store } from "okengine";

export const articles = store.schema.table("articles", {
  id: field.id().primaryKey(),
  title: field.text().searchable({ weight: 2 }).notNull(),
  body: field.text().searchable().notNull(),
  status: field.text().notNull(),
  createdAt: field.timestamp().notNull().now(),
});

export const db = store.sql("app", { schema: { articles } });

After oke db push, the table gets a generated tsvector + GIN index for candidate retrieval (plainto_tsquery('english', …)).

Running Search

Bind a Flow, pass query, return data + meta:

Full HTTP loop with envelope meta from the search result:

src/flows/articles/search.ts

import { on, flow, http } from "okengine";
import { z } from "zod";
import { articles, db } from "@/schema";

export const search = on(
  http.get({
    in: z.object({ q: z.string().min(1) }),
  }),
  flow({
    do: async ({ q }, fx) => {
      const result = await fx.store(db).search(articles, {
        query: q,
        limit: 20,
      });
      return fx.json.ok(result.data, { meta: result.meta });
    },
  }),
);

curl -X GET "http://localhost:6530/articles/search?q=refund+policy" \
  -H "accept: application/json"

Embedding Pipeline

Detailed section

If you only need BM25, skip this. Writer Flows never call fx.embed — a system-owned durable CDC flow embeds after commit. A just-written row may be missing from semantic results for a short interval.

.embed() starts an async pipeline: after the row commits, the runtime embeds changed text, packs an LSH bucket (64 hyperplanes), and stores both beside the row.

body: field.text().searchable().embed(), // inherits oke({ store: { search: { embed } } })

oke db push adds search DDL when columns are .searchable() / .embed():

Object	Role
Generated `tsvector` + GIN	BM25 candidate retrieval (`plainto_tsquery('english', …)`)
`real[]` embedding column	Stored vector per `.embed()` field
`bigint` LSH column + B-tree	Stored SimHash pack (query ranks by Hamming, not equality)
Corpus stats / DF / hyperplane tables	IDF, average length, stable LSH planes

Hyperplanes insert once (ON CONFLICT DO NOTHING) and are never regenerated. Changing dims on a live column leaves the old planes in place — you will hit a length SearchConfigError until those rows are rebuilt.

.embed() without a configured ai element:

SearchConfigError: articles.body: .embed() requires a configured ai element (ai.model / ai.embed). Remove .embed() for BM25-only search, or declare an embedding model.

Project default block without dims or model:

extract: oke({ store: { search: { embed } } }) requires dims: positive integer
extract: oke({ store: { search: { embed } } }) requires model (ai.model handle or name string)

Fusion

Detailed section

If you only need BM25, skip this. Fusion runs only when both BM25 and LSH hit lists exist — then ranks are fused and truncated to limit.

Candidates are oversampled (max(limit × 5, 50), capped at 500) before fusion.

`fuse.strategy`	Formula	Default knobs
`"rrf"` (default)	Σ `1 / (k + rank)`	`k: 60` (Cormack, Clarke, Büttcher — SIGIR 2009; MAP flat for k ∈ [20, 100])
`"weighted"`	min-max per list, then linear mix	`weights.bm25` / `weights.vector` default `0.5` each

Reciprocal Rank Fusion ignores raw score scales — only ranks matter:

await fx.store(db).search(articles, {
  query: q,
  fuse: { strategy: "rrf", k: 60 },
  limit: 20,
});

meta.fusedBy is "rrf" and meta.rrfK echoes the damping constant when RRF ran.

Opt-in linear mix after per-list min-max normalization:

await fx.store(db).search(articles, {
  query: q,
  fuse: {
    strategy: "weighted",
    weights: { bm25: 0.4, vector: 0.6 },
  },
  limit: 20,
});

meta.fusedBy is "weighted"; rrfK is omitted.

Rerank

Rerank is a second, optional pass after fusion. Declare a prompt, then pass its name — never enabled by default:

src/ai/search-rerank.ts

import { ai } from "okengine";
import { z } from "zod";

const reranker = ai.model("reranker", {
  provider: "openai-compatible",
  model: "llama3.1",
});

export const searchRerank = reranker.prompt("search.rerank", {
  in: z.object({
    query: z.string(),
    docs: z.array(z.object({ id: z.string(), text: z.string(), score: z.number() })),
  }),
  out: z.object({ rankedIds: z.array(z.string()) }),
  budget: { maxCostPerCall: 0.02 },
});

src/flows/articles/search.ts

const result = await fx.store(db).search(articles, {
  query: q,
  rerank: { model: "search.rerank" },
  limit: 20,
});

The runtime calls fx.ask with { query, docs } where each doc’s text is the concatenated searchable fields. Return { rankedIds } in preferred order. Missing or empty rankedIds keeps the fused order.

Consequence: budgets on the prompt (maxCostPerCall) are the cost guardrail — search itself does not invent a second limit.

Backfill

oke db push applies shadow columns and indexes. It never silently backfills a large table. Run the rebuild yourself:

oke db search-backfill <table> [--batch=32]

Flag	Default	Meaning
`<table>`	(required)	SQL table name in the Manifest
`--batch`	`32`	Rows per page (embed batches pause between pages for provider rate limits)
`--env`	config env	`dev` \| `test` \| `prod`

Doctor warns when searchable columns land on existing rows:

table "articles" has searchable/embed columns on existing data — run `oke db search-backfill articles` (never auto on push)

Corpus stats below 100 rows print:

[oke db search-backfill] warn: table "articles" has only 12 rows — IDF/BM25 corpus statistics are not meaningful yet (threshold 100)

Ranking still runs; IDF is unstable until the corpus grows past 100 rows.

Cause: oke db search-backfill: no DATABASE_URL / OKE_STORE_SQL_URL / OKE_PGLITE_URL — cannot open SQL. Set a connection URL (compose .env.local or process env), then:

oke db search-backfill articles --batch 500

The CLI opens SQL, extracts the Manifest, and calls runSearchBackfill. Never auto-runs on push.

External Indexes

Not this capability

store.index is a separate facet with its own drivers. Use it when you need typo-tolerant HTTP search or a hosted vector engine — not as a substitute for .searchable() on the primary table.

store.index — pick the search physics

discriminated on driverId

vector
memory · pgvector
upsert(id, vector, meta?)
search(vector, topK?)
score → cosine similarity
Embeddings — ai.embed / fx.search
meilisearch
meilisearch (opt-in)
upsert(id, document)
search(q, { topK, filter, facets })
score → relevance (_rankingScore)
Typo-tolerant full-text + facets

Index stays memory until you set drivers.store.index explicitly — there is no silent fallback to Meilisearch or pgvector.

Omit { dims } — dimensions select a vector driver. Search takes a string:

src/db/indexes.ts

import { store } from "okengine";

export const articlesIndex = store.index("articles");

const idx = fx.store(articlesIndex);
if (idx.driverId === "meilisearch") {
  const { hits } = await idx.search(q, { topK: 20 });
  return hits;
}

See Meilisearch for oke.config.ts pins and keys.

Measured latency & recall (G17)

Headline numbers from the live-Postgres G17 gate (OKE_TEST_POSTGRES=1, Bun 1.4.2, Apple M4, Postgres 16). Trend-analysis only — not an SLA. Full tables and EXPLAIN live in the repo load-test report: src/bench/REPORT.md (G17).

When to stay on BM25 vs add LSH vs use an external index

Corpus size	BM25 (text) p50	LSH/hybrid p50	LSH precision@10 vs exact cosine	Guidance
≤10k	~1–5 ms	~1–9 ms	0.10–0.17 vector	Built-in hybrid is fine for ranking UX; LSH is not HNSW
~100k	~21 ms	~31–34 ms	0.017 vector	Expect tens of ms; re-`EXPLAIN` after `ANALYZE`
~1M	~0.3 s	~0.4 s	0.017 vector / 0 hybrid	Prefer external `store.index` (pgvector / Meilisearch) for semantic recall at this scale

Honest LSH note: after the Hamming-rank fix (2026-09-11), vector precision@10 vs exact cosine is 0.17 at 1k, 0.10 at 10k, ~0.02 at 100k–1M on the G17 hash-bag corpus — a smooth drop, not the v0.19.0 zero collapse. Still not HNSW. Prefer BM25-only or store.index when semantic recall matters.

Query plan: at N=100k, EXPLAIN (ANALYZE, BUFFERS) is a UNION of GIN Bitmap Index Scan and a Parallel Seq Scan + top-N heapsort on Hamming distance (~15 ms). Hamming-rank cannot use the LSH B-tree. Capture your own plan on production data.

Backfill: oke db search-backfill is interrupt-safe to re-run (G17 killed at 2k/50k embeds, resumed to completion in ~29 s on that table).

Requirements

Built-in hybrid search is a SQL-facet capability — not a fourth store facet.

Need	Requirement
Driver	`postgres` or `pglite` (PostgreSQL 15+)
Extensions	None — GIN + B-tree only
BM25	At least one `.searchable()` text column
LSH	`.embed()` + configured `ai` + `model` / `dims`
Backfill	Explicit `oke db search-backfill` (never auto on push)

Troubleshooting

Learn more

Store — four facets; fx.store handles
SQL — store.schema.table, field.*, list grammar
HTTP · Resources — list grammar (?search= LIKE, filters, cursor)
AI — ai.model, ai.prompt, fx.ask / fx.embed
fx — fx.store(db).search is a SQL read; fx.search(embed, query) is the index helper
Meilisearch — store.index full-text driver
Configuration — drivers.store.index (memory · pgvector · meilisearch)

Smallest Example

Mark columns and bind a route

Push schema and call

Progressive Patterns

Field Reference

Query Options

Two Surfaces

Declaring Columns

Running Search

Embedding Pipeline

Fusion

Rerank

Backfill

External Indexes

Measured latency & recall (G17)

When to stay on BM25 vs add LSH vs use an external index

Requirements

Troubleshooting

Learn more

Next

SQL

AI

Store Overview

On this page

Search

What your Flow does not do

Eventual consistency

What push creates

Missing AI / missing dims

RRF (default)

Weighted

BM25F constants

No vector hits

Low corpus warning

Unknown table

search-backfill needs a live SQL URL

SearchConfigError — .embed() needs model and dims

SearchConfigError — .embed() requires a configured ai element

.embed() requires a prior .searchable()

searchable() is only valid on text / varchar / char

searchable({ weight }) must be a finite number > 0

search(): table must be a store.schema.table()

SearchConfigError — no .searchable() columns

unknown list param / unfilterable column

missing hyperplanes

embedding length !== declared dims

Just-written row missing from semantic results

meta.engine lists lsh but fusedBy is missing

Doctor says run search-backfill

IDF/BM25 corpus statistics are not meaningful yet

CLI prints programmatic API / live SQL not wired

I passed ?search= and got LIKE, not BM25

SQL

AI

Store Overview

On this page