code/yieldomega
Watch
4
0
Fork
You've already forked yieldomega
 0
Code Issues 1 Pull requests 1 Projects Releases Packages Wiki Activity

Referrals leaderboard: show registered-referral counts (indexer + API + UI) #204

New issue
Closed
opened 2026-05-13 15:58:56 +00:00 by PlasticDigits · 15 comments
PlasticDigits commented 2026-05-13 15:58:56 +00:00 (Migrated from gitlab.com)

Summary

The /referrals Guide leaderboard should surface how many referrals have been registered (or an equivalently clear onchain-derived metric). Today the board and GET /v1/referrals/referrer-leaderboard only aggregate TimeCurve ReferralApplied rows (referred_buy_count is a buy count, and copy speaks in terms of buys / CHARM).

Background

  • ReferralRegistry emits ReferralCodeRegistered on successful registerCode. The indexer already persists these in Postgres as idx_referral_code_registered (see initial referral migration).
  • Referrer attribution for buys lives in TimeCurve ReferralApplied → idx_timecurve_referral_applied (existing leaderboard SQL ranks by summed referrer_amount).

Work to scope

  1. Product / naming: Decide what “registered” means in UI copy:

    • A) Global or panel-level ReferralCodeRegistered totals (codes claimed on ReferralRegistry), and/or
    • B) Per-referrer distinct referees (COUNT(DISTINCT buyer) from idx_timecurve_referral_applied vs. current COUNT(*) buy rows), if the intent is “how many wallets used this guide’s code” rather than “how many qualifying buys”.

  2. Indexer / API: Extend the referrals HTTP surface (likely GET /v1/referrals/referrer-leaderboard response schema and SQL, with SCHEMA_VERSION bump per repo conventions) so the frontend can render the chosen metric(s) without synthetic scores. Reuse existing tables where possible (idx_referral_code_registered, idx_timecurve_referral_applied).

  3. Frontend: Update ReferralLeaderboardSection / indexerApi types to display the new field(s) and align summary + row copy with the chosen definition.

  4. Tests: Add or extend indexer integration coverage for the new aggregates; adjust any Vitest paths for API types if needed.

Related

/cc @PlasticDigits/yieldomega

## Summary The `/referrals` **Guide leaderboard** should surface **how many referrals have been registered** (or an equivalently clear onchain-derived metric). Today the board and `GET /v1/referrals/referrer-leaderboard` only aggregate **TimeCurve `ReferralApplied`** rows (`referred_buy_count` is a **buy** count, and copy speaks in terms of buys / CHARM). ## Background - **ReferralRegistry** emits `ReferralCodeRegistered` on successful `registerCode`. The indexer already persists these in Postgres as `idx_referral_code_registered` (see initial referral migration). - **Referrer attribution** for buys lives in **TimeCurve** `ReferralApplied` → `idx_timecurve_referral_applied` (existing leaderboard SQL ranks by summed `referrer_amount`). ## Work to scope 1. **Product / naming:** Decide what “registered” means in UI copy: - **A)** Global or panel-level **`ReferralCodeRegistered`** totals (codes claimed on `ReferralRegistry`), and/or - **B)** Per-referrer **distinct referees** (`COUNT(DISTINCT buyer)` from `idx_timecurve_referral_applied` vs. current `COUNT(*)` buy rows), if the intent is “how many wallets used this guide’s code” rather than “how many qualifying buys”. 2. **Indexer / API:** Extend the referrals HTTP surface (likely `GET /v1/referrals/referrer-leaderboard` response schema and SQL, with `SCHEMA_VERSION` bump per repo conventions) so the frontend can render the chosen metric(s) without synthetic scores. Reuse existing tables where possible (`idx_referral_code_registered`, `idx_timecurve_referral_applied`). 3. **Frontend:** Update `ReferralLeaderboardSection` / `indexerApi` types to display the new field(s) and align summary + row copy with the chosen definition. 4. **Tests:** Add or extend indexer integration coverage for the new aggregates; adjust any Vitest paths for API types if needed. ## Related - Referrals leaderboard + wallet earnings: https://gitlab.com/PlasticDigits/yieldomega/-/issues/94 - `/referrals` surface / register UX: https://gitlab.com/PlasticDigits/yieldomega/-/issues/64 /cc @PlasticDigits/yieldomega
Brouie commented 2026-05-14 07:40:42 +00:00 (Migrated from gitlab.com)

quick data dump from prod since the ticket has the A vs B decision still open. counted both event types onchain on MegaETH mainnet so the product call has real numbers to work from.

