edgarkit

real-time SEC EDGAR ingestion on a $30/month postgres box

edgarkit polls SEC EDGAR every 30 seconds, parses everything, and serves it as JSON with 30 to 45 second latency from the SEC's own timestamps. total operating cost about $32 a month. here is what the pipeline actually looks like, what i got wrong the first time, and the gotchas that nobody warns you about.

Want this data via API instead of reading about it? Get a free API key →

the setup

edgarkit is 402,000 filings, 831,000 parsed insider transactions, 7,384 companies fully indexed. one long-running node process on fly.io free tier polls SEC EDGAR every 30 seconds. one postgres 15 database on render at $30 a month holds the whole thing. no queue, no lambda, no kafka. total cost $32 a month.

this post is the technical writeup for anyone building something similar or just curious how the sausage is made. if you are looking for a real-time SEC data API and don't want to build your own, that is what edgarkit is. free tier is 10k requests a month, no card. link is at the bottom.

why real-time SEC data is harder than it looks

SEC EDGAR publishes on a rolling basis 24/7. the technical latency from filing acceptance to public availability is 15 to 30 seconds. so "real-time" is theoretically possible. in practice, almost every paid vendor claiming real-time is 4 to 24 hours behind. here is why.

there is no incremental delta feed. the /cgi-bin/browse-edgar endpoint gives you a search interface. the /Archives/edgar/data/{cik}/ paths give you individual filings. neither one tells you "here are the filings i have not shown you yet." you have to poll the full-index and diff against your last seen state.

the full-index updates every 10 minutes. so if you poll it every 5 seconds you gain nothing. if you poll it every 60 seconds you are already at the ceiling of what the endpoint can give you.

rate limits are aggressive. SEC enforces 10 requests per second per IP. push past it and you get dropped to 1 per second silently for the rest of the day. i learned this the hard way in month 2 when i thought i was being clever with concurrent fetches.

filings arrive in different formats. form 4 is XML. 10-K and 10-Q are HTML with embedded XBRL. 13F is XML but with a table of holdings that needs CUSIP-to-ticker resolution. 8-K is HTML with item numbers you have to parse out of the header. every form type has its own schema and its own edge cases.

schemas drift. the SEC changes the XML schema for form 4 roughly two to three times a year, sometimes without announcement. if your parser assumes the schema is stable, you wake up to a broken pipeline.

footnotes are where the interesting stuff lives. roughly half of the useful metadata on form 4 (10b5-1 flags, whether a trade is a scheduled plan, executive titles at time of trade) is free-text in footnotes, not typed fields. regex is your friend, with fallbacks.

the pipeline architecture

boring on purpose:

SEC EDGAR
   ↓ (poll every 30s)
node worker on fly.io free tier
   ↓ (parse + normalize)
postgres 15 on render $30/mo
   ↓ (REST API)
express server on fly.io
   ↓
customer

no queue. no lambda. no cache layer. one node process runs the ingestion loop forever, another runs the API server. postgres is the source of truth for everything.

why the boring architecture? because at 400k filings the workload is nowhere near the size where you need distributed anything. postgres handles the write throughput (roughly 2 to 5 filings per second sustained) without breaking a sweat. adding kafka would double the operational complexity for zero user-visible benefit.

the tables

three primary tables. filings, transactions, companies. plus a few smaller ones.

create table filings (
  filing_id      bigserial primary key,
  accession_num  text unique not null,       -- SEC's own identifier
  cik            text not null,
  form_type      text not null,               -- '4', '13F-HR', '8-K', etc
  filed_at       timestamptz not null,
  raw_xml        text,                        -- TOAST-compressed
  parsed_ok      boolean default false,
  ingested_at    timestamptz default now()
);

create index on filings (filed_at desc);
create index on filings (cik, form_type, filed_at desc);

create table transactions (
  txn_id            bigserial primary key,
  filing_id         bigint references filings,
  reporter_cik      text not null,
  ticker            text,
  transaction_code  char(1) not null,         -- 'P', 'S', 'A', 'F', 'M', etc
  shares            numeric,
  price_per_share   numeric,
  transaction_date  date,
  is_10b5_1_plan    boolean,
  reporter_role     text,                     -- 'CEO', 'CFO', 'Director', '10%+ Owner', etc
  raw_footnote      text
);

create index on transactions (ticker, transaction_date desc);
create index on transactions (reporter_cik, transaction_date desc);
create index on transactions (filing_id);

every table has an ingested_at in addition to filed_at. that lets us measure our own latency. ingested_at - filed_at is what customers actually care about.

