mcp-sec-13f

# @pipeworx/sec-13f

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.

Part of [Pipeworx](https://pipeworx.io) — an MCP gateway connecting AI agents to 1576+ live data sources.

## 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.68