Company resolution as a DAG

“Apple” is not a primary key. AAPL, AAPL US, APC GY, CIK 0000320193, a LEI, a share-class FIGI, and a composite FIGI are different strings that must collapse to one issuer and then fan out again into many listings. If you JOIN on ticker you will merge Berkshire classes, steal a fund’s ISIN into an operating company, and 404 Japan.

This is how we resolve companies: a DAG of refreshable views. Open and public sources only in this story — LEI records, OpenFIGI, SEC (including N-PORT and Form ADV), EDINET. The API you call is Entity Reference / Entity Search; underneath, those are lookups into the graph this DAG emits.

Three grains, on purposeCopy Anchor Link

Search and Reference return security-grain Entity items. AAPL US and APC GY are different rows that share cik / lei. An entity-level id (CIK, LEI, EDINET, series) resolves to our primary listing for that issuer, not to every line. Entity Securities is the fan-out (capped). Entity Structure is legal-entity relationship records, not a ticker list.

There is no id field on the Entity item. You keep full_ticker, cik, lei, figi, and friends. The request echo id on Reference is the string you sent, after normalize. ISIN and CUSIP match and are never returned — licensing, not because we do not store them internally.

CIK is zero-padded to 10 digits on the entity. EDGAR folder paths use the integer form; that is a URL problem, not a resolve problem (filings to markdown).

Why a DAG of refreshable viewsCopy Anchor Link

Identifier data is wide, dirty, and late. A Python graph that rebuilds in memory every night does not survive a full LEI directory plus N-PORT history plus OpenFIGI expansion. A columnar warehouse of refreshable views gives us:

• Refreshable materialized views on a schedule (on the order of every six hours)
• Serial refresh so heavy phases do not run together and OOM
• Atomic swap on the issuer registry: consumers always see a complete snapshot, never a half-written JOIN
• Columnar scans over arrays of ISINs / FIGIs / tickers per issuer

A document search index is the wrong engine for this. Resolve is equality on normalized keys, then a rank. Search/autocomplete is a later projection of the same catalog.

Phases, in order of dependency:

1. Shared bridges — deduped LEI records, LEI↔CIK↔EDINET, child/subfund LEI sets
2. Fund sub-DAG first — series-level claims, then anti-join so operating companies cannot steal fund securities
3. Company edges — many sources emit (sec_type, sec_value) → issuer
4. Score and argMax — each security id belongs to exactly one issuer
5. Registry — one row per company, arrays of all claimed ids
6. Securities catalog — explode to listing grain (FIGI hierarchy + exchange)
7. Entities catalog — slugs, resolve keys, autocomplete projection

Open sources and what they are good forCopy Anchor Link

LEI directory + relationship records. Legal names, LEI status, jurisdiction. Relationship types include IS_SUBFUND_OF / IS_DIRECTLY_CONSOLIDATED_BY / IS_ULTIMATELY_CONSOLIDATED_BY. We exclude subfund LEIs from the operating-company graph. Child LEIs get a +2 tie-break so a security claimed by both a holdco and a subsidiary prefers the subsidiary.

LEI–ISIN mapping. Closest thing to a regulatory gold edge. Weight 10 — if the map says this ISIN is that LEI, it almost always wins.

OpenFIGI mapping. Share-class FIGI, composite FIGI, instrument FIGI, and the ISIN/CUSIP arrays on those records. Share-class FIGI is more specific (weight 4) than composite (weight 3, only when share-class is absent). This is how we expand a listing identifier into other identifiers without treating ticker as a primary key.

SEC N-PORT. Public fund holdings: a registrant LEI sitting next to held ISINs/CUSIPs. Historically accumulated and messy, so we do not trust a single filing:

score = frequency_factor × recency_factor
frequency_factor = min(distinct_filings, 4) / 4     -- saturates at 4
recency_factor   = 1 / (1 + days_since_last / 730) -- ~0.5 at two years

Pairs need score >= 0.3. One filing cannot pass (0.25 < 0.3). Weight then scales roughly 1–8 with score. That is how we get issuer↔ISIN edges for names that never appear in the LEI–ISIN file, without letting a 2018 typo live forever.

SEC profiles, series/class, Form ADV. CIK history from filings, fund series and class contracts, adviser CRD. The old company_tickers.json file is not in this graph — it is a convenience dump with no history and no share class. We take CIK/name from actual filings.

EDINET filer directory. edinet_code, names (including alphabetic), Japan securities codes. Name-cleaned bridges into the LEI directory / SEC when a Japan issuer also has a LEI or a US listing. EDINET codes sit on the registry as a first-class issuer key, not as a ticker alias.

