The goal
Collect historical data on companies across the AI value chain, make it easy to query, and build trend forecasts on top. Step one is getting the securities table right, because every analysis after it inherits its mistakes.
Design decisions
- UUID primary keys, not tickers. Tickers change: Facebook went from FB to META in 2022.
- Tickers are unique only among active securities. A delisted ticker can be reused, so a partial unique index constrains only securities still trading.
- A delisting date instead of
is_active. A date answers "what was trading on this day", which keeps survivorship bias out of backtests. - ADR rules as constraints. An ADR must have a share ratio and nothing else may. The ratio is NUMERIC, not floating point, because it feeds price conversions.
- Sector and AI layer stored separately. The GICS sector and a company's place in the AI value chain are different dimensions.
The principle
Constraints should block impossible data, not rare data. ARM's ordinary shares aren't listed anywhere and it trades only as an ADR on NASDAQ, so the rule "an ADR must have a local exchange" was dropped.