Indexer: materialize 24h volume, fix block timestamps, cap pagination (M4, M8) #243

New issue

Closed

opened 2026-05-31 04:41:22 +00:00 by PlasticDigits · 13 comments

PlasticDigits commented 2026-05-31 04:41:22 +00:00 (Migrated from gitlab.com)

Copy link

Reference

Gap analysis: gaps/GAP_1780200149.md — findings M4, M8 (both parts).

Current codebase

M4 — Per-request 24h volume aggregation: list_pairs_filtered in indexer/src/db/queries/pairs.rs:145-150 LEFT JOINs a subquery scanning swap_events for the last 24 hours on every pair-list request when sorting/filtering by volume. No materialized rollup or cache.

M8 (p1) — Block timestamp fallback: parse_block_time in indexer/src/indexer/poller.rs:155-173 uses Utc::now() when LCD timestamp is missing or invalid → candles aligned to ingestion time, not chain time.

M8 (p2) — Uncapped pagination:

• Pair list offset in api/pairs.rs:162 — .max(0) only; no upper cap (deep offset = full table scan).
• tokens / CG pairs endpoints — unbounded or weakly bounded list sizes (see api/tokens.rs, api/cg.rs).

Related: trader tier sync O(traders) every 10 min (indexer/src/indexer/trader_tracker.rs) — note for future but out of scope unless trivial cache hook.

Why this is needed

Pair list with sort=volume_24h is hot path for frontend Pool/Charts/Swap token discovery. O(pairs × swaps) per request does not scale. Uncapped offsets enable expensive scan DoS. Timestamp skew corrupts candle charts and CG/CMC historical data. Unbounded token lists increase memory and response times.

Constraints / guardrails

• Materialized volume: refresh strategy must be documented (cron, post-index hook, or PG materialized view).
• Timestamp fallback: prefer skip block / retry / fail over silent Utc::now(); if fallback retained, metric + exclude from candle close.
• Offset cap: align with PAIR_LIST_LIMIT_MAX (100) — e.g. max offset 10_000 or max page index.
• Token/CG endpoints: add limit/offset caps consistent with existing API patterns.
• Backward compatible JSON shapes.
• Index migrations allowed; document in indexer/migrations/.

Relevant files

Path	Role
indexer/src/db/queries/pairs.rs	Volume subquery
indexer/src/api/pairs.rs	Pair list offset/limit
indexer/src/indexer/poller.rs	parse_block_time
indexer/src/indexer/candle_builder.rs	Candle bucketing
indexer/src/api/tokens.rs	Token list
indexer/src/api/cg.rs, api/cmc.rs	Integrator pair lists
indexer/migrations/	New indexes / mat views

Recommended direction

1. M4: Add pair_volume_24h materialized table or PG materialized view refreshed every 1–5 min; or incremental rollup on swap insert. Index (pair_id, block_timestamp) on swap_events if missing.
2. M8 p1: On missing timestamp, retry LCD block query for header time; if still missing, skip candle update for block and log metric (do not use wall clock).
3. M8 p2: Cap offset ≤ e.g. 10_000; return 400 if exceeded. Add pagination defaults/max to tokens/CG pairs.

Acceptance criteria

• Pair list by volume_24h does not full-scan 24h swaps per request (explain refresh lag in docs).
• Missing block timestamp does not write skewed candles.
• Pair list rejects excessive offset.
• Token/CG pair endpoints bounded.
• Migration + indexes shipped.

Test plan — all paths

Path	Test
Pair list sort volume_24h	Correct order; bounded query time
Volume rollup after new swap	Updates within refresh SLA
Valid block timestamp	Candles use chain time
Missing timestamp	No candle write OR explicit fallback flag
offset=0, limit=50	200
offset=99999	400
Tokens list default	≤ max limit

Run: indexer integration tests + EXPLAIN on pair list query.

Test plan — attack / abuse vectors

Vector	Expected
Repeated volume_24h sort requests	Cached/rollup; stable latency
Deep offset scraping	400 after cap
Request all tokens unbounded	Paginated

Verification criteria

• EXPLAIN ANALYZE shows no seq scan on full 24h swap_events for pair list.
• Integration test for offset cap.
• Poller test: missing timestamp does not call candle builder with Utc::now().

