HyperliquidDailyMetricsDatabase

Documentation for eth_defi.hyperliquid.daily_metrics.HyperliquidDailyMetricsDatabase Python class.

class HyperliquidDailyMetricsDatabase

Bases: eth_defi.hyperliquid.vault_metrics_db.HyperliquidMetricsDatabaseBase

DuckDB database for storing Hyperliquid vault daily metrics.

Inherits shared metadata and lifecycle methods from HyperliquidMetricsDatabaseBase.

Stores daily share price time series and vault metadata. The share prices are computed using time-weighted returns from the Hyperliquid API portfolio history.

Example:

from pathlib import Path
from eth_defi.hyperliquid.daily_metrics import HyperliquidDailyMetricsDatabase

db = HyperliquidDailyMetricsDatabase(Path("/tmp/metrics.duckdb"))

# Query vault data
df = db.get_all_daily_prices()
print(df)

db.close()

Attributes summary

price_table	Name of the price table (set by subclass).
time_column	Name of the time column in the price table ("date" or "timestamp").

Methods summary

__init__(path)
close()	Close the database connection.
delete_vault_daily_prices(vault_address)	Delete all daily price records for a vault.
detect_broken_vaults()	Detect vaults with anomalous share price data.
fetch_vault_metadata(session, summary, timeout)	Fetch vault details and persist permission evidence before price checks.
get_all_daily_prices()	Get all daily price data across all vaults.
get_all_tracked_addresses()	Return all vault addresses that have any price data.
get_all_vault_metadata()	Get metadata for all vaults.
get_existing_dates(vault_address)	Get all dates with existing data for a vault.
get_latest_leader_fractions()	Get the latest leader_fraction for each vault.
get_leader_fraction_history(vault_address)	Get the recorded leader_fraction snapshots for a vault.
get_permission_observations()	Read independent permissions, including explicit unknown responses.
get_recently_tracked_addresses([within_days])	Return vault addresses with price data within the last within_days days.
get_vault_count()	Get the number of unique vaults with price data.
get_vault_daily_price_count(vault_address)	Get the number of daily price records for a vault.
get_vault_daily_prices(vault_address)	Get daily price data for a specific vault.
get_vault_last_date(vault_address)	Get the last date with price data for a vault.
mark_vaults_disappeared(known_addresses)	Set TVL to zero for vaults that disappeared from the API.
recompute_all_share_prices()	Recompute share prices for all vaults in the database.
recompute_vault_share_prices(vault_address)	Recompute share prices for a vault from stored tvl/pnl data.
record_bulk_closures(summaries, received_at)	Record explicit bulk-list closures even for filtered-out drained vaults.
record_permission(vault, info)	Persist a successful coherent response before any price processing.
record_permission_error(vault_address, error)	Record failed fetch/parse attempts separately from successful unknowns.
save()	Flush pending writes to disk.
tombstone_stale_vaults(known_api_addresses)	Write tombstone rows for vaults whose wind-down window has expired.
update_historical_daily_flows(vault_address, ...)	Backfill ledger flows on existing historical price observations.
update_vault_tvl_bulk(updates)	Bulk-update TVL and APR without mixing permission source responses.
upsert_daily_prices(rows[, cutoff_date])	Bulk upsert daily price rows for a vault.
upsert_price_batch(rows, columns)	Apply a deduplicated staging batch using hash joins, without ART indexes.
upsert_vault_metadata(vault_address, name, ...)	Insert or update a vault's metadata.

__init__(path)

Parameters

path (pathlib.Path) –

Return type

None

upsert_daily_prices(rows, cutoff_date=None)

Bulk upsert daily price rows for a vault.

Parameters

• rows (list[eth_defi.hyperliquid.daily_metrics.HyperliquidDailyPriceRow]) –

  List of HyperliquidDailyPriceRow items.

  Permission compatibility fields on new rows may accompany the latest source price. They never replace independent response observations, and matching-key updates preserve their original values and clock.

  The flow columns (daily_deposit_count etc.) should be None for dates outside the backfill window and 0 for dates with no activity within the backfill window. COALESCE preserves existing values when None is passed.

• cutoff_date (Optional[datetime.date]) – If provided, only store rows up to this date (inclusive). Used for incremental scanning / testing.

Return type

None

get_all_daily_prices()

Get all daily price data across all vaults.

Returns

DataFrame with all daily price records, ordered by vault then date.

Return type

pandas.DataFrame

get_vault_daily_prices(vault_address)

Get daily price data for a specific vault.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to query.

Returns

DataFrame with price records for this vault, ordered by date.

Return type

pandas.DataFrame

update_historical_daily_flows(vault_address, daily_flows, start_date, end_date)

Backfill ledger flows on existing historical price observations.

A normal scan can only attach flow data to portfolio observations that Hyperliquid still returns. Because the API downsamples old portfolio history, an old daily price row may remain in DuckDB even though a new vaultDetails response no longer contains that date. This method updates those existing rows directly from separately fetched userNonFundingLedgerUpdates data. Dates without a ledger event are explicitly stored as zero, which distinguishes a proven zero-flow day from an unknown historical day.

