How Search2 works

Search2 reads one table directly from PostgreSQL. It is intended for structured table filtering: select a table, add zero or more column filters, optionally add a broad text condition, and choose an order and result limit.

It does not use the profile text index and does not calculate relevance. A committed row is visible immediately, subject to normal database transaction visibility.

Search2 compared with Search

Behavior	Search2	Search
Scope	Exactly one table	One table or a whole profile
Data source	Live PostgreSQL rows	Text index for candidates, then PostgreSQL rows
Main strength	Explicit SQL-style column filters	Fuzzy, prefix, and relevance-ranked text lookup
Consistency after a write	Immediate	Eventually consistent for populated queries
Ranking	None	Relevance score
Total count	Returned	Not returned
Offset	Not supported	Supported
Default order	Row ID descending	Relevance, or position for an empty listing

Use Search2 when a screen is tied to one table and needs predictable predicates such as equality, null checks, or ranges. Use Search when users need typo-tolerant discovery, ranked results, or a search across several tables.

Selecting the table

Both the profile and table are required. Search2 verifies that the table belongs to the profile and checks that the caller has read permission before running the query. It cannot search across all tables in a profile.

Only non-deleted rows participate. A request with no filters and no text condition is valid and lists the table’s live rows.

Public column names from the table definition are used for filtering and ordering. Search2 translates those names to internal columns on the server. Supplying a physical database column name is rejected. Public server columns such as id, created_at, and row_revision can also be named directly.

How conditions combine

Every supplied condition is joined with logical AND:

not deleted
AND each column filter
AND the optional text condition

For example:

table: invoice
status equals issued
issue_date range 2026-01-01 through 2026-12-31
text contains Acme

The row must satisfy both column filters and must contain Acme somewhere in its database values.

Search2 does not currently provide OR groups, negated filters, joins, related-table predicates, or relevance weighting.

The fallback text condition

The optional text condition converts every value in the row to text and performs a case-insensitive substring match across them. This includes user columns and server-managed columns.

It is broader and simpler than Search free text:

• the whole input is treated as one substring rather than separate required words;
• it has no typo tolerance, prefix ranking, phrase boost, or score;
• it can match textual forms of numbers, booleans, dates, IDs, and revisions;
• % and _ retain their SQL pattern meanings.

For example, Acme Ltd matches that consecutive substring, but it does not independently require Acme and Ltd in two different columns.

An absent, empty, or whitespace-only text condition adds no text predicate.

Pattern characters are not escaped

In the text condition and pattern-based column filters, % matches any sequence and _ matches one character. Do not treat user input as a literal substring unless the client prevents or deliberately explains these wildcard characters.

Limits and counts

Search2 returns at most 100 rows when no limit is supplied. A supplied limit is inserted directly into the SQL query; unlike Search, Search2 does not impose a documented maximum.

The response also includes the total number of rows satisfying the same conditions before the limit. This lets a client show that more matches exist.

Hits and the count are read by two consecutive database queries rather than one snapshot. Under concurrent inserts, updates, or deletions, the returned count can briefly differ from the set of hits observed by that request.

Search2 has no offset or cursor. The total count therefore does not by itself provide ordinary page navigation: clients can request a larger limit, but they cannot request only the next page through Search2.

A limit of zero returns no hits while still returning the total count. Negative limits are not useful and cause the database query to fail.

Returned rows

Each hit contains:

• the row ID;
• the current row as JSON, with user columns changed back to public names;
• the configured row display column names and positionally aligned values;
• a generic match description identifying the searched table.

Temporal values are normalized to the server’s public textual representation. A null or unsupported row-display value becomes an empty string so that the values remain aligned with the display-column list.

Search2 returns no score and does not explain which particular condition or column matched. Its match description is informational, not a match trace.

As with Search, the hit JSON is a direct row representation rather than every optional table-data hydration or presentation transformation. Use the returned ID to read the row normally when the client needs the richer table-data view.

Continue with Filters and ordering for every filter type, type compatibility, wildcard behavior, and sorting rules.