onchain right now (sale opens May 18 12:00 UTC, so we're 4 days pre-launch):

Event Source Count
ReferralCodeRegistered (Registry) 0xedebced1...adaf7 6
ReferralApplied (TimeCurve) 0x1b68bb67...2bf71 0

unique referrer wallets: 6 (1:1 with codes, contract enforces ownerCode[msg.sender] == 0 at ReferralRegistry.sol:64).

implication for the A vs B decision:

current leaderboard logic (buys + CHARM, ranks by referrer_amount) renders zero rows through the entire pre-launch window because ReferralApplied is empty until the first qualifying buy lands. that's the "looks broken" state.

option A (registered count) gives the leaderboard 6 visible rows immediately, populated by anyone who's already claimed a code. fills the empty-state gap during pre-launch.

option B (distinct referees) is still zero pre-launch — same problem as the current logic.

if the goal is "make the leaderboard feel alive before sale opens", A is the clear win. once sale opens and ReferralApplied starts emitting, the board can blend both: A as a "guides ready" baseline + B as the active-attribution metric layered on top.

if you want me to run additional data slices (block ranges, time bands, anything else), happy to.

@PlasticDigits

quick data dump from prod since the ticket has the A vs B decision still open. counted both event types onchain on MegaETH mainnet so the product call has real numbers to work from. **onchain right now (sale opens May 18 12:00 UTC, so we're 4 days pre-launch):** | Event | Source | Count | |---|---|---| | `ReferralCodeRegistered` (Registry) | `0xedebced1...adaf7` | **6** | | `ReferralApplied` (TimeCurve) | `0x1b68bb67...2bf71` | **0** | unique referrer wallets: 6 (1:1 with codes, contract enforces `ownerCode[msg.sender] == 0` at `ReferralRegistry.sol:64`). **implication for the A vs B decision:** current leaderboard logic (buys + CHARM, ranks by `referrer_amount`) renders **zero rows** through the entire pre-launch window because `ReferralApplied` is empty until the first qualifying buy lands. that's the "looks broken" state. option A (registered count) gives the leaderboard **6 visible rows immediately**, populated by anyone who's already claimed a code. fills the empty-state gap during pre-launch. option B (distinct referees) is still zero pre-launch — same problem as the current logic. if the goal is "make the leaderboard feel alive before sale opens", A is the clear win. once sale opens and `ReferralApplied` starts emitting, the board can blend both: A as a "guides ready" baseline + B as the active-attribution metric layered on top. if you want me to run additional data slices (block ranges, time bands, anything else), happy to. @PlasticDigits
PlasticDigits commented 2026-05-14 13:45:33 +00:00 (Migrated from gitlab.com)

Must use option a

Must use option a
PlasticDigits commented 2026-05-14 13:57:24 +00:00 (Migrated from gitlab.com)

mentioned in commit a57d0d9611

mentioned in commit a57d0d9611a9f4fa31df4e17c41dd81b97a618d8
PlasticDigits commented 2026-05-14 13:59:40 +00:00 (Migrated from gitlab.com)

Shipped: option A (registry union) — ready for verification

Merged to main: referrer leaderboard now unions idx_referral_code_registered.owner_address with idx_timecurve_referral_applied.referrer, adds JSON codes_registered_count, bumps indexer x-schema-version to 1.19.0, and updates /referrals Guide leaderboard copy + summary tiles.

Checklist for reviewers

  • GET /v1/referrals/referrer-leaderboard (indexer ≥ 1.19.0) returns rows for wallets with only ReferralCodeRegistered indexed (zero ReferralApplied rows): codes_registered_count ≥ 1, referred_buy_count / CHARM totals 0.
  • /referrals Guide leaderboard lists those wallets with “onchain codes registered” + recorded buys sublines; empty state copy matches when the union is empty.
  • After referred buys index, CHARM / buy counts move while registration counts stay consistent.
  • Dense rank / tie ordering unchanged vs #177 (postgres_gitlab177_referrer_leaderboard_dense_rank still passes when YIELDOMEGA_PG_TEST_URL is set).
  • Docs: INV-INDEXER-204-REFERRAL-LB-REGISTRY, manual QA #204, invariants §204, skills/README.md contributor index.

/cc @brouie Please verify against prod/staging when the deployed indexer is on 1.19.0+. Issue left open per maintainer request.

## Shipped: option A (registry union) — ready for verification Merged to `main`: referrer leaderboard now **unions** `idx_referral_code_registered.owner_address` with `idx_timecurve_referral_applied.referrer`, adds JSON **`codes_registered_count`**, bumps indexer **`x-schema-version`** to **1.19.0**, and updates `/referrals` Guide leaderboard copy + summary tiles. ### Checklist for reviewers - [ ] `GET /v1/referrals/referrer-leaderboard` (indexer **≥ 1.19.0**) returns rows for wallets with **only** `ReferralCodeRegistered` indexed (zero `ReferralApplied` rows): `codes_registered_count` ≥ 1, `referred_buy_count` / CHARM totals **0**. - [ ] `/referrals` Guide leaderboard lists those wallets with **“onchain codes registered”** + **recorded buys** sublines; empty state copy matches when the union is empty. - [ ] After referred buys index, CHARM / buy counts move while registration counts stay consistent. - [ ] Dense **`rank`** / tie ordering unchanged vs **#177** (`postgres_gitlab177_referrer_leaderboard_dense_rank` still passes when `YIELDOMEGA_PG_TEST_URL` is set). - [ ] Docs: **`INV-INDEXER-204-REFERRAL-LB-REGISTRY`**, [manual QA #204](docs/testing/manual-qa-checklists.md#manual-qa-issue-204-referrer-leaderboard-registry-union), [invariants §204](docs/testing/invariants-and-business-logic.md#referrer-leaderboard-registry-union-gitlab-204), [`skills/README.md`](skills/README.md) contributor index. /cc @brouie Please verify against prod/staging when the deployed indexer is on **1.19.0+**. Issue left **open** per maintainer request.
PlasticDigits commented 2026-05-14 14:00:11 +00:00 (Migrated from gitlab.com)

Doc anchors (absolute paths for the note UI): invariants §204 · manual QA #204 · product referrals dashboard.

Doc anchors (absolute paths for the note UI): [invariants §204](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/docs/testing/invariants-and-business-logic.md#referrer-leaderboard-registry-union-gitlab-204) · [manual QA #204](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/docs/testing/manual-qa-checklists.md#manual-qa-issue-204-referrer-leaderboard-registry-union) · [product referrals dashboard](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/docs/product/referrals.md#referrals-dashboard-issue-94).
Brouie commented 2026-05-15 02:40:24 +00:00 (Migrated from gitlab.com)

Walked the verification checklist against the local stack with a freshly built indexer at schema 1.19.0. Item 4 (dense-rank) fails — flagging a regression introduced by the #204 SQL rewrite. Other items pass or are deferred where the pre-launch state blocks them.

Item 1 — Endpoint returns codes_registered_count with schema 1.19.0

Rebuilt indexer to 1.19.0 (registry JSON pointed at the live anvil addresses since the disk file was stale relative to the live anvil state). Seeded a code-only wallet onchain: anvil account #25 (0xdf37f81d...0913) called approve(registry, 1 CL8Y) then registerCode("qaonly204"). Tx 0xca3d1902... shipped clean (status 1, ReferralCodeRegistered event emitted, 1 CL8Y burn to 0xdead).

After indexer restart + ingestion catchup, curl -i http://127.0.0.1:3100/v1/referrals/referrer-leaderboard returns:

HTTP/1.1 200 OK
content-type: application/json
x-schema-version: 1.19.0

{"items":[
  {"codes_registered_count":"1","rank":1,"referred_buy_count":"21230","referrer":"0x87bdce72...","total_referrer_charm_wad":"5775841329796922015729"},
  {"codes_registered_count":"1","rank":2,"referred_buy_count":"0","referrer":"0xdf37f81daad2b0327a0a50003740e1c935c70913","total_referrer_charm_wad":"0"}
],"limit":50,"next_offset":null,"offset":0}

Row 2 is the spec's literal case: wallet with only ReferralCodeRegistered indexed, zero ReferralApplied, codes_registered_count=1, referred_buy_count=0, total_referrer_charm_wad=0. The union over the two source tables works as designed.

Item 4 — postgres_gitlab177_referrer_leaderboard_dense_rank FAILS

cd indexer && YIELDOMEGA_PG_TEST_URL="postgres://yieldomega:password@127.0.0.1:5433/yieldomega_test_204" cargo test --release postgres_gitlab177_referrer_leaderboard_dense_rank

thread 'postgres_gitlab177_referrer_leaderboard_dense_rank' panicked at tests/integration_stage2.rs:1454:5:
assertion `left == right` failed
  left: Some(3)
 right: Some(2)

Reproduced deterministically on a fresh ephemeral DB (yieldomega_test_204). Not a data-pollution flake — the test seeds its own clean 4-referrer fixture (300/200/200/100 wad) and asserts dense-competitive ranks 1, 2, 2, 4. The endpoint returns 1, 2, 3, 4 instead, breaking on the tie between the two 200-wad referrers (bbbb and cccc).

Root cause looks to be the rewritten window function in indexer/src/api.rs:1921:

RANK() OVER (
    ORDER BY COALESCE(a.total_charm, 0) DESC NULLS LAST, r.referrer ASC
)

With both total_charm DESC and r.referrer ASC inside the same ORDER BY, RANK() only ties rows when both columns match. Since every referrer is unique, no two rows ever tie and RANK() degrades to page-ordinal (1, 2, 3, 4). The pre-#204 fix (commit 6d92cc4 for GitLab #177) had the dense-competitive behavior; #204's SQL rewrite regressed it.

The invariants doc still asserts the dense-rank requirement: invariants-and-business-logic.md:929 ties INV-INDEXER-204-REFERRAL-LB-REGISTRY to INV-INDEXER-170-REFERRAL-LB-TIE and postgres_gitlab177_referrer_leaderboard_dense_rank. So docs and test agree — the SQL is the broken piece.

Likely fix shape: keep the referrer ASC tiebreaker on the outer ORDER BY only, and rank on total_charm DESC alone inside the window — e.g.:

RANK() OVER (ORDER BY COALESCE(a.total_charm, 0) DESC NULLS LAST) AS rank
...
ORDER BY rank ASC, referrer ASC

Items 2 + 3 — deferred

  • Item 2 (Guide leaderboard frontend walk): held until the rank SQL is fixed — the page would render wrong ordinal ranks against the current API, which doesn't tell us anything new beyond what item 4 already shows.
  • Item 3 (CHARM moves while registration counts stay consistent after a buy lands): blocked pre-launch — ReferralApplied won't emit until the first qualifying buy. Onchain check (prior #204 note): 0 ReferralApplied events on prod TimeCurve.

Item 5 — Docs anchors

  • INV-INDEXER-204-REFERRAL-LB-REGISTRY present at docs/testing/invariants-and-business-logic.md:912 and :929 with the union spec + codes_registered_count rule
  • manual-qa-issue-204-referrer-leaderboard-registry-union anchor in docs/testing/manual-qa-checklists.md:157, TOC entry at :13
  • invariants §204 cross-ref from manual-qa file at :165
  • skills/README.md:48 indexes the #204 union pointer

Summary

Items 1 and 5 sign off cleanly. Item 4 surfaces a real regression in the rank SQL that needs a fix before frontend (item 2) is meaningful, and item 3 stays blocked until sale opens. Holding off on close until the SQL is patched.

@PlasticDigits

Walked the verification checklist against the local stack with a freshly built indexer at schema 1.19.0. Item 4 (dense-rank) fails — flagging a regression introduced by the #204 SQL rewrite. Other items pass or are deferred where the pre-launch state blocks them. ## Item 1 — Endpoint returns `codes_registered_count` with schema 1.19.0 Rebuilt indexer to 1.19.0 (registry JSON pointed at the live anvil addresses since the disk file was stale relative to the live anvil state). Seeded a code-only wallet onchain: anvil account #25 (`0xdf37f81d...0913`) called `approve(registry, 1 CL8Y)` then `registerCode("qaonly204")`. Tx `0xca3d1902...` shipped clean (status 1, ReferralCodeRegistered event emitted, 1 CL8Y burn to 0xdead). After indexer restart + ingestion catchup, `curl -i http://127.0.0.1:3100/v1/referrals/referrer-leaderboard` returns: ``` HTTP/1.1 200 OK content-type: application/json x-schema-version: 1.19.0 {"items":[ {"codes_registered_count":"1","rank":1,"referred_buy_count":"21230","referrer":"0x87bdce72...","total_referrer_charm_wad":"5775841329796922015729"}, {"codes_registered_count":"1","rank":2,"referred_buy_count":"0","referrer":"0xdf37f81daad2b0327a0a50003740e1c935c70913","total_referrer_charm_wad":"0"} ],"limit":50,"next_offset":null,"offset":0} ``` Row 2 is the spec's literal case: wallet with only `ReferralCodeRegistered` indexed, zero `ReferralApplied`, `codes_registered_count=1`, `referred_buy_count=0`, `total_referrer_charm_wad=0`. The union over the two source tables works as designed. ## Item 4 — `postgres_gitlab177_referrer_leaderboard_dense_rank` FAILS ``` cd indexer && YIELDOMEGA_PG_TEST_URL="postgres://yieldomega:password@127.0.0.1:5433/yieldomega_test_204" cargo test --release postgres_gitlab177_referrer_leaderboard_dense_rank thread 'postgres_gitlab177_referrer_leaderboard_dense_rank' panicked at tests/integration_stage2.rs:1454:5: assertion `left == right` failed left: Some(3) right: Some(2) ``` Reproduced deterministically on a fresh ephemeral DB (`yieldomega_test_204`). Not a data-pollution flake — the test seeds its own clean 4-referrer fixture (300/200/200/100 wad) and asserts dense-competitive ranks `1, 2, 2, 4`. The endpoint returns `1, 2, 3, 4` instead, breaking on the tie between the two 200-wad referrers (`bbbb` and `cccc`). Root cause looks to be the rewritten window function in `indexer/src/api.rs:1921`: ```sql RANK() OVER ( ORDER BY COALESCE(a.total_charm, 0) DESC NULLS LAST, r.referrer ASC ) ``` With both `total_charm DESC` and `r.referrer ASC` inside the same `ORDER BY`, RANK() only ties rows when both columns match. Since every `referrer` is unique, no two rows ever tie and RANK() degrades to page-ordinal (`1, 2, 3, 4`). The pre-#204 fix (commit `6d92cc4` for GitLab #177) had the dense-competitive behavior; #204's SQL rewrite regressed it. The invariants doc still asserts the dense-rank requirement: `invariants-and-business-logic.md:929` ties `INV-INDEXER-204-REFERRAL-LB-REGISTRY` to `INV-INDEXER-170-REFERRAL-LB-TIE` and `postgres_gitlab177_referrer_leaderboard_dense_rank`. So docs and test agree — the SQL is the broken piece. Likely fix shape: keep the `referrer ASC` tiebreaker on the outer ORDER BY only, and rank on `total_charm DESC` alone inside the window — e.g.: ```sql RANK() OVER (ORDER BY COALESCE(a.total_charm, 0) DESC NULLS LAST) AS rank ... ORDER BY rank ASC, referrer ASC ``` ## Items 2 + 3 — deferred - Item 2 (Guide leaderboard frontend walk): held until the rank SQL is fixed — the page would render wrong ordinal ranks against the current API, which doesn't tell us anything new beyond what item 4 already shows. - Item 3 (CHARM moves while registration counts stay consistent after a buy lands): blocked pre-launch — `ReferralApplied` won't emit until the first qualifying buy. Onchain check (prior #204 note): 0 `ReferralApplied` events on prod TimeCurve. ## Item 5 — Docs anchors - `INV-INDEXER-204-REFERRAL-LB-REGISTRY` present at `docs/testing/invariants-and-business-logic.md:912` and `:929` with the union spec + `codes_registered_count` rule - `manual-qa-issue-204-referrer-leaderboard-registry-union` anchor in `docs/testing/manual-qa-checklists.md:157`, TOC entry at `:13` - `invariants §204` cross-ref from manual-qa file at `:165` - `skills/README.md:48` indexes the #204 union pointer ## Summary Items 1 and 5 sign off cleanly. Item 4 surfaces a real regression in the rank SQL that needs a fix before frontend (item 2) is meaningful, and item 3 stays blocked until sale opens. Holding off on close until the SQL is patched. @PlasticDigits
Brouie commented 2026-05-15 03:05:59 +00:00 (Migrated from gitlab.com)

mentioned in commit f0d89b2075

mentioned in commit f0d89b2075b506884dbcfb8401ed0cd545e700e1
Brouie commented 2026-05-15 03:08:14 +00:00 (Migrated from gitlab.com)

mentioned in merge request !390

mentioned in merge request !390
PlasticDigits commented 2026-05-15 06:12:44 +00:00 (Migrated from gitlab.com)

mentioned in commit b0b5962e9a

mentioned in commit b0b5962e9adc99ced0cd9f6325a1b2f3896f4584
Brouie commented 2026-05-18 02:38:10 +00:00 (Migrated from gitlab.com)

mentioned in issue #212

mentioned in issue #212
Brouie commented 2026-05-18 02:38:27 +00:00 (Migrated from gitlab.com)

mentioned in issue #213

mentioned in issue #213
PlasticDigits commented 2026-05-20 04:19:17 +00:00 (Migrated from gitlab.com)

mentioned in commit 336bd5848c

mentioned in commit 336bd5848c72a3000476475c4664e03225146c37
PlasticDigits commented 2026-05-20 04:19:24 +00:00 (Migrated from gitlab.com)

Verification complete — dense-rank regression fixed, docs tightened

Walked the full #204 scope against main @ 336bd58.

What shipped (recap)

  1. Option A (registry union): GET /v1/referrals/referrer-leaderboard unions idx_referral_code_registered.owner_address with idx_timecurve_referral_applied.referrer; each row exposes codes_registered_count (schema ≥ 1.19.0).
  2. Dense-rank regression fix (@brouie's item 4): f0d89b2 restores competitive RANK() — window ORDER BY is Σ CHARM DESC only; referrer ASC stays on the outer ORDER BY only. Without that split, unique referrers prevent ties and ranks degrade to page-ordinal 1, 2, 3, 4 instead of 1, 2, 2, 4.
  3. Docs (this pass): Clarified INV-INDEXER-170-REFERRAL-LB-TIE + INV-INDEXER-204-REFERRAL-LB-REGISTRY, product referrals dashboard table, and manual QA #204 dense-rank checklist row — cross-linked from skills/README.md.

Automated verification (local, Postgres ephemeral)

YIELDOMEGA_PG_TEST_URL=postgres://yieldomega:password@127.0.0.1:5433/yieldomega_test \
  cargo test --release postgres_gitlab177_referrer_leaderboard_dense_rank   → PASS
YIELDOMEGA_PG_TEST_URL=... \
  cargo test --release postgres_gitlab204_referrer_leaderboard_includes_registry_registrations → PASS
cd frontend && npm test -- indexerApi → PASS (25 tests)

Checklist for @brouie

  • Item 1 — registry-only rows: GET /v1/referrals/referrer-leaderboard returns wallets with codes_registered_count ≥ 1, referred_buy_count == 0, total_referrer_charm_wad == 0 when only ReferralCodeRegistered is indexed.
  • Item 4 — dense rank: Two referrers tied on Σ CHARM share the same rank; next rank skips (1, 2, 2, 4). postgres_gitlab177_referrer_leaderboard_dense_rank passes.
  • Item 2 — /referrals UI: Guide leaderboard shows onchain codes registered + recorded buys sublines; summary tiles include registration totals.
  • Item 3 — post-buy consistency: After first qualifying ReferralApplied buy, CHARM / buy counts move; codes_registered_count stays stable.
  • Item 5 — docs: invariants §204 · manual QA #204 · skills/README.md contributor index.

Please verify against prod/staging when the deployed indexer is on 1.19.0+. Issue left open per maintainer request.

/cc @brouie

## Verification complete — dense-rank regression fixed, docs tightened Walked the full #204 scope against **`main`** @ **`336bd58`**. ### What shipped (recap) 1. **Option A (registry union):** `GET /v1/referrals/referrer-leaderboard` unions `idx_referral_code_registered.owner_address` with `idx_timecurve_referral_applied.referrer`; each row exposes **`codes_registered_count`** (schema **≥ 1.19.0**). 2. **Dense-rank regression fix** (@brouie's item 4): **`f0d89b2`** restores competitive **`RANK()`** — window `ORDER BY` is **Σ CHARM DESC only**; **`referrer ASC`** stays on the **outer** `ORDER BY` only. Without that split, unique referrers prevent ties and ranks degrade to page-ordinal **`1, 2, 3, 4`** instead of **`1, 2, 2, 4`**. 3. **Docs (this pass):** Clarified **`INV-INDEXER-170-REFERRAL-LB-TIE`** + **`INV-INDEXER-204-REFERRAL-LB-REGISTRY`**, [product referrals dashboard table](docs/product/referrals.md#referrals-dashboard-issue-94), and [manual QA #204](docs/testing/manual-qa-checklists.md#manual-qa-issue-204-referrer-leaderboard-registry-union) dense-rank checklist row — cross-linked from [`skills/README.md`](skills/README.md). ### Automated verification (local, Postgres ephemeral) ``` YIELDOMEGA_PG_TEST_URL=postgres://yieldomega:password@127.0.0.1:5433/yieldomega_test \ cargo test --release postgres_gitlab177_referrer_leaderboard_dense_rank → PASS YIELDOMEGA_PG_TEST_URL=... \ cargo test --release postgres_gitlab204_referrer_leaderboard_includes_registry_registrations → PASS cd frontend && npm test -- indexerApi → PASS (25 tests) ``` ### Checklist for @brouie - [ ] **Item 1 — registry-only rows:** `GET /v1/referrals/referrer-leaderboard` returns wallets with **`codes_registered_count ≥ 1`**, **`referred_buy_count == 0`**, **`total_referrer_charm_wad == 0`** when only `ReferralCodeRegistered` is indexed. - [ ] **Item 4 — dense rank:** Two referrers tied on Σ CHARM share the same **`rank`**; next rank skips (**`1, 2, 2, 4`**). `postgres_gitlab177_referrer_leaderboard_dense_rank` passes. - [ ] **Item 2 — `/referrals` UI:** Guide leaderboard shows **onchain codes registered** + **recorded buys** sublines; summary tiles include registration totals. - [ ] **Item 3 — post-buy consistency:** After first qualifying `ReferralApplied` buy, CHARM / buy counts move; **`codes_registered_count`** stays stable. - [ ] **Item 5 — docs:** [invariants §204](docs/testing/invariants-and-business-logic.md#referrer-leaderboard-registry-union-gitlab-204) · [manual QA #204](docs/testing/manual-qa-checklists.md#manual-qa-issue-204-referrer-leaderboard-registry-union) · [`skills/README.md`](skills/README.md) contributor index. Please verify against prod/staging when the deployed indexer is on **1.19.0+**. Issue left **open** per maintainer request. /cc @brouie
PlasticDigits commented 2026-05-20 04:42:29 +00:00 (Migrated from gitlab.com)

mentioned in issue #225

mentioned in issue #225
Brouie commented 2026-05-21 03:50:51 +00:00 (Migrated from gitlab.com)

Walked the full verification checklist against production. All 5 items PASS.

Item 1 — registry-only rows: PASS

curl https://indexer.yieldomega.com/v1/referrals/referrer-leaderboard?limit=50 (x-schema-version 1.25.0) returns 32 wallets all at rank 13 with total_referrer_charm_wad: "0" and codes_registered_count: 1. These are pure registry-only entries — registered codes on ReferralRegistry with zero attributed ReferralApplied. The union is doing exactly what option A specified.

Item 4 — dense rank: PASS

Production data shows clean dense-competitive behavior:

  • Ranks 1–12: unique CHARM values, each at its own rank
  • Rank 13: 32 wallets all tied on zero CHARM, all share rank 13
  • Next rank would be 45 (13 + 32, skipped per spec)

This is the 1, 2, 2, 4 pattern but at scale — the f0d89b2 window-function fix works on real production data. No degradation to page-ordinal observed.

Item 3 — post-buy consistency: PASS

Compared to pre-launch baseline (6 days ago: 6 ReferralCodeRegistered, 0 ReferralApplied, leaderboard fully zero CHARM):

  • Post-sale: 12 ranks now with non-zero CHARM, leader at 70.747 CHARM
  • 44 total guides in the leaderboard (per global tile)
  • 526 recorded referral buys (per global tile)
  • 139 total guide CHARM (per global tile)
  • The 32 registry-only wallets remain stable on the board — codes_registered_count didn't drop them when CHARM activity flowed elsewhere

Item 2 — frontend /referrals UI: PASS

Walked the Guide leaderboard on production /referrals:

  • Header copy matches spec: "Ranked by CHARM earned from qualifying referred buys (indexed ReferralApplied). Guides who registered a code on ReferralRegistry appear as soon as that event is indexed—even before the first qualifying buy."
  • Summary tiles render the three global totals: 44 codes registered, 526 recorded buys, 139 CHARM
  • Each row shows "1 onchain code registered" + "N recorded buys with this referrer code" sublines (Option A copy)
  • API row values render correctly on UI (cross-checked rank 1 and rank 7: full wad values match the displayed totals)
  • Rank 13 ties: all 32 zero-CHARM wallets display rank 13 in the UI, not 13/14/15/... — dense-competitive rank flows through cleanly from API to render
  • Pagination: numbered controls PREV / 1 / 2 / 3 / NEXT at bottom of card; 20 rows per page; 44 entries → 3 pages
  • "You" row highlighting present on connected wallet (rank 7 for me, distinct border vs other rows)

Item 5 — docs: PASS

Anchors resolve cleanly:

  • invariants §204 — INV-INDEXER-204-REFERRAL-LB-REGISTRY + INV-INDEXER-170-REFERRAL-LB-TIE cross-link present
  • manual QA #204 — dense-rank checklist row present
  • skills/README.md contributor index — #204 union pointer indexed

#204 verified clean from QA side. Issue can close when convenient. @PlasticDigits

Walked the full verification checklist against production. All 5 items PASS. **Item 1 — registry-only rows: PASS** `curl https://indexer.yieldomega.com/v1/referrals/referrer-leaderboard?limit=50` (x-schema-version 1.25.0) returns 32 wallets all at rank 13 with `total_referrer_charm_wad: "0"` and `codes_registered_count: 1`. These are pure registry-only entries — registered codes on ReferralRegistry with zero attributed ReferralApplied. The union is doing exactly what option A specified. **Item 4 — dense rank: PASS** Production data shows clean dense-competitive behavior: * Ranks 1–12: unique CHARM values, each at its own rank * Rank 13: 32 wallets all tied on zero CHARM, all share rank 13 * Next rank would be 45 (13 + 32, skipped per spec) This is the `1, 2, 2, 4` pattern but at scale — the `f0d89b2` window-function fix works on real production data. No degradation to page-ordinal observed. **Item 3 — post-buy consistency: PASS** Compared to pre-launch baseline (6 days ago: 6 ReferralCodeRegistered, 0 ReferralApplied, leaderboard fully zero CHARM): * Post-sale: 12 ranks now with non-zero CHARM, leader at 70.747 CHARM * 44 total guides in the leaderboard (per global tile) * 526 recorded referral buys (per global tile) * 139 total guide CHARM (per global tile) * The 32 registry-only wallets remain stable on the board — `codes_registered_count` didn't drop them when CHARM activity flowed elsewhere **Item 2 — frontend /referrals UI: PASS** Walked the Guide leaderboard on production /referrals: * Header copy matches spec: "Ranked by CHARM earned from qualifying referred buys (indexed ReferralApplied). Guides who registered a code on ReferralRegistry appear as soon as that event is indexed—even before the first qualifying buy." * Summary tiles render the three global totals: 44 codes registered, 526 recorded buys, 139 CHARM * Each row shows "1 onchain code registered" + "N recorded buys with this referrer code" sublines (Option A copy) * API row values render correctly on UI (cross-checked rank 1 and rank 7: full wad values match the displayed totals) * Rank 13 ties: all 32 zero-CHARM wallets display rank 13 in the UI, not 13/14/15/... — dense-competitive rank flows through cleanly from API to render * Pagination: numbered controls PREV / 1 / 2 / 3 / NEXT at bottom of card; 20 rows per page; 44 entries → 3 pages * "You" row highlighting present on connected wallet (rank 7 for me, distinct border vs other rows) **Item 5 — docs: PASS** Anchors resolve cleanly: * invariants §204 — INV-INDEXER-204-REFERRAL-LB-REGISTRY + INV-INDEXER-170-REFERRAL-LB-TIE cross-link present * manual QA #204 — dense-rank checklist row present * skills/README.md contributor index — #204 union pointer indexed #204 verified clean from QA side. Issue can close when convenient. @PlasticDigits
PlasticDigits (Migrated from gitlab.com) closed this issue 2026-05-21 12:03:22 +00:00
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
cac-design-issue-544
renovate/configure
No results found.
Labels
Clear labels   
agent:fix_bugfix

   
agent:fix_conflicts

   
agent:fix_security

   
agent:gap_analysis

   
agent:implement

   
agent:open_issues

   
agents

   
agent:security_audit

   
agent:verify
  
area
timecurve

  
arena

   
arena-v2

   
audit

   
block:feature_review

   
block:security

   
bug

   
contracts

   
design

   
devops

   
docs
Documentation

  
documentation

   
enhancement

   
epic
Epic / umbrella issue

  
feature

   
frontend

   
indexer

   
low priority
Low priority, investigate other issues first

  
megaeth

   
operations

   
ops
Deploy and operations

  
performance

   
QA

   
qa checklist

   
referrals

   
removal
Legacy removal

  
security

   
smart-contracts

   
tech-debt

   
testing

   
time-arena
TimeArena v2 revamp

  
timecurve

   
visual-qa
No labels
agent:fix_bugfix
agent:fix_conflicts
agent:fix_security
agent:gap_analysis
agent:implement
agent:open_issues
agents
agent:security_audit
agent:verify
area
timecurve
arena
arena-v2
audit
block:feature_review
block:security
bug
contracts
design
devops
docs
documentation
enhancement
epic
feature
frontend
indexer
low priority
megaeth
operations
ops
performance
QA
qa checklist
referrals
removal
security
smart-contracts
tech-debt
testing
time-arena
timecurve
visual-qa
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
code/yieldomega#204
No description provided.