k
4442e97f28
New backend pipeline: 8 free public macro signals fetched in parallel,
upserted once per calendar day, served via /api/macro/{snapshot,history}.
- AHR999 (computed from Binance 200d klines)
- Altcoin Season Index (CoinGecko top-50 30d)
- Fear & Greed (alternative.me)
- BTC dominance, ETH/BTC ratio
- Stablecoin supply (DeFiLlama)
- Spot BTC ETF net flow (Farside)
- BTC perp open interest (Binance fapi)
Each fetcher is independently @_none_on_fail decorated so one outage
can't take down the snapshot; scoring renormalises across whichever
indicators returned a value. Daily cron at 03:00 UTC; on startup a
fire-and-forget bootstrap fills today's row if missing.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
67 lines
2.8 KiB
Python
67 lines
2.8 KiB
Python
"""Macro indicator snapshots (one row per day).
|
|
|
|
Adds the `macro_snapshots` table backing the BTC page's macro panel. Wide
|
|
table — one column per indicator — because every panel view fetches them
|
|
all at once and a long EAV table would just need an immediate pivot. Daily
|
|
snapshot uniqueness is enforced by a UNIQUE(snapshot_date) constraint so the
|
|
fetcher can run safely on cron without producing dupes.
|
|
|
|
Indicators (all free / public):
|
|
ahr999 : derived from price + age
|
|
altcoin_season_index : % of top-50 alts beating BTC over 90d (blockchaincenter formula)
|
|
fear_greed : alternative.me, 0-100
|
|
btc_dominance_pct : CoinGecko global
|
|
eth_btc_ratio : Binance ETHBTC
|
|
stablecoin_supply_usd : DeFiLlama (USDT+USDC+DAI total)
|
|
etf_flow_net_usd_1d : Farside Investors (BTC spot ETF daily net flow)
|
|
btc_open_interest_usd : Binance fapi open interest
|
|
composite_score : -100..100 weighted blend computed at insert time
|
|
regime_label : "BULL" | "BULLISH" | "NEUTRAL" | "BEARISH" | "BEAR"
|
|
|
|
`raw_json` retains the full upstream payload per indicator in case we want
|
|
to recompute or audit historical fetches without re-hitting the APIs.
|
|
|
|
Revision ID: 022
|
|
Revises: 021
|
|
Create Date: 2026-05-25
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "022"
|
|
down_revision = "021"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"macro_snapshots",
|
|
sa.Column("id", sa.Integer, primary_key=True),
|
|
sa.Column("snapshot_date", sa.Date, nullable=False, unique=True, index=True),
|
|
sa.Column("captured_at", sa.DateTime, nullable=False),
|
|
|
|
# The indicators. All nullable — any single upstream failure shouldn't
|
|
# block the whole row; we record what we got and leave the rest NULL.
|
|
sa.Column("ahr999", sa.Float, nullable=True),
|
|
sa.Column("altcoin_season_index", sa.Float, nullable=True),
|
|
sa.Column("fear_greed", sa.Integer, nullable=True),
|
|
sa.Column("fear_greed_label", sa.String(32), nullable=True),
|
|
sa.Column("btc_dominance_pct", sa.Float, nullable=True),
|
|
sa.Column("eth_btc_ratio", sa.Float, nullable=True),
|
|
sa.Column("stablecoin_supply_usd", sa.Float, nullable=True),
|
|
sa.Column("etf_flow_net_usd_1d", sa.Float, nullable=True),
|
|
sa.Column("btc_open_interest_usd", sa.Float, nullable=True),
|
|
|
|
# Optional composite — see app/services/macro/scoring.py.
|
|
sa.Column("composite_score", sa.Float, nullable=True),
|
|
sa.Column("regime_label", sa.String(16), nullable=True),
|
|
|
|
# Raw payload per indicator for debugging / re-scoring.
|
|
sa.Column("raw_json", sa.Text, nullable=True),
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_table("macro_snapshots")
|