Sai database queries and schema

Use this reference for the sai-keeper Postgres schema, migrations, tables, columns, SQL types, units, API surfacing, and aggregate freshness. It is a read/query snapshot. For direct Tailscale access, primary/replica routing, or write operations, use the private agent skill sai-db rather than treating this reference as the live operator runbook.

How to use this reference

1. Look up tables and columns: See schema reference for a domain-by-domain breakdown of Oracle, Perp, LP, Referral, and Stats data.
2. Understand data patterns: See database conventions for coordinates, units, and partitioning.
3. Find where data surfaces: See API surfacing for mappings between database tables and API endpoints.

Direct database access (psql)

Use direct SQL when validating endpoint behavior, unit semantics, and aggregate freshness.

Before connecting to Sai Keeper Postgres, verify that the workstation is on the company private network. The database host is reachable through Tailscale/VPN.

tailscale status

If Tailscale is not active or the expected private nodes are unavailable, stop and report that direct database access is blocked by network connectivity.

For operator DB checks on the Tailscale network, prefer local psql database names over repository runtime environment variables.

Credentials are expected to already work via the workstation’s local Postgres setup. Do not read or print password files such as ~/.pgpass, and do not search for credentials or dump secret environment values. If direct psql connection fails, report the connectivity or database selection issue to the user (for testnet vs mainnet, see private skill sai-db on ambient PGPASSWORD override).

SAI_DB="sai_keeper" # or "sai_keeper_2" depending on time

• Local default DB name: sai_keeper (override as needed) OR sai_keeper_2 if specified by user.
• Preferred connect order:
  • psql -d sai_keeper_2 -X -P pager=off -c "SELECT current_database(), current_user;"
  • psql -d sai_keeper -X -P pager=off -c "SELECT current_database(), current_user;"
• Basic connect: psql -d sai_keeper
• Run one query inline: psql -d sai_keeper -c "SELECT 1 AS ok;"
• List available databases when ambient psql connects to the wrong database: psql -X -P pager=off -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;"

Useful flags:

• -X: do not read ~/.psqlrc (reproducible output)
• -P pager=off: disable pager for long results
• -A -t -F ',': script-friendly unaligned output
• -c "<sql>": execute a SQL command non-interactively

Suggested debugging workflow:

1. Verify row freshness first (MAX(ts), COUNT(*) WHERE ts=current_date).
2. Compare API metric source table to a live recompute query.
3. Break totals by collateral_id/market_id to locate outliers quickly.

Connections to APIs

This database powers two primary APIs. For usage instructions and query patterns, see their respective skills:

• sai-graphql.md: Most tables are exposed via GraphQL queries and subscriptions.
• sai-rest.md: High-level metrics (volume, OI, TVL) are served via REST primarily from stats_* table queries (with stats_cache as legacy/auxiliary context).

Quick start

Use schema reference to see all available tables with explanations for each column.

Data source

The sai-keeper database schema is the source of truth for the Sai exchange’s indexed data. The schema is defined by SQL migrations in the sai-keeper repository.

• Schema Location: $HOME/ki/sai-keeper/models/migrations/
• Routines/Stored Procs: $HOME/ki/sai-keeper/models/routines/ (maintains stats/aggregates)

High-value SQL snippets

1) Open positions vs current-day OI snapshot

SELECT
  (SELECT COUNT(*) FROM perp_trade WHERE is_open = true AND trade_type = 'trade') AS open_positions,
  (SELECT COALESCE(SUM(oi_long_usd + oi_short_usd), 0) FROM stats_perp_oi_daily WHERE ts = current_date) AS oi_today_usd;

2) Check daily OI freshness

SELECT
  current_date AS today,
  MAX(ts) AS latest_oi_day,
  COUNT(*) FILTER (WHERE ts = current_date) AS rows_for_today
FROM stats_perp_oi_daily;

3) Compare “today only” vs latest available OI

SELECT
  COALESCE((
    SELECT SUM(oi_long_usd + oi_short_usd)
    FROM stats_perp_oi_daily
    WHERE ts = current_date
  ), 0) AS oi_today_usd,
  COALESCE((
    SELECT SUM(oi_long_usd + oi_short_usd)
    FROM stats_perp_oi_daily
    WHERE ts = (SELECT MAX(ts) FROM stats_perp_oi_daily WHERE ts <= current_date)
  ), 0) AS oi_latest_available_usd;

