Search

Add search to a list does the whole job, start to finish.

Give an entity a search: section and people can search its records by words. The compiler generates the index, keeps it up to date and generates its migration, and a query finds records with matches. The search runs on PostgreSQL's full-text search, so you need nothing else: app.jadpo names no connection for it.

search:, matches, SearchLanguage and SearchText are built into Jadpo. Todo, TodoView and find_todos are your app's, shown here.

domains/planning/entities/todo.jadpo:

/// Something to do.entity Todo {    id: Uuid    title: Text {        max_length: 200    }    notes: Text? {        max_length: 2000    }    search: {        fields: [title, notes]        language: SearchLanguage.english    }}

domains/planning/types/todo_view.jadpo, what a list shows of each:

use planning/entities { Todo }/// A todo in a list.type TodoView = Output {    pick: [Todo.id, Todo.title]}

domains/planning/queries/find_todos.jadpo:

use planning/entities { Todo }use planning/types { TodoView }/// The todos whose words match a search, best first.query find_todos(phrase: SearchText) -> List<TodoView> {    from: Todo    where: Todo matches phrase    limit: 20}

This test adds two todos with an action of the app, and finds one. "packing" finds "Pack" because the search reduces each word to its stem.

domains/planning/actions/add_todo.jadpo:

use planning/entities { Todo }/// Adds a todo.action add_todo(title: Todo.title, notes: Todo.notes) -> Todo {    return Todo.create {        set: {            title: title            notes: notes        }    }}

domains/planning/queries/find_todos.test.jadpo:

use planning/entities { Todo }use planning/types { TodoView }use planning/actions { add_todo }test "a word finds the todos that hold it" {    var van_todo: Todo = add_todo("Pack the van", none)    var watering: Todo = add_todo("Water plants", none)    assert van_todo.title == "Pack the van"    assert watering.title == "Water plants"    var packing_todos: List<TodoView> = find_todos("packing")    assert list.map(packing_todos, item -> item.title) == ["Pack the van"]}

• The fields. fields: lists text fields of the entity (a type built on Text, kept in one text column), by name. Fields listed first count most when ordering results: the first field's words weigh most, then the second's and the third's, and the fourth and every field after it weigh least, PostgreSQL's four weights. A field that is none has no words.
• The language. language: is one of SearchLanguage's, PostgreSQL 16's text search languages: arabic, armenian, basque, catalan, danish, dutch, english, finnish, french, german, greek, hindi, hungarian, indonesian, irish, italian, lithuanian, nepali, norwegian, portuguese, romanian, russian, serbian, simple, spanish, swedish, tamil, turkish and yiddish. It reduces each word to its stem, so "packing" finds "pack", and leaves out its stop words, such as "the". simple stems nothing and leaves nothing out. A PostgreSQL search always says its language: there is no default.
• The phrase. matches takes a SearchText (Values and types), what people type into search boxes: 1 to 200 characters with no line breaks, tabs or other control characters. It is read as words, quoted phrases and -word, never raw query syntax: pack "the van" -books finds records with "pack" and the phrase "the van" but not "books". Every word must match; an or someone types is a word like any other, and a stop word in most languages. A phrase of only stop words, or of no words, finds nothing.
• The order. With no order:, the best matches come first, then by id, in keyset pages like every other list (Pages): a page's cursor holds the last record's rank and id. A record's rank is worked out from its own words and the phrase alone. With an order:, the records that match come in that order instead.
• Where matches stands. On a where: line of a list or a page, alone or joined to other conditions by and, once in a query. Not under not or or, not in where_present:, an include or an aggregate query, and not beside a nearest or geo.distance order (query.condition_unsupported).
• search: replaces the full-text index kind: (Special kinds of index): one declaration, not an index plus hand-written conditions.

A page at a time

A long list comes a page at a time: the query returns a Page<TodoView> and says where each page starts. TodoSearch is your app's, and PageSize and PageCursor are built in.

domains/planning/types/todo_search.jadpo:

/// What someone typed into the search box, and where its page starts.type TodoSearch = Input {    phrase: SearchText    page_size: PageSize {        default: 2    }    after: PageCursor {        optional: true    }}

domains/planning/queries/search_todos.jadpo:

use planning/entities { Todo }use planning/types { TodoSearch, TodoView }/// The todos whose words match a search, best first, a page at a time.query search_todos(asked: TodoSearch) -> Page<TodoView> {    from: Todo    where: Todo matches asked.phrase    page: {        size: asked.page_size        after: asked.after    }}