## Reference Gap analysis: [`gaps/GAP_1780200149.md`](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/blob/main/gaps/GAP_1780200149.md) — findings **M4**, **M8** (both parts). ## Current codebase **M4 — Per-request 24h volume aggregation:** `list_pairs_filtered` in `indexer/src/db/queries/pairs.rs:145-150` LEFT JOINs a subquery scanning `swap_events` for the last 24 hours **on every pair-list request** when sorting/filtering by volume. No materialized rollup or cache. **M8 (p1) — Block timestamp fallback:** `parse_block_time` in `indexer/src/indexer/poller.rs:155-173` uses `Utc::now()` when LCD timestamp is missing or invalid → candles aligned to ingestion time, not chain time. **M8 (p2) — Uncapped pagination:** - Pair list `offset` in `api/pairs.rs:162` — `.max(0)` only; no upper cap (deep offset = full table scan). - `tokens` / CG pairs endpoints — unbounded or weakly bounded list sizes (see `api/tokens.rs`, `api/cg.rs`). Related: trader tier sync O(traders) every 10 min (`indexer/src/indexer/trader_tracker.rs`) — note for future but **out of scope** unless trivial cache hook. ## Why this is needed Pair list with `sort=volume_24h` is hot path for frontend Pool/Charts/Swap token discovery. O(pairs × swaps) per request does not scale. Uncapped offsets enable expensive scan DoS. Timestamp skew corrupts candle charts and CG/CMC historical data. Unbounded token lists increase memory and response times. ## Constraints / guardrails - Materialized volume: refresh strategy must be documented (cron, post-index hook, or PG materialized view). - Timestamp fallback: prefer skip block / retry / fail over silent `Utc::now()`; if fallback retained, metric + exclude from candle close. - Offset cap: align with `PAIR_LIST_LIMIT_MAX` (100) — e.g. max offset 10_000 or max page index. - Token/CG endpoints: add `limit`/`offset` caps consistent with existing API patterns. - Backward compatible JSON shapes. - Index migrations allowed; document in `indexer/migrations/`. ## Relevant files | Path | Role | |------|------| | `indexer/src/db/queries/pairs.rs` | Volume subquery | | `indexer/src/api/pairs.rs` | Pair list offset/limit | | `indexer/src/indexer/poller.rs` | `parse_block_time` | | `indexer/src/indexer/candle_builder.rs` | Candle bucketing | | `indexer/src/api/tokens.rs` | Token list | | `indexer/src/api/cg.rs`, `api/cmc.rs` | Integrator pair lists | | `indexer/migrations/` | New indexes / mat views | ## Recommended direction 1. **M4:** Add `pair_volume_24h` materialized table or PG materialized view refreshed every 1–5 min; or incremental rollup on swap insert. Index `(pair_id, block_timestamp)` on `swap_events` if missing. 2. **M8 p1:** On missing timestamp, retry LCD block query for header time; if still missing, skip candle update for block and log metric (do not use wall clock). 3. **M8 p2:** Cap `offset` ≤ e.g. 10_000; return 400 if exceeded. Add pagination defaults/max to tokens/CG pairs. ## Acceptance criteria - [ ] Pair list by `volume_24h` does not full-scan 24h swaps per request (explain refresh lag in docs). - [ ] Missing block timestamp does not write skewed candles. - [ ] Pair list rejects excessive offset. - [ ] Token/CG pair endpoints bounded. - [ ] Migration + indexes shipped. ## Test plan — all paths | Path | Test | |------|------| | Pair list sort volume_24h | Correct order; bounded query time | | Volume rollup after new swap | Updates within refresh SLA | | Valid block timestamp | Candles use chain time | | Missing timestamp | No candle write OR explicit fallback flag | | offset=0, limit=50 | 200 | | offset=99999 | 400 | | Tokens list default | ≤ max limit | Run: indexer integration tests + EXPLAIN on pair list query. ## Test plan — attack / abuse vectors | Vector | Expected | |--------|----------| | Repeated volume_24h sort requests | Cached/rollup; stable latency | | Deep offset scraping | 400 after cap | | Request all tokens unbounded | Paginated | ## Verification criteria - [ ] EXPLAIN ANALYZE shows no seq scan on full 24h swap_events for pair list. - [ ] Integration test for offset cap. - [ ] Poller test: missing timestamp does not call candle builder with `Utc::now()`.

PlasticDigits commented 2026-05-31 06:06:26 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit d4e4698d81

mentioned in commit d4e4698d81aef39e262baa49abdf5b7e330f0da5

PlasticDigits commented 2026-05-31 06:06:26 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit 9aca628794

mentioned in commit 9aca628794253022410e54380d3e16bc57575dc9

PlasticDigits commented 2026-05-31 06:06:37 +00:00 (Migrated from gitlab.com)

Copy link

Implementation complete (pushed to main — d4e4698)

Addressed GitLab #243 / gap analysis M4 and M8 (both parts).

What changed

M4 — Materialized 24h pair volume

