Database Model

Architecture

The database stores historical and identity data only. Current state (balances, open positions, open orders, vault TVL) is read directly from the Solana RPC and cached in Redis.

Layer	What lives there	Who writes
PostgreSQL	Historical events, closed positions, filled orders	Indexer (events) + API
Redis	Current state cache - balances, open positions, vault state	API (on RPC cache miss)
Solana chain	Source of truth for all current state	Programs

The indexer listens to on-chain events and writes immutable records to the DB. It never writes current state - that is always read from the chain.

---

Current State (RPC → Redis)

These are not in the DB. The API fetches them from the RPC on cache miss and stores them in Redis with a short TTL.

Data	On-chain account	Redis key pattern
Account balance	UserAccount PDA	account:{id}
Open positions	Position PDA per market	positions:{wallet}
Open orders	OrderBook slab	orders:{wallet}:{market}
Vault state (TVL, NAV, status)	Vault PDA	vault:{id}
User vault deposit (shares, lockup, depositor count)	VaultUserDeposit PDA + getProgramAccounts	vault_deposit:{vault_id}:{wallet}, vault_depositors:{vault_id}
Market config (base/settle currency, fees, tick size, margin %, status)	Market PDA	market:{symbol}
Protocol config	GlobalConfig account	global_config

---

Models

Diagram

---

accounts

One row per trading account. Stores identity only, balance is read from the UserAccount PDA on-chain.

Column	Type	Notes
id	Int	Primary key
wallet	String	Manager/owner wallet public key. For vault accounts this is always the manager's wallet (not the Vault PDA), so that WHERE wallet = ? returns all accounts for a user including their vault's trading account
address	String	UserAccount PDA address — unique per account. The indexer uses this to match on-chain events to DB rows (WHERE address = <PDA from event>)
name	String?	Optional display name, user-set via API
type	AccountType	primary, secondary, or vault — derived from UserAccount.account_type on-chain
created_at	DateTime

Indexes: wallet, address

Type values:

• primary — UserAccount.account_type == Primary on-chain. The API pre-computes the PDA and inserts the row on first auth, before the account exists on-chain
• secondary — UserAccount.account_type == Secondary on-chain. Created by the API when the user adds a sub-account; PDA is initialised on-chain lazily on first use
• vault — UserAccount.account_type == Vault on-chain. Created by the indexer on create_vault. The wallet column stores the manager's wallet so the account is queryable alongside the manager's personal accounts

Written by: API creates primary/secondary rows on auth/sub-account creation. Indexer creates vault rows on create_vault events.

---

markets

Used as FK target for historical tables. Live market config (fees, margin requirements, status) is read from the Market PDA on-chain.

Column	Type	Notes
id	Int	Primary key - internal ID used as FK target
symbol	String	Unique, e.g. SOL-PERP. Used for API lookups before fetching from RPC
address	String	Market PDA address on Solana, used to fetch live config from RPC
created_at	DateTime

All other config (base_currency, settle_currency, tick_size, step_size, maker_fee, taker_fee, status, margin requirements) is read from the Market PDA on-chain and cached in Redis under market:{symbol}.

Written by: Indexer - inserted on initialize_market events.

---

positions_history

Closed and liquidated positions only. Open positions are read from Position PDAs on-chain.

Column	Type	Notes
id	Int	Primary key
market_id	Int	FK → markets.id
account_id	Int	FK → accounts.id
wallet	String	Owner wallet (denormalised for fast queries)
side	String	long or short
size	Decimal	Base quantity
entry_price	Decimal	Average entry price
exit_price	Decimal	Price at close or liquidation
margin	Decimal	Allocated margin (USDC)
realized_pnl	Decimal	Total PnL at close - price PnL minus fees plus/minus funding
funding_paid	Decimal	Funding component isolated - positive = received, negative = paid. Captured from the close_position event
status	String	closed or liquidated, open positions are read from chain, never stored here
opened_at	DateTime
closed_at	DateTime

Indexes: (wallet, status), (market_id, status)

Written by: Indexer - inserted on close_position or liquidate_position events.

---

orders_history

Filled and canceled orders only. Open and partially filled orders are read from the OrderBook slab on-chain.

