fix: Charts Price (USD) history flattened to live hub mark; idle USTC/UST1 never mark-to-market

PlasticDigits commented

2026-08-19 00:57:39 +00:00

(Migrated from gitlab.com)

Summary

/charts and /trade Price (USD) history for UST1/cUSTC and UST1/USTR is a flat line at the live hub/oracle tick. CoinGecko USTC/USD moves, and CL8Y has DEX volume, but inverted candles do not. cLUNC/UST1 still moves because the chart is factory-oriented (cLUNC is asset_0) and the DEX ratio changed. Users expect USTC-quoted USD (and therefore UST1 USD) to follow the CEX USTC oracle even when that pair has no new swaps.

This is one product bug with two coupled causes in the indexer:

As-of-now rewrite: every hub refresh overwrites all historical swap_events.price_usd and candle USD OHLC with human × current hub_prices.
Last-trade-only candles: update_candles_for_swap never writes a bar unless a swap lands, so idle intervals cannot track oracle/hub ticks.

Fixing only (1) leaves sparse step-functions. Fixing only (2) is immediately undone by the next rewrite. Bundle both.

Related: #556 (hub USD + this rewrite), #522 (P522 price_usd), #543 / #524 (per-bar invertUsd), #515 (CEX USTC/LUNC).

Environment (production, 2026-08-19)