• Added pair_volume_24h table + migration 20260531143000_pair_volume_24h_rollup.sql
• Background refresh every ~5 min via volume_aggregator (+ initial refresh on indexer startup)
• GET /api/v1/pairs?sort=volume_24h now JOINs the rollup table instead of scanning 24h swap_events per request

M8 (p1) — Block timestamp fallback

• resolve_block_time: valid tx RFC3339 → use it; missing/invalid tx → LCD block header time; no Utc::now() fallback
• If both tx and header time are unusable, block ingest fails and cursor does not advance

M8 (p2) — Pagination caps

• Pair list: offset > 10_000 → 400
• GET /api/v1/tokens: limit (default 200, max 500) + offset cap 10_000
• GET /cg/pairs: limit (default 500, max 1000) + offset cap 10_000

Documentation

• Updated docs/indexer-invariants.md — invariants V1–V4, block time + rollup sections
• New agent playbook: skills/AGENTS_INDEXER_VOLUME_PAGINATION.md

Tests run (docker network — host 127.0.0.1:5432 unreachable in this env)

• indexer_pair_volume_pagination — rollup matches live sum; volume sort OK
• indexer_ingestion_hardening — header fallback; invalid tx+header fails without cursor advance
• api_pairs — offset=99999 → 400
• api_tokens — offset cap
• api_cg — offset cap + existing CG tests

---

Verification checklist (QA)

• Migration applied: \d pair_volume_24h shows table; indexer starts cleanly
• GET /api/v1/pairs?sort=volume_24h&order=desc returns sensible order (note up to ~5 min lag vs live swaps)
• EXPLAIN ANALYZE on pair list query: no seq scan aggregating full 24h swap_events
• After a new swap, volume sort updates within one refresh cycle (~5 min)
• GET /api/v1/pairs?offset=99999 → 400
• GET /api/v1/tokens?offset=99999 → 400
• GET /cg/pairs?offset=99999 → 400
• Indexer logs: missing tx timestamp falls back to header (warn), no wall-clock candle skew
• Charts/candles align with chain time on LocalTerra/QA pairs

---

Requesting verification from @qa agent team — please run the checklist above on QA/localnet after deploy and confirm volume sort lag + pagination behavior.

Issue left open pending QA sign-off.

## Implementation complete (pushed to `main` — `d4e4698`) Addressed GitLab **#243** / gap analysis **M4** and **M8** (both parts). ### What changed **M4 — Materialized 24h pair volume** - Added `pair_volume_24h` table + migration `20260531143000_pair_volume_24h_rollup.sql` - Background refresh every ~5 min via `volume_aggregator` (+ initial refresh on indexer startup) - `GET /api/v1/pairs?sort=volume_24h` now JOINs the rollup table instead of scanning 24h `swap_events` per request **M8 (p1) — Block timestamp fallback** - `resolve_block_time`: valid tx RFC3339 → use it; missing/invalid tx → LCD block header `time`; **no** `Utc::now()` fallback - If both tx and header time are unusable, block ingest fails and cursor does not advance **M8 (p2) — Pagination caps** - Pair list: `offset` > 10_000 → **400** - `GET /api/v1/tokens`: `limit` (default 200, max 500) + `offset` cap 10_000 - `GET /cg/pairs`: `limit` (default 500, max 1000) + `offset` cap 10_000 ### Documentation - Updated [`docs/indexer-invariants.md`](docs/indexer-invariants.md) — invariants **V1–V4**, block time + rollup sections - New agent playbook: [`skills/AGENTS_INDEXER_VOLUME_PAGINATION.md`](skills/AGENTS_INDEXER_VOLUME_PAGINATION.md) ### Tests run (docker network — host `127.0.0.1:5432` unreachable in this env) - `indexer_pair_volume_pagination` — rollup matches live sum; volume sort OK - `indexer_ingestion_hardening` — header fallback; invalid tx+header fails without cursor advance - `api_pairs` — offset=99999 → 400 - `api_tokens` — offset cap - `api_cg` — offset cap + existing CG tests --- ### Verification checklist (QA) - [ ] Migration applied: `\d pair_volume_24h` shows table; indexer starts cleanly - [ ] `GET /api/v1/pairs?sort=volume_24h&order=desc` returns sensible order (note up to ~5 min lag vs live swaps) - [ ] `EXPLAIN ANALYZE` on pair list query: no seq scan aggregating full 24h `swap_events` - [ ] After a new swap, volume sort updates within one refresh cycle (~5 min) - [ ] `GET /api/v1/pairs?offset=99999` → **400** - [ ] `GET /api/v1/tokens?offset=99999` → **400** - [ ] `GET /cg/pairs?offset=99999` → **400** - [ ] Indexer logs: missing tx timestamp falls back to header (warn), no wall-clock candle skew - [ ] Charts/candles align with chain time on LocalTerra/QA pairs --- **Requesting verification from @qa agent team** — please run the checklist above on QA/localnet after deploy and confirm volume sort lag + pagination behavior. Issue left **open** pending QA sign-off.