Column	Type	Notes
id	Int	Primary key
market_id	Int	FK → markets.id
account_id	Int	FK → accounts.id
wallet	String	Owner wallet (denormalised)
side	String	long or short
type	String	limit or market
price	Decimal?	Null for market orders
size	Decimal	Original quantity
filled	Decimal	Total filled quantity
order_value	Decimal	price * size - pre-computed from the fill event, avoids recomputing at query time
reduce_only	Boolean	Whether the order was flagged reduce-only on-chain
trigger_conditions	String?	Human-readable description for stop/TP orders, null for standard limit/market
status	String	filled, canceled, or expired, open orders are read from chain, never stored here
created_at	DateTime
closed_at	DateTime	When the order was fully filled, canceled, or expired

Indexes: (wallet, status), (market_id, status)

Written by: Indexer - inserted when an order is fully filled (FillEvent with remaining = 0) or canceled (cancel_order).

---

trades

Immutable record of every fill. Used for public trade feed, per-account trade history, and candle building.

Column	Type	Notes
id	Int	Primary key
market_id	Int	FK → markets.id
tx_hash	String	Unique — Solana transaction signature. Used for deduplication and linking to Solana Explorer
maker_account_id	Int?	FK → accounts.id. Nullable - counterparty may not be in our system
taker_account_id	Int?	FK → accounts.id. Nullable - counterparty may not be in our system
maker_wallet	String	Maker wallet (denormalised for public feed queries)
taker_wallet	String	Taker wallet (denormalised for public feed queries)
price	Decimal	Fill price
size	Decimal	Fill quantity
side	String	Aggressor side (long or short)
maker_fee	Decimal	Fee charged to maker
taker_fee	Decimal	Fee charged to taker
timestamp	DateTime	On-chain slot time

Indexes: (market_id, timestamp), maker_wallet, taker_wallet, maker_account_id, taker_account_id

Queries:

• Public feed: WHERE market_id = ? ORDER BY timestamp DESC
• Candles: group by time interval, one row per trade (no deduplication needed)
• Per-account history: WHERE maker_account_id = ? OR taker_account_id = ?

Written by: Indexer - inserted on each FillEvent from onLogs.

Candles: There is no separate candles table. OHLCV data for any interval is derived from trades.

---

vaults

Stores immutable vault identity. Live vault state (TVL, NAV per share, status, depositor count) is read from the Vault PDA on-chain.

Column	Type	Notes
address	String	Primary key - Vault PDA address on Solana. Used as FK target by vault_transactions and vault_stats
account_id	Int?	FK → accounts.id - the vault's trading account (type = vault)
description	String?	Free-text description set by the manager, stored here because arbitrary text is expensive on-chain
created_at	DateTime	Used to display vault age without an RPC call

All other config (name, owner, performance_fee, lockup_period, min_deposit, min_owner_share, strategy_type, TVL, NAV, status, depositor count) is read from the Vault PDA on-chain and cached in Redis under vault:{id}.

Written by: Indexer - inserts row on create_vault event. API - updates description when manager edits vault info.

---

account_transactions

Immutable ledger of every event that affects an account's balance. One row per event per account.

Column	Type	Notes
id	Int	Primary key
account_id	Int	FK → accounts.id
type	AccountTransactionType	See enum values below
asset	String	Asset symbol — USDC (default)
amount	Decimal	Amount (always positive; direction implied by type)
status	AccountTransactionStatus	pending, completed, or failed. Only relevant for deposit and withdrawal; all other types are always completed
tx_hash	String?	Solana transaction signature. Only set for deposit and withdrawal
created_at	DateTime

Indexes: account_id

AccountTransactionType enum:

• deposit — USDC deposited from external wallet into the platform
• withdrawal — USDC withdrawn from platform to external wallet
• transfer_in — funds received from another sub-account
• transfer_out — funds sent to another sub-account
• fee — trading fee charged on a fill
• funding — funding rate payment (positive amount = received, negative = paid — use signed amount or separate credit/debit rows)
• realized_pnl — PnL credited or debited on position close
• liquidation - liquidation penalty deducted

AccountTransactionStatus enum: pending, completed, failed

Written by: Indexer — inserted on deposit_margin, withdraw_margin, transfer_margin, FillEvent (fee), funding settlement, close_position, and liquidate_position on-chain events.

---

account_snapshots

Hourly snapshots of each account's equity and cumulative PnL. Used to render the account value and PnL charts over time on the Portfolio page.

