Skip to content

listing.md

Latest commit

 

History

History
196 lines (174 loc) · 8.98 KB

listing.md

File metadata and controls

196 lines (174 loc) · 8.98 KB
Raw

listing

Purpose

Stores canonical exchange-specific listing identity and the authoritative listing quote unit.

Grain

One row per (exchange_id, symbol).

Live Stats

  • Snapshot source: data/pyvalue.db on 2026-07-28
  • Row count: 75,680
  • Table size: 3,395,584 bytes (3.2 MiB)
  • Approximate bytes per row: 44.9

Columns

Column Type Null Key Notes
listing_id INTEGER no PK canonical listing surrogate key
issuer_id INTEGER no FK issuer metadata link
exchange_id INTEGER no FK, idx canonical exchange link; part of composite unique key
symbol TEXT no bare canonical listing symbol such as AAPL; part of composite unique key. CHECK enforces uppercase, no whitespace, and [A-Z0-9.&^*-] characters only
currency TEXT no authoritative listing quote unit, including subunits such as GBX, ZAC, and ILA. NOT NULL since migration 069; CHECK enforces 3-char uppercase ASCII letters
isin TEXT yes idx ISO 6166 security identifier. Added by migration 088. Nullable — EODHD publishes none for ~25% of listings and absence is a valid state. Deliberately not UNIQUE: every venue trading the same shares carries the same ISIN, which is what makes it the cross-listing grouping key. CHECK enforces 12 uppercase alphanumerics with a 2-letter country prefix and a numeric check digit
primary_listing_status TEXT no canonical primary-listing classification: unknown, primary, or secondary. CHECK enforces the vocabulary since migration 088

Keys And Relationships

  • Primary key: listing_id
  • Physical foreign keys:
    • exchange_id -> exchange.exchange_id
    • issuer_id -> issuer.issuer_id
  • Physical references from other tables:
    • financial_facts.listing_id -> listing_id
    • financial_facts_refresh_state.listing_id -> listing_id
    • market_data.listing_id -> listing_id
    • metric_compute_status.listing_id -> listing_id
    • metrics.listing_id -> listing_id
    • provider_listing.listing_id -> listing_id
  • Unique constraints beyond the primary key:
    • (exchange_id, symbol)
  • Main logical refs: canonical root for facts, prices, metrics, and primary-listing status

Secondary Indexes

  • idx_listing_isin (isin) WHERE isin IS NOT NULL
  • idx_listing_issuer (issuer_id)

Main Read Paths

  • canonical-scope resolution joins listing to exchange and projects the canonical symbol listing.symbol || '.' || exchange.exchange_code as a display label only — never a filter/join key. Full / by-exchange scope scans the supported universe (list_supported_listings); an explicit --symbols request seeks only the requested rows (list_supported_listings_for_symbols: split the canonical symbol, seek exchange by exchange_code, then listing by the (exchange_id, symbol) UNIQUE index)
  • downstream joins from facts, market data, metrics, and primary-listing status
  • FX currency discovery and currency-scoped data checks

Main Write Paths

  • refresh-supported-tickers — the sole runtime writer of listing rows, of listing.currency, and of listing.isin; it never deletes them: a prune removes only the provider layer (provider_listing + raw/fetch/normalization state), and a listing left with no provider mapping is retained, unreachable through the provider-joined scopes until a provider maps it again (2026-07-11 design)
  • migration-time backfill from legacy securities

ingest-fundamentals never writes here. It attaches each payload to a listing that refresh-supported-tickers has already catalogued and skips any symbol whose listing is absent (creating one would require writing the NOT NULL listing.currency). Currency therefore has a single source of truth.

Sample Rows

  • Snapshot source: data/pyvalue.db on 2026-07-28
  • Sample window: first 5 rows returned by SQLite ordered by listing_id ASC
