Skip to content

exchange.md

Latest commit

 

History

History
113 lines (90 loc) · 3.41 KB

exchange.md

File metadata and controls

113 lines (90 loc) · 3.41 KB
Raw

exchange

Purpose

Stores the canonical exchange identity registry shared across providers.

Grain

One row per canonical exchange code.

Live Stats

  • Snapshot source: data/pyvalue.db on 2026-07-28
  • Row count: 73
  • Table size: 12,288 bytes (12.0 KiB)
  • Approximate bytes per row: 168.3

Columns

Column Type Null Key Notes
exchange_id INTEGER no PK surrogate canonical exchange identifier
exchange_code TEXT no unique stable uppercase canonical exchange code such as US or LSE
created_at TEXT no initial insert timestamp
updated_at TEXT no last maintenance timestamp

Keys And Relationships

  • Primary key: exchange_id
  • Physical foreign keys: none
  • Physical references from other tables:
    • listing.exchange_id -> exchange_id
    • provider_exchange.exchange_id -> exchange_id
  • Unique constraints beyond the primary key:
    • exchange_code
  • Main logical refs: referenced physically by provider_exchange.exchange_id and listing.exchange_id

Secondary Indexes

  • None beyond the primary key and unique constraints.

Main Read Paths

  • canonical exchange lookup during provider-catalog joins
  • low-cardinality exchange review and debugging

Main Write Paths

  • migration-time backfill from legacy supported_exchanges
  • canonical exchange upserts during provider exchange refreshes
  • never deleted by catalog refreshes: canonical identity is retained even when a provider drops the venue (only the provider_exchange row and its provider layer go — 2026-07-11 design)

Column Usage Notes

  • exchange_id: stable canonical key for new normalized exchange relationships.
  • exchange_code: still the canonical exchange symbol used elsewhere in the app during this phase.
  • created_at: original insert timestamp for the canonical row.
  • updated_at: latest touch timestamp from migration or catalog maintenance.

Sample Rows

  • Snapshot source: data/pyvalue.db on 2026-07-28
  • Sample window: first 5 rows returned by SQLite ordered by exchange_id ASC
[
  {
    "exchange_id": 1,
    "exchange_code": "AS",
    "created_at": "2026-03-22T10:57:47.052304+00:00",
    "updated_at": "2026-07-11T14:03:20.675029+00:00"
  },
  {
    "exchange_id": 2,
    "exchange_code": "AT",
    "created_at": "2026-03-22T10:57:47.052304+00:00",
    "updated_at": "2026-07-11T14:03:20.676076+00:00"
  },
  {
    "exchange_id": 3,
    "exchange_code": "AU",
    "created_at": "2026-03-22T10:57:47.052304+00:00",
    "updated_at": "2026-07-11T14:03:20.676008+00:00"
  },
  {
    "exchange_id": 4,
    "exchange_code": "BA",
    "created_at": "2026-03-22T10:57:47.052304+00:00",
    "updated_at": "2026-07-11T14:03:20.676436+00:00"
  },
  {
    "exchange_id": 5,
    "exchange_code": "BC",
    "created_at": "2026-03-22T10:57:47.052304+00:00",
    "updated_at": "2026-07-11T14:03:20.675658+00:00"
  }
]

Review Notes

  • Keep this table narrow; provider-owned exchange metadata belongs in provider_exchange.
  • Avoid drifting provider-owned metadata into the canonical exchange layer.