Strategy Tracker — Overview

Three PostgreSQL tables that define what a strategy is and what regime filters exist. This is a master/definition layer only — computing actual regime values (daily or any other timeframe) is a separate pipeline, designed later.

Managed with plain SQLAlchemy 2.0 models + Alembic, not raw .sql files. An earlier, much larger st_-prefixed schema (st_strategy, st_regime_def, run/trade/metrics tables, a bespoke strategies/db/migrate.py) was scoped out in backend/strategies/ before this was built and has since been removed. This package is a smaller, independent replacement, not an implementation of that plan — it now lives in the same backend/strategies/ path (reused after the removal, not a leftover of it).

---

Package File Map

File	Role
strategies/models.py	SQLAlchemy models — StrategyClassification (table st_group), Strategy (table st_master), RegimeMaster
alembic/env.py, alembic.ini	Alembic config (backend root)
alembic/versions/d7379ebee87b_create_strategy_tracker_tables.py	Creates all 3 tables (as strategy_classification/strategy/regime_master)
alembic/versions/2141dce5b2f0_rename_strategy_classification_and_.py	Renames strategy_classification/strategy → st_group/st_master (regime_master untouched)
strategies/seed_strategy_classification.py	Idempotent seed for st_group (13 rows)
tests/conftest.py, tests/test_strategy_models.py	pytest — asserts hypothesis NOT NULL is enforced at the DB level

---

Why a separate declarative base

strategies/models.py defines its own StrategyBase(DeclarativeBase) instead of reusing the shared app.database.session.Base used by the pre-existing NSE/MCX models in app/models/models.py.

Those models map tables Alembic never created (scr_master, scr_nseeq_eod, holidays, ...) and their ORM definitions can drift from the live Postgres schema in ways that are harmless at runtime. They are not harmless to alembic --autogenerate: pointing autogenerate at the full shared metadata diffs every table in the connected schema against it, and queues DROP TABLE for every production table it doesn't recognize. This actually happened once while building this migration — caught before applying, never shipped. alembic/env.py now does two things to keep this from recurring:

1. target_metadata = StrategyBase.metadata — only the 3 strategy-tracker tables.
2. An include_object filter that skips any reflected table/index not in that metadata, so autogenerate never proposes touching anything it doesn't own.

Regular queries/inserts work identically regardless of which declarative base a model uses — only Base.metadata (autogenerate/create_all) needs the split.

---

Schema

st_group (class StrategyClassification)

Controlled vocabulary for tagging a strategy. Seeded, never computed. Named strategy_classification until migration 2141dce5b2f0 renamed it (Python class name unchanged).

Column	Type	Notes
id	SERIAL PK
axis	VARCHAR(20) NOT NULL	CHECK IN ('horizon','nature','family')
code	VARCHAR(50) NOT NULL	UNIQUE (see below)
label	VARCHAR(100) NOT NULL	human-readable

UNIQUE(axis, code) as specified, plus a single-column UNIQUE(code) — Postgres can only FK to a column set that carries its own unique/PK constraint, and the composite unique above doesn't cover code by itself. st_master.horizon/nature/family FK to code directly, which relies on this. Codes are distinct across axes in the seed data, so this doesn't loosen anything the composite unique implies. One known gap: a plain FK to code doesn't check that e.g. st_master.horizon only ever holds an axis='horizon' code — enforcing that would need a composite FK against a generated axis column, which was deliberately left out to keep this master table lean.

Seed values:

axis	codes
horizon	intraday, swing, positional
nature	time_based, level_based, indicator_based, chart_based, statistical
family	breakout, mean_reversion, trend_following, volatility, arbitrage

st_master (class Strategy)

One tracked, parameterized strategy instance. Named strategy until migration 2141dce5b2f0 renamed it (Python class name unchanged).

Column	Type	Notes
id	SERIAL PK
strategy_code	VARCHAR(100) NOT NULL UNIQUE
param_hash	VARCHAR(64) NOT NULL UNIQUE	hash of canonical_json
canonical_json	TEXT NOT NULL	the exact string that was hashed — kept as text, not JSONB, so it stays byte-stable
display_name	VARCHAR(200) NOT NULL
source	VARCHAR(20) nullable	CHECK IN ('amibroker','algotest','stockmock','tradingview','manual')
symbol	VARCHAR(50) NOT NULL	single instrument
timeframe	VARCHAR(20) NOT NULL	free text (5min, daily, ...)
param_values	JSONB NOT NULL DEFAULT '{}'	GIN-indexed (ix_st_master_param_values)
horizon, nature, family	VARCHAR(50) nullable	FK → st_group.code
hypothesis	TEXT NOT NULL	hard constraint — no backtest stats allowed as a substitute
status	VARCHAR(20) NOT NULL DEFAULT 'candidate'	CHECK IN ('candidate','paper','live','retired')
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()
retired_at	TIMESTAMPTZ nullable
retired_reason	TEXT nullable

regime_master

Defines WHAT a regime filter is — not its computed daily values. Starts empty; regime data generation is out of scope for this table.

Column	Type	Notes
id	SERIAL PK
name	VARCHAR(100) NOT NULL UNIQUE	e.g. open_above_pdh, vix_high
category	VARCHAR(20) NOT NULL	CHECK IN ('gap','openrange','trend','volatility','calendar')
hypothesis	TEXT NOT NULL	economic reasoning — hard constraint
definition	TEXT NOT NULL	human-readable rule description
timeframe	VARCHAR(20) NOT NULL	the timeframe this regime is defined on
status	VARCHAR(20) NOT NULL DEFAULT 'proposed'	CHECK IN ('proposed','active','rejected')
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()

---

Running Locally

See Local CLI for the exact commands to migrate, seed, and test this against your local tedb.