search

Full-text search per organization on D1’s FTS5, and a list-query helper for plain list routes: tenant, search, filters, sort and a keyset cursor. examples/crm uses both, for its ⌘K palette and every list’s ?q=.

Each record gets a document (a title, a subtitle, a body) in search_documents, written in the same db.batch() as the change, like an audit row. An FTS5 index over that table follows it by trigger. The organization comes from the checked member, never from the input, and every query matches inside it.

Uses void/db, void/schema-d1 and zod, and nothing from other Flare plugins.

Set up

Add the table to db/schema.ts, then pnpm db:generate:

export { searchDocuments } from "@recranet/flare/search/schema"

Drizzle can’t declare an FTS5 table, so append the index and its three triggers to the migration that was just written. SEARCH_INDEX_SQL in @recranet/flare/search/schema is the text to append, after a --> statement-breakpoint line:

--> statement-breakpoint
CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5(tenant, title, subtitle, body, content='search_documents', content_rowid='rowid', tokenize='unicode61 remove_diacritics 2', prefix='2 3');
--> statement-breakpoint
CREATE TRIGGER IF NOT EXISTS search_documents_ai AFTER INSERT ON search_documents BEGIN
  INSERT INTO search_index(rowid, tenant, title, subtitle, body) VALUES (new.rowid, new.tenant, new.title, new.subtitle, new.body);
END;
--> statement-breakpoint
-- ...and search_documents_ad, search_documents_au: copy them from SEARCH_INDEX_SQL

Later void db generate runs compare Drizzle snapshots, so they leave the index alone.

Column	What
rowid	The FTS row id
organization_id	The tenant. From the checked member
tenant	The organization as one FTS token (hex), so the index itself matches inside the tenant
entity_type, entity_id	"contact", its id. Unique per organization
title	What a result shows; weighted most
subtitle	Shown under the title, searched (an email, a domain, a deal’s stage)
body	Searched, never shown (notes, a job title)
updated_at	Milliseconds

Writing

search.put(member, document) adds or replaces a record’s document and returns a Drizzle insert; search.remove(member, type, id) a delete. Put them in the write’s batch:

import { search } from "@recranet/flare/search"

const m = await access.requireMember(c, { contact: ["create"] })
await db.batch([
  db.insert(contacts).values(row),
  search.put(m, { type: "contact", id: row.id, title: `${row.firstName} ${row.lastName}`, subtitle: row.email, body: row.notes }),
])

await db.batch([db.delete(contacts).where(mine), search.remove(m, "contact", id)])

On update, pass the whole record (before merged with the changes), not only what changed: put replaces the document.

search.putMany(member, type, documents) returns upserts of 11 documents each (nine parameters a document, under D1’s 100), for an import’s batches.

A write that doesn’t go through put (a seed, raw SQL, a bulk update) leaves the index behind. search.reindex(tenant, type, documents, { replace: true }) writes a type’s documents again and drops the ones not passed, in inserts of 11 documents (D1 takes 100 parameters a statement) and batches of 50. The CRM runs it nightly per organization as a job.

Searching

const hits = await search.query(m, q, { types: ["contact", "company", "deal"], perType: 5, limit: 15 })
// [{ type: "company", id, title: "Hooli", subtitle: "hooli.example" }, ...]

Best first (FTS5’s bm25, title 10, subtitle 4, body 1; defineSearch({ weights })). limit is 1 to 100, default 20; perType caps each type, for a palette’s groups. An empty query finds nothing.

What a query matches:

• Words are runs of letters and digits. ann@exa is ann and exa.
• Every word must match, in any column, in any order.
• The last word also matches as a prefix (smi finds Smith), for typeahead. Others match whole words.
• Case and accents don’t matter: jose finds José.
• Quotes, *, -, AND, OR, NEAR and column: are plain text: user input never reaches FTS5’s query syntax. At most eight words count.

In a list

search.match(m, type, q, idColumn) is a WHERE condition: the column is the id of one of the organization’s matching documents. Undefined without a query, so and() drops it. It narrows; the list keeps its own order.

db.select().from(contacts).where(and(eq(contacts.organizationId, m.organizationId), search.match(m, "contact", q, contacts.id)))

Lists

defineList(table, options) makes a list route’s decisions and leaves the query to the route. The table needs id and organizationId.

import { defineList, search } from "@recranet/flare/search"

