Data pipeline & refreshers

How data gets into creddit, and how it stays fresh.

The read-only-app principle

The Next.js app never writes data and never reads on-chain at request time for time-series. Pages are Server Components that read Postgres (schema onchain_credit) through a typed query() wrapper, plus a small amount of just-in-time RPC for "now"-only values. Everything time-series is produced ahead of time by off-chain jobs in scripts/ that read on-chain state (and a few external APIs) and upsert into onchain_credit. So:

All ingestion lives in scripts/refreshers/*.ts, driven by scripts/refresh-*.ts cron entrypoints.
The app is a pure reader of the DB. If a number looks stale, the question is always "did the refresher run, and did the page re-prerender", never "is the app computing it wrong live".
Every venue is measured with the same APY convention: the realised ratio of an on-chain compounding index between two blocks, annualised by the actual elapsed time (annualizeRatio in src/lib/data/apy.ts). Never average per-snapshot annualised rates. The canonical write-up is in Metrics: how & why.

Where the data comes from

Source	Helper	Used for
Ethereum RPC (current state)	`src/lib/data/rpc.ts`, `ETHEREUM_RPC_URL` (default `ethereum-rpc.publicnode.com`)	live `eth_call`, `eth_getStorageAt`
Ethereum RPC (archive)	`src/lib/data/rpc.ts`, `ETHEREUM_ARCHIVE_RPC_URL` (default `eth.drpc.org`)	historical-block reads for trailing-ratio APYs; block-by-timestamp FALLBACK
Batched RPC	`src/lib/data/rpc-batch.ts`	refresher-scale Multicall3 + chunked `eth_getLogs` with retry
DefiLlama Coins API	`src/lib/data/prices.ts`, `src/lib/data/llama-prices.ts`, `rpc.ts`	USD prices + token decimals (historical endpoint also serves "now"); block-by-timestamp
NY Fed reference-rates API	inline in `refreshers/sofr-rates.ts`	SOFR + compounded averages + the SOFR index
Morpho Blue GraphQL	inline (`blue-api.morpho.org/graphql`)	curator-vault fee / allocation, market collateral-USD
Euler Goldsky subgraph	inline in `refreshers/curator-vault-state.ts`	Euler vault state (partial)
Fluid public API	adapters under `src/lib/data/adapters/`	Fluid per-vault collateral exposure attribution
`creddit_indexer_fluid_dex_pool` (rindexer)	SQL join in `refreshers/fluid-dex.ts`	DEX swap volume aggregation

In production both RPC vars point at Alchemy; the publicnode / dRPC URLs in code are the unset-env dev fallbacks.

Block-by-timestamp is the pipeline's most load-bearing dependency, and it is not the RPC. Every 6h refresher must resolve its snapshot block before it can read anything, and that lookup goes to DeFiLlama's Coins API (free, unauthenticated). On 2026-06-25 00:00 UTC it returned HTTP 500; five of the six refreshers that call it (fluid-ll, token-yields, aave-v3, sparklend, morpho) threw and the tick wrote nothing, while the paid archive RPC was healthy throughout. blockByTimestamp now falls back to a binary search over the archive RPC, with a process-level circuit breaker (re-probing Llama periodically so a long backfill self-heals) so one failure does not make every later lookup re-pay the ~15s retry ladder.
The fallback resolves the same block the primary does: Llama returns the first block at or after the queried second (not "at or before", as the code claimed for a long time), and the bisect matches it exactly, verified against the live chain. Parity is the point: every row already in the database was anchored by Llama, so a fallback that rounded the other way would anchor ~12s earlier and read subtly different on-chain state than a live tick, invisibly.

Scheduled refreshers

All crons run on the Hetzner box (root@dexhq.io), in UTC, via the scripts/run-cron.sh wrapper. The verified server crontab:

Cron (UTC)	Entrypoint	Writes (`onchain_credit.*`)	Upstream	How it works
`0 /6 * *`	`refresh-assets.ts`	see sub-refreshers below	mixed	Orchestrator: runs the ten per-asset refreshers in order, catching per-asset errors so one bad asset never blocks the rest.
`15 /6 * *`	`refresh-vault-capacity.ts`	`vault_capacity`	on-chain RPC + DefiLlama + Morpho Blue API	Per-strategy borrow/supply-cap headroom + total supplied/borrowed USD. Aave/Spark static governance caps (`getReserveCaps`); Fluid dynamic Liquidity-Layer caps (auto-expanding to `maxBorrowLimit`); Morpho Blue markets have no caps, so `market(id)` deposits/borrows are read on-chain and the deposits are stored as the cap (headroom = available liquidity), with posted-collateral USD from the Blue API (NULL on failure); registry-driven vault/carry set. Upserts only when values change (bumps `changed_at`), else touches `last_checked_at`.
`30 /6 * *`	`refresh-collateral-exposure.ts`	`market_collateral_exposure` (basis `fluid_borrow`), `market_risk_current`	Fluid public API + on-chain storage reads	Fluid "underwritten capital": per collateral asset, how much supplied stablecoin is borrowed against it across isolated vaults (smart-debt legs sized by vault debt shares x pool per-share amounts). Plus a tick-based risk layer read straight from the vaults' own tick/branch storage (`readFromStorage` via Multicall3), distance-to-liquidation buckets, 100% coverage by construction.
`45 /6 * *`	`refresh-lending-positions.ts`	`lending_reserves`, `lending_borrowers`, `chain_scan_cursors`, `lending_positions_current`, `market_collateral_exposure` (basis `position_collateral`), `market_risk_current`	on-chain RPC + DefiLlama	Aave v3 / SparkLend account-level positions. Cursor-scans variableDebt mint/burn logs to maintain a borrower registry, multicalls current debt for the candidate set, reads the top-N borrowers' collateral (`getUserAccountData` + config bitmask + per-reserve balances, all pinned to one anchor block), attributes each account's stablecoin debt to its collateral pro-rata so slices sum to borrowed dollars. First run does the full log backfill (to 2023) and can take tens of minutes; later runs scan only since the stored cursor. A failed inner read is skipped, never written as zero.
`50 /6 * *`	`refresh-portfolio.ts`	`portfolio_position_snapshots`, `portfolio_flow_events`, `chain_scan_cursors`	on-chain RPC + Dune mirror (`token_price_bars`) + DefiLlama (block-by-ts only)	Read-only portfolio spine + flow ledger (WS4). MARKET marks come from the Dune price mirror (same-bar division); DeFiLlama is no longer a mark source (Milestone C), only the block-by-timestamp lookup. Snapshots every eligible registered wallet's legs across all six venue readers at one anchor block, valued in both marks, keyed on the aligned 6h window (upsert -> a same-window re-run is idempotent); cursor-scans the position-token Transfer streams + protocol events since the last cursor, filtered to the flow-scan wallet set (wider than the snapshot set; see below), and upserts flows. Registration backfills moved to the minutely drain cron below. See the portfolio subsection below.
`* * * * *`	`drain-portfolio-backfills.ts`	`portfolio_position_snapshots`, `portfolio_flow_events`, `portfolio_backfill_state`	archive RPC + DefiLlama	WS5 registration-backfill queue drain: FIFO, one wallet at a time (claim -> archive-routed child -> next), until the queue is empty or the per-invocation cap (`PORTFOLIO_BACKFILL_MAX`, default 10). Quiet no-op when the queue is empty. Stale-`running` reaper + attempts budget run every invocation. A failed child is retried once then parked (`MAX_BACKFILL_ATTEMPTS = 2`), and EVERY failure exits 2 to page the WS8 cron alert, naming the wallet. A signup's "History syncing" clears ~a minute after sign-in. The child stamps a live reconstruction cursor (`progress_done/total/started_at`, migration `060`, best-effort) after each daily grid point; `/api/portfolio/summary` derives the `/portfolio` building-chart progress bar + measured-rate ETA from it while the row is `running`. See Registration backfill.
`20 3 * * *`	`refresh-morpho-universe.ts`	`morpho_market_registry` (`status='universe'` rows), `chain_scan_cursors`	archive RPC	Daily. Exhaustive Morpho Blue market-universe ingestion (T4 §3.5): a cursor-driven `CreateMarket` log scan that keeps EVERY mainnet market in the registry as a `universe` row (loan/collateral token + on-chain decimals), so the portfolio loader can qualify any market. First run walks from the Morpho Blue deploy (`MORPHO_UNIVERSE_FROM_BLOCK` overrides for the deep backfill); steady-state scans only new blocks. Inert to the curated machinery (see database.md). Bounded per-tick by the T4 §3.6 discovery intersection, so the exhaustive universe never costs a per-tick read of thousands of markets.
`40 3 * * *`	`refresh-metamorpho-factory.ts`	`metamorpho_vault_registry` (`status='universe'` rows), `chain_scan_cursors`	archive RPC	Daily. MetaMorpho factory vault-universe ingestion (T5 §3.5): a cursor-driven `CreateMetaMorpho` log scan over BOTH factories (v1.0 `0xa9c3…1101` + v1.1 `0x1897…5c24`, identical event) that keeps EVERY MetaMorpho vault in the registry as a `universe` row (on-chain-verified asset + share/asset decimals), so the portfolio erc4626 loader values a wallet's deposit in ANY MetaMorpho vault as a Money market fund (role `curator`), not just the hand-curated Repo-lending subset. First run walks from the v1.0 deploy (`METAMORPHO_FACTORY_FROM_BLOCK` overrides); steady-state scans only new blocks. This is the PORTFOLIO universe ONLY — `curator-vaults.ts` / the Repo lending page are NOT widened. Bounded per-tick by the same T4 §3.6 discovery intersection.
`/10 * * *`	`refresh-portfolio-discovery.ts`	`portfolio_wallet_index`, `chain_scan_cursors`	on-chain RPC	Every 10 min. T4 §3.6 discovery enrollment drain: enrolls eligible wallets that have no FRESH completeness certificate (a one-time FULL-universe current read per wallet that indexes its current Morpho/erc4626 holdings + stamps `accounts.discovery_scanned_`), capped `PORTFOLIO_ENROLL_MAX` (default 10). Selection is freshness-based, not presence-based* (coverage hardening): missing, older than the accounts row, or past the 18h staleness horizon. That is the repair half of the freshness gate — a demoted wallet is re-certified within one run instead of paying the full-universe read tax indefinitely, and it is what would have repaired the incident wallet, whose stale stamp existed and so was skipped by the old "no watermark at all" rule forever. Un-scanned wallets force the 6h batch to a full-universe read (the fail-open), so draining them is what makes the discovery intersection ENGAGE for new signups; quiet no-op once every eligible wallet is watermarked (the migration-050 seed watermarks all historically-active wallets, so this drains only genuinely new signups). Best-effort per wallet: a failed read never sets a watermark (retried next run).
`30 4 * * 0`	`refresh-portfolio-reconcile.ts`	`portfolio_wallet_index`, `chain_scan_cursors`	on-chain RPC	Weekly (Sun). T5 §3.6 discovery reconciliation SAFETY NET: reads each SCANNED wallet against the COMPLETE universe (not the discovery-bounded read) and adds any currently-held Morpho/erc4626 membership the index MISSED, raising a WS8 drift alert. Bounds a discovery bug to "found within a week + alert", never "silently wrong PnL". Wallets processed least-recently-reconciled first (`accounts.discovery_scanned_at` LRU; ported off the `portfolio:discover:%` scope rows, which a later cleanup migration deletes — selecting from them would have silently emptied this sweep and killed the backstop with no error anywhere), capped `PORTFOLIO_RECONCILE_MAX` (default 50), so coverage rotates across all scanned wallets over successive weeks. Carries a starvation tripwire: eligible wallets present but an empty batch prints a `[fail] STARVED` line and exits non-zero, so "the backstop checked nothing" can never read like a healthy no-op. It also prints one `[fail] DIVERGENCE wallet=… venue=… key=… (repaired)` line per missed membership and repairs dangling links (an `account_wallets` row whose wallet has no `accounts` row, so since 056 the pipeline can write nothing for it at all: re-mints the shadow account via `trackedAccountUpsert` and requeues its backfill). Any of the three ends the run non-zero, because a run-cron alert needs both a non-zero exit and a grep-matching line. Steady-state finds ZERO AGED drift; a position the byproduct has not reached yet is repaired quietly rather than paged, which is what keeps a brand-new position from becoming a weekly false alarm. Its cohort is the widened FLOW-SCAN population, so a briefly-unlinked wallet — which keeps a fresh certificate across the gap and is therefore bounded on re-add — is included.
`30 3 * * 1`	`refresh-vault-risk.ts`	`vault_risk_params`	on-chain RPC	Weekly. Per-strategy max-LTV + liquidation threshold (Fluid `getVaultVariables2Raw` bit-unpack; Aave/Spark self-discover the live e-mode category then read its collateral config). LTV/LT move on a governance timescale, so weekly is plenty. Upserts only on change with a `changed_at` audit.
`30 4 * * 1`	`sync-portfolio-tokens.ts`	none in propose mode (`--approve` inserts `portfolio_tokens`)	DefiLlama stablecoins API + on-chain RPC	Weekly. Portfolio token-registry sync (T6). PROPOSE + ALERT only, never mutates on the cron: ranks the DefiLlama top-20 mainnet stablecoins (by Ethereum circulating), diffs them against `portfolio_tokens`, and asserts (a) every `assets` profile ticker has a registry row and (b) every wallet-tracked `variable_rate` row resolves a redemption rate (a `JIT_RATE_GETTERS` entry OR a `token_yield_apy.share_rate` row). Ranking drift, a profile gap, or an unresolved rate source each fire a WS8 alert. A human applies an addition with `sync-portfolio-tokens.ts --approve <SYMBOL>` (resolves the mainnet address from the DefiLlama detail endpoint + reads decimals on-chain, inserts an idle par row); it never retires a row. See the Registry sync subsection below.
`0 13 * * 1-5`	`refresh-sofr.ts`	`sofr_rates`	NY Fed	Weekdays at 13:00 UTC (NY Fed publishes ~08:00 ET). Pulls the latest ~30 business days of SOFR + compounded 30d/90d/180d averages + the SOFR index and upserts by date (generous overlap captures late revisions).
`10 4 * * 1`	`chat-retention.ts`	`chat_conversations`, `chat_messages` (cascade), `chat_usage`	— (DB only)	Weekly (Mon 04:10). Prunes the app-state chat tail so it does not append forever (audit B6): deletes conversations with no activity in 365 days (their messages cascade via the migration 038 FK) and `chat_usage` rows older than 400 days, both in one transaction. Child/leaf deletes only — the migration 056 `uid` FKs point at `accounts`, so pruning a conversation or usage row never cascades into an account or its portfolio history. Portfolio history is RETAINED IN FULL by design (see database.md → Retention). Manual crontab add, prod only (staging carries no crons).
`15 2 * * *`	`ops/backup-creddit.sh`	(DB dump, not a table)	—	Daily Postgres backup of the `creddit` DB at 02:15 UTC. Server-only script (not committed in this repo).
hourly	(disk-usage alert)	—	—	Operational alert, not a data job.

Sub-refreshers inside `refresh-assets.ts` (the 6h "assets" job)

refresh-assets.ts is an orchestrator (scripts/refresh-assets.ts) that runs these modules in order. Each is independently importable and several have a *ForSnapshot export the backfills reuse.

Module	Writes (`onchain_credit.*`)	Upstream	How it works
`refreshers/fluid-ll.ts`	`fluid_ll_apy`	on-chain RPC	Fluid Liquidity Layer supply/borrow exchange prices; APY = annualised realised index ratio over the 6h window (and a 24h-trailing variant for smoother long charts).
`refreshers/fluid-dex.ts`	`fluid_dex_apy`	rindexer swaps + DefiLlama	Sums swap volume from the indexer over the 6h window, reads fee rate + smart-col/smart-debt reserves via DexResolver, prices in USD, computes `fee_apy_usd = fee_window_usd x 1460 / (tvl_col + tvl_debt)`, LP-net of the protocol revenue cut. Also reads `FluidDexResolver.getDexState(pool)` at `block_at_snapshot` for the per-1e18-share pot content + the pool price/centre pair (6 raw columns, migration `047`) — the inputs to the REALISED cum-return series. That read is the only NON-FATAL one in the pool's Pass-1: it is caught individually so a resolver miss degrades to NULL columns instead of costing the pool its fee/TVL row, and the upsert `COALESCE`s the six columns so a later failed re-run cannot wipe values already landed.
`refreshers/token-yields.ts`	`token_yield_apy`	archive RPC	Share-to-asset rate per yield wrapper at the snapshot block (`convertToAssets(1e18)` for ERC-4626, `getStETHByWstETH` for wstETH, `getRate()` for weETH/ezETH, `exchangeRate()` for cbETH (the Coinbase-published rate), NAVConsumer for reUSD, external `rateSource` for osETH, etc). Stores `share_rate`, 24h `supply_apy`, 30d `apy_30d`, and `total_supply`. Registry-driven: every curator vault in `src/data/curator-vaults.ts` and every Fluid fToken in `src/data/fluid-ftokens.ts` is auto-included (an fToken needs a row here so its /portfolio leg has a quoted APY). A failed read writes no row, never a zero — so a snapshot can be MISSING for a token that is otherwise covered, and every reader must treat that as a gap rather than a 0% yield (`wrapperRowPresent` in `carries-table.ts`; see metrics.md, "A missing wrapper row is a gap, never a zero"). This is a real failure mode, not a theoretical one: the `2026-06-25 00:00 UTC` run wrote only 4 of 78 tokens when the DefiLlama block-by-timestamp lookup returned HTTP 500 (see the block-by-timestamp note under "Where the data comes from").
`refreshers/token-basis.ts`	`token_basis`, `token_price_bars`	Dune mirror + DB	`basis = market_price_usd / redemption_value_usd - 1`, where redemption value = (latest `share_rate` from `token_yield_apy`, or 1 for par tokens) x numeraire (USD / ETH / BTC). Market + ETH/BTC numeraire quotes come from the Dune price MIRROR (`getBarSeriesAt(tsSec)` over `token_price_bars`), NOT DeFiLlama. The same-bar rule, via newest-common-bar pairing: for an ETH/BTC-numeraire token the reader returns each leg's whole covering-bar window (all bars in `[align(ts) − 48h, align(ts)]`, `BAR_STALE_HARD`) and `computeBasisFromBars` divides the token and its numeraire at their NEWEST COMMON `bar_ts`. It never divides a token bar by an independently walked-back numeraire bar — that leaks the cross-bar ETH/BTC move straight into the basis (the ±30-50bps of cross-vintage noise this refactor deletes). No common bar in the window counts the token `failed` (never fabricated, never a DeFiLlama fallback); the run logs how many tokens paired at a >1h-old common bar. A snapshot where ALL market-class tokens fail — meaning no bar within the 48h ceiling, a genuinely DEAD mirror rather than one merely lagging past the 6h soft threshold (M20) — prints a literal `[fail]` line and exits non-zero. Pinned tokens (`basisClass: "pinned"`, e.g. sUSDS) skip the mirror bar entirely and write `market_price_usd := redemption_value_usd`, `basis = 0` go-forward (M19: an arbitrage-closed deviation is feed noise). Runs after token-yields so the current tick's `share_rate` is available. Prepends the best-effort HOURLY mirror sync (`syncBars(mirrorTrackedTokens(), MAX(bar_ts), now)` → `token_price_bars`, ~0.25 credits; a >48h gap is clamped to 48h and the older gap is caught up by the bulk tool) that supplies the very bars this run then consumes. The computation is the shared `computeBasisFromBars` (`src/lib/data/basis-compute.ts`), reused by the shadow backfill (and by Milestone C's `priceInBookFromMirror` via the exported `pairAtNewestCommonBar`) so the paths cannot drift. See Token price bars (Dune mirror) and Rebuilding token_basis from the mirror.
`refreshers/aave-v3.ts`	`aave_v3_reserve_apy`	on-chain RPC	Aave v3 `liquidityIndex` / `variableBorrowIndex` (RAY-scaled) annualised over 6h; also stores deposited + available liquidity.
`refreshers/sparklend.ts`	`sparklend_reserve_apy`	on-chain RPC	Same method as Aave against SparkLend's pool (separate table to avoid `(snapshot_ts, token_address)` collisions on shared tokens).
`refreshers/morpho.ts`	`morpho_market_apy`, `market_collateral_exposure` (basis `isolated_market`)	on-chain RPC + Morpho Blue API	Active Morpho Blue markets from `morpho_market_registry` (both tracks). Supply + borrow share rates from `Morpho.market(id)` (`borrow_share_rate` added migration 041), realised trailing-24h APYs, spot borrow APY from the AdaptiveCurve IRM. Market params read via on-chain `idToMarketParams` (exact 1e18 lltv). Exposure rows for repo-track markets only. Live runs fetch posted-collateral USD from the Blue API best-effort.
`refreshers/yield-token-assets.ts`	`assets`	DB rollup	Rolls `token_yield_apy` history into the Asset-profiles table: `current_apy` (latest `apy_30d`), 1M/YTD/1Y cumulative returns, productive vs underlying market cap.
`refreshers/curator-vault-state.ts`	`curator_vault_state`	Morpho Blue API + Euler subgraph	Per money-market curator vault: fee, net APY, total assets, and per-market allocation (collateral / supplyUsd / LLTV). Euler allocation is partial; those vaults get description + return chart but no allocation block yet.
`refreshers/pendle-markets.ts`	`pendle_markets`, `pendle_market_state`	Pendle hosted API + on-chain RPC	Registry sync (new markets from the API, verified on-chain via `readTokens()`/`expiry()` before insert; deterministic `active → matured` flip from `maturity_ts`, mirrored onto matured term carries in `carry_registry`) + one multicall snapshot per run pinned to a single block: `readState().lastLnImpliedRate` → `implied_apy`, `PendlePYLpOracle` 900s TWAP `pt_to_asset_rate`/`pt_to_sy_rate` (the oracle reverts rather than serve a degraded window; reverted reads store NULL), `SY.exchangeRate()`, pool sizes. `liquidity_usd` is a display aid from the same API response — the RPC snapshot never depends on the API being up; `underlying_apy` is backfill-only (the active-list endpoint does not serve it), the realised figure derives from `sy_exchange_rate`. One-shot history: `scripts/backfill-pendle-history.ts` (per-market daily series from the API, `basis='pendle_api'`).

`refresh-portfolio.ts` (the 6h portfolio job, WS4)

Two jobs run per tick (entrypoint scripts/refresh-portfolio.ts, logic scripts/refreshers/portfolio.ts, shared read/valuation modules in src/lib/portfolio/); both share ONE anchor block. (Registration backfills are NOT part of this tick: the minutely drain cron owns the WS5 queue, see below.)

Valuation spine -> portfolio_position_snapshots. Selects eligible wallets (accounts LEFT JOIN portfolio_backfill_state, snapshot when status IS NULL OR status NOT IN ('queued','running') -- a chat-seeded account has no state row and must not be skipped; running is excluded so the WS5 backfill and this cron never write the same wallet's window at once). Since migration 048 an accounts row alone no longer earns a snapshot: a row may be a SHADOW account, minted only so a wallet somebody TRACKS on /portfolio has a FK target for portfolio_backfill_state. An account is eligible when it is a real signed-in user (last_seen_at IS NOT NULL, stamped only by the SIWE verify path) OR some account's account_wallets list still references it. A shadow is therefore snapshotted while at least one account tracks it and drops out of the pass the moment nobody does — which is what makes un-tracking a wallet free at delete time (the link is removed, no history is pruned; re-adding requeues a gap PATCH that preserves the pre-gap history, for a gap longer than 12h; a SHORTER gap is instead covered by the widened FLOW-SCAN population, since the requeue guard deliberately no-ops there — see "The flow-scan population is wider than the snapshot population" below). The 048 self-row seed is load-bearing here: the 042 chat-seeded accounts carry last_seen_at = NULL (only a sign-in stamps it), so without their seeded self-row they would have silently dropped out of this pass. Runs all six venue readers (readAllPositions, venue-isolated) at the anchor block, values each leg in both marks (buildSnapshotRows), and upserts keyed on the aligned 6h window. snapshot_ts is ALWAYS the aligned window; block_number is the anchor. The upsert makes a same-window re-run idempotent. Rate resolution uses mode now (the wrapper's own on-chain getter at the anchor block, same-block as the venue index); the WS5 archive backfill uses mode history, which tries the SAME on-chain getter at the historical grid block first (read via the archive RPC, so it is exact per-block and matches a coinciding live snapshot) and falls back to the block-anchored token_yield_apy.share_rate series when a wrapper has no getter. A now ERC-4626 convertToAssets(1e18) getter scales the raw read to the human assets-per-share rate by 10^(assetDecimals + 18 − shareDecimals) (= 18 for every covered vault, including the 6-dec-share syrupUSDC/syrupUSDT: 6 + 18 − 6 = 18); it is NOT the assetDecimals unless the share is 18-dec, so it agrees with the history share_rate (token-yields' default divisor 18). index_raw stores the BARE venue index; the value columns already fold in the accounting-asset->book rate, and WS6 recomposes the index for exact ratio math.
Flow ledger -> portfolio_flow_events. Cursor-scans, all filtered to the registered wallet set at the RPC (eth_getLogs OR-array on the owner topic), stopping CURSOR_SAFETY_BLOCKS = 64 below the anchor (replicated from lending-positions.ts):
- Position-token Transfer streams (aTokens, variableDebtTokens, ERC-4626 shares, PTs), one combined address-array scan, two passes (wallet at topic1 = out/burn, at topic2 = in/mint). A Transfer from 0x0 is a mint (deposit / borrow / acquire), to 0x0 a burn (withdraw / repay / redeem), between non-zero addresses a position-token move (transfer_in/out). variableDebtTokens only ever mint from 0x0 (borrow) / burn to 0x0 (repay). NB the Aave/Spark aToken/vToken Transfer value is the UNDERLYING amount (verified against aave-v3-origin AToken._mintScaled/_burnScaled), but it BUNDLES interest accrued since the user's last touch, so the flow VALUE is approximate at that level (a touch also emits a near-zero interest-mint Transfer that reads as a tiny deposit); this never corrupts index-leg yield, which pnl.ts attributes from the composed-index RATIO, ignoring flows.
- Protocol events: Aave/Spark Pool LiquidationCall (M4; borrower at topic3) and the seven Morpho Blue singleton events (the only observable Morpho flow surface; owner at topic3 for Supply/Repay/SupplyCollateral/Liquidate, at topic2 for Withdraw/Borrow/WithdrawCollateral -- verified against the deployed Morpho Blue ABI). A liquidation row carries BOTH the seized collateral (asset / amount_raw) and the debt-repayment leg (debtAsset / debtToCover for Aave, loanToken / repaidAssets for Morpho), so its value can be booked as the equity destroyed (M4), not the full seizure.
- Fluid events (FWS3, src/lib/portfolio/fluid-flows.ts): a chain-wide LogOperate + LogLiquidate scan by topic0 only (both events have ZERO indexed params — user/liquidator is msg.sender/a DSA, never an owner, D6 — so they cannot be wallet- or vault-filtered at the node; decode ALL, filter in-process to the tracked NFT set), plus a wallet-filterable factory ERC-721 Transfer scan. The tracked NFT set is derived PER RUN with no new table: (a) distinct fluid nftIds already in the snapshot table, (b) positionsNftIdOfUser at the anchor, (c) factory Mint/Transfer for the wallets in the window — so an NFT acquired or shed mid-window is still matched. A signed colAmt/debtAmt becomes a deposit/withdraw (col) or borrow/repay (debt) leg; a T1/normal leg's amount is the leg token's own units (wei-identical to the same-tx ERC-20 transfer), a SMART leg's 1e18 DEX shares are decomposed into their pool tokens via getDexState AT THE FLOW BLOCK (composition drifts). A user↔user NFT transfer moves the whole levered position (M16); mints, burns and self-transfers are not value flows (the same-tx LogOperate carries the value), and neither is the OPENING transfer of a position born in that tx (M17, bookableNftTransfers — the zapper mints the NFT to itself, operates, then hands it to the user in one tx, so its from is the zapper rather than 0x0 and M16 would book the whole position a second time on top of the operate rows; a PRE-EXISTING position transferred in a tx that also operates it is a real hand-over and keeps its M16 rows). State-diff liquidations (M15, below). The decoded events are APPENDED to the fluid_event_log cache (D7).
- Flows are valued at their own block in both marks (block timestamps + ERC-4626 flow-block indexes read from the archive RPC; MARKET marks come from the Dune price MIRROR at each flow's OWN minute — the newest common token_price_bars bar ≤ the flow ts, priceInBookFromMirror, dividing the token and its numeraire at the SAME bar — NOT a 6h-bucket DeFiLlama call; a liquidation's debt asset is priced the same way, to net it out of the loss). Before valuing, when the Dune keys are present, syncExactMinutes trues the exact 5-min bars for the flow assets (+ WETH; the BTC reference rides the query) at the distinct flow minutes up from Dune in one credit-guarded execution per ≤6h cluster; keyless or a failed true-up degrades silently to the standing hourly bars (entry marks are minute-precise where the true-up succeeded, hourly-common-bar otherwise, both coherent by construction — see metrics.md M5.1/M18). A failed price/rate read leaves that mark NULL (M9), never zero. TWO honesty rules live in the pure valueDetectedFlow: (a) a liquidation books the equity destroyed = value(seized collateral) − value(debt covered) per mark, not the full seizure (a same-book carry only; a cross-book position, never charted, keeps the seizure value); (b) an ERC-4626 shares flow whose convertToAssets(1e18) failed to read at the flow block is left UNVALUED (both marks + amount_underlying NULL, amount_raw kept), never descaled through the null-index branch, which would fabricate a value off by 10^(shareDecimals − assetDecimals). Separately, an Aave/Spark liquidation's collateral aToken / debt vToken burns + fee (recorded as same-tx withdraw/repay/transfer_out) are seizure mechanics that pnl.ts excludes from flow-netting (see metrics.md M4), so a seizure never nets as a phantom external flow.
Cursor scopes in chain_scan_cursors: portfolio:transfers (combined position-token streams), portfolio:events:morpho-blue, portfolio:events:aave, portfolio:events:sparklend, and (FWS3) portfolio:events:fluid-operate + portfolio:transfers:fluid-nft (scanned in lockstep over the combined window). The flow-events PK (chain_id, wallet, tx_hash, log_index, leg) makes re-scanning a range idempotent (no duplicate rows); leg (migration 044) lets one Fluid LogOperate write two rows (col + debt) and a smart leg two pool-token rows per side under one log_index. First run with no cursor scans a modest catch-up window (PORTFOLIO_FROM_BLOCK overrides the start); the deep per-account history is WS5's backfill job.
Settling the JIT's provisional tier. Every row this cron writes is settled by construction (the scan stops 64 blocks below the anchor), so over the wallets and blocks it derived authoritatively its rows are the only truth standing. Once the flow pass is complete it therefore DELETEs the basis='provisional' rows (the JIT's reorg-exposed tail, see the JIT section below) in exactly that region: a provisional row on the same primary key was already promoted to live by the tick's upsert, and one that is NOT re-derived is a phantom left by a reorg that moved or dropped its log. Scope is the whole point — a delete that reaches past what the tick re-derived removes a real flow nothing will re-write, which breaks M3 in the direction that manufactures yield (a deposit gone while its balance stands in the snapshot spine). So it is bounded three ways: by the tick's own flow-scan WALLET list, frozen when the scans took it (a wallet whose registration backfill finishes mid-tick was excluded from the scans and must not be swept minutes later); ABOVE by the LOWEST block any surface derived this tick, not the anchor-64 line blindly (in PORTFOLIO_LEDGER_MODE=on the ledger-owned surfaces stop at the ingester tip, and the residual window simply settles on the next tick); and BELOW by the lowest from-block any scope scanned, so a wallet that only enters the scan population now — its backfill just released it, and the replay re-lays only up to its own certify block — keeps the JIT rows the cursors have already passed. A tick whose cursors were all already at their bound re-derived nothing and sweeps nothing. Placing the sweep AFTER the pass rather than inside each scope's transaction is deliberate: a scope that throws aborts the tick before the delete runs, so a provisional row is never retired on the strength of a replacement that failed to land (the reverse ordering leaves a real flow missing from every read until the next tick, 6h later; the price is that a reorg-relocated phantom can be visible beside its replacement for the rest of one tick). The backfill's windowed deletes are basis-blind by design and already clear provisional rows inside the ranges they re-derive. Writer serialisation (the advisory lock): with backfills running on their own minutely schedule, a backfill can be mid-flight DURING this tick. The eligible-wallet exclusion covers the common case, but two residual races remain: an account (chat-seeded, no state row) selected as eligible at the start of a tick can be enqueued+claimed mid-tick, and a JIT page-load can persist flows mid-backfill. All writers of the two history tables therefore take ONE transaction-scoped advisory lock (src/lib/portfolio/write-lock.ts, pg_advisory_xact_lock(hashtextextended('onchain_credit.portfolio_history_writers', 0))): the tick's snapshot + flow writers and the backfill's windowed delete+insert take it blocking (every lock-holding transaction is pure DB work — reads and valuation happen before BEGIN — so worst-case blocking is sub-second), and the JIT flow persist takes the TRY variant and SKIPS when busy (a page load never queues; the mini-scan re-detects next load and the cursor re-scan catches up regardless). Without the lock, an upsert landing between a backfill's DELETE and INSERT aborts that backfill on a PK collision.

The six venue readers (src/lib/portfolio/readers/*): aave, sparklend, morpho-blue, erc4626, pendle, and fluid (FWS2). The erc4626 universe is PORTFOLIO_ERC4626_VAULTS (src/data/erc4626-universe.ts) = the Morpho/Euler curator funds (src/data/curator-vaults.ts, generated) PLUS the Fluid Liquidity Layer fTokens (src/data/fluid-ftokens.ts). The fToken set is the FULL FluidLendingFactory.allTokens() enumeration (T4 §3.5): fUSDC / fUSDT / fGHO / fUSDtb (USD book), fsUSDS (USD book, sUSDS wrapper), fWETH (ETH book), fwstETH (ETH book, wstETH wrapper) — every based underlying, not only the USD ones, now that all three PnL books chart. Deduped by address (the vault:<addr> positionKey is part of the snapshot PK, so a duplicate entry would fail the write outright). Note the two Fluid shapes split across TWO readers: a Fluid vault position is an NFT (fluid), while a plain Fluid lending deposit has no NFT and is just an ERC-4626 share token, so it reads, flows, values and books through the generic erc4626 path (mint from 0x0 = deposit, burn to 0x0 = withdraw; accounting asset = asset() = the underlying, which buckets.ts books into its USD/ETH book — and every fToken underlying is already book-mapped WITH a redemption-rate path, par or a JIT wrapper getter, so none is skipped M9). The merged universe is used at all five portfolio call sites (reader binding, JIT mini-scan, backfill probe/replay, 6h cursor scan, leg name map); the Curator funds page reads bare CURATOR_VAULTS, so Fluid never appears there. Every reader is DB-free (its universe is injected) EXCEPT fluid, which needs no universe at all: FluidVaultResolver.positionsNftIdOfUser(wallet) lists a wallet's NFT ids and positionByNftId(id) returns ONE self-describing 109-word payload (its vault, type, both token pairs, exchange prices, accrued supply/borrow/dustBorrow), decoded with the published resolver ABI (fluid-abi.ts, the SAME tuple the capacity refresher reuses). Per NFT the reader emits: a NORMAL leg per side (index = the vault exchange price, M14, qty = normalAmount x 1e12 / exPrice, debt INCLUDES dustBorrow); or, for a SMART leg (T2/T4 col, T3/T4 debt), TWO reads per side (one per pool token, qty = shares x tokenPerShare / 1e18 from one getDexState per DEX per anchor, read at the block being valued because pool composition drifts, index = null -> accrual value). It branches on isSmartCol/isSmartDebt from the payload, NEVER on the exchange price (smart => exPrice == 1e12 is one-directional). Closed positions and any read whose decimals / getDexState fail are skipped (M9). D8 gate: FWS2 shipped FLUID_FLOW_COVERAGE = false (every Fluid leg held "Outside the yield book" with the coverage-pending reason); FWS3 landed the flow scanner and FLIPPED this ONE const to true, so the M1/M14 inclusion rules (same-book / cross-book, no e-mode gate) now decide and Fluid legs chart. The coverage-pending reason is retained in the type/labels for any older history that carries it.

Discovery intersection (T4 §3.6, src/lib/portfolio/discovery.ts). The Morpho and ERC-4626 universes are now EXHAUSTIVE (every mainnet market via morpho-universe.ts; the full fToken + curator + managed vault set, PLUS the exhaustive MetaMorpho factory universe via metamorpho-factory.ts — T5 §3.5 — assembled into reg.erc4626Universe by loadRegistries), so reading position()/balanceOf() over the whole universe every tick would grow without bound. Instead the cron INTERSECTS those two universes with portfolio_wallet_index (restrictToDiscovered, reusing the restrictRegistries/open-set override shape): the READER gets only the markets/vaults these wallets have touched, while the FULL exhaustive reg still feeds the flow scan (so it keeps discovering — once the registry is exhaustive, scanMorphoFlows's byId admits every market). The small universes (Aave/Spark, Pendle, Fluid) stay full-sweep. Discovery is a FREE byproduct of the existing flow scan: membershipsFromFlows turns the flow detections into index rows (no extra getLogs). A new wallet is ENROLLED by the /10min discovery drain (refresh-portfolio-discovery.ts → runEnrollmentDiscovery): a one-time FULL-universe strict current read (readDiscoveryPositionsOrThrow) finds exactly its current holdings and sets a watermark (the current read is COMPLETE for discovery — an event scan bounded to a coverage floor could miss a still-held position acquired before it, which the watermark would then mark scanned and the loader silently drop). Migration 050 seeds the index AND the watermark from existing snapshot/flow history. A wallet is SCANNED iff it has a watermark, not merely an index row: the incremental byproduct only adds the current window's markets, so an un-watermarked wallet (however many byproduct rows it has) is still read full-universe. Fail-open (M9-class): if the index is unreadable (pre-migration) OR any wallet in the batch is un-scanned, the read falls back to the FULL universe — a not-yet-discovered wallet is slow-but-correct, never bounded to empty; a scanned wallet that opens a brand-new position is picked up by the next tick's byproduct ("found a tick late", never mischarted). The bound is measured on read COUNT: a wallet's read scales with its touched markets (verified: an obscure non-curated WBTC/USDC position bounded a full universe to the wallet's 5 markets and still charted), so cron wall time stays flat as the universe grows. The watermark is never derived from a partial read. A watermark means "the index is COMPLETE for this wallet" and is what authorises BOUNDING every future read, so it is baked state — it must not come from readAllPositions, whose contract is to SWALLOW a venue's failure. Both enrollment and the weekly reconcile therefore read through readDiscoveryPositionsOrThrow (readAllPositionsSettled, throwing if ANY venue failed) BEFORE any index or watermark write: an incomplete read leaves no trace, the wallet stays un-scanned, keeps being read full-universe (the fail-open), and is retried next run. They also assertRegistriesComplete before reading at all, because the strict read closes only VENUE failures and a REGISTRY-level degradation is invisible to it: loadRegistries catches a loadFactoryVaults failure and silently falls back to the static erc4626 set (measured: 73 → 72 vaults, losing exactly the factory rows), so every venue then "succeeds" against a shrunken universe, a wallet's factory-vault membership is never seen, and the watermark is baked anyway — the same silent-bounding class, entered one level up. The reconcile is the worse of the two: it would report ZERO drift for a membership it never looked for AND bump the LRU stamp, rotating that still-blind wallet to the back of the weekly queue (weeks, at PORTFOLIO_RECONCILE_MAX=50). Both abort instead, write nothing, and exit non-zero so the cron-failure alert pages. This is why deployment.md orders the migrations before these crons. Otherwise a single Morpho RPC hiccup during enrollment would index a wallet with none of its Morpho memberships, stamp the watermark anyway, and silently drop those legs from every future bounded read — and the reconcile, the designated backstop for exactly that, would have read an empty "found" set, reported zero drift, bumped the watermark and LRU-rotated the still-blind wallet to the back of the queue. It cannot detect drift in a venue it failed to read, so it must not claim to have checked it. Reconciliation safety net (T5 §3.6, scripts/refreshers/portfolio-reconcile.ts, weekly): because the index BOUNDS a read, a discovery bug that missed a membership would silently drop a leg — so a low-cadence sweep reads each SCANNED wallet against the COMPLETE universe, adds any held membership the index missed, and raises a WS8 drift alert. A discovery bug therefore degrades to "found within a week + alert", never "silently wrong PnL" (verified: a wallet whose index was seeded MISSING a real MetaMorpho holding had it detected, added, and alerted).

Completeness certificates (coverage hardening). The paragraph above describes the design as shipped in T4/T5, where the "scanned" test was the PRESENCE of a chain_scan_cursors scope row. That is no longer true, and the reason is the 2026-07-21 coverage incident: migration 056 gave portfolio_wallet_index.wallet an ON DELETE CASCADE FK to accounts, so deleting an account purged a wallet's memberships while its scope row survived (no FK on a text scope). The wallet was re-added, the next tick saw "certified complete" over an EMPTY index, bounded the read to nothing, and a value-accrual leg's whole principal booked as a permanent phantom loss. Three changes close it, and the hardening plan has the full causal chain.

The certificate moved onto the accounts row (discovery_scanned_block / discovery_scanned_at, migration 061) — the FK parent whose deletion cascades the memberships away, so certificate and memberships now die together and the poisoned state is not representable. See Discovery certificate lifecycle. The scope row is dual-written for one release (rollback safety) and readers fall back to it whole-batch on a 42703, never per row.
Freshness, with demotion. A wallet is SCANNED iff its certificate exists AND is at least as new as accounts.created_at AND is younger than STALE_CERTIFICATE_MAX_SECONDS (18h = 3 ticks). The staleness horizon is the load-bearing half: comparing against created_at alone can never demote a FROZEN certificate, because created_at does not move. Anything else demotes to the full-universe read, and the /10min drain re-certifies. Staleness is a performance state, never a correctness state.
Earned advancement, per scope. The 6h tick now persists each scan scope's memberships INSIDE that scope, ordered scan → writeFlows → upsertWalletIndex → saveCursor, so an upsert failure holds that scope's cursor back and the next tick re-scans (flow writes are idempotent on the PK). The previous single best-effort upsert at the end of the run lost every scope's memberships whenever a LATER scope threw, while the certificate stayed intact — the incident's state reached through a different door. After all scopes complete, certificates advance for the tick's wallets only if the enumerated veto signals are clean: a degraded registry load (which silently SHRINKS the target universe while every venue reports success) and a failed membership upsert. "No exception was thrown" is explicitly not sufficient.

A re-add PATCHES the gap; it no longer destroys the history (Phase 7a). Until now a re-add was a destructive full delete plus an open-set replay, which erased not only positions that closed inside the gap but positions that closed BEFORE it while the wallet was tracked and live-captured. Now, when a wallet with a durable coverage anchor is requeued: the pre-gap history stays exactly as recorded, the gap is reconstructed as if tracking never stopped, and the positions reconstructed through the gap are those OPEN AT GAP START plus those TOUCHED DURING THE GAP — including ones that closed inside it, not "open now". First-time adds keep today's behaviour.

Five details carry the design:

The trigger is the durable anchor (portfolio_backfill_state.covered_through_*, migration 063), written only on a terminal done (or a committed gap-patch segment) and advanced by the 6h tick for every un-vetoed SNAPSHOTTED wallet — including one demoted to the full-universe read, whose snapshots are complete even when its discovery certificate is stale (advancing only the discovery-certified subset would FREEZE a demoted wallet's anchor while live 6h rows kept landing above it, and a later patch would re-lay that span at daily granularity over them). The trigger reads the anchor alone, never the live status: a re-add requeues done→queued and the drain claims queued→running before the child runs, so a status = 'done' gate would always fall through to the destructive fresh replay, and an out-of-budget resume would replay over its own committed segments. The anchor's lifecycle carries the provenance instead — it is CLEARED on empty (the prune deletes the history it certified) and kept intact on error and across the requeue/claim, so a crashed or resumed patch resumes from the durable anchor rather than fresh-replaying. "Stored snapshots exist" is deliberately NOT the trigger: a crashed first-time replay also leaves rows behind but no anchor, so it correctly fresh-replays.
The anchor stores the BLOCK, not just the timestamp. A live snapshot keys an aligned ts (18:00) but READS at the tick's real head block (~18:50 in the incident). Deriving the sweep's lower bound with blockByTimestamp(ts) would land below the block the tip was actually read at, so the sweep would re-detect flows already baked into the tip's balances and book a phantom move at the seam.
A separate grid. computeGapWindow places every point strictly ABOVE the anchor (first UTC midnight after the tip, then daily, then the 6h seam). Reusing computeBackfillWindow would day-floor the start, and three of the four tick phases are non-midnight, so grid[0] would collide with a preserved row on the snapshot PK — a deterministic crash for nearly every patch.
The group set is re-read from chain at the tip block, not taken from the stored tip, because a stored tip can itself be a partial snapshot (the incident's held 2 of 5 legs); a group the re-read finds but the stored tip lacks is logged as a partial-tip tripwire. Every group must resolve to a reader-universe entry or the patch aborts loudly — a group that cannot be read would be reconstructed as "held nothing" for the whole gap.
Segmented atomic writes. Each ~30-day segment commits its flows, its snapshots and the advanced anchor in ONE transaction, so a gap of any length makes durable progress, a re-run resumes a strictly smaller gap, and the fresh path's two-transaction crash hole (snapshots committed, crash before the flow write, flows then lost forever at a moved tip) does not exist here. Native-ETH flows (no Transfer logs) are the deltas between consecutive balance anchors, seeded per segment from the PREVIOUS segment's final anchor — not the loop-invariant tip — so a balance move in an early segment is booked once, not re-booked by every later segment. A segment also OWNS its (startTs, anchorTs] window for those synthetic native-ETH rows: it window-deletes them (matched by the reserved nativeEthDiffTxHash prefix) before re-writing its own, symmetric with the snapshot windowed delete and in the same transaction, so a live-captured diff row that a still-snapshot-eligible wallet booked at its 6H WINDOW ts above a FROZEN anchor (a parked-error span, whose anchor is deliberately kept but done-guarded from advancing, or the concern-1 demotion lag) is REPLACED by the patch's DAILY-grid row rather than persisting as a second synthetic PK for the same move (native ETH is par, so the yield curve is shielded, but net-capital in/out and entered-basis P&L would otherwise silently double; B5). REAL flows keep their pure upsert, since their real (tx_hash, log_index, leg) PK is idempotent under re-derivation and a real hash never carries the reserved prefix. When the patch COMPLETES it runs the same residual (nowBlock, head] tail sweep the fresh path does (full-universe, non-native, UPSERTED as ledger rows above the last snapshot seam): the patch held running for minutes while the 6h tick advanced the global cursor past that span, so a flow landing there would otherwise be missed forever.

empty also changes meaning: a wallet is pruned only when the gap-mode group set is empty AND no stored history exists, so a wallet whose positions all closed during the gap gets the patch and ends done rather than losing six months of chart for being empty on re-add day. floor_ts is unchanged (the original "tracked since" anchor is part of the preserved history), and multiple gaps compose — each re-add patches from the then-current anchor. Cross-account inheritance is deliberate: account B first-adding a wallet account A previously tracked inherits the preserved history and the patched gap; history is wallet-keyed by design, tracking is watch-only, and everything reconstructed is public chain data.

The flow-scan population is wider than the snapshot population (Phase 5). Snapshots are written for ELIGIBLE wallets; the flow scans run over eligible ∪ tracked ∪ snapshotted-in-48h. The hole this closes: a wallet unlinked and re-added within 12h skips the backfill requeue (correctly — the STALE_HISTORY_INTERVAL guard exists so the shared-wallet add case does not burn a 90-day archive replay), but during the gap it was not eligible, so it was not flow-scanned either, and the cursors advanced past those blocks for everyone else. The gap's flows and memberships were therefore missed permanently by the incremental path.

This is load-bearing, not belt-and-braces. Un-tracking deletes only the account_wallets row, and trackedAccountUpsert is ON CONFLICT DO NOTHING, so accounts.created_at does not move on re-add. Across a ≤12h gap the wallet's certificate was last advanced ≤12h ago against an 18h horizon, so it is still FRESH: the tick on re-add BOUNDS that wallet's read against its index. Nothing demotes it and nothing replays it. These gap memberships being present is the only thing keeping that bounded read truthful.

The TRACKED arm joins accounts (a dangling account_wallets link whose shadow account was deleted must not enter, or the flow write trips the 056 FK on portfolio_flow_events.wallet and aborts the tick) and excludes queued/running — without that exclusion its only marginal contribution over the eligible set would be exactly the mid-backfill wallets the eligible predicate deliberately holds out, so it would silently reverse a concurrency guarantee. The 48h arm is what actually bridges an unlink gap, since an unlinked wallet has no account_wallets row at all; migration 062 indexes (chain_id, snapshot_ts DESC) for it, because that predicate has no wallet term and the spine is retained in full. The union always contains the eligible set, so the scan can only widen, and it degrades to exactly the eligible set if the query fails.

Memberships written for a temporarily untracked wallet are harmless (FK-cascaded with the account, ignored while ineligible) and its certificate is untouched (only the eligible-and-fresh set advances). Known limit: native-ETH flows are derived from snapshot balance diffs rather than scanned, so they cannot widen — a native-ETH move inside an unlink gap is never booked (the ≤12h re-add skips the replay that would recover it). Native ETH is 'none' accrual so no yield is fabricated, but the wallet keeps a permanent yield-invariant residual for that amount.

The registration backfill now certifies what it learned. It is the one process that provably walks a wallet's whole history, and it used to write nothing to the index and never touch the certificate — which is why the incident survived a complete, correct re-derivation of the wallet's history. On a terminal done (or empty; never error) it derives memberships from the UNION of the probe's strict full-universe current read, every snapshot row the replay wrote, and every flow row the run wrote including the full-universe tail sweep (a position opened mid-replay appears only there), upserts them, and then stamps the certificate at the tail sweep's head block. Bake THEN certify: a crash between the two leaves the wallet un-certified, which is the safe side.

Divergence is detected by machine, not by reading charts (Phase 6). Two tripwires, deliberately on different paths. The weekly reconcile is the SLOW one: it reads each certified wallet against the complete universe, repairs what the index missed, prints one [fail] DIVERGENCE … line per membership and ends the run non-zero. Its honest acceptance is "within one reconcile CYCLE for its cohort", not "within a week" unconditionally, because the batch is capped and LRU-ordered; the dangling-link check is the exception, being a single un-capped query per run. The 6h tick carries the FAST one: after committing both its snapshots and its flows it reports any leg that was present at a wallet's previous snapshot, is absent now, and has no flow explaining it. Running it after the flow writes is what keeps an ordinary close silent. Together with M21's read-path coverageAnomalies that is same-tick detection on the write path, per-request detection on the read path, and a weekly full-universe backstop.

Staging. scrub-staging-pii.sql now deletes the portfolio:discover:% scope rows alongside the user-data TRUNCATE, and reseed-staging.sh fails closed if any survive. Without it the nightly reseed manufactured the incident's poisoned state on staging for every wallet in the prod dump.

Fluid flow scanner (FWS3, src/lib/portfolio/fluid-flows.ts). Measured chain-wide volume: ~142 LogOperate/day, ~1 LogLiquidate/day, ~59 factory ERC-721 Transfer/day. Because LogOperate/LogLiquidate are unfilterable (zero indexed params), the chain-wide scan is the same on the 6h cron and the JIT mini-scan; the decoded stream is cached in fluid_event_log (D7) so registration backfills never re-scan the chain. Opening-transfer suppression (M17): every transfer-derived flow row passes through ONE shared gate, bookableNftTransfers inside buildTransferFlows, on BOTH paths (scanFluidFlowsChain and scanFluidFlowsFromCache are the only two entry points and both build their rows through buildFluidFlowRows, which calls it): a transfer whose tx ALSO carries a LogOperate for the SAME nftId AND whose position did not exist before that tx books nothing, because the operate rows already carry that tx's true external capital. Without it a zapper open (mint-to-self → operate → transfer to the user, all in one tx) booked the whole position TWICE, and inside the live series that prints a phantom interval yield of −equity. The unfiltered transfers still feed the tracked-NFT set (they are what tie a zapper-opened NFT to the wallet, and thus what attribute the operate rows that survive). The cache read is clamped to the cache TIP (planFluidEventRead): fluid_event_log's only writer is this 6h cron, which stops CURSOR_SAFETY_BLOCKS below its anchor, so the tip trails the chain head by up to ~6h of blocks — while a registration backfill runs to the head and its factory-transfer getLogs is LIVE. Reading events from the cache alone would hand the gate an EMPTY operate set for anything in that trailing gap (a position opened, then signed up for, before the next tick): the zapper's transfer would be booked, the next cron tick would add the operate rows, and since the cron's flow write is an upsert that never deletes, the double-booked rows would survive. So the backfill reads the cache over [from, tip] and chain-scans (tip, head]. That gap also silently dropped the operate rows themselves from a backfill (a phantom yield of +equity), which the clamp fixes too. State-diff liquidations (M15): Fluid has no per-position liquidation event, so a vault's LogLiquidate in the window TRIGGERS a diff of every tracked NFT on that vault across the window anchors; an NFT whose col/debt DECREASED with NO matching LogOperate for that nftId is a liquidation, booked ONCE as a realized-loss row valued as the equity destroyed (Δcol value − Δdebt value at the anchors, clamped ≥ 0), provenance = the LAST LogLiquidate on that vault in the window (multiple partial liquidations in one window collapse into one row), position_key = the NFT group prefix, never a withdraw/repay (M4). dRPC pacing: a chain-wide (no-address) getLogs is expensive server-side even for few results, so the scan uses ≤1000-block chunks (initialSpan 800 / minSpan 200); the dRPC free tier returns HTTP 408 on a wide range, now retried alongside 429/5xx (rpcRequest), and getLogsChunked halves the span + retries on any residual timeout. Deploy seed (D7): scripts/seed-fluid-event-log.ts scans the trailing 90 days chain-wide ONCE (~648 chunks, ~12,780 operate rows, ~11 min, attended, free tier) and records the coverage start; the 6h cron appends thereafter. Run it as a server step at deploy: ETHEREUM_ARCHIVE_RPC_URL=<archive> DATABASE_URL=<...> npx tsx scripts/seed-fluid-event-log.ts.

JIT / live path (src/lib/portfolio/live.ts, triggered by POST /api/portfolio/refresh AFTER first paint — the WS6 GET routes serve stored history plus the cached live result only (cachedLiveResult), never blocking a page load on RPC): a signed-in wallet's current legs read across all readers at one fresh anchor block ("6h stays, now = JIT RPC"), plus a mini flow scan from the wallet's last snapshot block to the anchor (persisted, idempotent). The mini-scan includes a wallet-filtered factory Transfer scan PLUS a chain-wide LogOperate scan over the mini-window (≤ ~1800 blocks) filtered in-process to the wallet's NFTs (LogOperate cannot be wallet-filtered); the JIT path does NOT write the fluid_event_log cache (the 6h cron owns it and re-scans this window with its 64-block safety margin). Rate-limited to one live refresh per wallet per 60s (in-memory, single process); concurrent cold-cache calls for the same wallet COALESCE onto one in-flight computation (coalesce), so a page-reload storm cannot fan out the per-wallet multicall + flow scan. Fluid's positionsNftIdOfUser is one ~30k-gas call, so the JIT path needs no NFT cache. Two-tier persist (src/lib/portfolio/flow-basis.ts). This is the one scan in the pipeline that runs all the way to the anchor, so its top ~64 blocks sit in the window every other writer refuses to touch: a reorg can re-include the same transaction at a different (tx_hash, log_index), and since that is a different primary key, the settled cron scan would write the re-included version alongside the JIT's original instead of upserting over it, leaving a phantom row that double-counts the flow forever (M3: a doubled deposit manufactures or destroys yield). So a JIT row at/below the settle line (anchor − 64, the boundary block itself included) is written basis='live', exactly as before, while the reorg-exposed tail above it is written basis='provisional'. Provisional rows are read exactly like live rows (no basis filter exists on the read path, so /portfolio, the P&L engine and the entry-basis derivation see one undivided ledger) but are OWNED by the writers that can prove them: every JIT resync DELETEs this wallet's provisional rows over the range it re-derives and re-writes the fresh scan in the SAME transaction (a moved log, or a transaction that vanished entirely, cannot survive its own re-scan). That delete is bounded on BOTH sides by the range the resync actually reached, never open-ended above: the anchor comes from a load-balanced RPC, so the next refresh can legitimately be served a LOWER head than the last one, and on the ledger flow path the ledger-owned surfaces stop at the ingester tip — in both cases a row above the scan's reach is left standing rather than deleted un-re-derived. The 6h cron then retires what it has settled: ONE sweep at the end of its flow pass — after every settled write of that tick has committed, so a row is never dropped on the strength of a replacement that failed to land — over exactly the wallets its scans ran with and the block window they covered (see the cron section above). A same-PK settled write simply promotes the row to live. The persist therefore runs on EVERY refresh, including one that finds nothing, because "the previous scan's flow is no longer on chain" is exactly the case the clean exists for (a refresh with nothing to write and no provisional row in range settles that with one indexed EXISTS probe and opens neither a transaction nor the shared write lock, so the lock rate stays proportional to flow activity rather than to page views); a refresh that skips the persist because the writer lock is busy skips both halves together. Only settled rows feed portfolio_held_pts (a registry nothing removes a PT from, so a row that may still vanish must not widen the PT universe). Freshness is stamped at COMPLETION, not at start: the pipeline routinely takes tens of seconds (dRPC pacing, getLogsChunked span-halving), and a start-stamp would spend that duration out of the 60s window — a 45s run would land with 15s of servable life and a >60s run would be DEAD ON ARRIVAL (never servable, yet always re-computed on the next reload, so the user could never reach a live-merged view). The result is only merged by a GET when its anchor block is strictly newer than the wallet's newest stored snapshot (liveMergeAdmissible); a cached read that the 6h cron has since overtaken is dropped rather than merged, because merging it would re-state "now" from an older chain state and book any flow in the gap as phantom yield.

Quoted rates — earned vs advertised (FWS4, api-data.loadQuotedRates, read by GET /api/portfolio/positions): the advertised comparator per current leg, feeding the dashboard's "24h APY" column. Each venue-rate loader prefers the de-noised *_24h trailing column and falls back per row to the latest 6h-window value where the 24h figure is not yet populated (a pool younger than 24h). Aave/Spark read the latest {aave,sparklend}_reserve_apy (supply_apy_24h/borrow_apy_24h, 6h fallback); Morpho reads morpho_market_apy (same 24h columns); ERC-4626 and wrapper terms read token_yield_apy (already a trailing-24h rate, unchanged). Fluid reads fluid_ll_apy (latest per token_address, the ETH pseudo aliased to WETH; supply_apy_24h/borrow_apy_24h with a 6h fallback) for a normal leg's LL rate, and fluid_dex_apy (fee_apy_usd_24h per pool_address, falling back to the 6h fee_apy_usd until the pool has 3 prior rows) for a smart leg's pool fee, marked APPROXIMATE. The fee's SIGN follows the leg side (carries-table.ts smartColApy/smartDebtApy): a smart-collateral leg EARNS the fee (fee + LL supply + wrapper); a smart-debt leg supplies debt-side liquidity and ALSO earns it, so the fee REDUCES its funding cost (LL borrow + wrapper − fee). Because a smart-leg key carries no pool, loadQuotedRates resolves each held vault's per-side DEX pool on-chain ONCE via readFluidVaultDexes (getVaultEntireData.constantVariables.supply/.borrow, the Liquidity Layer mapped to null); best-effort. The pool fee is the DEFINING component of a smart leg, so an unresolved fee — an untracked/off-list pool OR a failed readFluidVaultDexes read — nulls the WHOLE smart-leg quoted rate to a dash (M9), never a fee-less number. No new cron or table.

RPC routing: current-state reads use ETHEREUM_RPC_URL (publicnode default); the flow scanner's eth_getLogs and all block-pinned reads use ETHEREUM_ARCHIVE_RPC_URL (dRPC default), because publicnode rejects archive eth_getLogs over wide ranges. getLogsChunked gained an optional rpcUrl for this. The FWS4 quoted-rate getVaultEntireData reads run at latest on publicnode (the DEX addresses are immutable per vault).

Crontab (manual server step, listed in the deployment runbook and the PR body): 50 */6 * * * /opt/onchain-credit/scripts/run-cron.sh refresh-portfolio.ts (one minute after refresh-lending-positions.ts (45), reusing the same reserve registry that job maintains) and * * * * * /opt/onchain-credit/scripts/run-cron.sh drain-portfolio-backfills.ts (the WS5 queue drain; run-cron's per-script flock keeps invocations from stacking when a backfill outlives its minute).

Concurrency: run-cron.sh takes a per-script flock, keyed by checkout ($LOG_DIR/$(basename "$DIR")-<script>.lock), so if a portfolio tick overruns its 6h slot on a slow archive RPC the next tick logs "already running ... skipping this tick" and exits 0 rather than stacking a second scan. The lock is per-checkout, so the prod and staging working copies on the shared box never serialize against each other. See the run-cron.sh section below.

Staging user data (reseed + scrub): the nightly reseed (scripts/ops/reseed-staging.sh) DROPs and fully restores the staging DB from a prod dump, with no table-exclusion mechanism, so a wallet's real positions would otherwise ride into staging. scripts/ops/scrub-staging-pii.sql therefore TRUNCATEs accounts + account_wallets + the three portfolio tables (portfolio_position_snapshots, portfolio_flow_events, portfolio_backfill_state) right after the restore, and the reseed's fail-closed leak check RAISEs if any of the five still holds rows. account_wallets (048) is in the list because the account↔tracked-wallet mapping is private user data (the positions themselves are public chain data; the fact that an account watches them is not). All five go in ONE TRUNCATE — accounts now has TWO FK referrers (portfolio_backfill_state and account_wallets), and Postgres refuses to truncate an FK-referenced table unless every referrer is in the same statement — so the list is built dynamically from whichever tables exist (to_regclass), tolerating a dump that predates any of 042 / 043 / 048. Adding 048 to this scrub is not optional hygiene: without it the nightly scrub starts failing outright (cannot truncate a table referenced in a foreign key constraint). To give staging something to render, scripts/ops/seed-portfolio-fixtures.ts re-registers 2-3 public whale wallets as accounts; it must be re-run after each nightly reseed (or touch /root/.reseed-paused during a multi-day validation window so the reseed is skipped and the fixtures persist). The same trap reappears with the DESTRUCTIVE snapshot repartition (067): it RENAMEs the live table to portfolio_position_snapshots_preswap and keeps it for a manual DROP, and a rename carries the 056 FK to accounts along with a full copy of the user data — so an aside table still standing at the 02:15 backup rides into the 03:00 reseed and breaks the same TRUNCATE, aborting the script before every leak check below it. portfolio_position_snapshots_preswap is therefore in both the scrub list and the leak-check list (to_regclass-guarded, so a no-op whenever it does not exist), portfolio_flow_events_preswap was pinned beside it pre-emptively and migration 072 (the flow ledger's hash repartition) now produces exactly that table, so the fix was already in place rather than rediscovered by a failed reseed, scripts/ops/scrub-staging.test.ts keeps the two lists in lockstep, and the deployment runbook additionally pauses the reseed across the verification window.

Alerting (WS8): several fail-soft Telegram paths, sharing one bot (env ALERT_TG_BOT_TOKEN + ALERT_TG_CHAT_ID; both unset = silent no-op, so a dev box or an un-provisioned env never posts). The shared, unit-tested formatters + fail-soft sender live in scripts/ops/alert.ts. (1) A cron-failure alert lives in scripts/run-cron.sh (see its section below): on a non-zero exit it POSTs a message naming the checkout, script, exit code and the reason — it greps this run's slice of the logfile for the failure markers the jobs already print ([fail], [partial], .../fail], fatals) and includes them, so an alert is self-explanatory instead of costing an SSH round trip. This covers ALL cron jobs, not just the portfolio one. A job that wants to page therefore only has to exit non-zero and print a matching line — which is exactly how the backfill drain's per-wallet failure alert works (it exits 2 and prints one ERROR uid=… attempt n/2 … line per failure). Two constraints bind, and a job that pages must respect both: the 220-char cut per line (so each line leads with the load-bearing facts and trails the unbounded provider message), and — the tighter one — tail -n 8 lines. The drain's per-wallet lines do NOT own those 8 slots: the backfill child's stdio is inherited into the same logfile, so its own [backfill] fatal: … matches the grep too and competes ~1:1. In a broad outage the per-wallet lines are therefore truncated to an arbitrary few, which is why the drain's summary line (printed last, so it always survives the tail) carries the aggregate count AND names the wallets that actually parked. That line has to contain a literal ERROR: to match at all: the grep's failure token is [fail] with brackets, so a line saying only "FAILED" matches nothing and never reaches Telegram. No second bot, no second code path. (2) An unmapped-asset alert fires from refresh-portfolio.ts AFTER it commits: any leg the book map sent to EXCLUDED while a wallet holds a nonzero position at/above the $1 dust floor, aggregated to one message per tick, now tagged with the venue that surfaced it (T6 extended it past the original wallet path to the exhaustive Morpho loan assets too). Legs the registry KNOWS are excluded from it: an asset with a portfolio_tokens row is by definition not an unknown asset, whatever book it resolves to. That matters for the deliberate book NULL carve-outs (a non-USD fiat like EURC; gold, XAUt; a dollar-denominated token with no par claim, apxUSD — the latter two declared in migration 071), which are valued in USD and never charted BY DESIGN and so land EXCLUDED-with-value on every tick — it would otherwise page every EURC holder every 6h with a remediation ("map it in buckets.ts") that is wrong for a token the registry already covers, and muting a real signal under known noise is the failure this alert can least afford. A token absent from the registry AND unbucketed still fires. (3) Three registry alerts fire from the weekly sync-portfolio-tokens.ts propose run: top-20 stablecoin ranking drift, an asset profile with no portfolio_tokens row, and a wallet-tracked variable_rate token that resolves no rate source. A KNOWN, decided skip (eBTC, unrated) is filtered upstream so it never pages — the same "known + quiet" discipline as the partial-failure floor. (4) A discovery reconciliation drift alert TYPE/formatter is defined here for T5's weekly full-universe sweep to emit when it finds a position the per-wallet discovery index missed (found a week late, paged, never silently mis-charted). (5) A coverage-anomaly WARNING (M21) is the one tripwire that is deliberately NOT on a Telegram path: buildBookCurve reports every value/pt-leg transition the flow ledger cannot explain, and the READ path (api-data.ts) prints [portfolio] WARNING unexplained leg <kind> wallet=… key=…. It therefore lands in the app's pm2 log, not a cron log, and is invisible to run-cron.sh's grep (which cannot see it anyway, and whose failure tokens it does not match, so it can never trip a false cron alert). It is throttled to one line per (wallet, kind, key, mark) per UTC day, and transitions at the newest point of the series are recorded on the curve but never printed (they are dominated by two benign races: the 6h refresher's snapshot/flow split commit, and a JIT tip read whose flow persist was lock-skipped). The WRITE-path counterpart is alert path (6) below. (6) An unexplained leg disappearance alert (alertLegDisappearance) is that tripwire's WRITE-path counterpart: after the 6h tick commits its snapshots AND its flows, any leg present at a wallet's previous snapshot, absent from the one just written, and with NO flow row in the scanned window is reported and POSTed as one aggregated message. It runs after the flow writes on purpose, so an ordinary close and its withdrawal are judged together and a real exit stays silent; it fires on the tick that CAUSED the gap rather than when somebody next loads a page, and unlike the read-path WARNING it is in a cron, which is the only place a Telegram alert can originate. Fully fail-soft: it never affects a tick that has already committed. (7) The weekly reconcile now also exits non-zero on a DIVERGENCE (a membership a bounded read had been dropping, repaired) or a DANGLING LINK, each printing a [fail]-tagged line, so those pair with the cron-failure alert instead of only appearing in the drift message.

Registry sync — `sync-portfolio-tokens.ts` (T6)

Mirrors the sync-carries.ts --approve shape but, unlike the ad-hoc carry/curator syncs, it runs as a weekly cron because its checks power the WS8 registry alerts. The default run (the cron) is PROPOSE + ALERT and never mutates — the "never auto-mutates" rule from the taxonomy plan §3.3. Its three checks (pure logic in scripts/portfolio-tokens-diff.ts, so they unit-test without a DB/RPC/DefiLlama):

Top-20 stablecoin diff. Ranks the DefiLlama stablecoins list by Ethereum circulating, takes the top 20, resolves each entrant's mainnet address from the per-stablecoin detail endpoint, and diffs against portfolio_tokens by address: a new entrant with no ACTIVE registry row at that address is PROPOSED as an idle par, wallet-tracked add (a non-USD fiat peg proposes book NULL); a known accruer is never a par proposal; a source='stablecoin-top20' row no longer ranked is reported as drifted OUT for a human to retire (never auto-retired). Address, not symbol — DefiLlama symbols are not unique across pegged assets, and a symbol key silently counts the WRONG token as covered, permanently, since the registry row never leaves. Live: DefiLlama lists two "GUSD" assets — Gate USD (~$320M Ethereum circulating, 0xaf6186b3…) and Gemini Dollar (~$38M, 0x056fd409…, what 049 seeds). Both are real mainnet contracts whose symbol() returns "GUSD", so the seeded Gemini row absorbed Gate USD's top-20 slot and the $320M member could never be proposed. Coverage is also scoped to status='active', so a RETIRED row cannot suppress a genuine re-entrant forever. An entrant whose mainnet address cannot be resolved is reported as unresolved (console only, no alert): coverage is undecidable, so it is neither counted as covered nor proposed, and it cannot drift its own symbol's row out. That is a real state — USDD's detail address is tron:TXDk8mbt…, not an EVM address at all.
Asset-profile completeness. Every onchain_credit.assets profile ticker must have a portfolio_tokens row, or its bare balance can never enter the wallet venue as a Variable rate asset.
Variable-rate rate-source resolution. Every WALLET-TRACKED variable_rate row must resolve a redemption rate via JIT_RATE_GETTERS OR token_yield_apy.share_rate; an unresolved one would be silently skipped by the wallet venue (M9). The check is scoped to wallet-tracked rows (an untracked vault share is valued through the erc4626 venue, not the bare-token path). eBTC is the one ACKNOWLEDGED, quiet gap.

--approve <SYMBOL> applies a named new-entrant proposal: it takes the mainnet address the diff already keyed on (DefiLlama chain-prefixes some — bsc:0x8d0D… for USD1 — which a bare-0x parse rejected outright, so the prefix is stripped and the address treated as a CANDIDATE only), reads decimals()/symbol() on-chain to verify it ON MAINNET and refuses on mismatch or an absent contract (which is what makes accepting a prefixed address safe: a token that genuinely lives only on BSC fails this check), and inserts an idle par, wallet-tracked row (ON CONFLICT DO NOTHING). It refuses a known accruer, a symbol not currently a top-20 entrant, or one it cannot resolve, and it NEVER retires a drift-out row (retirement stays a manual, deliberate UPDATE/DELETE). Coverage extensions are registry rows or a flipped wallet_tracked flag from here, never a code change.

Two gaps closed after the 2026-06-25 incident. (a) The alert used to say only "script X exited 1", on the stated theory that exit-code alerting cannot see log lines — but the wrapper OWNS the logfile and can simply read it back. (b) More seriously, a partial failure alerted nothing at all: refresh-assets.ts exited non-zero only when a refresher THREW, while a refresher that catches errors per item (token-yields catches per token, writing no row for a token that fails) could lose most of its rows and still return normally — exit 0, silence, and a hole in token_yield_apy that only surfaced weeks later as a false 0% APY on the carry charts. A reported per-item tally (RefresherStats) now makes an anomalous partial a failure. Anomalous, not any: five curator vaults fail every single run today (Cannot convert 0x to a BigInt), so a "failed > 0" rule would page every 6h until muted. The floor is a failure RATE (PARTIAL_FAILURE_RATIO, 10%; today's token-yields steady state is ~6% and stays quiet), unit-tested in scripts/ops/alert.test.ts. A per-item refresher can only be wired to the rate floor if its steady state sits BELOW it: token-yields qualifies, but curator-vault-state currently fails ~40% of its 60 vaults every run (a pre-existing Morpho-V2 / Euler indexing gap), so under any single global floor it would page continuously. Until those underlying failures are fixed it deliberately stays all-or-nothing (void), rather than reporting a tally that would either spam or force the floor so high it hides real regressions elsewhere. So: token-yields reports; the chronically-degraded per-item refreshers stay void by design; genuinely all-or-nothing refreshers return void because they have nothing partial to report.

(2) An unknown-asset alert lives in the refresher itself (scripts/ops/alert.ts, alertUnknownAssets): exit-code alerting can't see a data event where the run SUCCEEDS, so after committing its snapshot writes the job POSTs directly when the bucket map (buckets.ts) sent an accounting asset to EXCLUDED while a wallet holds a nonzero MARKET value in it (a new/unmapped asset that would otherwise silently drop out of the books). One aggregated message per tick (all such assets, distinct-wallet + summed-value), so the same unknown asset does not fan out; the pure parts are unit-tested in scripts/ops/alert.test.ts. Both paths are strictly fail-soft: a failed POST is logged and swallowed and never changes the job's exit code. Job failures also remain visible in /tmp/onchain-credit-cron/refresh-portfolio.log and the cron mailer.

(3) A discovery-drift alert (scripts/ops/alert.ts, alertDiscoveryDrift) fires from the weekly reconciliation sweep (refresh-portfolio-reconcile.ts, T5 §3.6) when it finds a currently-held Morpho/erc4626 membership that the discovery index MISSED — i.e. a bounded 6h read had been silently dropping that leg. Unlike the unknown-asset alert there is no value floor: any missed membership is a real bug (a scan gap in the flow-scan byproduct or the enrollment scan), and the sweep both ADDS it to portfolio_wallet_index (so the next tick reads it) and posts one aggregated message (distinct wallets + memberships, up to DISCOVERY_DRIFT_ALERT_MAX_ROWS enumerated). Steady-state fires ZERO rows; a non-empty alert means "investigate discovery.ts; the positions are now indexed". Same fail-soft POST + alert.test.ts unit coverage as the other paths.

Registration backfill (WS5)

So an account is not born with an empty chart, each newly-registered wallet gets the history of its OPEN positions replayed once at signup. Two product decisions (2026-07-12) shape the replay: open-only — history exists only for the position groups the wallet holds when the backfill runs (the portfolio is a scoped fixed-income view; a standalone position closed before signup would surface history the user does not recognise) — and a daily pre-signup grid (UTC midnights; 6h resolution starts at signup). Note the grid is visible on the 1W timeframe, which draws the raw 6h cadence (?bucket=6h, added 2026-07-17): across the pre-signup part of a recently-registered wallet's week each midnight point sits between three empty 6h windows, so it renders as a lone dot rather than a line (isolatedIndices in chart-series.ts — without that dot it would draw nothing at all, since a segment needs two consecutive non-null points). Every longer timeframe reduces to one point per UTC day, where the distinction does not arise. scripts/backfill-portfolio-wallet.ts (logic in src/lib/portfolio/backfill.ts) is BOTH the queued job the drain cron runs and the manual repair tool — note a FRESH repair run (no coverage anchor, or --fresh) replays what is open AT RUN TIME, so re-running a wallet that has since closed a position also prunes that position's history, by design.

Open-group granularity (src/lib/portfolio/open-set.ts): "open" is decided per position GROUP, never per leg — dropping one closed leg of a still-open group would misstate historical NET value (an Aave debt repaid pre-signup under still-held collateral would inflate every past equity point). Groups: aave/sparklend = the VENUE ACCOUNT (cross-margined account-wide, so any open leg keeps the wallet's whole venue history and its full reserve universe); morpho-blue = the MARKET id; pendle = the PT instrument; erc4626 = the vault; fluid = the NFT. One derivation from the probe's strict current read feeds three surfaces that cannot drift: the replay's restricted reader universes (restrictRegistries, incl. the reg.curatorVaults erc4626 override), the grid-read post-filter (filterReadsToOpenSet, which also reins in the self-describing Fluid reader whose per-block enumeration would resurface closed NFTs), and the flow filters.

Enqueue (built in src/app/api/auth/verify, not WS1): on a successful SIWE verify, after the account is upserted, enqueueBackfill(uid) inserts a portfolio_backfill_state row status='queued' — INSERT ... ON CONFLICT (uid) DO NOTHING, so it fires exactly once (on account creation, or a chat-seeded account's first login with no state row) and a re-login of an already-processed account is a no-op (a completed backfill is never re-queued). The FK to accounts(uid) requires the account to exist first.

Probe: ONE STRICT multicall round of the wallet's CURRENT positions across all readers — strict via readGridPointOrThrow, because under open-only this read ALONE decides terminal empty (never re-queued on re-login), so a swallowed venue failure must abort the run, not silently drop a venue. empty = no current positions, full stop; the wallet ends status='empty' after ONE read pass with NO log sweep at all (past activity alone no longer earns a replay). A non-empty wallet's reads define the open groups, and only then does the candidate 90-day window get swept — RESTRICTED to the open groups' tokens/events, wallet as the topic filter, post-filtered by the open set (pool-level Aave/Spark liquidation events cannot be restricted at the RPC) — in ascending block segments (BACKFILL_FLOW_SEGMENT_BLOCKS, default 200k ≈ 27.8 days) whose detected flows are FOLDED into the first-activity answer and released, never retained (probe memory is O(one segment) regardless of wallet activity), with an early exit once a curve-starting block is found (no later, higher-block segment can lower the minimum — an active wallet's probe scans ~one segment; the par-only fallback is exactly the case that still needs the whole window). First activity block = the first swept log of an open group that can start a yield curve: a par wallet-token (idle) flow is excluded unless it is the only activity (firstCurveActivityBlock), so an old stablecoin top-up cannot pin the chart's start months before the first yield-bearing position. Fluid probe (FWS3): the wallet-filtered sweep structurally CANNOT see LogOperate (zero indexed params), so the fluid probe uses FACTORY ERC-721 Transfers only, filtered to the OPEN NFT ids. The fluid FLOW rows themselves come from the cache during the replay, not from the probe.

Replay: from max(signup − 90d, first activity, coverage floor 2025-05-21) to now, on a DAILY grid — UTC midnights (floor_ts = the midnight at/below the range start) plus ONE final 6h-aligned seam point when "now" lies past the last midnight, so the live cron's next 6h tick continues the series without a same-day gap (~91 points for a full 90-day window; MAX_GRID_POINTS=120 safety clamp). Midnight is on the 6h grid, so every replay ts is a valid aligned window and nothing downstream can tell a daily point from a 6h one. Each grid point is read via archive multicalls at blockByTimestamp(gridTs) over the RESTRICTED open-group universe and valued in both marks by the SAME buildSnapshotRows path the live cron uses (verified byte-identical at a shared block), but written with basis='backfill'. floor_ts records the range start = the account's "tracked since" anchor (per-account, shown in the methodology footer). It is SET on done (and cleared on empty), never LEAST-merged: under full-history ownership (below) every run deletes everything at/below its own end before re-laying rows, so the anchor always equals the run's grid[0] (= min(snapshot_ts) up to an at-most-24h empty lead-in day, which writes no rows) — the old monotone-down LEAST merge belonged to the windowed-delete world and would leave the anchor stranded below the earliest row after a raised-start re-run. Alongside the segmented flow writes, a tail sweep covers (probe now-block, current head] with UNRESTRICTED targets — the position-token streams AND the wallet-venue ERC-20 streams (native ETH is excluded: its flows are snapshot-diff-derived and self-heal on the next tick, below) — (that span is post-signup real time, where open-only scoping does not apply — a position opened mid-replay must keep its birth deposit; fluid via a small chain scan): the replay takes minutes, and a flow landing in that span could otherwise be missed forever (the concurrent 6h tick excluded this wallet at eligibility-snapshot time, then advanced the global cursor past the span; the JIT mini-scan only rescues it until the next snapshot). The cursor's later overlap re-scan is idempotent on the flow PK.

Strict grid read (M9): unlike the live path, which reads through readAllPositions and silently swallows a venue's RPC failure (the next tick recovers), the backfill BAKES each grid point into history, so it reads through readAllPositionsSettled (which reports the venues that threw) via readGridPointOrThrow, with strictNative set (below). A grid point whose read reports ANY failed venue is retried (GRID_READ_MAX_ATTEMPTS=4, backing off); if it still fails the whole backfill THROWS (the account is left non-done for a clean re-run) rather than persisting a phantom-empty window (a total failure) or a leg silently MISSING from an otherwise-present ts (a partial failure, which would read as a phantom loss then gain). An all-venues-succeed empty result is a genuine "held nothing" and is written as an empty window. Two things the venue-level strictness alone did not cover. (a) The UNIVERSE the venues are read against: loadRegistries CATCHES a loadPortfolioTokens/loadFactoryVaults failure and degrades to an empty wallet universe / the static erc4626 set, which is right for the live paths but indistinguishable from "the registry is empty" — so for a wallet whose only holdings are wallet-venue (a pure sUSDe/reUSD holder) the probe would read "holds nothing", and the empty branch PRUNES the account's history and sets a terminal status='empty' that is never re-queued. loadRegistries now REPORTS every degradation and the backfill entry calls assertRegistriesComplete before the probe, aborting the run instead (the account is left running for the caller to mark error, so it is retried, not resolved wrongly). (b) The native-ETH leg: the wallet reader skips a FAILED eth_getBalance exactly as it skips a true zero, INSIDE an otherwise-successful venue read, so readGridPointOrThrow never saw it — and the anchor path recorded the absence as a 0 balance, which deriveNativeEthFlowsFromAnchors turned into a fabricated full-balance transfer_out + next-day transfer_in plus a one-day equity dip. Both backfill reads (probe and grid) now pass strictNative, so a failed native read fails the wallet venue, retries, and then aborts the point. It is scoped to the native leg deliberately: eth_getBalance never reverts, so a null there is unambiguously a failure, whereas a null ERC-20 balanceOf can be a permanent revert and a strict mode there would abort every backfill forever. The throw fires BEFORE the windowed delete+insert, so prior good rows are never touched. Positions that predate the range enter as the OPENING BALANCE of the first snapshot (their qty at grid[0]), NOT as a flow — that is what makes the chart honest. First activity RAISES the start so a chart never opens on a flat-zero lead-in — and only CURVE-STARTING activity counts (par/idle wallet-token flows are excluded unless they are all there is), so the lead-in cannot re-enter through an idle top-up months before the first yield-bearing position; a par flow the raised start leaves behind is absorbed into grid[0]'s opening balance by the flow clip below. Note this start is per-WALLET, so it belongs to whichever BOOK opened first; each book's plotted chart is anchored again at read time on its own first curve-starting observation (trimIdleLeadIn, see Portfolio), which needs no re-backfill and changes no stored row. The charted range's flows are then written SEGMENTED, streaming (the whale-OOM fix's flow half): one short transaction under the writer lock deletes the wallet's flow history at/below the probe's anchor block, then each bounded block segment (BACKFILL_FLOW_SEGMENT_BLOCKS, default 200k blocks) is detected (detectFlowSegment, ledger-served when certified) → valued in both marks → UPSERTED in its own short lock-held transaction (upsertGapFlows, the same idempotent flow upsert the gap patch uses) → released, with one rss/heapUsed log line per segment; the FINAL transaction windowed-deletes the tail span (anchor, certify block] before upserting the fluid/native-ETH/tail residues, so the union of the two deletes is exactly the old single write's <= certifyBlock bound (full-history ownership preserved) — so flow-phase memory is O(one segment's flows) however active the wallet is, and the lock is only ever held for the writes, never across the segment's RPC/valuation work. A failed segment fails the wallet exactly like a failed whole-window scan did (non-done, the next run re-derives). The anchored-wallet case is explicit (review findings): the only way an ANCHORED wallet reaches this destructive path is the operator's --fresh, and that dispatch REVOKES the coverage anchor up front — and again atomically inside the wipe transaction — precisely so the crash-intermediate state stays repairable: with the anchor left standing, a run that died between the wipe and the last segment would dispatch its retry to the GAP path, which by design never re-derives at/below the anchor, permanently stranding the wiped span (every lost deposit/withdrawal would read as yield/loss beside complete snapshots). Revoked, the retry re-enters the FRESH path and fully re-derives; a completed run re-earns the anchor at done. A healthy destructive re-run of a done wallet has a reader-visible window (accepted, operator-only): between the wipe's commit and the last segment's upsert — minutes of per-segment detect/value RPC — a concurrent /portfolio read of that wallet sees complete snapshots with NO charted-range flows, so historical deposits transiently read as yield and entry bases vanish; fresh signups have no prior rows and first-time wallets sit behind the building gate, so only the manual repair of a live done wallet exposes it. Flow valuation is strict about block timestamps (valueFlows): the per-distinct-block eth_getBlockByNumber reads run at bounded concurrency (VALUE_FLOWS_RPC_CONCURRENCY, default 24) with a 4-attempt retry ladder, and a block that still cannot be resolved THROWS — failing the segment/tick like any failed scan — instead of the old silent per-flow skip, which could complete a run done with flows missing from the ledger (their principal booking as yield). The completed run's rows are byte-identical to the unsegmented pipeline's (pinned by fixture tests: boundary blocks, same-tx multi-log, fold equivalence). Fluid flows come from the fluid_event_log CACHE (D7), never a chain re-scan (scanFluidFlowsFromCache: factory transfers stay a direct wallet-filtered getLogs; a window predating the seed's coverage start falls back to a chain scan with a loud log line, guarded) — and their DETECTION deliberately stays whole-window, not segmented: the M15/M17 gates are window-scoped (an operate anywhere in the window suppresses a state-diff liquidation for that NFT), and the cache's volume is protocol-global, not wallet-proportional. Their VALUATION is batched (valueFlowsInBatches, FLUID_VALUE_BATCH_FLOWS default 5000): valueFlows is per-flow, so batching changes no row while capping the per-call block-ts/marks state a fluid-hyperactive wallet's tens of thousands of legs would otherwise hold at once. PT entries older than the flow range get the synthetic basis per M6 downstream (WS6 reconstructs the entry fill from the first snapshot's rate when no in-range acquisition exists).

Wallet-venue history (T3): the wallet venue's FLOW ledger is replayed over history too. Before T3 the backfill read wallet BALANCES only, so a variable_rate wallet token (sUSDe / reUSD / …) whose balance changed mid-window booked the whole balance step as phantom yield — a bare variable-rate leg is a value-accrual leg, attributed as Δvalue − netLegFlow, so with no flow the acquired principal reads as profit. Two derivations, mirroring the forward cron (both idempotent, both flowing through the SAME open-set filter, valuation and delete+insert as the other venues): (1) ERC-20 replay — the OPEN wallet tokens' Transfer streams are swept alongside the position-token streams (a SEPARATE getLogs pass, so a bare token can never collide with a position-token address in scanTransferFlows' by-token map; the native-ETH sentinel is dropped — it has no Transfer logs), so every historical acquisition/disposal books as a transfer_in/transfer_out at its real block and the replayed variable-rate curve then attributes only share-rate yield (the flow nets the acquired principal). A variable_rate wallet-token acquisition can also be the wallet's first-activity block (a par token's cannot — firstCurveActivityBlock). (2) Native-ETH balance diffs — the eth_getBalance the wallet reader already issues at each grid point (≈1 read/wallet/day) is diffed against the previous anchor into a transfer_in/transfer_out (native ETH is par, so Δvalue = net flow EXACTLY, gas spend lands as a small transfer_out; grid[0] is the opening balance, never a flow), keyed by the grid point's aligned window (nativeEthDiffTxHash) so a re-run upserts the same row and the 6h seam anchor hands the series to the live cron (which continues by diffing its next snapshot against the seam — a snapshot-diff flow self-heals, so native ETH needs no tail sweep). No double-count at the live/backfill boundary: an ERC-20 flow carries the real (tx_hash, log_index) PK the forward cron re-produces, a native-ETH flow the window-keyed synthetic tx, so a forward re-scan of the seam overlap UPSERTS the same row rather than duplicating it (verified live: the backfilled curve of a real held-through sUSDe holder with a mid-window top-up attributes only the share-rate yield, not the top-up principal, with a clean per-interval yield-invariant residual).

Archive RPC: readAllPositions reads through multicall3, which targets ETHEREUM_RPC_URL — and publicnode REJECTS archive eth_call. So the backfill runs in its OWN process with ETHEREUM_RPC_URL defaulted to the archive endpoint (the CLI entry sets it before importing anything that snapshots the URL at module load; the cron spawns the CLI as a child with the archive env). This keeps the read/value code path identical to live while routing every read to an archive-capable node.

Processing (the minutely drain cron): * * * * * run-cron.sh drain-portfolio-backfills.ts drains the queue with a WORKER POOL (BACKFILL_WORKERS, default 4; Phase C D3, above) — the workers RACE on the same atomic UPDATE ... WHERE uid IN (SELECT ... ORDER BY updated_at LIMIT 1 FOR UPDATE SKIP LOCKED) claim (flipping the oldest available row running), each spawns its archive-routed child and awaits it before claiming again — until the queue is empty or the SHARED per-invocation cap (PORTFOLIO_BACKFILL_MAX, default 10; 0 disables) is reached; leftovers ride the next minute. SKIP LOCKED locks disjoint rows, so no two workers ever claim the same wallet and approximate FIFO holds. run-cron's per-script flock guarantees invocations never overlap, so the reclaim's live-child safety holds even when a run outlives its minute, and an empty-queue invocation prints nothing. The child owns the done/empty transition; the drain owns retry, parking and alerting (next paragraph). Because the live-snapshot wallet selection excludes queued and running, a wallet is live-snapshotted only after its backfill finishes; the residual concurrent-writer races are closed by the advisory writer lock (above).

Child failure: retry once, then park, and page either way (2026-07-16). A backfill child that exits non-zero has its attempts burned and is either RE-QUEUED (budget left) or PARKED as error (attempts >= MAX_BACKFILL_ATTEMPTS = 2, i.e. one retry). Every failure — the retryable first one AND the final one — is returned to the drain, which exits 2, which is what fires run-cron's Telegram alert; the per-failure log line leads with the WALLET, the attempt number and whether it was FINAL, so the alert answers "who lost history, and is it over" without an SSH round trip. The retried wallet is skipped for the REST of that invocation (the drain would otherwise re-claim it milliseconds later and burn the whole budget in one tick against a provider that just failed); the next minutely tick is the backoff.

This replaced a silent park. The child records its own status='error' + message before exiting non-zero, so the drain's old UPDATE ... WHERE uid = $1 AND status = 'running' fallback matched no row on the common path, and only the reaper's parks ever exited non-zero. A transient rpc http 503 therefore parked a wallet with no alert at all while the cron reported success.
How long a park lasted depended on the wallet, and this is the part worth understanding. The drain never re-claims an error row (the claim query takes only queued), but enqueueOrRequeueBackfill does: it requeues status IN ('error','empty') (resetting attempts=0, error=NULL), and the SIWE verify route calls it on every successful sign-in. So for an account's OWN wallet a park self-healed at the owner's next login — stale history until then, and nobody told, but self-limiting. For a watch-only tracked wallet (v0.9.0 multi-wallet) it did not: its accounts row is a SHADOW that nobody ever signs in as (last_seen_at stays NULL, which is what makes it a shadow), so the sign-in path can never fire and the only recovery is a remove + re-add or an operator re-queue. That is the live case: 0xef08c6a4…, a tracked wallet, parked 2026-07-15 14:28 and sat until a manual re-queue a day later. Tracking made the silent park open-ended for exactly the wallets whose owner is not the one signing in.
MAX_BACKFILL_ATTEMPTS shares the attempts column with the reclaim budget deliberately (that column already means "failures burned by the current run" and resets to 0 on done/empty, which is exactly the retry counter's semantics, and reusing it keeps the fix migration-free); the budgets can interact, so a wallet that orphaned once then fails a child parks on that first failure instead of retrying — earlier and still alerting, never later or silently. A timed-out child (SIGKILL at CHILD_TIMEOUT_MS) is the one failure never retried: the replay would still need >30 min, and run-cron's exclusive flock means a second 30-minute child blocks every other drain tick behind it.

Crash recovery (stale running reclaim): the queued→running flip commits BEFORE the child is spawned/awaited, so if the whole cron process tree is torn down mid-run (server reboot, OOM-kill of the cgroup) neither the parent's crash-fallback nor the child's terminal write runs, and the row is stuck running forever — permanently excluded from BOTH this queue and the live snapshot cron with no other recovery. So on every drain invocation, before claiming, processBackfillQueue reclaims any running row older than STALE_RUNNING_MS (60 min) back to queued, incrementing its attempts (migration 045); a row whose budget is spent (attempts >= MAX_RECLAIM_ATTEMPTS = 3) parks as error instead — a wallet whose backfill reliably tears the process down must not reclaim-loop forever under a minutely schedule (repair stays available via the manual CLI). The staleness threshold MUST exceed CHILD_TIMEOUT_MS (30 min): a legitimately in-flight child's updated_at is stamped at claim time and not heartbeated during its run, and run-cron.sh's flock forbids overlapping invocations, so a row older than the reclaim cutoff cannot be a live child. A reclaimed row re-enters the queue and re-runs (the windowed delete+insert makes that idempotent).

Repair / idempotency of the FRESH replay path (M9, the append-only carve-out; a re-add with a coverage anchor takes the gap-patch path instead, which preserves everything at or below the anchor — see above): a fresh run OWNS the wallet's ENTIRE history — it deletes everything at/below its own end for BOTH tables (snapshots by ts, flows by block) and re-lays the open-only daily replay. A lower-bounded ("windowed") delete would orphan old rows below a raised range start (open-only can raise first activity when the earliest-activity group has since closed) and splice stale full-universe history onto the new — the orphaned rows would chart a pruned leg's value and then book it as a phantom realized loss at the seam. A re-run is therefore a full re-derivation, not a patch: it REPLACES post-signup 6h live rows with daily rows and PRUNES groups closed since the last run (open-only, by design); a wallet re-run with NO open positions prunes its whole history and clears floor_ts (status='empty'). It is NOT the recovery for a missed 6h snapshot tick — a missed tick is just a chart gap the next tick moves past. Two runs with no intervening wallet activity produce identical rows (blockByTimestamp is deterministic, archive reads at a fixed block are deterministic, DeFiLlama historical prices are deterministic).

Repair runbook (manual; ONLY for a broken/incomplete backfill — a missed 6h tick needs no repair, it is just a chart gap):

bash

# DESTRUCTIVE RE-DERIVATION (the FRESH path; a wallet with a coverage anchor takes the gap patch instead, or pass `--fresh` to force this one): deletes the wallet's ENTIRE stored history (both
# tables) and re-lays the open-only daily replay from the recomputed range start.
# Post-signup 6h resolution is thinned to daily and groups closed since the last
# run are pruned. NEVER pass a small --days on a wallet with a long history —
# the full-range delete still runs, so --days 7 amputates everything older.
# `--fresh` on an anchored wallet REVOKES the coverage anchor up front, so an
# ABORTED repair re-runs FRESH (never gap-patches over its own wipe); the anchor
# is re-earned at 'done'. While the repair runs, a concurrent /portfolio read of
# that wallet transiently sees snapshots without charted-range flows (minutes).
ETHEREUM_ARCHIVE_RPC_URL=<archive> DATABASE_URL=<...> \
  npx tsx scripts/backfill-portfolio-wallet.ts --uid 0x<wallet>
# (--days / BACKFILL_DAYS exist for acceptance runs on throwaway accounts only.)
# To re-queue instead of running inline, INSERT ... ON CONFLICT DO UPDATE SET
# status='queued' and the minutely drain picks it up within ~a minute.

Event ledger ingester (portfolio 100k scale)

The event ledger is the wallet-count-independent spine that lets the portfolio serve up to 100k registered wallets without the wallet-topic eth_getLogs OR-arrays that hard-fail at ~1-2k wallets. It is not a cron: it is an always-on PM2 process (creddit-event-ingester, scripts/ingester/ingest-events.ts), added as a manual server step (see Deployment → event ledger ingester). The ledger accumulates events; the venue readers stay the balance-of-record.

What it scans. The scan surface is derived once per cycle from the SAME DB registries the venue readers use, by the pure trackedContracts(reg, vaults) (src/lib/portfolio/tracked-contracts.ts) — the single source, so the reader universe and the event universe cannot drift. Six streams (all by contract address + event-signature topic0, never by wallet): transfers (aTokens + variableDebtTokens + every ERC-4626 share token + ALL Pendle PT addresses), morpho (the Blue singleton, 7 events), aave-pool / spark-pool (LiquidationCall + UserEModeSet), fluid-operate (chain-wide LogOperate + LogLiquidate, no address filter), fluid-nft (the Fluid VaultFactory ERC-721 Transfer).
The loop (~60s). head = eth_blockNumber, scanTo = head − 64 (the same safety margin the flow scans use). Per stream: cursor-scan (chain_scan_cursors scope ledger:<stream>) from cursor+1 to scanTo via getLogsChunked, decode → resolve block timestamps (exact per-block, an in-process LRU with BOUNDED fan-out so a wide window cannot burst the node) → idempotent write into raw_events (ON CONFLICT DO NOTHING; a partition is auto-created before a batch crosses into a missing one) → (singleton streams) stamp the '*' coverage certificate → advance the cursor. That order is load-bearing: the cursor is committed LAST, never ahead of the certified range, so a failed stamp self-heals on the next cycle's re-scan instead of wedging the certificate at a gap. Per-stream fail-soft: one stream's failure logs [ingester/<stream>] ... error and never blocks the others.
New-token catch-up (the "USDS" runbook). A transfers token not yet stamped live (a newly added curator vault, Pendle roll, or synced reserve — or any token after a coverage reseed) has its Transfer history backfilled from the 2025-05-21 floor to the live cursor via the archive RPC, then stamped live. It is drained INCREMENTALLY: scan + write per ~50k-block window (bounded memory, never the whole history in one in-memory array), interpolated block timestamps (2 boundary reads/window), advancing a backfilling resume marker after each committed window and capped to a few windows per token per cycle so the catch-up never starves the live scans. Ownership is live-ONLY, so a token whose catch-up failed or is mid-drain RESUMES from its confirmed tip next cycle (never stranded, never restarted). It stays in the live scan throughout (idempotency makes the overlap harmless), and the per-cycle token count is capped so a fresh deploy / reseed cannot stampede.
Coverage certificates. event_coverage holds a contiguous [from_block, to_block] range per (stream, address) ('*' for the singleton / chain-wide streams). The rule the writers enforce (mergeCoverage): never stamp a range you did not scan — a gap between the stored range and an update throws. Only a live row is a completeness certificate; backfilling is a progress/ownership marker. The stamp is safe under the two writers the cutover runs at once (ingester + one-time backfill): the durable merge is atomic in SQL (LEAST/GREATEST/sticky-live), the JS gap check is the honesty gate. For a transfers token, to_block is the historical catch-up completeness mark; the live freshness bound is the shared ledger:transfers cursor, which the live scan (not the per-token row) advances.

`backfill-event-ledger.ts` (one-time historical sweep)

scripts/backfill-event-ledger.ts fills raw_events for every stream from the 2025-05-21 coverage floor up to where the ingester's live cursor has reached, so the ledger has depth below the point the ingester started following live. Run once, attended, after the PM2 ingester is up. Resumable: each stream sweeps bottom-up in windows and saves a resume cursor (chain_scan_cursors scope ledger:<stream>:backfill) after each window, and event writes are ON CONFLICT DO NOTHING, so a re-run is a no-op over already-written ranges. Block timestamps are interpolated per window (2 boundary reads/window), never one read per event block. Archive RPC throughout. At completion a stream is stamped live from the bottom it ACTUALLY swept (completionCoverageFrom), so a re-run with a lower --from-block than a prior run's floor cannot certify blocks no run scanned — it preserves the stored bottom rather than over-claiming down to the requested floor.

bash

ETHEREUM_ARCHIVE_RPC_URL=<archive> DATABASE_URL=<...> \
  npx tsx scripts/backfill-event-ledger.ts        # [--stream <name>] [--from-block N] [--to-block M]

A bounded live smoke (scripts/ingester/smoke-ledger.ts, read-only, not part of npm test) scans a few-hundred-block window for one stream and asserts the decoded rows equal the eth_getLogs ground truth.

Ledger-driven cron (Phase B)

Phase B makes the 6h cron CONSUME the ledger, behind the PORTFOLIO_LEDGER_MODE flag read once per run:

off (default) — today's behavior, byte-identical. The cron runs its own flow scans + full-universe reads; the ledger is written but unread. This is the rollback position at every step.
shadow — the legacy path stays AUTHORITATIVE (its writes land exactly as today). Additionally, in memory only, the cron re-derives the ledger-owned flows + the dirty/recompose snapshot rows, DIFFs them against the legacy results, logs one greppable [portfolio/parity] line, and upserts one portfolio_parity_runs row. Nothing extra is written to the history tables.
on — the ledger path is authoritative: flows are derived from raw_events; snapshots are the dirty re-reads + recomposition (below).

Ledger-derived flows (src/lib/portfolio/ledger-flows.ts). The four legacy scanners put every registered wallet into an eth_getLogs topic OR-array (the ~1-2k-wallet failure). The ledger versions filter to the wallet set in SQL on the raw_events topic indexes instead, then run the SAME per-log classifiers the legacy scanners run (flows.ts classifyTransferLog / detectMorphoFlow / detectLiquidationLog; fluid-flows.ts decoders + buildFluidFlowRows) — so a given log classifies identically either way (proven by ledger-flows.test.ts running one fixture set through both, and asserted end-to-end by the shadow diff). They feed the UNCHANGED valueFlows → writeFlows pipeline. The ledger owns the position-token transfers, morpho, aave/spark liquidation, and fluid surfaces. The wallet-venue bare-token Transfer scan and native-ETH balance-diff flows are NOT ledger-owned (the ledger deliberately excludes high-volume bare tokens for scale) and keep their existing derivation in every mode.

Freshness (never a chain-scan fallback). In on mode the flow pass consumes only up to min(anchor−64, every relevant ledger:<stream> cursor). If the ingester lags the anchor, the flow + dirty passes HOLD at the ledger tip and log [portfolio] ledger lag … loudly — never a silent skipped range, never a fall-back to a chain scan.

Dirty set + recomposition (dirty-set.ts + recompose.ts). Instead of re-reading every eligible wallet, on mode re-reads only the DIRTY set — wallets appearing in raw_events since the previous tick's scan tip (Transfer/Morpho-owner/Fluid-NFT topics + UserEModeSet users), always-dirty Fluid holders, a rotating reconciliation shard (hash(wallet) mod K == tick mod K, K = RECON_DAYS × 4, so every wallet is re-read once per RECON_DAYS, default 7), and never-snapshotted wallets — WALLET-SHARDED at ≤500 per pass with a per-shard transaction. Every OTHER eligible wallet is RECOMPOSED: its latest stored legs × freshly-read shared sources (per-reserve normalized indexes, ERC-4626 share rates, Morpho accrued rates, Pendle PT rates, mirror prices — each read ONCE per tick, O(universe)), producing rows shape- and value-identical to a full re-read (recompose reconstructs a synthetic read and runs the SAME buildSnapshotRow). M9 honesty: if ANY leg of a wallet cannot be recomposed honestly (missing/failed shared source, unrecognized leg shape, a Fluid leg — Fluid recompose is deferred), the WHOLE wallet is PROMOTED into the dirty re-read set, never written partial/stale/zeroed. The shared universe reads are venue-isolated (one venue's transport failure degrades to an empty index → the affected wallets promote, never abort the tick). Native ETH is par and eventless, so a recomposed wallet carries its stored ETH qty forward (bounded by the reconciliation shard).

Two extra always-dirty conditions cover surfaces recompose cannot own honestly. (1) Bare variable_rate wallet-venue holders (wstETH, weETH, …): a bare ERC-20 transfer is NOT a ledger stream (the ledger excludes high-volume bare tokens for scale), so a mover is never ledger-dirtied — but its Transfer flow IS scanned every tick, so a stale recomposed qty netted against that flow would fabricate yield (accrual value: yield = Δvalue − netFlow, and Δvalue = 0 while a real flow lands). Their holders are re-read every tick, never recomposed. A PAR bare token (USDC) fabricates no yield (accrual none), but its VALUE can lag its scanned flow up to RECON_DAYS until the reconciliation shard re-reads it — an accepted, bounded, self-healing divergence (native ETH avoids it only because its flow is ALSO snapshot-diff- derived, so no flow is emitted while its snapshot is stale). (2) Incomplete-window wallets: a transient venue-read failure in the dirty pass persists a snapshot missing that venue's legs; any wallet whose latest window dropped a venue present in its prior window is re-read, healing the gap in ONE tick (as the legacy full-read path does) instead of recompose reproducing it until the reconciliation shard.

Held-PT set (portfolio_held_pts, migration 065). loadPendleMarkets used to keep a matured PT in the universe by seq-scanning the two biggest user tables on every cold refresh. It now consults portfolio_held_pts (active OR within grace OR pt ∈ held_pts); the snapshot + flow writers upsert a PT there whenever they write a pendle-venue / known-PT row, and migration 065 SEEDED it from the same four "held-by-anyone" arms, so the universe is behavior-identical (proven in registry.test.ts). The four-arm scan is retained as the PENDLE_MARKETS_QUERY_LEGACY fallback for a box where 065 has not landed.

Parity workflow + flip criteria. In shadow the [portfolio/parity] line reports flows_legacy / flows_ledger / missing / extra / flow_field / snap_compared / snap_mismatch / promoted / lag; the same counts + a capped sample of the first 50 mismatches (PK + both values) land in portfolio_parity_runs (UPSERT per window+mode; flows_field is migration 066). Tolerance: qty_raw / index_raw / amount_raw EXACT, the float value columns at 1e-9 relative. Skipped snapshot diff ≠ clean. If the tick's stored-leg preload fails, the snapshot half of the diff is SKIPPED (the flow half still runs): the line prints snap_compared=null snap_mismatch=null snap_skipped=1 and the row persists NULL snapshot counters (migration 069) — never 0, which is reserved for "the diff RAN and compared zero rows" (legitimate when every wallet is dirty or has no stored legs). isCleanParity is false for a skipped tick, and any counters-are-zero SQL flip check self-defends because snap_mismatch = 0 is not true for NULL — a recurring preload failure can therefore never certify the flip with the recompose leg untested. (On a box without 069 the NULL write fails and the tick shows as a missing row — a visible hole, the same safe failure mode as before the skip path existed.)

The SNAPSHOT diff is apples-to-apples with what recompose actually owns, not a raw legacy-vs-recompose row diff: (a) it seeds recompose from the PREVIOUS window (snapshot_ts < snapshotTs), because the legacy path has already written THIS window before the diff runs — without that bound recompose would carry this tick's just-written legacy qty forward and reproduce the legacy read by construction (a vacuous zero); (b) it compares only recompose-OWNED position venues (aave / sparklend / morpho-blue / erc4626 / pendle) — bare wallet-venue tokens and native ETH are recomposed stale BY DESIGN (not ledger-dirtied), so comparing them would surface that accepted staleness as noise every tick; (c) it excludes wallets with a position-surface flow in the ingester-lag window (ledger tip, anchor−64], the snapshot analog of the flow diff's tip clamp. Two residual, rare divergences remain, both expected and self-healing — recognize them in soak evidence rather than debugging them as recompose bugs: (a) a position changed in the last ~64 blocks by an otherwise-non-dirty wallet (unscanned this tick, so unattributable) — negligible at the small population shadow runs against (see the flip-before-scale note below), gone next tick; (b) a redemption-only snap_field on a wrapper valued through the DB share-rate fallback — a getter-less wrapper (reUSD), or a transient getter failure on exactly one side: the legacy pass resolves redemption rates per leg while recompose pre-resolves them minutes later in the same tick, and the mode-now fallback reads the LATEST token_yield_apy row, so a token-yields write landing between the two shows as a value_redemption-only drift for that tick (the same timing family the shared market context removed for prices; getter-backed wrappers are block-pinned and immune).

Mark coherence: one market context per tick. The cron builds exactly ONE loadMarketContext per tick, over the UNION of the read-pass accounting assets and the stored-leg assets recompose folds (both expanded via accountingAssetsOf, incl. PT underlyings), and every valuation in that tick — the legacy/dirty pass, the recompose pass (buildRecomposeSources takes the context INJECTED, never fetching its own), and therefore both sides of the parity diff — prices from that single object. This is the intra-tick form of the vintage-coherence principle: the price mirror is written concurrently by other refreshers, so two context fetches minutes apart inside one tick can see different bars, and the 2026-07-25 prod shadow soak showed exactly that — rows with identical qty_raw/index_raw/value_redemption drifting 0.003%–0.06% on value_market alone (plus one priced-vs-null split). With the shared context, identical qty_raw/index_raw yields a BIT-identical value_market on both sides, and a mid-tick mirror write can no longer split the passes; the 1e-9 parity tolerance is unchanged. Coherence is per-ASSET: in on mode, an asset surfacing ONLY in a promoted wallet's fresh re-read (requested by no dirty read and no stored leg tick-wide) is priced by one supplemental fetch merged ADD-ONLY into the tick context (mergeMarketContextAssets) — an already-requested asset, priced or honestly null, is never re-marked, so no asset can carry two marks in one tick while the promoted leg still gets a price instead of a one-tick null. The JIT positions fast path keeps its own per-request fetch — it has no tick to cohere with and is already coherent within its one refresh.

Flip criterion: ≥3 consecutive days of zero-diff shadow ticks on staging (all of flows_missing / flows_extra / flows_field / snap_mismatch zero — NULL snapshot counters mean the diff was skipped and the tick is NOT clean; expect 4 rows/day, and treat a missing row as a hole in the evidence, not a clean day), then flip staging to on; soak, then repeat on prod (which also starts shadow). Rollback at every step is the flag back to off — the legacy code paths remain intact through the soak, so a rollback is a config change with no data migration. Read the last N ticks with SELECT snapshot_ts, mode, flows_missing, flows_extra, flows_field, snap_mismatch, promoted FROM onchain_credit.portfolio_parity_runs ORDER BY snapshot_ts DESC LIMIT 12;.

Flip BEFORE scaling (ordering constraint). shadow keeps the LEGACY full-universe sweep authoritative and adds the dirty/recompose + ledger-flow re-derivation ON TOP — roughly double the tick time and peak memory, and it re-runs the very O(wallets × universe) sweep §1/ §2 calls the OOM/hard-fail at scale. So shadow is a PRE-SCALE / staging-volume verification tool: it cannot itself survive at 100k. The flag must be flipped to on (which drops the legacy sweep) while the eligible population is still small enough for the legacy authoritative sweep to complete — flip-before-scale, never leave shadow running as the population grows toward 100k.

Backfill from the ledger, JIT fast path, worker-pool drain (Phase C)

Phase C removes the last O(wallets) hot paths and the last archive-getLogs cost on the signup + request paths. Everything here is gated so off keeps today's derivation byte-identical — with one deliberate exception, the two-tier flow persist (basisprovisional + its two deletes), which is mode-independent because the JIT write it repairs is; the JIT fast paths are additionally OFF by default even in on mode (see the freshness gate below).

Backfill discovery from the ledger (D1). When PORTFOLIO_LEDGER_MODE is not off AND event coverage is CERTIFIED back to the segment start for every stream a detection call reads (each open-group position token live in event_coverage, plus the morpho/aave-pool/spark-pool singletons — and fluid-nft when the factory transfers are swept, i.e. only the probe), detectFlowSegment derives the wallet's flows/activity from raw_events by SQL (ledgerTransferFlows / ledgerMorphoFlows / ledgerLiquidationFlows / ledgerFluidFactoryTransfers) instead of an archive getLogs sweep. The ingester's 64-block margin means the ledger tip trails head, so the ledger serves [from, ledger-tip] and a SMALL chain residual sweeps (ledger-tip, segment end] — so the OUTPUT is IDENTICAL to the chain path (the deep-history bulk moves to an indexed SQL query; only the last ~100 blocks touch chain). Bare wallet-venue tokens are never ledger-covered (plan §3.2), so they stay a wallet-filtered chain scan in BOTH paths. If ANY part of a segment is not certified, that segment falls back to the legacy chain sweep and logs [backfill] ledger-coverage fallback.
Grid replay parallelism (D2, memory-bounded). The per-grid-point archive reads run with bounded concurrency (BACKFILL_GRID_CONCURRENCY, default 6) through a SLIDING-WINDOW pipeline (streamWithConcurrency): at most the window is live at once, and each completed point is FLUSHED to the replay's open transaction in strict ascending grid order as soon as it is next in line, after which its rows and read intermediates are released. Memory is O(concurrency window), not O(grid × legs) — the predecessor buffered the whole grid before writing, which OOM-killed a 76-leg wallet's ~112-point replay even at a 6 GB heap. Write order stays DETERMINISTIC and the lock/transaction/delete+insert contract is identical (one atomic transaction per fresh replay / per gap segment; BOTH paths lazy-open at the first flush, so the global write lock is held from the first flush through commit and never across an unread grid or a failing first point's retry cycle — see write-lock.ts for the full hold-shape contract); a point's strict read throwing (M9) rejects the pipeline and rolls the transaction back — nothing written, exactly the buffered abort. Month-partition auto-create runs on the pool BEFORE the transaction (post-067 CREATE TABLE … PARTITION OF takes an exclusive parent lock, which must never ride the minutes-long streamed transaction where it would block every /portfolio read until commit). Per-point venue isolation is unchanged, and the replay logs one rss/heapUsed line per 10 flushed points so whale onboarding is observable.
Worker-pool drain (D3). drain-portfolio-backfills.ts runs BACKFILL_WORKERS (default 4) concurrent wallet backfills. The workers race on the same atomic FOR UPDATE SKIP LOCKED claim, which hands each a DISTINCT oldest row, so no two workers ever run the same wallet (and approximate FIFO holds). The queued→running→done/empty/error lifecycle, reclaim budget, parking, and the shared writer lock are unchanged; a slow child now occupies one worker instead of blocking the whole minute. STALE_RUNNING_MS (60 min) still exceeds CHILD_TIMEOUT_MS (30 min), so a live child is never reclaimed.
JIT fast paths (D4), on only. Gated by JIT_LEDGER_LAG_BLOCKS (default 40) measured against the anchor (head). Because the ingester's tip trails head by ≥ 64, the default 40 keeps BOTH JIT ledger paths OFF (the JIT stays on the fresh full read + chain mini-scan — no regression); an operator raises it above ~64 to opt in once the cron has proven on. (a) FLOWS: when fresh + certified, the mini scan's ledger-owned surfaces derive from raw_events (bounded at the tip; the residual lands on the next 60s refresh — the flow persist is an idempotent upsert, never a delete, so nothing is lost). Else today's chain mini-scan runs unchanged (a fallback, never a silent skip). (b) POSITIONS: when the wallet has NO ledger event since its last snapshot AND holds no fluid / bare-variable-rate leg AND every shared recompose source resolves, positions are served via Phase B's recompose (mode now, aggregator-mid tier, anchor stamped) instead of a full read; ANY miss → full read; force:true always full-reads. The 60s cache, coalescing and LiveResult shape are unchanged.
Wallet-venue scan chunking (D5). scanTransferFlows / scanMorphoFlows / scanLiquidationFlows chunk the padded wallet OR-array at WALLET_TOPIC_CHUNK (default 500) per getLogs pass (disjoint chunks → dedup is inherent) — the last OR-array ceiling, closed for the off/shadow legacy scans. Changes call COUNT, never results.
held_pts from the JIT + backfill (D6). The JIT flow writer and the backfill/gap-patch writers now upsert portfolio_held_pts from their written pendle/PT rows (the same ON CONFLICT DO NOTHING the cron uses), so a matured PT whose only presence is a JIT or backfilled history stays in loadPendleMarkets' held set without waiting for the cron.
Coverage tripwire (D7). In a non-off mode, after the snapshot writes, the cron flags any WRITTEN leg whose ledger stream/token lacks a live event_coverage row (a new reserve/vault/PT the ingester has not caught up) and POSTs one aggregated, deduped Telegram alert (the alertUnknownAssets transport, prefix LEDGER COVERAGE GAP). Fail-soft.
Partitioning (D8): both history tables are partitioned, by different keys, for the same reason (per-wallet read locality at the 100k target — never retention; neither table is ever pruned). portfolio_position_snapshots is repartitioned monthly by snapshot_ts (migration 067, -- DESTRUCTIVE, manual — see docs/deployment.md), and the writers auto-create future month partitions (ensureMonthPartitions, a safe no-op before the migration). portfolio_flow_events is repartitioned BY HASH (wallet), MODULUS 16 (migration 072, also -- DESTRUCTIVE/manual): wallet is already the second PK column, so the PK, all three ON CONFLICT targets and every writer are unchanged, and each hot flow statement prunes to one partition. Its 16 partitions are created once by the migration (a hash modulus is fixed at CREATE time), so the flow writers take no partition step at all and ensureMonthPartitions refuses any non-RANGE parent. Monthly range partitioning of the flow ledger was designed and rejected (its PK lacks a timestamp, adding one breaks the conflict targets, and a month key prunes no hot flow read) — see the migration headers and docs/database.md.

`scripts/run-cron.sh`

The single cron wrapper (scripts/run-cron.sh <script.ts> [args...]). It:

cds to the repo root and sources .env.local (so cron jobs get the same env as the app).
Takes a non-blocking per-script flock on $LOG_DIR/$(basename "$DIR")-<script>.lock, keyed by the checkout (the repo-root basename) so the prod and staging working copies on the shared box never serialize against each other; two runs of the SAME script in the SAME checkout do. If the lock is held (a previous tick still running), it logs "already running ... skipping this tick" and exits 0 — a skipped tick is normal, not a failure. The lock releases automatically when the process exits (fd 9 closes), so a crash never wedges it.
Runs the script through the repo-local tsx (node_modules/.bin/tsx), capturing the real exit code (via rc=$?, kept off set -e) so cron still sees the script's true pass/fail.
Tees combined stdout/stderr to /tmp/onchain-credit-cron/<script>.log, prepended with a timestamp banner each run.
On a non-zero exit, best-effort alerts a Telegram bot (WS8; env ALERT_TG_BOT_TOKEN + ALERT_TG_CHAT_ID, both unset = silent skip). The message names the checkout (ALERT_ENV override, else the repo-root basename — prod and staging share the box, so it must say which failed), the script, the exit code, and a why: block: the wrapper greps this run's slice of its own logfile (everything after the last run banner) for the failure markers the jobs print — [fail], [partial], .../fail], fatals, 429/5xx — and includes the newest few, capped so a pathological run cannot exceed Telegram's message limit. (It long claimed exit-code alerting "cannot see log lines"; it writes that logfile, so it can.) The block runs with errexit off and a time-bounded curl, so alerting can never change $rc (re-exited unchanged) or wedge the wrapper; a failed POST is logged as "ignored". ALERT_TG_API_BASE overrides the endpoint for local testing (prod uses the Telegram default). This covers every cron job, not only the portfolio one.

Extra args are forwarded, e.g. a SOFR backfill:

bash

/opt/onchain-credit/scripts/run-cron.sh refresh-sofr.ts --since=2018-04-03

To run any refresher by hand on the box, use the same wrapper so env + tsx resolution match cron exactly:

bash

/opt/onchain-credit/scripts/run-cron.sh refresh-assets.ts
/opt/onchain-credit/scripts/run-cron.sh refresh-vault-capacity.ts

Deploy does NOT run refreshers

The production deploy (.github/workflows/deploy.yml, triggered on every push to main) is code only. The GitHub runner SSHes to root@dexhq.io, does git fetch + git reset --hard FETCH_HEAD, npm ci, npm run build, pm2 restart onchain-credit --update-env (rolling back to the previous commit if the build fails). It does not run DB migrations, backfills, or data refreshers.

So when a change needs new or backfilled data, that is a manual server step after the deploy:

bash

# on the box, after the deploy lands
psql -d creddit -f /opt/onchain-credit/scripts/sql/0NN-whatever.sql   # migration, if any
/opt/onchain-credit/scripts/run-cron.sh refresh-<thing>.ts            # or a backfill

ISR re-prerender caveat

Pages are App Router with per-page ISR (revalidate 1800s on home / repo-lending / multi-strategy-funds, 3600s on asset-profiles / carries), prerendered at build time. A page only picks up freshly written DB rows on its next revalidate cycle. So if you run a refresher (or backfill) after a deploy's build has already prerendered the pages, the new data can be invisible for up to the revalidate window. To force it immediately, re-run the deploy (or otherwise rebuild) so the pages re-prerender against the now-current DB.

Backfill scripts

scripts/backfill-*.ts are one-off history-seeding jobs (not crons): seeding token_yield_apy for a newly added wrapper, reconstructing deep Fluid history from core-storage reads (backfill-fluid-core.ts, backfill-fluid-history.ts), repairing a gap (backfill-recent-gap.ts), etc. They reuse the same annualizeRatio math and (where possible) the same *ForSnapshot functions as the live refreshers, so backfilled history is methodologically identical to live data.

scripts/backfill-portfolio-wallet.ts is the exception that is BOTH a queued cron job and a manual tool: it replays ONE registered wallet's 90-day history (basis='backfill') and is the portfolio repair path (windowed delete+insert). See Registration backfill (WS5). It must run with ETHEREUM_ARCHIVE_RPC_URL set (publicnode rejects archive eth_call); the cron spawns it with that env automatically.

Run them through the same wrapper:

bash

/opt/onchain-credit/scripts/run-cron.sh backfill-wsteth.ts
/opt/onchain-credit/scripts/run-cron.sh backfill-fluid-core.ts

DefiLlama backfills must use batchHistorical. Per-timestamp historical price calls get rate-limited (HTTP 429) at backfill scale. The DefiLlama-backed backfills (e.g. backfill-token-basis.ts) encode {coin: [ts, ...]} and hit https://coins.llama.fi/batchHistorical so each request covers many timestamps at once. Do not loop per-timestamp.

Adding a deposit asset to the Repo lending page

Three edits, in this order, then one backfill:

Rate history. Add the reserve to AAVE_V3_TOKENS / SPARKLEND_TOKENS (scripts/refreshers/{aave-v3,sparklend}.ts) with its real decimals. Fluid needs nothing: fluid-ll.ts enumerates every Liquidity Layer token already, so an asset there has history from 2025-05-21 on day one.
Underwritten capital. Add it to STABLES (scripts/refreshers/shared.ts) with the poolVenues it is actually a reserve on. That one constant drives both the Fluid and the Aave/Spark exposure writers.
The page. Add it to MoneyMarketAsset + MONEY_MARKET_ASSETS + ASSET_DECIMALS + ASSET_ADDRESS in the CLIENT-SAFE src/lib/data/money-market-assets.ts (NOT the reader: a value imported from money-market-rates.ts pulls Postgres into the client bundle), then add its venue rows to STATIC_MARKETS in src/lib/data/money-market-rates.ts. Register its coin in src/components/icons/token-marks.tsx, give each new market id a colour in MARKET_COLOR (MoneyMarketRatesChart.tsx; the fallback palette is position-indexed and recolours when filters change), and widen REPO_LOAN_ASSETS in scripts/morpho-rule.ts if isolated markets on the asset should be admissible. The tests in money-market-rates.test.ts, token-marks.test.ts and morpho-rule.test.ts fail on a missing decimals entry, a missing coin, or a loan-asset gate that has drifted from the tabs.

Then seed the reserve's history, which is the only manual step:

bash

/opt/onchain-credit/scripts/run-cron.sh backfill-usds-reserves.ts

backfill-usds-reserves.ts walks every 6h snapshot from 2025-05-21 forward and calls the SAME refresh*ForSnapshot the live crons use, with a ["USDS"] allow-list so it re-reads one reserve instead of all 30+. The walk must run forward in time: each snapshot's supply_apy_24h anchors on the row at-or-before ts − 24h, so a backward walk would null the first day of every run. Both venues are attempted per timestamp and isolated from each other, so a transient failure on one does not punch a hole in both series.

Decimals are the trap here. fluid_ll_apy stores its size columns as RAW token amounts, so the divisor belongs to the token. USDS and GHO are 18-decimal against USDC/USDT's 6; a shared constant would report a $730M book as $730 trillion with no error anywhere. fluidScale() reads ASSET_DECIMALS, and the test asserts each entry against the token's real address and decimals().

Adding a token to `BASIS_TOKENS`

Every registry entry carries a required basisClass: "pinned" | "market" (pinned-basis-class-plan.md; see metrics M19). There is no implicit default and no allowlist escape: the coverage test fails the build on any tracked wrapper (BASIS_TOKENS or MIRROR_EXTRA in dune.ts) with no declared class. Use "pinned" ONLY for a wrapper redeemable instantly, permissionlessly, fee-negligibly and capacity-unbounded into the book numeraire (or a real 1:1 PSM) — sUSDS is the only one today; every border case defaults to "market" (the honest side). A pinned token's market_price_usd is written equal to its redemption_value_usd with basis = 0 go-forward (never a historical rewrite), and it is skipped from the Market Depth chart in favour of the pinned note.

The 6h token-basis refresher iterates BASIS_TOKENS, so a new entry starts snapshotting on the next tick with no backfill. History is the manual part:

bash

# START at/before the token's launch — the run only writes what DefiLlama serves.
/opt/onchain-credit/scripts/run-cron.sh backfill-token-basis.ts 2024-09-01T00:00:00.000Z --only=sUSDS

Always pass --only. A bare re-run re-fetches every price from DefiLlama and re-derives rows that already exist, so an upstream price revision would silently move published history; --only scopes the write to the new token and leaves other tokens' stored rows untouched. An unknown symbol is a hard error rather than a no-op, because a backfill that writes nothing looks exactly like one that succeeded.

--only does not protect the scoped token's own rows: the upsert is ON CONFLICT (snapshot_ts, token_address) DO UPDATE, so every timestamp in range is re-derived from freshly fetched prices. On a token whose history is already published, re-running from launch silently rewrites the series behind figures the docs and the panel already quote. That is fine on a token being backfilled for the first time (nothing to rewrite) and is why the sUSDS run below was safe: it had 3 refresher rows. On an established token, scope START to the range you actually mean to rebuild.

Run it in the same change that adds the token — the 30-day floor expires.MIN_HISTORY_DAYS = 30 in basis.ts hides any leg with under 30 days of history, so a freshly tracked token first renders /carries Market depth's "no secondary-market history is tracked for this pair yet". That is what hid sUSDS on morpho-susds-usdt for three days (tracked 2026-07-14, backfilled 2026-07-17).

The floor is not a standing guard, and this is the part that bites: historyDays is (Date.now() - sample_start) / 86_400_000 where sample_start is the oldest row in the last 365 days. The 6h refresher writes a row every tick, so historyDays grows on wall-clock alone. Skip the backfill and the leg does not stay hidden — at day 30 the gate simply opens, and the panel starts publishing worstAdverse1y and a chart labelled one year from a month of rows. An un-backfilled token fails loudly for 30 days and quietly thereafter, so "it's still hidden" is not evidence the backfill is outstanding, and the invisible window is the only period in which the omission is obvious.

Pick START from the token's launch, not from a recent date. The skipped (token, snapshot) count is expected and benign: the grid spans every basis token's timestamps, and a snapshot with no price (or no share_rate) for the selected token is skipped, so a run that writes 1,394 rows and skips 1,342 is healthy. A too-recent START silently costs history — it cannot be told apart from a source limit by looking at the result.
Do not audit coverage by dividing rows by 4. The grid is not a uniform 6h series: sUSDS is daily before ~2025-05 and 6h after, so its 1,394 rows are 431 distinct days, not 349. Count DISTINCT snapshot_ts::date and list the gaps (lag(snapshot_ts)) instead.
A superseded note here claimed DefiLlama "serves no historical price for sUSDS or sUSDe before roughly 2025-08-25" and that a re-run could not recover earlier months. Both were wrong, and it was the START (then 2025-06-01) that bounded the data: re-running sUSDS from 2024-09-01 recovered 2024-10-10 onward, and sUSDe has held rows back to 2024-03-06 all along. Verify a suspected source limit against the table before writing it down. What is real for sUSDS is a 202-day hole (2025-02-05 → 2025-08-25) where DefiLlama serves no price while share_rate stays continuous; that hole, not a coverage floor, is why its 1y stats read ~326 days.

Rebuilding `token_basis` from the mirror (shadow → parity → swap)

The 6h refresher now sources token_basis market quotes from the Dune mirror (the same-bar rule above), but the SERVED history was written by the old DeFiLlama path. scripts/backfill-token-basis-shadow.ts re-derives that history from mirror bars and promotes it, guarded by a parity review. This is transient tooling, not a numbered migration, and it re-uses the refresher's exact computeBasisFromBars, so the rebuilt series matches the live tick by construction.

Shadow backfill. Re-derives the full series into onchain_credit.token_basis_dune_shadow (created on first run, mirroring 020-token-basis.sql) at every 6h grid point over the MIRROR WINDOW (the first mirrored bar to now), for every BASIS_TOKEN. Idempotent + resumable (auto-resumes from the shadow's latest snapshot; --from=ISO bounds the low end, --full rebuilds from the window open after adding a token). Reads only token_price_bars + token_yield_apy (no Dune credits).
bash
```
/opt/onchain-credit/scripts/run-cron.sh backfill-token-basis-shadow.ts
```
Parity report. Compares the served token_basis to the shadow, per token, over the overlap window, and writes a markdown + a JSON file. It presents numbers only, with no pass/fail threshold (the reviewer judges): (a) 6h step-noise stddev old vs new + ratio (expect ~30-50bps → ~1-3bps for majors) — the stddev differences only snapshot pairs EXACTLY 6h apart, skipping and counting wider gaps (daily pre-2025 history, refresher outages) so a 24h level change never inflates the noise figure; (b) mean + max absolute level offset; (c) the top-5 deepest old-series discounts, each tagged whether the new series confirms a same-sign discount within ±1 snapshot (survival) or not (an old-feed artifact candidate); (d) row coverage. Stats are the tested-pure helpers in src/lib/data/basis-shadow.ts.
bash
```
/opt/onchain-credit/scripts/run-cron.sh report-token-basis-parity.ts \
  --md=/tmp/token-basis-parity.md --json=/tmp/token-basis-parity.json
```
Swap (destructive, review-gated). After the report is approved, promote the shadow in ONE transaction. Boundary: token_basis history predates the mirror window (some tokens back to 2023); the swap DELETEs only the served rows AT-OR-AFTER the shadow's earliest snapshot whose token is a shadow token, then INSERTs the shadow. Every row strictly BEFORE the boundary, and every token the shadow does not cover, survives untouched. The boundary ts + affected-row counts print before the transaction runs. It REFUSES a shadow that is empty or spans < 300 days, AND runs a per-token density gate: it prints a per-token deleted-vs-inserted table and refuses if any token's inserted count is under 90% of its deleted count (a single token gutted by a mirror gap that the aggregate counts would hide). Override a specific token with --allow-coverage-loss=<token,...> (symbols or lowercase addresses).
bash
```
/opt/onchain-credit/scripts/run-cron.sh backfill-token-basis-shadow.ts --swap --yes-i-reviewed-parity
# add --allow-coverage-loss=<token,...> only for tokens the parity review accepts
```
Staging reseeds from prod nightly, so run the durable swap on prod (with permission), and re-verify the /carries Market depth 1-year extreme markers by CDP screenshot afterward (client-rendered — never HTML grep).

Repairing stored portfolio marks from the mirror (guarded re-mark)

The valuation switch (Token price bars (Dune mirror), Milestone C) makes NEW portfolio marks coherent, but the marks ALREADY stored in portfolio_flow_events.value_market and portfolio_position_snapshots.value_market were written by the old DeFiLlama path and carry the ±30-50bps of cross-vintage noise (the phantom-wallet −0.82 ETH entered basis, §1 of the plan). Two guarded repair scripts re-mark those stored value_market columns FROM THE STANDING MIRROR and nothing else.

Both scripts re-derive value_market = amount × priceInBook(asset, book, ts) where amount is the ALREADY-DESCALED stored column (amount_underlying for flows, qty_underlying for snapshots), so the re-mark reproduces the exact composition valueFlows / buildSnapshotRow used, venue-uniformly (the Aave rayMul / ERC-4626 index / Fluid leg / Morpho descaling is already folded into the stored amount). The price is read from token_price_bars only (the hourly newest-common-bar ≤ the row's ts, same-bar division) — pure DB reads, ZERO Dune credits, no fetch-through (a scattered historical minute is uneconomical; the ~1-3bps of hourly-vs-minute staleness is within the phantom acceptance band). After the standing read, standingMirrorMarks applies the same Phase-1 enrichment as the live path (mark-enrichment.ts: stETH/eETH derived from wstETH/weETH; non-traded vault shares composed off their underlying) so a repaired historical mark matches live. Each repair row carries its stored block_number; rows are grouped by (timestamp, block_number), composition reads the newest share rate at or before that block, and no timestamp fallback is used when the block-anchored rate is absent. Derived stETH/eETH instead use the newest wrapper share-rate observation at or before the actual selected wstETH/weETH mirror bar_ts, matching the normal valuation path and preventing cross-time division. The pure re-derivation + diff logic is scripts/repair/remark-lib.ts (unit-tested in remark-lib.test.ts).

Locked rules (do not reopen):

value_redemption is NEVER touched — it is block-precise and verified-correct (§1).
A row with no bar / no common bar at its ts is LEFT UNTOUCHED, logged, and counted — never zeroed, never partially derived (the 2026-06-25 lesson: a partial repair must not manufacture values).
Excluded from the re-mark: kind='liquidation' rows (composite equity-destroyed value, M4); PT-family rows (plan §7 out of scope — venue pendle, or an Aave/Spark reserve whose underlying is a known PT); WETH / ETH-sentinel identity legs in the ETH book (market ≡ amount); book='EXCLUDED' (no book unit); rows with a null stored amount.
Idempotent: re-deriving an already-repaired row yields the same value (same stored amount × the same standing bar), so a second run writes nothing.
Writes run as batched UPDATEs (≤1000 rows/statement) inside ONE transaction that holds the portfolio writer advisory lock (write-lock.ts), serialising against the live cron / JIT / backfill writers. The scripts NEVER TRUNCATE or DELETE.

Ops flow (staging first, then prod with permission):

Dry-run (the default — no --execute). Prints per-wallet + per-asset repaired-row counts, the sum of |Δ value_market|, the 10 largest |Δ| rows (wallet, ts, asset, old, new), the projected entered-basis shift per wallet (flows), and any skipped-no-bar assets (a possible mirror coverage gap). Review these before writing.
bash
```
/opt/onchain-credit/scripts/run-cron.sh repair/remark-flow-marks.ts
/opt/onchain-credit/scripts/run-cron.sh repair/remark-snapshot-marks.ts
```
Wallet-targeted mode (--wallet=<addr>) scopes both the dry-run and the execute to one wallet — used for the phantom-wallet acceptance check before a global run.
bash
```
/opt/onchain-credit/scripts/run-cron.sh repair/remark-flow-marks.ts --wallet=0xef08c6a47c04764b9c0964e00bd01c3e200707c5
```
Execute (--execute) performs the batched UPDATEs under the writer lock. The snapshot re-mark re-levels the stored basis / PnL chart series across the whole history — the charts will show less wiggle. That is the point (the DeFiLlama noise is deleted); communicate it. A [fail]-bracketed line + non-zero exit on any DB/derivation error pages the run-cron Telegram alert.
bash
```
/opt/onchain-credit/scripts/run-cron.sh repair/remark-flow-marks.ts --execute
/opt/onchain-credit/scripts/run-cron.sh repair/remark-snapshot-marks.ts --execute
```

Acceptance: the phantom wallet's entered basis reads +0.05 ETH ± 0.02 via the real /api/portfolio/* path (POST refresh before GET), a random sample of wrapper flow rows re-verifies against on-chain pool reads at their blocks within a few bps, and a levered wrapper position's stored basis chart is visibly de-noised (before/after screenshot pair).

`backfill-fluid-dex-pershare.ts` (per-share pot content)

Fills the six per-share / pool-price columns (migration 047) on fluid_dex_apy rows written before the refresher read them. Every row already carries block_at_snapshot, so there is no timestamp-to-block resolution: it re-reads getDexState(pool) at that exact block and stores the raw words.

bash

/opt/onchain-credit/scripts/run-cron.sh backfill-fluid-dex-pershare.ts
/opt/onchain-credit/scripts/run-cron.sh backfill-fluid-dex-pershare.ts --pool 0x3c04…cda7
/opt/onchain-credit/scripts/run-cron.sh backfill-fluid-dex-pershare.ts --force   # re-read non-NULL rows

Per-pool floor. The DEX resolver (block 23,881,747, Dec 2025) is newer than most pools and returns EMPTY returndata below its deployment, so the script binary-searches each pool's first decodable block and skips every row beneath it. Those rows stay NULL forever, which is a property of the resolver, not a gap to repair; the per-pool report prints the floor and the below-floor count so the coverage is stated, never silently truncated.
Idempotent + resumable. Rows are selected on token0_per_supply_share IS NULL, so a re-run only touches what is still missing.
Retries are load-bearing. getDexPerShareState THROWS on an RPC failure and returns null only for genuinely empty returndata. Conflating the two would let a transient 429 push a pool's floor forward and permanently strand readable snapshots, so a failed probe retries with backoff and, if it still fails, the pool is skipped rather than floored.
Order on prod: migration → deploy → run the backfill → check the per-pool report. Staging reseeds from prod nightly, so the durable run is the prod one.

Token price bars (Dune mirror)

onchain_credit.token_price_bars (migration 054) is a local mirror of Dune USD price bars — the historical market-price source the mark pipeline reads once the Dune price-mirror refactor's later phases land (docs/plans/dune-price-mirror-plan.md). The STANDING data is hourly (prices.hour, bars on the hour); narrow exact 5-minute bars (prices.minute) are layered in only where a flow is trued up (the cost model — a prices.minute window execution scans the whole partition). The point: a portfolio mark and a token_basis row divide two quotes from the same bar, so the fast ETH/BTC USD level cancels exactly and only the slow secondary-market basis survives — deleting the ±30-50bps of cross-vintage DeFiLlama noise that produced the phantom-wallet entered-basis error. A mark reads the newest common bar ≤ its ts (hourly by default, exact-minute where trued up), within ~1-3bps of basis drift in calm markets. Phase A ships the table empty in prod; it is filled by the bulk load + the 6h sync.

Schema. (chain_id, token_address, bar_ts) PK; price_usd NUMERIC CHECK (> 0); source — the true query provenance stamped per row, dune:prices.hour (standing / window bars) or dune:prices.minute (exact-minute true-up); ingested_at. chain_id 1 = ethereum (every ERC-20, incl. the WETH denominator), 0 = the bitcoin reference row (the BTC-book numeraire, normalized to a fixed sentinel address). bar_ts is the bar START (UTC) — hourly bars on the hour, true-up bars on the 5-min grid; a DB CHECK enforces 300s alignment. See Database & schema.
Client + dual queries. src/lib/data/dune.ts — plain HTTP: execute a saved parameterized query → poll (reading execution_cost_credits free) → page (32k/page) → INSERT ... ON CONFLICT DO NOTHING. A prices.minute WINDOW execution scans the whole time partition (~110-170 credits/month), so windows use a HOURLY query (DUNE_QUERY_ID_HOURLY, query 8033816, prices.hour, bars on the hour, cost sublinear in span) and only narrow flow true-up uses the EXACT-MINUTES query (DUNE_QUERY_ID_MINUTES, query 8033818, syncExactMinutes, ≤6h clusters). Reads take the bar covering a ts (bar_ts = floor(ts/300)*300), walking back to the newest bar within BAR_STALE_HARD (48h) and returning the bar's actual bar_ts so a caller can flag staleness. A served bar older than BAR_STALE_SOFT (6h) is still coherent (same-bar pairing holds at any age) and still served — a real, stored, coherent bar beats a null — but getBarSeriesAt logs one line per run (token count + max age) so a lagging mirror is visible in ops (M20). Beyond 48h the read misses and null takes over.
Sync cadence. The token-basis refresher prepends an incremental HOURLY syncBars(mirrorTrackedTokens(), MAX(bar_ts), now) on its existing 6h grid (~0.25 credits/run, ~30/month); no crontab change. A >48h gap is CLAMPED to the most recent 48h (fresh bars for the snapshot); the older gap is caught up by re-running the bulk tool, never the cron.
Fetch-through. A read miss beyond the 48h walk-back triggers ONE HOURLY syncBars for a ±1h window (cheap floor), then re-reads; a second miss returns null (M9 — an honest null, never a fabricated mark, never a thrown valuation).
Append-only. A stored bar is FROZEN (ON CONFLICT DO NOTHING), so a Dune history restatement cannot move it — a stable audit trail. Migration 055 makes this structural by REVOKEing UPDATE, DELETE from the app role(s) (the schema-wide arwd default privilege had re-granted them despite 054 withholding UPDATE); the postgres owner keeps them for a deliberate operator purge.
Bulk load. scripts/backfill-token-price-bars.ts — ~1 year of hourly bars in ONE execution (allowLongWindow, ~16 credits; --halves splits into two if it pages awkwardly), idempotent + resumable (skips a span whose WETH bars ≥ 0.9 × window hours), hard-aborts a chunk that returns zero rows for all tokens or a credit-guard stop. [fail]-tagged failure lines + a non-zero exit page the cron alert. Older-than-preload history + exact flow minutes resolve later via fetch-through / syncExactMinutes, so the window is an optimization, not a correctness boundary.
Tracked set. mirrorTrackedTokens() in dune.ts is the raw Dune sync set = BASIS_TOKENS addresses (incl. the newly added DAI par row) ∪ MIRROR_EXTRA_TOKENS (the rate-source wrappers that are not carry legs) ∪ WETH, minus Phase-1 derived bases and composed non-traded wrappers; the bitcoin reference rides the saved query unconditionally. stETH/eETH consume wstETH/weETH bars, and iETHv2/yoETH/yoUSD/ fLiteUSD/yvUSD consume their underlying's bars, so empty raw slots are not requested. marketMarkTrackedTokens() is the wider logical coverage set used by mirror-coverage.test.ts: every market-class base must have an honest mark path, while explicitly composed wrappers are exempt from having their own bar.
Read-only consumers (no credits). GET /api/portfolio/prices serves the signed-in Portfolio's ETH/BTC ≈ $X display annotation from the newest WETH (chain 1) and BTC reference (chain 0) bars. It is hit on every signed-in page load, so it calls readBar ONLY — the pure, indexed LIMIT 1 DB read with no deps and no network path. It must never import a batched entry point (getBar, getBarsAt, getBarSeriesAt, or loadMirrorMarks / priceInBookFromMirror / loadMarketContext above them), all of which fetch through to Dune on a miss and would put page traffic on the credit budget. It therefore does not count against the ~20/day fetch-through alert below. A miss is served as null (M9); a bar past BAR_STALE_SOFT logs a warning (M20).
Budget guard. The sync is 1 execution/6h regardless of traffic; a per-process credit guard (DUNE_MAX_CREDITS_PER_RUN, default 50) stops issuing once a run reaches it (returns partial). The exact-minutes query costs by calendar SPAN, not count (~1.5 credits clustered ≤1h, ~22+ scattered over a year), so it is clustered ≤6h. Alert if fetch-throughs exceed ~20/day (a coverage gap — fix the tracked set). Keyless (DUNE_API_KEY / the relevant DUNE_QUERY_ID_* unset) degrades to a logged skip + DB-only reads.
Credits / ToS. ~30-60/month steady vs the ~2,500/month quota. The credit model retains query results in our DB, so confirm the Dune API terms permit that (one-time check) — see External dependencies.

Table conventions (agent-grade)

New tables follow the conventions piloted by the lending-positions migration (scripts/sql/026-lending-positions.sql): canonical keys (chain_id + lower-cased addresses; symbols are display attributes), block-anchored rows (block_number/block_at_snapshot alongside snapshot_ts), current-state tables (*_current) separated from snapshot history, and an explicit basis column stating the methodology of derived rows. DDL for every table lives in scripts/sql/001..043-*.sql. See Database & schema for the full table list and reader-side details.

Metrics: how & why — the annualizeRatio APY convention and per-metric formulas.
Database & schema — table-by-table schema and the reader functions.
Architecture — overall system shape.
Operational processes — how the refreshed series feed the carry screener and the Fluid vault registry.

Data pipeline & refreshers ​

The read-only-app principle ​

Where the data comes from ​

Scheduled refreshers ​

Sub-refreshers inside refresh-assets.ts (the 6h "assets" job) ​

refresh-portfolio.ts (the 6h portfolio job, WS4) ​

Registry sync — sync-portfolio-tokens.ts (T6) ​

Registration backfill (WS5) ​

Event ledger ingester (portfolio 100k scale) ​

backfill-event-ledger.ts (one-time historical sweep) ​

Ledger-driven cron (Phase B) ​

Backfill from the ledger, JIT fast path, worker-pool drain (Phase C) ​

scripts/run-cron.sh ​

Deploy does NOT run refreshers ​

ISR re-prerender caveat ​

Backfill scripts ​

Adding a deposit asset to the Repo lending page ​

Adding a token to BASIS_TOKENS ​

Rebuilding token_basis from the mirror (shadow → parity → swap) ​

Repairing stored portfolio marks from the mirror (guarded re-mark) ​

backfill-fluid-dex-pershare.ts (per-share pot content) ​

Token price bars (Dune mirror) ​

Table conventions (agent-grade) ​

Related docs ​

Data pipeline & refreshers

The read-only-app principle

Where the data comes from

Scheduled refreshers

Sub-refreshers inside `refresh-assets.ts` (the 6h "assets" job)

`refresh-portfolio.ts` (the 6h portfolio job, WS4)

Registry sync — `sync-portfolio-tokens.ts` (T6)

Registration backfill (WS5)

Event ledger ingester (portfolio 100k scale)

`backfill-event-ledger.ts` (one-time historical sweep)

Ledger-driven cron (Phase B)

Backfill from the ledger, JIT fast path, worker-pool drain (Phase C)

`scripts/run-cron.sh`

Deploy does NOT run refreshers

ISR re-prerender caveat

Backfill scripts

Adding a deposit asset to the Repo lending page

Adding a token to `BASIS_TOKENS`

Rebuilding `token_basis` from the mirror (shadow → parity → swap)

Repairing stored portfolio marks from the mirror (guarded re-mark)

`backfill-fluid-dex-pershare.ts` (per-share pot content)

Token price bars (Dune mirror)

Table conventions (agent-grade)

Related docs