Network: columbus-5 (https://dex.cl8y.com, indexer https://indexer.dex.cl8y.com)
Live CEX USTC ≈ $0.004788 (KuCoin/MEXC; CoinGecko also polled). GET /api/v1/oracle/history/ustc?limit=1000 spans only ~8.5h at 30s polls and already moves ~1.4%. Multi-day CoinGecko USTC is larger; pair charts should reflect that path, not a single tick.
Hub snapshot: cUSTC = live USTC; UST1 ≈ $0.999; USTR ≈ $0.01151.

Pair	Address	Default UI invert (#524)	What the user sees on Price (USD)
UST1/cUSTC	`terra1ceprjsxp86ggftf5e38wwt34l83e5gq7penkdnv4wsatkwcs8v6qccw55f`	on (other token = cUSTC)	cUSTC/USD → flat at live USTC
UST1/USTR	`terra16vxrhpvpcucu05y0nr862vf9hnqeh274uaff4s7hz4n0ea74006qf5hgqy`	on (other token = USTR)	USTR/USD → flat at live hub USTR
cLUNC/UST1	`terra1su5363453fj326u4t0kqar30f35cm3n0dc9yksg379u6875z350s4mm7h4`	off (cLUNC is base)	USD of 1 cLUNC follows DEX `human` × live UST1 → moves

Measured on GET /api/v1/pairs/{addr}/candles?interval=1h: close / close_human is identical on every bar and equals the current hub_prices quote (cUSTC 0.004788, USTR 0.01151452, UST1 0.999285). Rel range of invertUsd = 0%. That is invertUsd (#543) applied to as-of-now rewritten factory USD — not a frontend plot bug.

Current codebase

Candle write path is swap-only

indexer/src/indexer/candle_builder.rs update_candles_for_swap is the only live candle writer. Parser indexer/src/indexer/parser.rs stamps price_usd at ingest via pair_price_usd::price_usd_for_human_quote_per_base (P522-2: USD of 1 human asset_0 = human quote-per-base × quote USD). Missing / non-positive price_usd skips the candle (P522-4 / #543). Rebuild rebuild_candles_from_swaps is also swap-bucket only. Oracle ticks, hub refreshes, and pair_reserves snapshots do not create or extend candles.

GET /api/v1/pairs/{addr}/candles (indexer/src/api/pairs.rs) returns stored rows (default 90-day window, limit clamp 1–1000). The dApp PriceChart.tsx plots that series after applyChartDisplayInvert / invertUsd. It does not mix in GET /api/v1/oracle/history/{ticker} (that feed is Protocol-only, useProtocolOracleQueries.ts).

Hub refresh rewrites all history to now

refresh_hub_prices runs on book-snapshot cadence (~10s, BOOK_SNAPSHOT_INTERVAL_MS) in poller.rs. After replacing the current hub_prices snapshot it always calls backfill_usd_from_hub:

-- swap_events.price_usd := se.price * hp.price_usd  (current hub)
-- candles OHLC := *_human * hp.price_usd            (current hub)
JOIN hub_prices hp ON hp.asset_id = p.asset_1_id

hub_prices is a single current row per ticker (custc / ust1 / ustr) — no history table (indexer/migrations/20260818180000_hub_prices.sql). pair_reserves is also one row per pair (upsert), not a time series.

Consequences:

Every cUSTC-, UST1-, or USTR-quoted pair’s entire USD history is scaled to the latest hub tick. Human OHLC (DEX prints) is preserved; USD OHLC is not time-stamped.
Default invert on UST1-as-base pairs (#524) displays factory_usd / human = quote USD. After the rewrite that equals live cUSTC/USTR, so the chart is a horizontal line at the current CoinGecko-ish USTC (or current USTR). That matches the report: “USTC barely/never changed”; “UST1 never changed” (UST1 hub ≈ $1 and/or inverted USTR mark stuck at now).
cLUNC/UST1 is not inverted by default, so the pane shows USD of cLUNC = human × live UST1. human (UST1 per cLUNC) actually moved on-chain, so that pair still “has a chart.” The rewrite still flattens its implied quote USD to live UST1 (invertUsd unique = 1).
LUNC-quoted pairs are not in hub_prices, so they are not rewritten this way. There is still no mark-to-market without swaps.
volume_usd is not rewritten (ingest-time, #548 C8). Tape price_usd and volume USD disagree in time basis.
Full-table UPDATE swap_events + UPDATE candles every ~10s is load, not O(1). H10 / #556 said GET must not scan swap_events; the background loop now does worse than a GET scan.

What is already correct (do not regress)

P522-1 human quote-per-base; P522-2 factory USD of asset_0; P522-Q catalog (USTC/cUSTC/uusd = #515 ustc; LUNC/cLUNC/uluna = #515 lunc; UST1/USTR = hub, not $1 / not 2.5×).
H1–H3: usd(cUSTC) = USTC oracle; usd(UST1) from deepest cUSTC/UST1 reserves (not last print); usd(USTR) from deepest vs already-priced hub. Snapshot GET stays O(1).
C543 / T524: indexer stores factory USD; UI invert is invertUsd per bar, never 1/x of USD, never human on a USD axis.
CG/CMC last_price stays human (H10). Advisory feeds (X5) — not settlement, TWAP, or /ust1 window.
CEX catalog stays ustc | lunc | vfdusd. /oracle/price/ust1 (and ustr / custc) stay 400 (H6).

Why the new implementation is needed

Honest history. Price (USD) candles are a time series. Scaling every past bar by today’s USTC/USTR/UST1 mark destroys CEX and hub movement and makes inverted UST1/cUSTC look like USTC “never moved,” contradicting CoinGecko and oracle_prices.
Idle mark-to-market. UST1 USD is reserves(cUSTC/UST1) × usd(USTC) (H2). That can change when USTC CEX moves with zero UST1/cUSTC swaps. Retail charts must follow that mark; last-trade-only candles cannot.
Same rule for all catalog quotes. cLUNC-looking movement today is an accident of orientation + DEX human chops, not a correct USD clock. LUNC-quoted pairs should also pick up #515 lunc ticks between prints so behavior is consistent.
Stop rewriting the tape. Historical swap_events.price_usd is an as-of-ingest advisory. Replacing it every 10s also corrupts Open/Close stats, limit USD notionals that read tape, and any integrator using price_usd as a print.

Constraints / guardrails

Time-stamp USD; never as-of-now rewrite. Do not UPDATE historical swap_events.price_usd or candle USD from the live hub_prices snapshot. Live hub is for new ingest, current GET /hub-prices, overview fields, and new mark bars only.
Human OHLC stays DEX prints. Mark-to-market may carry forward last human (or use reserve spot as the mark’s human only on zero-trade bars, documented). Do not fabricate trade_count / volume_* for oracle ticks. Histogram volume stays swap volume (C5 spirit).
P522-2 / P522-4 unchanged. Candle open/high/low/close remain factory USD of asset_0 from price_usd math. No human-on-USD-axis fallback. UI invert stays frontend invertUsd per bar (C543-1–C543-9, T524-4).
Quote catalog only (P522-Q / A1). Mark-to-market only pairs whose quote (or documented hub graph) is USTC/cUSTC/uusd, LUNC/cLUNC/uluna, UST1, or USTR identified by contract/denom, not symbol. Gem natives spoofing USTR must not get USD bars. Unknown quote → skip (NULL), never invent.
Oracle down → no new USD marks (H1 / X6). Do not freeze last peg, $1, or 2.5×. Non-finite / fits_numeric_38_18 overflow → skip write. high ≥ low ≥ 0, merge invariants in merge_candle_ohlc.
No vFDUSD in DEX USD (X4 / H7). Do not add ustr/ust1/custc to CEX OracleTicker. Do not use /oracle/price for hub.
Do not scan swap_events on GET. Materialize marks on oracle/hub cadence. Cap work: catalog-priced active factory pairs only (listed, hub/oracle quote). No unbounded UPDATE of the whole tape.
CG/CMC last_price stays human. Do not switch aggregators to USD.
Not settlement. Copy stays “reference.” Do not feed marks into validate_limit_order_price, wrap-mapper, or /ust1 window.
Allowlists. Candle interval already allowlisted; keep it. Any new ticker/query params: unknown / ../ / javascript: → 400. Numeric limit stays clamped.
Do not scale every historical bar by the latest tape human (existing #543 don’t). Per-bar human × as-of quote USD only.
Rebuild is one-shot / explicit, not the 10s loop. Reconstructing damaged history from oracle_prices as-of block_timestamp (USTC/LUNC quotes) plus documented hub reconstruction is allowed as a migration or ops job, not per refresh.

Relevant files

File	Role
`indexer/src/db/queries/hub_prices.rs`	`backfill_usd_from_hub` (remove from refresh); `refresh_hub_prices` loop
`indexer/src/indexer/hub_usd.rs`	Live hub marks from reserves + USTC oracle
`indexer/src/indexer/candle_builder.rs`	Swap merge; extend with mark-to-market upsert
`indexer/src/db/queries/candles.rs`	GET + `rebuild_candles_from_swaps`
`indexer/src/indexer/pair_price_usd.rs`	P522-Q `price_usd` / `volume_usd`
`indexer/src/indexer/parser.rs`	Ingest-time stamp (keep as-of ingest)
`indexer/src/indexer/oracle.rs` + `db/queries/oracle.rs`	CEX ticks + `oracle_prices` history
`indexer/src/indexer/poller.rs`	Oracle + hub cadence wiring
`indexer/src/db/queries/pair_reserves.rs`	Current reserves (H2/H3); not a history table
`indexer/src/api/pairs.rs`	`GET .../candles`
`indexer/tests/api_hub_prices.rs`, `candle_human_usd.rs`, `candle_skip_zero_price.rs`	Extend
`frontend-dapp/src/components/charts/priceChartCandles.ts`	invertUsd per bar (no client-side oracle stitch)
`frontend-dapp/src/utils/tradePairDisplayOrientation.ts`	Default invert UST1-as-base
`docs/indexer-invariants.md`, `skills/AGENTS_INDEXER_HUB_USD.md`, `skills/AGENTS_FRONTEND_USD_CANDLE_INVERT.md`, `docs/frontend.md` § Trade pair display invert	Invariants / playbook
`scripts/qa/verify-issue-556.sh`	Do not weaken; add `verify-issue-NNN`

Recommended direction

Delete backfill_usd_from_hub from the refresh transaction. refresh_hub_prices only replaces hub_prices. Ingest continues to stamp price_usd from the then hub/oracle. Add a regression test: two swaps at different USTC oracles keep different price_usd after a later hub refresh; candle USD OHLC is not all human × latest_hub.
Mark-to-market writer on oracle tick and/or hub refresh (same cadence order as today, after hub materialize):
- For each catalog-priced factory pair, usd = last_human × as-of quote USD (quote USD = #515 ticker or current hub mark per P522-Q). Prefer last swap human; if none, reserve spot humanized (H2/H3) so a seeded idle UST1/cUSTC pool still charts.
- Upsert the current bucket for each interval (1m…1w): merge USD OHLC with merge_candle_ohlc; trade_count / volumes unchanged (0 on a new empty bar).
- Skip when oracle/hub quote USD is missing or non-positive.
One-shot repair of already-flattened prod/QA rows: rebuild price_usd / candle USD from se.price × oracle_prices average as-of block_timestamp for USTC/LUNC quotes. For UST1/USTR quotes, document reconstruction (cannot replay destroyed hub history; going forward hub is live-only). Idempotent SQL; not the 10s loop.
Frontend: keep plotting indexer candles + per-bar invertUsd. Do not client-side overlay CoinGecko or /oracle/history onto pair charts (CORS, X5, duplicate clocks). After indexer fix, inverted UST1/cUSTC 1h/1d history must not be a unique single close/close_human.
Docs: new invariant row (time-stamped candle USD; no as-of-now hub rewrite; idle mark bars with trade_count=0). Playbook + make verify-issue-NNN. Optional later: hub_prices_history if product wants Protocol UST1/USTR sparkline — out of scope unless needed for reconstruction; pair charts should not require a new HTTP ticker.

Acceptance criteria

AC1. Hub refresh does not change historical swap_events.price_usd or candle USD for existing rows.
AC2. After two UST1/cUSTC (or cUSTC-quoted) prints at USTC $0.005 then $0.004, stored price_usd and candle USD differ by that CEX move even if human is unchanged. Inverted close/close_human equals the as-of USTC, not the latest tick.
AC3. With zero new swaps, a USTC oracle move still writes/extends USD candles for UST1/cUSTC (and other cUSTC/uusd-quoted catalog pairs) within one hub/oracle period. Inverted Price (USD) tracks USTC. Non-inverted tracks UST1 hub (reserves × USTC), not a frozen last print.
AC4. Same idle behavior for UST1/USTR (USTR USD follows hub, which moves when USTC and/or pool reserves move) and for LUNC-quoted pairs using #515 lunc.
AC5. Mark-only bars have trade_count = 0 and zero volume; swap bars still increment counts. GET /candles includes mark bars. Chart volume histogram does not show fake volume.
AC6. cLUNC/UST1 factory USD still moves when DEX human moves; implied quote USD (invertUsd) may now also move with UST1 hub/USTC instead of sticking to one live tick.
AC7. Unknown/gem quotes stay NULL / no new USD bars. Oracle down → no new marks, no $1 freeze (H1).
AC8. CG/CMC last_price unchanged (human). /oracle/price/ust1 remains 400. GET /hub-prices still O(1) current snapshot.
AC9. Default invert rules (#524) unchanged. Adaptive priceFormat (#543) still applies. No client CoinGecko stitch.
AC10. Skills + docs/indexer-invariants.md + docs/frontend.md + make verify-issue-NNN. Existing make verify-issue-556, verify-issue-543, verify-issue-522, verify-issue-515 still pass.

Test plan (all paths)

Indexer unit / lib

merge_candle_ohlc still holds for mark merges (proptest untouched).
Helper: usd = human × quote_usd for mark payload; skip ≤0 / overflow fits_numeric_38_18.

Indexer integration (Postgres)

No rewrite: insert swap + candle at hub USTC 0.005; refresh_hub_prices with 0.004; assert swap_events.price_usd and candle USD unchanged.
Ingest as-of: two swaps, oracle moved between them, same human; two distinct price_usd; rebuild candles matches ingest stamps.
Idle USTC: pair with last human (or reserves only, zero swaps); tick USTC 0.005 → 0.0045; current 1m/1h/1d close USD moves; trade_count stays 0 on mark-only bar; human unchanged if last-trade carry-forward.
Idle UST1: UST1/cUSTC reserves fixed, USTC moves → hub UST1 USD moves (H2); non-inverted candle close tracks hub UST1; inverted tracks USTC.
Idle USTR: deepest USTR pair reserves fixed, USTC moves → USTR hub moves; UST1/USTR candles update.
LUNC quote: cLUNC-quoted pair; LUNC oracle tick with no swap → USD candles move; USTC tick does not reprice a pure LUNC quote.
Oracle down: ustc=None → hub empty (existing hub_refresh_oracle_down_clears_marks); no new USD marks; no leftover $1.
Skip unknown quote / spoof native symbol=USTR.
Positive-price skip: price_usd NULL/0 still does not write USD columns (candle_skip_zero_price).
GET candles: mark bars visible in default 90d window; interval junk → 400; limit clamp; pair 404.
Load: refresh loop must not UPDATE all swap_events (test via row updated_at / query plan assertion or hook that counts updates).
Frontend vitest: inverted factory points still invertUsd per bar; a series with varying as-of quote USD is not flattened by the client; empty/invalid bars still dropped (#226/#543). No new fetch to CoinGecko from PriceChart.
Regressions: make verify-issue-556 verify-issue-543 verify-issue-522 verify-issue-515 verify-issue-524 verify-issue-548.

Test plan (attack, hack, and abuse)

DoS / write amplification (current hole): a 10s full-table UPDATE swap_events + UPDATE candles is an availability bug. After the fix, hub refresh must not rewrite historical tape. Fuzz: many pairs × intervals; mark writer must be bounded to catalog pairs + current buckets, not all 90d rows every tick.
GET DoS: do not reconstruct history from oracle_prices ⋈ swap_events on each GET /candles. Materialize. Existing limit cap 1000 / 90d default stay.
Symbol spoof / thin pool (A1 / H8): native gem symbol=USTR or dust pool < $100 TVL must not win hub ranking or receive invented USD candles. Identity = hub CW20 address / uusd/uluna denom.
Oracle manipulation as settlement: marks remain advisory (X5). Tests that limit price validation / window mint ignore candle USD. Do not document marks as NAV.
Path / ticker injection: existing allowlists on /candles interval, /oracle/history/{ticker}, /hub-prices/{ticker}. Homoglyphs, ../, javascript:, extra underscores → 400. No SQL string paste of interval except the existing match table in rebuild.
Numeric bombs: 1e309, NaN, negative human, high_human * hub overflow NUMERIC(38,18) / POWER(10,20) — skip write, never 500, never Inf in JSON (X6).
Fake volume / wash: mark bars must not increment trade_count or volume_* (would inflate Charts 24h volume if someone later summed candle volume). Overview total_volume_24h_usd stays swap_events.volume_usd only (C5 / C8).
Stale-client stitch: frontend must not multiply all bars by latest tape human (#543). Test a fixture where latest human ≠ historical human; inverted history still uses per-bar human.
Unlisted pair: factory provenance (#311) still gates insert; marks only for indexed factory pairs.
Clock / empty buckets: mark uses indexer wall clock aligned to truncate_to_interval (oracle fetched_at / hub updated_at), not LCD block time of an absent swap. Do not back-fill arbitrary past gaps on every tick (that is a bounded migration). Open of an existing in-progress bucket stays first print/mark in that bucket (open unchanged on merge).

Verification criteria

Local / CI

make verify-issue-NNN (new) + make verify-issue-556 verify-issue-543 verify-issue-522 verify-issue-515.
Integration: cd indexer && cargo test --test api_hub_prices --test candle_human_usd -- --test-threads=1 plus new mark-to-market test file.
Frontend: priceChartCandles.test.ts, tradePairDisplayOrientation.test.ts.

Production / QA (columbus-5 or LocalTerra with oracle+hub)

GET /api/v1/pairs/<UST1_cUSTC>/candles?interval=1h : close/close_human is not a single unique value across days when USTC moved; inverted series tracks oracle/history/ustc directionally (advisory, sources may differ).
Pause trading on UST1/cUSTC (or use a quiet window): USTC CEX move still extends candles within ~oracle/hub period; trade_count on new bars = 0.
UST1/USTR inverted history is not a unique live USTR tick if USTR hub or USTC moved.
cLUNC/UST1 still shows DEX-driven movement; no regression to empty chart.
GET /api/v1/hub-prices still current snapshot; Protocol CEX card still uses /oracle/history/{ticker} independently.
Indexer logs: no per-refresh full-table swap rewrite; hub refresh stays snapshot replace + bounded mark upserts.

Done when a reviewer can open /charts UST1/cUSTC (default invert) over several days and see USTC-like USD movement with or without new CL8Y volume, and cLUNC is no longer the only pair whose Price (USD) history moves.

## Summary `/charts` and `/trade` **Price (USD)** history for **UST1/cUSTC** and **UST1/USTR** is a flat line at the **live** hub/oracle tick. CoinGecko USTC/USD moves, and CL8Y has DEX volume, but inverted candles do not. **cLUNC/UST1** still moves because the chart is factory-oriented (cLUNC is `asset_0`) and the DEX ratio changed. Users expect USTC-quoted USD (and therefore UST1 USD) to follow the CEX USTC oracle **even when that pair has no new swaps**. This is one product bug with two coupled causes in the indexer: 1. **As-of-now rewrite:** every hub refresh overwrites **all** historical `swap_events.price_usd` and candle USD OHLC with `human × current hub_prices`. 2. **Last-trade-only candles:** `update_candles_for_swap` never writes a bar unless a swap lands, so idle intervals cannot track oracle/hub ticks. Fixing only (1) leaves sparse step-functions. Fixing only (2) is immediately undone by the next rewrite. Bundle both. Related: [#556](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/556) (hub USD + this rewrite), [#522](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/522) (P522 `price_usd`), [#543](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/543) / [#524](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/524) (per-bar `invertUsd`), [#515](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/515) (CEX USTC/LUNC). ## Environment (production, 2026-08-19) - Network: columbus-5 (`https://dex.cl8y.com`, indexer `https://indexer.dex.cl8y.com`) - Live CEX USTC ≈ `$0.004788` (KuCoin/MEXC; CoinGecko also polled). `GET /api/v1/oracle/history/ustc?limit=1000` spans only ~8.5h at 30s polls and already moves ~1.4%. Multi-day CoinGecko USTC is larger; pair charts should reflect that path, not a single tick. - Hub snapshot: cUSTC = live USTC; UST1 ≈ `$0.999`; USTR ≈ `$0.01151`. | Pair | Address | Default UI invert (#524) | What the user sees on Price (USD) | |------|---------|--------------------------|-----------------------------------| | UST1/cUSTC | `terra1ceprjsxp86ggftf5e38wwt34l83e5gq7penkdnv4wsatkwcs8v6qccw55f` | **on** (other token = cUSTC) | cUSTC/USD → **flat at live USTC** | | UST1/USTR | `terra16vxrhpvpcucu05y0nr862vf9hnqeh274uaff4s7hz4n0ea74006qf5hgqy` | **on** (other token = USTR) | USTR/USD → **flat at live hub USTR** | | cLUNC/UST1 | `terra1su5363453fj326u4t0kqar30f35cm3n0dc9yksg379u6875z350s4mm7h4` | **off** (cLUNC is base) | USD of 1 cLUNC follows DEX `human` × live UST1 → **moves** | Measured on `GET /api/v1/pairs/{addr}/candles?interval=1h`: `close / close_human` is **identical on every bar** and equals the **current** `hub_prices` quote (cUSTC `0.004788`, USTR `0.01151452`, UST1 `0.999285`). Rel range of invertUsd = **0%**. That is `invertUsd` (#543) applied to as-of-now rewritten factory USD — not a frontend plot bug. ## Current codebase ### Candle write path is swap-only [`indexer/src/indexer/candle_builder.rs`](indexer/src/indexer/candle_builder.rs) `update_candles_for_swap` is the only live candle writer. Parser [`indexer/src/indexer/parser.rs`](indexer/src/indexer/parser.rs) stamps `price_usd` at **ingest** via [`pair_price_usd::price_usd_for_human_quote_per_base`](indexer/src/indexer/pair_price_usd.rs) (P522-2: USD of 1 human `asset_0` = human quote-per-base × quote USD). Missing / non-positive `price_usd` skips the candle (**P522-4** / [#543](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/543)). Rebuild [`rebuild_candles_from_swaps`](indexer/src/db/queries/candles.rs) is also swap-bucket only. Oracle ticks, hub refreshes, and `pair_reserves` snapshots do **not** create or extend candles. `GET /api/v1/pairs/{addr}/candles` ([`indexer/src/api/pairs.rs`](indexer/src/api/pairs.rs)) returns stored rows (default 90-day window, `limit` clamp 1–1000). The dApp [`PriceChart.tsx`](frontend-dapp/src/components/charts/PriceChart.tsx) plots that series after [`applyChartDisplayInvert`](frontend-dapp/src/components/charts/priceChartCandles.ts) / [`invertUsd`](frontend-dapp/src/utils/tradePairDisplayOrientation.ts). It does **not** mix in `GET /api/v1/oracle/history/{ticker}` (that feed is Protocol-only, [`useProtocolOracleQueries.ts`](frontend-dapp/src/components/protocol/useProtocolOracleQueries.ts)). ### Hub refresh rewrites all history to *now* [`refresh_hub_prices`](indexer/src/db/queries/hub_prices.rs) runs on book-snapshot cadence (~10s, `BOOK_SNAPSHOT_INTERVAL_MS`) in [`poller.rs`](indexer/src/indexer/poller.rs). After replacing the **current** `hub_prices` snapshot it always calls `backfill_usd_from_hub`: ```sql -- swap_events.price_usd := se.price * hp.price_usd (current hub) -- candles OHLC := *_human * hp.price_usd (current hub) JOIN hub_prices hp ON hp.asset_id = p.asset_1_id ``` `hub_prices` is a **single current row** per ticker (`custc` / `ust1` / `ustr`) — no history table ([`indexer/migrations/20260818180000_hub_prices.sql`](indexer/migrations/20260818180000_hub_prices.sql)). `pair_reserves` is also **one row per pair** (upsert), not a time series. Consequences: - Every cUSTC-, UST1-, or USTR-quoted pair’s **entire** USD history is scaled to the latest hub tick. Human OHLC (DEX prints) is preserved; USD OHLC is not time-stamped. - Default invert on UST1-as-base pairs (#524) displays `factory_usd / human` = **quote USD**. After the rewrite that equals **live** cUSTC/USTR, so the chart is a horizontal line at the current CoinGecko-ish USTC (or current USTR). That matches the report: “USTC barely/never changed”; “UST1 never changed” (UST1 hub ≈ $1 and/or inverted USTR mark stuck at *now*). - cLUNC/UST1 is **not** inverted by default, so the pane shows USD of cLUNC = `human × live UST1`. `human` (UST1 per cLUNC) actually moved on-chain, so that pair still “has a chart.” The rewrite still flattens its implied quote USD to live UST1 (`invertUsd` unique = 1). - LUNC-quoted pairs are **not** in `hub_prices`, so they are not rewritten this way. There is still no mark-to-market without swaps. - `volume_usd` is **not** rewritten (ingest-time, [#548](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/issues/548) **C8**). Tape `price_usd` and volume USD disagree in time basis. - Full-table `UPDATE swap_events` + `UPDATE candles` every ~10s is load, not O(1). **H10** / #556 said GET must not scan `swap_events`; the background loop now does worse than a GET scan. ### What is already correct (do not regress) - P522-1 human quote-per-base; P522-2 factory USD of `asset_0`; P522-Q catalog (USTC/cUSTC/`uusd` = #515 `ustc`; LUNC/cLUNC/`uluna` = #515 `lunc`; UST1/USTR = hub, not `$1` / not `2.5×`). - **H1–H3:** `usd(cUSTC)` = USTC oracle; `usd(UST1)` from deepest cUSTC/UST1 **reserves** (not last print); `usd(USTR)` from deepest vs already-priced hub. Snapshot GET stays O(1). - **C543 / T524:** indexer stores factory USD; UI invert is `invertUsd` per bar, never `1/x` of USD, never human on a USD axis. - CG/CMC `last_price` stays human (**H10**). Advisory feeds (**X5**) — not settlement, TWAP, or `/ust1` window. - CEX catalog stays `ustc` \| `lunc` \| `vfdusd`. `/oracle/price/ust1` (and `ustr` / `custc`) stay **400** (**H6**). ## Why the new implementation is needed 1. **Honest history.** Price (USD) candles are a time series. Scaling every past bar by *today’s* USTC/USTR/UST1 mark destroys CEX and hub movement and makes inverted UST1/cUSTC look like USTC “never moved,” contradicting CoinGecko and `oracle_prices`. 2. **Idle mark-to-market.** UST1 USD is `reserves(cUSTC/UST1) × usd(USTC)` (**H2**). That can change when USTC CEX moves with **zero** UST1/cUSTC swaps. Retail charts must follow that mark; last-trade-only candles cannot. 3. **Same rule for all catalog quotes.** cLUNC-looking movement today is an accident of orientation + DEX `human` chops, not a correct USD clock. LUNC-quoted pairs should also pick up #515 `lunc` ticks between prints so behavior is consistent. 4. **Stop rewriting the tape.** Historical `swap_events.price_usd` is an as-of-ingest advisory. Replacing it every 10s also corrupts Open/Close stats, limit USD notionals that read tape, and any integrator using `price_usd` as a print. ## Constraints / guardrails 1. **Time-stamp USD; never as-of-now rewrite.** Do not `UPDATE` historical `swap_events.price_usd` or candle USD from the live `hub_prices` snapshot. Live hub is for **new** ingest, **current** GET `/hub-prices`, overview fields, and **new** mark bars only. 2. **Human OHLC stays DEX prints.** Mark-to-market may carry forward last human (or use reserve spot as the mark’s human **only on zero-trade bars**, documented). Do not fabricate `trade_count` / `volume_*` for oracle ticks. Histogram volume stays swap volume (**C5** spirit). 3. **P522-2 / P522-4 unchanged.** Candle `open/high/low/close` remain factory USD of `asset_0` from `price_usd` math. No human-on-USD-axis fallback. UI invert stays frontend `invertUsd` per bar (**C543-1–C543-9**, **T524-4**). 4. **Quote catalog only (P522-Q / A1).** Mark-to-market **only** pairs whose quote (or documented hub graph) is USTC/cUSTC/`uusd`, LUNC/cLUNC/`uluna`, UST1, or USTR identified by **contract/denom**, not symbol. Gem natives spoofing `USTR` must not get USD bars. Unknown quote → skip (NULL), never invent. 5. **Oracle down → no new USD marks (H1 / X6).** Do not freeze last peg, `$1`, or `2.5×`. Non-finite / `fits_numeric_38_18` overflow → skip write. `high ≥ low ≥ 0`, merge invariants in `merge_candle_ohlc`. 6. **No vFDUSD in DEX USD (X4 / H7).** Do not add `ustr`/`ust1`/`custc` to CEX `OracleTicker`. Do not use `/oracle/price` for hub. 7. **Do not scan `swap_events` on GET.** Materialize marks on oracle/hub cadence. Cap work: catalog-priced **active** factory pairs only (listed, hub/oracle quote). No unbounded `UPDATE` of the whole tape. 8. **CG/CMC `last_price` stays human.** Do not switch aggregators to USD. 9. **Not settlement.** Copy stays “reference.” Do not feed marks into `validate_limit_order_price`, wrap-mapper, or `/ust1` window. 10. **Allowlists.** Candle `interval` already allowlisted; keep it. Any new ticker/query params: unknown / `../` / `javascript:` → 400. Numeric `limit` stays clamped. 11. **Do not scale every historical bar by the latest tape human** (existing #543 don’t). Per-bar human × **as-of** quote USD only. 12. **Rebuild is one-shot / explicit**, not the 10s loop. Reconstructing damaged history from `oracle_prices` as-of `block_timestamp` (USTC/LUNC quotes) plus documented hub reconstruction is allowed as a **migration** or ops job, not per refresh. ## Relevant files | File | Role | |------|------| | [`indexer/src/db/queries/hub_prices.rs`](indexer/src/db/queries/hub_prices.rs) | `backfill_usd_from_hub` (remove from refresh); `refresh_hub_prices` loop | | [`indexer/src/indexer/hub_usd.rs`](indexer/src/indexer/hub_usd.rs) | Live hub marks from reserves + USTC oracle | | [`indexer/src/indexer/candle_builder.rs`](indexer/src/indexer/candle_builder.rs) | Swap merge; extend with mark-to-market upsert | | [`indexer/src/db/queries/candles.rs`](indexer/src/db/queries/candles.rs) | GET + `rebuild_candles_from_swaps` | | [`indexer/src/indexer/pair_price_usd.rs`](indexer/src/indexer/pair_price_usd.rs) | P522-Q `price_usd` / `volume_usd` | | [`indexer/src/indexer/parser.rs`](indexer/src/indexer/parser.rs) | Ingest-time stamp (keep as-of ingest) | | [`indexer/src/indexer/oracle.rs`](indexer/src/indexer/oracle.rs) + [`db/queries/oracle.rs`](indexer/src/db/queries/oracle.rs) | CEX ticks + `oracle_prices` history | | [`indexer/src/indexer/poller.rs`](indexer/src/indexer/poller.rs) | Oracle + hub cadence wiring | | [`indexer/src/db/queries/pair_reserves.rs`](indexer/src/db/queries/pair_reserves.rs) | Current reserves (H2/H3); not a history table | | [`indexer/src/api/pairs.rs`](indexer/src/api/pairs.rs) | `GET .../candles` | | [`indexer/tests/api_hub_prices.rs`](indexer/tests/api_hub_prices.rs), [`candle_human_usd.rs`](indexer/tests/candle_human_usd.rs), [`candle_skip_zero_price.rs`](indexer/tests/candle_skip_zero_price.rs) | Extend | | [`frontend-dapp/src/components/charts/priceChartCandles.ts`](frontend-dapp/src/components/charts/priceChartCandles.ts) | invertUsd per bar (no client-side oracle stitch) | | [`frontend-dapp/src/utils/tradePairDisplayOrientation.ts`](frontend-dapp/src/utils/tradePairDisplayOrientation.ts) | Default invert UST1-as-base | | [`docs/indexer-invariants.md`](docs/indexer-invariants.md), [`skills/AGENTS_INDEXER_HUB_USD.md`](skills/AGENTS_INDEXER_HUB_USD.md), [`skills/AGENTS_FRONTEND_USD_CANDLE_INVERT.md`](skills/AGENTS_FRONTEND_USD_CANDLE_INVERT.md), [`docs/frontend.md`](docs/frontend.md) § Trade pair display invert | Invariants / playbook | | [`scripts/qa/verify-issue-556.sh`](scripts/qa/verify-issue-556.sh) | Do not weaken; add `verify-issue-NNN` | ## Recommended direction 1. **Delete `backfill_usd_from_hub` from the refresh transaction.** `refresh_hub_prices` only replaces `hub_prices`. Ingest continues to stamp `price_usd` from the **then** hub/oracle. Add a regression test: two swaps at different USTC oracles keep different `price_usd` after a later hub refresh; candle USD OHLC is **not** all `human × latest_hub`. 2. **Mark-to-market writer** on oracle tick and/or hub refresh (same cadence order as today, after hub materialize): - For each catalog-priced factory pair, `usd = last_human × as-of quote USD` (quote USD = #515 ticker or current hub mark per P522-Q). Prefer last swap human; if none, reserve spot humanized (H2/H3) so a seeded idle UST1/cUSTC pool still charts. - Upsert the **current** bucket for each interval (`1m`…`1w`): merge USD OHLC with `merge_candle_ohlc`; `trade_count` / volumes unchanged (0 on a new empty bar). - Skip when oracle/hub quote USD is missing or non-positive. 3. **One-shot repair** of already-flattened prod/QA rows: rebuild `price_usd` / candle USD from `se.price` × `oracle_prices` average **as-of** `block_timestamp` for USTC/LUNC quotes. For UST1/USTR quotes, document reconstruction (cannot replay destroyed hub history; going forward hub is live-only). Idempotent SQL; not the 10s loop. 4. **Frontend:** keep plotting indexer candles + per-bar invertUsd. Do **not** client-side overlay CoinGecko or `/oracle/history` onto pair charts (CORS, X5, duplicate clocks). After indexer fix, inverted UST1/cUSTC 1h/1d history must **not** be a unique single `close/close_human`. 5. **Docs:** new invariant row (time-stamped candle USD; no as-of-now hub rewrite; idle mark bars with `trade_count=0`). Playbook + `make verify-issue-NNN`. Optional later: `hub_prices_history` if product wants Protocol UST1/USTR sparkline — **out of scope** unless needed for reconstruction; pair charts should not require a new HTTP ticker. ## Acceptance criteria - [ ] **AC1.** Hub refresh does **not** change historical `swap_events.price_usd` or candle USD for existing rows. - [ ] **AC2.** After two UST1/cUSTC (or cUSTC-quoted) prints at USTC `$0.005` then `$0.004`, stored `price_usd` and candle USD differ by that CEX move even if `human` is unchanged. Inverted `close/close_human` equals the **as-of** USTC, not the latest tick. - [ ] **AC3.** With **zero new swaps**, a USTC oracle move still writes/extends USD candles for UST1/cUSTC (and other cUSTC/`uusd`-quoted catalog pairs) within one hub/oracle period. Inverted Price (USD) tracks USTC. Non-inverted tracks UST1 hub (`reserves × USTC`), not a frozen last print. - [ ] **AC4.** Same idle behavior for UST1/USTR (USTR USD follows hub, which moves when USTC and/or pool reserves move) and for LUNC-quoted pairs using #515 `lunc`. - [ ] **AC5.** Mark-only bars have `trade_count = 0` and zero volume; swap bars still increment counts. GET `/candles` includes mark bars. Chart volume histogram does not show fake volume. - [ ] **AC6.** cLUNC/UST1 factory USD still moves when DEX `human` moves; implied quote USD (`invertUsd`) may now **also** move with UST1 hub/USTC instead of sticking to one live tick. - [ ] **AC7.** Unknown/gem quotes stay NULL / no new USD bars. Oracle down → no new marks, no `$1` freeze (**H1**). - [ ] **AC8.** CG/CMC `last_price` unchanged (human). `/oracle/price/ust1` remains 400. `GET /hub-prices` still O(1) current snapshot. - [ ] **AC9.** Default invert rules (#524) unchanged. Adaptive `priceFormat` (#543) still applies. No client CoinGecko stitch. - [ ] **AC10.** Skills + `docs/indexer-invariants.md` + `docs/frontend.md` + `make verify-issue-NNN`. Existing `make verify-issue-556`, `verify-issue-543`, `verify-issue-522`, `verify-issue-515` still pass. ## Test plan (all paths) **Indexer unit / lib** 1. `merge_candle_ohlc` still holds for mark merges (proptest untouched). 2. Helper: `usd = human × quote_usd` for mark payload; skip ≤0 / overflow `fits_numeric_38_18`. **Indexer integration (Postgres)** 3. **No rewrite:** insert swap + candle at hub USTC `0.005`; `refresh_hub_prices` with `0.004`; assert `swap_events.price_usd` and candle USD **unchanged**. 4. **Ingest as-of:** two swaps, oracle moved between them, same human; two distinct `price_usd`; rebuild candles matches ingest stamps. 5. **Idle USTC:** pair with last human (or reserves only, zero swaps); tick USTC `0.005` → `0.0045`; current `1m`/`1h`/`1d` close USD moves; `trade_count` stays 0 on mark-only bar; human unchanged if last-trade carry-forward. 6. **Idle UST1:** UST1/cUSTC reserves fixed, USTC moves → hub UST1 USD moves (**H2**); non-inverted candle close tracks hub UST1; inverted tracks USTC. 7. **Idle USTR:** deepest USTR pair reserves fixed, USTC moves → USTR hub moves; UST1/USTR candles update. 8. **LUNC quote:** cLUNC-quoted pair; LUNC oracle tick with no swap → USD candles move; USTC tick does **not** reprice a pure LUNC quote. 9. **Oracle down:** `ustc=None` → hub empty (**existing** `hub_refresh_oracle_down_clears_marks`); no new USD marks; no leftover `$1`. 10. **Skip unknown quote / spoof native `symbol=USTR`.** 11. **Positive-price skip:** `price_usd` NULL/0 still does not write USD columns (`candle_skip_zero_price`). 12. **GET candles:** mark bars visible in default 90d window; `interval` junk → 400; `limit` clamp; pair 404. 13. **Load:** refresh loop must not `UPDATE` all `swap_events` (test via row `updated_at` / query plan assertion or hook that counts updates). 14. **Frontend vitest:** inverted factory points still `invertUsd` per bar; a series with **varying** as-of quote USD is **not** flattened by the client; empty/invalid bars still dropped (#226/#543). No new fetch to CoinGecko from `PriceChart`. 15. **Regressions:** `make verify-issue-556` `verify-issue-543` `verify-issue-522` `verify-issue-515` `verify-issue-524` `verify-issue-548`. ## Test plan (attack, hack, and abuse) 1. **DoS / write amplification (current hole):** a 10s full-table `UPDATE swap_events` + `UPDATE candles` is an availability bug. After the fix, hub refresh must not rewrite historical tape. Fuzz: many pairs × intervals; mark writer must be bounded to catalog pairs + current buckets, not all 90d rows every tick. 2. **GET DoS:** do not reconstruct history from `oracle_prices ⋈ swap_events` on each `GET /candles`. Materialize. Existing `limit` cap 1000 / 90d default stay. 3. **Symbol spoof / thin pool (A1 / H8):** native gem `symbol=USTR` or dust pool `< $100` TVL must not win hub ranking or receive invented USD candles. Identity = hub CW20 address / `uusd`/`uluna` denom. 4. **Oracle manipulation as settlement:** marks remain advisory (**X5**). Tests that limit price validation / window mint **ignore** candle USD. Do not document marks as NAV. 5. **Path / ticker injection:** existing allowlists on `/candles` `interval`, `/oracle/history/{ticker}`, `/hub-prices/{ticker}`. Homoglyphs, `../`, `javascript:`, extra underscores → 400. No SQL string paste of interval except the existing match table in rebuild. 6. **Numeric bombs:** `1e309`, NaN, negative human, `high_human * hub` overflow `NUMERIC(38,18)` / `POWER(10,20)` — skip write, never 500, never Inf in JSON (X6). 7. **Fake volume / wash:** mark bars must not increment `trade_count` or `volume_*` (would inflate Charts 24h volume if someone later summed candle volume). Overview `total_volume_24h_usd` stays `swap_events.volume_usd` only (**C5** / **C8**). 8. **Stale-client stitch:** frontend must not multiply all bars by latest tape human (#543). Test a fixture where latest human ≠ historical human; inverted history still uses **per-bar** human. 9. **Unlisted pair:** factory provenance (#311) still gates insert; marks only for indexed factory pairs. 10. **Clock / empty buckets:** mark uses indexer wall clock aligned to `truncate_to_interval` (oracle `fetched_at` / hub `updated_at`), not LCD block time of an absent swap. Do not back-fill arbitrary past gaps on every tick (that is a bounded migration). Open of an existing in-progress bucket stays first print/mark in that bucket (`open` unchanged on merge). ## Verification criteria **Local / CI** - `make verify-issue-NNN` (new) + `make verify-issue-556` `verify-issue-543` `verify-issue-522` `verify-issue-515`. - Integration: `cd indexer && cargo test --test api_hub_prices --test candle_human_usd -- --test-threads=1` plus new mark-to-market test file. - Frontend: `priceChartCandles.test.ts`, `tradePairDisplayOrientation.test.ts`. **Production / QA (columbus-5 or LocalTerra with oracle+hub)** 1. `GET /api/v1/pairs/<UST1_cUSTC>/candles?interval=1h` : `close/close_human` is **not** a single unique value across days when USTC moved; inverted series tracks `oracle/history/ustc` directionally (advisory, sources may differ). 2. Pause trading on UST1/cUSTC (or use a quiet window): USTC CEX move still extends candles within ~oracle/hub period; `trade_count` on new bars = 0. 3. UST1/USTR inverted history is not a unique live USTR tick if USTR hub or USTC moved. 4. cLUNC/UST1 still shows DEX-driven movement; no regression to empty chart. 5. `GET /api/v1/hub-prices` still current snapshot; Protocol CEX card still uses `/oracle/history/{ticker}` independently. 6. Indexer logs: no per-refresh full-table swap rewrite; hub refresh stays snapshot replace + bounded mark upserts. **Done when** a reviewer can open `/charts` UST1/cUSTC (default invert) over several days and see USTC-like USD movement **with or without** new CL8Y volume, and cLUNC is no longer the only pair whose Price (USD) history moves.

PlasticDigits commented

2026-08-19 00:57:40 +00:00

(Migrated from gitlab.com)

marked as related to #556

PlasticDigits commented

2026-08-19 00:57:41 +00:00

(Migrated from gitlab.com)

marked as related to #522

PlasticDigits commented

2026-08-19 00:57:42 +00:00

(Migrated from gitlab.com)

marked as related to #543

PlasticDigits commented

2026-08-19 00:57:42 +00:00

(Migrated from gitlab.com)

marked as related to #524

PlasticDigits commented

2026-08-19 00:57:43 +00:00

(Migrated from gitlab.com)

marked as related to #515

PlasticDigits commented

2026-08-20 02:17:16 +00:00

(Migrated from gitlab.com)

mentioned in merge request !1100

PlasticDigits commented

2026-08-20 02:17:46 +00:00

(Migrated from gitlab.com)

mentioned in merge request !1101

PlasticDigits commented

2026-08-20 02:31:49 +00:00

(Migrated from gitlab.com)

mentioned in commit 1a18a62011

mentioned in commit 1a18a62011faf4451ef176aae5fec797069bc464

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-08-20 03:23:49 +00:00

PlasticDigits commented

2026-08-20 03:23:52 +00:00

(Migrated from gitlab.com)

mentioned in commit 38c5252de3

mentioned in commit 38c5252de3582f12601fc30b0943cfbc6a6c472c

PlasticDigits commented

2026-08-20 03:52:05 +00:00

(Migrated from gitlab.com)

mentioned in commit 9c802c1aac

mentioned in commit 9c802c1aac8278a64f02559d6434697d46d1a4a3

PlasticDigits commented

2026-08-20 03:54:22 +00:00

(Migrated from gitlab.com)

Merged via !1101.

Core VERIFY_ISSUE_568_SKIP_RELATED=1 make verify-issue-568 passed (docs, no as-of-now hub rewrite, lib + candle_usd_mark integration, invertUsd frontend). Remaining:

Quiet-window USTC move on columbus-5 (prod QA; inverted UST1/cUSTC should track CEX USTC)
One-shot SQL repair rebuilds USTC/LUNC-quoted history from oracle_prices; UST1/USTR-quoted history still cannot be replayed (no hub time series)

Merged via !1101. Core `VERIFY_ISSUE_568_SKIP_RELATED=1 make verify-issue-568` passed (docs, no as-of-now hub rewrite, lib + `candle_usd_mark` integration, invertUsd frontend). Remaining: - Quiet-window USTC move on columbus-5 (prod QA; inverted UST1/cUSTC should track CEX USTC) - One-shot SQL repair rebuilds USTC/LUNC-quoted history from `oracle_prices`; UST1/USTR-quoted history still cannot be replayed (no hub time series)

PlasticDigits commented

2026-08-20 03:54:30 +00:00

(Migrated from gitlab.com)

mentioned in issue #570