4) OI by collateral on latest day

WITH d AS (
  SELECT MAX(ts) AS ts
  FROM stats_perp_oi_daily
  WHERE ts <= current_date
)
SELECT
  s.ts,
  s.collateral_id,
  ot.base AS collateral_symbol,
  SUM(s.oi_long_usd + s.oi_short_usd) AS oi_usd
FROM stats_perp_oi_daily s
JOIN d ON s.ts = d.ts
LEFT JOIN oracle_token ot ON ot.id = s.collateral_id
GROUP BY s.ts, s.collateral_id, ot.base
ORDER BY oi_usd DESC;

5) Top users by cumulative volume

SELECT
  user_address,
  SUM(volume_usd_long + volume_usd_short) AS cumulative_volume_usd
FROM stats_perp_by_user
GROUP BY user_address
ORDER BY cumulative_volume_usd DESC
LIMIT 50;

6) Top users by 30-day volume

SELECT
  user_address,
  SUM(volume_usd_long + volume_usd_short) AS volume_30d_usd
FROM stats_perp_by_user
WHERE ts >= current_date - INTERVAL '30 days'
GROUP BY user_address
ORDER BY volume_30d_usd DESC
LIMIT 50;

Known pitfalls and interpretation notes

• open_positions and open_interest can diverge:
  • open_positions is live from perp_trade
  • open_interest in REST stats comes from daily snapshots (stats_perp_oi_daily)
  • If current day rows are missing, open_interest can read as 0 while open positions are non-zero.
• Unit semantics:
  • Base units are bigint (collateral_amount, tvl, etc).
  • USD metrics require conversion: base_units / 1e6 * price_usd (this codebase currently assumes 6 decimals in several routines).
• OI spikes can come from snapshot inclusion semantics:
  • Daily OI procedure logic can include positions opened and closed on the same day.
  • That inflates daily OI vs strict end-of-day “still open” interpretation.
• perp_borrowing OI and stats_perp_oi_daily OI are related but not guaranteed identical at any instant; they are populated by different paths and timings.

Playbook for user questions

• ”What fields are in the perp_trade table?” -> Consult schema.md#perp_trade.
• ”How is volume calculated?” -> Consult conventions.md#units and surfacing.md#rest-api (derived from stats_perp_by_user).
• ”What fields are in the stats_perp_by_user table?” -> Consult schema.md#stats_perp_by_user. Includes volume, PnL, and trade counts per user/market/collateral.
• ”Where are liquidations stored?” -> Liquidations are records in perp_trade_history with trade_change_type = 'position_liquidated'. See schema.md#perp_trade_history.

Schema reference

This document provides a detailed breakdown of the tables and types in the sai-keeper database.

Common (001_common.sql)

Tables

• schema_version: Tracks the current migration version.
  • version: bigint (PK)
• historical_sync: Tracks blocks synced during historical indexing.
  • id: bigserial (PK)
  • batch_start_block: bigint
  • batch_end_block: bigint
  • created_at: timestamptz
• exchange_state: Current operating state of the exchange.
  • state: exchange_status (active, paused, close_only)
  • last_updated_block: bigint
• exchange_config: Protocol-wide configuration parameters.
  • max_pnl, max_sl, liquidation_threshold: float8
  • fee_tiers, price_impact: jsonb
  • last_updated_block: bigint
• block: Maps block numbers to timestamps.
  • block: bigint (PK)
  • block_ts: timestamptz
• tx: Maps transaction hashes to blocks and indexes.
  • block: bigint, tx_index: bigint (Composite PK)
  • tx_hash: text, evm_hash: text

Types

• string_pair: (key1 text, key2 text)
• exchange_status: active, paused, close_only

Oracle (002_oracle.sql)

Tables

• oracle_token: Metadata for assets tracked by the oracle.
  • id: bigint (PK)
  • base: text, permission_group: bigint
• oracle_token_info: Visual/display metadata for tokens.
  • oracle_token_id: bigint (PK, FK)
  • logo_url: text, symbol: text, trading_view_symbol: text
• oracle_token_price_source: External sources for token prices.
  • oracle_token_id: bigint, price_source: text (Composite PK)
  • symbol: text
• oracle_price: Latest market price for each token.
  • oracle_token_id: bigint (PK)
  • price_usd: float8, last_updated_block: bigint
• oracle_price_history: Time-series of oracle prices.
  • oracle_token_id: bigint, block: bigint (Composite PK)
  • price_usd: float8
  • Partitioned by block range.

