app.storage.postgres.search

Full-text search and the SQL approval-state expression.

approval_join() mirrors app.domain.approval.evaluate() rule by rule (see that module’s docstring) as a set-based SQL expression over decision_event, so the “needs approval” filter is applied before pagination:

  1. no valid policy -> policy_missing;

  2. policy not required -> not_required;

  3. the event for (identity, current digest) is accepted -> accepted;

  4. that event is rejected -> rejected;

  5. an accepted event for the identity exists (necessarily for another digest, because rules 3/4 did not apply) -> changed_since_acceptance;

  6. otherwise pending.

Rules 1 and 2 depend only on the policy and are resolved in Python to a constant. A parity test compares this expression with the domain function on a fixture set.

Rules 3/4 join decision_event on identity and digest directly: the unique constraint uq_decision_event_identity_digest (one formal decision per content hash, see docs/developer/decisions/0006-content-digest-approval.rst) guarantees at most one event, so that event is the domain’s “latest event for the current digest” and the join never duplicates a record row. Rule 5 is an EXISTS over the identity’s accepted events, which PostgreSQL plans as a hashed subplan (one scan of the accepted events) when many rows are evaluated and as per-row probes of uq_decision_event_identity_digest (identity prefix) for a single page. This replaced two correlated ORDER BY ... LIMIT 1 subqueries that ran ~20,000 index probes per statement at 20,000 records and whose cost estimate pushed PostgreSQL into ~0.4-0.8 s of JIT compilation per statement (see docs/developer/performance.rst); the new statements cost far below jit_above_cost, so JIT stays off without any setting.

Without the “needs approval” filter the state does not restrict the rows, so the page is selected first and only its rows are joined to the decision events; with the filter the join precedes LIMIT.

The record_kind filter (decisions / errata) is a plain WHERE condition shared by both paths, so it combines with every other filter including “needs approval”.

Order. SearchSort picks a descending leading key (text rank for relevance with a query, updated_at for recent, none for title) followed by the lower-cased title and the id, so equal leading keys still give stable pages. Both page paths (with and without “needs approval”) use the same key list.

Text queries use websearch_to_tsquery with the simple configuration (exact tokens: identifiers, UUIDs, paths) OR’ed with the english configuration (stemmed body words), matching how the record.search vector is built by the migration trigger.

Public API: NEEDS_APPROVAL_STATES, approval_join(), search().

app.storage.postgres.search.NEEDS_APPROVAL_STATES: frozenset[ApprovalStateCode] = frozenset({ApprovalStateCode.CHANGED_SINCE_ACCEPTANCE, ApprovalStateCode.PENDING, ApprovalStateCode.POLICY_MISSING, ApprovalStateCode.REJECTED})

States for which app.domain.approval.evaluate() reports needs_approval.

app.storage.postgres.search.approval_join(base, policy)

Join record rows to the evidence their approval state depends on.

Parameters:

  • base (FromClause) – record or a subquery of it exposing the identity columns and digest.

  • policy (Policy | None) – The configured policy, or None if missing or invalid.

Return type:

tuple[FromClause, ColumnElement[str]]

Returns:

The relation to select from (base itself when the policy alone decides the state) and the state expression yielding an ApprovalStateCode value per row.

app.storage.postgres.search.search(session, query, filters, page, page_size, policy, sort=SearchSort.RELEVANCE)

Run a filtered, paginated full-text search.

Parameters:

  • session (Session) – Unit-of-work session.

  • query (str | None) – Free text, or None/blank for all records.

  • filters (SearchFilters) – Filters.

  • page (int) – 1-based page number.

  • page_size (int) – Page size, 1 to MAX_PAGE_SIZE.

  • policy (Policy | None) – Configured policy, or None.

  • sort (SearchSort) – Result order (see SearchSort).

Return type:

SearchPage

Returns:

The page.

Raises:

ValueError – If page or page_size is out of bounds.