the raw_xml column is TEXT with postgres TOAST compression rather than JSONB. reason: JSONB indexes were bloating the DB by 3x and we never actually queried into the XML programmatically. TEXT with TOAST compression cut storage by 60% and query performance is unchanged for our access patterns.

the ingestion loop

pseudocode of the ingestion worker. real code is a few hundred lines but this is the core:

const POLL_INTERVAL_MS = 30_000;
const RATE_LIMIT_DELAY_MS = 120; // stay under 10/sec

let lastAccessionSeen = await getLastAccessionFromDB();

async function pollOnce() {
  const fullIndex = await fetchWithRateLimit(
    'https://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent&type=&owner=include&count=100&output=atom'
  );
  const newFilings = parseFullIndex(fullIndex)
    .filter(f => f.accessionNum > lastAccessionSeen);

  for (const filing of newFilings) {
    try {
      const rawXml = await fetchWithRateLimit(filing.url);
      await ingestFiling(filing, rawXml);
      lastAccessionSeen = filing.accessionNum;
    } catch (err) {
      logError(err, filing);
      // continue with next filing; we retry failed ones from a queue
    }
  }
}

setInterval(pollOnce, POLL_INTERVAL_MS);

three things going on here that matter:

  1. rate-limited fetch with 120ms delay between requests. below the SEC's 10/sec ceiling with margin.
  2. per-filing try/catch. one broken XML file doesn't kill the whole loop. we log the error and move on. right now we have 46,433 accumulated ingestion errors, mostly from schema-drift or corrupted XML. that's about 0.04% of the 109k ingestion runs. acceptable.
  3. stateful across restarts. lastAccessionSeen is persisted to the DB so a worker restart doesn't re-ingest 100k filings.

the parsing gotchas

things that ate a week each when i first hit them:

form 4 footnotes

the SEC form 4 XML has a schema for typed fields. transaction code, shares, price, date, all typed. but half the interesting metadata is in an optional <footnotes> section as free text. examples:

  • "these shares were acquired pursuant to a rule 10b5-1 trading plan adopted on march 3, 2024"
  • "the reporting person disclaims beneficial ownership of these securities except to the extent of any pecuniary interest"
  • "represents shares withheld by the issuer to satisfy tax withholding obligations upon vesting"

that first one is the 10b5-1 flag. it tells you whether the trade was pre-committed or opportunistic. cohen/malloy/pomorski showed pre-committed trades have zero informational content and opportunistic ones have real predictive power. distinguishing them matters a lot.

my regex is roughly:

const TEN_B5_1_PATTERNS = [
  /rule\s+10b5[-\s]?1/i,
  /pre[-\s]?committed\s+trading\s+plan/i,
  /automatic\s+trading\s+plan/i,
];

function detect10b5_1(footnoteText) {
  if (!footnoteText) return false;
  return TEN_B5_1_PATTERNS.some(re => re.test(footnoteText));
}

this catches roughly 94% of the 10b5-1 flags in my sample. the 6% miss rate comes from insiders using idiosyncratic phrasing that doesn't match my patterns. i have not found a way to close that gap without an LLM in the loop, which adds latency and cost i haven't been willing to eat.

CUSIP-to-ticker for 13F

13F holdings are reported by CUSIP, not ticker. mapping CUSIP to ticker is not something SEC provides directly. the workarounds:

  • SEC's proprietary listing (mutual funds only, updated quarterly)
  • NYSE and NASDAQ maintain their own CUSIP-to-ticker maps for listed companies
  • stale open-source mirrors on github, mostly outdated

i use a hybrid. NYSE + NASDAQ for current tickers, SEC's proprietary listing for funds and ETFs, plus a fallback lookup against the companies table where i've already resolved a mapping. accuracy is around 97% for common tickers, drops to 85% for less common ones. amendments, share class changes, and ticker changes are the big miss categories.

amendment linkage

form 4/A amends a prior form 4. 10-K/A amends a prior 10-K. so on for every form type. the amendment relationship is supposed to be captured in a <periodOfReport> or <originalAccession> field. it's inconsistently populated.

my current heuristic: for any /A filing, look for a prior non-amendment filing from the same CIK with the same periodOfReport. if there's exactly one, that's the parent. if there's more than one, take the most recent. if there are zero, log the amendment as orphaned. orphaned amendments are about 2% of the total.

insider name normalization

"BUFFETT WARREN E", "Warren E Buffett", "Warren Buffett", "BUFFETT, WARREN EDWARD" are all the same person.

my approach:

  1. normalize to uppercase LAST-FIRST-MIDDLE where possible
  2. match on reporter_cik where the SEC provides it (which is now most of the time)
  3. fuzzy-match by last name + first initial for legacy filings without a CIK