Perpetuals (003_perp.sql)

Tables

• perp_collateral: Supported collateral assets.
  • oracle_token_id: bigint (PK), is_active: boolean
• perp_market: Trading pairs (e.g. BTC/USDC).
  • id: bigint (PK)
  • base_token_id, quote_token_id: bigint
  • spread_p, max_leverage: float8
  • perp_market_group_id, perp_fee_id: bigint
• perp_market_group: Groups of markets (e.g. Majors, Altcoins).
  • id: bigint (PK)
  • name: text, min_leverage, max_leverage: float8
• perp_market_group_vault: Maps market groups to specific collateral vaults.
  • perp_market_group_id, collateral_id: bigint (Composite PK)
  • vault: text (contract address)
• perp_market_price: Latest price for a perp market.
  • perp_market_id: bigint (PK)
  • price: float8, last_updated_block: bigint
• perp_market_visibility: Toggles market visibility in frontend.
  • perp_market_id: bigint (PK, FK), visible: boolean
• perp_borrowing_group: Borrowing rate metrics for a group and collateral.
  • group_id, collateral_id: bigint (Composite PK)
  • acc_fees_long, acc_fees_short: float8
  • oi_long, oi_short, oi_max: bigint
  • fee_per_block, fee_exponent: float8
  • acc_last_updated_block, last_updated_block, last_updated_tx_index, last_updated_event_index: bigint
• perp_borrowing: Borrowing rate metrics for a specific market and collateral.
  • Same fields as perp_borrowing_group plus perp_market_id, borrowing_group_id.
• perp_trade: Current state of open and pending trades.
  • trader: text, id: bigint (Composite PK)
  • trade_type: perp_trade_type, perp_market_id: bigint, collateral_id: bigint
  • collateral_amount, open_collateral_amount: bigint
  • is_long, is_open: boolean, leverage, open_price, close_price: float8
  • tp, sl: float8, referral_code: text
  • open_block, close_block: bigint, last_updated_*: bigint
• perp_trade_history: Event log of trade changes (opens, closes, liquidations).
  • id: bigserial (PK)
  • trader: text, trade_id: bigint, trade_change_type: perp_trade_change_type
  • realized_pnl_pct: float8, block, tx_index, event_index: bigint
• perp_trade_state: Derived trade metrics (real-time PnL, liq price).
  • trader: text, id: bigint (Composite PK)
  • pnl_pct, pnl_collateral, borrowing_fee_*, closing_fee_*, pnl_collateral_after_fees: float8
  • remaining_collateral_after_fees, liquidation_price: float8
• perp_trade_trigger_history: History of limit/stop order executions.
  • id: bigserial (PK)
  • trader: text, trade_id: bigint, executor: text
  • trigger_type: perp_trade_trigger_type
  • trade_trigger_price, market_price: float8
  • trigger_execution_block, trigger_executed_block: bigint, is_success: boolean, error_msg: text
• perp_fee: Fee configuration.
  • id: bigint (PK), open_fee_p, close_fee_p, trigger_order_fee_p: float8, min_position_size_usd: bigint
• perp_fee_charged_history: Detailed breakdown of fees paid per trade event.
  • id: bigserial (PK)
  • trader: text, trade_id: bigint, fee_type: perp_fee_type
  • total_fee_charged, vault_fee, trigger_fee, gov_fee, net_gov_fee, referrer_allocation: bigint
  • fee_multiplier: float8, catalyst_address: text, collateral_left, bad_debt: bigint
  • open_fee_component, trigger_fee_component, triggerer_reward, final_closing_fee: bigint
  • block, tx_index, event_index: bigint
• perp_open_interest_history: Time-series of OI per market/group.
  • id: bigint, id_type: perp_id_type, collateral_id: bigint, block, tx_index, event_index (Composite PK)
  • oi_long, oi_short: bigint
• perp_pending_acc_fees_history: Time-series of accumulated fees.
  • id: bigint, id_type: perp_id_type, collateral_id: bigint, block, tx_index, event_index (Composite PK)
  • acc_fee_long, acc_fee_short: float8
• perp_balance_history: History of contract balances.
  • block: bigint, denom: text (Composite PK), balance: bigint
• perp_blacklist: Blacklisted traders.
  • trader: text (PK), blacklisted_ts: timestamptz, reason: text

Types

