[Audit L-02] Replace lower(column) referral predicates with lowercase-bound parameters

Brouie commented

2026-05-06 02:09:17 +00:00

(Migrated from gitlab.com)

Source

internal full-stack audit audits/audit_full_1777967937.md (2026-05-05), finding L-02 (Low). Cross-checked at HEAD 390f9be.

Problem

several referral aggregate queries in indexer/src/api.rs use WHERE lower(column) = lower($n) against columns whose stored values are already lowercase (every addr_hex(...) insert path uses format!("{:#x}", a), which emits lowercase) and whose bind params are also lowercased (addr.to_lowercase(), bind_addr_lower).

both lower() calls are redundant CPU, and wrapping the column in lower() defeats the btree index idx_timecurve_referral_applied_referrer (migrations/20260325000000_referral_and_prize_payouts.up.sql:36-37). cost is negligible at current data volume but scales linearly with referral throughput.

Affected lines (HEAD `390f9be`)

indexer/src/api.rs:422 — lower(kk.buyer) = lower(b.buyer) JOIN predicate inside /v1/timecurve/buys LATERAL (both sides come from addr_hex, both already lowercase)
indexer/src/api.rs:1814 — WHERE lower(referrer) = lower($3) in /v1/referrals/applied (param lowercased at line 1807)
indexer/src/api.rs:1971 — WHERE lower(referrer) = lower($1) in /v1/referrals/wallet-charm-summary referrer_charm_wad subquery
indexer/src/api.rs:1974 — WHERE lower(buyer) = lower($1) in same handler, referee_charm_wad subquery
indexer/src/api.rs:1977 — WHERE lower(referrer) = lower($1) in same handler, referred_buy_count subquery
indexer/src/api.rs:1980 — WHERE lower(buyer) = lower($1) in same handler, referee_buy_count subquery (param lowercased at line 1965)

Recommended fix

drop both lower(...) wrappers on the 6 lines above; rely on the existing lowercase invariant on both sides
consider adding a btree index on idx_timecurve_referral_applied(buyer) to mirror the existing referrer index — lines 36-37 of the up migration only cover referrer; the referee_* subqueries scan without an index

Acceptance criteria

no lower(column) = lower($n) patterns remain in indexer/src/api.rs against tables whose addresses are stored lowercase
explain plan on /v1/referrals/wallet-charm-summary for a non-existent referrer hits the idx_timecurve_referral_applied_referrer btree (or seq scan only when the table is small enough that the planner prefers it)
new migration adds idx_timecurve_referral_applied_buyer (optional but matches the symmetry of the queries)
cargo test + cargo clippy --all-targets -- -D warnings clean

References

audit audits/audit_full_1777967937.md L-02
WarBow patterns adopted elsewhere in the file already use the bare-column form (bind_addr_lower(...) + WHERE column = $n), see lines 758, 828, 894, 1085-1108
INV-INDEXER-157 cross-link: this fix touches the same handler family but does not change the public-facing 500 redaction surface

