Indexer

Summary

The Indexer is a background service that subscribes to Solana program account changes and transaction logs, decodes on-chain state using the Anchor IDL, and keeps the PostgreSQL database in sync. It also publishes events to Redis so the API Server can broadcast real-time WebSocket updates.

The Indexer is the only service with a persistent RPC WebSocket connection. The API Server never reads from the RPC for serving requests - it reads from PostgreSQL (history) or Redis (live state).

---

Responsibilities

Responsibility	Description
History recording	Insert immutable records into trades, positions_history, orders_history, account_transactions, vault_transactions on each relevant on-chain event
Identity creation	Insert rows into markets on initialize_market, vaults + accounts on create_vault. If an event references a UserAccount PDA with no matching accounts.address row, create it automatically
WS triggering	After writing to DB or receiving a live state change, PUBLISH an event to Redis so the API broadcasts to subscribed clients
Hourly stats job	Every hour: upsert account_snapshots, vault_stats, and market_stats rows by reading live state from chain
Candle building	Upsert candles rows from each FillEvent

---

Subscription Strategy

Two complementary approaches are used together:

Method	API	Use case
onProgramAccountChange	RPC WebSocket	Real-time updates for Position, OrderBook, FundingState, Vault, UserAccount accounts - used to trigger WS broadcasts and Redis cache updates
onLogs	RPC WebSocket	Fill events, liquidations, margin movements, and vault events not reflected in account data alone

On startup, the Indexer does a full sync via getProgramAccounts to catch any changes that occurred while it was offline, then switches to subscriptions.

---

Account Monitoring (→ WS)

These accounts are monitored for live state changes. On each change the Indexer PUBLISHes a Pub/Sub event to Redis, which the API receives and broadcasts to subscribed WebSocket clients. None of this is written to PostgreSQL - the DB only stores history. The Redis cache (live state) is populated by the API on RPC cache miss, not by the Indexer.

Account	Anchor type	WS channel triggered
UserAccount	UserAccount	account:{id}
Position	Position	positions:{wallet}
OrderBook	OrderBook	orderbook:{symbol}
FundingState	FundingState	market:{symbol}
Vault	Vault	vault:{id}

---

Event-driven DB Writes

The Perps and Vault programs emit structured log events (via Anchor's emit! macro). Each event maps to one or more DB inserts.

Perps Program Events

Event	DB write	Notes
FillEvent	INSERT trades, UPSERT candles	---
CancelOrderEvent	INSERT orders_history (status canceled)
ClosePositionEvent	INSERT positions_history (status closed)
LiquidationEvent	INSERT positions_history (status liquidated)
DepositMarginEvent	INSERT account_transactions (type deposit)
WithdrawMarginEvent	INSERT account_transactions (type withdrawal)
TransferMarginEvent	INSERT account_transactions ×2 - transfer_out for sender, transfer_in for receiver
InitializeMarketEvent	INSERT markets

Vault Program Events

Event	DB write	Notes
CreateVaultEvent	INSERT accounts (type vault) + INSERT vaults	PDA address computed from event data
VaultDepositEvent	INSERT vault_transactions (type deposit)
VaultWithdrawEvent	INSERT vault_transactions (type withdrawal)

---

Periodic Jobs

The Indexer runs two background cron jobs.

Hourly Stats Job

Runs every hour. Reads live state from chain and upserts one row per entity per bucket into the stats tables.

Table	Source	Computation
account_snapshots	UserAccount PDA + open Position PDAs	---
vault_stats	Vault PDA	---
market_stats	getProgramAccounts for all Position accounts per market + trades table	----

Leaderboard Job

Runs every X minutes. Recomputes all leaderboard_stats rows from the DB. No chain reads required - all data is already in positions_history, trades, and account_snapshots.

Column	Computation
equity	Read from Redis (account:{id}) - populated by API on RPC cache miss
all_time_pnl	---
realized_pnl	SUM(positions_history.realized_pnl)
volume	SUM(size * price) FROM trades WHERE maker_wallet = wallet OR taker_wallet = wallet
trade_count	COUNT(*) FROM trades WHERE maker_wallet = wallet OR taker_wallet = wallet
funding_paid	SUM(positions_history.funding_paid)
win_rate	COUNT(*) FILTER (WHERE realized_pnl > 0) / COUNT(*) FROM positions_history
sharpe_ratio	Computed from the equity series in account_snapshots
max_drawdown	Peak-to-trough from the equity series in account_snapshots

The endpoint GET /leaderboard is a simple SELECT * FROM leaderboard_stats ORDER BY all_time_pnl DESC - no computation at request time.

---

Candle Building

Candles are derived from FillEvent logs.

1. A FillEvent arrives via onLogs
2. The indexer floors the fill timestamp to the current interval bucket (1m, 5m, 15m, 1h, 4h, 1d)
3. It upserts the candles row for that (market_id, interval, open_time) bucket:
   • If no candle exists yet: open = close = fill.price, high = fill.price, low = fill.price, volume = fill.size
   • If a candle already exists: update high/low/close and add to volume

Candle data reflects real trade prices, not mid-market estimates.

---

Account Matching

The Indexer must link on-chain UserAccount PDA addresses to accounts rows in the DB.

When an event references a UserAccount PDA:

1. Look up accounts WHERE address = <PDA from event>
2. If found: use that row
3. If not found: insert a new accounts row with the PDA address and type = secondary - this handles wallets that created sub-accounts on-chain directly without going through the API

Primary accounts (index = 0) are always created by the API on first auth, so they should always have a matching row.