the CIK match handles about 92% of cases cleanly. the fuzzy fallback covers most of the rest but occasionally merges two people with similar names. i accept that as a known-bug rather than a bug to fix, because the alternative (LLM-driven entity resolution) doesn't pencil out at my scale.

latency in practice

current measurements from the production DB, based on the last 7 days:

  • median ingested_at - filed_at for form 4: 31 seconds
  • p95 for form 4: 58 seconds
  • median for 8-K: 34 seconds
  • median for 10-K: 4 minutes 20 seconds (bigger files, XBRL parsing is the bottleneck)
  • median for 13F: 2 minutes 15 seconds (CUSIP resolution takes real time)

for form 4 that's within striking distance of the SEC's own publishing latency. for 10-K and 13F i could probably shave 30-50% by streaming the XBRL parser rather than buffering it. haven't gotten around to it yet.

the costs

full monthly breakdown:

  • render postgres, 16 GB storage, 2 GB RAM: $30
  • fly.io node worker (ingestion): $0 (free tier)
  • fly.io node worker (API server): $0 (free tier)
  • cloudflare DNS + WAF: $0 (free tier)
  • domain edgarkit.com: $1 (amortized annual)
  • plausible analytics: $0 (self-hosted on the same fly instance)
  • SEC EDGAR API: $0 (public data)
  • postgres backups (render built-in): $0

monthly total: about $31.

serves 402k+ filings across 7,384 companies at sub-minute latency. i suspect this is 5 to 10x cheaper than the same product would cost on AWS with lambdas and elasticache and all the other things you're "supposed" to use. postgres is really good at what it does.

what i got wrong the first time

tried to use lambdas for ingestion. cold starts added 500-2000ms of latency on every filing. at 30-second SEC latency floors, that's a 3-7% relative increase. moved to a long-running node process on fly.io free tier. cold-start problem gone, cost went to zero.

tried to parse XML with xml2js. works fine for form 4. absolutely destroys performance on 10-K XBRL files which can be 50-500 MB each. one 10-K would peg CPU at 100% for 30+ seconds. switched to sax for streaming parsing. 10-K parse time dropped by 80%.

stored full XML in a JSONB column. the JSONB indexes bloated the DB by 3x. queries against the JSONB were fast, but i never actually needed to query into the XML programmatically. i just needed to keep it around for auditing. moved to TEXT with TOAST compression. DB shrank by 60%.

used one big table for all form types. form 4 has different columns than 10-K which has different columns than 13F. shoving them all into one table with a giant data JSONB column worked but every query needed a form_type filter and the query planner couldn't optimize. split into per-form-type tables with FK back to the main filings table. queries 5-10x faster.

didn't checkpoint the lastAccessionSeen state. worker crashed once and re-ingested 4 hours of filings on restart. now it's committed to the DB after every batch.

what i'd change if i started over

  • would probably use clickhouse for the transactions table. 99% of queries against it are analytical scans over large windows. postgres does fine but clickhouse would be 5-10x faster on aggregations.
  • would build XBRL parsing as a separate service from the start. it's slow enough to be worth isolating so its performance doesn't affect other ingestion.
  • would set up webhooks earlier. i built webhooks in month 4 after enough customers asked. shouldn't have waited.
  • would set up an incremental amendment resolver as a background job. right now amendments are resolved at ingestion time. some resolution requires context from filings that arrive later, so a delayed second-pass would improve accuracy.

things i haven't figured out yet

  • the 6% of 10b5-1 flags my regex misses. an LLM would probably fix it but adds cost and latency.
  • the 3% of CUSIP-to-ticker misses. especially on OTC and ADR names.
  • whether real-time sub-minute latency actually matters for most customers. anecdotally the customers who care most are alerting bots and discord servers. for most use cases (research, screening, backtesting) 5-minute latency would be fine.

a thing i thought i understood and someone corrected me on

i had a guide up about the TPL / Horizon Kinetics one-share-a-day pattern where i framed it as a 10b5-1 plan running as a daily conviction signal. someone on r/investing pointed out its probably better read as screener real estate: SEC EDGAR and every third-party insider screener sorts by filing date, newest first. filing once a day puts you at the top of every screener every day. that's more concrete than my 'publish conviction' framing and it fits the mechanics of the data better.

mentioning it here because part of building a data api is talking to people who use the data differently than you do, and updating your read when someone lands a better one.

if you want to poke at it

if you build something with this or have questions about the pipeline, i'm at mat@edgarkit.com. always curious what people are working on.