API role has no statement_timeout, /reputation returns 0 instead of 25 for unranked accounts, and two endpoints depend on search_path

Problem and intended result

Three small, independent defects in the API layer, found while measuring the endpoints at full mainnet scale. They are filed together because each is a few lines and they share one place to test (the REST endpoints and their role).

1. reptracker_user has no statement_timeout

db/builtin_roles.sql sets no statement_timeout for the API role. balance_tracker sets ALTER ROLE btracker_user SET statement_timeout = '10s', haf_block_explorer's API role has 10 s and hivemind's 5 s. On a full-scale test node during a HAF catch-up stall on 2026-10-15 (03:06–03:25 UTC), the other apps' /sync-status calls were cancelled at 10 s while reputation_tracker's ran 270 s, 367 s, 143 s and 44 s (postgres log), holding pooled connections the whole time. Earlier stalls show the same pattern (14 s on 2026-09-02, 36–43 s on 2026-09-28, 104 s on 2026-10-13). In steady state /sync-status takes 0.3 ms, so a 10 s cap costs nothing.

Intended: the API role gets statement_timeout = '10s', set the same way balance_tracker sets it.

2. /accounts/{account-name}/reputation returns 0 where everything else returns 25

get_account_reputation returns 0 when the account has no account_reputations row or its raw reputation is 0 (IF _rep IS NULL OR _rep = 0 THEN RETURN 0). On mainnet that is 1,953,393 of 2,621,482 accounts (1,947,285 without a row, 6,108 with reputation 0). For every other small value the function's own formula, max(log10(|rep|) − 9, 0) × 9 + 25, yields 25. The usual display formula (condenser/hive-js repLog10) also yields 25 for 0, since log10(0) = −∞ is clamped by the max(…, 0), and hivemind returns 25: its rep_log10() has IF _rep = 0 THEN RETURN 25, and its reputation_score() uses COALESCE(…, 25), with the comment "In case of reputation being 0, the score is set to 25 rather than 0". So a new account shows 0 through this API and 25 through hivemind and the wallets.

Decided: parity with hivemind — return 25 for an account that exists but has no reputation row or reputation 0. Absent accounts keep the HTTP 400 "Account '…' does not exist". The endpoint description in the OpenAPI block says so. This is a client-visible change, so note it in the change log / commit message.

3. Two endpoints depend on the caller's search_path

get_account_reputation reads the unqualified account_reputations, and get_reptracker_version reads the unqualified version. They work only when the session's search path contains the app schema, as the app's own PostgREST configuration and haf_block_explorer's do. Called from a plain session, SELECT reptracker_endpoints.get_reptracker_version() fails with relation "version" does not exist (reproduced). Other roles have USAGE on reptracker_endpoints.

Intended: these references resolve regardless of the caller's search path, e.g. through the install-time schema name (the endpoints are created through format() with the schema already) or a SET search_path clause on the functions.

Required behavior

  • db/builtin_roles.sql: ALTER ROLE reptracker_user SET statement_timeout = '10s'; (idempotent on reinstall/upgrade).
  • endpoints/get_reputation.sql: return 25 for an existing account with no or zero reputation; update its OpenAPI description (and regenerate the spec block in endpoints/endpoint_schema.sql).
  • endpoints/get_reputation.sql, endpoints/get_rep_version.sql: schema-qualify the table references (honouring a custom install schema).

Acceptance criteria

  • After install or upgrade, reptracker_user's role settings contain statement_timeout=10s.
  • GET /accounts/{a}/reputation for an existing account with no reputation row returns 25; for an account with raw reputation 0 it returns 25; for a nonexistent account it still returns HTTP 400 with "Account '…' does not exist"; values for accounts with reputation (e.g. one negative, one large positive) are unchanged.
  • SELECT reptracker_endpoints.get_reptracker_version() and SELECT reptracker_endpoints.get_account_reputation('<existing account>') succeed in a session whose search_path is the default ("$user", public).
  • The existing tavern/regression tests pass, updated where they encoded the old 0.

Verification

Tavern (pattern) tests for the three reputation cases above; a psql check of the role setting and of the two functions under a default search_path, after install_app.sh and after an upgrade in place.

Integration points

  • db/builtin_roles.sql — role statement_timeout.
  • endpoints/get_reputation.sql — 25 for no/zero reputation; qualified table name; description.
  • endpoints/get_rep_version.sql — qualified table name.
  • endpoints/endpoint_schema.sql — regenerated OpenAPI text for the reputation endpoint.

Scope boundary

Block-processing performance and the VACUUM FULL requests (#13 (closed)) are separate issues. Response caching headers are unchanged.