Portfolio 100k-Wallet Scale Plan — Event Ledger Architecture

Owner: Fred. Architect/orchestrator: Claude (Fable). Implementation + reviews: Opus 4.8 agents at max effort, per phase, gated by the architect against the invariants in §7.

0. Goal and non-goals

Goal. Re-plumb the portfolio spine so it serves up to 100k registered wallets without hard failures, with no functional regression on the /portfolio view: same positions, same marks, same flows, same charts, same API shapes. Side benefits (also in scope): new-user first chart in seconds instead of minutes, sub-minute live freshness, flat RPC cost (~$100/mo at 100k on Alchemy PAYG).

Non-goals. No new user-facing features. No change to valuation math, flow classification semantics, bucketing, or the accounts/eligibility model. No multi-chain work. Analytics refreshers untouched.

1. Why the current design fails at scale (summary)

1. Wallet-topic OR-arrays in scanTransferFlows / scanMorphoFlows / scanLiquidationFlows put every registered wallet into eth_getLogs topics. Providers cap topic-array/body size; hard failure at ~1–2k wallets.
2. O(wallets × universe) 6h sweep: ~350–400 inner calls per wallet per tick. At 100k wallets: ~80k sequential aggregate3 requests, multi-GB result arrays in one Node pass (OOM), venue isolation at whole-venue granularity, one giant write transaction.
3. Serial signup backfill: 90-day chain scans per wallet, one at a time, on free-tier archive RPC. Minutes per wallet; a signup surge queues for days.
4. DB growth: unpartitioned append-only tables; loadPendleMarkets seq-scans the full snapshot + flow tables inside loadRegistries on every JIT refresh.

2. Target architecture

Principle: events are for discovery, dirty-marking, and flow classification — never balance-of-record. Balances always come from multicall reads. The completeness bar for the event set: every on-chain action that changes a wallet's position quantity emits at least one stored event. The weekly reconciliation sweep bounds any gap to staleness, never permanent wrongness.

Components:

• Event ledger (onchain_credit.raw_events, partitioned): all events on tracked contracts + the Fluid chain-wide streams, ingested continuously, wallet-count independent.
• Ingester: an always-on PM2 process polling ~60s behind a 64-block safety margin, address set derived each cycle from the same DB registries the venue readers use (trackedContracts(registries) — single source, so reader universe and event universe cannot drift).
• Coverage certificates (onchain_credit.event_coverage): per-address contiguous covered block range. A newly-derived address triggers a catch-up backfill (deployment→cursor) and is stamped live only after it completes — never stamp coverage that was not scanned (the PR #484 certificate philosophy, generalized).
• 6h snapshot cron, rebuilt: universe-level shared reads (per-reserve indexes, exchange prices, DEX states, prices — O(universe), wallet-count independent) + per-wallet RPC re-reads only for the dirty set (wallets with ledger events since their last snapshot), an always-dirty set (Fluid smart-leg holders, see §5.2), and a reconciliation shard (every wallet re-read fully every RECON_DAYS). Untouched wallets' snapshot rows are recomposed: stored qtyRaw × freshly-read shared index/rate/price. Every eligible wallet still gets rows every window — table shape, charts, and downstream reads unchanged.
• Flow ledger derivation: flows derived from raw_events by SQL + the existing decode/valuation code, replacing live chain scans in the cron and the JIT mini-scan.
• Backfill from ledger: signup activity discovery is an indexed SQL query; archive multicalls only at the wallet's own grid points, parallelized, drained by a worker pool.

3. Event coverage spec

3.1 Class A — structurally complete, universe-independent

Stream	Address filter	topic0 set	Notes
morpho	Morpho Blue singleton	Supply, Withdraw, Borrow, Repay, SupplyCollateral, WithdrawCollateral, Liquidate	every market ever created; events carry market id
fluid-operate	none (chain-wide)	LogOperate, LogLiquidate	zero indexed params; filtered in-process downstream
fluid-nft	Fluid VaultFactory	ERC-721 Transfer	NFT mint + position-NFT ownership transfers
aave-pool	Aave v3 Pool	LiquidationCall, UserEModeSet	classification events (see below)
spark-pool	SparkLend Pool	LiquidationCall, UserEModeSet	same

UserEModeSet is NEW coverage: an e-mode flip changes classification with no balance event; without it a wallet is misclassified until its next event or reconciliation. LiquidationCall does not dirty-mark (Transfers already do); it books the flow as a liquidation, not a voluntary exit.

3.2 Class B — registry-derived ERC-20 Transfer streams

One combined stream transfers (address array, topic0 = Transfer, no wallet topics):

• aTokens + variableDebtTokens of every Aave v3 + SparkLend reserve (lending_reserves, auto-synced from getReservesList() by the lending-positions refresher)
• ERC-4626 share tokens (PORTFOLIO_ERC4626_VAULTS: curator vaults + fTokens)
• Pendle PT tokens (pendle_markets, ALL rows with an underlying — not just active; a matured PT still transfers)

Sufficiency: supply=mint, withdraw=burn, borrow=vToken mint, repay=vToken burn, plus wallet-to-wallet transfers. M2/M14: interest accrual never changes scaled balances, so no event ⇒ no quantity change.

3.3 Known gaps (why the reconciliation sweep exists)

• Fluid absorbed liquidations emit no per-NFT event (M15). Mitigation: LogLiquidate dirty-marks every tracked NFT in that vault; the re-read's state-diff does the booking, exactly as today.
• Ingester outage: cursors + idempotent upserts + 64-block lag re-scan.
• Anything genuinely eventless: bounded by RECON_DAYS staleness, then corrected.

3.4 Universe-expansion protocol (the "USDS" runbook)

1. Registry gains members (auto: reserves/morpho markets/carry sync; manual: curator vaults, pendle additions).
2. Next ingester cycle: derived set contains unknown address → coverage row backfilling → catch-up job backfills its full Transfer history (deployment→live cursor) → stamp live.
3. Holder repair: from the caught-up events, holders ∩ registered wallets → dirty-mark for next tick AND enqueueOrRequeueBackfill for history repair.
4. Class A venues need none of this (singleton/chain-wide streams already covered new markets/vaults from birth); adding registry rows only enables valuation, replayed from events already stored.
5. Tripwire: alert when a snapshot values a leg whose position token lacks a live coverage row (extends the WS8 unknown-asset alert pattern).

4. Schema (migration 049, additive)

-- raw_events: append-only, partitioned by block_number range (250k blocks/part,
-- auto-created by the writer). PK includes the partition key.
CREATE TABLE onchain_credit.raw_events (
  chain_id     int    NOT NULL,
  block_number bigint NOT NULL,
  block_ts     timestamptz NOT NULL,
  tx_hash      text   NOT NULL,
  log_index    int    NOT NULL,
  address      text   NOT NULL,   -- lowercased
  topic0       text   NOT NULL,   -- lowercased hex
  topic1       text,
  topic2       text,
  topic3       text,
  data         text   NOT NULL,
  stream       text   NOT NULL,   -- 'transfers'|'morpho'|'fluid-operate'|'fluid-nft'|'aave-pool'|'spark-pool'
  PRIMARY KEY (chain_id, block_number, tx_hash, log_index)
) PARTITION BY RANGE (block_number);
-- Indexes (on parent): (address, block_number), (topic0, block_number),
-- (topic1, block_number), (topic2, block_number).

CREATE TABLE onchain_credit.event_coverage (
  chain_id   int  NOT NULL,
  stream     text NOT NULL,
  address    text NOT NULL,       -- '*' for chain-wide / singleton streams
  from_block bigint NOT NULL,
  to_block   bigint NOT NULL,     -- contiguous [from_block, to_block]
  status     text NOT NULL,       -- 'backfilling' | 'live'
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (chain_id, stream, address)
);

Cursors reuse chain_scan_cursors with scopes ledger:<stream>. Reorg model: identical to today — scan to head−64, idempotent PK upserts; a same-PK re-scan is a no-op. Phase B adds portfolio_held_pts (chain_id, pt_address, PK both), maintained by the snapshot/flow writers and seeded once from existing rows, so loadPendleMarkets stops seq-scanning history tables. Phase C repartitions portfolio_position_snapshots (monthly by snapshot_ts, migration 067) and portfolio_flow_events (BY HASH (wallet) MODULUS 16, migration 072 — monthly was rejected for it, see Phase C below) while the tables are near-empty post-reset (data-preserving copy; -- DESTRUCTIVE-tagged because it rewrites objects; prod application stays a gated manual step).

5. Phases

Branch bases: Phase A feat/portfolio-100k-ledger off origin/staging; B and C stack on their predecessor. One PR per phase into staging. Docs updated in the same PR (docs/data-pipeline.md, docs/database.md, docs/architecture.md). Migrations forward-only, expand/contract.

Phase A — event ledger + ingester (no behavior changes)

Deliverables:

1. Migration 049-event-ledger.sql (§4).
2. src/lib/portfolio/tracked-contracts.ts: pure trackedContracts(reg, vaults) → {transfers: string[], streams: [...]} with unit tests. THE only source of the ingester's address set; consumes PortfolioRegistries + PORTFOLIO_ERC4626_VAULTS.
3. scripts/ingester/ingest-events.ts: the loop (poll ~60s; derive set, cached ~5 min; per stream getLogsChunked cursor+1 → head−64; per-batch distinct block-header timestamp reads with an in-process LRU; idempotent batched upserts; partition auto-create; cursor advance per stream; new-address detection → catch-up backfill from contract deployment (binary-search first log or COVERAGE_FLOOR, whichever later) → stamp coverage). Fail-soft per stream; a stream's failure never blocks the others. Structured logs matching run-cron alert grep conventions.
4. scripts/backfill-event-ledger.ts: one-time historical sweep of all streams from COVERAGE_FLOOR (2025-05-21) to the live cursor, resumable via coverage rows, archive RPC.
5. PM2 entry + deployment/docs updates; env: existing ETHEREUM_RPC_URL / ETHEREUM_ARCHIVE_RPC_URL (Alchemy slots in by env on the server; code stays provider-agnostic).

Explicitly out: no consumer changes; the 6h cron keeps its own scans (the overlap is harmless and is Phase B's parity baseline).

Acceptance: tsc + vitest green; unit tests for tracked-set derivation, decode, coverage/catch-up state machine, partition boundary math; a bounded live smoke (few-hundred-block window against a public RPC) asserting decoded rows == getLogs ground truth. Reviews (§8) pass with no CONFIRMED correctness findings.

Phase B — ledger-derived flows + dirty-set cron (flagged)

Feature flag PORTFOLIO_LEDGER_MODE = off (default) | shadow | on, read by the cron and (Phase C) the JIT path.

Deliverables:

1. src/lib/portfolio/ledger-flows.ts: derive FlowDetected[] for a block range + wallet set from raw_events (SQL on topic1/topic2/owner topics + in-process filtering for fluid via the NFT ownership map), feeding the UNCHANGED valueFlows → writeFlows pipeline. Reuses the existing decode helpers (flows.ts classify, fluid-flows.ts LogOperate decode) — the classification logic must not fork.
2. Cron rebuild in scripts/refreshers/portfolio.ts:
   • off: today's derivation, byte-identical. ONE mode-independent exception was added later with the two-tier flow persist: the provisional sweep at the end of the flow pass runs in every mode, because the JIT write it retires does too (src/lib/portfolio/flow-basis.ts).
   • shadow: today's path stays authoritative; additionally compute ledger-derived flows + dirty-set snapshot rows and DIFF (flow PK sets; snapshot rows compared on qtyRaw/indexRaw/values within tolerance), logging a single greppable parity line ([portfolio/parity]) per tick + a parity summary table row.
   • on: ledger path authoritative: flows from ledger; snapshots = shared universe reads + re-reads for dirty ∪ always-dirty (fluid smart-leg holders) ∪ reconciliation shard (wallet hash mod K == tick mod K, K sized for RECON_DAYS=7) + recomposition for the rest. Wallet-sharded read passes (≤500 wallets per venue pass), per-shard transactions, per-shard venue isolation.
3. Recomposition core src/lib/portfolio/recompose.ts (pure, heavily unit-tested): latest stored legs + fresh shared indexes/rates/prices → new SnapshotRows. M9 discipline: a leg whose shared source failed this tick is SKIPPED, never zeroed, never stale-copied silently.
4. portfolio_held_pts + loadPendleMarkets rewrite.
5. Parity harness doc: how to read [portfolio/parity], flip criteria (≥3 consecutive days zero-diff on staging), rollback (flag back to off).

Phase C — backfill from ledger, JIT fast path, partitioning

Status: IMPLEMENTED (2026-07-24) on feat/portfolio-100k-ledger (D1–D9). Deltas from the sketch below, all documented in docs/data-pipeline.md / docs/deployment.md: grid concurrency env is BACKFILL_GRID_CONCURRENCY (default 6, not ~8); the JIT freshness gate is JIT_LEDGER_LAG_BLOCKS (default 40) measured against the anchor — because the ingester's 64-block margin means the tip always trails head by ≥ 64, this default keeps the JIT ledger paths OFF (fresh full-read + chain mini-scan, no regression) until an operator raises it above ~64; the backfill probe keeps output identical to the chain path by SQL-serving [from, ledger-tip] and chain-sweeping the small (tip, now] residual, falling back wholesale on an uncovered window. Only portfolio_position_snapshots is repartitioned (monthly, migration 067, -- DESTRUCTIVE/manual); portfolio_flow_events is NOT partitioned MONTHLY — its PK (chain_id, wallet, tx_hash, log_index, leg) has no timestamp, so a monthly partition would need ts in the PK, which breaks the 5-column flow ON CONFLICT target used by three writers; per the brief's "STOP and record" rule the flow table was left alone in C proper. The partition auto-create helper is wired into the snapshot writers (load-bearing) and, at the time, the flow writers (forward-compat no-op).

RESOLVED by migration 072 (follow-up, not the deferred PK extension). The monthly design for the flow ledger was rejected outright rather than shipped behind sign-off: a month key prunes NO hot flow read (nothing filters on ts), so it would only fan every per-wallet statement out over one relation per month. 072 instead partitions portfolio_flow_events BY HASH (wallet), MODULUS 16. wallet is already the second PK column, so the PK, all three conflict targets and every writer are UNCHANGED — no PK extension, no coordinated writer change — and each SINGLE-wallet statement prunes to one of 16 partitions (the wallet = ANY($n) family prunes per value and reaches all 16 by ~64 distinct wallets, so the two cron probes passed the whole tick population touch all 16). Its partitions are created once by the migration (a hash modulus is fixed at CREATE time), so the flow writers' partition calls are DELETED and ensureMonthPartitions now refuses any non-RANGE parent. That deletion makes the deploy order ONE-WAY, and it IS a rollback floor: the previous release's relkind-only probe turns those five call sites into 42P16 failures the instant 072 lands, so ship the code first and never redeploy an earlier build against the hash table. See docs/deployment.md and docs/database.md.

Two-tier flow persist (post-C fix, not in the sketch below). The JIT is the one flow scan that runs to the anchor, so it writes the reorg-exposed last ~64 blocks; those rows carry basis='provisional' and are retired by two bounded deletes (the resync's own self-clean, the cron's once-per-tick settle sweep). It is NOT mode-gated — the phantom-duplicate window it closes exists in off too, which is the mode running in prod — so the Phase C acceptance criterion reads "off = byte-identical EXCEPT the flow basis value and the two provisional deletes". See src/lib/portfolio/flow-basis.ts.

1. Backfill: probeWallet chain sweeps → ledger SQL (coverage-gated: refuse discovery over uncovered ranges); grid-point archive reads parallelized (bounded concurrency ~8); drain worker pool (env BACKFILL_WORKERS, default 4; per-wallet advisory-lock claims preserve FIFO fairness).
2. JIT (live.ts) under flag on: mini flow scan replaced by a ledger freshness check (ingester cursor within ~5 min of head → skip scan; else fall back to today's mini-scan); fast path: no ledger events since last snapshot → recompose-only live view (shared reads cached ~60s process-wide). The 60s per-wallet window + coalescing stay.
3. Repartition snapshots/flows (§4), clean-slate timing.
4. Coverage tripwire alert (§3.4.5).

6. Cutover and ops

1. Merge A → staging auto-deploys + auto-applies additive 049. Manual (Fred or ops session): add PM2 ingester process on the server, run backfill-event-ledger.ts once (staging DB), watch logs.
2. Provision Alchemy app; set env ETHEREUM_RPC_URL/ETHEREUM_ARCHIVE_RPC_URL for ingester + crons (app request path keeps public RPC until proven).
3. Merge B; run staging in shadow ≥3 days; flip staging to on after clean parity; merge C; staging soak ≥2 days (JIT + backfill exercised via minted cookie per the local-verify runbook).
4. Prod: release PR gated on Fred's explicit approval, migrations manual, flag starts shadow on prod too, flip after parity there. Rollback at every step = flag to off (old code paths remain intact through the soak period; their removal is a LATER cleanup PR, not part of this plan).

7. Regression invariants (review checklist — every reviewer, every phase)

1. M9: failed reads skipped, never zero, never fabricated — including the new recomposition path (failed shared source ⇒ skip leg, log).
2. Venue isolation preserved; Phase B narrows blast radius to per-shard.
3. R4: e-mode-null + debt ⇒ wallet contributes nothing that pass.
4. M13 matured-PT par fallback; M14 smart-leg branch on flags not exchange price; M15 liquidations booked as losses, never withdraw/repay.
5. Idempotency: all writers keyed on existing PKs; re-runs are no-ops. The two provisional deletes (flow-basis.ts) are the only non-upsert flow writes, and each may only remove rows over the (wallet, block) region its own transaction (JIT) or tick (cron) has just re-derived.
6. Write-lock discipline: every snapshot/flow writer takes PORTFOLIO_WRITE_LOCK; JIT keeps TRY-lock skip semantics.
7. 64-block safety margin on every live scan and the ingester.
8. Chart/table continuity: in shadow/on, every eligible wallet gets snapshot rows for every 6h window it got them before (recomposition included) — no gaps, no shape changes to portfolio_position_snapshots / portfolio_flow_events / API responses.
9. Withhold-not-zero JIT semantics (PR #461) unchanged.
10. Flow classification parity: ledger-derived flows byte-equal legacy scans over identical ranges (proven in shadow, asserted in tests via fixtures).
11. Eligibility predicate (048 semantics) untouched.
12. Backfill grid semantics (daily grid + 6h seam, open-only replay, windowed delete rules) unchanged in Phase C rework.
13. Docs updated in the same PR; vitepress build green (dead-link check).
14. No em-dashes in any user-facing copy; no new user-facing copy expected.

8. Execution protocol (agents)

Per phase: one Opus 4.8 max-effort implementer (worktree /Users/friedrichcoen/claude/oc-100k, commits locally, stages only files it created/edited by explicit path); then three Opus 4.8 max-effort reviewers in parallel with distinct lenses — (a) functional regressions on /portfolio (1st/2nd/3rd-order effects), (b) financial/data honesty (M-invariants, valuation, flow booking), (c) scale/performance correctness (the failure modes in §1 actually die; no new O(wallets) hot paths; SQL uses indexes) — each returning CONFIRMED/PLAUSIBLE findings with repro reasoning; then one Opus 4.8 max-effort fixer applying confirmed findings; then verifier (tsc, vitest, targeted greps). The architect reads the final diff against §7 before the PR opens. Reviews are posted to the PR as comments. The 1000-line-per-file soft norm and repo comment style apply.

9. Cost/scale budget (reference)

Alchemy PAYG ($0.45/1M CU first 300M, $0.40 after; eth_call 26 CU flat, getLogs 60 CU): ingester ~20–25M CU/mo (wallet-independent); dirty re-reads ~10M/mo and reconciliation ~20M/mo at 100k wallets; JIT ~50–150M/mo at 100k (scales with DAU, not registrations). Steady state ≈ $0–15/mo at 1k, ~$15–25 at 10k, ~$70–115 at 100k; onboarding 100k ≈ ~$150 one-time; history stream backfill < $1 one-time. Throughput ceiling 10k CU/s ≫ any burst here.