• perp_id_type: market, group
• perp_trade_type: trade, stop, limit
• perp_trade_change_type: position_opened, limit_order_created, stop_order_created, order_triggered, position_closed_tp, position_closed_sl, position_liquidated, position_closed_user, order_closed_user, tp_updated, sl_updated, leverage_updated
• perp_trade_trigger_type: limit_open, stop_open, tp_close, sl_close, liq_close
• perp_fee_type: opening, closing

LP (004_lp.sql)

Tables

• lp_vault: Current state of SLP vaults.
  • address: text (PK)
  • shares_denom, shares_erc20, collateral_denom, collateral_erc20: text
  • collateral_id, available_assets, tvl, net_profit, rewards, closed_pnl, liabilities, current_epoch_positive_open_pnl, current_epoch, epoch_start: bigint
  • share_price: float8
• lp_vault_history: Historical snapshot of vault metrics.
  • vault: text, block: bigint (Composite PK)
  • Same fields as lp_vault.
• lp_deposit_history: Log of deposit and withdrawal events.
  • id: bigserial (PK)
  • action: lp_action_type, depositor: text, vault: text
  • amount, shares: bigint, block, tx_index, event_index: bigint
• lp_vault_assets_received_history: External asset transfers to vaults (fees, etc).
  • vault: text, block: bigint, tx_index: bigint, event_index: bigint (Composite PK)
  • sender: text, denom: text, amount, assets_less_deplete: bigint
• lp_vault_pnl_history: History of PnL changes and distribution to LPs.
  • vault: text, block: bigint, tx_index: bigint, event_index: bigint (Composite PK)
  • current_epoch, prev_positive_open_pnl, new_positive_open_pnl, current_epoch_positive_open_pnl: bigint
  • acc_pnl_per_token_used: float8
• lp_withdraw_request: Pending user withdrawal requests.
  • depositor: text, vault: text, unlock_epoch: bigint (Composite PK)
  • shares: bigint, auto_redeem: boolean

Types

• lp_action_type: deposit, create_withdraw_request, cancel_withdraw_request, redeem

Referral (005_referral.sql)

Tables

• referral_code: Registered referral codes.
  • code: text (PK), referrer: text, description: text, created_block: bigint, is_active: boolean
• referral_code_creation_history: Log of code creation.
  • id: bigserial (PK), code, referrer, description: text, block, tx_index, event_index: bigint
• referral_redeem_history: Log of users redeeming codes.
  • id: bigserial (PK), trader, code, referrer: text, block, tx_index, event_index: bigint
• referral_claim_history: History of referral commission claims.
  • id: bigserial (PK), referrer: text, amount: bigint, denom: text, coin_breakdown: jsonb, chain: text, block, tx_index, event_index: bigint

Stats (006_stats.sql)

table stats_block_ranges

Maps time buckets to block ranges.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_block_ranges"

• granularity (stats_granularity): Bucket size (1m, 1h, 1d, etc); part of composite PK.
• ts (timestamptz): Bucket timestamp; part of composite PK.
• start_block (bigint): First chain block included in the bucket.
• end_block (bigint): Last chain block included in the bucket.

table stats_oracle_price

Aggregated oracle prices over time.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_oracle_price"

• granularity (stats_granularity): Aggregation interval; part of composite PK.
• ts (timestamptz): Bucket timestamp; part of composite PK.
• oracle_token_id (bigint): Oracle token ID; part of composite PK.
• avg_price_usd (float8): Average token price in USD for the bucket.
• min_price_usd (float8): Minimum token price in USD for the bucket.
• max_price_usd (float8): Maximum token price in USD for the bucket.
• open_price_usd (float8): First observed token price in USD for the bucket.
• close_price_usd (float8): Last observed token price in USD for the bucket.

table stats_cache

Singleton cache for global exchange metrics.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_cache"

• id (integer): Singleton row identifier (CHECK (id = 1)).
• trading_volume_24h (numeric): Rolling 24h trading volume in USD.
• trading_volume_all_time (numeric): Cumulative trading volume in USD.
• total_trades_24h (bigint): Rolling 24h trade count.
• total_trades_all_time (bigint): Cumulative trade count.
• open_interest (numeric): Current open interest in USD.
• total_users_24h (bigint): Distinct active users in last 24h.
• total_users_all_time (bigint): Cumulative distinct users.
• total_open_positions (bigint): Count of currently open positions.
• tvl (numeric): Total value locked in USD.
• accrued_trading_fees_24h (numeric): Rolling 24h accrued trading fees in USD.
• accrued_trading_fees_all_time (numeric): Cumulative accrued trading fees in USD.
• last_updated (timestamptz): Last cache refresh timestamp.

