@pipeworx/sec-13f

Connect: https://gateway.pipeworx.io/sec-13f/mcp · Install: one-click buttons

Tools: 6

Institutional ownership from SEC Form 13F, asked security-first. Hosted, keyless to the caller. Deltas are a first-class citizen here, not an afterthought: manager_changes and security_flows answer “what changed” directly, because a static holdings snapshot isn’t a newsletter — a change is.

Tools

• who_owns(security, limit?) — which managers hold a security, ranked by position size. Accepts a company name, a ticker (resolved live through OpenFIGI — see “Ticker resolution” below) or a 9-character CUSIP.
• manager_portfolio(manager, limit?) — every position one manager reported, largest first (current quarter only — see manager_changes for what changed).
• manager_changes(manager, quarter?, compare_to?, limit?) — what one manager NEW-ed, EXITED, INCREASED or DECREASED between two loaded quarters. Defaults to the manager’s two most recent loaded quarters.
• security_flows(security, limit?) — total position value and holder count per loaded quarter for one security, plus which managers bought/sold/added/trimmed between the two most recent quarters.
• manager_profile(manager) — concentration (top-5/top-10 % of portfolio), turnover between the two most recent loaded quarters, and top positions. Does not include sector exposure — 13F carries no industry/sector field, and this pack does not fabricate one.
• holdings_coverage() — which SEC release windows and quarters are loaded, filings/holdings counts per quarter, and whether there’s enough loaded to diff (can_diff_quarters).

Ticker resolution — deliberately NOT a CUSIP↔ticker table

13F reports positions by CUSIP; there is no ticker column anywhere in the dataset. CUSIP identifiers are proprietary to CUSIP Global Services, so this pack does not build, cache, or redistribute a bulk CUSIP↔ticker crosswalk (same discipline as the paywalled-standards rule). Instead, a ticker-shaped input (e.g. "NVDA") is resolved live, per call, through OpenFIGI’s public mapping API — openly licensed Bloomberg FIGI — to get the security’s canonical name, which is then matched against issuer_name in our own already-ingested holdings, preferring the candidate with the largest aggregate value (a real operating company’s smallest institutional line still dwarfs a themed/ leveraged ETF’s largest one). Every response that used this path reports matched_on: "ticker_via_openfigi" and what it resolved to (resolved_via.cusip, .figi, .issuer_name) — never silently. Before this, who_owns("NVDA") matched issuer_name ILIKE '%NVDA%' and returned “Direxion Daily NVDA Bull 2X ETF” ranked alongside the real NVIDIA CORPORATION position — a silent wrong answer, not a missing feature.

Why this is hosted rather than proxied

The ingest program only accepts a local build when it makes a question possible that the upstream cannot answer. This is the clean case: the SEC publishes 13F manager-first, and so does every API over it. Our own edgar_institutional_holdings says so in its description — answering “which funds hold $TICKER” means running it once per manager, a loop over ~8,000 filers. That is a shape problem, not a rate-limit problem, and no upstream fixes it. One indexed query over the flat table inverts it.

What 13F does not tell you

Stated here and in every response, because 13F is routinely over-read:

• Long US-listed equity only. Shorts, cash, bonds and non-US holdings are excluded by rule. “How much does X own” is answerable only inside that universe.
• Quarterly, and late. Filed up to 45 days after the period end. It is a lagged snapshot, never a current position.
• $100M floor. A manager under that threshold does not file at all, so absence is not evidence.
• No ticker column. CUSIP is the only identifier of record; issuer names are as the filer typed them (“NVIDIA CORP”). A ticker search falls back to an issuer-name match and says so in matched_on.

One thing the numbers depend on

A single manager can report the same security on several lines — different investment discretion, or puts and calls alongside the common. who_owns collapses lines per manager before ranking. Without that you get the largest lines rather than the largest holders, which is a different list that looks equally plausible.

Data + refresh

Source: https://www.sec.gov/data-research/sec-markets-data/form-13f-data-sets — one rolling 3-month ZIP carrying seven TSVs. Three are used: SUBMISSION (accession → CIK), COVERPAGE (accession → manager) and INFOTABLE (the holdings, ~3.8M rows/release, 396MB uncompressed).

The filename encodes its window (01mar2026-31may2026_form13f.zip) and is not derivable — releases do not land on calendar quarters. Both the worker config and the script read SEC’s index page for the newest link; a hardcoded quarter would ingest once and serve stale data forever.

• Standing path: workers/data-pipeline (sec-13f-submissions, sec-13f-managers, sec-13f-holdings).
• Backfill / recovery (needs a Supabase service key or linked supabase CLI): node scripts/ingest-sec-13f.mjs --only holdings.
• Backfill via the gateway’s admin route (needs only PIPEWORX_INTERNAL_SECRET, the credential every fleet session already carries — no raw DB key): node scripts/ingest-sec-13f-admin.mjs --url https://www.sec.gov/files/structureddata/data/form-13f-data-sets/<window>_form13f.zip. Posts through POST /admin/sec13f_load on the gateway (table-allowlisted, ≤3,000 rows/call, same ceiling as every other hosted ingest here). This is how the second quarter (fleet #339, needed so manager_changes/security_flows have something real to diff) was loaded.
• Freshness: registered in shared/src/freshness.ts at a 200-day SLA — release cadence plus the ~45-day filing lag, so a missed release trips it within a quarter.