## Source internal full-stack audit `audits/audit_full_1777967937.md` (2026-05-05), finding L-02 (Low). Cross-checked at HEAD `390f9be`. ## Problem several referral aggregate queries in `indexer/src/api.rs` use `WHERE lower(column) = lower($n)` against columns whose stored values are already lowercase (every `addr_hex(...)` insert path uses `format!("{:#x}", a)`, which emits lowercase) and whose bind params are also lowercased (`addr.to_lowercase()`, `bind_addr_lower`). both `lower()` calls are redundant CPU, and wrapping the column in `lower()` defeats the btree index `idx_timecurve_referral_applied_referrer` (`migrations/20260325000000_referral_and_prize_payouts.up.sql:36-37`). cost is negligible at current data volume but scales linearly with referral throughput. ## Affected lines (HEAD `390f9be`) - `indexer/src/api.rs:422` — `lower(kk.buyer) = lower(b.buyer)` JOIN predicate inside `/v1/timecurve/buys` LATERAL (both sides come from `addr_hex`, both already lowercase) - `indexer/src/api.rs:1814` — `WHERE lower(referrer) = lower($3)` in `/v1/referrals/applied` (param lowercased at line 1807) - `indexer/src/api.rs:1971` — `WHERE lower(referrer) = lower($1)` in `/v1/referrals/wallet-charm-summary` referrer_charm_wad subquery - `indexer/src/api.rs:1974` — `WHERE lower(buyer) = lower($1)` in same handler, referee_charm_wad subquery - `indexer/src/api.rs:1977` — `WHERE lower(referrer) = lower($1)` in same handler, referred_buy_count subquery - `indexer/src/api.rs:1980` — `WHERE lower(buyer) = lower($1)` in same handler, referee_buy_count subquery (param lowercased at line 1965) ## Recommended fix 1. drop both `lower(...)` wrappers on the 6 lines above; rely on the existing lowercase invariant on both sides 2. consider adding a btree index on `idx_timecurve_referral_applied(buyer)` to mirror the existing referrer index — `lines 36-37` of the up migration only cover referrer; the `referee_*` subqueries scan without an index ## Acceptance criteria - [ ] no `lower(column) = lower($n)` patterns remain in `indexer/src/api.rs` against tables whose addresses are stored lowercase - [ ] explain plan on `/v1/referrals/wallet-charm-summary` for a non-existent referrer hits the `idx_timecurve_referral_applied_referrer` btree (or seq scan only when the table is small enough that the planner prefers it) - [ ] new migration adds `idx_timecurve_referral_applied_buyer` (optional but matches the symmetry of the queries) - [ ] `cargo test` + `cargo clippy --all-targets -- -D warnings` clean ## References - audit `audits/audit_full_1777967937.md` L-02 - WarBow patterns adopted elsewhere in the file already use the bare-column form (`bind_addr_lower(...)` + `WHERE column = $n`), see lines 758, 828, 894, 1085-1108 - `INV-INDEXER-157` cross-link: this fix touches the same handler family but does not change the public-facing 500 redaction surface

Brouie commented

2026-05-06 02:17:26 +00:00

(Migrated from gitlab.com)

mentioned in merge request !375

PlasticDigits commented

2026-05-06 12:41:52 +00:00

(Migrated from gitlab.com)

mentioned in commit a4ce4a7d55

mentioned in commit a4ce4a7d557f9e720a453eaceb031df398b2278e

PlasticDigits commented

2026-05-06 12:42:34 +00:00

(Migrated from gitlab.com)

Implemented GitLab #165 on main (merge 18a771b).

Summary

Replaced redundant lower(column) = lower($n) predicates with bare column = $n in indexer/src/api.rs for /v1/referrals/applied, /v1/referrals/wallet-charm-summary, and the /v1/timecurve/buys Kumbaya LATERAL join — addresses are already stored lowercase via addr_hex, and bind params are lowercased.
Added migration 20260506210000_idx_timecurve_referral_applied_buyer (idx_timecurve_referral_applied_buyer btree on buyer) with paired .down.sql for sqlx migrate revert.

Docs / cross-links