table stats_perp_by_user

User-level trading stats (daily).

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_by_user"

• ts (date): Snapshot date.
• user_address (text): User wallet address.
• market_id (bigint): Perp market ID.
• collateral_id (bigint): Collateral token ID.
• volume_usd_long (numeric): Long-side volume in USD.
• volume_usd_short (numeric): Short-side volume in USD.
• positive_pnl_usd (numeric): Cumulative profits (positive PnL) in USD.
• negative_pnl_usd (numeric): Cumulative losses (negative PnL) in USD.
• trades_count_long (bigint): Total count of long trades.
• trades_count_short (bigint): Total count of short trades.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_perp_liq_daily

Daily liquidation volume and counts per market.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_liq_daily"

• ts (date): Snapshot date; part of composite PK.
• market_id (bigint): Perp market ID; part of composite PK.
• collateral_id (bigint): Collateral token ID; part of composite PK.
• trades_count_long (bigint): Number of liquidated long positions.
• trades_count_short (bigint): Number of liquidated short positions.
• volume_long_usd (numeric): Liquidated long notional volume in USD.
• volume_short_usd (numeric): Liquidated short notional volume in USD.
• total_fee_charged_usd (numeric): Total liquidation-related fees charged in USD.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_perp_oi_daily

Daily Open Interest snapshots per market.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_oi_daily"

• ts (date): Snapshot date; part of composite PK.
• market_id (bigint): Perp market ID; part of composite PK.
• collateral_id (bigint): Collateral token ID; part of composite PK.
• oi_long_usd (numeric): Long-side open interest in USD.
• oi_short_usd (numeric): Short-side open interest in USD.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_slp_deposit_by_user

User LP stats (daily).

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_slp_deposit_by_user"

• ts (date): Snapshot date; part of composite PK.
• user_address (text): User wallet address; part of composite PK.
• vault (text): SLP vault address; part of composite PK.
• amount_deposited_usd (numeric): USD value deposited during this day.
• amount_withdrawn_usd (numeric): USD value withdrawn during this day.
• cumulative_amount_deposited_usd (numeric): Running cumulative deposited USD for user/vault.
• cumulative_amount_withdrawn_usd (numeric): Running cumulative withdrawn USD for user/vault.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_perp_sl_tp_daily

Daily SL/TP execution stats.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_sl_tp_daily"

• ts (date): Snapshot date; part of composite PK.
• market_id (bigint): Perp market ID; part of composite PK.
• collateral_id (bigint): Collateral token ID; part of composite PK.
• sl_trades_count_long (bigint): Number of long trades closed via stop-loss.
• sl_trades_count_short (bigint): Number of short trades closed via stop-loss.
• sl_losses_prevented_long_usd (numeric): Estimated long-side losses prevented by stop-loss in USD.
• sl_losses_prevented_short_usd (numeric): Estimated short-side losses prevented by stop-loss in USD.
• tp_trades_count_long (bigint): Number of long trades closed via take-profit.
• tp_trades_count_short (bigint): Number of short trades closed via take-profit.
• tp_profit_realized_long_usd (numeric): Realized long-side take-profit in USD.
• tp_profit_realized_short_usd (numeric): Realized short-side take-profit in USD.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_perp_fee_daily

Daily fee breakdown per market.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_fee_daily"

