Database & schema

Reference for the onchain_credit Postgres schema. The app is read-only against this schema; every row is produced by an off-chain job in scripts/ (see Data pipeline). This page documents every table, the migration workflow, and the cross-cutting conventions.

For where each number comes from and the cron cadence, cross-reference Data pipeline; for the math, Metrics: how & why; for the high-level orientation, Architecture.

Where it lives

• Prod: database creddit, schema onchain_credit, role onchain_credit. Local Postgres on the Hetzner box (ssh root@dexhq.io). Daily backup via scripts/ops/backup-creddit.sh at 02:15 UTC.
• Staging: a separate database creddit_staging, role onchain_credit_staging, served by the onchain-credit-staging pm2 process on :3002. It has its own (older, ad-hoc) refresh state. Known gaps: no automated staging deploy, the staging.creddit.xyz vhost is not symlinked into sites-enabled, and the staging DB refresh cadence is not wired to cron.

The core APY convention

Every trailing APY column in this schema (supply_apy, borrow_apy, apy_24h, apy_30d, fee_apy_usd, …) is computed by annualizeRatio in src/lib/data/apy.ts: the realised ratio of an on-chain compounding index between two blocks, annualised by the actual elapsed time. We never average per-snapshot annualised rates. The same method runs across every venue, so /carries and /repo-lending compare like-for-like.

Agent-grade table conventions

New tables follow the conventions piloted by migration 026, aiming to serve both the app and a future agent-facing API:

• Canonical keys: chain_id + lower-cased addresses in the primary key; symbols are display attributes, not identity.
• Block-anchored: block_number / block_at_snapshot alongside snapshot_ts, so every derived value is reproducible at its read block. One documented exception: token_yield_apy.supply_apy can be rewritten after the fact by the silent-gap repaint, whose value depends on a publish up to seven days after the row's own snapshot_ts (a gap's length is unknowable until it ends). The row's share_rate and block_at_snapshot stay block-anchored and untouched, so anything needing a point-in-time reconstruction derives its own window from share_rate rather than reading supply_apy. See metrics.
• Current / history split: current-state tables are suffixed *_current and are replaced each refresh; snapshot history tables key on snapshot_ts and accumulate.
• Explicit basis column stating the methodology of derived rows (e.g. fluid_borrow vs position_collateral vs pool_collateral).

Older tables predate these conventions (e.g. assets, the Fluid/Aave/Spark APY tables key on (snapshot_ts, token_address) with no chain_id), and that is called out per table below.

Reading a chain-keyed table

Where a table is chain-keyed, the key has to be used: every statement against it names chain_id explicitly, and every row shape derived from it carries chain_id as a field. The chain is never left implicit in the SQL: it is always a bound parameter, even though that parameter's value still comes from a single module constant today, so the day a second chain lands the work is threading a chain argument through the call sites rather than hunting for the reads that never asked. The reason is that the omission has no symptom today: with one chain in the table, a read that filters no chain returns exactly the right rows, and it keeps doing so right up to the day a second chain lands, at which point it silently folds two chains' rows into one wallet's history. There is no error, no gap and no null to notice, so the first half of that rule is enforced at the source instead: src/lib/portfolio/chain-scoping.test.ts scans every statement embedded in the TypeScript under src/ and scripts/ (SQL and shell files are outside its reach) against raw_events, portfolio_flow_events_v2, portfolio_position_snapshots, event_coverage, chain_scan_cursors, chain_blocks, chain_scan_ranges, morpho_market_impairment, reward_distributors, token_lineage and portfolio_derive_jobs, and fails the build on one that does not. A new chain-keyed table joins that list in the same PR as its migration, before anything reads it, which is the only moment it is free.

The second half — the row shape carrying chain_id — is a convention, checked per module rather than by that scan, which by design refuses to credit a projection (see the next paragraph) and so cannot also demand one. Today ledger-scan.test.ts and historical-groups.test.ts assert the projection for the two modules that read these tables on the served path; a new module owes its own assertion. Read a green scan as "the filter is there", not as "the row shape is right".

Naming the column is not scoping to a chain. SELECT chain_id, … FROM raw_events WHERE block_number BETWEEN … reads a chain-keyed table and filters no chain, so the check reads chain_id in a predicate position, not anywhere in the statement. Nor does describing the filter count as writing it: a -- chain_id = $1 is applied by the caller line inside the SQL is a sentence, so the check blanks every -- comment out of a statement before reading it, under both this rule and the stream rule below. The single exception is a write whose only chain-keyed reference is its INSERT INTO target: there is no predicate to write and the insert column list is the compliance. An INSERT … SELECT that reads a chain-keyed table is a read and gets no exemption. The same distinction is applied per set-operation branch, so one branch of a UNION, an EXCEPT or an INTERSECT cannot vouch for the next. It is not applied per OR branch: a WHERE built of alternatives is one statement, and cutting on OR would split compliant statements into a piece naming the table and a piece carrying the filter.

A reference counts whichever keyword reaches it. FROM, JOIN, USING, UPDATE, TRUNCATE, MERGE INTO and INSERT INTO are all read as references, because a DELETE … USING another_chain_keyed_table is the same half-scoped read as a join and was invisible while the check knew only the first four. Since the next gap of that kind will be some keyword nobody listed either, the check also counts the qualified table names it sees against the ones a keyword reached, and reports a statement it cannot model rather than certifying the part it understood. An implicit comma join and a comma-separated TRUNCATE are the two shapes it catches that way today, and it errs toward flagging: a comma join with both sides scoped is reported too, and the fix there is to write the join as a JOIN rather than to weaken the rule. A TRUNCATE has no predicate to write at all, so it can only ever clear the rule through the declaration below, which is the intended outcome for the most destructive statement available against one of these tables.

And one predicate per table read, not one per statement. A read that joins two chain-keyed tables and scopes one of them folds every chain's rows of the second into one chain's rows of the first, so a statement owes as many chain comparisons as it makes chain-keyed references (its INSERT INTO target aside). A join condition a.chain_id = b.chain_id counts for both sides, since it puts both rows on the same chain, and demanding a literal filter per alias on top of it would only push authors toward a redundant predicate.

But consistency is not scoping, so at least one comparison has to name a chain. A join tied to itself on chain_id and to nothing else puts both rows on the same chain rather than on a nominated one: it reads every chain, correlated correctly, which is the same wrong answer as the unfiltered read in a tidier shape. So the count carries a floor. One comparison must be an anchor, meaning chain_id compared against something that is not itself a chain_id reference (today, always the bound chain parameter). And an anchor is the predicate as written, never its negation: chain_id <> $1 is every chain except one, and the same goes for stream <> 'transfers' under the second rule below, so neither counts.

A statement that genuinely must span every chain declares itself with a cross-chain: <reason> comment, which the same test reads. Which files may carry that marker, and how many statements each may excuse, is an allowlist asserted in the test, so a second exception is a reviewed edit rather than a comment absorbed into a large diff. There is one today: the user wipe in scripts/ops/reset-portfolio-users.ts, where a chain filter would delete the account graph everywhere while leaving another chain's per-wallet cursor behind, still certifying that wallet complete over rows the wipe has just removed.

The same test carries a second rule, which protects the served portfolio numbers while the ledger rebuild adds streams to raw_events: every raw_events read on the served path carries a literal stream predicate, so a newly ingested stream cannot join the live flow derivation by accident. The served path is src/ plus scripts/refreshers/ (whose dirty-candidate read decides which wallets each tick recomposes). src/lib/portfolio/derive/ and src/lib/portfolio/v2/ are outside that rule (they are not the served read path), and inside the derive tree an un-scoped read must declare itself with an unscoped-raw-events: <reason> comment.

Literal means a quoted name (stream = 'transfers', stream IN (…)) or the bound-array form stream = ANY($n::text[]) that the served liquidation read uses; a bare stream = $n does not count, because the set of streams such a read admits is not visible in the source, which is the property being kept. The bound-array form is admitted on the strength of that one site, whose array is a module constant pinned by name in ledger-scan.test.ts, and the check does not verify that of the next such read. And the count is per read, exactly as the chain rule's is per reference: a statement reading raw_events twice owes two literals, so a tip read tucked into a CTE cannot be covered by the scoped read below it. Both rules stop at the same honest limit: they count predicates against references without resolving which reference each predicate belongs to, since that needs a parser. A statement scoping one alias twice, or a stream literal that belongs to the joined event_coverage row rather than to raw_events, clears the count.

Both rules exist to catch the forgotten predicate, which is how every site this check was written for came to be written. They are regexes over source text, not a parser, so code constructed to slip past them can; the test's own header lists the shapes that would. The guard against that is independent review, which every implementation here gets anyway. A green run means nobody forgot, not that nothing can get through.

Migration workflow

Schema changes are numbered SQL scripts in scripts/sql/NNN-*.sql, applied manually as the Postgres owner against database creddit:

psql -U postgres -d creddit -f scripts/sql/034-morpho-market-apy.sql

Each script is run once, in order, and is idempotent (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS). Because migrations run as the owner but the refreshers and app connect as the onchain_credit role, every CREATE TABLE ends with explicit GRANT SELECT, INSERT, UPDATE, DELETE … TO onchain_credit (and a sequence grant for BIGSERIAL tables); added columns are covered by the existing table grant.

The production deploy (.github/workflows/deploy.yml) is code-only — git reset --hard FETCH_HEAD; npm ci; npm run build; pm2 restart. It does not run migrations, backfills, or data refreshers. Those are manual server steps after deploy. Several migrations are deliberately split into a backward-compatible Na half applied before the deploy and a destructive Nb half applied after (e.g. 016a/016b add apy_30d then drop the llama_* columns; 032 drops the stress column only after the stress-free code ships). Read each script's header for ordering.

schema_migrations (2 columns) tracks which migrations have been applied. It is created/managed out-of-band (not by a numbered script in this repo) and is the source of truth for "what's been run".

Two tables are created by ad-hoc scripts, not numbered migrations:

• fluid_vault_registry — DEPRECATED (was created by the retired sync-fluid-vaults.ts); superseded by carry_registry (migration 035).
• schema_migrations — the migration ledger.

The ledger-first release's four migrations are all expand only, write no row, and are applied by hand, in number order, before that release's deploy (its entry has the commands and the checks):

File	What it adds	Described in
114-ledger-first-kinds.sql	the ledger's two new movement kinds, opening and adjustment, with their columns (explain_status, cause, reading_block) and CHECKs on portfolio_flow_events_v2	the reading audit's two kinds
115-derive-jobs.sql	the derivation queue, portfolio_derive_jobs, which the ledger worker drains	the derivation queue
116-observed-rate-facts.sql	the rate facts: rate_raw / rate_source on the readings, and a pre-window fill's consideration and rate_facts	the observed rate facts
117-audit-job-kind.sql	the queue's audit kind (priority 25, no settle line), restating 115's CHECKs to admit it	the derivation queue

carry_registry (migration 035) is the unified, protocol-agnostic carry registry that drives the /carries listing for Aave/Spark (and seeds Fluid for the eventual fold-in). Keyed by strategy_key; status runs the state machine (active/proposed/reject buckets/gone); upserted by scripts/sync-carries.ts. Only status='active' rows render. See Operational processes A.0/A.1.

---

Fluid

Migrations 002/003 originally created fluid_dex_apy_daily and fluid_ll_apy_daily (once-daily, 24h-window). Migration 004 dropped both and replaced them with the 6h-cadence fluid_dex_apy / fluid_ll_apy below — the _daily tables no longer exist, so they are not in the 21-table count.

fluid_dex_apy — 38 cols, ~55k rows

• Row: one Fluid DEX pool, one 6h snapshot — (snapshot_ts, pool_address).
• Purpose: Fluid DEX trading-fee yield (the "DEX fee leg" of smart-collateral / smart-debt carries) plus the pool's reserve composition and its per-share pot content.
• Key columns: pool_pair, token{0,1}_address/decimals/price_usd, swap volumes (vol_token{0,1}), fees (fee_token{0,1}, fee_window_usd, fee_apy_usd, fee_apy_usd_24h), smart-col / smart-debt reserves and their USD (smart_col_*, tvl_usd_smart_col, smart_debt_*, tvl_usd_smart_debt), fee_rate (4-dp scale), block_at_snapshot. fee_apy_usd = fee_window_usd × 1460 / (tvl_col + tvl_debt).
• Per-share columns (migration 047): token{0,1}_per_supply_share, token{0,1}_per_borrow_share, dex_price, dex_center_price — all RAW integers stored as numeric, read from FluidDexResolver.getDexState(pool) (0x11D80CfF056Cef4F9E6d23da8672fE9873e5cC07, a 30-word static return) at block_at_snapshot. The four per-share words (26-29) are the token amount in native decimals per 1e18 DEX shares; the two prices (words 1-2, lastStoredPrice / centerPrice) are 1e27-scaled. Raw so a stored row is byte-comparable to a direct cast call; descaled only at read time. The four per-share words drive the REALISED cum-return series (see metrics). dex_price / dex_center_price have no reader today: they fed the pool-price-vs-centre strip, which was retired in 2026-07 (see metrics). They are still written, so the stat can be revived without a backfill.
  • All six are nullable and a failed read stays NULL, never 0 (M9): a NULL means "we do not know".
  • A stored 0 is a genuine reading, and means the opposite of NULL: nobody has taken up that side of the pool, so a share has no content. The WRITER keeps the 0 (it is what the chain says); the READER (realizedPerShare in carries-table.ts) nulls that leg, because a pot of zero is not a valuation basis and a 0 slipping past the chart's != null gate would compound the leg flat, deleting a whole leg's economics at leverage. ETH-osETH and reUSD-USDT carry zero SUPPLY pots on part of their history.
  • History floors at the resolver, not at our data. The DEX resolver was deployed at block 23,881,747 (Dec 2025) and returns EMPTY returndata below it, while DEX history starts 2025-05-21. Rows under a pool's floor keep NULL per-share values permanently, and the realised chart mode correctly gates itself off for any window reaching into them. Coverage today: ~915 of 1673 snapshots per long-lived pool.
• Populated by: scripts/refreshers/fluid-dex.ts, every 6h (refresh-assets.ts orchestration). History was migrated from a once-daily 24h-window table to the 6h cadence in 004; the per-share columns are backfilled by scripts/backfill-fluid-dex-pershare.ts.
• Read by: carries-table.ts (the DEX-fee leg of /carries), apy.ts, and (FWS4) the portfolio earned-vs-advertised column — quoted-rate-tables.loadQuotedRates reads the latest fee_apy_usd per pool_address for a Fluid SMART leg's approximate quoted rate. Because a smart-leg position_key carries the vault but not the DEX pool, loadQuotedRates resolves each held vault's per-side pool on-chain (getVaultEntireData.constantVariables), then keys into this table.

fluid_ll_apy — 14 cols, ~54k rows