Existing price, NAV, PnL, and metadata values are never changed. The update is intentionally allowed to replace an old zero withdrawal, because older parser versions read Hyperliquid’s zero-valued usdc placeholder instead of netWithdrawnUsd.

Parameters

• vault_address (eth_typing.evm.HexAddress) – Lowercase or checksummed Hyperliquid vault address.

• daily_flows (dict[datetime.date, tuple[int, int, float, float]]) – Ledger events aggregated by UTC date as (deposit_count, withdrawal_count, deposit_usd, withdrawal_usd).

• start_date (datetime.date) – First existing daily observation to backfill, inclusive.

• end_date (datetime.date) – Last existing daily observation to backfill, inclusive.

Returns

Number of existing daily price rows updated.

Return type

int

get_existing_dates(vault_address)

Get all dates with existing data for a vault.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to query.

Returns

Set of dates that already have data in the database.

Return type

set[datetime.date]

get_vault_daily_price_count(vault_address)

Get the number of daily price records for a vault.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to query.

Returns

Number of daily price records.

Return type

int

get_vault_last_date(vault_address)

Get the last date with price data for a vault.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to query.

Returns

The latest date, or None if no data.

Return type

Optional[datetime.date]

get_leader_fraction_history(vault_address)

Get the recorded leader_fraction snapshots for a vault.

Returns only rows where leader_fraction was recorded (non-NULL). These are compatibility values attached to portfolio dates; they are not independent observations and cannot establish fresh capacity. Use the permission sidecar’s capacity_observed_at and coherent source inputs when checking the age of a leader-capital measurement.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to query (will be lowercased).

Returns

DataFrame with columns date and leader_fraction, ordered by date ascending. Empty if no snapshots recorded.

Return type

pandas.DataFrame

delete_vault_daily_prices(vault_address)

Delete all daily price records for a vault.

Used by the healing script to clear corrupted share prices before re-computing from fresh API data.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to delete.

Returns

Number of rows deleted.

Return type

int

recompute_vault_share_prices(vault_address)

Recompute share prices for a vault from stored tvl/pnl data.

Uses the stored tvl (total_assets), daily_pnl (pnl_update), and cumulative_pnl columns to reconstruct netflow_update, then feeds the data through _calculate_share_price() with the current chain-linked epoch reset logic.

This allows healing of share price data without re-fetching from the Hyperliquid API.

Parameters

vault_address (eth_typing.evm.HexAddress) – Vault address to recompute.

Returns

Dict with before/after statistics: {"rows": int, "old_sp_min": float, "old_sp_max": float, "new_sp_min": float, "new_sp_max": float, "epoch_resets": int, "changed_rows": int}

Return type

dict

recompute_all_share_prices()

Recompute share prices for all vaults in the database.

Iterates over every vault and calls recompute_vault_share_prices() for each.

Returns

Summary dict: {"total_vaults": int, "vaults_with_changes": int, "vaults_with_epoch_resets": int, "total_changed_rows": int, "per_vault": dict[str, dict]}

Return type

dict

detect_broken_vaults()

Detect vaults with anomalous share price data.

Checks for four classes of breakage:

1. Extreme daily returns (> 10,000%): typically epoch reset artefacts where share price jumped to/from 1.0

2. Share price at cap (>= 9,999): overflow from zero total_supply

3. Share price stuck at 1.0: share price identical for 5+ rows, suggesting failed epoch reset logic

4. Missing epoch_reset column: rows computed with old code that lacked the epoch_reset column (all NULL)

Returns

DataFrame with columns: vault_address, name, issue_type, affected_rows, example_value

Return type

pandas.DataFrame

close()

Close the database connection.

Return type

None

fetch_vault_metadata(session, summary, timeout)

Fetch vault details and persist permission evidence before price checks.

Both collectors use this path so failed parsing still retains a successful response receipt. Database failures propagate instead of being treated as a retryable provider failure. See the Hyperliquid info endpoint.

Parameters

• session (eth_defi.hyperliquid.session.HyperliquidSession) – Rate-limited API session.

• summary (eth_defi.hyperliquid.vault.VaultSummary) – Vault selected from the bulk catalogue.

• timeout (float) – HTTP request timeout in seconds.

Returns

Parsed metadata, or None after a recorded fetch/parse failure.

Return type

Optional[eth_defi.hyperliquid.vault.VaultInfo]

get_all_tracked_addresses()

Return all vault addresses that have any price data.

Returns

Set of lowercased vault addresses.

Return type

set[str]

get_all_vault_metadata()

Get metadata for all vaults.

Returns

DataFrame with one row per vault.

Return type

pandas.DataFrame

get_latest_leader_fractions()

Get the latest leader_fraction for each vault.

Reads the latest genuine permission snapshot per vault. If that response omits leader share, the previous value is not carried forwards. Inferred legacy snapshots are available in historical price exports instead. This catalogue lookup does not certify freshness; missing timing metadata does not invalidate a recorded historical share or policy cap.

