Skip to content

Data and Storage

Savvas Leousis edited this page Sep 26, 2026 · 2 revisions

Data and Storage

Market data goes into the lake (a DuckDB file, optionally with bars in Parquet files). Trading records go into state (a SQLite file). Both live under data/.

flowchart LR
  E[EODHD] --> P[IngestPipeline]
  Y[Yahoo Finance] --> P
  D[DefiLlama] --> P
  P --> Q{Quality check}
  Q -->|good rows, upsert| L[(lake.duckdb<br/>bars, statements,<br/>metadata, macro, TVL)]
  Q -->|bad rows| QB[quarantined_bars]
  P -->|run row + quality_json| R[ingest_runs]
  L --> X[lab, backtest, tick]
  X --> S[(state.sqlite<br/>strategies, orders,<br/>portfolios, jobs, ...)]
Loading

Sources

Pick one with --source eodhd|yahoo on the ingest commands. Default: eodhd (defillama for tvl).

Source Needs Gives
EODHD EODHD_API_KEY Daily and intraday (1m, 5m, 1h) bars, fundamentals, metadata, macro. Free tier: daily prices only, about 1 year.
Yahoo nothing Daily and intraday (1m to 1h) bars, basic profiles. No statements.
DefiLlama nothing DeFi total value locked (TVL) per chain, for stonks ingest tvl.

Ingest commands

uv run stonks ingest prices --tickers AAPL.US,BTC-USD.CC --since 2025-01-01
uv run stonks ingest prices --exchange US                 # every ticker on an exchange
uv run stonks ingest fundamentals --tickers AAPL.US        # EODHD paid tier
uv run stonks ingest metadata --tickers AAPL.US            # profile, splits, dividends, ...
uv run stonks ingest intraday --tickers AAPL.US --interval 5m
uv run stonks ingest macro --countries USA,DEU
uv run stonks ingest tvl --chains ethereum,solana
uv run stonks ingest aggregate --tickers AAPL.US --from 1h --to 4h
uv run stonks ingest all-intervals --tickers AAPL.US
uv run stonks ingest exchanges

How ingest behaves

  • Idempotent (safe to repeat): rows are upserted by primary key.
  • Soft-fail: one ticker failing is logged and skipped. The ingest_runs row counts tickers_ok and tickers_failed.
  • Quality checks: every price batch is checked. Impossible rows (missing or non-positive prices, high below low, close outside the range, duplicates, one-bar spikes that revert) go to quarantined_bars. Findings that can be real (a big move, a stale or flat series, zero-volume streaks) are warnings only. Each run stores a summary in quality_json, and bad runs raise an alert.
  • No blanking: for statements, a missing value never overwrites a stored one.
  • Vendor-neutral: columns use domain names (change_pct, not the vendor key). Every source fills the same tables.

A fallback source (retry a failed ticker on a second vendor) exists in the pipeline, but is not wired to config yet.

Bars: DuckDB or Parquet

By default bars live in the lake's bars table. Set [lake.bars] backend = "parquet" to keep them as partitioned Parquet files under <lake dir>/bars, which other processes can read while stonks serve holds the lake. Move existing bars with:

uv run python -m stonks.store.bars_migrate --to parquet

Stop stonks serve first. The copy is checked per ticker before the lake switches.

Lake tables (DuckDB)

Table Holds
instruments One row per ticker: asset class, exchange, currency, sector, identifiers
bars OHLCV at any interval, key (ticker, timestamp, interval)
prices Read-only view of daily bars
income_statement, balance_sheet, cash_flow_statement Statements, key (ticker, period_end, frequency), with filing_date
dividends, stock_splits, shares_outstanding, market_cap_history Corporate actions and size
news, news_sentiment, analyst_estimates, analyst_ratings, insider_transactions, employee_count, segmentation Equity metadata
crypto_profiles, bond_profiles, bond_yield_history, commodity_contracts Non-equity profiles
macro_indicators Country series, key (country_iso, indicator, observation_date)
defi_tvl TVL per chain and day
lake_settings Lake-level settings, such as the bar backend
quarantined_bars Rejected bar rows with reason codes
ingest_runs One row per ingest run, with quality_json

State tables (SQLite)

Table Holds
strategies, survival_reports, status_changes Registered strategies, lab results, audited status changes
lab_runs, lab_trials Every lab run and every tuning trial
tick_runs One row per tick, with a JSON summary
orders, fills, portfolio_snapshots The ledger, per portfolio
position_attribution Which strategy owns which share of each position
shadow_decisions, shadow_portfolio_snapshots Model books of shadow strategies
users, portfolios, subscriptions, audit_log Accounts; see Accounts and Modes
broker_connections, broker_credentials, broker_accounts, broker_positions, broker_activities Synced broker accounts
alerts, notification_*, push_subscriptions Alerts and per-user notifications
scheduled_runs, scheduler_instances, scheduler_deadline_alerts The scheduler's records
jobs, strategy_drafts API background jobs and Studio drafts

Migrations

stonks db init applies pending SQL migrations to both files (lake 001 to 013, state 001 to 014). Run it after every upgrade. It refuses a migration that would drop populated data unless STONKS_ALLOW_DESTRUCTIVE_MIGRATIONS=1 (back up first).

stonks db info lists every table and its row count.

Clone this wiki locally