PlasticDigits commented 2026-05-31 07:39:18 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit fe4cacc922

mentioned in commit fe4cacc922f03f90b0f58b1be57895add1794cc8

PlasticDigits commented 2026-05-31 07:39:18 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in commit 4a68a1fe24

mentioned in commit 4a68a1fe246a41347aaa8a6b8b646998e1ccbaee

PlasticDigits commented 2026-05-31 07:39:37 +00:00 (Migrated from gitlab.com)

Copy link

QA verification complete (@verify/issue-243 → main 4a68a1f)

Verified GitLab #243 / gap M4 + M8 on LocalTerra stack (indexer :3001, Postgres dex_indexer, dex_indexer_test). Implementation from d4e4698 meets all acceptance and verification criteria in the issue body.

What was verified

Criterion	Result
Pair list sort=volume_24h uses pair_volume_24h rollup (not live 24h swap_events scan)	PASS — EXPLAIN on prod DB: joins pair_volume_24h only; no swap_events in plan
Rollup table populated / refreshing	PASS — 50 rows; updated_at recent (~5 min loop)
Missing block timestamp → no wall-clock candles	PASS — invalid_tx_and_header_timestamp_fails_block; cursor unchanged; resolve_block_time has no Utc::now()
Tx timestamp missing → LCD header fallback	PASS — missing_tx_timestamp_uses_block_header_time
Pair list offset=99999 → 400	PASS (live API + api_pairs)
Tokens / CG pairs offset cap	PASS (live API + api_tokens, api_cg)
Migration shipped	PASS — 20260531143000_pair_volume_24h_rollup.sql applied
Integration tests	PASS — indexer_pair_volume_pagination (3), indexer_ingestion_hardening (6), api_pairs/api_tokens/api_cg

Follow-up on main

