SEC filings and financials

Lesson 9 · about 15 minutes · three data domains, one ingestion shape: a live poller, a nightly reconcile, and a per-issuer history backfill with a ledger.

The SEC domain is the newest and cleanest ingestion code in the repo. It was designed after the price-history lessons were learned, so it shows the pattern the team now reaches for: a cheap, frequent poller for freshness; a nightly sweep from a second source for gaps; a one-time history load driven by a ledger table; and a schema that tells you which is which. Financial statements and WSB sentiment follow the same shape with different vendors.

The three shapes

Shape	Cadence	Shared handler	Source	Write semantics
Live poll	every minute	none (`edgarFilingsPoll.ts`)	EDGAR latest-filings feed, free	upsert by accession number, advances a watermark
Date sweep (reconcile)	nightly, default last 3 days	`dateSweep.ts`	Massive index, T+1	`on conflict do nothing`: the poller's rows win
Issuer backfill (history)	every 2 minutes until drained	`issuerBackfill.ts`	Massive per-issuer	`on conflict do nothing`, outcome recorded per `(dataset, cik, ticker)`

Why two sources. Only EDGAR itself delivers a filing within minutes of acceptance, but its feed has no history and no reconciliation. Massive has a full SEC suite on the existing plan and key, but its index is a day behind. Splitting freshness and completeness across the two costs no procurement and makes gaps self-heal. docs/SEC_FILINGS.md § 0

Filings: the live poller

1pg_cron fires every minute through private.post_to_edge_function('/functions/v1/stocks/poll-edgar-filings'). migrations/20260910205127_schedule_sec_filings_jobs.sql:18

2The handler reads its watermark from SEC_Feed_Poll_State: the acceptance time the previous run reached. stocks/handlers/edgarFilingsPoll.ts

3It pages EDGAR's feed newest-first, up to 5 pages of 100, stopping at the watermark. At the 4pm rush the market files about 40 a minute, so five pages cover a poll that is a dozen minutes late. Past that, the nightly reconcile catches up. edgarFilingsPoll.ts PAGE_SIZE, MAX_PAGES

4It asks the database which CIKs we track via get_tracked_cik_tickers, joining SEC_CIK_Tickers to the company universe. A CIK can map to several share classes; one filing becomes one row per tracked ticker.

5It upserts into Stock_SEC_Filings and advances the watermark. On any EDGAR or database failure the watermark is not advanced, so the next run re-reads the same pages. A quiet minute is one feed request and zero writes.

EDGAR refuses anonymous clients. SEC_EDGAR_USER_AGENT must be set or the handler returns 500 rather than call without it. The CIK map itself is refreshed daily from the SEC's company_tickers_exchange.json by refresh-cik-map; rows are added and refreshed, never removed. stocks/handlers/cikMapRefresh.ts

The shared handlers

Filings and insider transactions each have a nightly sweep and a history backfill. Rather than four handlers, there are two generic ones and four thin configurations:

Route	Delegates to	Fetches	Writes
`/refresh-filings`	`handleDateSweepRequest`	`getFilingsIndex({filingDate})`	`Stock_SEC_Filings`
`/refresh-insider-transactions`	`handleDateSweepRequest`	`getForm4Rows({filingDate})`	`Stock_Insider_Transactions`
`/backfill-filings`	`handleIssuerBackfillRequest`	per-CIK index history for allowlisted forms	`Stock_SEC_Filings` + ledger
`/backfill-insider-transactions`	`handleIssuerBackfillRequest`	`getForm4Rows({issuerCik})`	`Stock_Insider_Transactions` + ledger

A route passes a config object: how to fetch one date or one issuer, which field is the issuer CIK, how records become rows, and where rows go. The body, validation, concurrency (4 dates or 4 issuers in flight) and error states belong to the shared handler. stocks/handlers/dateSweep.ts, issuerBackfill.ts, filingsRefresh.ts:20-28

Why the backfill is gated in SQL. The cron job calls backfill_sec_filings_if_pending(), a database function, not the edge function directly. When the ledger has nothing pending, the run is one query and no HTTP call. Filings run on even minutes, insider transactions on odd (1-59/2), so they never contend. migrations/20260911174209:49, 20260911175446:27

The ledger, SEC_Filings_Backfill, records attempts and last_error per issuer. After five failures an issuer is skipped. A ticker that joins the universe later shows up as pending and is loaded the same way, which is the whole point of driving the load from a table rather than a one-off script.

Financial statements

Same shape, different vendor endpoint (api.massive.com/stocks/financials/v1/…), same ledger idea (Stock_Financials_Backfill). Two things are specific:

The constraint is CPU, not requests. The edge runtime allows about 2 seconds of CPU per invocation. Twenty tickers of full history across three statements is roughly 4,000 result objects to parse, which is where earningsRefresh already measured the limit biting. So the backfill defaults to 20 tickers and the refresh to 250 companies at 5 recent quarters. stocks/handlers/financialsBackfill.ts DEFAULT_BATCH · financialsRefresh.ts COMPANIES_PER_RUN
Refresh has no change detection. Every six hours it takes the 250 least-recently-attempted companies and re-upserts a 15-month window. A restatement reaches us because the periods come back different. The universe is walked in about five days. financialsRefresh.ts REFRESH_WINDOW_MONTHS, STALENESS_CUTOFF_HOURS

Values are stored exactly as Massive sends them, including the 0.0 that means "not on this statement". The read RPC get_stock_financials nulls interest_expense and interest_income at the quarterly grain only, because the same zeros sum to the correct annual figure. TTM is computed, never filed, and sums flows but takes the current value for instants like total_assets. docs/FINANCIALS.md § "The 0.0 problem" · migrations/20260922120000_create_stock_financials.sql

WSB sentiment

The smallest sibling: wsbSentimentRefresh.ts fetches three periods (24h, 7d, 30d) from api.wsb.gold with WSB_GOLD_API_KEY and upserts per ticker. No ledger, no reconcile; the data is a rolling window so a missed run is overwritten by the next. migrations/20260914161927_create_wsb_sentiment.sql

Check yourself

Do this in the repo

Read the two shared handlers side by side and list what each config object must supply: src/supabase/functions/stocks/handlers/dateSweep.ts and issuerBackfill.ts.

With the local stack served, hit the reconcile with a deliberately bad range and read the 400:

curl -s -X POST 'http://localhost:54321/functions/v1/stocks/refresh-filings' \
  -H "apikey: $SERVICE_ROLE_KEY" -H 'Origin: https://api.wallstreetbets.com' \
  -H 'Content-Type: application/json' -d '{"from":"2026-09-20","to":"2026-09-01"}'

Run npx supabase test db and open tests/database/sec-filings-cron.test.sql: it pins the five job names and schedules from this lesson.

Primary source

docs/SEC_FILINGS.md is the research record and the design; sections 0 and 5 are enough. For the feed itself, SEC: Accessing EDGAR Data explains the user-agent requirement and rate expectations.

Next lesson: 0010 Earnings, the oldest ingestion domain, where data arrives by webhook instead of by poll. 0002 covers the transport every job here uses.

Ask your teacher: "what would I change to add a fourth dataset to issuerBackfill?" is a good test of whether the shape stuck.