Dune price mirror + coherent-mark refactor

Written 2026-07-19. Supersedes the narrower "Dune for flow valuation" idea discussed the same day; this is the full refactor agreed with Fred: a local mirror of Dune minute-bar prices becomes the single source for every persisted market mark, DeFiLlama is removed from the mark pipeline, and all historical market-vs-redemption data reconstructs coherently from the mirror.

---

1. Context and root cause (why this exists)

Investigated 2026-07-19 on the "phantom" wallet (0xef08c6a47c04764b9c0964e00bd01c3e200707c5, account 0xaae6…d050): its Fluid smart-col weETH-ETH / wstETH-debt position showed an M18 entered basis of −0.82 ETH (−4.3% of 19.1 ETH entry equity) where the true figure is ≈ +0.05 ETH.

Verified end to end (entry tx 0x7db48e61cd81f676cdfbc66fcd9f4ce50c8259554742d07951a293bad965fe1c, block 25407156, 2026-06-27 06:22:23 UTC):

• Redemption marks are correct and block-precise. On-chain stEthPerToken() = 1.2379826 and weETH getRate() = 1.0979478 at the flow block match the stored value_redemption to 5 decimals. Nothing on the redemption side changes, ever.
• Market marks are the disease. The stored wstETH/ETH market mark was 1.24182 (+31bps over redemption); the Uniswap v3 wstETH/WETH 0.01% pool spot at the same block was 1.23779 (−1.6bps, normal). The mark comes from loadMarketContext history mode: DeFiLlama USD quote ÷ DeFiLlama WETH USD at the 6h bucket. All M5 vintage guards passed (claimed vintages within 61s), but DeFiLlama's per-coin quotes are effectively stale relative to their claimed timestamps (wstETH barely moved for 2h while WETH tracked spot), so the USD ratio carries ±30–50bps of noise that no timestamp-coherence check can see. Neighbouring 6h samples jitter 1.2370 ↔ 1.2445 around a true ~1.2378.
• Entry basis amplifies it: the current Basis column re-marks every sync so the noise washes out visually; deriveLegEnteredBasis freezes ONE bucket's noise permanently, multiplied by GROSS leg notionals against small equity (debt 300 ETH × 31bps = −0.93; weETH col 106 ETH × 10bps = +0.11 → −0.82 on 19 ETH equity).
• The same noise sits in token_basis (the ±30–50bps "6h step at the i.i.d. noise floor" previously diagnosed) and therefore in the market-depth modal's depeg charts (MarketDepthModal → /api/carry-oracle → src/lib/data/basis.ts → token_basis), whose "deepest 1-year discount" markers likely tag feed artifacts, not market events.

