ADR-0022: Text search through definer functions that return ids
- Status: Accepted
- Date: 2026-11-12
Context and problem statement
Section titled “Context and problem statement”Lists and the global search find records by any part of a name, email, phone or number, using a trigram index on a search_text column. Under row level security (ADR-0002) PostgreSQL evaluates the policy first and uses a condition of the query as an index condition only if its operator is leakproof. LIKE is not. The index narrowed the search to the company and then every row of the company was filtered one by one. On the volume seed, a rare term in a large company missed the response-time targets of the lists and of the global search.
How can the search use its index without weakening the isolation between companies?
Decision drivers
Section titled “Decision drivers”- The search must meet its response-time targets at the volume of a large company.
- No change may let a query evaluate anything on rows of another company.
- No dependency on changes to the PostgreSQL system catalog or to an extension.
Considered options
Section titled “Considered options”All seven were evaluated when the decision was made.
- Mark the
LIKEfunction of PostgreSQL as leakproof. Rejected: it is not leakproof, since it can fail depending on its arguments, and marked that way the planner could evaluate it on rows of other companies before the policy. It also changes the system catalog. - An own leakproof function or operator. Rejected: either it is expanded back to
LIKE, or it cannot be used by the index without altering the operator family of the extension. - Rewrite the policy. Rejected: the restriction applies to any policy, whatever its form.
- A view with a security barrier. Rejected: the barrier is what prevents the condition from being pushed down, and without it the view leaks.
- The similarity operators of
pg_trgm. Rejected: not leakproof either, and they change the meaning from “contains” to “similar”. - A function that receives the company id and returns ids. Rejected: the isolation would depend on the API never passing the wrong id.
- One
SECURITY DEFINERfunction per table, with no company parameter, that reads the company from the transaction context and returns only ids.
Decision outcome
Section titled “Decision outcome”Chosen option: 7.
Six functions, search.<table>_ids, cover candidates, people, vehicles, customers, jobs and locations. They belong to the role ochema_search, which cannot log in and may read four columns of those tables: id, tenant_id, archived_at and search_text. The company comes only from app.tenant_id(): without a context a function returns nothing. The ids returned are then used in a query of the API that still runs under row level security, so the policy checks every row a second time.
Lists search in two steps. The function is called with a limit of 1,001 ids. Up to 1,000, the list filters by those ids. Above that, the term is common and the list reads in the order of its own index, where it stops early. To keep every pattern usable by the trigram index, a search term needs at least 3 letters or digits in a row. The API and the screens apply the same rule.
Consequences
Section titled “Consequences”- Good, because substring search uses the trigram index and the result is still filtered by the policy.
- Good, because nothing in the system catalog or in the extension is altered.
- Bad, because it is one more controlled exception to the isolation, with its own role, privileges and tests to maintain.
- Bad, because a term without 3 letters or digits in a row is refused. A person whose names are all shorter than that, such as “Li”, cannot be found by name alone: the screen tells the user to add the first name or to search by email or phone.
- Bad, because a new searchable table needs its own function and privileges, through a migration.
More information
Section titled “More information”packages/db/migrations/0014_wave01_volume_plans.sql: the six functions, the role privileges andapp.search_matches.apps/api/src/core/registers/list.ts: the two-step search of the lists.apps/web/src/registers/search-terms.ts: the minimum-term rule on the screens.packages/db/test/volume-plans.test.ts: isolation and catalog tests of the functions.- Concept page: Tenant isolation.
- Related: ADR-0003.