LEI↔CIK bridge (its own small DAG): N-PORT registrant pairs (locked), jurisdiction-aware name match LEI directory × SEC, EDINET name match, ADV name match. One issuer should not be a CIK row and a separate LEI-only row if we can prove they are the same.

Scoring when sources disagreeCopy Anchor Link

Each edge is (identifier type, identifier value) → (CIK or LEI-only company) with a weight and a source tag. Confirming sources add. LEI–ISIN 10 + N-PORT 6 is a strong ISIN claim.

Then:

• CUSIP-6 bonus (+3). The first six characters of a CUSIP identify the issuer. If a company’s other CUSIPs agree on a CUSIP-6, matching CUSIPs get a boost. This pulls share classes together without merging two issuers that happen to share a ticker fragment.
• Child LEI (+2). See relationship records above.
• argMax. For each (sec_type, sec_value), exactly one company wins. Deterministic tuple comparison — no “random last write.”

Issuer unification: CIK is primary when present (all LEIs under that CIK merge). LEI-only when there is no SEC CIK. Strength order when promoting keys: CIK > LEI > EDINET > internal id. Orphans (we have a listing-like name cluster and no public entity id) get a stable internal id so they still resolve instead of disappearing.

Tickers are last-resort edges, and only when the normalized ticker maps to exactly one company after the other sources. Ambiguous tickers are dropped as edges. They may still appear as listing labels on the catalog after FIGI matching.

Funds run first and claim their ISIN/CUSIP/FIGI/ticker set. The company DAG anti-joins those claims. Otherwise every ETF holding Apple’s ISIN would be an Apple edge, or worse, Apple would eat the ETF’s class ISIN.

From registry to something you can queryCopy Anchor Link

The issuer registry is one row per company: arrays of ISINs, CUSIPs, FIGIs, tickers, names. It is the “if we say this ISIN is this CIK, believe it” table.

The securities catalog explodes that into listings. Group key preference: composite FIGI, else share-class FIGI, else instrument FIGI, else SEC class contract, else normalized ticker, else ISIN, else CUSIP — plus exchange code. Dual listings are supposed to be two rows. A single “Apple” row would be a bug.

The entities catalog adds:

• Presence signals (do we have filings, holdings, …) for ranking, not for identity
• Slug maps for /stocks/aapl-style keys
• A resolve index: lookup_kind × resolve_key (slug, entity id, security id, linked id)

Entity Reference is a point lookup on that index. A ticker or FIGI returns that listing. A CIK / LEI / EDINET / series id returns the primary listing (is_primary_in_group, US-home venue preference). Search is scored autocomplete over the same rows, not a second identity algorithm.

Promote to “final” is gated. If a refresh would drop a US home listing in favor of a foreign sibling for a popular name, we reject the wave. Corporate actions are the usual cause; silent swap is worse than a delayed refresh.

How a lookup actually ranksCopy Anchor Link

Resolve is equality. Search is scored. Once several listings share an issuer, we still have to pick one row for id=AAPL vs id=0000320193.

• is_primary_in_group marks the preferred line inside a share-class / composite group. Autocomplete adds a sizable boost when that flag is set, and a penalty for non-primary equity siblings so you do not get five Apple lines before IBM.
• US home venue ranks above foreign lines of the same issuer when the query looks like a US ticker. A German dual listing of a US issuer should not win query=AAPL.
• result_band is a coarse quality tier (has filings, is trading, …) so a perfect ticker match on a dead shell does not outrank a slightly fuzzier match on an issuer people actually read.
• Slugs (aapl, brk-b) are a separate lookup kind from FIGI and from CIK. Cascade: exact slug → suffix / venue variants → identifier kinds. That is why BRK-B and BRK B can hit the same listing without putting every punctuation variant in the registry arrays.

Entity Search is not grouped by issuer. You get a page of securities, primary first. Entity Reference with a CIK is grouped: one primary row. People confuse those two and think Search is “broken” when they see AAPL and APC GY as two hits. That is the product.

Entity Structure walks legal-entity relationship records (IS_DIRECTLY_CONSOLIDATED_BY, ultimate parent, and friends). It will not list tickers. Apple the issuer is one LEI; listings are not extra nodes.

Failure modes we designed forCopy Anchor Link

If you are building thisCopy Anchor Link

• Model issuer vs listing as two tables, not a ticker primary key.
• Weight regulatory identifier maps above listings.
• Consensus-filter any filing-derived edge (N-PORT is the template).
• Isolate funds before companies.
• Resolve conflicts with argMax, not “last file wins.”
• Never republish ISIN/CUSIP just because you stored them.
• Use a columnar store with incremental materialized views and an atomic snapshot — this is a JOIN problem, not a search-index problem.

RelatedCopy Anchor Link