[
  {
    "listing_id": 1,
    "issuer_id": 1,
    "exchange_id": 1,
    "symbol": "AALB",
    "currency": "EUR",
    "isin": "NL0000852564",
    "primary_listing_status": "primary"
  },
  {
    "listing_id": 2,
    "issuer_id": 2,
    "exchange_id": 1,
    "symbol": "ABN",
    "currency": "EUR",
    "isin": "NL0011540547",
    "primary_listing_status": "primary"
  },
  {
    "listing_id": 3,
    "issuer_id": 3,
    "exchange_id": 1,
    "symbol": "ACOMO",
    "currency": "EUR",
    "isin": "NL0000313286",
    "primary_listing_status": "primary"
  },
  {
    "listing_id": 4,
    "issuer_id": 4,
    "exchange_id": 1,
    "symbol": "AD",
    "currency": "EUR",
    "isin": "NL0011794037",
    "primary_listing_status": "primary"
  },
  {
    "listing_id": 5,
    "issuer_id": 5,
    "exchange_id": 1,
    "symbol": "ADYEN",
    "currency": "EUR",
    "isin": "NL0012969182",
    "primary_listing_status": "primary"
  }
]

Review Notes

  • Canonical user-facing symbols such as AAPL.US are derived, not stored.
  • isin is identity evidence, not classification: it answers "which security". A depositary receipt is legally a distinct security with its own ISIN, so ISIN groups cross-listings of one security but never an ADR with its underlying. refresh-supported-tickers is the primary source (Isin from the provider's exchange symbol list, which covers listings whose fundamentals payload omits it); migration 088 seeded the column from stored General.ISIN. A refresh may correct a stored ISIN but never blanks one — a payload missing the field is treated as a provider gap, not a retraction. Shape normalization lives in pyvalue.identifiers (shaped_isin, shaped_lei) and mirrors the SQL CHECK predicates in migrations.py; keep the two in step.
  • There is deliberately no lei column here. Migration 088 added one so issuer grouping could read it cheaply; migration 090 removed it. Once reconcile-issuer-identity converges, a listing's LEI is functionally determined by its issuer_id — the grouper puts every listing sharing an LEI under one issuer, which carries the same value — so storing it here was a transitive dependency and the same fact in two tables. The LEI is now read from fundamentals_raw.data -> '$.General.LEI', the one place it originates. The cost was accepted knowingly and measured: reconcile-issuer-identity went from ~3s to ~97s on the live catalog, with every grouping outcome identical.
  • isin stays for the opposite reason, and the contrast is the point. ISIN identifies a security and a listing quotes exactly one, while an issuer may have issued many — Alphabet has one LEI and two ISINs. There is no issuer-level column for it to duplicate and no functional dependency to violate, so listing grain is its correct home.
  • listing.currency is the only persisted listing-currency truth. It is a quote unit and is not collapsed to base currency at storage time. It is written solely by refresh-supported-tickers; fundamentals ingestion reads the catalog and never creates or mutates a listing's currency.
  • Monetary normalization, market-cap calculations, FX discovery, and monetary metrics derive base currency from listing.currency.
  • Unknown primary-listing status is treated as eligible; downstream primary-only scopes exclude only secondary. This is load-bearing, not incidental: the classifier returns unknown whenever no evidence decides a listing, and 19,389 listings rely on it to stay in the universe — roughly 8,800 on domestic exchanges with no EODHD PrimaryTicker coverage, and roughly 10,600 quote-venue lines that were secondary until 2026-07, when the venue-based rules were removed for having no support in EODHD's data. See docs/providers/eodhd.md for the rule set and the accepted cost.
  • primary_listing_status is written only by ingest-fundamentals (as it stores each raw payload) and the two reconcile commands; every other command reads it. All three go through one orchestration (storage/universe_reconcile.py) over one rule set (pyvalue.universe.listing_classification), differing only in scope — so there is no second implementation to drift. Ingest re-evaluates each batch's whole neighbourhood, not just the listings that arrived, which is what makes the result independent of ingestion order. The write is guarded, so a settled catalog issues no updates. A flip to secondary changes nothing but this column -- the listing keeps its facts/metrics/market-data and is excluded from universe work solely by the primary-only scope filters. Migration 078 is the one-time backfill that resolved any leftover unknown listing with stored fundamentals (it shipped with the eager purge that was policy at the time).