dennisthiessenandClaude Opus 5
ad3e47dc50
bootstrap_universe(prune_missing=True) ran DELETE FROM tickers, cascading OHLCV, setups, scores and S/R levels away for any symbol that had merely dropped out of the index. Migration 032's own comment predicted this. The survivorship audit measured what it costs: 2.09 Sharpe against 1.57 on universe composition alone. Migration 035 adds untracked_on, the sibling of delisted_on — delisted means the security stopped trading, untracked means a prune took it out of the scanner. Nothing is deleted either way. Migration 036 separates three more things that were still conflated: universe_memberships what the index actually contained, when tickers.untracked_on excluded from scanning by a prune tickers.scan_disabled_on the operator's own "stop scanning this" tickers.tracking_source who owns the row: a bootstrap, or an operator The operator flag is its own column because with one column serving both writers, a prune -> re-entry cycle would clear an explicit "off" — re-entry cannot tell who set it. Re-entry clears untracked_on and never touches scan_disabled_on. untracked_on is NOT current index membership. The scheduled sync runs prune_missing=False, so a symbol can have left the index and still be scanned; the two legitimately differ and nothing asserts they agree. Tracking ownership is sticky towards manual: prune touches index-owned rows only, and a bootstrap never converts manual to index — a hand-added symbol that joins the index would otherwise lose its protection the moment it left again, which is exactly when it is needed. An explicit add of an index-owned symbol claims it as manual without touching either off-switch. Membership is observed, never asserted. first_seen_on is the first live sighting; absent_since is the first observation that found the symbol GONE, so the interval is [first_seen_on, absent_since) — named for what it stores rather than "last_seen_on", which would have claimed the opposite. Intervals are append-only, so a symbol that leaves and returns has two and a point-in-time query for the gap correctly answers "not a member". members_on() implements the boundary rule once. A date before the first live sync returns empty: that means "we were not looking", not "the index was empty". A failed fetch drives nothing. The cache and seed fallbacks mean the live fetch failed, and the seed lists are ~44 symbols against a ~500-symbol universe — a regression test caught a seed fallback untracking 2 of 3 tickers. Recording, pruning and clearing prune flags all require a live source; only adding still runs, so a cold start can still bootstrap from seeds. Migration decisions: existing rows become manual (fail-safe — a wrong guess costs an un-pruned symbol, not one swept out of the scanner), and existing untracked_on values are copied into scan_disabled_on, since the original writer is ambiguous and re-entry clears the prune column. Backtest eligibility is unchanged. _load_backtest_symbols deliberately does not use active_only, so today's scanner state cannot erase yesterday's candidates, and untracking a symbol still cannot silence a coverage gap in the report. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01HAH7vVtn7qbcn1EVheecHQ
60 lines
2.9 KiB
Python
60 lines
2.9 KiB
Python
from datetime import date, datetime
|
|
|
|
from sqlalchemy import Date, DateTime, ForeignKey, Index, String
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|
|
|
from app.database import Base
|
|
|
|
|
|
class UniverseMembership(Base):
|
|
"""One observed interval of a symbol's membership in an index universe.
|
|
|
|
Append-only. A symbol that leaves and returns has two rows, so a
|
|
point-in-time query for the gap between them correctly answers "not a
|
|
member" — reopening the original row would erase that.
|
|
|
|
**Observed, not asserted.** ``first_seen_on`` is the first date a live
|
|
universe fetch actually listed the symbol, not the date it joined the index.
|
|
Anything earlier is unknown, and this table never guesses: the removals list
|
|
reconstructed by hand in ``scripts/data/sp500_removals.json`` stays a
|
|
research artifact rather than a seed.
|
|
|
|
Membership is independent of *tracking ownership* (``Ticker.tracking_source``)
|
|
and of whether the symbol is scanned. A manually added symbol that happens to
|
|
be an index constituent gets intervals like any other; being untracked or
|
|
scan-disabled does not close one.
|
|
"""
|
|
|
|
__tablename__ = "universe_memberships"
|
|
__table_args__ = (
|
|
Index("ix_universe_memberships_ticker_universe", "ticker_id", "universe"),
|
|
# "Who was a member of U at date D" — the point-in-time query.
|
|
Index("ix_universe_memberships_universe_seen", "universe", "absent_since"),
|
|
)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
ticker_id: Mapped[int] = mapped_column(
|
|
ForeignKey("tickers.id", ondelete="CASCADE"), nullable=False
|
|
)
|
|
# "sp500" | "nasdaq100" | "nasdaq_all" — a symbol can belong to several.
|
|
universe: Mapped[str] = mapped_column(String(20), nullable=False)
|
|
# The first observation that listed this symbol. INCLUSIVE lower bound. Not
|
|
# the date it joined the index — that is unknown and never guessed.
|
|
first_seen_on: Mapped[date] = mapped_column(Date, nullable=False)
|
|
# The first observation that found the symbol ABSENT — hence the name. It is
|
|
# *not* "last seen present": the symbol was present at every observation
|
|
# before this one and absent at this one, so it is an EXCLUSIVE upper bound.
|
|
# NULL means still present at the most recent observation.
|
|
#
|
|
# member on D <=> first_seen_on <= D < absent_since (or absent_since IS NULL)
|
|
#
|
|
# The real departure lies somewhere in (previous observation, absent_since];
|
|
# with a daily sync that is a day, and with a missed week it is a week. The
|
|
# column records when we LOOKED, which is the only thing actually known.
|
|
absent_since: Mapped[date | None] = mapped_column(Date, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), default=datetime.utcnow, nullable=False
|
|
)
|
|
|
|
ticker = relationship("Ticker", back_populates="universe_memberships")
|