Returns

Dict mapping lowercased vault address to leader_fraction.

Return type

dict[eth_typing.evm.HexAddress, float]

get_permission_observations()

Read independent permissions, including explicit unknown responses.

New source responses retain their receipt clock. Recovered legacy records are separately labelled when the price clock is inferred.

Returns

Observation and uncertainty-boundary frame.

Return type

pandas.DataFrame

get_recently_tracked_addresses(within_days=4)

Return vault addresses with price data within the last within_days days.

Uses a pure date cutoff so that whole-day semantics are preserved for the daily table (DATE column) and behave identically for the HF table (TIMESTAMP column — DuckDB casts DATE to midnight).

Parameters

within_days (int) – Number of days to look back from today.

Returns

Set of lowercased vault addresses.

Return type

set[str]

get_vault_count()

Get the number of unique vaults with price data.

Return type

int

mark_vaults_disappeared(known_addresses)

Set TVL to zero for vaults that disappeared from the API.

Also writes tombstone price rows so downstream consumers see a fresh row reflecting removal.

Parameters

known_addresses (set[eth_typing.evm.HexAddress]) – Lowercased vault addresses currently present in the bulk API.

Return type

None

record_bulk_closures(summaries, received_at)

Record explicit bulk-list closures even for filtered-out drained vaults.

Only isClosed=true supplies deny evidence. Bulk open/missing flags cannot establish permission or infer allowDeposits. Each record is a coherent partial source response with its own availability clock. Repeated identical bulk denials do not refresh this clock; append again after a later genuine response changes or clears the recorded closure.

Parameters

• summaries (list[eth_defi.hyperliquid.vault.VaultSummary]) – Newly fetched stats-data vault summaries.

• received_at (datetime.datetime) – Immediate UTC receipt bound after bulk fetch.

Returns

None.

Return type

None

record_permission(vault, info)

Persist a successful coherent response before any price processing.

Unknown flags are observations too and supersede previously known flags.

Parameters

• vault (eth_defi.hyperliquid.vault.HyperliquidVault) – Client retaining the raw response receipt.

• info (Optional[eth_defi.hyperliquid.vault.VaultInfo]) – Parsed source response.

Returns

None.

Return type

None

record_permission_error(vault_address, error)

Record failed fetch/parse attempts separately from successful unknowns.

A failed HTTP request does not supersede an earlier known permission.

Parameters

• vault_address (eth_typing.evm.HexAddress) – Attempted vault address.

• error (BaseException) – Fetch or response parsing failure, stored by type only.

Returns

None.

Return type

None

save()

Flush pending writes to disk.

Return type

None

tombstone_stale_vaults(known_api_addresses, wind_down_days=4)

Write tombstone rows for vaults whose wind-down window has expired.

A vault is eligible for tombstoning when:

1. It has existing price data in the database

2. It is NOT present in the current bulk API listing

3. Its most recent price row is older than wind_down_days

4. It does not already have a tombstone row

The tombstone carries forward the last known share_price and cumulative_pnl so return calculations are not distorted.

Parameters

• known_api_addresses (set[str]) – Vaults still in the API (never tombstoned).

• wind_down_days (int) – Days after last price row before tombstoning.

Returns

Number of tombstone rows written.

Return type

int

update_vault_tvl_bulk(updates)

Bulk-update TVL and APR without mixing permission source responses.

Only updates rows that already exist in vault_metadata.

Parameters

updates (list[tuple[float, bool | None, float | None, eth_typing.evm.HexAddress]]) – List of tuples (tvl, is_closed, apr, vault_address). The flag is retained in the calling contract but permission is recorded independently.

Return type

None

upsert_price_batch(rows, columns)

Apply a deduplicated staging batch using hash joins, without ART indexes.

Permission fields in prices are compatibility fields only. Price-only updates preserve the original write clock and cannot refresh permissions. Existing constrained databases require the backed-up recovery migration.

Parameters

• rows (list[tuple]) – Database tuples matching columns.

• columns (list[str]) – Explicit price column order.

Returns

None.

Return type

None

upsert_vault_metadata(vault_address, name, leader, description, is_closed, relationship_type, create_time, commission_rate, follower_count, tvl, apr, allow_deposits=None, flow_data_earliest_date=None)

Insert or update a vault’s metadata.

Parameters

• vault_address (eth_typing.evm.HexAddress) – Vault address (will be lowercased).

• flow_data_earliest_date (Optional[datetime.date]) – Earliest date for which daily deposit/withdrawal flow data has been backfilled. None means no flow data yet.

• name (str) –

• leader (eth_typing.evm.HexAddress) –

• description (Optional[str]) –

• is_closed (Optional[bool]) –

• relationship_type (str) –

• create_time (Optional[datetime.datetime]) –

• commission_rate (Optional[float]) –

• follower_count (Optional[int]) –

• tvl (Optional[float]) –

• apr (Optional[float]) –

• allow_deposits (Optional[bool]) –

Return type

None