• Gap register gaps/GAP_1780200149.md: M4 / M8 marked Fixed (#243).
• New regression test pair_list_volume_sort_plan_does_not_touch_swap_events (EXPLAIN guard).

Manual re-check checklist (optional)

• GET /api/v1/pairs?sort=volume_24h&order=desc — sensible order (expect up to ~5 min lag vs live swaps)
• After a new swap, volume sort updates within one refresh cycle (~5 min)
• Charts on QA pairs align with chain time (no ingestion skew)
• GET /api/v1/tokens default page size ≤ 500

Closing — all issue acceptance + verification criteria satisfied. @brouie — ping if you see volume-sort lag or pagination regressions in prod.

## QA verification complete (@verify/issue-243 → `main` `4a68a1f`) Verified GitLab **#243** / gap **M4** + **M8** on LocalTerra stack (indexer `:3001`, Postgres `dex_indexer`, `dex_indexer_test`). Implementation from `d4e4698` meets all acceptance and verification criteria in the issue body. ### What was verified | Criterion | Result | |-----------|--------| | Pair list `sort=volume_24h` uses `pair_volume_24h` rollup (not live 24h `swap_events` scan) | **PASS** — `EXPLAIN` on prod DB: joins `pair_volume_24h` only; no `swap_events` in plan | | Rollup table populated / refreshing | **PASS** — 50 rows; `updated_at` recent (~5 min loop) | | Missing block timestamp → no wall-clock candles | **PASS** — `invalid_tx_and_header_timestamp_fails_block`; cursor unchanged; `resolve_block_time` has no `Utc::now()` | | Tx timestamp missing → LCD header fallback | **PASS** — `missing_tx_timestamp_uses_block_header_time` | | Pair list `offset=99999` → **400** | **PASS** (live API + `api_pairs`) | | Tokens / CG pairs offset cap | **PASS** (live API + `api_tokens`, `api_cg`) | | Migration shipped | **PASS** — `20260531143000_pair_volume_24h_rollup.sql` applied | | Integration tests | **PASS** — `indexer_pair_volume_pagination` (3), `indexer_ingestion_hardening` (6), `api_pairs`/`api_tokens`/`api_cg` | ### Follow-up on `main` - Gap register [`gaps/GAP_1780200149.md`](gaps/GAP_1780200149.md): **M4** / **M8** marked **Fixed (#243)**. - New regression test `pair_list_volume_sort_plan_does_not_touch_swap_events` (EXPLAIN guard). ### Manual re-check checklist (optional) - [ ] `GET /api/v1/pairs?sort=volume_24h&order=desc` — sensible order (expect up to ~5 min lag vs live swaps) - [ ] After a new swap, volume sort updates within one refresh cycle (~5 min) - [ ] Charts on QA pairs align with chain time (no ingestion skew) - [ ] `GET /api/v1/tokens` default page size ≤ 500 **Closing** — all issue acceptance + verification criteria satisfied. @brouie — ping if you see volume-sort lag or pagination regressions in prod.

PlasticDigits (Migrated from gitlab.com) closed this issue 2026-05-31 07:39:40 +00:00

Brouie commented 2026-06-06 02:05:52 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #335

mentioned in issue #335

PlasticDigits commented 2026-06-07 12:14:15 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #337

mentioned in issue #337

PlasticDigits commented 2026-06-12 04:46:03 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #361

mentioned in issue #361

PlasticDigits commented 2026-08-17 03:52:29 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #544

mentioned in issue #544

PlasticDigits commented 2026-08-19 11:52:58 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #576

mentioned in issue #576

PlasticDigits commented 2026-08-26 03:06:34 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #655

mentioned in issue #655

PlasticDigits commented 2026-08-28 05:22:09 +00:00 (Migrated from gitlab.com)

Copy link

mentioned in issue #692

mentioned in issue #692

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

renovate/tokio-util-0.x-lockfile

renovate/thiserror-2.x-lockfile

renovate/serde_json-1.x-lockfile

renovate/serde-monorepo

renovate/libc-0.x-lockfile

renovate/chrono-0.x-lockfile

cac-design-issue-1206

cac-design-issue-1317

cac-design-issue-1316

cac-design-issue-1311

cac-design-issue-1300

cac-design-issue-1306

cac-design-issue-1305

cac-design-issue-1302

cac-design-issue-1277

No results found.

Labels

Clear labels   

agent:fix_bugfix

  

agent:fix_conflicts

  

agent:fix_security

  

agent:gap_analysis

  

agent:implement

  

agent:implement

Queue implement gate

  

agent:implement

Queue implement gate

  

agent:open_issues

  

agent:ready

  

agent:research

  

agent:security_audit

  

agent:verify

  

architecture

Architecture

  

backend

API and services

  

blocker:hybrid

Blocks hybrid launch

  

blocker:launch

Blocks launch

  

blocker:limit-orders

Blocks limits launch

  

blocker:v2

Blocks v2

  

block:log_only

  

block:security

  

bug

  

ci

  

contracts

Smart contracts

  

correctness

  

deploy

  

dev

  

devops

  

docs

  

documentation

  

duplicate

  

e2e

  

enhancement

  

epic

Epic

  

feature

  

frontend

  

functional-completion

Functional

  

gas

  

good first issue

  

governance

  

help wanted

  

high-risk

  

hooks

  

hybrid

Hybrid swaps

  

indexer

Indexer

  

infra

CI/CD

  

infrastructure

  

integrators

  

invalid

  

launch-blocker

  

limit-orders

Limit orders

  

localnet

  

localterra

  

low priority

  

missing-implementation

Implementation

  

needs-design

Design decision required

  

ops

  

performance

  

priority

high

  

priority

medium

  

product

  

qa

  

QA

  

question

  

ready

Definition of Ready

  

ready

Definition of Ready

  

research

  

scripts

  

security

Security

  

security-hardening

Security hardening

  

smartcontracts

  

tech-debt

  

testing

Testing

  

ux

  

UX

  

v2

Pool-only swaps

  

verification

  

wontfix

No labels

agent:fix_bugfix

agent:fix_conflicts

agent:fix_security

agent:gap_analysis

agent:implement

agent:implement

agent:implement

agent:open_issues

agent:ready

agent:research

agent:security_audit

agent:verify

architecture

backend

blocker:hybrid

blocker:launch

blocker:limit-orders

blocker:v2

block:log_only

block:security

bug

ci

contracts

correctness

deploy

dev

devops

docs

documentation

duplicate

e2e

enhancement

epic

feature

frontend

functional-completion

gas

good first issue

governance

help wanted

high-risk

hooks

hybrid

indexer

infra

infrastructure

integrators

invalid

launch-blocker

limit-orders

localnet

localterra

low priority

missing-implementation

needs-design

ops

performance

priority

high

priority

medium

product

qa

QA

question

ready

ready

research

scripts

security

security-hardening

smartcontracts

tech-debt

testing

ux

UX

v2

verification

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

1 participant

Notifications

Subscribe

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/cl8y-dex-terraclassic#243

No description provided.