Search and vectors

Full-text matching and vector lookup are query predicates in the Data block, served natively by PostgreSQL. There is no search engine to deploy, no index to keep in sync, and no second authorization surface: a text or vector predicate goes through the same query path, the same tenancy scoping and the same access rules as every other filter.

The rule that shapes everything here: the Data block returns sets, not rankings. A text match composes with the rest of a query — filters, sort, pagination — inside one round trip and one transaction, and returns the rows that match. No relevance score comes back. Ranked retrieval, typo tolerance and cross-entity search belong to a retrieval service that reads from this one.

PostgreSQL only

Everything on this page needs a PostgreSQL datastore with the vector (pgvector) and pg_trgm extensions installed. See Prerequisites. Declaring a search: block or a vector field on a ClickHouse, DuckDB or SQLite datastore is not supported.

Declaring it

Everything is declared on the entity. Nothing is configured outside the YAML.

entities:
  - name: claim
    search:
      language: english          # a PostgreSQL text search configuration, per entity
      document:                  # the composite tsvector, weighted in this order
        - field: summary
          weight: A
        - field: adjuster_notes
          weight: B
    fields:
      - name: summary
        type: text
        search: { enabled: true }
      - name: adjuster_notes
        type: text
        search: { enabled: true, trigram: true }
      - name: claimant_name
        type: text
        search: { trigram: true }          # substring filter only; not in the document
      - name: notes_embedding
        type: vector
        dim: 1024
        metric: cosine
        precision: float32                 # float32 | float16 | binary | sparse
        index:
          kind: hnsw                       # hnsw | ivfflat | none
          m: 16
          ef_construction: 64

Declaration	Level	Effect
search.language	entity	Text search configuration. Fixed per entity, not per row. Default simple
search.document	entity	The fields composing the entity’s tsvector, in weight order
search.enabled	field	The field is part of the composite tsvector. It must also be named in search.document
search.trigram	field	Trigram index on the field, serving substring. Independent of enabled
type: vector	field	An embedding column. dim and metric are required
precision	field	Storage form. Defaults to float32
index	field	ANN index kind and parameters. Defaults to hnsw with pgvector’s defaults; none is exact scan

Three rules the loader enforces, so they are not discovered later:

• The document and the fields must agree. A field with search.enabled that the document does not name, or a document naming a field that did not opt in, is refused at load. Only string and text fields can be in the document.
• trigram is independent of enabled. A substring filter on an identifier field is common and should not pull that field into the relevance document.
• A vector is caller-written. The Data block validates the dimension and stores the value. It never generates an embedding, never calls a model, and does not track whether an embedding is current: whoever writes the row writes the vector, or leaves it NULL.

Migrating from fulltextsearch: true

The v1 entity flag fulltextsearch: true parsed and did nothing — no column, no index, no predicate. It is now refused at load, naming this block. Replace it with a search: block that names the fields the entity is searched by, and mark those fields search: { enabled: true }.

What it compiles to

Declaration	DDL
search.document	search_tsv tsvector GENERATED ALWAYS AS (setweight(to_tsvector(…), 'A') || …) STORED
that column	CREATE INDEX … USING gin (search_tsv)
search.trigram on a field	CREATE INDEX … USING gin (field gin_trgm_ops)
type: vector, precision: float32	vector(dim); float16 → halfvec(dim), binary → bit(dim), sparse → sparsevec(dim)
metric	operator class vector_cosine_ops / vector_l2_ops / vector_ip_ops (per precision)
index.kind: hnsw	USING hnsw (col <opclass>) WITH (m, ef_construction)
index.kind: ivfflat	USING ivfflat (col <opclass>) WITH (lists)

The tsvector column is STORED, so it is written in the same transaction as the row. A record a user just saved is matchable before the response returns; there is no index to catch up. It is an internal column: it is not a field, it appears on no wire schema, and select cannot name it.

Migration handles all of it. Adding a search: block to an entity whose table already holds rows adds the column and computes it for every existing row; adding a vector field adds the column and its index. Two changes are refused rather than applied in place:

• Changing dim — every stored embedding was produced at the old dimension and means nothing at the new one. The plan names the sequence instead: new column, backfill from re-embedded values, dual read, cut over, drop.
• Changing search.document — a generated column’s expression cannot be altered in place. The plan warns and leaves the column; drop and re-add it in an explicit migration.

