The Airflow DAGs that run paiddaily.io

paiddaily.io is a trading dashboard for DeFi yield markets — static Next.js frontend, FastAPI backend that only reads from Postgres, and 25 Airflow DAGs that keep the data fresh. Here's the full architecture, from source to screen.

New to the tool? Start with what Airflow is and how the Airflow scheduler works, then come back for this production breakdown.

The architecture

The rule: Airflow writes, the API reads. No RPC calls on the request path, no HTTP fetches inline, no model calls, no background loops inside the API process. Data freshness is a DAG concern. Request latency is an API concern. They never compete.

Aerodrome — 4 DAGs

Aerodrome is the LP management surface. Four DAGs keep pool data, position state, and the vote market fresh.

aero_positions_and_alerts runs every 2 minutes. Three tasks in one DAG: Multicall3 batch reads for on-chain pool state (tick, sqrtPrice, liquidity), then two parallel branches — range monitor (detects in/out-of-range position transitions and fires push notifications) and wallet sync (Sugar position refresh for every active wallet).

aero_claims_locks_sync runs every 5 minutes. Three parallel read-driven syncs: pool address resolution (resolves on-chain addresses for pools a user has viewed), AERO claim sync (walks claim history for accessed wallets), and veAERO lock sync (refreshes voting escrow state). All three only process entities that have been accessed since the last sync.

aero_vote_market_scan runs every 15 minutes. Walks the alive gauges and computes $/veAERO for the current epoch. A full scan takes around 25 minutes of RPC work, so max_active_runs=1 plus a cooperative lock turn overlapping schedules into cheap no-ops. The execution timeout sits well above the measured scan time — an earlier 10-minute cap killed every run mid-scan and froze the data for days.

aero_pool_universe_refresh runs hourly. Pulls the full Aerodrome pool universe from DefiLlama — TVL, volume, APR, IL risk for ~425 pools. On a fetch failure the prior rows stay intact and the error is stamped, so the list page shows staleness instead of blanks.

Boros — 3 DAGs

Boros covers funding-rate yield markets.

boros_snapshot_and_arb runs every 5 minutes. Fetches market snapshots from the Boros API, computes forecast metrics, upserts snapshots, then runs the cross-venue arb scanner — pairing markets by underlying across venues and computing spread, fees, and edge.

boros_market_discovery runs hourly at :10. Market sync only. A vault-refresh task used to follow it; it came out because Boros publishes no vaults endpoint and nothing read the tables it wrote.

boros_daily_report runs daily at 00:30 UTC. Generates the Boros recommendation from the snapshot and arb data the 5-minute cycle already keeps fresh, and caches it for the API. A historical-refresh task was removed for the same reason as the vault refresh: it never wrote its data anywhere.

Pendle — 4 DAGs

Pendle covers yield tokenization markets on Ethereum and Arbitrum.

pendle_price_refresh runs every 5 minutes. Updates only the underlying USD price on the latest snapshot row per market. Between daily snapshots, that is the one column that moves fast enough to matter — a stale price means a visibly wrong TVL column.

pendle_new_pool_detection runs every 15 minutes. Drains pendle.market.listed events from the domain event outbox, looks up market metadata, and upserts ticker legs (underlying, PT, YT, SY) — closing the gap between a Pendle market sync and the next hourly ticker harvest.

pendle_sync_markets runs hourly at :05. Pages the Pendle V2 /markets/all endpoint for every tracked chain, writes to bronze, upserts via stored proc. Handles new market discovery and relisting; the natural key makes a double run in the same hour a no-op.

pendle_daily_pipeline runs daily at 00:15 UTC. Three DAGs that used to be sequenced by schedule alone, now one DAG with real task dependencies. It retires matured markets, then snapshots every live market (implied APY, underlying 7-day MA, LP APR, catalyst-aware forecast, regime flips). Two branches then run in parallel off the fresh snapshot: APY-drift catalyst suggestions feeding the recommendation engine, and a Morpho Blue refresh that powers the PT-as-collateral lending cards on the Pendle detail pages.