The test adds four todos and searches them a page of two at a time. "Pack books" matches in its title and its notes, so it comes first.

domains/planning/queries/search_todos.test.jadpo:

use planning/entities { Todo }use planning/types { TodoSearch, TodoView }use planning/actions { add_todo }/// Titles joined by commas.type TitleList = Text {}/// The titles a page holds, in order.function titles(page: Page<TodoView>) -> TitleList {    return TitleList(text.join(list.map(page.items, item -> item.title), ", "))}test "the best matches come first, a page at a time" {    var in_notes: Todo = add_todo("Call the bank", "ask about packing boxes")    var in_title: Todo = add_todo("Pack the van", none)    var in_both: Todo = add_todo("Pack books", "packing tape too")    var watering: Todo = add_todo("Water plants", none)    var first: Page<TodoView> = search_todos(        TodoSearch {            phrase: "packing"        }    )    assert titles(first) == "Pack books, Pack the van"    var second: Page<TodoView> = search_todos(        TodoSearch {            phrase: "packing"            after: first.next        }    )    assert titles(second) == "Call the bank"    assert second.next == none    assert in_notes.title == "Call the bank"    assert in_title.title == "Pack the van"    assert in_both.title == "Pack books"    assert watering.title == "Water plants"}test "a quoted phrase, a word left out, and or as a word" {    var van_todo: Todo = add_todo("Pack the van", none)    var books: Todo = add_todo("Pack books", "the van is full")    assert van_todo.title == "Pack the van"    assert books.title == "Pack books"    var phrase: Page<TodoView> = search_todos(        TodoSearch {            phrase: "\"pack the van\""            page_size: 10        }    )    assert titles(phrase) == "Pack the van"    var no_books: Page<TodoView> = search_todos(        TodoSearch {            phrase: "pack -books"            page_size: 10        }    )    assert titles(no_books) == "Pack the van"    var both_words: Page<TodoView> = search_todos(        TodoSearch {            phrase: "books or van"            page_size: 10        }    )    assert titles(both_words) == "Pack books"    var stop_words: Page<TodoView> = search_todos(        TodoSearch {            phrase: "the"            page_size: 10        }    )    assert stop_words.items == []}

How it is kept

The words are a column of the entity's table that PostgreSQL works out from the fields on every write, jadpo_search, so they can never disagree with the record, and a GIN index finds the records that match. jadpo migrate new writes both; the migration for the todos above holds:

CREATE TABLE "todo" (    "id" uuid NOT NULL,    "title" text NOT NULL,    "notes" text,    "jadpo_search" tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english'::regconfig, coalesce("title", '')), 'A') || setweight(to_tsvector('english'::regconfig, coalesce("notes", '')), 'B')) STORED,    CONSTRAINT "todo_pkey" PRIMARY KEY ("id"));CREATE INDEX "todo_jadpo_search_idx" ON "todo" USING gin ("jadpo_search");

• No decision to make. Adding search: to an entity that already has records adds the column, which PostgreSQL fills for every record there, and its index. Changing fields: or language: makes the column again from them, its index with it; removing search: drops both. None needs a backfill or an acceptance, since the column holds nothing that isn't in the fields.
• The column's name is Jadpo's. An entity with search: can't have a field stored as jadpo_search.
• Lifecycle. Like every index the compiler derives, the index holds only the records the entity's visible: lets queries see (Indexes).

jadpo sql search_todos shows the statement, the rank selected beside the record's fields and each page starting after the last one's rank and id:

SELECT "id", "title", ts_rank("jadpo_search", websearch_to_tsquery('english'::regconfig, $1))::float8 AS "jadpo_rank"FROM "todo"WHERE "jadpo_search" @@ websearch_to_tsquery('english'::regconfig, $1)  AND ($2 IS NULL OR ts_rank("jadpo_search", websearch_to_tsquery('english'::regconfig, $1))::float8 < $2 OR (ts_rank("jadpo_search", websearch_to_tsquery('english'::regconfig, $1))::float8 = $2 AND "id" > $3))ORDER BY "jadpo_rank" DESC, "id" ASCLIMIT $4

Search and policy

A search is a query, so policy filters its records exactly as it filters any query's, in the same statement and before the page is cut (When policy says no). A search never shows a record or a field its caller can't read:

• Records. Only the records the caller may read are searched. A record's rank comes from its own words and the phrase, never from other records, so a record the caller can't see never moves one they can, never changes where a page ends and is never counted: a Page holds no total.
• Fields. Which records match a search, and how well, shows the words of every field it lists. So each field in fields: must be one everyone who may run the query may read, as a condition's must (What else shows a field); otherwise policy.output_field_unproved names the matches line.
• No snippets. A result is the query's output type: no highlighted text from a field the output doesn't pick.

Here each member reads only their own notes. Bob's note uses the word three times, but Ann's search never finds it, and her two notes come one to a page as if Bob's weren't there:

domains/notes/entities/note.jadpo:

use notes/entities { Member }use notes/types { NoteText }/// A member's note, which only they may read.entity Note {    id: Uuid    owner_id: Member.id {        on_delete: OnDelete.cascade    }    body: NoteText    policy: {        create: [owner_id]        read: [owner_id]    }    search: {        fields: [body]        language: SearchLanguage.english    }}

domains/notes/queries/search_notes.jadpo:

use notes/entities { Note }use notes/types { NoteView }/// The caller's notes that match a search, best first, one at a time.query search_notes(user: Principal.user, phrase: SearchText, after: PageCursor?) -> Page<NoteView> {    from: Note    where: Note matches phrase    page: {        size: 1        after: after    }}

domains/notes/queries/search_notes.test.jadpo:

use notes/entities { Member, Note }use notes/types { NoteView }use notes/actions { register, write_note }test "a search finds only the caller's notes, and others' never move them" {    var ann: Member = attempt register("ann")    var bob: Member = attempt register("bob")    var budget_plan: Note = write_note(Principal.user(ann), "budget plan")    var budget_review: Note = write_note(        Principal.user(ann),        "budget review, and the budget after it"    )    var bobs_note: Note = write_note(Principal.user(bob), "budget budget budget")    assert budget_plan.body == "budget plan"    assert budget_review.body == "budget review, and the budget after it"    assert bobs_note.body == "budget budget budget"    var first: Page<NoteView> = search_notes(Principal.user(ann), "budget", none)    assert list.map(first.items, item -> item.body) == ["budget review, and the budget after it"]    var second: Page<NoteView> = search_notes(Principal.user(ann), "budget", first.next)    assert list.map(second.items, item -> item.body) == ["budget plan"]    assert second.next == none    var bobs: Page<NoteView> = search_notes(Principal.user(bob), "budget", none)    assert list.map(bobs.items, item -> item.body) == ["budget budget budget"]    assert bobs.next == none}

Search and personal data

A field marked personal: may be searched (Marking personal fields). Its words are kept only in its record's own row, worked out from the field, so whatever clears or deletes the field (erasure, anonymising, retention) clears its words in the same statement, and nothing is copied anywhere else. A field of a sensitive category, such as PersonalData.health, isn't searched yet: every read of a sensitive field is recorded in the access records, which aren't built, so it is toolchain.not_built_yet in fields:. An encrypted field is never searched.

An engine instead of PostgreSQL

Not built yet: store: is toolchain.not_built_yet where it is written (What is built), so every search today is PostgreSQL's. What follows is the design.

Search is the search capability, so an engine's package, such as Meilisearch, Typesense, Algolia or OpenSearch, can fill it. search: { fields: [...] store: products_index } names a connection whose package provides search. The engine's package does the whole job: it upserts a record's searchable fields, removes a record, and queries by words with equality filters.

// domains/catalogue/entities/product.jadpoentity Product {    ...    search: {        fields: [name, description]        store: products_index    }}

• Only equality filters reach an engine. A matches query on an entity whose search uses an engine may only have where: lines that test equality on fields the index holds. Anything else is a compile error naming PostgreSQL search, which can filter any way.
• Ids from the engine, records through policy. The engine returns ranked ids, already filtered by tenant. Jadpo then reads those records through the query, with policy applied as for any query (Policy), so a result is never one the caller couldn't read.
• Kept up to date after commit. Every committed change to a searchable field reaches the engine through the outbox, and is retried like any delivery (Delivery: at least once, safe to repeat). Erasing a person removes their records from the index (Erasure). jadpo search reindex <entity>, not built yet, will rebuild the index.
• Engine features (synonyms, ranking rules, typo tolerance settings) are the package's own operations, called from a domain the connection lists.
• Development and tests use PostgreSQL search whatever production uses, so nothing leaves the machine and no engine is needed to run a test.