Schema and indexes: supabase/migrations/061_sec_13f.sql. The (cusip, value_usd DESC) index is what makes the inversion viable; without it this is a 3.8M-row sequential scan and the build is slower than the API it replaced.

Multi-quarter backfill — throughput, resume, and what degrades at depth

Measured 2026-09-04 loading quarters 3-8 (fleet #1245, going from 2 quarters to 8 for manager_changes/security_flows to have a real trend to show):

• ~870 rows/sec through the admin route, so one quarter’s ~2.8-3.8M holdings rows takes ~1-2 hours, not minutes. Size 8 quarters of wall-clock time accordingly.
• The loader is resumable, not all-or-nothing. Every write uses Prefer: resolution=merge-duplicates keyed to the table’s real unique id (infotable_sk for holdings, accession_number for managers/submissions), and scripts/ingest-sec-13f-admin.mjs ships --skip-rows N for exactly this. If a load dies mid-quarter, do not delete and reload — get the exact committed count (count(*), not pg_class.reltuples, which is stale until the next autovacuum), subtract the pre-load baseline, and resume with --skip-rows <delta>. Batches are atomic per POST (≤3,000 rows), so the delta is always a clean multiple of the batch size — there is no partial-row corruption to clean up. Do not run this loader unwatched by a Monitor/log-tail you might later tear down. A Monitor and a run_in_background loader can share a process group in the harness, so calling TaskStop on the Monitor can kill the sibling loader with zero error output — the log just stops mid-line. If that happens, it looks exactly like a silent crash; check for it before assuming a DB-side failure.
• sec.gov shares one egress IP across the whole fleet and rate-limits by IP. Running this loader’s ZIP fetch at the same time as any other lane’s SEC bulk pull (Form 4/insider, EDGAR full-text, XBRL) tripped a 429 from SEC’s Akamai front end with an extendable 10-minute timeout. Coordinate before starting a bulk sec.gov fetch — see CLAUDE.md’s Cross-Project Rules.
• Depth degrades who_owns on the ILIKE/ticker path — the index isn’t the problem, the heap is. resolveTicker()’s issuer_name ILIKE '%name%' ORDER BY value_usd DESC LIMIT 50 (which runs on every plain-ticker call before any cusip lookup) does use the sec_13f_holdings_issuer_trgm_idx GIN trigram index — EXPLAIN (ANALYZE, BUFFERS) at ~10.1M rows / 1.8GB heap showed the index step taking 87ms. The other 5.16 of a 5.27s cold execution was the Bitmap Heap Scan fetching ~28K matched rows’ table pages, most of which are not resident in Supabase’s 512MB shared_buffers. A warm re-run of the same predicate came back in 107ms. This cost scales with table size, not with what the index can do — at 8 quarters (~9.7GB heap) expect cold ticker lookups to cost more than the ~5-20s measured at quarter 3, and it will keep growing with every future quarter added. A resolver-table follow-up (caching ticker→CUSIP so resolveTicker()’s scan runs once per security ever, not once per cold call) is tracked separately rather than blocking this backfill. (Resolved: migration 154 built that resolver table — 8,510ms -> 2.1ms.)
• who_owns sums in the DB, and the reason is round-trip COUNT, not any one plan (fleet #1254, migration 156). Two plan fixes landed first — 154 above, and 155 for windowQuarterMap — and neither moved who_owns, which stayed at 28-30s for a mega-cap. What it was actually paying: MSFT has 10,729 holdings rows and 6,801 accessions in the latest window, so one call made 103 sequential PostgREST requests — 11 Range pages, then 46+46 chunks of 150 to join submissions and managers — and returned 25 managers. The fix is sec_13f_who_owns(), which does the window scoping, amendment dedupe, manager join and group-by server-side in ONE request: 29.81s -> 11.75s for MSFT, 27.78s -> 9.68s for AAPL, with every manager total unchanged. It is deliberately NOT a smaller fetch — see the split-lines note above; capping rows is the #427 bug, where a manager’s own total silently shrinks. Verify with node scripts/sec13f-who-owns-bench.mjs before|after|--diff, which fails on any changed manager total rather than on wall-clock alone. Responses carry aggregated_in_db. If it is false, the pack fell back to the old 103-trip path because the RPC 404’d — i.e. a deploy landed ahead of migration 156. That is correct but slow, and it is the one thing to check before re-diagnosing a slow who_owns from scratch.

Tools

• who_owns — Which institutional managers hold a given US-listed security, ranked by position size — the reverse of every 13F API, which requires you to name the manager first. Give a company name, ticker (e.g. “N
• manager_portfolio — Every 13F position reported by one institutional manager, largest first — the classic manager-first view, over the same indexed data. Give the manager name (“Berkshire Hathaway”, “Pershing Square”). R
• manager_changes — What one institutional manager NEW-ed, EXITED, INCREASED or DECREASED between two 13F quarters — the newsletter primitive: a portfolio snapshot is not news, a change is. Give the manager name; optiona
• security_flows — Net institutional buying or selling of one security across the loaded 13F quarters — total position value and holder count per quarter, plus which managers newly bought, sold out, added to or trimmed
• manager_profile — A profile of one institutional manager’s 13F book: portfolio concentration (share of value in the top 5 / top 10 positions), turnover between the two most recent loaded quarters, and top positions. Us
• holdings_coverage — What this 13F dataset currently covers: which SEC release windows and reporting quarters are loaded, how many filings and holdings rows are in each, and total size. Use to check freshness and to know