INV-INDEXER-165 in docs/testing/invariants-and-business-logic.md (§ referrals #94 + anchor #indexer-referral-applied-address-predicates-gitlab-165).
docs/indexer/design.md, docs/product/referrals.md, skills/README.md, .cursor/skills/yieldomega-guardrails/SKILL.md.

Verification checklist

Run sqlx migrate run (or deploy) so idx_timecurve_referral_applied_buyer exists.
EXPLAIN (ANALYZE, BUFFERS) on GET /v1/referrals/wallet-charm-summary?wallet=0x… with a wallet that has no rows: expect Index Scan on idx_timecurve_referral_applied_referrer / _buyer when the planner chooses index vs seq scan on small tables.
Smoke GET /v1/referrals/applied?referrer=0x…, GET /v1/timecurve/buys with Kumbaya rows — results unchanged vs prior behavior.
cargo test + cargo clippy --all-targets -- -D warnings in indexer/ (clean at merge).

Issue left open for operator QA.

/cc @brouie — please verify when convenient.

Implemented GitLab #165 on `main` (merge `18a771b`). **Summary** - Replaced redundant `lower(column) = lower($n)` predicates with bare **`column = $n`** in [`indexer/src/api.rs`](indexer/src/api.rs) for `/v1/referrals/applied`, `/v1/referrals/wallet-charm-summary`, and the `/v1/timecurve/buys` Kumbaya LATERAL join — addresses are already stored lowercase via `addr_hex`, and bind params are lowercased. - Added migration **`20260506210000_idx_timecurve_referral_applied_buyer`** (`idx_timecurve_referral_applied_buyer` btree on **`buyer`**) with paired **`.down.sql`** for `sqlx migrate revert`. **Docs / cross-links** - **`INV-INDEXER-165`** in [`docs/testing/invariants-and-business-logic.md`](docs/testing/invariants-and-business-logic.md) (§ referrals #94 + anchor `#indexer-referral-applied-address-predicates-gitlab-165`). - [`docs/indexer/design.md`](docs/indexer/design.md), [`docs/product/referrals.md`](docs/product/referrals.md), [`skills/README.md`](skills/README.md), [`.cursor/skills/yieldomega-guardrails/SKILL.md`](.cursor/skills/yieldomega-guardrails/SKILL.md). **Verification checklist** - [ ] Run `sqlx migrate run` (or deploy) so **`idx_timecurve_referral_applied_buyer`** exists. - [ ] `EXPLAIN (ANALYZE, BUFFERS)` on `GET /v1/referrals/wallet-charm-summary?wallet=0x…` with a wallet that has **no** rows: expect **Index Scan** on **`idx_timecurve_referral_applied_referrer`** / **`_buyer`** when the planner chooses index vs seq scan on small tables. - [ ] Smoke **`GET /v1/referrals/applied?referrer=0x…`**, **`GET /v1/timecurve/buys`** with Kumbaya rows — results unchanged vs prior behavior. - [ ] `cargo test` + `cargo clippy --all-targets -- -D warnings` in **`indexer/`** (clean at merge). Issue left **open** for operator QA. /cc @brouie — please verify when convenient.

Brouie commented

2026-05-07 02:36:45 +00:00

(Migrated from gitlab.com)

@PlasticDigits — verification PASS on a4ce4a7 (merged MR !375).

migration 20260506210000_idx_timecurve_referral_applied_buyer ran cleanly on the active stack — _sqlx_migrations shows success: t. live \\d idx_timecurve_referral_applied confirms both _referrer and _buyer btree indexes present.
EXPLAIN (ANALYZE, BUFFERS) on the changed predicate paths against the active DB — planner picks the dedicated btrees for both columns:

-- WHERE referrer = $1 (non-existent address)
Aggregate  (cost=4.31..4.33 rows=1) (actual time=0.095..0.096)
  ->  Index Scan using idx_timecurve_referral_applied_referrer  (cost=0.29..4.30) (actual 0.087..0.088)
         Index Cond: ((referrer)::text = ...)
Execution Time: 0.190 ms

-- WHERE buyer = $1 (non-existent address, hits the new btree)
Aggregate  (cost=8.31..8.32 rows=1) (actual time=0.061..0.061)
  ->  Index Scan using idx_timecurve_referral_applied_buyer  (cost=0.29..8.30) (actual 0.057..0.057)
         Index Cond: ((buyer)::text = ...)
Execution Time: 0.107 ms

no seq scan on either path. the new buyer btree is the planner-preferred path for the referee subqueries.

smoke tests against the active sale (26822 buys ingested):

GET /v1/referrals/applied?limit=3&offset=0 — 3 items, paginates clean
GET /v1/referrals/applied?referrer=0x87bdce...8b5e&limit=3 — 3 items, all rows correctly scoped to the queried referrer
GET /v1/referrals/wallet-charm-summary?wallet=0x87bdce...8b5e — referrer_charm_wad and referred_buy_count populated correctly (this wallet is a referrer with 26822 referrals); referee_* zero (wallet is not a referee — correct)
GET /v1/timecurve/buys?limit=3 — 3 items, total 26822, next_offset: 3 (Kumbaya rows ingest path unchanged)

source side — grep "lower(" indexer/src/api.rs returns only the bind_addr_lower helper at :1102 and its 6 caller sites (:770, 840, 906, 1122, 1183, 1283). all the lower(column) = lower($n) patterns from the original 6 cited lines are gone.
cargo clippy --all-targets -- -D warnings && cargo test clean at HEAD 284c15c — 42/42 PASS.

ready for close on your side.

@PlasticDigits — verification PASS on `a4ce4a7` (merged MR !375). 1. migration `20260506210000_idx_timecurve_referral_applied_buyer` ran cleanly on the active stack — `_sqlx_migrations` shows `success: t`. live `\\d idx_timecurve_referral_applied` confirms both `_referrer` and `_buyer` btree indexes present. 2. EXPLAIN (ANALYZE, BUFFERS) on the changed predicate paths against the active DB — planner picks the dedicated btrees for both columns: ``` -- WHERE referrer = $1 (non-existent address) Aggregate (cost=4.31..4.33 rows=1) (actual time=0.095..0.096) -> Index Scan using idx_timecurve_referral_applied_referrer (cost=0.29..4.30) (actual 0.087..0.088) Index Cond: ((referrer)::text = ...) Execution Time: 0.190 ms -- WHERE buyer = $1 (non-existent address, hits the new btree) Aggregate (cost=8.31..8.32 rows=1) (actual time=0.061..0.061) -> Index Scan using idx_timecurve_referral_applied_buyer (cost=0.29..8.30) (actual 0.057..0.057) Index Cond: ((buyer)::text = ...) Execution Time: 0.107 ms ``` no seq scan on either path. the new buyer btree is the planner-preferred path for the referee subqueries. 3. smoke tests against the active sale (26822 buys ingested): - `GET /v1/referrals/applied?limit=3&offset=0` — 3 items, paginates clean - `GET /v1/referrals/applied?referrer=0x87bdce...8b5e&limit=3` — 3 items, all rows correctly scoped to the queried referrer - `GET /v1/referrals/wallet-charm-summary?wallet=0x87bdce...8b5e` — `referrer_charm_wad` and `referred_buy_count` populated correctly (this wallet is a referrer with 26822 referrals); `referee_*` zero (wallet is not a referee — correct) - `GET /v1/timecurve/buys?limit=3` — 3 items, total 26822, `next_offset: 3` (Kumbaya rows ingest path unchanged) 4. source side — `grep "lower(" indexer/src/api.rs` returns only the `bind_addr_lower` helper at `:1102` and its 6 caller sites (`:770, 840, 906, 1122, 1183, 1283`). all the `lower(column) = lower($n)` patterns from the original 6 cited lines are gone. 5. `cargo clippy --all-targets -- -D warnings && cargo test` clean at HEAD `284c15c` — 42/42 PASS. ready for close on your side.

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-05-09 04:57:07 +00:00

PlasticDigits commented

2026-05-13 15:13:33 +00:00

(Migrated from gitlab.com)

mentioned in commit 842f423fbb

mentioned in commit 842f423fbb6082af5a33e40b57fc918b7171505b

Rows
Columns

[Audit L-02] Replace lower(column) referral predicates with lowercase-bound parameters #165

Source

Problem

Affected lines (HEAD 390f9be)

Recommended fix

Acceptance criteria

References

Affected lines (HEAD `390f9be`)