• ts (date): Snapshot date; part of composite PK.
• market_id (bigint): Perp market ID; part of composite PK.
• collateral_id (bigint): Collateral token ID; part of composite PK.
• opening_fee_count (integer): Count of opening-fee events.
• opening_fee_total (bigint): Total opening fees in base collateral units.
• opening_fee_total_usd (float8): Total opening fees in USD.
• opening_vault_fee (bigint): Opening-fee portion allocated to vaults (base units).
• opening_vault_fee_usd (float8): Opening-fee portion allocated to vaults (USD).
• opening_trigger_fee (bigint): Opening trigger-order fees (base units).
• opening_trigger_fee_usd (float8): Opening trigger-order fees (USD).
• opening_gov_fee (bigint): Opening-fee protocol/governance allocation (base units).
• opening_gov_fee_usd (float8): Opening-fee protocol/governance allocation (USD).
• opening_referrer_fee (bigint): Opening-fee referrer allocation (base units).
• opening_referrer_fee_usd (float8): Opening-fee referrer allocation (USD).
• closing_fee_count (integer): Count of closing-fee events.
• closing_fee_total (bigint): Total closing fees in base collateral units.
• closing_fee_total_usd (float8): Total closing fees in USD.
• closing_vault_fee (bigint): Closing-fee portion allocated to vaults (base units).
• closing_vault_fee_usd (float8): Closing-fee portion allocated to vaults (USD).
• closing_trigger_fee (bigint): Closing trigger-order fees (base units).
• closing_trigger_fee_usd (float8): Closing trigger-order fees (USD).
• closing_gov_fee (bigint): Closing-fee protocol/governance allocation (base units).
• closing_gov_fee_usd (float8): Closing-fee protocol/governance allocation (USD).
• closing_referrer_fee (bigint): Closing-fee referrer allocation (base units).
• closing_referrer_fee_usd (float8): Closing-fee referrer allocation (USD).
• total_bad_debt (bigint): Aggregate bad debt amount in base collateral units.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_referrals_daily

Daily referrer earnings and volume.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_referrals_daily"

• ts (date): Snapshot date; part of composite PK.
• referrer (text): Referrer address; part of composite PK.
• trades_count (bigint): Number of referred trades for the day.
• earnings_usd (float8): Referrer earnings in USD for the day.
• volume_usd (float8): Referred trading volume in USD for the day.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_slp_vault

Aggregated SLP vault metrics (APY, TVL, volume).

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_slp_vault"

• ts (timestamptz): Bucket timestamp; part of composite PK.
• granularity (stats_granularity): Bucket size; part of composite PK.
• vault (text): SLP vault address; part of composite PK.
• share_price (float8): Vault share price at snapshot time.
• apy (float8): Annual percentage yield estimate.
• deposits (float8): Deposited collateral amount in token units for the bucket.
• deposits_usd (float8): Deposited amount converted to USD.
• withdrawals (float8): Withdrawn collateral amount in token units for the bucket.
• withdrawals_usd (float8): Withdrawn amount converted to USD.
• volume (float8): Total vault activity volume in token units.
• volume_usd (float8): Total vault activity volume in USD.
• users (bigint): Count of unique users active in the bucket.
• new_users (bigint): Count of first-time users in the bucket.
• assets_usd (float8): Vault assets under management in USD.
• closed_pnl (float8): Closed PnL attributable to the vault in USD.
• liabilities (float8): Vault liabilities in USD.
• updated_at (timestamptz): Timestamp of the last record refresh.

table stats_perp_trade_snapshots

Snapshots of active trades for PnL tracking.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_perp_trade_snapshots"

• granularity (stats_granularity): Snapshot interval; part of composite PK.
• ts (timestamptz): Snapshot timestamp; part of composite PK.
• trader (text): Trader address; part of composite PK.
• trade_id (bigint): Trade ID scoped to trader; part of composite PK.
• collateral_amount (bigint): Trade collateral amount in base units.
• leverage (float8): Position leverage at snapshot time.
• pending_pnl_usd (float8): Unrealized (pending) PnL in USD at snapshot time.
• realized_pnl_usd (float8): Realized PnL in USD attributable in the interval.
• opened_in_period (boolean): True if position opened during this interval.
• closed_in_period (boolean): True if position closed during this interval.

table stats_user_portfolio

Time-series of user PnL and volume.

psql -d sai_keeper_2 -X -P pager=off -c "\d stats_user_portfolio"

• granularity (stats_granularity): Aggregation interval; part of composite PK.
• ts (timestamptz): Bucket timestamp; part of composite PK.
• user_address (text): User wallet address; part of composite PK.
• realized_pnl_usd (float8): Realized PnL in USD in this bucket.
• realized_pnl_usd_cumulative (float8): Running cumulative realized PnL in USD.
• pending_pnl_usd_cumulative (float8): Running cumulative unrealized (pending) PnL in USD.
• volume_usd (float8): Trading volume in USD in this bucket.
• volume_usd_cumulative (float8): Running cumulative trading volume in USD.
• trades_count (bigint): Number of trades in this bucket.
• trades_count_cumulative (bigint): Running cumulative trade count.

table leaderboard

Global PnL and volume leaderboard.

psql -d sai_keeper_2 -X -P pager=off -c "\d leaderboard"