const contactList = defineList(contacts, {
  sort: { createdAt: contacts.createdAt, lastName: { column: contacts.lastName, nocase: true } },
  filters: { companyId: contacts.companyId, since: (v) => gte(contacts.createdAt, new Date(v)) },
  search: (m, q) => search.match(m, "contact", q, contacts.id),
})

export const GET = defineHandler.withValidator({ query: contactList.schema })(async (c, { query }) => {
  const m = await access.requireMember(c, { contact: ["list"] })
  const { where, orderBy, limit, page } = contactList.query(m, query)
  return page(await db.select().from(contacts).where(where).orderBy(...orderBy).limit(limit + 1))
})
// { items: [...], nextCursor: "WyJjcmVh..." }

Option	Default	What
sort		Sorts by URL key: a column, or { column, nocase } to ignore case. The first is the default
defaultDir	desc	The default sort’s direction. ?sort= alone is ascending
filters	none	By URL key: a column (equals the value), or a function from the value to a condition (undefined ignores it)
search	none	?q= as a condition: search.match(...), or like(q, ...columns) for a small table without documents
limit, maxLimit	25, 100	Page size

query(member, input, ...conditions) answers where (the organization, search, filters, the cursor and your conditions), orderBy, limit and page(rows). Select limit + 1 rows: page keeps limit and makes the cursor when there are more.

• Pages are keyset on (sort column, id), never OFFSET. Ties break on id, in the sort’s direction.
• Empty values sort last in both directions. A column declared notNull skips that, so an index on it can serve the order.
• The cursor holds the sort, its direction and the last row’s value and id (base64url JSON). One from another sort, or one that isn’t a cursor, starts at the first page. Allow 500 characters for it in your own schema.
• schema is a Zod object of q, limit, cursor, sort, dir and each filter as an optional string. .extend() it to type a filter (stage: z.enum(dealStages).optional()).
• A page that shows a page count can still use where and orderBy with .offset() and a count(), as the CRM’s list pages do.

Exports and restores

D1’s export refuses a database with a virtual table, and void db export dumps FTS5’s shadow tables, which a restore can’t load back. The index holds nothing the documents don’t, so take it out for the export and put it back from the documents after. DROP_SEARCH_INDEX_SQL, SEARCH_INDEX_SQL and REBUILD_SEARCH_INDEX_SQL hold the statements; save them as files once:

-- drop-search-index.sql (DROP_SEARCH_INDEX_SQL)
DROP TRIGGER IF EXISTS search_documents_ai;
DROP TRIGGER IF EXISTS search_documents_ad;
DROP TRIGGER IF EXISTS search_documents_au;
DROP TABLE IF EXISTS search_index;

-- create-search-index.sql: SEARCH_INDEX_SQL, then REBUILD_SEARCH_INDEX_SQL
INSERT INTO search_index(search_index) VALUES ('rebuild');

pnpm void db execute --remote --file drop-search-index.sql
pnpm wrangler d1 export <database> --remote --output backup.sql
pnpm void db execute --remote --file create-search-index.sql

search.rebuild() does the last step from the Worker (a job, an owner’s route).

Writes go on while the index is gone; search finds nothing until the rebuild, which picks them up. Restore the export into a database without the index, then run the same two. D1 Time Travel restores need none of this.

In examples/crm

• lib/search.ts: what each record type is found by (contacts by name, email, job title, phone and notes; deals show their stage), and reindexOrganization.
• lib/records.ts: create, update and delete each put or remove the document in the batch with the change and its audit row. The JSON API’s lists are a defineList on createdAt with search.match.
• lib/list-pages.ts: the list pages’ sort, search and filters through defineList, with numbered pages.
• routes/api/search.ts: the ⌘K palette, five of each kind the role may list, best first.
• lib/inbound.ts: inbound mail’s activities write their documents in their batch. The seed writes its rows’ documents.
• lib/jobs.ts: the search-reindex job, nightly at 03:30, one queue unit per organization.

Notes

• Search matches word starts, not substrings: mith doesn’t find Smith, where the CRM’s LIKE %q% did. Prefixes of two and three letters are indexed, so short typeahead queries stay cheap.
• query and match join the index with search_documents and check organization_id again, besides the tenant token.
• The index is about the size of the text it holds. Every write adds a statement to the batch and the trigger’s index writes, which count towards D1’s rows written.
• Not here: highlights (FTS5’s snippet()), synonyms, typo tolerance, ranking by recency. For those, or for search across organizations, use a search service.