Stocks ingestion

Lesson 7 · about 15 minutes · how prices get into the tables, why every job is idempotent, and the three platform limits that shaped the design.

Every price in this system arrives by REST polling from Massive; the plan has no WebSocket. So ingestion is a set of cron-driven POSTs to service-only routes on the stocks function, each pulling one slice from Massive and upserting it. This lesson covers the price and profile jobs. SEC filings and financials are lesson 9; the storage layout they write into is lesson 8.

The chain, not three readers

1Quartr defines the universe. Its webhook fills Companies with quartr_company_id, company_name and a tickers jsonb array. Massive is filtered down to this set; a whole-market call returns ~12,400 bars and about 5,400 are kept. docs/MASSIVE_INGESTION_CONTEXT.md §1

2/refresh writes Stock_Current_Quotes. Selection is the SQL function get_tickers_for_refresh(p_tiers): active, non-blocked companies whose tickers are in the requested market-cap tiers, plus brand-new companies with no quote row yet. The handler pages it with .range() because the small tier exceeds max_rows. stockRefresh.ts:244-292, migration 20260710190921

3History jobs read the quotes table to know which tickers exist. A ticker becomes eligible for bars only after the quote loop has written it. docs/MASSIVE_INGESTION_CONTEXT.md §2

The jobs

Route	Massive call	Cadence	Writes	Idempotency key
`refresh`	snapshot (whole market) then per-ticker fallback	mega 5s, large+mid 30s, small 60s, weekdays 04:00-20:00 ET	`Stock_Current_Quotes`	ticker
`ingest-minute-bars`	snapshot in 5 chunks of 1,200 tickers, 5 in flight	every 30s in session	`Stock_Price_Minute`	(ticker, timestamp) minuteBarIngest.ts:16-19
`refresh-historical`	per-ticker aggregates, sequential	5-10 min via SQL wrappers `refresh_intraday_mega_large()` etc.	intraday / hourly tiers	(ticker, timestamp, retention_tier)
`backfill-daily`	grouped daily, one call per date, whole market	01:00 UTC Tue-Sat, trailing 4 days	`Stock_Price_Daily`	same, stamped midnight ET
`refresh-market-cap`	ticker details	07:00 UTC, per-run budget, oldest first	`shares_outstanding`; a trigger derives market cap	ticker, re-pulled after 3 days
`refresh-company-profiles`	ticker overview, branding images	every 6h, weekly full	`Companies`, `company-logos` bucket	company id
`retry-blocked` → `hydrate` → `prune-stale`	snapshot diff; aggregates 730d daily + hourly	daily 05:00 / every 5 min / daily 05:05	`Companies.blocked`, quotes, history	see lifecycle
`repair-splits`	splits feed, then self-calls `refresh-historical`	15:00 and 21:00 UTC; weekly backlog	daily bars around a split	ticker + window

Why one grouped call per date but N calls per ticker for hourly? Massive's grouped endpoint returns every ticker in one request but exists only for daily bars. That single fact makes daily cheap and complete (~500 calls rebuilds two years), forces intraday through the snapshot trick, and makes hourly cost ~5,400 calls a night. When a chart tier has gaps, this asymmetry is the first thing to suspect. docs/PRICE_TIER_SOURCING.md

Refresh: two phases and a circuit breaker

Phase 1 pulls the whole-market snapshot and keeps tracked tickers. Phase 2 walks companies with no ticker in the snapshot and tries each of their listings with getCurrentQuote, US exchanges first. Success resets the company's failure count; failure increments it, and ten consecutive failures set Companies.blocked. A blocked mega or large cap raises an alert row, because that is almost always a rename, not a delisting. stockRefresh.ts:384-470, :95-140

retry-blocked later diffs blocked companies against a fresh snapshot and unblocks matches; hydrate then cold-fills them with 730 days of daily bars, hourly bars, market cap, profile and earnings; prune-stale does the reverse, blocking quote rows whose tickers Massive no longer serves and cleaning rename orphans. retryBlocked.ts:39, hydrate.ts:85-130, pruneStale.ts:44-66

The Massive client

massiveFetch is the only way out to Massive. It runs at full speed and reacts: a 429 waits for Retry-After or exponential backoff with jitter, a 5xx and any transport failure retry up to five times, and every attempt has a 20-second AbortSignal.timeout. A 4xx other than 429 throws MassiveHttpError immediately so callers can tell a delisted symbol from a blip. getSnapshot deliberately fails fast because it feeds the 5-second cron and a retry would collide with the next tick. massiveClient.ts:50-58, :91-160; docs/EDGE_FUNCTIONS_STATE.md

The limits that shaped all of this

Limit	Consequence in code
Edge function wall clock 400s, CPU 2s per request	Per-run ticker budgets with a `remaining` count; `backfill-daily` capped at 31 days; whole-market `JSON.parse` is a real CPU charge. marketCapRefresh.ts:15-20
PostgREST `max_rows = 1000`, silent truncation	Every universe read pages explicitly; `get_tickers_for_refresh` gained an `ORDER BY` so offset paging is stable. migration 20260710190921:3-14
Cron overlap	A hung fetch once stretched a 2.4s run to 68s and overlapped the 30s cron; hence the per-attempt timeout and idempotent upserts everywhere. massiveClient.ts:100-104
Daily bars must be midnight ET	CHECK constraint `daily_bar_timestamp_is_midnight_et`. A different stamp is a different key and duplicates history instead of overwriting it. docs/BACKFILL_RUNBOOK.md

Check yourself

Do this in the repo

Run refresh locally without spending Massive calls. Setting the key to the sentinel switches on mock quotes: stockRefresh.ts:38

# in src/supabase/functions/.env
MASSIVE_API_KEY=test-massive-api-key
# then, with functions served
curl -s -X POST 'http://localhost:54321/functions/v1/stocks/refresh' \
  -H "apikey: $SECRET_KEY" -H 'Origin: https://api.wallstreetbets.com' \
  -H 'Content-Type: application/json' -d '{"tiers":["mega"]}' | head -c 800

List every cron that targets the stocks function and match each to a row in the jobs table above:
```
grep -rhoE "/functions/v1/stocks/[a-z-]+" src/supabase/migrations | sort | uniq -c
```
Read docs/BACKFILL_RUNBOOK.md end to end. It is the one runbook you will be handed at 3am.

Primary source

docs/MASSIVE_INGESTION_CONTEXT.md in this repo is the authoritative map of every ingest path and its limits. For the platform numbers, Supabase: Edge Function limits.

Next: Lesson 8, price history storage: the partitions these jobs write into and the roll-ups that run in SQL. Ask your teacher in the terminal to walk any single handler.