How Search works

Search finds rows inside one profile. It can search one table or all readable tables in that profile, and it returns complete current rows rather than copies stored in the text index.

There are two distinct behaviors:

Input	What the server does
No search text or column conditions	Lists live rows directly from PostgreSQL. A table must be selected.
Search text, one or more column conditions, or both	Finds candidates in the profile’s search index, then loads the matching live rows from PostgreSQL.

This distinction matters. Empty Search is useful for opening a picker or browsing a table. A populated Search is useful for finding rows by their content.

What is searchable

For every indexed row, Search includes:

• non-empty text values;
• numeric values, represented as text;
• dates, times, and timestamps in the same normalized textual form used by the public row data.

Search does not include booleans, nulls, nested objects, arrays, or blank strings. It also excludes server bookkeeping such as row ID, deletion state, revision, creation time, and the internal accounting account reference.

Column conditions use the public column name from the table definition. Clients do not need to know the physical database column name. Across a whole profile, a condition applies to every table that has that public column name; within a selected table, it applies only to that table’s column.

Choose useful public names

A profile-wide condition such as customer = Acme intentionally reaches every readable table with a column publicly named customer. Reuse a name when columns have the same meaning, and choose distinct names when they do not.

The profile index

Each profile has one text index shared by all its tables. Table identity remains attached to every indexed row, so Search can restrict a query to one table and can identify the source table of a profile-wide result.

The index is created lazily when the background worker processes the profile’s first indexable row. A new or empty profile may therefore have no text index yet: a populated search reports that no index exists, while an empty-query table listing still works.

The index is a lookup aid, not the source of truth. After it identifies candidate IDs, the server reads the rows from PostgreSQL again. As a result:

• returned content uses public column names;
• a row deleted after it was indexed is not returned;
• current row display values come from the table definition;
• stale index content is never returned as if it were the current row.

If a row contains no searchable values, it has no index document. It can still appear in an empty-query table listing, because that path reads PostgreSQL directly.

When the index changes

Creating, updating, or deleting table data records an indexing job in the same PostgreSQL transaction as the row change. Accounting journal changes and quantity-ledger changes also enqueue updates for their derived searchable rows.

A background worker applies those jobs to the text index. This makes Search eventually consistent:

1. the row transaction commits;
2. the durable indexing job becomes available;
3. the background worker updates the profile index;
4. a search reader observes the committed index update.

The row can therefore be readable through table data slightly before a populated Search finds its new content. Empty-query listing does not have this delay because it bypasses the index.

Indexing jobs survive server restarts. A failed job remains queued and is retried with increasing delays. Updates are safe to replay: the worker removes the previous document for the table-and-row pair before adding the replacement.

Soft-deleted rows and rows that no longer exist are removed from the index. A row whose last searchable value becomes empty is removed as well.

Read permissions

The text index is shared within the profile, but Search never returns rows from a table the caller cannot read.

• When a request selects one table, the server checks read access to that table before searching.
• When a request searches the entire profile, the server removes hits from unreadable tables before returning the response.

Because permission filtering happens after ranking a profile-wide page, a page can contain fewer visible results than its requested limit. Selecting a readable table avoids that particular ambiguity.

Read access is a data-plane grant on the table’s permission object. See Roles and permissions for how grants work.

What a result contains

Each hit identifies the row and its source table, includes the current database row as JSON with user columns remapped to their public names, and provides the table’s configured row display columns together with their values. Search matches also include a relevance score. A table listing has a score of zero because it did not perform text matching.

The hit JSON is not a replacement for a full table-data read. Search reloads the database row, but it does not run every presentation transformation or optional hydration available through table data. Use the hit’s table and row ID to read the row normally when the client needs that richer representation.

The optional position is the row’s one-based position in ID order among non-deleted rows of that table. It is present for a table listing and for a sorted search, but not for the ordinary relevance-ranked result.

Continue with Queries, matching, and ordering to choose between free text, column conditions, exact matching, fuzzy matching, and table listing.