Column	Type	Notes
id	Int	Primary key
account_id	Int	FK → accounts.id
equity	Decimal	Total account value at snapshot time - balance + unrealized PnL of open positions
realized_pnl	Decimal	Cumulative realized PnL up to this point
timestamp	DateTime	Snapshot time (floored to hour)

Unique constraint: (account_id, timestamp)

Indexes: (account_id, timestamp)

Written by: Indexer - upserted on the hourly stats job (reads open position PnL from chain).

---

leaderboard_stats

Precomputed leaderboard metrics per account. Recomputed periodically by a background job.

Column	Type	Notes
id	Int	Primary key
account_id	Int	FK → accounts.id
equity	Decimal	Current account value (from chain via Redis)
all_time_pnl	Decimal	equity - total_deposited - total return since account creation
realized_pnl	Decimal	SUM(positions_history.realized_pnl)
volume	Decimal	SUM(size * price) FROM trades
trade_count	Int	COUNT(*) FROM trades
funding_paid	Decimal	SUM(positions_history.funding_paid) - net funding paid/received
win_rate	Decimal	COUNT(realized_pnl > 0) / COUNT(*) FROM positions_history
sharpe_ratio	Decimal	Computed from account_snapshots.equity series
max_drawdown	Decimal	Peak-to-trough from account_snapshots.equity series
updated_at	DateTime	Last time the job ran for this account

Unique constraint: account_id

Indexes: all_time_pnl - default sort order.

Written by: Indexer leaderboard job - recomputes all rows every X minutes.

---

vault_transactions

Immutable record of every vault deposit and withdrawal. Captures NAV per share at transaction time for realised PnL calculation.

Column	Type	Notes
id	Int	Primary key
vault_address	String	FK → vaults.address
wallet	String	Depositor wallet address
type	String	deposit or withdrawal
amount	Decimal	USDC deposited or withdrawn
shares	Decimal	LP shares issued or burned
nav_per_share	Decimal	NAV per share at transaction time
fee	Decimal?	Performance fee charged on withdrawal. Null for deposits
pnl	Decimal?	Realised PnL at withdrawal: (nav_per_share - avg_entry_nav) * shares_burned. Null for deposits
created_at	DateTime

Indexes: vault_id, wallet

Written by: Indexer - inserted on vault_deposit and vault_withdraw events.

---

vault_stats

Hourly snapshots of vault-level metrics. Each row covers exactly one 1-hour bucket. Used to render TVL, share price, and PnL charts over time on the Vault Details page.

Column	Type	Notes
id	Int	Primary key
vault_address	String	FK → vaults.address
tvl	Decimal	Total AUM in USDC at snapshot time (point-in-time)
nav_per_share	Decimal	NAV per LP share at snapshot time (point-in-time)
apr	Decimal	Annualised return computed from the 30-day nav_per_share series: ((nav_now / nav_30d_ago) - 1) * (365 / 30). Precomputed by the Indexer hourly job so the API does not need to aggregate on each request. null if the vault is less than 1 day old
pnl	Decimal	PnL generated in this 1h bucket
timestamp	DateTime	Bucket start time (floored to hour)

Unique constraint: (vault_id, timestamp)

Indexes: (vault_id, timestamp)

Written by: Indexer - upserted on the hourly stats job (reads vault state from chain).

---

market_stats

Hourly snapshots of per-market aggregate metrics. Each row covers exactly one 1-hour bucket. open_interest is a point-in-time value (read the latest row or filter by timestamp). All other fields (volume, trade_count, liquidations) are per-bucket flows - sum N rows to get any time window (3h, 24h, 7D).

Column	Type	Notes
id	Int	Primary key
market_id	Int	FK → markets.id
open_interest	Decimal	Total base quantity of open positions at snapshot time (point-in-time)
volume	Decimal	USDC volume traded in this 1h bucket
trade_count	Int	Number of fills in this 1h bucket
liquidations	Int	Number of liquidations in this 1h bucket
timestamp	DateTime	Bucket start time (floored to hour)

Unique constraint: (market_id, timestamp)

Indexes: (market_id, timestamp)

Written by: Indexer - upserted on the hourly stats job (reads open positions from chain via getProgramAccounts).

---

faucet_claims

One row per faucet claim. Used to enforce the 24h cooldown per wallet. Devnet only.

Column	Type	Notes
id	Int	Primary key
wallet	String	Wallet that claimed
claimed_at	DateTime	Time of the claim

Indexes: wallet

Written by: API - inserted on each successful /faucet/claim request.

---