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.
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.

Migration workflow

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

bash

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.

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.
- Readers must tolerate the columns being absent. /carries is statically prerendered, and the staging deploy runs npm run build BEFORE migrate.sh — so a query naming these columns pre-migration fails the build, which rolls back, which means the migration never runs. carries-table.ts therefore probes information_schema and falls back to typed NULLs (chart drops to its quoted path). Keep that guard until 047 is on prod AND a prod dump has been reseeded to staging.
- 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 — api-data.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) api-data.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).

`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 — the reader HARD-filters maturity_ts IS NULL OR maturity_ts > now(), the 6h pendle refresher flips active -> matured, and sync-carries never proposes under 14 days of runway), 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 api-data.loadFluidState — 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 — liquidityIndex / variableBorrowIndex, the basis for the realised-ratio APY), supply_apy_24h / borrow_apy_24h, total_deposited / available_liquidity, block_at_snapshot.
Populated by: scripts/refreshers/aave-v3.ts, every 6h, via one getReserveData(asset) per reserve.
Read by: money-market-rates.ts, carries-table.ts, apy.ts, vault-capacity.ts (getEmodeDebtUtilization: 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).
Populated by: scripts/refreshers/sparklend.ts, every 6h.
Read by: money-market-rates.ts, carries-table.ts, apy.ts, vault-capacity.ts (getEmodeDebtUtilization: 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 only the seed/fallback.
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.
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).
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).
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 Repo lending "Curator funds" page keeps its curated src/data/curator-vaults.ts (a vetted, allowlisted-curator, ≥$100k subset) and its Euler vaults; neither is widened by this table.
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.

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, snapshot_ts). Keyed on collateral_symbol (not address) because Fluid LP collateral has no single address.
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.
Key columns: exposure_usd, exposure_qty (meaning depends on basis), basis (fluid_borrow exact / position_collateral Aave-Spark attributed / 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), 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).
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.
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 noNewDebt reserve). 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.)
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….
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), 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 PORTFOLIO_ERC4626_VAULTS (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, … — same 6h convertToAssets sampling, from the wrapper's deployment).
Read by: strategies-table.ts, yield-history.ts, carries-table.ts, oracles.ts, basis.ts, curator-funds.ts, apy.ts, and portfolio/api-data.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 is rebuilt + promoted by scripts/backfill-token-basis-shadow.ts (shadow → parity → swap; 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` — 6 cols

Row: one token, one 5-minute bar — PK (chain_id, token_address, bar_ts).
Purpose: a local mirror of Dune USD price bars — the historical market-price source the mark pipeline reads once the Dune price-mirror refactor's later phases land (docs/plans/dune-price-mirror-plan.md). Standing data is hourly (prices.hour); narrow exact 5-minute bars (prices.minute) are layered in only where a flow is trued up. 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). Ships EMPTY in Phase A; nothing reads it yet.
Key columns: chain_id (1 = ethereum, incl. the WETH denominator; 0 = the bitcoin reference row, the BTC-book numeraire), token_address (lower-cased; chain-0 rows normalized to a fixed bitcoin sentinel), bar_ts (bar START, UTC — hourly on the hour, true-up on the 5-min grid; a CHECK enforces 300s alignment), price_usd (NUMERIC CHECK (> 0)), source (per-row provenance: dune:prices.hour or dune:prices.minute), ingested_at.
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. 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.

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).
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.
Read by: the refresher itself (registry-driven snapshot loop) and the phase-1 PT-carry integration.

`pendle_market_state` — 13 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).
Populated by: scripts/refreshers/pendle-markets.ts (6h, basis='rpc') and scripts/backfill-pendle-history.ts (one-shot per-market daily history from the Pendle API, ON CONFLICT DO NOTHING so API rows never overwrite cron rows).
Read by: phase-1 PT-carry integration; later the fixed-rates section + agent/MCP surface.

Capacity / risk params

`vault_capacity` — 29 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.
Populated by: scripts/refreshers/vault-capacity.ts, every 6h.
Read by: vault-capacity.ts → app/carries/page.tsx.

