reference · full-text

Full-text search

Full-text search finds rows by keywords in their text. Use it for "find products matching wireless headphones", search-as-you-type, log line filtering, and anywhere a user is typing words instead of structured filters. The ranking algorithm is BM25 - the same one Elasticsearch, Lucene, and most search engines use.

Full-text indexes live on their own runtime endpoint - they are not declared on the schema. You index a text under (table, field, doc_id), then search. The doc_id is what links search hits back to your rows - use the row's primary key.

1. Index a text.

what this does

Tell OriginChain to make a piece of text searchable. Re-indexing the same doc_id replaces the old text in the same write - no stale matches.

POST /v1/tenants/:t/fts/:table/:field

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "doc_id": "sku-9281",
    "text":   "Lightweight road runner with a carbon plate, designed for marathon pace."
  }'

db.fts.index(
    "shop.products",
    "description",
    doc_id="sku-9281",
    text="Lightweight road runner with a carbon plate, designed for marathon pace.",
)

await db.ftsIndex("shop.products", "description", {
  doc_id: "sku-9281",
  text:   "Lightweight road runner with a carbon plate, designed for marathon pace.",
});

err := db.FTSIndex(ctx, "shop.products", "description", originchain.FTSIndexRequest{
    DocID: "sku-9281",
    Text:  "Lightweight road runner with a carbon plate, designed for marathon pace.",
})

what each field means

Field	Where	What it is
:table	URL	The schema name. Same as your row table - e.g., `shop.products`.
:field	URL	Which "logical column" you're indexing under. You can index multiple fields per table - `title`, `description`, etc.
doc_id	body	A unique ID for this document. Use the row's primary key so hits link back cleanly.
text	body	The text to search. No size limit on this endpoint, but very large documents are better split into multiple `doc_id`s.

common mistakes

Indexing only one of several fields. If you want users to search "Wireless headphones" and match products whose title or description contains those words, you need to either concatenate both fields into one text before indexing, or index each field separately and union the results.
Forgetting to re-index on updates. Editing a row's text does not automatically update the FTS index. Re-call this endpoint with the new text whenever you change the source.

2. BM25 - ranked search.

what this does

Return the top-k documents ranked by relevance to the query. This is what most users mean when they say "search". Rare query words count more than common ones; documents where the query words appear more often (relative to length) rank higher.

GET /v1/tenants/:t/fts/:table/:field?mode=bm25

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+marathon&mode=bm25&k=10" \
  -H "Authorization: Bearer $OC_TOKEN"

hits = db.fts.search(
    "shop.products",
    "description",
    q="carbon marathon",
    mode="bm25",
    k=10,
)
for hit in hits:
    print(hit.score, hit.doc_id)

const hits = await db.ftsSearch("shop.products", "description", {
  q:    "carbon marathon",
  mode: "bm25",
  k:    10,
});
for (const hit of hits) {
  if (typeof hit === "object") console.log(hit.score, hit.doc_id);
}

hits, err := db.FTSSearch(ctx, "shop.products", "description", originchain.FTSSearchRequest{
    Q:    "carbon marathon",
    Mode: "bm25",
    K:    10,
})
if err != nil { /* handle */ }
for _, h := range hits {
    fmt.Println(h.Score, h.DocID)
}

query params

Param	Required	Notes
q	yes	The query text. URL-encode spaces as `+` or `%20`.
mode	yes	`bm25` for this section.
k	no	Max results. Default 10.
fuzzy	no	Edit distance for typo tolerance. `fuzzy=1` matches one-character typos.
highlight	no	`highlight=true` returns matched-term snippets per hit.
facets	no	Comma-separated field names to aggregate as facet counts.

3. Boolean AND - every word must match.

what this does

Return every document that contains all the query words, in any order, with no ranking. Fast token-presence check - use when you don't need relevance scoring.

GET /v1/tenants/:t/fts/:table/:field?mode=boolean

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+marathon&mode=boolean" \
  -H "Authorization: Bearer $OC_TOKEN"

hits = db.fts.search(
    "shop.products",
    "description",
    q="carbon marathon",
    mode="boolean",     # every word must appear
)
for hit in hits:
    print(hit.doc_id)

const hits = await db.ftsSearch("shop.products", "description", {
  q:    "carbon marathon",
  mode: "boolean",
});

hits, err := db.FTSSearch(ctx, "shop.products", "description", originchain.FTSSearchRequest{
    Q:    "carbon marathon",
    Mode: "boolean",
})

Returns doc_id strings only - no score field. If you need ordering, use BM25.

4. Phrase - exact word order.

what this does

Match documents that contain the query words contiguously, in the exact order given. Use for branded phrases ("New York Times"), product model numbers, log message templates.

GET /v1/tenants/:t/fts/:table/:field?mode=phrase

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/fts/shop.products/description?q=carbon+plate&mode=phrase" \
  -H "Authorization: Bearer $OC_TOKEN"

hits = db.fts.search(
    "shop.products",
    "description",
    q="carbon plate",
    mode="phrase",      # exact phrase "carbon plate", in that order
)

const hits = await db.ftsSearch("shop.products", "description", {
  q:    "carbon plate",
  mode: "phrase",
});

hits, err := db.FTSSearch(ctx, "shop.products", "description", originchain.FTSSearchRequest{
    Q:    "carbon plate",
    Mode: "phrase",
})

5. Languages + analyzer steps.

Analyzers transform text before indexing and at query time. Same pipeline is applied to both sides, so a user typing "runs" matches a document that said "running" if you enabled English stemming.

analyzer steps

Step	What it does
lowercase	Unicode-aware case fold. "Café" → "café".
fold_diacritics	"café" matches "cafe".
stop:<lang>	Drop common stop words ("the", "and", "of" in English).
stem:<lang>	Suffix-strip. "running" / "runs" → "run". Snowball-based.
lemma:<lang>	Dictionary lookup. "ran" → "run". More precise than stem but only available in 9 languages.

stemming + stop-words available for

ArabicDanishDutchEnglishFinnishFrenchGermanHungarianItalianNorwegianPortugueseRomanianRussianSpanishSwedishTamilTurkishHindi

Analyzer choice is set per-field at index time. See FTS tables for the full configuration reference.