Hyperliquid + Derive — 3 DAGs

Daily snapshots for the HYPE tools and the Derive vault board. Each one lands raw inputs only. The math lives in database views.

hype_netfloat_snapshot runs daily at 00:30 UTC. Fetches the buyback run-rate, total stake, and Assistance Fund balance, archives to bronze, and upserts. The net-float formula, flow gauge, and absorption buffer are computed in a view. The unlock schedule is a constant, which avoids a paid emissions API.

hype_carry_stack_snapshot runs daily at 00:45 UTC. Fetches only the venues that are new to the carry stack — HLP, top user vaults, validator staking, USDC earn, HyperEVM lending. Pendle PT and Boros funding rows are reused live in the ranking view, not re-ingested.

derive_vault_snapshot runs daily at 01:00 UTC. Fetches Derive vault statistics plus a trailing shareRate history and lands one row per vault per day. Realized APY, variance, risk, and ranking come from a view. Every source is free and needs no auth.

Picks + paper trading — 3 DAGs

daily_opportunity_pick runs daily at 12:30 UTC. Gathers candidates from the Pendle and Boros regime views, scores them, and records the winner and runners-up. The call-of-the-day endpoint reads from that log. Pure Postgres — no external calls.

paper_mtm_daily runs daily at 13:00 UTC. Marks every open simulated position to live venue data (liquidating any position that breaches its health factor), then recomputes the per-strategy scoreboard.

paper_control_daily runs daily at 13:30 UTC, after the mark settles. A Thompson-sampling bandit reweights, promotes, and retires strategy variants, reallocates a shared virtual bankroll by a fixed composite reward, and logs every decision.

Creators — 3 DAGs

The creator lens turns market commentary videos into structured briefs, a cross-creator consensus, and a credibility score. Video polling does not happen here — a shared platform ingestion pipeline polls YouTube and copies new videos into the database.

creators_pipeline runs hourly. Turns approved creators' posts into content, enqueues them for decode, and sends notifications. Both steps are pure stored procs — no polling, no model calls.

creators_decode runs every 30 minutes. A local model (Ollama) decodes new creator videos into briefs, then the same DAG reduces the trailing 7 days of briefs into the consensus digest. Both model steps share a 1-slot pool, so model runs never overlap, and the heartbeat is stamped only when both succeed. This used to be a scheduler job running outside Airflow; moving it in puts every job on one schedule, one set of logs, one retry policy.

creators_scoring runs daily at 06:00 UTC. Resolves ready crypto calls into outcomes with a free DefiLlama price lookup, then recomputes each creator's credibility from resolved outcomes, consistency, and recency.

Tickers — 3 DAGs

ticker_harvest runs hourly. Scans Aerodrome pools, the vote market, and Pendle markets for distinct token addresses and upserts them into the ticker registry. Pure intra-DB — no external calls.

ticker_enrich runs hourly at :05, offset from harvest to avoid pile-up. Batches tickers with a stale or missing price and fills in symbol, decimals, and USD price from DefiLlama.

ticker_refresh runs daily at 04:00 UTC. Nightly equities-cache refresh from a brokerage instruments API: upserts by symbol, deactivates anything no longer in the feed, then pulls liquidity ratings.

Platform — 2 DAGs

domain_events_prune runs daily at 04:00 UTC. Retention for the domain event outbox. Deletes only safe events — types with no consumer, or events every consumer has already received.

paiddaily_analytics runs daily at 11:30 UTC. Ingests GA4 traffic and Search Console search data into Postgres. Ingest-only, no email. No all_done trigger rule anywhere, on purpose: a failed fetch must fail the run and show red, not hide behind a green DAG.

The shape

25 DAGs. Eight domains. Every write goes through a stored proc. Every read comes from Postgres. The shortest interval is 2 minutes (aero_positions_and_alerts). The longest is daily (snapshots, recommendations, scoring, ticker refresh). The API has no idea any of this is happening — it just reads tables that happen to be fresh.