Empirical fix validation (2026-07-19). Dune prices.minute (hybrid table, 900k+ tokens, bars aligned to 5-minute intervals, both legs sampled by the same pipeline on the same bar) at the phantom entry minute gives wstETH/WETH = 1.23786 — within 0.6bps of the on-chain truth; the whole 40-minute window sits within ±1bp. Measured cost: 0.913 credits on the small engine for that single point-execution (Dune query 8032985). Quota: 2,500/mo. (NB — a prices.minute WINDOW execution scans the whole time partition, ~110-170 credits/month; §Phase A's cost-model revision moves standing data to an hourly query and reserves prices.minute for narrow exact-minute true-up. The 0.6bps accuracy result above is unaffected — hourly bars carry the same same-bar coherence.)

Alternatives researched and rejected: Chainlink historical rounds (stETH/ETH feed has a 0.5% deviation threshold — the posted price sits anywhere in a 50bp band; most wrappers have no market feed), CoinGecko onchain / Bitquery / DexPaprika (per-pool OHLCV — hands the pool-selection problem back to us, paid tiers), Kaiko-class (CEX-weighted, expensive), per-venue on-chain pool readers (needs UniV3+Curve+UniV4+Fluid+Balancer mechanisms; rETH/osETH/ezETH have no clean pool at all — this is why the live tier went to Kyber in the first place).

2. The core principle

market(block) = (1 + basis) × redemption(block).

Basis (the secondary-market premium/discount) is the SLOW variable — bps per day outside depegs. Redemption is EXACT at any block from the chain. All historical noise came from re-measuring the full market price as a ratio of two fast-moving USD levels sampled at not-quite-the-same instant. With a mirror of same-bar USD quotes, priceInBook(token, ts) = bar(token, ts).usd / bar(numeraire, ts).usd is coherent by construction — the ETH/BTC level cancels exactly, leaving only the slow ratio. The decomposition is the REASON same-bar division is safe; the implementation just divides same-bar quotes.

3. Target architecture

Dune prices.minute (5-min bars)
        │  6h incremental sync (rides existing refresher cron, ~1 credit)
        │  + fetch-through on miss (backfills, pre-window history)
        ▼
onchain_credit.token_price_bars           ← the ONLY historical market source
        │
        ├─► priceInBook helper (same-bar division)
        │       ├─► flow valuation (valueFlows history mode)  → entry basis M18
        │       ├─► snapshot valuation (6h cron, backfill)    → stored series/charts
        │       └─► liquidation USD netting / Outside-section USD levels
        │
        └─► token-basis refresher (6h)  → token_basis  → /api/carry-oracle
                                                       → market-depth modal, carries UI
KyberSwap aggregator mid (§4.3a)  → LIVE "now" tier ONLY (ephemeral, never persisted)
                                    fallback: latest mirror bar (≤6h, flagged)
Chain (archive RPC)               → ALL redemption rates, share decompositions,
                                    PT TWAPs, block timestamps (unchanged)
DeFiLlama                         → DELETED from the mark pipeline

Mark-source map (the contract this refactor implements):

Surface	Market side	Redemption side
Entry basis (each flow, at its block)	mirror bar at the flow MINUTE	chain at the flow block
Live sync (current basis)	Kyber mid, ephemeral	chain now
Stored 6h snapshots, portfolio charts, market-depth modal	mirror bars (6h grid)	chain at snapshot block

JIT semantics ("settle on next cron"): a flow detected minutes after it happened may predate both the mirror's last sync and Dune's own bar ingestion. The JIT async valuation reads the latest available bar (≤6h-old basis, worth ~1–3bps in calm markets) and the next cron sync + cheap re-mark trues it to the exact minute once the bar exists. This is a design decision, not a bug.

4. Phases

Four separately reviewable PRs, standard pipeline (feature branch → PR → review → merge to staging → release). Each PR updates the relevant /docs page in the same PR (vitepress build as dead-link check). Never deploy prod without explicit permission.

---

Phase A — the mirror: token_price_bars + Dune client + bulk load

Migration scripts/sql/054-token-price-bars.sql (next free number; 053 is sgho-token):

-- 5-minute price bars mirrored from Dune prices.minute. The ONLY historical
-- market-price source for portfolio marks and token_basis. Agent-grade:
-- chain_id + address keys, bar-anchored, append-only.
CREATE TABLE IF NOT EXISTS onchain_credit.token_price_bars (
  chain_id      INTEGER     NOT NULL,  -- 1 = ethereum; 0 = the BTC reference row
  token_address TEXT        NOT NULL,  -- lowercase; Dune's fixed native addresses for natives
  bar_ts        TIMESTAMPTZ NOT NULL,  -- 5-min-aligned bar start (UTC)
  price_usd     NUMERIC     NOT NULL CHECK (price_usd > 0),
  source        TEXT        NOT NULL DEFAULT 'dune:prices.minute',
  ingested_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (chain_id, token_address, bar_ts)
);
CREATE INDEX IF NOT EXISTS token_price_bars_ts_idx
  ON onchain_credit.token_price_bars (bar_ts DESC);
GRANT SELECT, INSERT, UPDATE ON onchain_credit.token_price_bars TO onchain_credit;

Notes: migrations are applied manually on the server as the postgres owner (migrate.sh <bare-db-name>); the GRANT is mandatory (past incident). No prerendered page reads this table (flow valuation is cron/JIT; the modal is a client-side lazy API call), so there is no prerender-migrate deadlock exposure — but apply the migration BEFORE the deploy that ships readers, per standard order.

Tracked-asset set = BASIS_TOKENS ∪ numeraires (WETH, BTC reference) ∪ the coverage deltas: add DAI and sDAI rows to BASIS_TOKENS (par / share_rate respectively); note LBTC/eBTC/weETHs/PST land with the Fluid backlog, each adding one row here. WETH/ETH-sentinel legs are identity in the ETH book and never need bars for book-unit marks — WETH bars exist as the DENOMINATOR and for USD display.

Cost-model revision (2026-07-20, from the Phase-A bulk load). A prices.minute WINDOW execution scans the whole time partition, so a 1-month window cost ~110-170 credits — ~100x the original estimate. TWO validated saved queries replace the single minute-window query:

• HOURLY — prices.hour, DUNE_QUERY_ID_HOURLY (query 8033816), same params (tokens, from_iso, to_iso) and output, bars on the hour (⊂ the 300s grid, CHECK passes). Cost is SUBLINEAR in span: 1y × ~40 tokens ≈ 16 credits in one execution, 1 month ≈ 1.5, a 6h window ≈ 0.25 (per-execution floor ~0.22). This is every window path: sync, preload, fetch-through — the STANDING data.
• EXACT-MINUTES — prices.minute, DUNE_QUERY_ID_MINUTES (query 8033818), params tokens + minutes (CSV of 5-min-aligned ISO timestamps). Cost scales with the CALENDAR SPAN of the requested minutes, not their count (~1.5 credits clustered ≤1h, ~22+ scattered over a year), so it is reserved for narrow ≤6h clusters — the Milestone C/D flow true-up path.

Dune client src/lib/data/dune.ts:

• Plain HTTP against the Dune API (DUNE_API_KEY env; server + .env.local). No SDK.
• Dual query routing. syncBars(tokens, fromTs, toTs) runs the HOURLY query (DUNE_QUERY_ID_HOURLY) for a window; syncExactMinutes(tokens, minutesTsSec[]) runs the EXACT-MINUTES query (DUNE_QUERY_ID_MINUTES), aligning each ts to its 5-min bar and splitting into ≤6h clusters (one execution each). Execute → poll (reading execution_cost_credits off the status, free) → page results (32k rows/page).
• Window clamp. syncBars refuses a window > 48h (a [fail]-style log) and clamps to the most recent 48h unless allowLongWindow (only the bulk load passes it): a stale-table catch-up must go through the bulk tool, never become one giant cron execution.
• Credit guard. A per-process accumulator (DUNE_MAX_CREDITS_PER_RUN, default 50) stops issuing further executions once reached (prints [fail], returns partial); the per-execution credit cost is logged at info level always.
• getBar(chainId, token, tsSec) / getBarsAt(tokens, tsSec) read the bar covering ts (bar_ts = floor(ts/300)*300), walking back up to BAR_STALE_MAX (6h) if absent, returning the bar and its actual bar_ts so callers flag staleness.
• Fetch-through: a DB miss beyond BAR_STALE_MAX triggers one HOURLY syncBars for a ±1h window (cheap floor; exact minutes are reserved for explicit true-up) and re-reads. Misses after fetch-through return null (M9: honest null, never fabricated). Fetch-through failures never throw a valuation; they degrade to null.
• Unit tests: bar alignment, walk-back bounds, fetch-through write-back, null honesty, CSV param encoding, the 48h clamp, cluster splitting + alignment, the credit guard, and dual-id routing. Injectable fetch + pool (no network, no DB).

Bulk load script scripts/backfill-token-price-bars.ts:

• Loads ~1 year of HOURLY bars for the tracked set in ONE execution (allowLongWindow, ~16 credits one-time; --halves splits into two half-year executions if the single result pages awkwardly). Idempotent upserts, resumable (skips a span whose WETH bars ≥ 0.9 × window hours), [fail]-bracket logging + the all-tokens-zero abort + a credit-guard-stop abort.
• Older-than-preload history + exact flow minutes resolve later via fetch-through / syncExactMinutes, so the window is an optimization, not a correctness boundary.

Sync in the 6h cron: prepend a syncBars(tracked, lastSyncedBar, now) step to scripts/refreshers/token-basis.ts (it already runs on the 6h grid; no crontab change). The 6h window costs ~0.25 credits ⇒ ~30/mo steady state. A >48h gap is CLAMPED to the most recent 48h (fresh bars for the current snapshot) and the older gap is caught up by re-running the bulk tool, never the cron.

Flow valuation semantics (Milestone C/D): a mark reads the NEWEST COMMON bar ≤ the flow ts shared by the asset and its numeraire — hourly standing data by default, and the exact 5-minute bar where syncExactMinutes has trued a flow up. Because both legs divide from the SAME bar, the ETH/BTC level cancels; the residual staleness of an hourly bar against a flow minute is ~1-3bps of basis drift in calm markets, within the phantom acceptance band (entered basis +0.05 ± 0.02 ETH), and the exact-minute true-up removes it where a flow warrants the precision.

Coverage test (build-breaking, mirroring aggregator-mid.test.ts): every non-identity, non-PT accounting asset reachable by portfolio valuation (the buckets/registry universe) must be in the tracked set; listing a new asset without a mirror row fails CI.

Phase A ships with the table EMPTY in prod until the ops step runs the bulk load. Nothing reads it yet. Docs page: docs/ data-pipeline section, new "Token price bars (Dune mirror)" entry: schema, sync cadence, fetch-through, credits budget, ToS note (verify Dune API terms permit retaining query results for app use — the credit model is built around it; one-time check, link the ToS in the docs page).

Acceptance: bulk load completes on staging DB in one hourly execution (~16 credits, ~40 assets × ~8.7k hourly bars/yr); spot-check 10 random (token, ts) against the Dune UI; the 6h sync appends the newest window (~0.25 credits); a syncExactMinutes cluster trues a sample flow minute to the exact 5-min bar; coverage test green.

---

Phase B — token_basis rewritten from the mirror (+ market-depth modal dedup)

Refresher switch (scripts/refreshers/token-basis.ts): replace getHistoricalPrices (DeFiLlama) and the ad-hoc btcUsdAt fetch with getBarsAt(tracked ∪ numeraires, tsSec) — token and numeraire quotes from the SAME bar. market_price_usd = the token's bar USD; redemption_value_usd and basis computed exactly as today (share_rate at-or-before T × numeraire bar USD). RULE, stated in a header comment: both legs of any ratio must come from the same bar; never divide a mirror quote by another vendor's quote — that re-creates the incoherence this refactor deletes.

Historical rewrite scripts/backfill-token-basis.ts (existing scaffolding): re-derive the full token_basis history from mirror bars + token_yield_apy share rates at each 6h grid point, for every tracked token, over the mirror window. This OVERWRITES a served table (the carries market-depth modal and the carry-oracle panel read it), so:

• Parity gate before overwrite: the script first writes to token_basis_dune_shadow (same DDL) and emits a per-token parity report: (a) step noise — stddev of 6h-to-6h basis change — must DROP materially (expect ~30–50bps → ~1–3bps for majors); (b) LEVEL must sit within the old series' noise envelope (no systematic re-level beyond ~10bps except where the old series was provably wrong); (c) known real dislocation episodes (eyeball the top-5 old-series discount extremes per token) must survive in the new series or be explicitly classified as old-feed artifacts in the report. Fred reviews the report; only then the swap step (single transaction: DELETE + INSERT from shadow, or table swap) runs.
• The modal needs no code change (it reads token_basis), but re-verify its 1-year extreme markers by screenshot after the swap (client-rendered — CDP browser verification, never HTML grep).

Acceptance: parity report reviewed and approved; modal screenshots show tightened series; fetchTokenBasisOverlay consumers unaffected (the overlay still works pre-Phase-C — it now serves clean numbers).

---

Phase C — valuation switch + DeFiLlama deletion from the mark pipeline

The core code change. New helper in src/lib/portfolio/valuation-sources.ts (or a sibling mirror-prices.ts):

priceInBookFromMirror(asset, book, tsSec):
  book USD  → bar(asset, ts).usd                        (single quote)
  book ETH  → bar(asset, ts).usd / bar(WETH, ts).usd    (same bar, coherent)
  book BTC  → bar(asset, ts).usd / bar(BTC-ref, ts).usd (same bar)
  identity (WETH/ETH sentinel in ETH book) → 1, no read
  missing bar after fetch-through/walk-back → null (M9)

Rewire loadMarketContext mode "history" to build priceInBook and the usd map (Outside/EXCLUDED section, liquidation debt netting, wedge checks) entirely from the mirror. DELETE: EPS_PAIR, VINTAGE_DEDUPE, STALE_MAX pairing logic, pairToBookPrice, fetchTokenBasisOverlay, the per-vintage extra denominator fetches, fetchBtcUsdAt, and the DeFiLlama getHistoricalPrices import — with their tests, replaced by same-bar tests. mode:"now" keeps the Kyber tier verbatim, but its context quotes (tokenUsd/pairUsd for mid scaling and the fallback price) come from the latest mirror bar (≤6h, flagged stale beyond) instead of a live DeFiLlama fetch.

Flow valuation (valueFlows in flows.ts): the 6h-bucket ctxByBucket machinery collapses — each flow's market mark reads priceInBookFromMirror(asset, book, flowTs) at the flow's own MINUTE (this is the entry-basis precision win). Redemption path untouched (chain at flow block). JIT: same call; settle-on-next-cron semantics per §3.

Snapshot valuation (valueLeg callers in the cron/backfill writers, snapshot.ts / backfill.ts): history-mode market marks now come from the mirror at the snapshot's 6h ts (which IS a bar boundary). Stored-data semantics note for the PR body: the "methods never mix in storage" doctrine becomes "mirror bars are the single stored method".

Inventory task (do first, in the PR): git grep every importer of src/lib/data/prices.ts and disposition each — known: valuation-sources.ts (this phase), scripts/refreshers/token-basis.ts (done in Phase B), scripts/refreshers/fluid-dex.ts (inspect: if it prices display TVL, port to mirror latest-bar or leave with an explicit out-of-scope comment). DeFiLlama usage OUTSIDE the mark pipeline (token-yield APY sources, fluid_ll_apy, DEX HQ) is explicitly OUT OF SCOPE — do not touch. Delete src/lib/data/prices.ts only if the inventory empties; otherwise leave with a header comment forbidding new mark-pipeline use.

Tests: flows.test.ts / valuation tests rewritten against an injectable mirror; entry-basis.test.ts unchanged (pure); add a regression test encoding the phantom shape (col + debt wrapper legs, same-direction feed error → entered basis must equal the same-bar reconstruction, not the incoherent ratio).

Docs (same PR): docs/metrics.md — M5 rewritten from "vintage coherence" to the basis decomposition + same-bar rule; M18 gains the mirror-precision note; the JIT settle-on-next-cron semantic documented. prompts/assistant-metrics.md kept in sync with docs/metrics.md (standing rule).

Acceptance: local build + full test suite; a staging JIT sync for a test wallet produces flow rows whose wrapper marks match an on-chain pool spot-check within a few bps; no DeFiLlama call appears in portfolio-pipeline logs.

---

Phase D — repair stored marks (destructive; guarded)

Two repair scripts, run manually on the server (run-cron pattern), both advisory-lock aware (the portfolio writer lock), both idempotent, both staging first:

1. Flow re-mark scripts/repair/remark-flow-marks.ts: for every portfolio_flow_events row whose asset is a tracked mirror token (skip identity/PT/liquidation-synthetic rows), recompute value_market = qty × priceInBookFromMirror(asset, book, flow ts) and UPDATE in place. value_redemption untouched (verified correct). Dry-run mode prints a per-wallet before/after entered-basis diff first.
2. Snapshot re-mark scripts/repair/remark-snapshot-marks.ts: same for stored snapshot value_market on tracked assets, 6h grid = bar grid. This re-levels the stored basis/PnL chart series — the PR body and docs must say so (charts will show less wiggle across the whole history; that is the point). NOT the destructive full backfill re-derivation — a targeted UPDATE of one column class.

Lesson from the 2026-06-25 incident applies: never run a PARTIAL repair that manufactures fake values — a missing bar leaves the row untouched and logged, never zeroed.

Acceptance (the definition of done for the whole plan):

Correction (2026-07-20): the +0.05 ± 0.02 ETH band was itself wrong. The original band assumed both wrapper legs sat at par at the entry block. A block-precise Curve NG round-trip mid at entry block 25407156 instead shows weETH at −4.5bp (and wstETH slightly rich), so the TRUE entered basis is ≈ 0.00 ETH, not +0.05. The repaired app path now reports −0.021 ETH via the app's own deriveLegEnteredBasis on the re-marked rows, with each leg reconstructed within 0.9bp of the on-chain pools (weETH −3.96 vs −4.49bp; wstETH −0.70 vs −1.59bp). The old −0.82 ETH disease is gone. The corrected, falsifiable acceptance criterion below replaces the phantom-basis point target.

• Per-leg market marks match block-precise pool truth within ~1.5bp. Each wrapper leg's mirror-derived mark, at its flow block, agrees with an on-chain round-trip pool mid (Curve NG / Uniswap v3) to within ~1.5bp. Verified on the phantom entry block 25407156 (weETH −3.96 vs −4.49bp; wstETH −0.70 vs −1.59bp, both under 0.9bp of divergence). The composed entered basis on that wallet reads −0.021 ETH via the real /api/portfolio/* path (staging verification flow: SSH tunnel + minted session cookie + POST refresh before GET), and the UI shows it (CDP browser check on /portfolio, not SSR grep).
• A random sample of 10 wrapper flow rows re-verified against on-chain pool reads at their blocks within a few bps.
• Stored basis chart for a levered wrapper position visibly de-noised (before/after screenshot pair in the PR).

---

5. Ops runbook (server steps, in order)

1. (Phase A merge) .env.local on server: DUNE_API_KEY, DUNE_QUERY_ID_HOURLY (query 8033816), DUNE_QUERY_ID_MINUTES (query 8033818), and optionally DUNE_MAX_CREDITS_PER_RUN (default 50). Both saved queries already exist on the account; note their ids.
2. Apply migration 054 manually (postgres owner, bare DB name, GRANT included), staging DB first, then prod (with permission).
3. Run backfill-token-price-bars.ts via run-cron (staging → prod) — one hourly execution (~16 credits; add --halves if the single result pages awkwardly). Watch credits on the Dune usage endpoint.
4. (Phase B) run the shadow backfill + parity report → Fred approves → swap.
5. (Phase C) normal deploy; watch one full 6h cron cycle's logs for mirror-sync
   • valuation health; check Telegram alert channel stays quiet.
6. (Phase D) dry-run repairs on staging, review diffs, run, verify acceptance; then prod with permission.

Wait out the Actions deploy before any run-cron invocation (npm ci drops tsx).

6. Risks and mitigations

• Dune outage / API change: mirror + walk-back means valuation degrades gracefully (≤6h-stale coherent bars, then honest nulls). The live tier (Kyber) is independent. No emergency DeFiLlama fallback is kept in the mark pipeline — deliberate; a stale coherent bar beats a fresh incoherent ratio.
• Dune restates history: the mirror freezes what we ingested (stable audit trail); fetch-through never overwrites an existing bar.
• Credits blowout: the original single prices.minute month-window query cost ~110-170 credits/month (~100x the estimate) — replaced by the HOURLY query (query 8033816: ~16 credits/yr preload, ~0.25/6h sync ⇒ ~30/mo steady) and a narrow EXACT-MINUTES query (8033818) reserved for ≤6h flow-true-up clusters. Budget ~30-60/mo vs the 2,500 quota. Two structural guards: syncBars CLAMPS any window > 48h to the most recent 48h (a stale-table catch-up goes through the bulk tool, never one giant cron execution), and a per-process CREDIT GUARD (DUNE_MAX_CREDITS_PER_RUN, default 50) stops issuing once reached. Fetch-throughs are logged — alert if > 20/day (a coverage gap; fix the tracked set instead).
• Depegs: basis moves fast during one; entries get minute precision, stored series keeps 6h granularity (same as today), Kyber remains the real-time surface. No regression, documented expectation.
• Chart re-leveling surprise (Phase D): communicated in PR + docs; parity report is the guard.
• Worktree/agent execution gotchas (for the coordinator): real node_modules via cp -cR (kill nested symlink, rm .next), node v20 absolute paths, staging DB via SSH tunnel only (NEVER prod from a dev server), client-rendered UI is verified by CDP screenshot, kill dev servers before psql cleanup.

7. Out of scope

• DeFiLlama usage outside the mark pipeline (token-yield APY sources, fluid_ll_apy TVL, DEX HQ, chain-level metrics).
• Kyber live tier mechanics, redemption-band constants, AGGREGATOR_REGISTRY.
• PT valuation (M6/M11/M12 pull-to-par — on-chain TWAP, already coherent).
• New asset coverage (LBTC/eBTC/weETHs/PST) beyond adding DAI/sDAI rows.
• The carries cum-return methodology (issue #271 reconciliation).