• Row: one Fluid Liquidity Layer token, one 6h snapshot — (snapshot_ts, token_address).
• Purpose: Fluid LL supply/borrow APY (the borrow-cost leg of Fluid carries; also a supply row on /repo-lending).
• Key columns: supply_apy / borrow_apy (annualised from the 6h exchange-price ratio), supply_apy_24h / borrow_apy_24h, raw supply_exchange_price / borrow_exchange_price (the compounding accumulator), total_supply, total_borrow, utilization (1e2 scale), block_at_snapshot.
• Populated by: scripts/refreshers/fluid-ll.ts, every 6h.
• Read by: carries-table.ts, money-market-rates.ts, apy.ts, directly by app/carries/page.tsx, and (FWS4) quoted-rate-tables.loadQuotedRates — the latest supply_apy/borrow_apy per token_address is the exact quoted rate for a Fluid NORMAL leg and one additive term for a SMART leg (the ETH pseudo 0xeeee row is aliased to WETH, since the portfolio reader maps an ETH leg's accounting asset to WETH).

fluid_vault_rates — 23 cols

• Row: one Fluid VAULT, one 6h snapshot — (snapshot_ts, vault_address). Same aligned grid and the SAME two blocks as fluid_ll_apy, which is what makes the two comparable.
• Chainless on purpose, like fluid_ll_apy and fluid_dex_apy above it. This table is a per-vault refinement of those series and is INNER-joined to them row for row, so a chain_id here could not be scoped end to end: the same statements read a chainless partner. That is the column that looks like a key and enforces nothing, which is what Reading a chain-keyed table exists to prevent, so it is left out rather than declared and ignored. Fluid's other chains need all three series keyed together, in one migration, with the reads and chain-scoping.test.ts updated in the same change.
• Purpose: what a vault's own borrower pays, as opposed to what the shared Liquidity Layer charges the token. Fluid prices borrowing per token at one layer; a vault then applies its own term, and fluid_ll_apy cannot see it. Added by migration 093 (issue #769).
• The vault term, by vault kind (verified against the deployed vault's updateExchangePrices and the resolver's _getExchangePricesAndRates):
  • Normal debt (T1 / T2): the vault's borrow exchange price grows at the layer's growth times configs.borrowRateMagnifier / 10000. At 10000 (1x) the two indexes are the same series, so today's published figures do not move. Fluid routes borrow incentives through this magnifier, so a vault can sit below 1x for as long as a programme runs.
  • Smart debt (T3 / T4): the two tokens' interest accrues inside the DEX borrow share and the layer exchange price is replaced by the 1e12 constant, so the VAULT index carries only the vault's own fixed overlay. borrow_apy is therefore the overlay alone — 0 when the vault charges none (the state of all seven live smart-debt vaults), negative when it pays its borrowers.
• Key columns: supply_apy / borrow_apy (annualised from the 6h VAULT exchange-price ratio), supply_apy_24h / borrow_apy_24h, the raw supply_exchange_price / borrow_exchange_price (the vault's own 1e12 accumulators — the same index the portfolio values a normal Fluid leg with, M14), the layer's ll_supply_exchange_price / ll_borrow_exchange_price as the vault sees them, is_smart_col / is_smart_debt, the raw supply_rate_magnifier / borrow_rate_magnifier, the resolver's signed supply_rate_vault / borrow_rate_vault / rewards_or_fee_rate_*, and block_at_snapshot / block_at_window_start / window_seconds.
• A magnifier is two different fields, and the rule is PER SIDE. On a normal side it is the 1e2-scale multiplier (10000 = 1x). On a smart side it is a packed sign+magnitude: bit 0 is the sign and bits 1..15 the magnitude at 1e2 precision, so a raw 1 means no overlay and a raw 20001 means ±100%/yr. Which reading applies is decided by that side alone — is_smart_debt for borrow_rate_magnifier, is_smart_col for supply_rate_magnifier — so a T2 vault reads its supply field as an overlay and its borrow field as a multiplier. Same convention in vault_capacity.*_rate_magnifier. The supply columns are recorded, not yet read by a published figure: a carry's target leg is the collateral wrapper's appreciation plus the Liquidity-Layer supply rate, so a vault-level supply term is outside it (the one-time alert covers the gap; see metrics).
• Populated by: scripts/refreshers/fluid-vault-rates.ts, every 6h (refresh-assets.ts, immediately after the Liquidity Layer). Its --backfill mode walks the same 6h grid over history; a failed read and a block at which the vault did not exist BOTH write no row, never a zero.
• Read by: carries-table.ts — every Fluid funding leg, live and historical, and apy.ts's getTrailingApy (IndexTable = "fluid_vault_rates", keyed by vault_address). The join is an INNER one: a snapshot with no vault row is a snapshot whose funding cost cannot be stated, and the point is withheld rather than falling back to the layer rate.

carry_registry — unified carry registry (all venues)

• Row: one carry — PK strategy_key (fluid-vault-<id> / aave-<col>-<debt> / spark-<col>-<debt>). Created by migration 035.
• Purpose: the single source of truth for which carries /carries lists, across Fluid, Aave v3 and SparkLend. Only status='active' renders.
• Key columns: protocol, vault_address, vault_id/vault_type (Fluid), collateral_label/debt_label, collateral_addr/debt_addr/emode_category_id (Aave/Spark), config (jsonb; for Pendle-PT collateral it also carries pendleMarket/colDecimals/maturityTs so a new maturity needs zero hand-wiring), tvl_usd, size_usd, ltv, liquidation_threshold, status (state machine: active/proposed/reject buckets/matured/gone), status_reason, maturity_ts (migration 037; term carries only — a 30-day runway floor, defined once in src/lib/data/carry-runway.ts: the reader HARD-filters maturity_ts IS NULL OR maturity_ts > now() + 30 days, the 6h pendle refresher flips active -> matured on the same clock, and sync-carries classifies the same way. The boundary is strictly-more-than, so exactly 30 days out delists; a NULL maturity is a perpetual carry and is unaffected. Applies to already-listed carries as well as new ones — changed 2026-09-02 from a 14-day floor that gated new listings only), first_seen_at, became_active_at, last_synced_at.
• Populated by: scripts/sync-carries.ts — ad-hoc, not a cron (--dry-run / --approve <key> / --reject <key>); see processes A.0. Needs GRANT … TO onchain_credit.
• Read by: carries-table.ts, oracles.ts, basis.ts, the capacity/risk/rate refreshers, and (FWS4) the portfolio's read-registries.cachedFluidState — the protocol='Fluid' rows supply the positions-table label's vault id (vault_id, names the market) and the wound-down / below-floor / blocked status annotation (status). This is ANNOTATION ONLY (D2): nothing is filtered out (status != 'active' still renders), and a vault a wallet holds that is ABSENT from the registry simply gets no annotation (the Fluid reader values it self-describingly regardless).

fluid_vault_registry — DEPRECATED (~116 rows)

• The old Fluid-only registry, written by the retired sync-fluid-vaults.ts. Superseded by carry_registry (migration 035 seeded carry_registry from it). No longer written or read; retained for history, safe to drop later.

Lending venues (rates)

aave_v3_reserve_apy — 15 cols, ~12.3k rows

• Row: one Aave v3 mainnet reserve, one 6h snapshot — (snapshot_ts, token_address).
• Purpose: Aave v3 supply/borrow rates (/repo-lending) and the Aave-funding leg of /carries.
• Key columns: supply_apy / borrow_apy, raw liquidity_rate_ray / variable_borrow_rate_ray (RAY spot rates, kept so APY can be recomputed), the compounding indexes supply_index / borrow_index (RAY 1e27, the basis for the realised-ratio APY), supply_apy_24h / borrow_apy_24h, total_deposited / available_liquidity, block_at_snapshot.
• The index pair is BROUGHT CURRENT to the snapshot block, read from the Pool's getReserveNormalizedIncome / getReserveNormalizedVariableDebt and not from getReserveData's stored liquidityIndex / variableBorrowIndex. Same two accumulators, same RAY scale; the stored pair only advances when a transaction touches the reserve, so on a quiet reserve two 6h anchors sample the span between the last two touches and the window annualises the wrong elapsed time (over the 30 days to 2026-08-17, 120 windows: GHO sd 1.65% around a rate flat at 3.750%; SparkLend rETH an empty window in 117 of 118). Values stay integral RAY, which backfill-apy-24h.ts requires (it BigInt()s the column). Rationale and version-safety evidence: scripts/refreshers/aave-pool.ts.
• available_liquidity is the underlying the reserve will actually release at the snapshot block, in TOKEN units: the pool-enforced virtual balance, bounded by the aToken's own holdings. It is NULL when that read fails — never 0, and never an aToken-supply-minus-debt derivation, which is short by unminted treasury accrual, long by an Aave v3.3 reserve deficit (+14% on WETH), and clamps a genuine shortfall to zero.
• Populated by: scripts/refreshers/aave-v3.ts, every 6h: one getReserveData(asset) at the window end for the spot rates and deposited total, plus the normalized index pair at each of the two window anchors. Rewritten by scripts/backfill-aave-v3.ts, oldest first and upsert in place across each reserve's OWN stored span, so a rewrite changes values and never row counts. Deepening a reserve's coverage is the separate opt-in --extend-history run.
• Read by: money-market-rates.ts (the Utilization column, derived as deposits minus drawable cash over deposits), market-liquidity.ts (the liquidity term of the /carries Borrowable headroom, in token units), carries-table.ts, apy.ts, vault-capacity.ts (getDebtMarketUtilization: total_deposited/available_liquidity drive the debt-reserve utilization line on the /carries capacity tooltips).

sparklend_reserve_apy — 15 cols, ~8.3k rows

• Row: one SparkLend reserve, one 6h snapshot — (snapshot_ts, token_address).
• Purpose: identical to Aave's table; SparkLend is an Aave v3 fork on its own PoolDataProvider. A separate table avoids (snapshot_ts, token_address) key collisions on shared collateral tokens (WETH, weETH, wstETH) and keeps the per-venue refreshers independent.
• Key columns: same shape as aave_v3_reserve_apy (supply_apy/borrow_apy, RAY spot rates, supply_index/borrow_index, *_apy_24h, total_deposited/available_liquidity), and the index pair is brought current to the snapshot block by the same rule. SparkLend has no virtual accounting, so the drawable amount its available_liquidity carries is the aToken's own token balance; NULL on a failed read, same as Aave's.
• Populated by: scripts/refreshers/sparklend.ts, every 6h; rewritten by scripts/backfill-sparklend.ts across each reserve's own stored span, by the same correction-only rule as Aave's.
• Read by: money-market-rates.ts, market-liquidity.ts, carries-table.ts, apy.ts, vault-capacity.ts (getDebtMarketUtilization: total_deposited/available_liquidity drive the debt-reserve utilization line on the /carries capacity tooltips).

morpho_market_apy — 18 cols, ~11.1k rows

• Row: one Morpho Blue isolated market, one 6h snapshot — (chain_id, snapshot_ts, market_id). market_id is the bytes32 keccak of the market's immutable params (not a token address), so this table is keyed differently from the Aave/Spark tables and carries chain_id per the agent-grade conventions.
• Purpose: the admitted Morpho isolated markets on /repo-lending (repo track) and, for carry-track markets, the funding-leg history for /carries. Membership is registry-driven (morpho_market_registry, migration 041); src/lib/data/morpho-markets.ts MORPHO_SEED_MARKETS is the seed set and the fallback for a registry read that fails only. A registry that reads fine and admits nothing yields no isolated markets at all — the segment disappears from /repo-lending and the refresher writes nothing — because the admission rule delists for bad debt, drained utilization or a sub-floor book, and republishing the seed set would resurrect exactly those.
• Borrow leg (migration 041): borrow_share_rate (the borrow-side compounding index, (totalBorrowAssets + 1) / (totalBorrowShares + 1e6), same virtual offsets as share_rate) and borrow_apy_24h (its realised trailing-24h APY) so a Morpho carry's funding leg is a realised borrow-index ratio like every other venue. The existing borrow_apy stays as the spot IRM rate for the repo-tab display.
• Key columns: loan_symbol, collateral_symbol, share_rate (supply-side compounding index, assets-per-share with the virtual offset), supply_apy (realised 6h index-ratio, not spot), supply_apy_24h, borrow_apy (spot from the AdaptiveCurve IRM, display-only), total_deposited / total_borrowed / available_liquidity / utilization, lltv (immutable), oracle_address, irm_address, block_at_snapshot.
• Both share rates are formed from BROUGHT-CURRENT balances. market(bytes32) returns the four totals as last written and lastUpdate says how stale that is, so the refresher first replays Morpho's own expectedMarketBalances (accrueMorphoMarket in src/lib/data/morpho-markets.ts: wTaylorCompounded interest on the borrow total, credited identically to the supply total, plus the fee-share mint when a curator has one set) at each anchor block's timestamp. Without it a quiet market would annualise the span between its last two touches as if it were the window, exactly as the Aave-family stored index pair did. total_deposited / total_borrowed / utilization come from the same accrued totals so the row cannot state a size its own index disagrees with; the accrual credits supply and borrow identically, so available_liquidity is unchanged by it. The replication is exact: it reproduces the interest and feeShares fields of 118 consecutive on-chain AccrueInterest events to the unit.
• A market that does not exist yet gets NO row. Morpho's singleton answers an unknown market id with a zeroed struct rather than reverting, so a backfill walking back past a market's creation used to write a row per window measuring nothing: zero totals and a share rate of exactly 1e-6 (the bare virtual offsets). Each read as a 0% window, and the market's first REAL window then took its ratio against that 1e-6 floor, which on USDC/ETH annualised to a single 8,383,689.61% window that carried the whole series' standard deviation. The refresher now tells "not yet created" apart from a failed read and skips the window (reported apart from the ok/failed tallies, since a backfill crosses every market's creation exactly once); scripts/backfill-morpho.ts --drop-pre-creation-rows removes the historical ones, cutting each market's series at its first FULLY-REAL window: a window is annualised across two anchors, so the one that crosses the creation (live index at the end, virtual floor at the start) is not a reading either, nor are any leading windows where the market existed but held no supply shares. Those rows are also the only ones that ever carried a NULL borrow_share_rate, so removing them retires that gap rather than filling it: there is no market at those blocks to read, and the house rule is that a missing reading is a gap and never a zero.
• Populated by: scripts/refreshers/morpho.ts, every 6h (iterates morpho_market_registry active rows, both tracks; also writes an isolated_market-basis row to market_collateral_exposure for repo-track markets only). scripts/backfill-morpho.ts rewrites rate history across each market's own stored span (which is how a delisted market's history is corrected at all, since the live walk resolves membership through status = 'active'). Note the walk resolves membership through status = 'active', so a delisted market's history is only reachable with --market.
• Read by: morpho-markets.ts, money-market-rates.ts, apy.ts, carries-table.ts (funding leg).

morpho_market_registry — Morpho market membership (migration 041)

• Row: one Morpho Blue market, PK (chain_id, market_id); strategy_key = morpho-<col>-<loan>-<hex8> (unique). Governance table for both surfaces.
• Purpose: the source of truth for which Morpho markets the app shows (repo tab reads status='active' AND 'repo' = ANY(tracks); carry-track markets are mirrored into carry_registry). Also the refresher's work-list.
• Key columns: tracks text[] ({repo,carry}), status (proposed / active / rejected / below_floor / duplicate_market / blocked_adapter / blocked_oracle / delisted_unhealthy / gone / universe), lltv, borrowed_usd / supplied_usd / free_liquidity_usd, supplying_vaults, api_listed, bad_debt_usd, verified_block, config (jsonb morpho-blue StrategyConfig), maturity_ts (PT carries).
• irm_address and irm_flagged_at (migration 081) record two different facts, deliberately. irm_address is the market's rate model as read from the chain (idToMarketParams.irm; empty when the params read failed, never the Blue API's value), and it is what /repo-lending gates the published 90% target utilization on — that number is a constant of the AdaptiveCurve CONTRACT, not of Morpho. irm_flagged_at is when a human was TOLD about a market running something else; NULL means never. Reusing the first as the second is the trap: the sync has written irm_address for every discovered candidate, hard-failed ones included, since this table was created, so the standing set of foreign-IRM markets would have been deduped out of its own alert permanently. A universe row carries no IRM at all (that upsert writes none), which is why a null on an ACTIVE row is a data gap rather than a foreign model, and the reader logs it instead of just dropping the target.
• Populated by: scripts/morpho-discovery.ts + scripts/sync-carries.ts (the curated admission rule, processes.md A.7; the top ~200 markets), PLUS scripts/refresh-morpho-universe.ts which widens the table to EVERY mainnet market as status='universe' rows (portfolio taxonomy T4 §3.5), so the portfolio loader can qualify + value a wallet's position in any market. Seeded with the 7 prior markets as repo active.
• universe rows are inert to the curated machinery: the page readers + refreshers/morpho.ts filter status='active', so they never see them; sync-carries.ts's reconciliation skips status='universe' (alongside gone) so it never bulk-flips them — the rule is shouldGoneMarkMorphoMarket in scripts/morpho-rule.ts, unit-tested, and it is deliberately NOT inline: it was originally written against a mis-typed prior map, which made it compare a record to the string "universe" (never equal), so the exemption silently did nothing and every routine sync flipped the entire universe to gone — permanently, since morpho-universe.ts re-upserts ON CONFLICT DO NOTHING behind an advanced cursor and never heals them. scripts/ is now typechecked (tsconfig.scripts.json), which catches that class at the call site; and they carry tracks='{}' + strategy_key='universe-<id>' (distinct from the curated scheme) + are upserted ON CONFLICT (chain_id, market_id) DO NOTHING, so a curated row is never touched. A universe market that later graduates into curated discovery is promoted in place by sync-carries' own market upsert.
• Read by: money-market-rates.ts, refreshers/morpho.ts (both status='active' only), and src/lib/portfolio/registry.ts loadMorphoMarkets (NO status filter — reads the whole covered universe, curated + universe).

metamorpho_vault_registry — MetaMorpho factory vault universe (migration 052)

• Row: one MetaMorpho vault, PK (chain_id, address) (lower-cased vault/share-token address). The PORTFOLIO universe of Money market funds (portfolio taxonomy T5 §3.5).
• Purpose: the exhaustive MetaMorpho vault set for /portfolio, so a wallet's deposit in ANY MetaMorpho vault (not just the hand-curated Repo-lending subset) charts as a Money market fund. This is the PORTFOLIO universe ONLY — the curated src/data/curator-vaults.ts (a vetted, allowlisted-curator, ≥$100k subset, Euler vaults included) is a separate registry serving /portfolio and the assistant's curator-funds tool, and is not widened by this table. Neither is the /money-market-funds listing, which is rule-based and has a registry of its own.
• Key columns: factory (v1.0 0xa9c3…1101 / v1.1 0x1897…5c24), asset_address (from the CreateMetaMorpho topic3, the underlying), asset_decimals + share_decimals (on-chain-verified; REQUIRED — the loader skips a row missing either, since a wrong scale silently corrupts the descaled quantity), name / asset_symbol (display-only, may be NULL — the reader reads asset() live), status (always universe), first_seen_block, verified_block.
• Populated by: scripts/refresh-metamorpho-factory.ts (daily): a cursor-driven CreateMetaMorpho log scan over BOTH factories (identical event, one OR-array getLogs), each vault on-chain-verified for conformance (decimals()/asset()/asset decimals()), upserted ON CONFLICT (chain_id, address) DO NOTHING. A vault whose reads fail (not a live ERC-4626) is skipped (M9), never ingested with a guessed scale. Cursor scope metamorpho-factory:createvault.
• Read by: src/lib/portfolio/registry.ts loadFactoryVaults → assembleErc4626Universe (union with the static curated set + Fluid fTokens + multi-strategy funds (erc4626 role managed), deduped, static wins, factory rows stamped role curator), stored on reg.erc4626Universe. Bounded per-wallet by the T4 §3.6 discovery intersection (venue erc4626), so the exhaustive universe never costs a per-tick read of every vault; the T5 §3.6 reconciliation sweep backstops the index.

lending_reserves — 15 cols, ~85 rows

• Row: one Aave v3 / SparkLend pool reserve, discovered on-chain — PK (chain_id, protocol, underlying).
• Purpose: the reserve registry for the position scan. reserve_index is the reserve's position in the pool's getReservesList, which the per-user configuration bitmask indexes into.
• Key columns: symbol, decimals, a_token, variable_debt_token (all lower-cased), reserve_index, usage_as_collateral, is_active, is_frozen, liquidation_threshold / ltv (fractions, added in 027), block_number.
• Populated by: scripts/refreshers/lending-positions.ts, every 6h (discovery phase, from getReservesList — not a curated list).
• Read by: internal to the lending-positions pipeline (feeds market_collateral_exposure / market_risk_current); not read directly by an app surface.

Money market funds (/money-market-funds)

Four tables added by migration 086, all mainnet-only and all keyed the agent-grade way (chain_id + lower-cased address, block-anchored, current-state split from history). Together they are the source of truth for which Morpho funds the tab lists, what each one holds, and who can change it.

The doctrine these tables encode: the Morpho API is used for discovery (which vaults exist), veto (its own warnings, shown as flags) and labels (curator name and mark URL). Every number presented as a hard fact is read from the chain at one pinned block. When a chain read fails the column is written NULL and the divergence is recorded in chain_api_delta; it is never filled in from the API.

money_market_manager — approved fund managers (migration 086)

• Row: one manager, PK manager_key.
• Purpose: the human decision the whole listing rule hangs off. A fund can clear every automatic gate and still not render, because nothing lists under a manager nobody has approved.
• Key columns: manager_key (the Morpho API's own Curator.id, lower-cased), display_name (what the UI shows), morpho_curator_id / morpho_curator_name, mark_source_url (the cdn.morpho.org asset, for the mirroring step), status (approved / proposed / rejected), status_reason, approved_at.
• Why the key is the API's id, not a slug of the name: four live curators publish an id that is not the slug of their display name (galaxy for "Galaxy Curation", armitage for "Armitage by Wintermute", eco-vaults-shf for "Waterline", dialectic for "Dialectic Meccanico"). Deriving the key from the name would mint a second, unapproved manager the day a curator rebrands, and every one of its funds would silently fall off the page.
• Populated by: migration 086 seeds ten managers as approved (the seven already surfaced on the site, plus Re7 Labs, Block Analitica and B.Protocol, pre-approved so a fund of theirs lists on its own merits without a migration; none of the three has a mainnet fund above the entry floor today); scripts/sync-money-market-funds.ts inserts every newly discovered curator as proposed, and --approve-manager <key> / --reject-manager <key> flip one.
• Read by: src/lib/data/money-market-funds.ts (display names and the Manager filter).

money_market_fund_registry — the listing decision (migration 086)

• Row: one Morpho vault of either generation, PK (chain_id, address). Every discovered vault gets a row, not only the listed ones, so a fund that grows past the floor is picked up by the next sync without a code change.
• Purpose: which funds render, and why each of the rest does not.
• Key columns: slug (the stable row id and deep-link key, unique per chain), generation (1 = MetaMorpho, 2 = Vaults V2), vault_kind (morpho_vault / fee_wrapper), manager_key + manager_source (api_curator / name_rule / manual), partner (co-brand, e.g. Safe or Trezor), asset_* and share_decimals (chain-verified; the backfill needs both to descale), denomination (USD / ETH / BTC), tvl_usd / tvl_native, status, status_reason, below_floor_since (the delist clock), gate_report (jsonb, per-gate pass or fail at the last sync), became_listed_at, verified_block.
• Both kinds of fund live here (migration 100, #810). kind ∈ money_market / multi_strategy (an open enum; principal_token is what the PT ticket adds), with rate_getter (jsonb, the same shape the token registry uses) and rate_divisor_pow10 beside it. The multi-strategy tab used to read a hand-written TypeScript array while the money-market tab read this table, so a fund could be on a tab and invisible to the portfolio — Lido Earn USD, listed since 2026-08-20, which no wallet could hold — or in the portfolio's reader universe and on no tab at all. Two tabs, two queries, one registry now, and generation becomes nullable because a Mellow meta-vault or an IPOR Fusion PlasmaVault has no Morpho vault generation. The ten multi-strategy rows are seeded from src/data/fund-registry.ts (generated, drift-tested) with each fund's became_listed_at read from this repository's own history rather than set to now(), and each row records the commit that establishes it.
• Coverage is EVER LISTED, the tab is CURRENTLY listed. The portfolio's fund universe is became_listed_at IS NOT NULL whatever the status says today, so a fund that drops below the listing floor leaves the tab and stays readable for every wallet holding it. Only the TAB filters on status = 'listed'. Nothing else may, and a status filter in a coverage query is the exact bug this separation exists to remove.
• Every listed fund carries a block-pinned share-rate getter, and no price feed. A fund share does not trade: its value is shareRate(block) x value(underlying), chained down through wrappers until a traded asset is reached, so it never gets a price bar, a secondary-market fact or a divergence badge. Five of the ten multi-strategy funds are not ERC-4626 at all (the two Lido Earn meta-vaults revert on asset(), liquidETH's rate lives on a Veda Accountant, tETH's convertToAssets returns wstETH per share), which is why the portfolio's ERC-4626 reader universe is filtered on the declared getter KIND rather than on membership.
• Status machine: proposed (passes every gate, manager not yet approved), listed (renders), delisted (was listed, then under the exit floor for a sustained week), ineligible (fails a gate other than the floor), rejected (an explicit human no), gone (no longer discovered). Only listed renders.
• name is stored with its whitespace normalised, runs collapsed and the ends trimmed, and nothing else about it changed: it is the fund's own identity. One live vault, MEV Capital's " Usual Boosted USDC", is named with a leading space on chain, which verbatim sorts it above every other fund in the alphabetical order and hangs a space out of line in the name column. The slug was never exposed to this, because it already maps every run of non-alphanumerics to one dash and strips the leading and trailing ones, so normalising the name moves no published row id.
• The slug takes the END of the address, not the start. Several managers mine vanity addresses, so the leading characters are chosen rather than random: five sets of same-named Morpho vaults share their first six characters and one of those sets has three members. The trailing six separate all 928 vaults in today's universe.
• Populated by: scripts/sync-money-market-funds.ts (daily + on demand).
• Read by: src/lib/data/money-market-funds.ts; scripts/refreshers/money-market-funds.ts and scripts/backfill-money-market-funds.ts both read their work-list from it, and so does the token-yields runtime union, which is what makes an approval take effect without a deploy.

money_market_fund_state — per-fund current state (migration 086)

• Row: one listed fund, PK (chain_id, address). Current state only; the history that charts read lives in token_yield_apy.
• Purpose: everything the drawer's exit and governance panels show.
• Key columns: total_assets / total_assets_usd / share_price, idle_assets, withdrawable_now (exit liquidity), force_deallocatable + force_deallocate_penalty (V2), performance_fee / management_fee, owner_address / curator_address / guardian_address / allocators[] / sentinels[], timelock_seconds (V1) or timelocks jsonb (V2, all eighteen selectors with their duration and whether the action has been abdicated), pending_changes jsonb (a submitted cap change names its direction only when the ceiling in force was read: the direction is a comparison against that ceiling, and both arrive in the same call, so an unread one leaves the row stating the new figure and nothing about which way it moves), adapters / caps / gates jsonb (V2), liquidity_adapter, deposits_open, exit_restricted, public_allocator_admin / public_allocator_fee_wei (V1), depositor_count / top1_depositor_share / top10_depositor_share / depositor_exact, bad_debt_usd + realized_bad_debt_usd + bad_debt_as_of + bad_debt_markets_read / bad_debt_markets_total, asset_price_usd + denomination_price_usd + price_as_of, redemption_rate + par_basis, quoted_apy (tooltip only, never a published return), flags text[], api_warnings, chain_api_delta.
• The price pair travels with the moment it was observed. Quotes are taken under the strict bounds (0.9 confidence, six hours), so a thin or hours-old quote is refused and the tick has no price. When that happens the last good pair stands for up to a day, price_as_of still names the tick that observed it, and the row carries the stale_price flag; past a day the columns go null and the size reads unavailable. Neither case can remove a fund from the page.
• redemption_rate is what one deposit token is worth in the fund's own denomination, and par_basis says how that was arrived at: par (one unit by design), redemption_rate (the token's tracked on-chain rate, for a wrapper that accrues), reference (the token IS the price reference, so its deviation is zero by construction), or unmeasured (no usable redemption path this run, which raises the par_unmeasured flag and makes the listing rule's par gate abstain). unmeasured covers both a token the repo tracks no path for and a tracked wrapper whose rate is missing or over a week old; only the first can be held off the page on its market price. A null rate is never read as 1.
• A failed bad-debt reading never overwrites the last good one. bad_debt_* is upserted only on a tick that measured THIS fund, which excludes both a read that threw and a response that came back carrying nothing about any market the fund lends into. bad_debt_as_of is what makes a carried figure legible.
• All quantities are HUMAN units of the fund's own deposit asset. Fees, penalties, LLTVs and utilizations are fractions. The one exception is public_allocator_fee_wei, which is wei of ether and says so.
• chain_api_delta is a log line, not a gate. Each tick compares our own computed exit liquidity against Morpho's published figure for the same vault and records anything more than 2% apart. The two are read at different blocks, so small drift is expected; a large one means one of the two readings is wrong.
• Populated by: scripts/refreshers/money-market-funds.ts, every 6h inside refresh-assets.ts.
• Read by: src/lib/data/money-market-funds.ts.

money_market_fund_allocation — per-fund, per-market (migration 086)

• Row: one slot of one fund, PK (chain_id, fund_address, slot_key), where slot_key is a Morpho market id, the literal idle, or vault:<address> for a fund held inside another fund.
• Purpose: the drawer's "where is the money" table, the Collateral filter, and the per-market inputs the exit panel adds up.
• Key columns: slot_kind (market / idle / nested_fund), collateral_symbol + collateral_address, lltv, oracle_address + oracle_family, irm_address, allocated / allocated_usd / share_of_fund, supply_cap (the BINDING cap) + cap_read + cap_headroom, market_supply / market_borrow / market_liquidity / market_utilization / market_borrow_apy, pa_max_in / pa_max_out / jit_depth (V1 only), withdrawable_here, permitted_only, bad_debt_usd, realized_bad_debt_usd.
• idle folds two things that are the same money. A MetaMorpho v1.1 fund holds uninvested cash as its own token balance; a v1.0 one parks it in Morpho's collateral-less market, where nobody can borrow it. Both are instantly withdrawable, so they are reported as one idle line rather than as a market row no reader would recognise.
• supply_cap on a Vaults V2 fund is the smallest of THREE limits. One Morpho market consumes three cap ids on a V2 vault (the adapter, the collateral token, and the exact market parameters), and an allocation has to clear every one. Looking the cap up by market id returns zero, which is the easy way to publish a ceiling the contract would not honour.
• cap_read separates a ceiling that does not exist from one that did not read. Both leave supply_cap NULL, and the drawer's cap cell renders a NULL as "Uncapped", which is the most permissive thing it could say: that the fund may put any amount into that market. On a MetaMorpho vault config(id).cap is a uint184 and is ALWAYS a number, so a NULL there can only mean the per-market config call did not come back; on a Vaults V2 fund a NULL is a real answer when every cap id read and nothing binds. cap_read false makes the cell and the headroom beside it read n/a, and takes the market out of the count of markets the manager can fill without asking anyone. Defaults true, so rows written before the column existed keep their meaning.
• permitted_only is a market the curator has opened and not funded. It renders greyed: it is where the money is allowed to go next, which is part of what a depositor is agreeing to. The test is relative, not an exact zero, since live vaults leave single-wei remnants in markets they have fully exited.
• A CLOSED market is not written at all. A cap of zero (or, on MetaMorpho, a market switched off in config) with nothing but dust in it is a market the fund has left: the vault refuses a supply to it, so it is neither funded nor permitted. Writing it added a dead row, and a collateral the fund had left to the set behind the screener's Exposure column. The three-way split runs before any figure is computed from the market list, so the exit total and the shares describe the same set the table shows. See "Funded, permitted, closed" in metrics.md.
• bad_debt_usd and realized_bad_debt_usd are THIS fund's own shares of the market's currently-unbacked debt and of what it has already written off, each prorated by the fund's share of that market's supply, and both populated for either vault generation from one per-market read. They are DIFFERENT CLAIMS and are never summed: one is a loss that may still land, the other a loss already inside the share price. NULL means the market was not measured this tick (bad debt is read by market id from the ids the funds held at the PREVIOUS tick, so a market entered in the last six hours has no reading yet); 0 means measured and none. The fund-level columns on money_market_fund_state are the sums, and they arm the bad_debt and past_bad_debt flags respectively.
• A failed bad-debt reading does not write. The columns above are upserted only when the reading succeeded; otherwise the previous tick's figures stand, the flags they imply stand with them, and bad_debt_as_of keeps the date they were taken so the drawer can label a stale figure as stale.
• Rows are replaced wholesale each tick, so a market a fund has left disappears rather than lingering at its last value.
• Populated by: scripts/refreshers/money-market-funds.ts, every 6h.
• Read by: src/lib/data/money-market-funds.ts.

Positions / exposure ("Underwritten capital")

lending_borrowers — 7 cols, ~77.5k rows

• Row: one account ever seen in a tracked reserve's debt-token mint/burn logs — PK (chain_id, protocol, reserve, account).
• Purpose: the borrower registry that lets each refresh re-read only accounts that were nonzero last run plus accounts with new log activity.
• Key columns: first_seen_block, last_active_block, current_debt_raw (cached last-observed raw balance; NULL = never read). Partial index on current_debt_raw > 0.
• Populated by: scripts/refreshers/lending-positions.ts, every 6h (cursor-scans variableDebt Transfer logs).
• Read by: internal to the pipeline.

lending_positions_current — 12 cols, ~2.1k rows

• Row: one top-borrower account, current state — PK (chain_id, protocol, account). Current-state table, replaced per refresh.
• Purpose: account-level position detail (oracle totals, health factor, per-reserve legs) for the position-attributed exposure layer.
• Key columns: block_number, snapshot_ts, total_collateral_usd / total_debt_usd (pool oracle base currency), health_factor, avg_liquidation_threshold (effective, e-mode aware), e_mode (Aave getUserEMode category; 0 = none, NULL = read failed), collateral / debts (jsonb leg arrays [{address, symbol, qty, usd}]).
• Populated by: scripts/refreshers/lending-positions.ts, every 6h.
• Read by: no app or pipeline reader. The market aggregates (market_collateral_exposure / market_risk_current) are built in-memory from the same 6h scan (the exposures / positions arrays in lending-positions.ts), never read back from this table. It is retained as an inspectable per-account surface (oracle totals, health factor, per-reserve legs) for the agent / direct DB consumers, so it is deliberately kept despite having no app reader.

market_collateral_exposure — 20 cols, ~18.6k rows

• Row: one collateral slice of one market — PK (chain_id, protocol, supplied_asset, collateral_symbol, collateral_address, snapshot_ts) (migration 079).
• Purpose: the "Underwritten capital" panel on /repo-lending — per collateral asset, how much lender capital sits behind it, with per-slice risk quality and cap-headroom.
• The key carries the collateral SYMBOL and the ADDRESS, and needs both. A ticker is not an identity: two isolated Morpho markets on one deposit asset can be collateralised by different tokens that carry the same ticker, and they are two separate books. Keyed on the symbol alone (the pre-079 shape) only one of them could be stored, so morpho.ts published NEITHER rather than serve one market's book under the other's label. The address cannot simply replace the symbol either: the empty string is a legal collateral_address (Fluid LP collateral has no single token, and the synthetic Unattributed remainder is not a position), and those rows are distinct by symbol. Widening the key only relaxed uniqueness, so no stored row moved and both readers, which already resolve an isolated market by collateral address, were unchanged.
• Key columns: exposure_usd (always a USD figure: the deposit-asset debt this collateral backs, marked at the deposit asset's live price), exposure_qty (unit depends on basis, below), basis (fluid_borrow / position_collateral Aave-Spark / pool_collateral legacy / isolated_market Morpho), coverage_pct, block_number; risk layer collateral_usd, wtd_liq_threshold, dist_buckets (jsonb, distance-to-liquidation buckets le5/5to10/10to25/gt25/unknown, plus unattributed when a remainder is published), emode_share_pct + emode_known_usd / emode_debt_usd; cap-headroom (phase 3) cap_instant_usd, cap_ceiling_usd, cap_meta (jsonb method descriptor: aave_static / spark_automator / fluid_elastic, plus noNewDebt for a slice that can take no new borrowing).
• The per-slice distance distribution is kept even though the panel no longer draws it. dist_buckets / near_liq_pct stay persisted and feed market_risk_current; on Fluid they are measured against each vault's CURRENT accrued exchange prices rather than its last stored pair, which moves the distribution materially (the "within 5%" band was understated by an order of magnitude on some markets by the stale pair).
• exposure_qty per basis. fluid_borrow: the borrowed stablecoin amount in its own units. position_collateral: the attributed collateral quantity, so qty x collateral price == collateral_usd; null when the collateral has no usable price. Nothing rewrites history, so rows stamped before this contract carry attributed DEBT restated in collateral units instead — the same slice understated by its account's collateralization ratio. isolated_market: the borrowed loan-token amount in native loan-token units. Migration 026's column COMMENT predates the Morpho basis and enumerates only the first three.
• coverage_pct means the same thing on both live bases: the share of the market's borrowed book whose collateral was actually valued (attributed / book, in percent). It is not a scan-reach statistic and it is no longer structurally 100 on Fluid — debt whose collateral cannot be named or priced now lands in the remainder instead of being attributed to a slice whose collateral was dropped.
• The remainder is a real row. Both live writers publish the shortfall as a synthetic Unattributed slice (collateral_symbol = 'Unattributed', collateral_address = '') whose exposure_usd is the unattributed debt and whose exposure_qty, collateral_usd, wtd_liq_threshold, dist_buckets and cap columns are all null, so the slices always sum to the book and readers can tell "no collateral behind this" from "collateral we could not value". Consumers that special-case it (the panel's donut, the /portfolio collateral cluster, the collateral filter) see it on Fluid too, not only on the Aave/Spark basis. Both writers suppress a remainder under 0.1% of the book as rounding; live today Fluid's is ~$1.1k on a $162M USDC book, so no wedge renders until a pool or vault read degrades.
• Populated by: three writers, every 6h — collateral-exposure.ts (Fluid, fluid_borrow), lending-positions.ts (Aave/Spark, position_collateral), morpho.ts (isolated_market).
• The deposit-asset set is one constant. STABLES in scripts/refreshers/shared.ts drives BOTH the Fluid and the Aave/Spark writer, so adding a deposit asset extends them in lockstep. Each entry declares poolVenues, the Aave-family venues where the asset is a live reserve: the set is genuinely ragged (GHO has no SparkLend reserve), and declaring the absence here keeps scanMarket's "stable reserve missing from discovery" error meaningful for an asset that should be there and vanished. Fluid needs no declaration because its legs come from the vault registry. Note that STABLES is the set the WRITERS cover, which is no longer identical to the set Repo lending renders: GHO keeps poolVenues: ["Aave v3"] and is still scanned there, while Aave's GHO reserve is deliberately not a repo market, so those exposure rows are currently written and not read.
• Read by: cap-exposure.ts → components/money-market/UnderwrittenCapital.tsx, money-market-rates.ts.
• Eligibility is e-mode-aware. Whether a collateral can take new debt (and so whether cap_meta.noNewDebt is set) is decided from the e-mode-aware LTV, not the base reserve flags. Aave "liquid e-mode" collateral — Pendle PT tokens especially — carries a base LTV of 0 and base usageAsCollateralEnabled = false, yet is borrowable-against at ~90% LTV inside a per-maturity e-mode (e.g. PT-srUSDe-22OCT2026 in the "PT-srUSDe Stablecoins" e-mode). Such collateral counts as active, not deprecated. reserveCapBounds (lending-positions.ts) therefore gates on emodeParamsFor(...).ltv <= 0 || isFrozen || !isActive, deliberately NOT on the base usageAsCollateral flag (for a base-market reserve ltv>0 ⇔ usageAsCollateral, so non-e-mode behaviour is unchanged).
• Drives the /repo-lending Collateral Exposure filter. Each market row carries activeCollaterals (money-market-rates.ts) = its non-deprecated collaterals (isolated market = its single collateral; pooled venue = its slices minus the Unattributed remainder and any slice marked noNewDebt — an Aave/Spark reserve with no borrowing power, or a Fluid collateral whose every vault has been wound down). The filter lists every active collateral and shows a market iff at least one of its active collaterals is selected (deselect-all empties the table; a row with no attributable active collateral is never hidden). ETH is canonicalised to WETH everywhere it surfaces (filter, table label, chart legend, donut) so the same asset is one entry across protocols.
• Fluid smart-collateral pairs are split into legs for the filter. A Fluid slice is labelled with the DEX pair it holds (GHO/USDC, USDC/ETH, WBTC/cbBTC). A pair is one position but two exposures, and the filter is asset-denominated, so activeCollateralsFor() runs each label through collateralLegs() (src/lib/collateral.ts): ticking GHO keeps the GHO/USDC market on screen. This is filter-only — the underwritten-capital donut reads collateralExposure, which keeps the pair label, because there the pair is the position being measured. Decomposition turns the raw 41 exposure labels into 30 filter options. The checklist groups them by denomination via collateralCategory() (USD / ETH / BTC / Gold / Other): gold (PAXG, XAUt) is a first-class denomination, and collateral that is none of the above (LINK, AAVE, EURC) lands in Other rather than being silently swept into USD.

market_risk_current — 15 cols, ~6 rows

• Row: one market summary — PK (chain_id, protocol, supplied_asset). Current-state table, replaced each refresh.
• Purpose: the market-level risk summary + cap-implied worst-case callout under "Underwritten capital".
• Key columns: block_number, snapshot_ts, total_borrowed_usd, total_collateral_usd (attributed), wtd_liq_threshold, near_liq_pct (share of measured debt within 10% of liquidation), dist_buckets (jsonb), method (account_hf per-account HF distance / vault_tick per-tick distance), and the cap layer stable_liquidity_usd / stable_borrow_cap_usd / stable_current_borrow_usd / largest_collateral (jsonb). (A stress column existed in 030 and was dropped in 032.)
• total_borrowed_usd is the market's whole book on both bases — for Fluid the Liquidity Layer's own total borrow for that stablecoin, including borrowers outside the vaults in the breakdown, not the sum of the attributed slices. total_collateral_usd and dist_buckets stay attributed-scope, which is why coverage_pct on the slices is the figure that reconciles the two.
• stable_liquidity_usd is the drawable amount described under the reserve tables above, times the stablecoin's price: what the pool will actually release, NULL (never 0) when the read fails, and degrading independently of stable_current_borrow_usd. stable_borrow_cap_usd is always the LIVE, on-chain-enforced borrow cap for the supplied stablecoin; an eventual ceiling a cap automator would grow into is never stored here (it belongs to the cap layer's ceiling, and today all three Spark stables read as uncapped anyway). largest_collateral is withheld (null) when a cap read failed, and also when any live limit's collateral cannot be named.
• The risk row is CURRENT-state while the slices are a snapshot, so both writers write them in one transaction at one anchor block, and a market they cannot read this run keeps BOTH rows. A same-run pair therefore has zero skew, and readers reject a pair whose stamps disagree rather than dividing one run's collateral by another run's debt.
• Populated by: lending-positions.ts (account_hf) and collateral-exposure.ts (vault_tick), every 6h.
• Read by: cap-exposure.ts, money-market-rates.ts.

chain_scan_cursors — 4 cols, ~4 rows

• Row: one incremental-scan cursor — PK (chain_id, scope).
• Purpose: generic block-height cursors so the log scanners (and future scanners) resume where they left off. scope is a free string, e.g. vdebt-transfers:Aave v3:0xa0b8…. The retired per-wallet discovery watermarks (portfolio:discover:<wallet>, superseded by the certificate on accounts in 061) were deleted by migration 106; nothing writes that scope.
• Key columns: last_scanned_block, updated_at.
• Populated by: scripts/refreshers/lending-positions.ts, every 6h.
• Read by: internal to the pipeline.

Assets & yields

assets — 14 cols, ~14 rows

• Row: one covered yield-bearing collateral asset — PK ticker. Derived rollup table.
• Purpose: the Asset profiles screener on /asset-profiles (and home-page metrics).
• Key columns: name, issuer, href, productive_usd / underlying_usd (market caps), current_apy, one_month_return, ytd_return, one_year_return, rating, tier, as_of. (The four llama_* comparison columns were dropped in 016b.)
• Populated by: scripts/refreshers/yield-token-assets.ts, daily — rolls up token_yield_apy into these rows.
• Read by: assets-table.ts → app/asset-profiles/page.tsx.

token_yield_apy — 9 cols, ~112k rows

• Row: one yield-bearing wrapper, one 6h snapshot — (snapshot_ts, token_address).
• Purpose: the foundational per-token share-rate / yield series. Feeds the collateral-yield leg of /carries, the /multi-strategy-funds performance charts, the /asset-profiles rollup, and token_basis.
• Key columns: share_rate (raw on-chain asset-per-share rate — convertToAssets/getRate/getStETHByWstETH/NAV, 1e18-descaled; ETH-denominated for ETH wrappers, not a USD price — renamed from price_usd in 015), supply_apy (24h-trailing, annualised; rewritten across a silent publish gap, and therefore the one column here that is not point-in-time reproducible — see metrics), apy_30d (trailing 30d, the single source of truth for the 30d figure), total_supply (human share units; TVL = total_supply × share_rate), block_at_snapshot.
• Populated by: scripts/refreshers/token-yields.ts, every 6h. Its universe is the hand-curated BASE_YIELD_TOKENS plus portfolioErc4626Vaults() (src/data/erc4626-universe.ts: the generated curator-vault registry + the Fluid fTokens in src/data/fluid-ftokens.ts), so a new ERC-4626 token starts snapshotting with no code change. History for a curator/fToken vault is seeded by scripts/backfill-curator-vaults.ts (--only=<addr> scopes a run to a newly added token; without history its 24h supply_apy is null until the trailing window has two points); a hand-curated base wrapper gets a targeted backfill instead (backfill-susds.ts, backfill-sgho.ts, backfill-usd3.ts, … — same 6h convertToAssets sampling, from the wrapper's deployment). See the August 2026 USD yield batch for the five series that batch added and the anchor each one's start date is pinned to.
• Read by: strategies-table.ts, yield-history.ts, carries-table.ts, oracles.ts, basis.ts, curator-funds.ts, apy.ts, and portfolio/quoted-rate-tables.ts (the quoted "advertised" APY of an erc4626 leg is this table's rate, keyed by the SHARE-TOKEN address; that is why an fToken needs a row here).

token_basis — 8 cols, ~36k rows

• Row: one collateral token, one 6h snapshot — (snapshot_ts, token_address).
• Purpose: secondary-market basis ("exit risk") — the depeg the carry series cannot see, since token_yield_apy reads redemption rates with no market-arb noise.
• Key columns: numeraire (USD / ETH / BTC), market_price_usd (the token's Dune-mirror bar, via getBarSeriesAt over token_price_bars), redemption_value_usd (token_yield_apy.share_rate x the SAME-BAR numeraire quote), basis = market_price_usd / redemption_value_usd − 1 (negative = market discount). Both legs of the ratio are read at their NEWEST COMMON bar_ts (the same-bar rule via newest-common-bar pairing), which is what deletes the cross-vintage DeFiLlama noise; see data-pipeline.
• Populated by: scripts/refreshers/token-basis.ts, every 6h (market + numeraire from the token_price_bars mirror, shared computeBasisFromBars). History was rebuilt from the mirror once, by a shadow → parity → swap procedure that ran on prod on 2026-07-20; its scratch table token_basis_dune_shadow is dropped by migration 108 (see data-pipeline).
• Read by: basis.ts, app/api/carry-oracle/route.ts (per-strategy oracle transparency on /carries; the Metrics: how & why panel). The reader tolerates the table being absent so the app can deploy before the migration/backfill.

token_price_bars — 11 cols

• Row: one token, one hourly bar — PK (chain_id, token_address, bar_ts).
• Purpose: the hourly USD bar table — the historical market-price source the mark pipeline reads, and the only one (the Dune price-mirror refactor built it as a Dune mirror; #810 hardened it in migration 098; the pricing categories added the vendor hole fill, so Dune is the standing writer rather than the only one). Every bar is hourly (prices.hour) and there is no finer grid, with a CHECK on the alignment. A 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 cross-vintage DeFiLlama noise behind the phantom-wallet entered-basis error).
• Key columns: chain_id (1 = ethereum, incl. the WETH denominator; 0 = the bitcoin reference row, which fed the BTC book until it was retired and now serves token_basis's BTC numeraire only), token_address (lower-cased; chain-0 rows normalized to a fixed bitcoin sentinel), bar_ts (bar START, UTC, on the hour, with a CHECK on the alignment), price_usd (NUMERIC CHECK (> 0)), source (per-row provenance: dune:prices.hour, dune:dex-ratio for a token routed off the aggregated channel, llama:coins for a token on DefiLlama's aggregate, and coingecko:hourly / llama:chart for an hour the tape or the routed query left empty that the fill bought from a vendor's hourly history — the one pair of other writers a routed row admits), ingested_at.
• Migration 098 (#810 R6) adds five columns, all defaulted or nullable so the previous release runs unchanged against them:
  • rejected (BOOLEAN NOT NULL DEFAULT FALSE) + reject_reason — the spike gate's verdict. A bar is retracted when it departs from BOTH neighbours by more than 5% while those neighbours agree with each other within 1%; it keeps its row and its price (the audit trail) and every read that PRICES skips it, so a walk-back lands on the last accepted bar. The resume gauges (countHourlyBars, the minute-coverage read) deliberately still count it: append-only means its grid slot can never be refilled, so treating it as missing would re-buy a row the insert discards.
  • volume_usd, dune_source — the two output columns the saved Dune queries emit, stored verbatim. NULL is a real state: coinpaprika-sourced rows never report a volume, and every bar written before 098 has neither, which is how a reader tells a pre-098 window from one that genuinely has no source.
  • carried (BOOLEAN NOT NULL DEFAULT FALSE) — this bar repeats the immediately preceding hour's (price_usd, volume_usd) pair EXACTLY, i.e. a forward fill rather than a fresh print (two NULL volumes count as equal). Derived once at write time by EVERY writer, because the comparison needs the neighbour: a bar no Dune query produced carries the flag too, so an absent dune_source says nothing about whether the flag was set. It is what lets a reader tell a vendor forward fill from a quiet market, instead of inferring it from runs of identical prices. Dune's fill copies the whole row, so "null or zero volume" is NOT the carried test: PST repeated price and volume for six hours on 2026-09-07, while coinpaprika rows report no volume in any hour. Where the writer could not have SET the flag it is false by construction, not by measurement, and a reader that takes the column at face value will read those falses as real ones. There are three such shapes. (1) At an hour where the WRITER CHANGES, twice over: a vendor bar carries no volume against a Dune predecessor's real one, so the pair can never match however still the quote was, and the Dune bar at the next hour was written when the filled hour did not yet exist, so its writer found no predecessor at all. (2) After a HOLE IN THE GRID, where the preceding hour was simply missing at write time — the tape skipped it, or DefiLlama refused a quote for it, which is the ordinary shape of the aggregate feed rather than an incident. (3) When a writer RAN OUT OF ORDER: the hole fill's spike gate refuses hour H while writing H+1 in the same pass, H lands on a later tick, and H+1's false is frozen because carried is not in this migration's column-level GRANT UPDATE. For a leg that publishes no traded size the write-time test reduces to price equality, so the flag is recomputable from any two stored bars; Dune's own legs do publish a size, so there a stored false may be a real one.
  • Write scope: 098 re-opens exactly rejected and reject_reason with a COLUMN-level GRANT UPDATE, leaving 055's table-level REVOKE in force. The gate can record a verdict; nothing in the app can move a price.
  • Readers probe first. Every read that names these columns asks information_schema (src/lib/data/mirror-columns.ts, cached 5 min present / 1 min absent) and emits the pre-098 SQL — no filter, typed NULL literals — when they are absent. The /carries basis panel reads them during next build, so an unguarded column reference would fail the build, roll the deploy back, and leave the migration unapplied.
• Populated by: src/lib/data/dune.ts — the 6h token-basis refresher's prepended incremental sync, scripts/backfill-token-price-bars.ts (one-time bulk load), and fetch-through on a read miss; plus the aggregate leg (llama-aggregate-sync.ts) and the hole fill (history-fill.ts, sources coingecko:hourly / llama:chart, for an hour older than 6h the tape never printed), both of which write through the same upsertBars and are therefore bound by the same exclusivity, de-duplication and append-only rules. The fill also RETRACTS a Dune bar two independent vendors agree is wrong (reject_reason vendor-disagreement), which is an UPDATE of the same two columns the spike gate writes and never a replacement of the price. See Filling the tape's holes. The one-off re-judge (scripts/repair/rejudge-price-bars.ts, run as the table OWNER) applies the same two rules to the stored history: it retracts with reject_reason vendor-disagreement-rejudge, and it is the one writer that REPLACES a row — it deletes a retracted row and writes the vendor's bar in its hour, in one transaction, after writing the deleted row to a backup file. See Re-judging the stored history. Append-only: ON CONFLICT DO NOTHING, so a stored bar is FROZEN (a Dune restatement never moves it); migration 055 REVOKEs UPDATE, DELETE from the app role(s) so the freeze is enforced at the grant level, not just by client convention (the schema-wide arwd default privilege had otherwise re-added them, silently defeating 054's narrow GRANT SELECT, INSERT). The postgres owner is unaffected, so a deliberate operator purge (e.g. a corrupt-feed cleanup) still works. See Token price bars (Dune mirror).
• Read by: nothing in Phase A. Phase B/C wire the reads (token_basis rewrite, portfolio marks) — the client's getBar / getBarsAt take the bar covering a ts, walking back up to 6h and returning its actual bar_ts so a caller can flag staleness. See metrics.

token_price_sync_state — 8 cols, one row per synced feed (migration 098)

• Row: one tracked price feed — PK (chain_id, token_address). Current-state table; the history it summarises is token_price_bars itself.
• Purpose: the per-token sync cursor (#810 R6). The six-hourly mirror sync used to open its window at the table-wide MAX(bar_ts), a watermark every other token advances on every tick, so a token that lagged Dune's ingestion had its window stepped over — and because bars are append-only and nothing else back-fills the standing feed, those hours were lost permanently. Every token lost 19h in the week of 2026-08-24 that way, and PST was dark for 101h in July 2026 before anyone noticed. One cursor per token turns a failed execution into a retry instead of a hole.
• Key columns: last_bar_ts (the cursor: normally this token's newest ACCEPTED bar as of its last successful sync, and after a completed CATCH-UP the end of the span that execution asked for — a span Dune was asked for and had no rows in is answered, and moving the cursor is what stops a permanently quiet feed re-buying the same chunks every six hours; NULL = never synced successfully), last_attempt_at, last_success_at, consecutive_failures (reset to 0 on any success, so a large value is a feed that is stuck rather than merely late), floor_loaded (has this token's history been loaded back to the 2026-01-01 floor — set when the load has RUN over the whole span, which is not the same as "bars came back", so a token whose market did not exist in January does not re-buy its absent history every six hours), updated_at.
• A FAILED sync stamps the attempt and leaves last_bar_ts alone. That is the whole mechanism: the window an execution lost is the window the next tick asks for. It does, however, still record floor_loaded when the caller knows the token's history reaches the floor: this INSERT creates the row when none existed, and the bar-extent bootstrap is only consulted while there is NO row, so a row created with the column's false default would send an already-complete token down the floor-load branch on the next tick. The flag is sticky-on, never off, so an incomplete floor load (which asserts nothing) cannot mark itself done.
• Populated by: scripts/refreshers/token-basis.ts (every 6h, via src/lib/data/mirror-sync.ts). Rows are created lazily on the first attempt rather than seeded by the migration, because the standing sync set becomes registry-driven in the next piece of #810 and a seed written from today's code list would bake in a list that is about to move. A token synced before this table existed bootstraps from its own stored bar extent.
• Read by: the sync plan (which token needs a floor load, where each incremental window opens) and the spike gate (the cursor bounds its judge window). Every read degrades to empty with one warning if the table is absent, which leaves exactly the pre-098 behaviour. See data-pipeline.

Pendle (fixed rates)

pendle_markets — 15 cols, ~55 rows

• Row: one Pendle V2 market (a PT/SY AMM pool with one maturity) — PK (chain_id, market_address).
• Purpose: the dimension table of the fixed-rate data layer. Registry-driven snapshotting, PT-carry discovery joins (phase 1), and the future term-curve / fixed-rates surface.
• Key columns: pt_address / pt_symbol / pt_decimals, yt_address, sy_address, underlying_address / underlying_symbol, maturity_ts (on-chain expiry()), status (active → matured, flipped deterministically from maturity_ts, never deleted), oracle_ready (whether the market's TWAP ring buffer guarantees a 900s window under swap bursts — the increaseObservationsCardinalityNext ops signal; snapshot rates are valid regardless because the oracle reverts rather than degrade).
• underlying_address is the PT's ACCOUNTING asset, not the market's yield token. It holds the SY's assetInfo() asset — the unit getPtToAssetRate is quoted in, and therefore the unit a PT position is marked in: USDe for a PT-sUSDe market, stETH for PT-wstETH, apxUSD for PT-apyUSD. It is deliberately not the SY's yieldToken() (sUSDe / wstETH / apyUSD), which differs from it by the SY exchange rate on most markets. The distinction is load-bearing: the mark path converts a PT quantity with getPtToAssetRate and then prices the result at this address's bar, so the yield token would overstate every PT it values by that rate. Native ETH (which Pendle's SYs report as address(0)) is stored as the repo's ETH sentinel 0xeeee…eeee.
• It is a unit, never a counterparty, and no reader-facing label may be drawn from it. The token whose solvency a PT holder actually inherits is the SY's yield token — what the PT redeems into, and what Pendle names the PT after. The two are different on most live markets, so the /carries settlement sentence reads its token off pt_symbol (PT-reUSD-… → reUSD) and never off underlying_symbol, which would state a PT-reUSD position as inheriting USDC's solvency. Same column, two questions: what is it worth (this column) and whose credit is it (the PT's own name). See metrics.
• Populated by: scripts/refreshers/pendle-markets.ts, every 6h. New markets come from the Pendle hosted API and are verified on-chain (readTokens() + expiry() must match) before insert; underlying_address is read from the chain (SY.assetInfo()), never taken from the API. The same pass re-reads assetInfo() for every existing row and corrects any that disagrees, so the column cannot drift from the chain — unless the correction would move the market's book (this column is also what bookForAccountingAsset resolves a PT leg through), in which case the row keeps its stored unit and the run names it on an operator line rather than dropping the market out of the yield book silently.
• Read by: the refresher itself (registry-driven snapshot loop) and the phase-1 PT-carry integration.

pendle_market_state — 15 cols, growing ~220/day + backfill

• Row: one market, one snapshot — PK (chain_id, market_address, snapshot_ts).
• Purpose: the fixed-rate time series. Term curves per asset family fall out of this table by construction (implied APY across that family's active maturities at a snapshot).
• Key columns: implied_apy (the market fixed rate, e^(readState().lastLnImpliedRate/1e18) − 1 — what a PT buyer locks in accounting-asset terms), pt_to_asset_rate / pt_to_sy_rate (900s TWAP from the canonical PendlePYLpOracle; pt_to_asset_rate is a compounding-index-shaped series accreting to par at maturity, so realised trailing PT yield = annualizeRatio of it — the app-wide convention), sy_exchange_rate (the underlying yield's compounding index), total_pt / total_sy, liquidity_usd, underlying_apy (backfill-only; NULL on 'rpc' rows, where the realised figure derives from sy_exchange_rate), block_number (NULL on backfill rows), basis ('rpc' cron snapshots | 'pendle_api' daily history backfill), and the redemption-index pair (migration 101): redemption_index_factor = min(1, SY.exchangeRate() / YT.pyIndexStored()) at the row's block — what one PT actually settles for, per unit of its accounting asset, dimensionless and in [0,1], exactly 1 on an unimpaired market — plus py_index_stored, the denominator, descaled by 1e18 exactly as sy_exchange_rate descales the numerator so the two are on one scale. redemption_index_factor is the authority; the pair is evidence. LEAST(1, sy_exchange_rate / py_index_stored) approximates it and the cap is not optional (on an unimpaired market the raw quotient exceeds 1 while the factor is exactly 1: the apyUSD pair quotes 1.000088512000069932 against a stored 1.000000000000000000), and even below the cap the reconstruction is only as precise as sy_exchange_rate, which is written through a double while py_index_stored is exact.
• Populated by: scripts/refreshers/pendle-markets.ts (6h, basis='rpc' — which also RUNS the backfill below for every market it inserts in a tick, capped at two markets and after the snapshot loop, so a market's factor history exists before a PT bought in its first hours is ever marked) and scripts/backfill-pendle-history.ts (per-market daily history from the Pendle API; no API-sourced column ever overwrites a cron row; --market scopes to one market, --since YYYY-MM-DD bounds the series, --no-factors skips the chain reads, --dry-run reports the rows it would insert — counted against the timestamps already stored, so the number is the one a wet run lands — and writes nothing).
• The redemption-index pair is the one column pair the backfill FILLS on an existing row. It is not served by the API: for each daily point the backfill resolves the last block at or before that point (reusing an existing row's own block_number where there is one, so the row keeps describing a single block) and reads SY.exchangeRate() + YT.pyIndexStored() there — 4 to 5 chain reads per row (a block resolution is a DefiLlama call plus one or two verification reads, or a full bisect on fallback; the two legs ride one multicall). The conflict clause writes the pair only when the stored one is NULL and the read produced one, so a re-run over a finished market updates nothing and a failed read can never null a factor already banked. Because of the cost this is run per market, on markets a tracked wallet holds, never in bulk; see deployment.
• NULL means unread, never par. Every row written before migration 101 has both columns NULL, and so does any row whose legs failed to read. The reader (src/lib/portfolio/v2/pt-factor.ts) joins the latest non-null factor at or before a moment and bounds how far that row may be carried: 18h for a 'rpc' row, 48h for a 'pendle_api' one, returning null past that. Two of the writer's own windows is room for one missed tick; the cron gets a third because its rows are stamped with the 6h window floor but read at the head block minutes later, so a flat 12h would expire the previous row in the minutes before its replacement lands. The bound is the whole point: without it a writer that goes quiet keeps answering "par" with total confidence for a market that may have been written down since.
• Coverage gap the 6h cron cannot close: the snapshot loop only visits status='active' markets, so a market admitted by the refresher's matured catch-up (one that had already matured when this deployment first synced) has no 'rpc' rows at all and never will. The v2 history endpoint does serve an expired market in full (verified live on PT-apyUSD-18JUN2026: 111 daily points, 2026-02-27 → 2026-06-17), so the one-shot backfill above is that market's only source of history and has to be run once after a catch-up admits one. The API-sourced columns are display history only — a PT position is still MARKED from the pendle_markets registry row plus an archive read of getPtToAssetRate at the position's own block (the redemption index at/after maturity), never from this table. The redemption-index pair is the exception, and the reason a factor backfill on a held market is a portfolio step rather than a display one: the portfolio read path reads it as a series. A market's series ends at its maturity (the 6h arm visits active markets only); after maturity a held leg's factor is recoverable from its own spine row as qty_underlying / qty, never assumed par.
• Read by: phase-1 PT-carry integration; later the fixed-rates section + agent/MCP surface.

Capacity / risk params

vault_capacity — 31 cols, ~22 rows

• Row: one carry strategy's current borrow/supply headroom — PK strategy_key. Current-state table.
• Purpose: the borrowable-headroom and vault-size figures in the /carries KPI tower. Three cap models: Fluid dynamic (elastic) caps, Aave/Spark static governance caps, and Morpho Blue's no-cap markets (deposits are the ceiling).
• Key columns: source (fluid / fluid-t2 / fluid-t3 / fluid-t4 / aave-v3 / sparklend / morpho-blue), borrow side borrow_symbol/decimals/current/cap, borrow_dynamic_cap + Fluid expand mechanics (borrow_expand_percent, borrow_expand_duration_sec; NULL on Morpho, which has no dynamic-cap mechanics), supply side (Aave/Spark e-mode only) supply_*; per-leg display strings borrow_pool_breakdown(_detail) / supply_pool_breakdown(_detail) for T3/T4 LP-denominated legs; per-token smart-debt remainders borrow_token{0,1}_address/remaining/decimals; smart_debt_legs (jsonb per-token LL limits); total_supplied_usd / total_borrowed_usd; block_at_read, changed_at, last_checked_at. Values are stored in whole tokens, not wei.
• Morpho rows: keyed by strategy_key (never vault address: every Morpho carry shares the Blue singleton). borrow_cap = borrow_dynamic_cap = the market's loan-token deposits, so dynamic_cap − current = available liquidity; supply_current / supply_cap are NULL. See Metrics.
• Fluid rate magnifiers (migration 080, Fluid rows only, NULL elsewhere): borrow_rate_magnifier / supply_rate_magnifier, 1e2 scale (10000 = 1x). Fluid prices borrowing per token at the shared Liquidity Layer; a vault multiplies that layer rate by its magnifier to get what its own borrowers pay, and Fluid's rebalancers move it to route borrow rewards. Persisted for one reason: to answer "is the rate we publish for this vault the rate its borrowers pay?" at the cron boundary, and — because the previous run's value is stored — to answer it once per change instead of every six hours (see the unmodelled-rate-model alert in Data pipeline). Read them as that dedup's memory, not as a guaranteed-current reading: a value that constitutes a FINDING (a magnifier off 1x) is written only once the alert reporting it has been delivered, so an undelivered alert leaves both columns on the previous run's values until the next run repeats the finding. Ordinary 1x values are written inline every run.
• Populated by: scripts/refreshers/vault-capacity.ts, every 6h.
• Read by: vault-capacity.ts → app/carries/page.tsx.

funding_shock — 13 cols, one row per strategy per 6h window (migration 092)

• Row: one carry strategy's +5pt utilization sensitivity for one window — PK (chain_id, strategy_key, snapshot_ts). History table, kept forever: a funding shock is only interpretable against the curve that produced it, so every row carries the fingerprint of that curve and history is never reinterpreted under today's configuration.
• Purpose: the /carries Funding shock column — the borrow-APY change five more percentage points of utilization would produce, and the additional borrowing behind it. See Metrics.
• A row is written for EVERY strategy, whatever the outcome. A market whose rate model could not be reproduced is recorded with its health state and a diagnostic rather than left out, so "we withheld this, and why" is a fact in the table instead of something to reconstruct from logs.
• Key columns: source_block + source_timestamp (every input for one row read at THIS block), protocol, health (healthy / configuration_changed_revalidating / unsupported_model / parity_failed / stale_inputs — only healthy is ever shown), scenario (full_shock / capped_to_full / no_headroom), model_version (which calculator produced it), fingerprint (sha256 of the invalidating model fields), result (the published payload: blended rate change, one USD borrow figure, and the per-leg detail — each leg's weight is its NORMALISED share of the row, so the legs of one row sum to 1, and venueRateMultiple is present only where the levels are a multiple of the funding market's own rate). Each leg may also carry a curve — { points: [{u, apy}], kinks: [u] }, the market's own borrow curve sampled by the SAME run through the SAME model, spanning u = 0 to 1 with both scenario points pinned onto it, which is what the detail panel's chart is drawn from. It is optional and stays optional: the field was added to an existing jsonb payload with no migration and no model_version change (the calculators did not move), so rows written before it are served unchanged and simply render no chart. A curve that cannot be drawn never costs a reader a figure: the writer drops it and records why in diagnostics.curve_unavailable, and the reader strips it and serves the row (servableShock). The row's own health is decided by the numbers alone, which is also what keeps a release that widens the sampling from blanking the column for the release before it after a rollback. provenance (dynamic inputs that are NOT invalidating — balances, stored rates, external rate-source values, prices — plus provenance.model, the fingerprint's own fields, so a change can name what moved), diagnostics (withhold reason, configuration_changed events).
• Two CHECK constraints carry the contract: the health enum, and (health = 'healthy') = (result IS NOT NULL) — a payload and a healthy state are the same claim, so a stored result under any other health could be served by a future reader that forgot to check.
• No extra index, but the query shape matters: the primary key serves both access patterns (one strategy's history, and the latest row for each of a named set of strategies). What no index can fix is a SELECT DISTINCT ON (strategy_key) … ORDER BY strategy_key, snapshot_ts DESC over this table: Postgres plans DISTINCT ON as Sort + Unique and has no loose index scan, so it reads every row and drags the jsonb payloads through an external sort whatever indexes exist. Both readers therefore drive the scan off their own key list (unnest($1::text[]) CROSS JOIN LATERAL (… ORDER BY snapshot_ts DESC LIMIT 1)), which is one index scan per strategy. Keep that shape: rows are kept forever, so a full sort here would grow without limit on a page that regenerates hourly.
• Populated by: scripts/refreshers/funding-shock.ts, every 6h.
• Read by: funding-shock/store.ts → app/carries/page.tsx. The reader is fail-closed: only healthy rows under 12 hours old, revalidated against the same payload gate the writer applies, and a missing table returns an empty map with one warning rather than taking the screener down.

vault_risk_params — 8 cols, ~21 rows

• Row: one strategy's risk parameters — PK strategy_key.
• Purpose: max-LTV + liquidation threshold per carry strategy; these move on a months timescale (governance) so weekly polling suffices.
• Key columns: max_ltv, liquidation_threshold (fractions; LT ≥ max-LTV, the gap is the safety buffer), source (fluid-vault / aave-emode / spark-emode / aave-reserve / spark-reserve), emode_category (self-discovered), block_at_read, changed_at (bumped only on a real value change — an audit trail), last_checked_at (touched every successful read).
• Populated by: scripts/refreshers/vault-risk-params.ts, weekly (Monday 03:30 UTC).
• Read by: vault-risk.ts → app/carries/page.tsx.

capacity_notifications — 12 cols, ~1 row

• Row: one capacity-alert signup — PK id (BIGSERIAL).
• Purpose: captures email requests from /carries when a strategy's borrowable USD drops below the institutional minimum (default $100k). Send pipeline is a follow-up; for now it only captures.
• Key columns: email, strategy_key, vault_address (lowercased), borrow_symbol, threshold_usd + borrowable_usd_at_signup (snapshots at signup), requested_at, notified_at, unsubscribed_at. Partial unique index on (lower(email), strategy_key) where not unsubscribed.
• Populated by: app/api/notify-capacity/route.ts (user-facing form), not a cron.
• Read by: the API route (write path); a future poller cron.

Misc

sofr_rates — 8 cols, ~2.1k rows

• Row: one calendar day of SOFR — PK rate_date.
• Purpose: the "vs SOFR" benchmark across /asset-profiles, /multi-strategy-funds, and /repo-lending.
• Key columns: rate (daily overnight SOFR, annualised ACT/360, stored as percent), avg_30d / avg_90d / avg_180d (compounded backward-looking averages from SOFRAI), sofr_index (NY Fed daily compounding index — ratio of two dates' index = realised window return), source (ny_fed), ingested_at. Everything here is stored exactly as the NY Fed publishes it, on the money-market basis; the restatement onto the site's annual-effective basis happens at read time and is never written back.
• Populated by: scripts/refreshers/sofr-rates.ts, weekdays 13:00 UTC (NY Fed). Each date is written only in the columns whose feed covered it, so a null average means the NY Fed published none for that date rather than that a fetch failed.
• Read by: sofr.ts (consumed by MoneyMarketRatesChart, the strategies comparison charts, the /repo-lending Vs SOFR column, the home rate tape and the assistant's SOFR tool). Because the averages publish only on New York business days, the reader walks back to the latest populated 30-day average rather than taking the newest row — but only up to 4 calendar days measured against the wall clock, which clears a Friday figure read on the Tuesday after a Monday holiday. Past that ceiling every surface withholds the comparison with cash instead of quoting one. The ceiling is wall-clock rather than measured against the series' own newest row on purpose: when the job that writes this table stops, the whole series freezes together and a row-relative rule would keep printing last month's cash rate beside supply APYs that other jobs are still updating.

curator_vault_state — 6 cols, ~35 rows

• Row: one curator-fund vault — PK token_address.
• Purpose: fee + live market allocation for the Morpho / Euler USD curator vaults, read only from our DB at request time. It fed the funds sub-view on /repo-lending until that view was removed in 2026-08; it now serves the assistant's get_curator_funds tool. The /money-market-funds tab does not read it.
• Key columns: fee (fractional performance fee), net_apy (protocol-reported, cross-check/display), total_assets_usd, allocation (jsonb array of collateral markets [{collateral, supplyUsd, maxLltv, lltv}]). Markets sharing a collateral symbol are merged into one entry: supplyUsd is their combined supply, while maxLltv is the highest liquidation LTV among them — a maximum, not a supply-weighted average, so it may belong to a market holding only part of that supply. lltv repeats the same value under the key existing readers use.
• Populated by: scripts/refreshers/curator-vault-state.ts (Morpho API for Morpho vaults, Euler on-chain/subgraph for Euler). Cadence is its own refresher, not in the standard 6h cron block above.
• Read by: curator-funds.ts / curator-vault-state.ts → the assistant's get_curator_funds tool.

newsletter_subscribers — 6 cols, ~2 rows

• Row: one newsletter signup — PK id (BIGSERIAL), email unique.
• Purpose: stores marketing-banner email signups (interim, before migrating to a real ESP).
• Key columns: email, user_agent, source, created_at, wallet_uid (migration 046: the SIWE session address at subscribe time, lower-cased; NULL for anonymous signups; deliberately NO FK to accounts so a subscription outlives an account deletion; FIRST-WINS on re-subscribe — an anonymous row gets linked when the same email re-subscribes signed-in, but an already-linked row is never re-pointed, so a later signup cannot re-attribute someone else's email).
• Populated by: app/api/newsletter/subscribe/route.ts, not a cron. The wallet comes ONLY from the verified session cookie (never the body), best-effort: a session-read failure never fails a signup. Subscribe-before-SIWE is covered client-side (src/lib/newsletter-pending.ts): the banner remembers a successful submit in localStorage and the next successful sign-in silently resubscribes it once with the session cookie attached, letting the first-wins fill link it; the key clears only on a 2xx so a transient failure retries on the next sign-in.
• Read by: the API route only.
• PII: wallet_uid pairs an email with a wallet, so scrub-staging-pii.sql nulls it on reseed and reseed-staging.sh fails closed if any link survives (column-existence-guarded for pre-046 dumps).

schema_migrations — 2 cols

• Row: one applied migration.
• Purpose: the ledger of which scripts/sql/NNN-*.sql have been run. Managed out-of-band (not created by a numbered script in this repo). Consult it before applying a migration to avoid double-applying.

Accounts + chat assistant (app state)

These tables store app state (a user's identity, chats, and daily spend), not market data, so they intentionally do not follow the agent-grade chain_id/block conventions above. uid throughout is the signed-in lowercase wallet address (SIWE).

onchain_credit.accounts (migration 042) is the neutral creddit account: one row per signed-in wallet, created on the first successful SIWE verify. The wallet address IS the account id (uid), and the same account gates every signed-in surface. It supersedes the stage-1 arrangement where the wallet address was an implicit identity with no table; 038/039 still key their own uid on the same address, and 042 seeds accounts from their existing distinct uids so no prior identity is lost.

The three chat_* tables below are dormant: the assistant's release is deferred and its UI has been removed, so nothing in the app writes them. The schema, the API that writes it and the retention cron are all kept intact for when the assistant is picked back up — see architecture.

Table	What it holds
accounts	The account itself: uid (PK, lowercase wallet address, CHECK uid = lower(uid)), created_at (account creation = the portfolio "tracked since" anchor), created_block (eth block height at first sign-in; best-effort and nullable, so sign-in never fails on an RPC hiccup), last_seen_at (bumped on each sign-in). Written by /api/auth/verify on a successful SIWE verify: insert on first sign-in, then bump last_seen_at on repeat sign-ins (created_at / created_block are set once and never overwritten). History floor (migration 103): history_floor_ts / history_floor_block are where THIS WALLET's tracked history starts — the UTC midnight of the rolling 30-day window opened when the wallet was first added, and the first mainnet block at or after it — and a CHECK makes a half-written pair impossible, because a ts with no block would have the replay window and the coverage certificate naming two different instants. The pair is resolved ONCE, on the INSERT that creates the row, and NEVER updated by a later sign-in or re-add: it is the number the wallet's wallet-token coverage row is stamped from and the number six mechanisms ask the certificate about (the replay window, the ingester's catch-up start, the enrolment gate, the re-derivation range, the per-wallet completeness stamp, the reconciler's two figures), and the certificate is from_block <= <the floor asked about> — so a floor that moved UP would hold the wallet uncertified for ever and one that moved DOWN would certify it over blocks nobody scanned. Migration 104 wrote the pair explicitly for every row that predates 103, at the 2026-01-01 / block 24,136,053 boundary those wallets already serve — so where a wallet's history starts is now a stored fact for every account and the code carries no rule about it. NULL therefore means one thing: an add whose boundary block could not be resolved, and the reader (src/lib/portfolio/history-floor.ts, UNRESOLVED_HISTORY_FLOOR) answers with the bottom of the indexed event history — the conservative direction, which withholds rather than certifying over a hole — until that wallet's next replay resolves and stores the real pair. Readers (history-floor.ts) select both columns in one statement and never catch a database error, because two of them run inside somebody else's transaction (the completeness stamp, inside the merge's global write lock; the reconciler, inside its REPEATABLE READ snapshot), where a swallowed error has already aborted the transaction and the next statement fails with an unrelated 25P02 while the caller still holds the lock. Writers (accounts.ts) name both columns on every INSERT and bind NULL into both when there is no pair, which the CHECK permits; neither upsert's conflict clause touches them, so an existing floor never moves. Grants SELECT, INSERT, UPDATE to onchain_credit. Discovery completeness certificate (migration 061): discovery_scanned_block / discovery_scanned_at say "portfolio_wallet_index is COMPLETE for this wallet as of that block/time", and are what authorise the 6h cron to BOUND the wallet's position read. They live HERE, on the FK parent whose deletion cascades the memberships away (056), so certificate and memberships die together and "certified complete + empty index" is not representable — that asymmetry (the certificate used to live in chain_scan_cursors under a scope with no FK) is the root cause of the 2026-07-21 coverage incident. accounts is also the only table that exists for EVERY pipeline wallet: a chat-seeded / never-signed-in wallet is cron-eligible via the 048 self-row but has no portfolio_backfill_state row. The block is monotone-up (GREATEST); discovery_scanned_at is stamped now() by whichever process EARNED it. A third column, discovery_reconciled_at, is the weekly sweep's own LRU key and is written ONLY by that sweep: the 6h tick advances discovery_scanned_at for the whole certified population in one statement, and Postgres now() is transaction-start time, so ordering the sweep by it would tie every row and freeze the rotation. discovery_scanned_block is forensic today (the freshness gate reads only the timestamp). See Discovery certificate lifecycle.
chat_conversations	One thread per row: id (uuid), uid, title (first ~60 chars of the opening message), timestamps.
chat_messages	One message per row: conversation_id (FK, cascade delete), role (user/assistant), parts (jsonb, the AI SDK UIMessage parts verbatim so a thread rehydrates on reload).
chat_usage	Per-uid, per-UTC-day token + request accounting. Input tokens are split into no_cache_tokens / cache_read_tokens / cache_write_tokens (migration 040) so cost visibility and the budget reflect real spend: the budget is cost-weighted (cache-reads 0.1x, cache-writes 1.25x, output 5x), so CHAT_*_TOKEN_BUDGET are weighted tokens/day, not raw. The chat route reads the caller's weighted row + the whole-app daily sum before each turn and refuses when either budget is exceeded (a wallet is free to mint, so the budget, not the sign-in, is the spend guard).
chat_profiles	Stage-2 per-user intake (risk band, base currency, size band, stable-only, term preference, notes). Written only by the set_user_profile tool, scoped to the caller's own row.
account_wallets (migration 048)	The wallets an account tracks on /portfolio: PK (account_uid, wallet), plus label (≤64 chars, no control characters) and created_at. The account's OWN wallet is stored as a row too (label ''), so the list is uniform and "is primary" is just wallet = account_uid; the 048 seed backfills that self-row for every account that existed at migration time, and /api/auth/verify writes it on each sign-in. Tracking is watch-only (no ownership proof: everything shown is public on-chain data, and the list itself is private to the account), capped at MAX_TRACKED_WALLETS = 3 rows per account (the signed-in wallet + 2). A tracked wallet gets a shadow accounts row (ensureTrackedAccount, which deliberately never sets last_seen_at) so portfolio_backfill_state's FK, the queue drain and backfillWallet(uid) work on it unchanged; last_seen_at IS NOT NULL therefore means "has signed in". wallet carries no FK of its own, so un-tracking one account's wallet can never cascade into another account's. PII: the account↔wallet mapping is private, so scrub-staging-pii.sql TRUNCATEs it and reseed-staging.sh fails closed if any row survives.

Grants go to the onchain_credit role (prod); on staging the reseed's ALTER DEFAULT PRIVILEGES grants migrate.sh-created tables to onchain_credit_staging automatically. Staging never carries chat content:scrub-staging-pii.sql truncates the four chat tables after a reseed, in the same statement as accounts and the portfolio tables (see the Portfolio scrub note for why it must be one statement).

Referential integrity (migration 056). chat_conversations.uid, chat_profiles.uid and chat_usage.uid each carry a FOREIGN KEY (uid) REFERENCES accounts(uid) ON DELETE CASCADE. Deleting an account (which happens in practice, e.g. from-scratch signup testing) therefore purges its chats, profile and usage rows rather than stranding them. The FK also makes the lowercase guarantee transitive (these three tables have no lowercase CHECK of their own): a uid must match the lowercase accounts PK, and every writer already takes uid from verifySessionCookie (lowercase only). Because a signed cookie can outlive its account (the verify-route upsert is best-effort, and a deleted account leaves a valid cookie), /api/chat calls ensureAccountRow(uid) before any persistence write, so a legitimate chat write is never FK-rejected.

Portfolio (read-only)

The three tables behind the read-only portfolio (migration 043, WS3). Unlike the chat/app-state tables above, these do follow the agent-grade conventions (chain_id keys, lowercase addresses, block-anchored rows, an explicit basis column, CHECK constraints on the row identity, explicit GRANTs): a portfolio snapshot is market data attributed to a wallet, not free-form app state. Every row is written by the WS4 6h cron (scripts/refresh-portfolio.ts) or the WS5 archive replay (scripts/backfill-portfolio-wallet.ts, basis='backfill'); the app writes only the JIT flow rows and otherwise reads only. The cron's snapshot rows are basis='provisional', and that is not a defect: it reads every leg at the chain HEAD, which is above the finalized line by design (measured 64 to 93 blocks), so the row states the finality of the read it came from — the same line the flow ledger has always split on, now applied to both relations so the two sides of one interval cannot sit on different finality assumptions. Nothing on the read path filters basis on either table, so this changes what a row says about itself and not what any surface serves. Unlike a flow row there is no promotion path: nothing re-reads a past 6h window, and promoting on block height alone would bless values that may have been read from a block a reorg replaced (the row does not record which block hash it read at). The real fix is anchoring the snapshot pass below the settle line, which is a separate change. The venue readers (src/lib/portfolio/readers/*) produce the qty_raw/index_raw pairs; the PnL engine (src/lib/portfolio/pnl.ts) derives every performance number at read time (see Metrics → Portfolio performance).

The book partition (M1) stamps each leg's native book (USD/ETH, or EXCLUDED for an asset the book map does not recognise, or one the registry declares out of coverage) on the row. A third book, BTC, existed until August 2026; migration 077 turned its four assets into declared exclusions. Narrowing the book CHECK on portfolio_position_snapshots (portfolio_pos_book_chk2) and portfolio_tokens (portfolio_tokens_book_chk) to drop the value is migration 107 (specified as 078 and written later), which refuses to run while any book='BTC' row survives (see Portfolio → Retired: the BTC book). The book map is src/lib/portfolio/buckets.ts resolving over the portfolio_tokens registry at runtime (T2), with the hard-coded map as the seed + fallback; the M1 inclusion rules (whole-account grouping for Aave/Spark, per-market for Morpho, the e-mode gate, the same-book check) are applied at read time, never by skipping a write, so a snapshot stays valid when an account's structure changes later. Data honesty (M9): a failed on-chain read is skipped, never written as zero (a written row always carries a real qty_raw); the valuation columns (value_market / value_redemption), the derived qty_underlying, and a no-index leg's index_raw are the nullable ones.

Table	What it holds
portfolio_position_snapshots	The valuation spine: one row per (chain_id, wallet, venue, position_key, snapshot_ts), append-only apart from the live tip described below, which is the one documented exception. snapshot_ts is the aligned 6h window (alignedWindowIso) on every checkpoint row and the ANCHOR BLOCK'S OWN timestamp on a live-tip row, which is what makes a tip identifiable from its key alone; block_number is the anchor block actually read. qty_raw = raw share/scaled quantity (Aave/Spark scaledBalanceOf, ERC-4626 share balanceOf, Morpho supplyShares/collateral, Pendle PT balanceOf; Fluid normal leg = the M14 invariant normalAmount x 1e12 / vaultExchangePrice, smart leg = shares x tokenPerShare / 1e18 in the pool token's units); index_raw = the same-block compounding index that grows qty_raw into accounting-asset units (Aave normalizedIncome/Debt RAY 1e27, ERC-4626 convertToAssets(1e18), Morpho (totalAssets+1)/(totalShares+1e6), Fluid normal leg = the vault-level exchange price, 1e12 scale, M14) — its absolute scale cancels in every ratio and is NULL for a no-index leg (Morpho collateral, a par/spot balance, a Fluid SMART leg). accounting_asset (lowercase) is what buckets.ts maps to book (Fluid's 0xeeee ETH pseudo-token is normalised to WETH). emode_category is the account-level getUserEMode, denormalized onto the account's Aave/Spark legs (0 = disabled, NULL elsewhere), feeding the M1(b) gate. collateral_enabled (migration 091, nullable, additive) is the ACCOUNT BOUNDARY input (issue #717): on an Aave/SparkLend supply leg it says whether the venue counted that supply as collateral securing the account at block_number. It is read from STATE at the anchor block and never reconstructed from logs, because a collateral toggle moves no tokens and its two Pool events (ReserveUsedAsCollateralEnabled/Disabled) are deliberately outside the ingested streams (tracked-contracts.ts carries the measurement). The gate is per pool and was read off the deployed implementations rather than assumed: Aave v3.5 counts a supply on the user-configuration bit 2*reserveId+1 ALONE (GenericLogic.calculateUserAccountData has no threshold condition, and LiquidationLogic seizes on that same bit), so a zero-threshold reserve — every Pendle PT reserve — is still collateral; SparkLend (Aave v3.0.2 lineage) requires that bit AND a non-zero RESERVE liquidation threshold, the conjunction its own ValidationLogic calls isCollateralEnabled. NULL means NOT READ — a row written before 091, or a supply whose configuration read failed (M9) — and the read path treats NULL as ENABLED, which is the conservative direction (an account is never presented as a cleaner same-book carry than it might be) and is exactly the pre-091 behaviour, so the column can be filled by a later repair (scripts/repair/backfill-collateral-flag.ts) without any release ordering. NULL on debt legs and on every other venue by construction. value_market / value_redemption are the two marks (M5) as the writer computed them; every writer still fills them, and the page computes both at read from the row's facts and the price and rate series (values computed at read) wherever it can: they are served only for a row whose value reads a rate no fact answers (a row written before 116), and they are what the revert path serves and what the parity census compares against. rate_raw / rate_source (migration 116, nullable, additive, paired by a CHECK) are that FACT: the rate of the leg's own accounting asset at block_number exactly as the writer read it (a wrapper's or a fund's redemption rate, the per-share rate on a share-counted leg; the PT's own rate on a Pendle PT leg, for reference) and where it came from (chain | series | live, the observed rate facts); NULL on a leg whose value reads no rate and on every row written before 116. A row with qty_raw = 0 is a RESTATEMENT, not corruption: no ordinary writer produces one (the wallet reader emits nothing at all for a zero balance, M9), and the only source of one is the ghost adjudicator zeroing a row an archive read has falsified. Zero and NULL are opposite claims here — zero says read, and there was nothing there, NULL says the price read failed — so such a row must be read as an observation, and never coerced to NULL or deleted. basis = live | backfill | provisional (082): the archive replay writes backfill, and the 6h cron writes provisional because it reads at the chain head, which is above the chain's finalized line by design (see the ownership paragraph above); live is reserved for a live read at or below the settle line. block_ts (082) is the anchor block's OWN timestamp, which is not snapshot_ts (the aligned window label the row is keyed on) and is what a block-anchored consumer should read. It is populated on live rows only — the 6h cron already reads its anchor's timestamp and stores it; the archive replay resolves a block per grid point and does not yet thread that block's timestamp into its INSERT, so backfill rows carry NULL and a consumer must treat the column as optional. OFF-GRID LIVE-TIP ROWS (the append-only exception). A live refresh (POST /api/portfolio/refresh) that read every venue cleanly, at a block whose ledger merge committed and claimed its range, stores that reading here with snapshot_ts = block_ts = the anchor block's timestamp, which is off the six-hourly grid (epoch % 21600 != 0) unless the block happens to land exactly on it. ONE predicate owns that rule (isOffGridSnapshotTs / offGridSnapshotTsSql, src/lib/portfolio/live-tip.ts) and the writer and both retire statements use it, so "what is a live tip" cannot come to be spelled two ways. At most one tip per wallet exists at a time, and it is always the wallet's newest reading by block: inside the write transaction the writer reads the wallet's newest stored block over EVERY row, checkpoints included, and refuses to insert unless the reading's block is above it (so a sync can never land below or at a checkpoint written seconds earlier, which would have netted an empty flow set against two readings; the loader drops such a row and the served selection orders by block as defense in depth); then it DELETEs the wallet's older off-grid rows below the new block in the same transaction. A complete reading that finds NOTHING writes no rows and still retires the wallet's own tips, so a wallet that closed everything falls back to its last scheduled reading rather than a tip labelled live. The 6h checkpoint deletes the tips of the wallets it wrote for at or below its own anchor inside its insert transaction (bounded to the last 90 days, a partition-key predicate that still reaches any realistic leftover; the page-load retire is bounded to seven). Only a FULL read is ever stored: the recompose fast path serves its reading in memory and writes nothing. History therefore stays on the grid: only the tip is ever off it, and it survives at most one tick. Everything else about the row is ordinary, which is the point: basis is the same flowBasisAt verdict as any other row (in practice always provisional: the reading's block is the anchor and the settle line sits at least 64 blocks below it, so a reorged tip is superseded by the next reading rather than repaired in place), block_ts is always populated, and every reader reads it without knowing it is a tip. No schema change was needed: the PK already carries snapshot_ts, the CHECK already admits all three bases, and the monthly partitions are keyed on snapshot_ts (ensureMonthPartitions runs before the write). venue CHECK admits fluid (populated by the FWS2 reader). escrow in-transit legs (the queued-exit venue): position_key is escrow:<classId>:<claimKey> — the escrow CLASS from src/lib/portfolio/escrow-registry.ts (lido-withdrawal-queue, etherfi-withdraw-queue, the two Maple/Syrup queues, the two Ethena cooldowns) and the claim TICKET, which is the request id or ERC-721 token id where the class has one and <owner>:<payout asset> for the Ethena silos, which have no ticket at all. So the claim key MAY contain colons and only the second part may be parsed as the class. accounting_asset is the payout asset (the native-ETH sentinel for Lido/EtherFi), qty_raw is the CLAIMABLE amount re-read at the anchor block — never the requested amount, because 1 of 1,739 matched Lido claims settled 50.5 bps short — and index_raw is always NULL (an entitlement to a fixed payout has no compounding index). Ownership is resolved from chain state at the anchor block, so a sold claim NFT moves the leg rather than orphaning it. These rows were excluded from every served snapshot read for one release (the retired reader's own venue guard, v1-read-guard.ts): the attribution shipped then had no internal_out/internal_in, so it would have read the leg as a gain for the cooldown and an equal loss at the claim. The guard went with that reader, and the engine serving the page has both kinds, so the rows are read like any other leg — including by the disappearing-position alarm. Fluid position_key shapes: a T1/normal leg is 6 colon-parts fluid:vault:<addr>:nft:<id>:supply|:debt; a SMART leg is 7 parts with the pool token BEFORE the terminal side fluid:vault:<addr>:nft:<id>:<tokenLower>:supply|:debt (so endsWith(':debt') still yields the side, and both shapes group by the first five parts = one NFT, up to 4 legs for a T4). Indexed for per-wallet time series (portfolio_pos_wallet_ts_idx on (chain_id, wallet, snapshot_ts DESC)); the per-book split is bucketed in memory (REAL_BOOKS.map in ledger-v2-api.ts), never a SQL predicate, so migration 058 drops the redundant (chain_id, wallet, book, snapshot_ts) index that 043 had added. Migration 059 adds two indexes that serve loadPendleMarkets' held-by-anyone probes (M13): a full (chain_id, accounting_asset) index (arm 2 — a PT held as ANY venue's accounting asset, e.g. an aave/spark PT-collateral reserve whose underlying IS the PT) and a partial (chain_id, lower(split_part(position_key,':',3))) WHERE venue='pendle' functional index (arm 1 — a direct pendle leg); both replace a former all-users sequential scan. Partitioning (migration 067, Phase C D8): repartitioned monthly BY RANGE (snapshot_ts). The PK already carries snapshot_ts, so the partition key is in every unique constraint and the snapshot writer's ON CONFLICT target is UNCHANGED (writeSnapshots as of 067; the statement now issues from commitTickWrites); the writers auto-create future month partitions (ensureMonthPartitions, a safe no-op before the migration), called POOL-SIDE before each writer's transaction opens, for two reasons: CREATE TABLE … PARTITION OF takes ACCESS EXCLUSIVE on the parent and must never sit inside a transaction that also holds the global portfolio write lock; and the helper's process memo is non-transactional, so a create rolled back with its caller's transaction would leave the memo claiming a partition Postgres has un-created (a source-level tripwire in partitions.test.ts enforces the placement). The DDL runs under a 2s lock_timeout so a queued ACCESS EXCLUSIVE cannot stall the whole (FIFO) lock queue behind one slow reader, and the 6h cron pre-creates the current + next month so the rollover never fires from whichever writer happens to be first into a new month. The "is this table partitioned?" probe caches true permanently but expires false after 60s, and any 23514 no partition of relation invalidates it immediately (notePartitionWriteError) — without that, a writer process that was already running when this DESTRUCTIVE/manual migration was applied would keep issuing plain INSERTs into a table that no longer accepts them for the rest of its run. 067 is -- DESTRUCTIVE/manual (rename-aside + data-preserving copy, _preswap kept for a manual DROP — see the 067 release step).
portfolio_flow_events — DROPPED by migration 095 (-- DESTRUCTIVE, hand-run per environment; see the retirement runbook). Kept here as the record of what the pages used to read. Replaced by portfolio_flow_events_v2	The old flow ledger (M3/M4): one row per (chain_id, wallet, tx_hash, log_index, leg), append-only, so an event can never be double-counted. wallet is in the PK because a single ERC-20 Transfer of a position token (aToken / ERC-4626 share / PT) between two registered wallets is ONE log that the scan nets into TWO rows sharing that log_index ({sender: transfer_out}, {receiver: transfer_in}); a PK without wallet would collide and drop the receiver's transfer_in, whose +balance would then read as yield (violating M3 "a flow is not profit"). leg (migration 044, FWS3) is the fifth PK column for Fluid: one Fluid LogOperate carries a collateral AND a debt delta (one log → two rows), a SMART leg splits into two pool-token rows per side, and an NFT transfer moves multiple legs under one log, so (chain_id, wallet, tx_hash, log_index) alone cannot hold them. Every non-fluid venue emits one row per (wallet, tx, log) and leaves leg='' (the column DEFAULT); Fluid sets it to the position_key tail (supply/debt/<tokenLower>:supply/<tokenLower>:debt) or, for a state-diff liquidation row (whose provenance is a shared last-LogLiquidate on the vault), the NFT id. kind ∈ deposit/withdraw/borrow/repay/transfer_in/transfer_out/liquidation. amount_raw is the event's native units (a/debt-token Transfer amounts are balance units = scaled × index; a Fluid smart leg's shares are decomposed to the pool token's units at the flow block). Flows are netted per tx hash at read time (a leverage loop is not a deposit, M3); LiquidationCall (Aave/Spark), Liquidate (Morpho), and a Fluid state-diff liquidation (M15) land as kind='liquidation' and are a realized loss, never a user withdrawal (M4). A Fluid liquidation row's position_key is the NFT GROUP PREFIX (fluid:vault:<addr>:nft:<id>, 5 parts). value_market / value_redemption value the flow at its block in the same mark as the curve it adjusts (M5). The venue CHECK mirrors the snapshot table. basis ∈ live | backfill | provisional (migration 070): live = derived by a scan that had SETTLED the block, i.e. a row at or below its writer's settle line; backfill = the WS5 archive replay; provisional = a row in the reorg-exposed tail ABOVE that line, which BOTH writers produce. The line is min(anchor − 64, the chain's own finalized head) (P4; contract §4.9 / decision 34), read once per tick from eth_getBlockByNumber("finalized") and applied to the snapshot spine on the same terms — a line that marks the flow rows of an interval provisional while marking its snapshot rows live would put the two sides of one interval on different finality assumptions. So the tail is the finalized-head gap, measured 64 to 93 blocks, not a fixed 64, and the 64 survives only as a FLOOR under its own name (SETTLE_SAFETY_FLOOR), distinct from the read-consistency scan margin (SCAN_SAFETY_BLOCKS) it used to be conflated with. The 6h cron is therefore no longer a settled writer by construction: its scan stops at anchor − 64, which sat above the finalized line in five of six measured samples, so its own top rows are written provisional exactly like a JIT row. Every flow scope's cursor consequently stops at min(scanBound, settleLine) rather than at its scan bound — a cursor at the scan bound would park that residual permanently behind it, re-scanned by nobody — so the next tick re-derives those rows on the same coordinates and the upsert promotes them. That is also the invariant the sweep leans on: every provisional row a scope writes sits ABOVE that scope's own saved cursor, hence inside the range that same scope re-derives before any sweep can reach it. If the finalized head cannot be read at all the line is NO_SETTLEMENT and nothing settles that pass: rows stay in the tier their writer can still delete, the sweep is a no-op, and every cursor holds. A provisional row is an ordinary flow row on every READ path (same columns, same valuation, included in /portfolio exactly like a live row: there was no basis predicate anywhere on the read path); it differs only in who may DELETE it. Three deletes retire the tier, and together they are what makes a reorg unable to double-count a flow: the JIT clears this wallet's provisional rows over the range each resync re-derives (same transaction as the re-write, so a tx re-included at different (tx_hash, log_index) coordinates cannot leave the old row behind under a PK the upsert would never collide with), and the 6h cron clears the provisional rows over exactly the region it has now derived authoritatively -- the wallets its own flow scans ran with, between the lowest block any scope scanned and the tick's settle bound -- in ONE sweep after every settled write of that tick has committed (rows outside that region survive: retiring a row the tick did not re-derive would delete a flow nothing can reconstruct). That sweep excludes the synthetic native-ETH balance-diff rows outright, and the third delete is the one that retires them: a diff row is not scanned from a log, it is derived from the INTERVAL between two balance observations, so re-deriving "the blocks in [from, to]" re-derives nothing about it and a block-bounded statement that reached it would remove a flow nothing had re-written. The settled native-ETH write supersedes the JIT's provisional diff row instead, in the same transaction, bounded per wallet by that wallet's own previous snapshot below (EXCLUSIVE) and this window above — the exclusivity is what keeps the previous window's own diff row, sitting exactly on the boundary and covering the interval BEFORE it, out of reach. The two scopes are exact complements on all three clauses (venue, position_key, the synthetic tx_hash prefix), so the two statements partition the ledger and exactly one of them can ever retire such a row. A wallet with no previous observation is in neither: an opening balance is not a flow (M3), so no settled row is written for it and the JIT, reading the same relation, had nothing to diff either. A settled write on the same PK simply PROMOTES the row (basis = EXCLUDED.basis); the reverse is forbidden (a provisional write never demotes an already-settled row, which would expose a cron/backfill row to a sweep that nothing would re-derive). All three are bounded on BOTH sides by what their own writer has just re-derived — the first two by the block range, the third by the interval, per wallet — and all three are served by the partial index portfolio_flow_provisional_idx (chain_id, wallet, block_number) WHERE basis='provisional' (tiny by construction: every tick empties the tier up to its settle bound). Indexed newest-first per wallet and per position for netting; migration 059 adds a full (chain_id, asset) index (arm 3 of loadPendleMarkets' held-by-anyone probes — a PT held as ANY venue's flow asset) and a partial (chain_id, lower(split_part(position_key,':',3))) WHERE venue='pendle' functional index (arm 4 — a direct pendle flow leg), both replacing a former all-users sequential scan. ⚠ DEPLOY + ROLLBACK WARNING (044), both directions: the leg column and the 5-column PK are a coordinated change, and deploy.yml ships the SQL file on the box with the code (single atomic ssh script), so neither direction can be sequenced away. Forward (deploy, then migrate): between the code restart and the manual migrate.sh 044 run there is a window where EVERY flow insert fails for EVERY venue, because the new writers reference a schema that does not exist yet (column "leg" ... does not exist, or there is no unique or exclusion constraint matching the ON CONFLICT specification if leg exists but the PK swap has not run). The window is loud (the cron-failure alert fires), atomic and non-corrupting (a rejected insert writes nothing), and self-healing (the first cron tick after 044 writes normally; the JIT path degrades to stored history, jit:false, meanwhile), so run migrate.sh 044 immediately after the deploy, before the next 6h tick. Rollback (code rollback after 044 is applied): the previous release's flow writers use the 4-column ON CONFLICT, which no longer matches the 5-column PK, so they are HARD-BROKEN for ALL venues until the code rolls forward again. That release ships the 3 widened writers atomically (writeFlows, writeFlowRows, writeBackfillFlows — the names as of 044; the tick's half is now commitTickWrites); rolling back the code without reverting 044 wedges the flow ledger. Accepted (staging first; the two-release expand-then-contract sequencing was considered and rejected as not worth the delay for a staging-gated feature). See deployment.md (the 044 note) for the exact migrate.sh invocation and the one-time scripts/seed-fluid-event-log.ts seed step, in order. Partitioning: PARTITION BY HASH (wallet), MODULUS 16 (migration 072, Phase C D8 — the flow half). The partition key is wallet, which is ALREADY the second column of the 5-column PK, so the primary key is unchanged, verbatim: the partition key sits inside every unique constraint, all three writers' ON CONFLICT (chain_id, wallet, tx_hash, log_index, leg) targets still match it exactly, and no writer needed a single change. Every single-wallet statement on this table filters wallet by equality, so the planner prunes it to exactly ONE of the 16 partitions — verified on PG 16 through the app's own driver: the per-wallet ledger read, loadMaxFlowBlock, the /events count + page, the JIT provisional self-clean DELETE and its pre-flight EXISTS, and both backfill DELETEs all plan to a single partition. The multi-wallet variants (wallet = ANY($2)) prune per value at plan time, which means the partition count SATURATES fast: n distinct wallets cover 16 × (1 − (15/16)^n) partitions on average (6.5 at n=8, 10.3 at 16, 14.0 at 32, 15.7 at 64). The exact count is sample-dependent — it is a hash of the actual addresses — so measured on PG 16 across four independent wallet samples: n=1 → 1 every time, n=8 → 6–8, n=16 → 9–10, n=32 → 12–15, and n ≥ 64 → all 16 in every sample. Read it as "saturated by ~64 distinct wallets", never as a per-n constant. Two of the four are passed the tick's entire wallet population, so they touch all 16 today at ~500 wallets and will at 100k — the cron settle sweep (handed the full scanWallets set, on the write path, inside the transaction holding the global portfolio write lock) and the cron's explanation-set probe (the whole walletsWithTwo batch). Only the reconciler's aged-drift probe (drifted wallets) and the aggregated /events page (a user's own tracked wallets) prune usefully. That is an accuracy note, not a regression: against an identically-seeded un-partitioned copy the plans are equivalent in kind (Bitmap Heap Scan, Index Scan becomes Append over 16 relations a sixteenth the size, same rows filtered). The loadPendleMarkets held-by-anyone flow arms that also named no wallet lived only in the pre-065 fallback query, which is deleted, so they are not a cost this scheme pays. MODULUS 16 puts ~6,250 wallets per partition at the 100k target; a hash modulus is fixed at CREATE time, so all 16 partitions are created ONCE by the migration and nothing creates a flow partition at runtime, ever — which is why this parent stays owned by postgres (unlike raw_events and the snapshot spine, whose runtime auto-create forced an ownership transfer to the app role) and why the flow writers' ensureMonthPartitions calls are deleted rather than left as no-ops. The helper additionally refuses any non-RANGE parent outright (it probes pg_partitioned_table.partstrat, not just relkind) and no longer even ADMITS this table in its signature (MonthPartitionedTable excludes it, so a call copy-pasted from a snapshot writer does not compile), because a month partition is range syntax and Postgres rejects it against a hash parent (invalid bound specification for a hash partition) — a surviving call would have thrown on the write path rather than no-opped, which is exactly why 072's deploy order is one-way (see the rollback warning below). MONTHLY RANGE was designed and REJECTED, not deferred: ts is not in the PK, so a range key forces it in and breaks all three 5-column conflict targets (a coordinated writer+schema change with a hard deploy order in both directions, plus a reorg-dedupe problem — with ts in the key the same log re-scanned at a corrected timestamp becomes a second row instead of an idempotent overwrite); and a month key prunes NO hot read here, since not one hot statement carries a ts predicate, so it would only fan each of them out over one relation per month, 12 more a year, for ever. What hash does not buy: it balances WALLETS, not BYTES (one whale's rows all land in one partition), and it gives up per-month retention DROP — irrelevant here, since the flow ledger is the product and is never pruned. ⚠ DEPLOY + ROLLBACK WARNING (072), one-way: post-072 code works unchanged against the still-un-partitioned table (the deleted call sites were no-ops there), so the FORWARD direction is free — but pre-072 code does NOT work against the hash table. Every flow read and write is fine; the vestigial ensureMonthPartitions call in front of the write is not. The previous release's helper gates on relkind = 'p' alone, so once the parent is hash-partitioned those five call sites (live.ts, the 6h refresher, and three in backfill.ts) stop no-opping and start emitting month bounds at it, which Postgres rejects (42P16 invalid bound specification for a hash partition) with no self-heal path (isMissingPartitionError requires 23514) and a permanently-memoized positive probe. So 072 must be applied AFTER its code release, and that release is a hard rollback floor: redeploying any earlier build against the hash table wedges every flow write — loudly on the 6h tick (in the pre-072 build this warns about, which committed its snapshots first, leaving balances that read as yield, M3; a current build publishes both halves in one transaction, so a failed flow write rolls the snapshots back with it) and SILENTLY on the JIT path, whose catch only logs mini flow scan failed. Reachable without an operator decision, because deploy.yml reverts to $PREV on a build failure. See deployment.md. Relocated logs (independent of any of that): raw_events is keyed with block_number, inserted ON CONFLICT DO NOTHING, and never reorg-pruned, so a log RELOCATED by a reorg deeper than the ingester's 64-block margin is held twice, durably. Both copies classify into flows identical in (wallet, tx_hash, log_index, leg) and differing only in block — a pair one upsert statement cannot absorb (21000, "command cannot affect row a second time"), so it aborts the whole batch and keeps aborting it every tick. ledger-scan.ts dedupeRelocatedFlows keeps one row per coordinate at the ledger readers' output, highest block wins. The synthetic native-ETH diff rows are held to the same one-row-per-coordinate rule from the other end: ts is stored FLOORED, exactly as nativeEthDiffTxHash floors before hashing, so a row's tx_hash is always re-derivable from the ts it carries.
portfolio_tokens (migration 049, T2; sync T6)	The token registry — the single source of truth for (a) the accounting-asset → book map src/lib/portfolio/buckets.ts consults at runtime (registry.ts loadPortfolioTokens builds bookMap over EVERY row, and accountingAssetBooks() is a memoised LIVE READ of these rows since #810 piece E; the hard-coded ACCOUNTING_ASSET_BOOKS constant that used to be its fallback is deleted, because a map computed from the shipped seed at import cannot see a row an operator edited after the deploy) and (b) the wallet-venue sweep universe (the wallet_tracked rows = balanceOf/eth_getBalance targets). One row per (chain_id, address) (lowercase; native ETH sentinel 0xeeee…eeee). book ∈ USD/ETH or NULL — a declared exclusion: the token is valued in USD and shown under "Outside the yield book", but never charted into a book curve. Five kinds qualify: a non-based fiat (EURC), a non-USD/ETH denomination (XAUt is gold, AAVE is a governance token), a dollar-denominated token with no honest par claim (apxUSD — redemption is whitelist-only and settles at a value tracking an offchain preferred-share basket, plus a live secondary discount, so par would over-report it), an accruing wrapper over such a token (apyUSD, the ERC-4626 over apxUSD — a share of a non-par base is not a dollar claim either), and — since August 2026 — bitcoin and its wrapped forms, whose book was RETIRED rather than never granted (WBTC, cbBTC, LBTC, eBTC; see Portfolio → Retired: the BTC book). Migration 071 added XAUt + apxUSD, 073 added apyUSD + AAVE, 074 added the FalconX pair (AA_FalconXUSDC and its 1:1 wrapper wFalconX) + sUSDat, and 077 flipped the four BTC rows (book NULL, wallet_tracked false, rate_source NULL — their bare balances moved to a live disclosed-holdings path so a bitcoin-priced figure never rendered inside the USD statement; migration 105 sweeps them again, see below). A follow-up migration 078, held back to the release AFTER the retirement deploy so the ledger runner cannot apply it in the same invocation as 077, then narrows the book CHECK here and on portfolio_position_snapshots to drop 'BTC', guarding itself against any surviving row (body in the coverage-expansion plan). The FalconX pair left that bucket on 2026-09-17 (migration 109, below), and the correction is worth stating because the original reading was not wrong about the chain, only about which question the chain answered. Everything 074 verified still holds: requestWithdraw on the CDO reverts NotAllowed() for any caller without a Keyring credential, proven at an archive block where the redemption window was genuinely open, with the same call succeeding when only the credential check is stubbed out; Morpho Blue holds a large share of the tranche and is not credentialed, so neither it, nor a liquidator seizing that collateral, nor an ordinary holder who received the token by transfer can redeem it. What that establishes is an exit term, not an absence of value: AA_FalconXUSDC is the senior tranche of a credit vault lending USDC to FalconX, and a tranche of a credit vault is a fund share — its value is its NAV per unit times the value of what the vault holds, whoever is allowed to ask for it. So it is valued the way every fund share is (composed, NAV x USDC, the NAV read on chain at the leg's own block from the vault's own virtualPrice()), and the credential gate is SHOWN as the holding's exit term, on the same row annotation a wound-down Fluid vault or a matured PT already uses. The bucket that remains — "a dollar-denominated token with no honest par claim" — is apxUSD and apyUSD, and only those two. sUSDat is the accruing-wrapper kind and a policy-consistency call: its yield is the same offchain preferred-share dividend stream as the apyUSD family, and its own convertToAssets reads 0.9195 USDat per share, below par since that basket's June 2026 drawdown. A declared row classifies exactly as an unregistered one (both resolve EXCLUDED); what it changes is that the WS8 unknown-asset alert stops paging on a decision already made. class ∈ par (idle, yield ≡ 0) / variable_rate (a wrapper whose bare balance charts as a Variable rate asset); wallet_tracked gates the sweep; rate_source is the token's own address for a variable_rate row (its redemption rate resolves via the block-pinned rate_getter on this same row or token_yield_apy.share_rate); source ∈ buckets-seed/asset-profile/stablecoin-top20/manual; status ∈ active/retired. Seeded from the buckets map, the 13 asset profiles (+ eBTC), and the top-20 idle stablecoins + ETH/WETH + BTC wrappers (the BTC rows survive as the RECORD of the retirement decision: an active row is "covered" to the weekly sync and the WS8 alert, so neither re-raises a settled question); migration 053 adds sGHO (Aave Savings GHO) as a variable_rate asset-profile row. Migration 074 (the August 2026 USD yield batch) adds four variable_rate, wallet_tracked USD rows (USD3, with source='asset-profile' since the profile ships in the same release, plus sUSDD, stUSDS and wsrUSD) and PROMOTES the srUSDe row 049 had seeded untracked and rate-source-less, so bare srUSDe balances now enter the wallet sweep alongside the PT-srUSDe collateral that row was originally for. Two traps that migration pins in writing, because a silent mis-read is the failure mode here: USD3 is 6-decimal on BOTH sides and is NOT Reserve's 18-decimal Web3 Dollar of the same ticker; and wsrUSD is itself the savings vault (its asset() is rUSD, the par stable, which is already a wallet-tracked 049 row), not a wrapper over srUSD, so no srUSD row exists or is wanted. Migration 085 adds the IPOR Fusion vault share rETHLPC as an ETH-book variable_rate row on the shape 049 gave the other multi-strategy fund shares (wallet_tracked false, rate_source NULL, source='buckets-seed'): the erc4626 venue reader emits its position off the static universe with WETH as the accounting asset, so sweeping the share as a bare balance would double-count the same holding under two venues. Nothing is re-booked by it — no reader had ever emitted a leg for that address — so unlike 074 it owes no per-wallet history repair. Its decimals is 20, not 18: IPOR Fusion mints shares at the underlying's decimals plus two, and this column is the balanceOf divisor, so a copied 18 would descale every quantity by a factor of 100 without failing (the matching share-rate and supply divisors live in src/data/erc4626-universe.ts; see the vault's section in the pipeline). Coverage extensions are rows here, not code changes: adding a variable_rate, wallet_tracked row makes a new asset chart with no rebuild (loadPortfolioTokens picks it up). The corollary is that such a row is only safe once its redemption-rate path exists: a USD book with no resolvable rate makes valuation SKIP the leg (M9) instead of storing it, which is strictly worse than the EXCLUDED-with-market-value treatment the leg had before, so 074's rows ship in the same release as their rate_getter or token_yield_apy series. The weekly sync-portfolio-tokens.ts proposes top-20 stablecoin additions + asserts profile/rate-source completeness (WS8 alerts); --approve <SYMBOL> inserts, a human retires. GRANTs SELECT, INSERT, UPDATE, DELETE to onchain_credit (the sync's writes). Migration 099 (#810) turns this table into the registry that decides EVERYTHING about a token, and the code lists that used to describe an asset become projections of it — including the par set redemptionRateKind reads, which is now class = 'par' plus the two par-rebasing ETH bases (stETH/eETH, whose class is variable_rate because one SHARE does not redeem at par) minus the retired-BTC-book wrappers, rather than a hand-kept literal beside the column. valuation ∈ market/composed/derived/identity says how the asset is valued; feed ∈ dune_tape/dune_dex_ratio/llama_aggregate or NULL says where its price series comes from, for its WHOLE series. That column defines every feed set and they cannot disagree: the STANDING set (what the spike gate judges and the dark-feed alert watches) is feed IS NOT NULL AND status = 'active', the DUNE CSV is its feed = 'dune_tape' half, and the other two legs — the routed saved query and DefiLlama's aggregate — fill their own rows. The three PARTITION the standing set, they do not overlap: a token two writers both filled would have its provenance decided by whichever execution landed first and frozen there permanently by the append-only bar table, so partitionBySource splits every token list and upsertBars refuses a bar offered under the wrong source at the one choke point they all share (mirror-coverage.test.ts asserts the partition over the rows). llama_aggregate carries one further restriction that is a rule rather than a convention: it may price an IDLE or no-base row and never a variable_rate row that has a base, because an aggregate blends venues and a blended quote's venue-mix wander would land in that row's published basis line as a basis nothing in the market did; feed_config (jsonb) carries the saved query id and cooldown for a routed token, and is NULL for every other kind; rate_kind ∈ getter/series and rate_getter (jsonb {kind, target?, divisorPow10?, shareDecimals?, quoteAsset?, quoteAssetDecimals?} — the last two for an oracle kind, whose price is quoted in a named asset and whose scale depends on that asset's decimals) say where the redemption rate comes from — a block-pinned on-chain read at the leg's own block by default, the stored six-hourly series only where the rate is not readable on Ethereum at a block and then DECLARED (sUSDai, whose ERC-4626 accounting lives on the Arbitrum hub); underlying_address is what a composed row composes against and what a market wrapper's redemption line composes down to; wrapper_address is the series a derived row is priced through (stETH via wstETH); unit ∈ token/shares (shares for stETH and eETH, whose balance moves without a transfer, so the share count is the constant a ledger can net on); liquidity ∈ secondary/primary_buffer with liquidity_verified moved here from three static arrays; profile_ticker + issuer move the asset-profile link off coveredAssets(), which is what makes "a profiled token always has a wallet-token row" true by construction rather than by comment (cbETH had a profile and no row at all for four months); and composed_evidence carries the one line justifying a composition. CHECKs enforce the vocabulary plus the two shapes that are meaningless half-stated: a composed row has an underlying and a derived row a wrapper. The seed is generated from src/data/token-registry.ts and a unit test fails the build if the two drift — the TypeScript seed is what a process answers from before initRegistries() overlays the database rows, so the two being the same data is what makes a box between a deploy and its migration behave exactly like the release it is running. 099 also retires the pinned class (M19): every idle asset declares feed = 'dune_tape', so the ~15 par assets that were marked at exactly $1 whatever the market said are Dune-marked, and sUSDS, the one declared pin, is market. That is a METHOD change over stored rows, so the release carries a full re-derive of every tracked wallet. Migration 102 (#811) adds the first three Pendle PT PAYOUT assets — USDat (0x23238f20…, Saturn Dollar, 6 decimals), trUSD (0xd0580192…, Tori Finance) and iUSD (0x48f9e38f…, InfiniFi) — as par / market / dune_tape USD rows in the same shape as the twenty-three seed stablecoins. A PT settles into its payout asset, so tracking that asset is what lets a PT state a dollar at all; the choice of which three is HOLDINGS-driven and never automatic (a newly listed Pendle market does not add its payout asset here), and a PT on one of the sixteen still-untracked payout assets shows under Other with the reason payout-asset-not-tracked rather than dropping out silently. Both facts that fail silently were measured rather than assumed: decimals() was read on chain (USDat is 6-decimal, and this column is the balanceOf divisor), and prices.hour was counted before the feed was declared — 703 hourly rows for each of the three over the 30 days to 2026-09-09, against the three ZERO-row phantoms 099's own check caught. Their price HISTORY is bought by the DEPLOY, not by the migration and not by a runbook step: declaring a feed enrols a token in the mirror's auto-backfill on add (R6), and the standing feed set is read from the COMPILED seed, so the first refresh-assets.ts tick after the code ships buys 2026-01-01 → now in three 90-day Dune executions — every floor-loading token on one CSV, so these three cost three executions between them, about twelve credits one-off, bounded by the refresher's own DUNE_MAX_CREDITS_PER_RUN ceiling. Two of the three executions return nothing (all three tapes begin 2026-08-11) and still pay their scan; floor_loaded records that the load RAN, so the empty span is bought once. It happens on prod, since staging runs no scheduled refreshers; a hand-run on staging would re-buy after each nightly reseed only until a prod backup carrying the floor_loaded rows is restored (the reseed restores prod's dump and the PII scrub does not touch token_price_bars or token_price_sync_state). scripts/backfill-token-price-bars.ts is a repair tool here, not a gate — the auto-load resumes itself if a guard stops it. Migration 105 applies the COVERAGE RULE settled 2026-09-16, and it is the file that separates two decisions this table had been conflating. SUPPORT is decided by what a tracked wallet can be holding: a live Aave v3 / SparkLend reserve, an asset in a Fluid vault we list or a Fluid lending token, an asset in a curated (active) Morpho market, an active Pendle PT and its payout asset, a listed fund on either funds tab, an asset with an asset profile, or the 1:1 twin of one of those. BASE decides only which VIEW a holding appears in — dollar-based in the USD view, ether-based in the ETH view, everything in the All view, a book NULL row in the All view ONLY — and it has never decided whether the balance is READ. So 105 does three things. It ADDS eighteen rows (seventeen live Aave v3 / SparkLend reserves — LINK, UNI, CRV, BAL, SNX, ENS, LDO, RPL, 1INCH, LUSD, mUSD, sDAI, eUSDe, tBTC, FBTC, BTC.b, ETHx — plus USDai, the dollar Fluid vaults 174 and 180 lend against), with addresses and decimals taken from lending_reserves and then READ ON CHAIN: tBTC is 18-decimal, not 8, and this column is the balanceOf divisor. USDai enters with book NULL, alongside apxUSD: a book plus class = 'par' is what makes the writer store a holding at quantity × 1 on the redemption mark whether or not anything marked it, and this contract has no redeem path to support that — so it is valued MARKET-only in dollars, in the All view, like every other declared exclusion. It flips wallet_tracked on for the ELEVEN bare tokens the old rule left unswept for want of a base (WBTC, cbBTC, LBTC, eBTC, XAUt, AAVE, apxUSD, apyUSD, sUSDat, wFalconX, AA_FalconXUSDC), which is what deletes the live disclosed-holdings read path with it. And it RETIRES eleven (FDUSD, GUSD, TUSD, USD0, USD1, USDD, USR, rUSD, eUSD, stUSDS, wsrUSD): each was seeded from a top-20 stablecoin ranking or an August yield batch and none is reachable from anything the product lists — verified against prod and staging on 2026-09-16 against live reserves, listed funds, active Pendle markets, ACTIVE curated Morpho markets, listed Fluid vaults and the PT payout rows, all zero. The ONE remaining untracked population is the fund shares the ERC-4626 reader already values as a vault:<addr> leg: sweeping those as bare balances too would count one holding twice, and token-registry.test.ts derives that exception from the reader's own universe rather than from a list. A CHANGE to a row an earlier migration seeded cannot be written in that migration's own file — the runner records a migration by FILENAME and never re-runs it — so 105 carries a generated block of per-address UPDATEs alongside its INSERT, narrowed to five columns (wallet_tracked, status, feed, liquidity, liquidity_verified) so it can neither re-book a row nor rescale a quantity. FEEDS were measured before they were declared, the rule 099's P1 check left behind: one Dune query over prices.hour for all 23 touched addresses over [2026-08-17, 2026-09-16), 720 hourly slots. Nineteen printed 720/720 or 701/720 and take a dune_tape; FOUR do not and take none — BTC.b (231/720, last print 2026-08-29: a tape that STOPPED), USDai (81/720, first print 2026-09-12: a tape that has only just started), wFalconX and AA_FalconXUSDC (0/720). Those four are tracked, listed and value to a dash, which is the honest report; declaring a feed nobody fills would page the dark-feed alert every six hours forever. Migration 109 (#907) gives all four a price, and the dash is gone. It was the CHANNEL that was absent, not the market. Two of them take a new feed kind, llama_aggregate: BTC.b's Dune tape stopped on 2026-08-29 while Uniswap v4 alone took 242 trades worth $12.3M in the same window (last trade 2026-09-17), and USDai's had only just started — DefiLlama quotes both hourly back to 2026-01-01 at confidence 0.99, checked point by point over 2 x 168 hours: the observation the vendor answers with runs about eight minutes EARLY and does so consistently (median offset 500s on both, p90 510s, worst 800s on BTC.b and 560s on USDai, all 168 of USDai's points and 119 of BTC.b's sit about 500s before the hour they are stamped at and BTC.b's other 49 within 140s after it, none of them near the bound), which is a systematic bias rather than noise and still leaves the half-hour matching bound 2.25x of room. USDai ALSO takes the dollar book, correcting 105: that file read "par" as a claim about the Ethereum contract, while the par test creddit applies everywhere else is ECONOMIC — a peg history plus a backing, and USDC's own Ethereum contract has no redeem function either. Booking it is safe only because the feed lands in the same file: a booked par row answers identity on the redemption line, so without a market mark beside it nothing could contradict its face value. The FalconX pair stops needing a quote at all: the tranche is composed onto USDC at the credit vault's own virtualPrice(AA_FalconXUSDC) (1.108325 USDC per tranche token at block 25,996,646; 136,072,945.291605681 tranche tokens x that = $150,813,047.09 against the vault's own getContractValue() of $150,813,232.94, the $185.85 gap being the getter's last decimal place — the two figures imply 1.108326365807 per token against the getter's 1.108325, 1.37e-6 apart or about 168 tranche tokens' worth, and the junior tranche's totalSupply() is 0 at that block, so there is no other claim on the contract value for the difference to be; the valuation multiplies by virtualPrice() itself, so the residual never reaches a mark), and wFalconX is composed onto the TRANCHE at a rate of one — read, not assumed: its totalSupply() and the tranche balance it holds agree to the wei at that block, and its mint/burn move one for one. The rate_getter divisor for the tranche is 6, not 18 (USDC's 6 decimals + 18 - the tranche's 18), and it is the number that fails silently if copied. 109 is the first AMENDMENT-ONLY migration: it seeds no rows (all four addresses already have one, from 099 and 105), it widens the feed CHECK to admit the new kind (dropped and re-added by name, so the file is idempotent), and its UPDATE block is generated from src/data/token-registry.ts like 105's — with a WIDER column set, deliberately, because the change it carries IS a re-booking. Apply it AFTER the deploy: all four rows already exist, so the database's feed and book win over the compiled seed until it lands. The reason for the ordering is USDai and the FalconX pair, not BTC.b — the old bundle's loader admits only the three feed values it knows and installs NULL for anything else, so llama_aggregate reads to it as NO feed and nothing can put BTC.b or USDai in a Dune CSV or on the dark-feed alert. What an early apply WOULD do is leave USDai booked par with a market mark that bundle cannot fill (full face value on the redemption line with nothing able to contradict it) and leave the FalconX pair booked USD and variable_rate behind a getter kind it has no reader for, which makes the redemption resolver raise and the leg SKIP (M9) — the holding vanishes rather than showing a dash, and it is not in the "outside" group either, because that group is where an EXCLUDED-book row goes and this row is no longer EXCLUDED. Neither is permanent (no bar is written under the wrong source and a snapshot is re-derivable); the order is what stops a holder seeing either. Migration 111 puts the PRICING CATEGORY's evidence on the row (pricing categories, 2026-09-22). Every tracked asset is valued either at what it trades for or at the rate it redeems into, and which one it is is a question about the route a holder actually has — so mint_terms and redeem_terms (∈ instant / capped / queued / gated / none, NULL on an idle row and on a fund share, neither of which is tested) say how the token is created and how it is turned back, capacity_reader (∈ erc4626 / sky_savings / rocket_pool / none) names which reader can measure that route's capacity, and terms_verified is the date the pair was read at the issuer's own contracts. instant means atomic, permissionless AND uncapped; capped means atomic but bounded, which pins a price only while the bound is at or above $5M — which is why the capacity is a SERIES (see token_pricing_measurements below) rather than a fact recorded once. loopable and loopable_verified are the fifth and sixth columns and the only two nothing seeds: the six-hourly job derives the flag from the same two facts the category test uses, and NULL is an answer ("not judged yet"), never a false. The date beside it is written on every tick the verdict is known, changed answer or not, because the flag itself can never be retracted there — an unmeasured tick must not publish a false — so without a date a flag judged in June by a job that has since stopped reads exactly like one judged this morning. Every CHECK is derived from the same value list the loader bounds against, the pairing feed already carries: a value the column admits and the loader does not read back is a fact the database holds that no running process can see. The same file carries five category MOVES read at block 26,100,239 — sDAI, sUSDS and sUSDD become composed (DSR-style savings modules, atomic and uncapped in both directions, so the rate is the cleaner reading of the price), srUSDe joins them (atomic within a mint cap and a liquid buffer), and USD3 goes the other way to market (its own mint and redeem are shut, maxDeposit 0 with no idle USDC against $80.1M of assets, while a Curve pool trades it at its rate) — and ONE new row, USDD 2.0 (0x4f8e5de4…cd1a, 18 decimals), because sUSDD's composition needs a tracked bottom and SavingsUsdd.asset() answers that address rather than the 2021 USDD at 0x0c10bf8f…b5c6 the coverage rule retired. Both carry a display name ("USDD 2.0" and "USDD (legacy)") for the reason the pair exists at all: two mainnet contracts answer symbol() with "USDD", so nothing may key on the ticker.
token_pricing_measurements (migration 111)	The readings a pricing category is decided on, append-only and keyed (chain_id, token_address, kind, measured_at). kind ∈ capacity_mint / capacity_redeem (read at a block, which the row carries) and dex_trading_days_30d / dex_median_daily_volume / reported_median_daily_volume / dex_top_pool_liquidity (measured over a WINDOW, which is named in detail instead — stamping a block on a 30-day count would claim a precision it does not have, so the CHECK requires a block for the first pair and forbids one for the second). Migration 112 adds the fifth kind and widens both of those CHECKs (dropped and re-added by name, so the file is idempotent): the volume bar of the category test is judged on the price vendor's REPORTED daily volume — exchanges and DEXes together, since the bar asks whether a traded price exists and a token turning over millions a day on centralised venues has one whatever its pools are doing — while dex_median_daily_volume stays as the on-chain half of the same figure and as the reading the bar falls back to for a token the vendor does not list. The day count and the pool depth stay on-chain, because a reported figure cannot say which days had trades in them and a pool's reserves are a balance sheet. value_usd MAY be NULL, and that is the point of the last CHECK: an uncapped mint or an uncapped redemption has no number to store, and inventing a ceiling so the column could stay NOT NULL would put a made-up figure into the series every verdict is computed from — so a row states either a measured value or, in detail, that there is no bound, and never neither. A FAILED read writes NO ROW AT ALL: "the vault can pay nothing" and "we could not ask" are opposite statements, and a zero for the second would move a category on a timed-out request. The primary key IS the index the verdict reads: every read is "the newest row for this token and this kind", which the PK serves as an index-only backward scan, so a second (…, measured_at DESC) index would be maintained on every insert for no plan change — and the key also makes a re-run of a tick a no-op rather than a duplicate. Written by the six-hourly refreshers/token-pricing.ts (the capacity pair) and the weekly refresh-token-market.ts (the four market readings); read by src/lib/data/pricing-verdict.ts, which re-derives the fourteen-day flip hysteresis from the series rather than storing a verdict beside it. GRANTs SELECT, INSERT only — append-only is a grant, not a convention.
portfolio_backfill_state	The registration backfill queue (WS5): one row per account, uid PK REFERENCES accounts(uid) ON DELETE CASCADE. status ∈ queued/running/done/empty/error (default queued) drives the /portfolio building state, the live cron's eligible-wallet exclusion, and the minutely drain cron (drain-portfolio-backfills.ts) that claims queued rows FIFO. empty = no OPEN positions at backfill time (open-only; past activity alone no longer earns a replay). floor_ts is the replay range start = the account's "tracked since" anchor (a UTC midnight on the daily grid), shown in the methodology footer — SET on done, cleared on empty (whose history is pruned), and always equal to min(snapshot_ts). attempts (migration 045) counts stale-running reclaims; at 3 the row parks as error instead of reclaim-looping, and any terminal done/empty resets it to 0. progress_done/progress_total/progress_started_at (migration 060) are the live reconstruction cursor behind the /portfolio building state (its percent and its estimate): cleared at the start of every run, total stamped when the replay loop starts (which also starts progress_started_at, the ETA's rate clock — replay start, not claim time), done bumped after each daily grid point (backfill-progress.ts; best-effort writes). They deliberately never touch updated_at, so the drain's orphan-reclaim clock ("stamped at claim, never heartbeated") is unchanged, and they are only surfaced while status='running', so a stale cursor from an aborted run can never read as live progress. A partial index portfolio_backfill_status_idx on (status, updated_at) WHERE status IN ('queued','running') (migration 057) serves the minutely drain's FIFO CLAIM (status='queued' ORDER BY updated_at ASC ... FOR UPDATE SKIP LOCKED) and its stale-running RECLAIM; it stays tiny because the terminal (done/empty/error) rows are excluded from the index. Coverage anchor (migration 063): covered_through_ts / covered_through_block record how far a wallet's history is genuinely complete, and the BLOCK a process actually read to get there. Written only on a terminal done and advanced by the 6h tick for every eligible wallet (including one holding zero positions, which writes no snapshot rows at all). Two consumers: it is the trigger and the seam for the Phase 7a gap patch (its presence means a previous run genuinely completed, so a re-add patches rather than destroys), and it is the second half of the 12h requeue staleness test — GREATEST(max snapshot_ts, covered_through_ts) < now() - 12h — so an all-closed wallet the tick is faithfully carrying is not mistaken for an abandoned one. The block matters because a live snapshot keys an aligned ts but reads at the tick's real head block, so a timestamp lookup would land below the true read block and re-sweep flows already baked into the tip. Tiered-build cursor (migration 076): NO LONGER READ OR WRITTEN. deep_extend_ts recorded the depth a two-tier build still owed, back when a signup replayed a recent window first and the rest as background queue work. The rolling 30-day window made a new wallet's whole history one pass, so the tiering and its backward-extension path were deleted (#873) and nothing reads this column. It is left in place deliberately: dropping a column is destructive and belongs in its own tagged migration, and an unread column costs nothing.
portfolio_wallet_index (migration 050, T4 §3.6)	The discovery index: one row per (chain_id, wallet, venue, universe_key) — the Morpho markets (venue='morpho-blue', universe_key = market id) and ERC-4626 vaults (venue='erc4626', universe_key = vault address) a wallet has touched. uid = the owning account (= wallet in the shadow-account model). loadRegistries/the 6h cron INTERSECTS the two BIG universes (exhaustive Morpho markets, all fTokens/curator/managed vaults) with this index so the snapshot READ stays bounded to a wallet's touched markets/vaults — keeping cron wall time flat as the universe grows to every mainnet market (verified: an obscure non-curated WBTC/USDC position bounds the read from the full universe to the wallet's 5 markets and still charts). The small bounded universes (Aave/Spark reserves, Pendle, Fluid NFTs) stay full-sweep and are NOT indexed. Populated three ways: the migration SEEDED it from the portfolio_position_snapshots + portfolio_flow_events rows that existed then (position_key encodes the universe_key) AND seeded a discovery WATERMARK (chain_scan_cursors scope portfolio:discover:<wallet>, since retired: 061 moved it onto accounts and 106 deleted the rows) for every historically-active wallet; the 6h cron upserts it from the derivation's own rows (membershipsFromKeyedRows, no extra getLogs — the flow-scan byproduct it used to read went with that scanner); and the /10min enrollment drain (refresh-portfolio-discovery.ts → runEnrollmentDiscovery) enrolls a new wallet via a one-time FULL-universe CURRENT read (indexes its current holdings + stamps the certificate). A wallet is SCANNED iff it holds a fresh completeness certificate, not merely an index row — the incremental byproduct only adds the CURRENT window's markets, so a brand-new wallet with one byproduct row still has undiscovered older holdings; treating that as scanned would bound the read and silently drop them (M9). The certificate means "the index is COMPLETE for current holdings" (the current-read enrollment guarantees that). Safety (M9-class): an un-certified wallet is UN-SCANNED and the loader reads the FULL universe for the whole batch (never bounds to empty), so a not-yet-enrolled wallet is slow-but-correct; a scanned wallet that opens a brand-new position is caught by the next tick's byproduct ("found a tick late", never mischarted). The SCANNED test is the certificate on accounts (see below), and the registration backfill also writes memberships here on its terminal transition.
fluid_event_log (migration 044, FWS3 / D7) — DROPPED by migration 095	The decoded Fluid event cache. Fluid movements are derived from raw_events like every other venue's.
fluid_event_coverage (migration 044, FWS3 / D7) — DROPPED by migration 095	The seed depth of the cache above. Replaced by the ingested event store's own per-stream coverage.

All of these GRANT SELECT, INSERT, UPDATE, DELETE to onchain_credit: the two history tables need DELETE for the WS5 windowed delete+insert repair (the only append-only carve-out, also the recovery for a missed cron run, M9); backfill_state needs it for an account reset. (The two retired Fluid cache tables hold the same grants; nothing exercises them any more.) On staging the whole user-data graph joins the scrub-staging-pii.sql TRUNCATE so no real user's positions ride a reseed, and reseed-staging.sh fails closed if any of accounts, account_wallets, portfolio_position_snapshots, portfolio_flow_events_v2, portfolio_wallet_index, portfolio_backfill_state or portfolio_derive_jobs (the derivation queue, whose rows name tracked wallets) survive; scripts/ops/seed-portfolio-fixtures.ts re-registers the public test whales after each reseed. accounts and every FK referrer must go in ONE TRUNCATE — Postgres refuses to truncate an FK-referenced table unless every referrer is in the same statement — so the scrub builds the list dynamically from whichever tables exist (to_regclass), tolerating a dump that predates any of 038 / 042 / 043 / 048 / 050. Since migration 056 that referrer set grew from two (backfill_state, account_wallets) to include the three chat tables, both history tables and portfolio_wallet_index, which is why the chat tables can no longer be truncated in a block of their own.

Referential integrity (migration 056). portfolio_position_snapshots.wallet, portfolio_flow_events_v2.wallet and portfolio_wallet_index.wallet each carry a FOREIGN KEY (wallet) REFERENCES accounts(uid) ON DELETE CASCADE. Deleting an account now cascades through its entire position history and flow ledger (previously those rows were stranded). Wallet un-tracking is unaffected: it deletes an account_wallets row, never an accounts row, and 048 keeps a tracked wallet's history on purpose — these FKs reference accounts, not account_wallets, so un-tracking still prunes nothing. Every writer creates the accounts row first: the 6h cron only snapshots a.uid FROM accounts, the add-wallet flow runs trackedAccountUpsert (the shadow account) before the tracking/backfill/live-paint writes, and the WS5 backfill throws in loadAccount ("register it first") if the account is gone. The one exception is the JIT live flow persist (live.ts writeFlowRows, via POST /api/portfolio/refresh for the signed-in wallet, which rests on the best-effort verify upsert like chat), so live.ts calls ensureAccountRow(wallet) before that persist — inside the mini-scan try, so an ensure failure never voids the live positions.

Discovery certificate lifecycle

A completeness certificate (accounts.discovery_scanned_block / discovery_scanned_at, migration 061) is the statement "portfolio_wallet_index is complete for this wallet", and it is what lets the 6h cron bound the wallet's read. Three rules govern it.

1. It shares the lifecycle of what it certifies. It lives on the accounts row, the FK parent whose deletion cascades the memberships away (056). Deleting an account destroys memberships AND certificate in one step, so the state that caused the 2026-07-21 incident — a live certificate over an EMPTY index — is not representable. Before this, the certificate was a chain_scan_cursors row under the text scope portfolio:discover:<wallet>, which has no FK and therefore SURVIVED the cascade: one wallet was re-added, its memberships gone, its watermark intact, and the next tick bounded it to an empty Morpho/erc4626 universe. On staging the nightly reseed reproduced the same state for every wallet in the dump. The scope rows are retired: nothing reads or writes them, and migration 106 deletes them (the staging scrub keeps deleting the prefix until a reseed from a post-106 prod dump has run).

2. Only a process that EARNED it may stamp it. Never derived from a partial read:

Stamped by	When	At which block
runEnrollmentDiscovery (/10min drain)	after a STRICT full-universe current read	the anchor block
reconcileOneWallet (weekly sweep)	after a STRICT full-universe current read	the anchor block
backfillWallet (registration replay)	on a terminal done or empty, never error	the tail sweep's head block
refreshPortfolio (6h tick)	ADVANCE ONLY, never mint: a wallet that already held a FRESH certificate, and only when no veto fired	the flow scans' scanTo

The tick is the only entry that is not a full walk, so it is restricted to ADVANCING a certificate that was already fresh. It can never MINT one: its whole discovery input is the flow-scan byproduct over ONE 6h window, which says nothing about holdings acquired before that window. Minting from it would certify an un-scanned wallet over an empty index, bound its next read to nothing, and never repair — enrollment skips anything fresh, and a 6h re-stamp against an 18h horizon means the staleness gate can never fire. The restriction is enforced twice: the caller passes only wallets the loader reported as fresh, and the UPDATE itself carries AND discovery_scanned_at IS NOT NULL.

The tick's vetoes are enumerated, not inferred from "nothing threw": a degraded registry load (which silently SHRINKS the target universe and lets every venue report success), a failed membership upsert, and no scope having scanned at all (a certificate must move because a pass happened, never because time passed). A scope scan error still fails the whole tick, so the advancement is simply never reached. The block is monotone-up (GREATEST), so an out-of-order write can never walk a certificate backwards.

Known residual: a silently STALE morpho_market_registry (the daily universe refresher failing without erroring) shrinks the flow scan's admit-set with no degraded entry, so it is not a veto. The weekly reconcile, which reads the complete universe, is the backstop for that class.

3. Staleness is a performance state, never a correctness state. 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). Any other state DEMOTES the wallet to the slow-but-correct full-universe read, and the /10min enrollment drain re-certifies it. 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. A wrongly-certified wallet is therefore bounded to an ~18h exposure window instead of forever.

The retired scope rows. 061 shipped with a deploy window in both directions: readers fell back to the old scope-row presence test (whole-batch, on a 42703) until the columns existed, and writers dual-wrote the scope row so a code rollback still found a current watermark. Both shims are gone and migration 106 deleted the rows. portfolio-reconcile.ts was the last reader ported off them, and it carries a starvation tripwire (eligible wallets present but an empty batch = a loud line plus a non-zero exit) so that class of failure can never be silent again.

Event ledger (portfolio 100k scale)

Two tables (migration 064) that back the always-on event-ledger ingester (scripts/ingester/ingest-events.ts), the wallet-count-independent spine of the 100k-wallet plan. Written by the ingester + the one-time backfill-event-ledger.ts; the 6h cron derives every receipt and its dirty/recompose snapshots from them (Data pipeline → the ledger-driven cron), and migration 065 adds two more tables (below). Agent-grade conventions, plus one structural first: raw_events is range-partitioned. Full context: Data pipeline → event ledger ingester.

Table	What it holds
raw_events (migration 064)	Append-only, immutable on-chain logs on the tracked contracts, keyed on (chain_id, block_number, tx_hash, log_index). Columns: block_ts, block_hash (lower, 082; '' on every row written before it), address (lower), topic0-topic3 (lower; NULL for absent indexed params), data (0x hex verbatim), stream ∈ transfers/morpho/fluid-operate/fluid-nft/aave-pool/spark-pool (082 widened the CHECK to admit the seven streams the ledger rebuild adds, and 118 the block feed's native-eth, whose synthetic rows carry the native-ether sentinel as their address and a log_index at or above 890,000,000). The write is ON CONFLICT DO UPDATE on block_hash ALONE, and nothing else in the row: a log is an immutable fact, but its chain identity is maintained, so a re-scan fills in a pre-082 empty hash and replaces one the chain no longer serves at that height. An ordinary re-scan of unchanged rows updates nothing. That hash is what makes the reorg repair a DELETE predicate rather than a heuristic (see data-pipeline); a row with an empty hash is never deleted on that evidence. block_ts is exact for live-scanned rows but interpolated for the wide historical sweeps (2 boundary reads/window, the Fluid-seed pattern), so it is provenance for discovery / dirty-marking / flow classification, never a balance input — a later phase re-reads a block ts where exactness matters. PARTITIONED BY RANGE (block_number) on 250,000-block boundaries, named raw_events_p<N> where N = from_block / 250000; migration 064 pre-creates the current-range band and the ingester / backfill auto-create the rest at runtime (ensurePartitions, event-ledger.ts) — which is why the table is owned by the onchain_credit role, not postgres: creating a PARTITION OF a parent requires owning it (and the CREATE privilege on the schema, which migration 064 also grants the role), and the writers connect as the app role. It is the first table the app role owns; consistent with the existing trust model (the role already holds full DML on every portfolio table and writes none of them by convention). Parent-level indexes (address, block_number), (topic0, block_number), (topic1, block_number), (topic2, block_number) and (topic3, block_number) (the last added by migration 068 so the JIT recompose veto's Morpho topic3 owner disjunct is index-served) cascade to every partition, present and future.
event_coverage (migration 064)	The coverage CERTIFICATE: one contiguous [from_block, to_block] range per (chain_id, stream, address), plus status ∈ backfilling/live. address is '*' for the singleton / chain-wide streams (whose to_block tracks the live tip); the transfers stream keys one row per token, so a new token (a "USDS" universe expansion) gets its own catch-up backfill and is stamped live only when it completes. erc4626 is the second per-address stream — one row per vault / wallet-tracked wrapper — and, like transfers, its rows are written by the historical backfill and the catch-up, never by the live scan: the live scan starts at the tip, not at the coverage floor, and stamping from there would certify a range nobody scanned. Until that backfill runs, erc4626 has no rows at all, which is the correct reading of "not rolled out" (see the rollout marker below). fluid-dex is the third — one row per DEX pool — and it is owned the same way and for the same reason: a per-pool row lets a vault's smart side require the certificate of its own pool rather than a blanket one, and the pool set is the resolver's own enumeration, which grew from 48 to 49 inside three days. Its '*' row is written, by the backfill runner at completion, because its declared address set genuinely is complete — and without that row the scope could never become required of any wallet. escrow is the fourth keying and the only one whose key is not an address the scan reads: one row per escrow contract — the counterparty a wallet's in-transit position names as its required scope — while the scan reads the contracts that EMIT (a withdrawal manager, a queue). For Maple/Syrup those differ, so a certificate keyed on the emitter would answer a question nobody asks. Its rows are owned by the one-time backfill on exactly the same terms as transfers, erc4626 and fluid-dex: the live scan stamps none of them. That is not a style choice — a cursorless live scan starts at the tip, so a row written from it would read [tip, tip], and mergeCoverage's gap-below check would then throw on the backfill's first window from the floor and on every window after it, wedging the stream with no repair short of deleting rows by hand. Its '*' marker is written by the backfill at completion, now that the in-transit position leg exists: the marker is what enters the scope into every wallet's required set, so it deliberately waited for the leg (a rolled-out scope with no leg certifies a leg that is not there) and landed in the same change that wrote it. Two of the six escrow classes — the Ethena-style cooldowns — earn no escrow row at all, because this stream reads nothing they emit: their request leg is an ERC-4626 Withdraw that the erc4626 stream already stores and certifies per address, so that is the scope their in-transit leg requires. For a transfers token to_block is the historical catch-up completeness mark — the live scan advances the shared ledger:transfers cursor, not the per-token row, so that cursor is the transfers freshness bound, not a per-token to_block. native-eth keys one row per WALLET too (migration 118): the first block the native-ether block feed read with the wallet in its set, [from, from] live, written by the live feed step itself BEFORE its read (the one per-address row a live scan stamps, because its claim is exactly "read with this wallet from here on", which only the live read can make) and lowered by the one-time history run over the blocks it read; the stream's cursor (ledger:native-eth) is its live bound, and the reading audit reads the two together to decide whether the feed spoke for a break's range. Its '*' marker is the on-switch alone: no venue requires the scope, so it withholds no wallet and only makes the ingested tip wait for the feed's cursor. wallet-token keys one row per WALLET (not per token), because a wallet-filtered scan cannot honestly certify a token: a row keyed on USDT would claim coverage for every holder while only the scanned wallets were covered. Its rows follow the transfers shape in every other respect, including the frozen to_block — which is why that stream's declared address set is monotone (a wallet that has ever held a row stays in the live filter), and why those rows, the one per-account row family in this table, are deleted by the staging scrub. What a wallet-token row certifies is the wallet's bare-token history: the stream claims only the addresses no address-scanned stream owns, so a wallet's transfers of an aToken, a vault share, a PT or a Fluid position NFT are certified by those addresses' own rows on transfers / fluid-nft. A consumer asking for a wallet's whole transfer history reads the union of the three. The rule the writers enforce (mergeCoverage, event-ledger.ts): never stamp a range you did not scan — a gap between the stored range and an update throws — generalising the PR #484 discovery-certificate philosophy. The stamp is safe under the two concurrent writers cutover creates (ingester + one-time backfill): the durable merge is atomic in SQL (LEAST/GREATEST/sticky-live), the JS gap check is the honesty gate. A live row is a completeness claim; a backfilling row is a progress/ownership marker (may run ahead of the current chunk) and, above the floor, the backfill campaign's record of the blocks it has actually imported for that address. The two readings of a backfilling row are distinguished by its to_block and nothing else: at [floor, floor] it is an up-front OWNERSHIP marker and proves nothing — it is written before the first scan window so the catch-up does not race the same address, so it says a pass BEGAN at the floor with the address declared, and a pass that then covers 1.5% of the range leaves exactly that row behind. Above the floor it is earned progress, written from committed windows as they commit (including when the run dies partway), and it is what the completion path reads. The completion path refuses to stamp live for an address unless the union of the blocks the current pass committed and the blocks already recorded for that address covers the whole certified range without a hole; an address already live from the floor passes on its stored row, because that claim is already published and refusing it would only block the re-promotion that could correct it. Without the rule, an address the registry gains mid-sweep is refused once and then absorbed by the next run (whose own registry read now contains it), and a half-finished repair of that address would supply the same evidence a completed one does — either way publishing a completeness claim over history nothing ever imported. Indexed (chain_id, stream, status) for the ingester's new-token detection.
portfolio_parity_runs (migration 065, 066, 069) — DROPPED by migration 095	The audit ledger of the two-pipeline comparison that preceded the retirement. Nothing writes or reads it.
ledger_backfill_acceptance_bands (migration 088; provenance + append-only key by 089)	The expected size of a completed historical import, as data rather than as a source constant, so a newly measured range does not need an application release. One row per measurement: (chain_id, stream, from_block, to_block, expected_rows, evidence_source, measured_at, note), PK (chain_id, stream, measured_at) — append-only across measurements, and readers take the newest row per stream. evidence_source is the load-bearing column. It is independent-chain-scan (an eth_getLogs enumeration of the range through the stream's own filters, taken from a provider other than the one the sweep collected with — compared EXACTLY), sampled-chain-density (the contract §4.2 logs-per-5,000-blocks measurement, extrapolated — compared with the 70%–200% allowance), or stored-rows-unverified, the quarantine value 089 stamped on every row written before provenance existed. The distinction is the whole point of the table: 088's recorder counted the stored corpus, so acceptance condition 1 became observed = observed and a production sweep that had missed its forecast was accepted by a band cut from its own rows. The backfill runner now REFUSES a quarantined band by name rather than falling back to the forecast silently, because the row exists precisely where a green was once issued. Quarantined rows are kept, not deleted: they are the audit trail — which is why 089 also REVOKEs UPDATE/DELETE from the app role: 088 granted only SELECT, INSERT, but the schema's default privileges hand the role a/r/w/d on every table created in it, so the narrow grant was additive and append-only was a convention rather than a rule. It is now a rule; a correction has to be a new row. Written by scripts/record-ledger-backfill-band.ts (which refuses when the verify endpoint is the provider this stream's own scan receipts name — falling back to the environment only for a stream with no receipt yet — when the range reaches above the finalized head, when --to is not the frozen handoff of a stream whose range is fixed, and for wallet-token, whose expectation moves with the enrolled wallet set). A jointly measured group (aave-token + spark-token) carries one row per stream and the runner sums them, because the band is the pair's total and condition 1 sums their imported rows the same way. Recording a band approves nothing — the runner's four conditions still decide.
portfolio_held_pts (migration 065, Phase B)	(chain_id, pt_address) PK — the set of Pendle PTs anyone holds, so loadPendleMarkets (registry.ts) can stop seq-scanning the two biggest user tables to decide whether a matured PT stays in the universe (M13). The snapshot writer and the ledger merge upsert a PT here (ON CONFLICT DO NOTHING) whenever they write a pendle-venue / known-PT row; migration 065 SEEDED it from the SAME four "held-by-anyone" arms the old query scanned, so loadPendleMarkets's universe (active OR grace OR pt ∈ held_pts) is behavior-identical (the four-arm scan it replaced was kept as a pre-065 fallback until #909 deleted it). The rebuilt ledger's writers feed it too, from the rows each merge actually inserted, each carrying the lowest block it was seen at. That is what keeps the receipt half of this maintenance alive now the old flow writers are gone: the snapshot half alone cannot see a PT that moved through a wallet without being held at a snapshot instant. Whether to withhold an unsettled row is the WRITER'S caller's decision, not the writer's — the 6h tick and the registration replay withhold nothing (the replay's window stops at the ingester's progress, tens of blocks below the chain head), and the page load, whose scan runs to the head, admits only rows at or below the finality line it just read. It is taken on the block, which is the fact the site already holds, rather than on the row's own basis. The two now answer the same question — issue #777 moved the basis stamp onto the writer's own settle line, the same line its cursor stops at, so a row below the cursor can no longer be labelled unsettled — but the block stays the input: it needs no column read, and a site that has just computed its own line is the one that knows what its line means. (Before #777 the two disagreed, and reading basis here would have dropped roughly 48–64 blocks of final rows' PTs from the registry permanently; the rows are relabelled by the one-off repair in deployment.md.) The asymmetry settles the direction anyway: admitting a PT costs one registry row, and failing to admit one leaves a real holding valued at nothing (M13). Migration 094 adds pfe2_pendle_ptkey_idx, the rebuilt ledger's counterpart of 059's partial functional index, so the pendle-key arm of both held-by-anyone probes is an index probe rather than a scan of all sixteen partitions (the asset arm is served by 082's pfe2_asset_idx).

Cursors reuse chain_scan_cursors with scopes ledger:<stream> (the ingester's live head, and what the hourly ingester alarm measures against the chain head to page on a feed that has fallen behind), ledger:<stream>:backfill (the one-time sweep's resume point), (Phase B) portfolio:ledger:dirty-tip (the on-mode dirty-scan tip) and portfolio:v2:tick (the rebuilt ledger's own 6h window — below). All four ledger tables GRANT SELECT, INSERT, UPDATE, DELETE to onchain_credit (DELETE for an operational re-seed). They are user-neutral market / audit data, not per-account state, so they do not join the staging PII scrub.

The flow ledger + chain identity (migrations 082 / 083)

Migration 082 created the relations the ledger-truth rebuild derives into, and 083 seeds the two registries it resolves against. portfolio_flow_events_v2 is the only flow ledger there is, and every portfolio surface reads it; the old relation beside it was dropped by migration 095.

Why a new relation rather than an ALTER TABLE. The row discriminator changed meaning: portfolio_flow_events.leg was '' on six of seven venues, while position_key is the leg identity. Both conventions inside one relation would have meant a re-scan of the same log by the previous release writing a SECOND row instead of overwriting it, silently.

Table	Owner	What it holds
portfolio_flow_events_v2 (082)	app + refreshers (user-scoped state: the 6h tick and the page-load persist both write it)	The flow ledger: the only one there is, written by the crons and read by every portfolio surface. PK (chain_id, wallet, tx_hash, log_index, position_key, seq), partitioned BY HASH (wallet) MODULUS 16 with the complete partition set created once by the migration (a hash modulus is fixed at CREATE time, so there is no runtime auto-create path and the parent stays owned by postgres). Carries an FK to accounts(uid) ON DELETE CASCADE, so a user wipe removes their ledger. Two quantities per row: amount_raw (what crossed the boundary, always >= 0) and qty_delta (the signed position-quantity delta); fees are never stored, they are the difference between the two. amount_raw is in the smallest units of THIS ROW'S asset, which is what makes (asset, amount_raw) the pair to state a quantity from — divide by that token's own decimals. amount_underlying beside it is the same movement expressed in the LEG'S ACCOUNTING ASSET, also in smallest units and not a human-unit figure; on a Pendle leg it is a different token again (the row names the principal token and the column carries the consideration in the underlying, or null), so descaling it by the row's decimals states a number in one token wearing another's ticker. Migration 097 writes that distinction onto the columns themselves, after the v1 ledger's identically-worded comment (043, where the column genuinely was descaled) led the Activity feed to print smallest units as quantities. to_balance + terminal record the post-event leg quantity, tied together by a CHECK so a closed leg and a non-zero balance cannot disagree. link_id is a durable GROUP id with a per-kind prefix (liq: liquidation, xw: cross-wallet, rh: router hop, esc: escrow claim ticket, rb: re-book, rot: rotation episode), enforced by a prefix CHECK so a transaction-scoped id cannot be written by accident; a group may legitimately be one-sided. Six indexes, including (chain_id, wallet, block_number DESC, log_index DESC) because the read path buckets receipts by block, never by wall-clock.
chain_blocks (082)	ingester cron	(chain_id, block_number) -> block_hash / parent_hash / block_ts / finalized_at. eth_getLogs already returns blockHash on every log, so this costs no extra RPC and turns "assumed complete" into "verified complete". A partial index covers the unfinalized tail, which is the only set the reorg sweep re-reads; a block at or below the chain's finalized head is stamped finalized_at and never re-read. The repair writes the canonical header LAST, inside the transaction that also withdraws the receipts, rewinds the cursors and deletes the orphaned logs — the header is what makes a repaired height look repaired to the next sweep, so a process killed before it repeats the repair and one killed after it would have owed a rewind for ever (issue #858). Written by the live per-stream scans: one row per log-producing block, plus the scan tip on every cycle — the tip anchor is what makes this a covering detection surface, since a reorg at or below it necessarily replaces it too, whereas the log-producing blocks alone leave a reorg at a quiet height invisible. Headers come from the endpoint that served the logs beside them (ETHEREUM_RPC_URL), which is also the endpoint the sweep verifies against; the per-address transfers catch-up interpolates timestamps off the archive endpoint and so stores no headers at all.
chain_scan_ranges (082)	ingester cron	The receipt that lets a cursor advance: per (chain_id, scope, from_block) a stored log_count, block_count and log_digest over the scanned range, plus the head and finalized heights at scan time. "The cursor moved" is not a completeness check; this is. It supersedes event_coverage.to_block, which is stale on 741 of 746 rows and must not gain a new consumer. provider records the serving host (never the URL, which carries the API key). verified_by / verified_at are stamped only when a SECOND archive-capable provider (LEDGER_VERIFY_RPC_URL) re-scanned the range and produced the same digest; unset, receipts are unverified and the digest degrades to single-provider self-consistency. Receipts accompany cursor advances, so the live per-stream scans write them and the per-address catch-up (certified by its event_coverage row) does not. The one-time historical backfill writes them too, under its own ledger:<stream>:backfill scope and in the same order (events, then receipt, then cursor) — which is what makes the programme's contiguity check answerable over history: the union of the two scopes must have no gap from the coverage floor to wherever the stream has reached, and the backfill runner's --verify mode refuses a rollout marker and fails the run when it does.

Growth, and the retention nobody prunes yet. Both chain-identity tables are append-mostly and nothing prunes either one today. chain_scan_ranges takes one row per stream per cycle — 6 streams x ~1,440 cycles/day ≈ 3.1M rows/year — and chain_blocks is the same order (one row per log-producing block, plus one tip anchor per cycle). The statement that degrades first is the reorg repair's receipt lookup (from_block <= n AND to_block >= n), which widens with the table.

That is stated rather than fixed on purpose: a receipt is the completeness evidence, and the programme's contiguity check reads it ("chain_scan_ranges rows are contiguous with no gaps"), so how long it is kept is a decision about how far back completeness can be proved, not a cleanup. The intended boundary when it is taken: receipts whose to_block is below the chain's finalized head and whose scanned_at is older than the retention window, and chain_blocks rows with a non-null finalized_at older than the same window — a finalized block cannot be re-orged, so its header is only ever historical evidence. Neither table has a reader outside the ingester, so pruning is safe at any depth the evidence policy allows. | morpho_market_impairment (082) | ingester cron (market data), written by scripts/ingester/market-impairment.ts | One row per market-level bad-debt event. Deliberately not a flow: the supplier's loss already reaches the curve through the reader's index, so a supplier-side flow row would double-count it. The record exists so the engine can move that slice from "interest earned" to "credit loss", show a dated event, and withhold the realised rate over the window that contains it. total_supply_shares is the value at the liquidation's log index, not at the end of its block: a chain read answers as of end-of-block, and any later supply or withdrawal on that market in the same block would silently resize every supplier's slice. The writer rebuilds it from the block's own supply/withdrawal events and proves the result against the end-of-block read; a disagreement means shares moved through something the stream cannot see (a non-zero market fee minting shares inside interest accrual is the first suspect) and the event is refused with the discrepancy named rather than recorded. Refusals hold the writer's own cursor (ledger:morpho-impairment) just below the refused block, so progress is kept and the gap keeps announcing itself. The one relation on this list that gained a DELETE after its own migration (087), and for the same reason chain_blocks and chain_scan_ranges carry one: the reorg repair. A liquidation the chain later orphans is deleted from raw_events by predicate, but the row derived from it is keyed on the orphaned transaction hash, so re-deriving the repaired range would write the canonical liquidation beside it — one economic event recorded twice, withheld twice and attributed twice. So the range this writer re-derives is reconciled against raw_events rather than merely upserted into, and the reorg sweep rewinds this cursor with the stream cursors. The delete is range-bounded, keyed on the logs the chain still carries, and issued only inside the morpho stream's coverage certificate — outside one, "raw_events does not carry this log" means "nobody looked", not "it did not happen". | | reward_distributors (082, seeded by 083) | registry: migration-seeded, extended by an --approve script | Claim-distributor addresses with an optional claim_topic0. NULL means eventless: the largest distributor paying these wallets emits no log at all, and the sender address is the only signal, so a design keyed on "look for a claim event" would miss most reward value. An unrecognised reward lands as a deposit, which fabricates capital, so this table is what stops eleven historical receipts becoming eleven phantom deposits the day a reward token joins the registry. | | token_lineage (082, seeded by 083) | registry: migration-only (each row is a curatorial decision) | Address pairs that denote the same claim (DAI/USDS, stkGHO/GHO, sGHO/GHO), so a wrapper rotation is recognised as a rotation instead of six phantom capital events, and the leg's vintage and basis carry across it. ratio_num/ratio_den are a real conversion only where one exists (DAI/USDS is 1:1); for a savings wrapper they assert lineage and the unit conversion is the wrapper's own rate at the receipt. Re-book rows are the degenerate case: from_address = to_address, one effective_block, recording that an address changed book mid-history. 077 retired the BTC book by setting book = NULL while leaving status = 'active', so the migration file was the only record of the date; 083 reconstructs it once and records the reconstruction in its own header. A re-book row dates the transition; it is not an instruction to convert stored values. For the one re-book seeded so far, prod's affected history was re-derived on 2026-08-14 and reads book = 'EXCLUDED' with USD values (zero rows remain at book = 'BTC'), so a read path must convert nothing there. Convert only rows still stored in the old unit. |

Changes to existing tables in 082. raw_events gains block_hash (DEFAULT '', so the previous release's writer keeps working) and its stream CHECK is widened to admit the seven new streams; the widening is split (ADD ... NOT VALID here, the VALIDATE in 084, which is a later release's own file) so a 7.8M-row / 23-partition validation scan does not hold ACCESS EXCLUSIVE against the ingester. Until 084 runs, the CHECK is enforced on every new row but reads convalidated = false, which is the correct state at that release, not a defect. event_coverage's stream CHECK is widened inline (746 rows). portfolio_position_snapshots gains block_ts and its venue / basis CHECKs widen to admit the in-transit escrow venue and the provisional basis. Those two CHECKs exist under two generations of names: the servers carry 067's _chk2 suffix, while a from-scratch fixture (which skips 067) carries 043/049's original _chk names, so 082 widens whichever names the database actually has and keeps each under its own name. Widening one generation only is a silent no-op in the other environment.

Rule: a constraint widening ships one or more releases BEFORE the first code that writes the new value. All four widenings above land in a release whose diff contains no application code at all, and the writers of the new stream names, the escrow venue and the provisional snapshot basis arrive two releases later. There is therefore no window in which new code meets an old schema, in either direction.

118: the native-eth stream name (issue #966). The fourteenth ledger stream, and the only one that is not an eth_getLogs scan: the ingester reads each block's transactions and receipts and writes, under this name, synthetic raw_events rows for native ether (a transaction's value, a fee, a beacon withdrawal, a fee recipient's priority fees, and the internal payments the reading audit lands), all on the native-ether sentinel address (0xeeee…) with log_index at or above 890,000,000, above every real log (the feed). The file adds 'native-eth' to raw_events_stream_chk (DROP + ADD … NOT VALID, catalogue only, the 082 pattern; its VALIDATE is a later release's own file and cannot fail, the new set being a strict superset) and to event_coverage_stream_chk (inline and validated). Expand only (R13): every row the old CHECKs accepted is accepted unchanged, the previous release writes no such row, and after a code revert nothing reads one (every raw_events read names its streams). It departs from the rule above deliberately: it ships in the same release as its writer, and is applied BY HAND BEFORE that release's deploy (the release entry), so the one ordering that matters (schema before writer) still holds; a deploy without it leaves the feed failing soft every cycle with its cursor held, never a wrong row. No new table and no new column: the flow ledger's rows for native ether use the kinds, counterparty classes and link kinds its CHECKs already admit (transfer_in / transfer_out, cost, and a WETH9 wrap's internal_out / internal_in pair under link kind swap).

A Pendle receipt's stamped facts, and the pre-window fills (migration 113)

Three meta keys on every PT receipt. The flow valuation (derive/marks.ts) reads, at a PT receipt's own block, the market's redemption-index factor, the PT's own rate by the one PT rate method, and the receipt valued at that rate in the leg's book, and derive() writes the stated ones into the row's meta beside the Pendle deriver's own keys: ptFactor, ptRate, ptMarkValue (JSON numbers, camelCase like route and syInterm). A read that failed leaves its key ABSENT, never null and never a guess, and a row derived before the stamps existed simply has none. No money column moves. The loader projects them (and route, and the row's amount_underlying) under jsonb_typeof guards, so a value of any other shape reads as "not stated" rather than raising; the read-time lot book prefers the stamped factor to the stored series, strikes each stamped acquisition at the payout coin's own bar (ptMarkValue over q × ptRate), and gives each lot the market's own yield at its purchase, which is what splits the expanded PT row's mark to market (M34).

Table	Owner	What it holds
portfolio_pt_prewindow_fills (113)	app (user-scoped state, written at the end of each wallet build by refreshPtPrewindowFills)	A held PT's REAL router fills from before the wallet's history window, for the lot book alone. PK (chain_id, wallet, position_key, tx_hash, log_index); FK wallet -> accounts(uid) ON DELETE CASCADE, so every wipe and partial delete takes the rows with the wallet (the staging scrub truncates it in the same statement as accounts, the reseed's leak check counts it, and the reset script verifies it empty). One row per fill: qty_raw (signed PT smallest units), value_market (valued as a ledger receipt is), payout_raw (the payout-asset coins, only where the consideration IS the payout asset), the three stamps, and opening_block / opening_qty_raw, the reading the set explains. Migration 116 adds the fill's own FACTS (decision f1): consideration_asset / consideration_raw (what it paid or received; both or neither) and rate_facts (the rates its valuation read at its block, in a movement's meta.rateFacts shape), from which the read computes the fill like an in-window receipt; a row stored before 116 has no consideration and is served its stored value_market and pt_mark_value. Written only when the fills explain that reading EXACTLY (net quantity, never a negative running balance) and every acquisition priced and read its factor; otherwise nothing new is stored. A leg's stored set is kept without re-valuation while it still explains the same opening, replaced only by a complete recompute, and removed only when the opening moved or the leg is no longer a pre-window holding, so a transient read failure never erases it. These are NOT receipts: the engine, the chart and the reconciliation never read them. Read by the loader only for a wallet holding a bare PT, and a box without the table reads as none.

The event_coverage rollout marker

A stream is rolled out when its (chain_id, stream, '*') row in event_coverage has status = 'live' and from_block <= 22527558. The marker reads status and from_block only; to_block stays unread, so this is not a new consumer of the stale column flagged in the chain_scan_ranges row above. The '*' address is already legal and already means chain-wide / singleton.

The row carries two separable claims, and they have different readers. Conflating them is what makes the convention read as self-contradictory on a stream whose address set never completes, so they are stated apart:

claim	what it says	who reads it
(a) the on-switch	this stream is rolled out — its scope may enter a wallet's required set	the requirement map (readRolledOutStreams)
(b) set completeness	the declared address set for this stream is complete	the tip loop's history gate (streamAddressSetCertified, via mayDrainHistory)

Claim (a) says nothing about addresses, so every stream's marker carries it. Claim (b) is a per-address stream's only, and it is asked exclusively of a stream whose registry entry says its address set is declared complete; the caller gates on that field, so the question is never put to a stream with an open set. The backfill runner therefore stamps the marker only where both claims hold — a declared-complete set, after the last window of it commits — and a human can write it during a backfill window with no deploy: one INSERT where the stream has no '*' row yet, one UPDATE where the row exists and is not yet live.

An open address set may carry the on-switch, and one of them must. wallet-token keys one row per wallet and its declared set is the tracked-wallet list, which grows on every signup — so claim (b) is permanently false for it and is not a claim its marker makes. Claim (a) still applies, and without it that stream's scope could never enter any wallet's required set: a wallet holding only bare tokens would require nothing, and a requirement set that is empty certifies vacuously, which is the un-require direction this whole convention exists to close. The marker is honest there because on a per-address stream the marker is never what certifies a wallet — the wallet's own per-address row is, and the per-wallet conjunction reads it directly. A wallet with no row is withheld with or without the marker. Nothing stamps wallet-token's marker automatically, because an open set has no completion event to hang it on: an operator writes it, once the enrolled wallets' catch-ups are live, and a wallet that registers afterwards is withheld until its own catch-up lands.

Scope of the rule, and it is part of the rule rather than a footnote. The marker governs only the streams this rebuild introduces (aave-token, spark-token, fluid-dex, erc4626, pendle-router, wallet-token, escrow, and since issue #966 the block feed's native-eth, whose marker an operator writes once the feed's cursor exists, the release entry's step 4, and which no venue requires). The six streams that shipped before the convention (transfers, morpho, fluid-operate, fluid-nft, aave-pool, spark-pool) predate it and are rolled out by definition: they stay required unconditionally and are never dropped from a wallet's required scopes for want of a marker row. The scoping is load-bearing rather than tidy. Measured read-only on prod on 2026-08-18, the five chain-wide shipped streams each carry their '*' row at 22527558 / live, but transfers carries 741 live per-address rows and zero '*' rows, so a universal reading would classify transfers as not rolled out and silently un-require the scope that carries the certificate for every Aave/Spark, ERC-4626 and Pendle leg. No '*' row is written for transfers, and after the split above the reason is the plain one: it is required by definition, so no reader consults a marker for it, and the only claim it could add is (b), which is false for it. The direction the whole convention must fail in: the certificate may over-require, never under-require.

The point of storing it as a row is that enabling a stream is a data change, not a deploy — seven deploys inside a backfill window is not an option. Rollout is evaluated globally per stream; per-wallet certification is then the conjunction over that wallet's own addresses, so a wallet whose per-address backfill has not finished finds the scope required and its missing certificate resolves to failed, which withholds rather than publishes. Disabling a stream stays a reviewed code change; the asymmetry is deliberate.

Widening a stream's EVENT SET invalidates its certificate

A coverage row certifies a (stream, address) range, and stream is a name, not a contract: the row says nothing about which events were being requested when the range was scanned. So adding a topic0 to a stream's filter silently redefines what its existing rows claim. Every row below the change was earned by a narrower filter and now reads as a completeness claim for events that were never requested down there — the same failure as a lowered floor, arriving through a door nothing was watching. A topic-set widening therefore resets the certificate: the stream's rows must be deleted so the one-time backfill re-earns them over the new event set.

Deleting is the only operation that works, and the reason is in the writer. stampCoverage merges from_block with LEAST, to_block with GREATEST, and keeps status sticky at live; the live scan re-stamps every singleton stream's row each cycle with status = 'live'. So an UPDATE … SET status = 'backfilling' is undone within one cycle and a raised from_block is pulled straight back down. DELETE leaves no row, the next live scan inserts one at its own scan start (the tip), and the range grows back down only as far as something has actually scanned.

Timing is part of the rule. The reset is owed to the first consumer that reads the NEW events, not to the deploy that adds them: running it earlier withdraws a certificate that is still true for everything currently read, which forces those readers onto their chain fallback for no gain. Run it as the first step of the backfill that re-earns it, and before any verification step that would otherwise read the stale row as proof the backfill had already happened. Two concrete cases are written up in the deployment runbook: the two Pool streams and the two Fluid streams.

The Fluid case is the one that shows why the rule is not bookkeeping. fluid-operate gained the event a vault emits when it writes a position off in full — the only trace such a write-off leaves, because the vault returns before emitting its ordinary liquidation event. Every block scanned before that topic joined the filter holds none of those events, and the stream's coverage row claims otherwise. Left unreset, a wallet whose position was written off in that range is certified complete over a range in which the event that explains its disappearance was never requested, and the loss reads as yield.

A stream nobody has scanned yet owes nothing, and that is a fact to CHECK rather than assume. The rule protects rows earned under a narrower filter. A stream whose one-time backfill has not run has no such rows: no per-address certificate, no '*' marker, and no stored logs. Widening its event set redefines nothing, so no reset is owed and none should be scheduled — the whole obligation is discharged by the backfill that is still to come. That exemption is claimed exactly once so far, when erc4626 gained the Morpho Vaults V2 forced-deallocation event, and it was claimed only after confirming on both prod and staging that the stream held zero coverage rows and zero stored logs. Widening it again after its backfill has run owes the reset in full. The order matters commercially as well as logically: on a 546-address stream the difference between widening before and after is re-scanning 3.2M blocks of archive history.

Its second reader is the ingester, and that one is about cost rather than correctness. The always-on loop's per-address catch-up backfills any address that has no live row yet. For transfers that is unconditional and is the shipped "USDS" runbook. For a stream whose address set is declared complete, running it before the one-time backfill has finished would set a 60-second loop racing the backfill runner over the same 3.2M blocks of archive reads — duplicated work with a duplicated bill, since both writes are idempotent — so the loop stays out of that stream's history until the runner has finished, and afterwards a newly listed reserve is an ordinary new-address catch-up. The loop accepts either the marker or anylive per-address row on that stream as evidence the runner finished (the runner stamps the whole declared set backfilling up front and live only at completion), so the gate is not a hard dependency on a future runner remembering to stamp the marker.

The rebuilt ledger's 6h tick cursor

portfolio:v2:tick in chain_scan_cursors — one row, chain-wide, never per wallet: how far up the chain the 6h tick has derived portfolio_flow_events_v2 for its whole tracked population. The tick's floor is that block plus one; the cursor advances, after the range is derived, to max(range start − 1, min(range top, the chain's finalised head)) — the same expression every old flow scan's cursor used, so the reorg-exposed tail the tick just wrote stays above it and is re-derived next tick. It is monotone-up (GREATEST), so a hand run beside the cron can only ever leave the higher of the two, and it is written by the 6h tick alone: the page-load writer reads it as its own floor and never advances it. (Once the ledger worker owns derivation — a constant that is off in this release, see the derivation queue — the worker writes it instead, and only when every job of one tick's sweep is done.) The window, the bootstrap chain behind it and the log line an operator greps are in data-pipeline.

Its prefix is a third family and the choice is load-bearing. portfolio:derive:v2:% is a wipe-on-reset family holding per-wallet state only, so a chain-wide value parked there is destroyed by one nightly reseed and the destruction reads as success. portfolio:v2-campaign:% is the retirement campaign's own family, and its arm block stops meaning anything once the campaign is over. This cursor is neither: it is permanent pipeline state, like ledger:<stream> and portfolio:ledger:dirty-tip beside it, so it takes a prefix of its own that no scrub, reseed check or reset statement touches.

Staging scrub. Unlike the event-ledger tables above, portfolio_flow_events_v2 is per-account state, so it joins the staging PII scrub and its leak check, and the shadow build's per-wallet cursors (portfolio:derive:v2:<chain_id>:<wallet>, matched as portfolio:derive:v2:%, one row per wallet, with the per-wallet markers filed beside them: …:jit-pending, …:replay-deferred, the reading audit's watermark …:audited-through and the continuous producer's follow record …:followed-after) are deleted by the same scrub. (The retired discovery watermarks, portfolio:discover:%, are the second prefix for one more release: nothing reads them and migration 106 deletes them, but the scrub, the leak check and the user wipe keep the prefix until a reseed from a post-106 prod dump has run.) The ledger prefix is a wipe-on-reset family: it holds per-wallet state only, and a campaign-global value must never be parked under it, or a nightly reseed or a user wipe destroys it and the destruction reads as success. Campaign globals live under portfolio:v2-campaign:, which no scrub or reset statement touches. The same statement lives in three files (the scrub, the reseed's leak check and scripts/ops/reset-portfolio-users.ts) and a test extracts the prefix set from all three and from both prose copies; the reseed's full blast radius is in data-pipeline.

One coverage row family IS per-account state, and it now has its own scrub statement.event_coverage is public-chain progress for every stream but one: a row per contract address, saying how far the ledger has been scanned for it. The wallet-token stream keys its coverage on the wallet (one row per enrolled wallet), so a prod dump would carry the enrolled-wallet set into staging inside that table — the same argument that put portfolio_wallet_index in the scrub. Truncating the table would force staging to re-backfill its whole event ledger nightly, so the shape is a stream = 'wallet-token'-scoped DELETE, with a matching leak check in the reseed.

It runs with the account TRUNCATE, not instead of it, and the reason is the stream's declared address set: the wallets the product serves, unioned with the wallets that already hold a coverage row. The second arm is what stops an un-tracked wallet quietly dropping out of the live filter and acquiring a hole in a certificate that still reads live — so deleting only the account graph would leave every dumped wallet enrolled forever through its surviving coverage row. Both arms empty in the same run.

The observed rate facts (migration 116)

A writer that values a row reads some RATES at the row's block: a wrapper's share rate, a fund's NAV per share, a share-counted leg's per-share rate, the rate a composed market line multiplies its underlying's bar by. Since the page computes the money columns at read (values computed at read) and cannot read the chain, each such rate is stored as a fact of the row it priced (portfolio ledger-first plan, decision b2):

Where	Columns	What they hold
portfolio_position_snapshots	rate_raw numeric, rate_source text	the leg's own accounting asset's rate at block_number as read (per share on a share-counted leg; a PT leg's own rate, for reference only). CHECK portfolio_pos_rate_fact_chk: both or neither, the source one of the three below, the rate positive. Added on the RANGE-partitioned parent, so every monthly partition carries it.
portfolio_flow_events_v2	meta.rateFacts (no column)	every rate the movement's valuation read at its block, keyed by asset (a per-share rate under <asset>#shares), each {"rate": n, "source": s}, beside the PT stamps ptRate / ptFactor / ptMarkValue. Absent where the movement read no rate.
portfolio_pt_prewindow_fills	consideration_asset, consideration_raw, rate_facts jsonb	the fill's consideration and its rate facts (a movement's shape); CHECK pptpf_consideration_chk.

rate_source says where the writer took the rate, and it decides how the read uses it:

• chain: the registry row's getter answered at the block. The stored rate IS the rate.
• series: the getter could not answer and the writer fell back to the six-hourly token_yield_apy series at or before the block (the replay's history-mode fallback, and every movement's). The read takes the series again at that block, so a late or corrected observation re-values the row; the stored rate answers only where the series no longer does.
• live: the 6h tick's or the live tip's now-mode fallback, the newest observation at the time of the write. No later read of the series can tell which observation that was, so the stored rate is the fact. On a 6h tick's reading it is the redemption line's rate only: the tick composed the market line in history mode, so the read prices a checkpoint's market line at the series at or before its block (one row, two rates).

A listed fund never takes the series (#810 R3), so its fact is always chain or absent. Movements are valued in history mode (P9), so their facts are never live. A row written before 116 has no fact and is served its stored columns until the rate-facts backfill fills it, which it does only with a fact that reproduces the row's stored value; the reconciler's values= line counts such rows (factless=), and two classes the writers keep producing can never be filled (the loader rule).

The reading audit's two kinds: opening and adjustment (migration 114)

portfolio_flow_events_v2 holds two records that are not movements anybody made, written by the reading audit (portfolio ledger-first plan, rulings R3, R4, R5):

Kind	Written by	What it states
opening	the registration replay, at the wallet's floor reading, for every leg that reading holds; the audit, at the reading that first finds a leg with no movement history (a coverage start); the opening backfill, at a wallet's floor reading, for one enrolled before these writers. Whatever the holding is worth: one under a cent is written too and only kept off the page (R7; PR #963)	the balance the leg holds at the first block the ledger speaks for it. qty_delta = to_balance = the read quantity (CHECK pfe2_opening_qty_chk). Never an activity line, never a flow in a user-facing total
adjustment	the audit, when a successful reading disagrees with the ledger and the automatic explanation found nothing that accounts for the residue	the correction: qty_delta = read − ledger (signed), to_balance = the read quantity

Three nullable columns, no default, carry what an audit row says about itself:

Column	On	Meaning
explain_status	adjustment only (CHECK pfe2_explain_status_chk: NOT NULL there, NULL on every other kind)	unexplained (pages) or accepted (its cause is in the accepted-cause registry, src/lib/portfolio/audit/accepted-causes.ts; never pages)
cause	adjustment	the accepted cause's registry id, or what the explanation found for an unexplained one
reading_block	opening and adjustment (CHECK pfe2_reading_block_chk: NOT NULL on both)	the block of the reading that wrote the row, equal to its own block_number

Keyed like every ledger row, and deterministic. An audit row's tx_hash is a sha256 of (kind, chain, wallet, venue, leg, reading block) and its log_index is the largest a log index can be (2147483647), so in ledger order it sits after every movement of its block (a reading at block B is the state at the END of B) and a re-run of the same audit writes the same key. Its values are the reading's own marks, pro rata to its quantity, and meta.audit records the ledger quantity it was compared against and the explanation's search range (and rehomedFrom, the block of the superseded reading a correction was moved from with its residue unchanged, once the alarm had paged it there). meta.audit.reading carries the reading row the audit row was written from (the columns the read path values a reading from, as that row stored them), so the row is still valued from its reading once the reading itself is gone. An exit a direct read booked (PR #961 final review, SF-3: a leg the ledger held that the reading omitted, read on its own at the reading's block by a strict read of its count, round 2 B1, which returned zero) carries meta.audit.directRead = 'zero', to_balance 0, and in meta.audit.reading the newest reading that still read the leg, with that reading's own block_number: its reading holds no row for the leg, so it is valued and sized from that earlier one. The reading's own audit restates it (it is never a row of a leg the reading failed to read), and a re-audit re-plans it from the row rather than reading the chain again. The derivation's merge never deletes one, and the wallet-token universe's superset guards never count one: an audit row is not a decoder's output, so a re-derivation over its block leaves it to the audit that wrote it, which restates it (inserted, rewritten or removed) on its next run over that reading. A reading at the same block that no longer reads the row's leg (a live tip the 6h tick retired at its own anchor block and replaced there with a checkpoint, a grid point a replay re-laid, with that leg's read failed) does not restate it: the chain at a block does not change, so the row is kept and compared again from the reading it carries (below). Where that reading has since been deleted (a superseded live tip), the row is taken over on the run that reaches the reading that superseded it, which removes it and books what it stated there when that reading read the row's leg with no movement of it between the two (a superseding reading no audit compared, a recomposed checkpoint, is compared for that leg alone). Otherwise the row stays at its block, valued from the reading it carries and compared again there from the quantity that reading read: restated, or removed once the ledger holds what it stated. A row with no reading standing above it at all (a tip an empty reading retired) is compared again the same way by the next audit that re-audits a reading below it, up to the watermark. The hourly alarm sizes a correction from the copy it carries too, wherever its reading no longer stands with its leg. The wallet's audited-through watermark, the block of the newest reading an audit compared, is one chain_scan_cursors row (portfolio:derive:v2:<chain_id>:<wallet>:audited-through, the per-wallet wipe family): where a break's search starts and the orphans a job inherits start (data-pipeline).

pfe2_kind_chk gains the two kinds after the twelve, unchanged in their order. Every existing row passes all four constraints (its kind is one of the twelve and the three columns are NULL). The previous release's loader counts an unknown kind and skips it, but the rest of that code does not tolerate the rows: its events list fails on one, its 6h tick's wallet-token universe guard counts them and refuses a range that holds one on a leg nothing moved in it, and its reconciler pages on one. So a revert of the release deletes both kinds as soon as its deploy is green (the rollback, step 3; PR #960 review B1).

The derivation queue: portfolio_derive_jobs (migration 115)

One durable queue in front of the ledger's writer (portfolio ledger-first plan, ruling R9). A row is one unit of derivation work: derive portfolio_flow_events_v2 for one wallet over one block range and merge it, through the same writer every other site calls. It holds no derived number — the rows it produces live in the ledger. One always-on process, the ledger worker (creddit-ledger-worker, data-pipeline), claims and runs them.

Column	Meaning
id	bigserial, the primary key: the row's identity is the job, not a chain fact, so two jobs over one wallet and range are two requests (a Synchronize during a sweep)
chain_id	carried on every row and named by every statement (the table is on the chain-scoping list above)
wallet	lower-cased 0x address, shape-checked
from_block, to_block	the inclusive range; from_block > 0 and to_block >= from_block
kind	who enqueued it: sync (a page load's refresh), enrol (a registration's whole window), continuous (the ingester's cycle end), rederive (a whole-window re-derivation), sweep (the 6h tick's population), audit (migration 117: check the stored readings in its range against the ledger, oldest first — one reading for the 6h tick and the live tip, whose job has from_block = to_block = its block, the whole grid for the registration replay; the reading audit)
priority	the kind's, lower first: sync 0, enrol 10, continuous 20, audit 25, rederive 30, sweep 40 — and only the kind's: pdj_priority_chk refuses any other number, so a hand-typed row cannot reorder the queue
state	queued → running → done | partial | failed; a retried job goes back to queued
attempts	claims that counted; the third failed attempt parks the job as failed. A claim whose writer only queued for the portfolio write lock past its wait budget is handed back (the count goes down by one on its retry), so contention never parks a job; so is one whose merge the stale-merge fence stopped, because another writer changed the wallet's ledger while the job derived (data-pipeline); and so is an audit that waits (for its wallet's derivation, for the audit of an earlier reading of the wallet since a wallet's audits apply in block order, behind its own write's fence, for the write lock on its own write, or on an in-place re-derivation that met the lock or the fence) until six hours after it was queued, when it ends partial instead (data-pipeline)
settle_line	the finality line the job's rows are judged against (#777) — present on exactly the three tick-range kinds and absent on enrol / rederive / audit (an audit derives nothing), enforced by pdj_settle_chk; -1 means finality was unreadable (settle nothing)
claimed_by, claimed_at	the worker (<host>:<pid>) and time of the last claim; claimed_at is also the retry backoff's clock, restamped when a failed attempt goes back to the queue so the backoff runs from the failure. A verdict is written only where claimed_by and attempts still match the claim that produced it, so a requeued-and-reclaimed job never takes a stale verdict
finished_at, error	when a job reached done / partial / failed, and why it is not done (or why it is being retried)
created_at	when it was enqueued; one sweep's jobs are inserted in ONE statement, so they share it exactly, and that shared instant is how the worker recognises the jobs of one sweep

The states, precisely. done: the merge committed and covered the job's whole range (a whole-window job also stamped the wallet's derive cursor). partial: it committed short of that, and what it still owes is recorded where the writer's call site records it today — a tick-range job's pending marker (…:jit-pending, which the next sweep widens down to; from the merged top + 1 where the ingested tip clamped the merge below the job's top, PR #959 review round 2, B1), a whole-window job's withheld stamp (which the re-derivation trigger comes back for) — or it derived nothing at all: a sync or enrol row refused while WORKER_OWNS_DERIVATION is false (nothing produces either kind yet, so it was typed by hand), a whole-window job naming a block other than its wallet's history floor, or a rederive held for its wallet's registration replay or for the arm block, or whose to_block is below that block (the reason says which). A partial tick-range row is still read until the wallet's own derived-through reaches its to_block: the "Updated" stamp holds below its from_block until then, whatever becomes of the pending marker (S4b, PR #959 review round 3, B2; data-pipeline). failed: three attempts that could not commit, or could not record what they owed; it stays until a person requeues or deletes it, and it pages (the ingester alarm's derive-lag arm, deployment). A wallet's failed continuous jobs fold into one standing row: a later failure is folded into it (its from_block / to_block widened, the later row deleted); only a job the worker parks at its own start (found running, its attempts spent) is parked without the fold. The worker closes such a row — state to partial, error saying what closed it and keeping what it failed on — once the ledger has caught up over its range without it: when the tick cursor (portfolio:v2:tick) passes its to_block, or when a later continuous job for the wallet finishes done through it (every continuous job derives from the wallet's tick floor, so a done one leaves the wallet's ledger complete up to its top). The ingester keeps enqueueing such a wallet, and it keeps one open continuous job per wallet: a wallet whose continuous job is still queued has that job widened to the new range (from_block down, to_block and settle_line up) rather than getting a second one; a running job is left alone and the wallet gets a new job.

from_block is the producer's, not where a continuous job starts. A continuous job (and a sweep an operator inserts while the inline tick owns the window) derives from min(from_block, the wallet's tick floor, its pending marker), read when it runs — the ledger below its range is what it anchors every running balance on, and above the tick floor that ledger is not the tick's (data-pipeline). The worker's log line names the derived range when it differs from the row's.

On a whole-window job, from_block must be the wallet's history floor. An enrol or rederive job derives the wallet's whole window and certifies it (it stamps the derive cursor), so it opens at the wallet's own history floor (accounts.history_floor_block, read when the job runs through the one reader every whole-history writer uses; a missing pair reads as the indexed floor) and the stamp is asked about that block. A row naming any other block derives nothing and ends partial, the floor named in error. A rederive job is also held — partial, nothing derived, no stamp — while the wallet's registration replay (portfolio_backfill_state.status) is error, queued or running: the re-derivation trigger's own hold, because a stamp there would certify a history the replay never laid down (#756). And a rederive job never stamps below the recorded arm block (portfolio:v2-campaign:arm-block): it is held while none is recorded or the ingested tip is below it, and derives nothing when its to_block is below it, because a derive cursor under the arm reads as a short build for good — the trigger's own third guard.

An audit job waits for the ledger, and says why when it gives up. It checks the reading only once the wallet's ledger is derived through the reading's block (the derive cursor's stamp, the tick cursor, the newest done job's to_block or the live tip, whichever is highest, with the tick cursor, a done sync job's to_block and the live tip each counted only below the wallet's pending marker, PR #959 review round 4, B3, and left out altogether when the marker cannot be read, round 5, SF-1); until then it returns to queued without spending an attempt, having asked for the continuous job that closes the gap (a wallet whose whole history is not derived yet waits for its registration replay or re-derivation instead). Past six hours it ends partial with the reason "not audited", and so does an audit that is still meeting its write's fence, the write lock, or an in-place re-derivation that only waited: each of those hands its attempt back until then. An audit that books an unexplained correction, or finds a leg unread twice, ends partial with the page's line in error (the ingester alarm's ledger-audit arm pages on the durable rows, not on the job's state); one that throws is retried like any other job. Its age is not counted by the derive-lag arm's "oldest queued" bound, because a deferred audit waits by design.

Indexes: (chain_id, state, priority, created_at) — the claim, which takes the next queued job by (priority, created_at) under FOR UPDATE SKIP LOCKED, and the alarm's reads — and (chain_id, wallet, state) for one wallet's jobs.

Owned by the app role (plan section 3): the queue is write-heavy scratch state whose writers all connect as onchain_credit, and the worker prunes its own finished rows, so the role that churns it can also VACUUM / ANALYZE it. It is the second relation that role owns (064 handed it raw_events). No foreign key to accounts, deliberately: a producer (the ingester's cycle end) must not depend on an account row existing at that instant, and a job for a deleted wallet fails its merge on the ledger's own FK and parks. The staging scrub truncates it and the reseed's leak check watches it, because every row names a tracked wallet.

What writes it in this release. The ingester (continuous, one job per wallet the newly ingested range dirtied), the worker (claims and verdicts), the three writers of a stored reading (audit: the 6h tick for every wallet it read, the live tip after its reading commits, the registration replay over its whole grid after the floor openings), and an operator by hand (a continuous or rederive row, deployment; a sync or enrol row is refused by the worker until the flip). The three writers that derive the ledger inline today — the 6h tick, the registration replay, the page load — keep doing so while the constant WORKER_OWNS_DERIVATION (src/lib/portfolio/derive-jobs.ts) is false, which it is for this release; flipped, they enqueue sweep, enrol and sync instead. Its two cursor rows live in chain_scan_cursors beside the ledger's: portfolio:worker:heartbeat (the worker's liveness, stamped every 30 s; its block is the highest block a finished job derived to) and portfolio:worker:continuous (how far the ingester has turned ingestion into jobs; every producer run that completes stamps it, an idle one in place, so its updated_at is the producer's liveness, which the ingester alarm pages on). Both are chain-wide pipeline state in a family of their own, outside every prefix a scrub, reseed or user wipe clears. Beside them, per wallet and INSIDE the per-wallet wipe family, the producer keeps its follow record (portfolio:derive:v2:<chain_id>:<wallet>:followed-after, S4b, PR #959 review SF-2): the block after which it has scanned every range with that wallet in its population, written when a newly loaded population gains the wallet, deleted when it loses it and raised above a stretch the producer skipped. It is what lets a quiet wallet's "Updated" stamp ride the producer's cursor (data-pipeline); a wipe that clears it costs that wallet nothing but the next population load's re-recording.

Retention

App-state chat rows are pruned on a schedule; portfolio history is kept in full.

Chat (pruned). The chat tables append forever in normal use (the list UI reads only the newest 100 threads; chat_usage accumulates one row per (uid, UTC day)), so a weekly cron (scripts/chat-retention.ts, crontab 10 4 * * 1, via run-cron.sh) prunes the stale tail (audit B6). It deletes chat_conversations with no activity in 365 days (updated_at is bumped every turn) and chat_usage rows older than 400 days, both in one transaction. chat_messages is never deleted directly: it cascades from the conversation delete via the migration 038 FK (conversation_id ... ON DELETE CASCADE). The prune is a child/leaf delete, so it can never reach accounts: the migration 056uid FKs point FROM the chat tables TO accounts (child to parent), and deleting a child row never cascades up. Windows are conservative on purpose (a user keeps a full year of conversations, and the daily-spend chat_usage rows outlive that by a grace month). Prod only; staging has no crons, and its reseed truncates the chat tables anyway.

The derivation queue (pruned by its consumer). portfolio_derive_jobs is scratch state: the ledger worker deletes done and partial jobs whose finished_at is more than 14 days old, once an hour. failed jobs are never pruned: each is a range some wallet's ledger may still be missing, and it pages until a person requeues (state = 'queued', attempts = 0) or deletes it — or, for a continuous job, until the worker closes it to partial because the ledger has caught up over its range (the tick cursor passed it, or a later job for the wallet derived through it), after which it is pruned with the rest.

Portfolio history (retained in full, by design). portfolio_position_snapshots and portfolio_flow_events_v2 are deliberately NOT pruned. They are the product, not a cache: the PnL engine derives every performance number at READ time from the full ledger, so returns since inception need every flow and the snapshot series backs every chart timeframe. Pruning them would silently truncate history, so retention here is a product decision (keep everything), not hygiene. Growth is bounded and slow (legs x wallets x 4 snapshots/day; flows only on real on-chain activity). Because nothing is ever dropped, both tables are partitioned for read locality rather than for retention: the spine monthly by snapshot_ts (migration 067), the flow ledger by hash(wallet) MODULUS 16 (migration 072). Neither partitioning scheme exists to make a DROP cheap; each exists so a per-wallet read stays a small index descent as the table grows toward the 100k-wallet target.

If snapshot volume ever forces the issue, the intended path preserves correctness rather than dropping rows: downsample snapshots older than ~6 months to the 1d grid (keep only each UTC day's last snapshot), because the 1d history bucket already reads only that row, so a view older than ~6 months loses no fidelity while the 6h resolution stays for the recent window that charts it. Flows are NEVER downsampled or pruned under any scheme (they are the read-time PnL inputs, and dropping one corrupts the whole curve after it). This is the documented design, not implemented.