`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.
Populated by: scripts/refreshers/sofr-rates.ts, weekdays 13:00 UTC (NY Fed).
Read by: sofr.ts (consumed by MoneyMarketRatesChart, the strategies comparison charts, and the home rate tape).

`curator_vault_state` — 6 cols, ~35 rows

Row: one curator-fund vault — PK token_address.
Purpose: fee + live market allocation for the money-market curator funds (Morpho / Euler USD vaults) so /repo-lending reads only our DB at request time.
Key columns: fee (fractional performance fee), net_apy (protocol-reported, cross-check/display), total_assets_usd, allocation (jsonb array of collateral markets [{collateral, supplyUsd, lltv}]).
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 → components/money-market/CuratorFundsTable.tsx.

`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 or the column missing pre-046 (42703 fallback to the legacy statement) 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 both the portfolio and the AI assistant. 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.

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). 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, basis='live') or the WS5 archive replay (scripts/backfill-portfolio-wallet.ts, basis='backfill'); the app writes only the JIT flow rows (basis='live' below the settle line, basis='provisional' in the reorg-exposed tail above it, see the flow-ledger row below) and otherwise reads only. 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/BTC, or EXCLUDED for an asset the book map does not recognise) on the row; 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. `snapshot_ts` is ALWAYS the aligned 6h window (`alignedWindowIso`); `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. `value_market` / `value_redemption` are the two marks (M5). `basis` = `live` \| `backfill`. `venue` CHECK admits `fluid` (populated by the FWS2 reader). 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 `api-data.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 `writeSnapshots` ON CONFLICT target is UNCHANGED; 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 `docs/deployment.md`).
`portfolio_flow_events`	The 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 (the 6h cron, which stops 64 blocks below its anchor, and the JIT for everything at/below its own settle line `anchor − 64`); `backfill` = the WS5 archive replay; `provisional` = a JIT row in the reorg-exposed tail ABOVE its settle line, i.e. the last ~64 blocks, which only the JIT ever writes. 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 is no `basis` predicate anywhere in `api-data` / `assemble` / `pnl`); it differs only in who may DELETE it. Two 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). 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). Both deletes are bounded ABOVE and BELOW by the block range their own writer re-derived, and both 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. This release ships the 3 widened writers atomically (`writeFlows`, `writeFlowRows`, `writeBackfillFlows`); 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 `DELETE`s 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 two `loadPendleMarkets` held-by-anyone flow arms also name no wallet, but they live only in `PENDLE_MARKETS_QUERY_LEGACY` — the fallback taken when migration `065` is unapplied and the `portfolio_held_pts` probe throws `42P01` — so they are a dead path wherever `065` is applied, i.e. on prod and staging, 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 (which commits its snapshots first, leaving balances that read as yield, M3) 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-flows.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; the hard-coded `ACCOUNTING_ASSET_BOOKS` stays only as the seed + pure-context fallback) 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`/`BTC` 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. Three kinds qualify: a non-based fiat (EURC), a non-USD/ETH/BTC denomination (XAUt, gold), and a dollar-denominated token with no honest par claim (apxUSD — whitelist-only redemption and a live secondary discount, so par would over-report it), the last two added by migration `071`. 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 `JIT_RATE_GETTERS` 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; migration `053` adds sGHO (Aave Savings GHO) as a `variable_rate` `asset-profile` row. 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 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).
`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 "History syncing" UI, 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 (the code tolerates the column being absent pre-045). `progress_done`/`progress_total`/`progress_started_at` (migration `060`) are the live reconstruction cursor behind the `/portfolio` "building history" state: 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, 42703-tolerant pre-060). 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.
`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 SEEDS it from existing `portfolio_position_snapshots` + `portfolio_flow_events` (position_key encodes the universe_key) AND seeds a discovery WATERMARK (`chain_scan_cursors` scope `portfolio:discover:<wallet>`) for every historically-active wallet; the 6h cron upserts it from the flow-scan byproduct (`membershipsFromFlows`, no extra getLogs); 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 + sets the watermark). A wallet is SCANNED iff it has a WATERMARK, not merely an index row — the incremental byproduct only adds the CURRENT window's markets, so 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 watermark means "the index is COMPLETE for current holdings" (the seed and the current-read enrollment both guarantee 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). Since the coverage hardening the SCANNED test is the certificate on `accounts` (see below), not the presence of a scope row, and the registration backfill also writes memberships here on its terminal transition.
`fluid_event_log` (migration 044, FWS3 / D7)	The decoded Fluid event cache. One row per `(chain_id, tx_hash, log_index)`: EVERY Fluid vault `LogOperate` / `LogLiquidate` chain-wide (not just tracked NFTs — future signups need arbitrary NFTs). `kind` ∈ `operate` (nft_id present; `col_amt`/`debt_amt` = the SIGNED int256 deltas in the leg's native units) / `liquidate` (nft_id NULL; `col_amt`/`debt_amt` = the vault-aggregate amounts, used only as the M15 state-diff TRIGGER). `ts` is provenance only (the pipeline re-reads block ts where it values a flow). Writers: the one-time deploy seed (`scripts/seed-fluid-event-log.ts`, trailing 90d chain-wide) and the 6h cron (appends what it decodes, idempotent on the PK). Readers: the registration backfill + the JIT mini-scan query this cache instead of re-scanning the chain (`LogOperate`/`LogLiquidate` have ZERO indexed params and cannot be wallet-filtered). ~150 events/day, ~55k rows/yr.
`fluid_event_coverage` (migration 044, FWS3 / D7)	One row per chain: `start_block`/`start_ts` = the earliest block `fluid_event_log` is seeded from (monotone-down). A backfill window starting BELOW `start_block` cannot be served from the cache and falls back to a chain scan with a loud log line (guarded; impossible once the 90d seed has run and the 90d registration window is bounded).

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; fluid_event_log needs it for an operational re-seed and fluid_event_coverage UPDATE for its monotone-down start. 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, portfolio_wallet_index or portfolio_backfill_state 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.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, which scrub-staging-pii.sql now prevents by deleting the portfolio:discover:% scope rows alongside the user-data TRUNCATE (with a fail-closed check in reseed-staging.sh).

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.

Deploy tolerance. Code and migration can land in either order. Before 061 the readers fall back to the legacy scope-row presence test, WHOLE-BATCH, on a 42703 (never per row: a per-row COALESCE would resurrect exactly the orphaned scope rows this design stops trusting). Writers dual-write the scope row for one release so a code rollback still finds a current watermark. A later cleanup migration deletes the scope rows once every reader is ported; portfolio-reconcile.ts was the last one, and it now 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; Phase B derives the 6h cron's flows + dirty/recompose snapshots from them behind the PORTFOLIO_LEDGER_MODE flag (Data pipeline → ledger-driven cron), and adds two more tables (migration 065, 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)` — the PK is also the reorg guard (a re-scan of a block range is `ON CONFLICT DO NOTHING`). Columns: `block_ts`, `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`. `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. 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`. 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). Indexed `(chain_id, stream, status)` for the ingester's new-token detection.
`portfolio_parity_runs` (migration `065`, Phase B; `flows_field` added by `066`; `snap_compared`/`snap_mismatch` made nullable by `069`)	The shadow-mode audit ledger: one row per `(chain_id, snapshot_ts, mode)` (UPSERT per aligned 6h window + mode). Holds the counts the `[portfolio/parity]` line reports (`flows_legacy`/`flows_ledger`/`flows_missing`/`flows_extra`/`flows_field`/`snap_compared`/`snap_mismatch`/`promoted`/`ledger_lag_blocks`) plus `details` jsonb — the first 50 mismatches, each with its PK + both sides' values. `flows_field` (common-PK flows differing on kind/asset/amount/value) gates `isCleanParity`, so it must be in the durable record too. `snap_compared`/`snap_mismatch` are NULL when the tick's snapshot diff was SKIPPED (stored-leg preload failure; the flow diff still ran) — 0 is reserved for "ran, compared zero rows", so a skipped tick can never satisfy a counters-are-zero flip query (NULL ≠ 0) and `isCleanParity` is false for it. Written only when `PORTFOLIO_LEDGER_MODE=shadow`, so an operator can decide the flip (≥3 consecutive zero-diff days on staging) from the durable record. Tolerance: `qty_raw`/`index_raw`/`amount_raw` EXACT, float value columns at 1e-9 relative.
`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 + flow writers 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 is retained as `PENDLE_MARKETS_QUERY_LEGACY`, the fallback where 065 has not landed).

Cursors reuse chain_scan_cursors with scopes ledger:<stream> (the ingester's live head), ledger:<stream>:backfill (the one-time sweep's resume point), and (Phase B) portfolio:ledger:dirty-tip (the on-mode dirty-scan tip). 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.

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.

Portfolio history (retained in full, by design). portfolio_position_snapshots and portfolio_flow_events 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.

Database & schema ​

Where it lives ​

The core APY convention ​

Agent-grade table conventions ​

Migration workflow ​

Fluid ​

fluid_dex_apy — 38 cols, ~55k rows ​

fluid_ll_apy — 14 cols, ~54k rows ​

carry_registry — unified carry registry (all venues) ​

fluid_vault_registry — DEPRECATED (~116 rows) ​

Lending venues (rates) ​

aave_v3_reserve_apy — 15 cols, ~12.3k rows ​

sparklend_reserve_apy — 15 cols, ~8.3k rows ​

morpho_market_apy — 18 cols, ~11.1k rows ​

morpho_market_registry — Morpho market membership (migration 041) ​

metamorpho_vault_registry — MetaMorpho factory vault universe (migration 052) ​

lending_reserves — 15 cols, ~85 rows ​

Positions / exposure ("Underwritten capital") ​

lending_borrowers — 7 cols, ~77.5k rows ​

lending_positions_current — 12 cols, ~2.1k rows ​

market_collateral_exposure — 20 cols, ~18.6k rows ​

market_risk_current — 15 cols, ~6 rows ​

chain_scan_cursors — 4 cols, ~4 rows ​

Assets & yields ​

assets — 14 cols, ~14 rows ​

token_yield_apy — 9 cols, ~112k rows ​

token_basis — 8 cols, ~36k rows ​

token_price_bars — 6 cols ​

Pendle (fixed rates) ​

pendle_markets — 15 cols, ~55 rows ​

pendle_market_state — 13 cols, growing ~220/day + backfill ​

Capacity / risk params ​

vault_capacity — 29 cols, ~22 rows ​

vault_risk_params — 8 cols, ~21 rows ​

capacity_notifications — 12 cols, ~1 row ​

Misc ​

sofr_rates — 8 cols, ~2.1k rows ​

curator_vault_state — 6 cols, ~35 rows ​

newsletter_subscribers — 6 cols, ~2 rows ​

schema_migrations — 2 cols ​

Accounts + chat assistant (app state) ​

Portfolio (read-only) ​

Discovery certificate lifecycle ​

Event ledger (portfolio 100k scale) ​

Retention ​