Querying

Three predicates. All of them compose with every existing filter, sort and pagination clause, and all three are available in the JSON body, the pipe syntax, GraphQL and the POST …/search body.

matches — full-text

Entity-level: it tests the entity’s composite tsvector, with the language the column was built with. Compiles to search_tsv @@ websearch_to_tsquery('<language>', $1). The parser is websearch_to_tsquery, so quoted phrases and -exclusion work and malformed input returns no rows rather than an error. The requester sends plain text.

{"from": "claim", "matches": "hydroplaning -rain", "where": {"status": {"$eq": "open"}}, "limit": 20}

from claim | where matches "hydroplaning -rain" and status = "open" | limit 20

An entity with no search: block refuses matches by name. There is no per-field form; a field that needs its own match semantics is its own tsvector column.

substring — trigram

A case-insensitive substring filter on a field declared trigram: true, served by its GIN index. Compiles to field ILIKE '%' || $1 || '%'. The requester sends plain text. A field without a trigram index is refused: the query would run as a sequential scan of every row on every call.

{"from": "claim", "where": {"claimant_name": {"$substring": "singh"}}}

from claim | where claimant_name substring "singh"

The JSON spelling is $substring rather than $contains, because $contains already means array and JSON containment.

near — k-nearest-neighbour

Exact k-NN against a vector field, ordered by distance, inside whatever filters the query also carries. Compiles to ORDER BY field <=> $1 LIMIT k (the operator follows the declared metric). The requester sends an embedding — a list of exactly dim numbers — never text or image bytes: the Data block does not embed anything, on the write path or the read path.

{"from": "claim", "near": {"field": "notes_embedding", "vector": [0.12, -0.03, …], "k": 10},
 "where": {"status": {"$eq": "open"}}}

from claim | near notes_embedding [0.12, -0.03, …] 10 | where status = "open"

The constraints are the contract, not gaps:

Constraint	Why
k is required and capped at 1000	An uncapped k is an unbounded index scan a caller can request by accident
The vector’s length must equal dim	Checked at compile time, before the server
No distance or score is returned	The caller gets a set of k rows. Ranking them against anything else is a retrieval service’s job
A caller’s own limit narrows k; it cannot widen it	Rows beyond k were never retrieved
near orders the result; an orderby is ignored	“The k nearest” in any other order is k arbitrary rows re-sorted

On the search route

POST /data/rest/{entity}/search takes the same predicates in its body: query is matches, and near is near. Both AND with filter and with every request option.

{"query": "hydroplaning", "filter": {"status": "open"}, "near": {"field": "notes_embedding", "vector": [...], "k": 10}}

Constraints worth knowing

• Language is per entity. to_tsvector is only immutable with a literal configuration, and a generated column may only call immutable functions. Per-row language means giving up the generated column; the answer for a multilingual entity is one entity definition per language.
• No typo tolerance, no ranking. websearch_to_tsquery stems by the configured dictionary and nothing more. That is a retrieval service’s job, deliberately.
• GIN write amplification. A stored tsvector is recomputed on every update touching its source fields, and the index pays for it. Declare search.document narrowly.
• HNSW builds are expensive and lock. The first deploy of a vector field on a large existing table is a scheduled operation, not a deployment step. Below a few thousand rows, index.kind: none is faster and free.
• Per-tenant tables give per-tenant indexes. That is what makes vector search work: filtered ANN over one index shared across tenants walks other tenants’ neighbours and discards them, and recall collapses.

Prerequisites

CREATE EXTENSION IF NOT EXISTS vector;    -- pgvector; vector columns and ANN indexes
CREATE EXTENSION IF NOT EXISTS pg_trgm;   -- trigram indexes for substring

These are installed by a database administrator, not by the migration: CREATE EXTENSION needs privileges a migration should not assume. The Data block qualifies every extension object it emits — the types, the operator classes and the distance operators — so the extensions may live in public (the default) while the tenant’s search_path stays pinned to its own schema. A declaration that needs an extension the database does not have fails at CREATE TABLE with type "vector" does not exist; that is the prerequisite, not a bug.