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
117 lines
4.5 KiB
Python
117 lines
4.5 KiB
Python
"""Record universe membership, tracking ownership and operator preference
|
|
|
|
Revision ID: 036
|
|
Revises: 035
|
|
Create Date: 2026-09-07 00:00:00.000000
|
|
|
|
Migration 035 separated "stopped trading" from "stopped scanning". This
|
|
separates three more things that were still conflated:
|
|
|
|
universe_memberships which symbols the index actually contained, when
|
|
tickers.tracking_source who owns the row: a bootstrap, or an operator
|
|
tickers.scan_disabled_on the operator's own "stop scanning this"
|
|
|
|
**Why the operator flag is a separate column.** ``untracked_on`` was written by
|
|
both the prune and ``set_tracked``. With one column a prune -> re-entry cycle
|
|
would clear an operator's explicit "off", because re-entry cannot tell which
|
|
writer set it. Splitting them makes the two survivable independently.
|
|
|
|
**Existing "off" states are carried over, not reinterpreted.** Any row that
|
|
already has ``untracked_on`` set was written by one of those two callers and
|
|
there is no way to tell which. Copying the date into ``scan_disabled_on`` is the
|
|
conservative reading: a later sync clears ``untracked_on`` on re-entry but never
|
|
touches ``scan_disabled_on``, so a symbol someone deliberately switched off
|
|
cannot be reactivated by an automated run.
|
|
|
|
**Existing rows default to ``manual``.** Provenance cannot be recovered from
|
|
``created_at``, and ``manual`` is the fail-safe value: manual rows are never
|
|
pruned, so a bad guess costs an un-pruned symbol rather than a symbol swept out
|
|
of the scanner. New rows created by a bootstrap are stamped ``index``
|
|
explicitly. Nothing ever converts ``manual`` to ``index`` — a hand-added symbol
|
|
that later joins the index keeps its protection.
|
|
|
|
Membership history starts at first observation. ``first_seen_on`` is the first
|
|
date a live fetch actually listed the symbol, never a reconstructed index
|
|
effective date; earlier membership is simply unknown.
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
revision: str = "036"
|
|
down_revision: Union[str, None] = "035"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.add_column("tickers", sa.Column("scan_disabled_on", sa.Date(), nullable=True))
|
|
op.create_index("ix_tickers_scan_disabled_on", "tickers", ["scan_disabled_on"])
|
|
op.add_column(
|
|
"tickers",
|
|
sa.Column(
|
|
"tracking_source",
|
|
sa.String(length=16),
|
|
nullable=False,
|
|
server_default="manual",
|
|
),
|
|
)
|
|
|
|
# Every pre-existing row is treated as manually tracked: unprunable until a
|
|
# bootstrap has had a chance to claim the ones it owns.
|
|
op.execute("UPDATE tickers SET tracking_source = 'manual'")
|
|
# Preserve any existing "off" so an automated sync cannot reactivate it.
|
|
op.execute(
|
|
"UPDATE tickers SET scan_disabled_on = untracked_on "
|
|
"WHERE untracked_on IS NOT NULL"
|
|
)
|
|
|
|
op.create_table(
|
|
"universe_memberships",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column(
|
|
"ticker_id",
|
|
sa.Integer(),
|
|
sa.ForeignKey("tickers.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("universe", sa.String(length=20), nullable=False),
|
|
sa.Column("first_seen_on", sa.Date(), nullable=False),
|
|
# NULL == still observed. Closing sets this and retains the row; a
|
|
# re-entry inserts a new one.
|
|
sa.Column("absent_since", sa.Date(), nullable=True),
|
|
sa.Column(
|
|
"created_at",
|
|
sa.DateTime(timezone=True),
|
|
server_default=sa.func.now(),
|
|
nullable=False,
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_universe_memberships_ticker_universe",
|
|
"universe_memberships",
|
|
["ticker_id", "universe"],
|
|
)
|
|
op.create_index(
|
|
"ix_universe_memberships_universe_seen",
|
|
"universe_memberships",
|
|
["universe", "absent_since"],
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
# Membership history is observation-only and cannot be reconstructed once
|
|
# dropped; the ticker rows and their price history are untouched either way.
|
|
op.drop_index(
|
|
"ix_universe_memberships_universe_seen", table_name="universe_memberships"
|
|
)
|
|
op.drop_index(
|
|
"ix_universe_memberships_ticker_universe", table_name="universe_memberships"
|
|
)
|
|
op.drop_table("universe_memberships")
|
|
op.drop_column("tickers", "tracking_source")
|
|
op.drop_index("ix_tickers_scan_disabled_on", table_name="tickers")
|
|
op.drop_column("tickers", "scan_disabled_on")
|