feat: Add per user performance to indexer

[mustermeiszer]

Context

We need to track per-user investment performance in the indexer. Token-level yield calculations already exist (v3.5.0) — rolling windows, TTM, YTD, since-inception on TokenSnapshot. What's missing is per-user earnings tracking: how much did a specific investor earn/lose with a specific token over any time period.

The approach is event-driven: snapshot on every position change, maintain a running cumulative view. No daily per-user snapshots — too heavy. The frontend reconstructs arbitrary time ranges using position snapshots + token price history (TokenSnapshot).

Tracking is per chain (TokenInstance level), matching where Transfer events fire and positions exist. Frontend aggregates across chains if needed.

---

Data model

New entity: InvestorPositionSnapshot

Write-once event log, one row per balance change per user.

Column	Type	Description
tokenId	hex	Share class token
centrifugeId	text	Chain identifier
accountAddress	hex	User account
poolId	bigint	Pool ID
balanceBefore	bigint	Balance before the change
balanceAfter	bigint	Balance after the change
tokenPrice	bigint?	Token price at time of change (WAD)
periodEarnings	bigint?	balanceBefore * (currentPrice - previousPrice)
cumulativeEarnings	bigint	Running total of period earnings
costBasisBefore	bigint	Cost basis before this event
costBasisAfter	bigint	Cost basis after this event
realizedPnl	bigint?	Realized gain/loss from this event (nonzero only on decreases)
cumulativeRealizedPnl	bigint	Running total of realized P&L
trigger	text	Event name (e.g. tokenInstance:Transfer)
logIndex	integer	Event log index for ordering within a block
defaultColumns(t, false)	—	createdAt, createdAtBlock, createdAtTxHash (immutable)

PK: (tokenId, centrifugeId, accountAddress, createdAtBlock, logIndex)
Index: (accountAddress, tokenId, createdAt) — the primary query path

Extend existing: TokenInstancePosition

Add fields to the existing entity for the "latest view":

Column	Type	Description
tokenPriceAtLastChange	bigint?	Token price when position last changed
cumulativeEarnings	bigint (default 0)	Running total of period earnings
costBasis	bigint (default 0)	Total cost of current position
cumulativeRealizedPnl	bigint (default 0)	Running total of realized gains/losses

---

Earnings formulas

Two complementary views of earnings:

1. Period earnings (price appreciation on held position)

periodEarnings = balanceBefore * (tokenPrice_now - tokenPrice_lastChange)

Captures how much the existing position gained/lost between position changes. Raw bigint, precision = tokenDecimals + 18 (balance × WAD-scaled price delta). null when prices unavailable or balance was zero.

2. Cost basis and realized/unrealized P&L

Tracks what the user "paid" for their shares vs what they're worth now.

On position increase (deposit, transfer in):

costBasis_after = costBasis_before + (amountAdded * currentTokenPrice)
realizedPnl = 0

On position decrease (redeem, transfer out):

avgCostPerShare = costBasis / balanceBefore
realizedPnl = amountRemoved * (currentTokenPrice - avgCostPerShare)
costBasis_after = costBasis_before - (amountRemoved * avgCostPerShare)

Unrealized P&L (computed by frontend at any time):

unrealizedPnl = currentBalance * currentTokenPrice - costBasis

Uses token price at time of Transfer as proxy for acquisition/disposal price. This is accurate — share transfers happen at or very close to the epoch execution price.

---

Hookpoint

Single integration point: tokenInstance:Transfer handler in tokenInstanceHandlers.ts.

This is the only place where addBalance/subBalance are called on TokenInstancePosition. All deposit executions, redemptions, and P2P transfers ultimately produce Transfer events.

Flow:

1. Transfer event fires for user account (not null/escrow)
2. Read position's current balance, tokenPriceAtLastChange, cumulativeEarnings, costBasis, cumulativeRealizedPnl
3. Read Token.tokenPrice (canonical NAV price)
4. Compute periodEarnings and new cumulativeEarnings
5. Compute cost basis change and realizedPnl:
   • Position increase: costBasis += amount * tokenPrice, realizedPnl = 0
   • Position decrease: avgCost = costBasis / balance, realizedPnl = amount * (tokenPrice - avgCost), costBasis -= amount * avgCost
6. Insert InvestorPositionSnapshot row (with all before/after state)
7. Update position: tokenPriceAtLastChange, cumulativeEarnings, costBasis, cumulativeRealizedPnl
8. Apply balance change (existing addBalance/subBalance)
9. Save

---

Frontend reconstruction

For "earnings on day D" for user+token:

1. Find last InvestorPositionSnapshot before day D → balanceAfter = balance entering day D
2. Get token prices from TokenSnapshot for day boundaries
3. No changes during day: earnings = balance * (price_end - price_start)
4. Changes during day: split at snapshot points, sum sub-periods

For "total earnings over [D1, D2]":

• cumulativeEarnings(last snapshot <= D2) - cumulativeEarnings(last snapshot <= D1) + boundary adjustments using token price snapshots

---

Files to change

File	Change
ponder.schema.ts	Add 4 columns to TokenInstancePosition, add InvestorPositionSnapshot table + relations
src/services/InvestorPositionSnapshotService.ts	New — service with createSnapshot()
src/services/TokenInstancePositionService.ts	Add setters for new fields
src/services/index.ts	Barrel export new service
src/handlers/tokenInstanceHandlers.ts	Hook snapshot creation into Transfer handler

---

Edge cases

• First position: periodEarnings = null, costBasis = amount * tokenPrice, set tokenPriceAtLastChange = currentPrice
• Full redemption (balance → 0): record final period earnings + realized P&L, costBasis → 0, cumulatives persist
• Token price 0/null: periodEarnings = null, realizedPnl = null, don't update cumulatives or cost basis
• Multiple transfers in same block: each gets its own snapshot (different logIndex)
• Mint/burn (null address): no snapshot — only user accounts tracked
• Escrow addresses excluded (existing logic)
• Division precision for avgCostPerShare: compute realizedPnl = amount * costBasis / balance directly to avoid separate division step

[filo87]

@mustermeiszer do you have few more details on how do you envision this?

[mustermeiszer]

Done