• trader (text): Trader address (PK).
• volume_usd (numeric): Cumulative trading volume in USD.
• realized_pnl_usd (numeric): Cumulative realized PnL in USD.
• collateral_used_usd (numeric): Cumulative collateral usage in USD.
• last_tracked_block (bigint): Last block included in leaderboard refresh.

table volume_leaderboard

Volume-only leaderboard.

psql -d sai_keeper_2 -X -P pager=off -c "\d volume_leaderboard"

• trader (text): Trader address (PK).
• volume_usd (numeric): Cumulative trading volume in USD for ranking.
• last_tracked_block (bigint): Last block included in refresh.
• threshold_block (bigint): Optional lower-bound block used for scoped volume windows.

table statsig_last_tracked_block

Tracks progress of stats refreshers.

psql -d sai_keeper_2 -X -P pager=off -c "\d statsig_last_tracked_block"

• event_type (text): Stats event/process identifier (PK).
• last_tracked_block (bigint): Latest processed block for that event type.

Types

• stats_granularity: 1s, 1m, 5m, 15m, 1h, 4h, 6h, 12h, 1d, 1w, 1mo

Database conventions

This document explains common patterns and semantics used across the sai-keeper database tables.

Event coordinates

Most historical tables (ending in _history) use a set of coordinates to uniquely identify an on-chain event:

• block: The block height.
• tx_index: The index of the transaction within the block.
• event_index: The index of the event within the transaction.

Together, (block, tx_index, event_index) provide a stable order and ensure uniqueness for indexed events.

Timestamps and dates

• timestamptz: Used for real-world time (e.g., block_ts in the block table).
• date: Used in stats_* tables for daily buckets (e.g., ts in stats_perp_by_user).
• block: Often used as a proxy for time in _history tables.

History and state tables

• State Tables (e.g., perp_trade, lp_vault): Store the current state of an entity. They are updated in place as new events arrive.
• History Tables (e.g., perp_trade_history, lp_deposit_history): Store an immutable log of changes. Each row represents a specific action or event.

Units and decimals

• Base Units (Int/Bigint): Fields like collateral_amount, tvl, and available_assets are stored in base units (e.g., 6 decimals for USDC, 18 for some EVM tokens).
• USD Values (Float8/Numeric): Fields like price_usd, volume_usd, and realized_pnl_usd are human-readable floats or high-precision numerics already scaled to USD.
• Percentages (Float8): Fields like pnl_pct or spread_p are stored as decimals (e.g., 0.01 for 1%).

Partitioning

The oracle_price_history table is partitioned by block range using the pg_partman extension. This ensures that queries over historical prices remain performant as the dataset grows.

Migrations for partitioned tables usually include a call to partman.create_parent:

SELECT partman.create_parent(
   p_parent_table => 'public.oracle_price_history',
   p_control => 'block',
   p_type => 'native',
   p_interval => '100000',
   ...
);

Identifiers

• IDs: Many protocol entities use bigint IDs assigned by the smart contracts (e.g., perp_market_id, trade_id).
• Addresses: Trader and vault addresses are stored as text (e.g., Bech32 nibi1... or hex 0x...).

API surfacing

This document maps database tables to their respective API exposures in GraphQL and REST.

GraphQL surfacing

sai-graphql.md covers API usage. Most DB tables map to a GraphQL type, but query fields are grouped below the root domain objects perp, lp, oracle, and referral.

Note: For live updates (Subscriptions), GraphQL watchers monitor these tables and push updates when rows change or are added.

REST API surfacing

sai-rest.md covers high-level metrics. In current code paths, /dexpal/v1/stats and /dexpal/v1/metrics are derived primarily from stats_* table queries in the API service layer.

Data refreshers

Aggregated tables in the stats domain are maintained by stored procedures in models/routines/. These procs run periodically (e.g., via statsig) to pull data from “State” and “History” tables into the “Stats” tables.

Key Procs:

• proc_update_stats_perp_by_user: Aggregates trades into daily user stats.
• update_stats_perp_oi_daily: Computes daily open interest snapshots in USD.
• proc_update_stats_perp_fee_daily: Aggregates fee history into daily USD fees.
• proc_update_volume_leaderboard: Maintains the leaderboard from trade history.
• func_refresh_stats_cache: Updates stats_cache (legacy/auxiliary path).

See also

• sai-graphql.md
• sai-rest.md