trader.persistence.models module

SQLAlchemy models for every table in requirements §10.

All tables are declared now so later slices add repositories without a migration step. Slice 1 only wires the snapshot repository.

class trader.persistence.models.AccountSnapshot(**kwargs)

Bases: Base

Periodic portfolio-level snapshot for performance tracking.

id: Mapped[int]

captured_at: Mapped[datetime]

account_id: Mapped[str | None]

cash: Mapped[Decimal]

equity: Mapped[Decimal]

buying_power: Mapped[Decimal]

portfolio_value: Mapped[Decimal]

is_paper: Mapped[bool]

positions: Mapped[list[PositionSnapshot]]

class trader.persistence.models.AnalystConsensusHistory(**kwargs)

Bases: Base

One trader scan-universe fetch of a symbol’s analyst consensus.

Append-only, like ArchivedNews — no unique constraint, because a later row is not a replacement, it is evidence the consensus moved. Freshness is answered by querying the newest row per symbol (AnalystConsensusRepository.latest_for_symbols), not by a separate coverage table: docs/ideas.md’s “age-based freshness policy” model, confirmed the right shape for non-bar data because BarCoverage/ NewsArchiveCoverage answer “has this span been fetched”, and the question here is “how old is the newest row”, which has no span at all.

id: Mapped[int]

symbol: Mapped[str]

fetched_at: Mapped[datetime]

strong_buy: Mapped[int]

buy: Mapped[int]

hold: Mapped[int]

sell: Mapped[int]

strong_sell: Mapped[int]

recommendation_mean: Mapped[float | None]

A rating (1..5), not money — Float, not DecimalText.

recommendation_mean_is_derived: Mapped[bool]

True when yfinance omitted recommendationMean and this app derived it from the five counts instead. See AnalystOpinion for the same flag.

target_mean_price: Mapped[Decimal | None]

target_high_price: Mapped[Decimal | None]

source: Mapped[str]

e.g. “yfinance” — the only source today, named anyway so a second source later does not need a migration to become distinguishable.

class trader.persistence.models.ArchivedNews(**kwargs)

Bases: Base

One historical news article, kept so a decision can be replayed.

The live path deliberately does not read this table: it stores the news it saw into decisions.inputs_json, because yfinance has no archive and the snapshot is the only record that will ever exist. Alpaca (Benzinga) does have history back to 2023, and that is what this table is for — accumulating backwards so a prompt can be rebuilt as it would have looked on a past date, through the same build_messages the live path uses.

Keyed (symbol, published_at, article_id). All three are needed: Benzinga publishes batches sharing a timestamp to the second, so the id is what keeps the second story in a batch; and one article legitimately appears under several symbols, filed once per symbol because that is how it was requested and how the prompt attributes it.

Rows are immutable evidence of what was said, not of what was true. A story corrected in place under the same id does not overwrite the row it already has — the model’s decision was made on the original wording, so replacing it would change the question being replayed.

id: Mapped[int]

symbol: Mapped[str]

article_id: Mapped[str]

The provider’s own id, as text. Alpaca’s is an integer, but the archive is keyed on this and docs/ideas.md’s fallback source for gaps (web.archive.org) has no integer id to offer. Not nullable: SQLite treats every NULL in a unique constraint as distinct, so a nullable id would re-insert on every backfill instead of deduping.

published_at: Mapped[datetime]

title: Mapped[str]

publisher: Mapped[str]

summary: Mapped[str | None]

url: Mapped[str | None]

content_type: Mapped[str]

fetched_at: Mapped[datetime]

When this row was archived. Not the same fact as published_at, and the only way to tell a 2023 story fetched today from one fetched in 2023.

class trader.persistence.models.BacktestRun(**kwargs)

Bases: Base

Parameters and results of a backtest (populated in Slice 4).

id: Mapped[int]

ran_at: Mapped[datetime]

strategy_id: Mapped[str]

ticker: Mapped[str]

start_date: Mapped[datetime]

end_date: Mapped[datetime]

parameters_json: Mapped[str | None]

starting_value: Mapped[Decimal | None]

ending_value: Mapped[Decimal | None]

total_return_pct: Mapped[Decimal | None]

max_drawdown_pct: Mapped[Decimal | None]

win_count: Mapped[int | None]

loss_count: Mapped[int | None]

benchmark_ending_value: Mapped[Decimal | None]

benchmark_return_pct: Mapped[Decimal | None]

class trader.persistence.models.BacktestTrade(**kwargs)

Bases: Base

One simulated fill.

Deliberately a separate table from trades: a simulated fill must never be readable as an order that actually happened.

id: Mapped[int]

backtest_run_id: Mapped[int]

ticker: Mapped[str]

quantity: Mapped[Decimal]

entry_at: Mapped[datetime]

entry_price: Mapped[Decimal]

exit_at: Mapped[datetime | None]

exit_price: Mapped[Decimal | None]

pnl: Mapped[Decimal | None]

class trader.persistence.models.BarCoverage(**kwargs)

Bases: Base

Which date ranges have actually been fetched for a symbol.

Without this, a missing bar is ambiguous: the market may have been closed, or the range may never have been downloaded. The second case would let a backtest run across a hole and report confident numbers from partial data.

id: Mapped[int]

symbol: Mapped[str]

interval: Mapped[str]

start_date: Mapped[datetime]

end_date: Mapped[datetime]

fetched_at: Mapped[datetime]

class trader.persistence.models.Base(**kwargs)

Bases: DeclarativeBase

Declarative base for all models.

Parameters:

kwargs (Any)

registry: ClassVar[registry] = <sqlalchemy.orm.decl_api.registry object>

Refers to the _orm.registry in use where new _orm.Mapper objects will be associated.

class trader.persistence.models.CachedBar(**kwargs)

Bases: Base

One OHLCV bar, cached so backtests are fast, offline, and repeatable.

Yahoo revises history, so re-fetching would make a backtest’s numbers drift for reasons unrelated to the code. Caching pins the input.

source (issue #41) is NULL for every bar this app fetched itself through YFinanceProvider/BarCache — the only live provider this app has ever had, so an unlabelled row needs no label to be unambiguous. A non-NULL value marks a row that arrived through trader seed-bars instead: a named vendor tag (seed_import.SOURCE_YFINANCE_SEED, seed_import.SOURCE_IBKR_SEED), never a boolean, because “seeded” alone would hide that the two seed files come from two different vendors with different adjustment/aggregation conventions and must stay distinguishable from each other, not just from a live fetch. BarRepository.delete_bars reads this column to refuse deleting a seeded row during a refresh — seed data has no later corrected fetch coming the way a live tail-refresh assumes, so a refresh silently replacing it with a differently-adjusted live fetch would corrupt validated backtest input rather than correct it.

id: Mapped[int]

symbol: Mapped[str]

interval: Mapped[str]

timestamp: Mapped[datetime]

open: Mapped[Decimal]

high: Mapped[Decimal]

low: Mapped[Decimal]

close: Mapped[Decimal]

volume: Mapped[int]

source: Mapped[str | None]

class trader.persistence.models.DecimalText(*args, **kwargs)

Bases: TypeDecorator

Store Decimal as TEXT so SQLite cannot round-trip it through float.

SQLAlchemy’s Numeric on SQLite goes via float and silently loses cents, which is unacceptable in a trading ledger.

This column type is the single chokepoint enforcing “money is never a float”, so it rejects a float rather than laundering it: Decimal(10.1) is Decimal(‘10.0999999999999996447286321199499070644378662109375’), which would round-trip as a legitimate-looking value.

Parameters:

• args (Any)

• kwargs (Any)

impl

alias of String

process_bind_param(value, dialect)

Reject anything that is not a Decimal (or a plain int) before storing.

Raises:

TypeError – value is a float or any other non-Decimal, non-int type — including bool, which Python treats as an int subclass and which is excluded explicitly so a stray boolean cannot silently pass as 0/1.

Parameters:

• value (Decimal | None)

• dialect (object)

Return type:

str | None

process_result_value(value, dialect)

Rebuild the exact Decimal from the stored text, or None.

Parameters:

• value (str | None)

• dialect (object)

Return type:

Decimal | None

class trader.persistence.models.Decision(**kwargs)

Bases: Base

One strategy evaluation, whether or not it produced a trade.

id: Mapped[int]

decided_at: Mapped[datetime]

strategy_id: Mapped[str]

ticker: Mapped[str]

action: Mapped[str]

reasoning: Mapped[str | None]

inputs_json: Mapped[str | None]

trade_id: Mapped[int | None]

rejection_reason: Mapped[str | None]

outcome_note: Mapped[str | None]

class trader.persistence.models.DeployedVersion(**kwargs)

Bases: Base

A code version a running process actually observed itself on.

Sparse, not per-cycle: DeployedVersionRepository.record_if_changed inserts only when the detected git_sha differs from the most recent row, so a trader run that runs for days on unchanged code adds nothing here — this is an event log of deploys actually observed, not a per-invocation timestamp.

trades/decisions/round_trips are not joined to this table at write time and get no new column: a row’s version is found at query time as the DeployedVersion with the greatest detected_at at or before that row’s own timestamp. A row that predates this table entirely has no match and must read as unknown version — never as the earliest recorded one, the same “unknown is not zero” discipline as the missing-analyst- coverage check.

id: Mapped[int]

git_sha: Mapped[str]

Full SHA, never abbreviated — versioning.UNKNOWN_GIT_SHA when the process could not determine it (not a git checkout, no git on PATH), recorded explicitly rather than guessed.

git_ref: Mapped[str | None]

None when no branch name resolves (detached HEAD, or the SHA itself is unknown) — never a guess.

detected_at: Mapped[datetime]

class trader.persistence.models.FocusList(**kwargs)

Bases: Base

A symbol trader build-focus-list computed overnight for a session (issue #44).

trading_day is a Date, not a UtcDateTime instant — the same “a claim about the session grid, not about instants” lesson BarCoverage already paid for. It is the session this row is for, stamped from the broker clock’s next_open.date() at the moment the batch runs (Decision 7), never wall-clock UTC midnight when the batch happened to execute. Intraday, a cycle reads only today’s trading_day; a batch that never ran, or stamped the wrong day, degrades to zero rows for today — the same shape themes: [] and a stale analyst_consensus_history already degrade to.

A history, not a pure set (unlike IndexMembership): yesterday’s rows are never deleted, only superseded by a different trading_day, so “what was the app considering watching on a given date” stays answerable the same way watchlist_events answers “why did it buy UUUU”. UNIQUE(trading_day, symbol) is what makes a same-day rerun (a manual retry after a partial build-focus-list failure) an upsert rather than a duplicate.

theme/origin mirror Candidate’s own fields (today always “analyst_scan”) rather than being hard-coded, so a future batch-only candidate source needs no column change. headline/url are schema symmetry with Candidate and are always None for an analyst_scan-origin row — there is no headline behind an analyst-consensus signal. recommendation_mean is a snapshot of the signal that earned the slot, the same “why is this here” motivation watchlist_events already serves for theme-search discoveries.

id: Mapped[int]

trading_day: Mapped[date]

symbol: Mapped[str]

theme: Mapped[str]

origin: Mapped[str]

headline: Mapped[str | None]

url: Mapped[str | None]

recommendation_mean: Mapped[float | None]

A rating (1..5), not money — Float, not DecimalText, same as AnalystConsensusHistory.recommendation_mean.

generated_at: Mapped[datetime]

class trader.persistence.models.IndexMembership(**kwargs)

Bases: Base

Which symbols trader scan-universe found in a named index.

A set, not a history — unlike AnalystConsensusHistory, membership on a given day is either true or it is not, so re-running the scrape upserts rather than appending. UniqueConstraint is what makes that upsert meaningful rather than merely conventional.

id: Mapped[int]

index_name: Mapped[str]

e.g. “sp500”. trader scan-universe supports only that value today.

symbol: Mapped[str]

discovered_at: Mapped[datetime]

class trader.persistence.models.NewsArchiveCoverage(**kwargs)

Bases: Base

Which spans of time have actually been fetched for a symbol.

Without this, “no rows” is two different claims: the symbol was quiet, or nobody asked. Measured over 189 weeks, the thin names this app trades are newsless in 41-73% of weeks (ITRG carried news in 51 weeks of 189), so a backfill that failed halfway would read as a genuinely quiet stretch — and the replay would then score the model on prompts missing news it would really have had. This is BarCoverage’s reason, applied to news.

Spans are instants, deliberately unsnapped. BarCoverage snaps onto the bar grid because a 1d bar is midnight-dated; a news item has a real publication time, and snapping would claim hours nobody fetched.

id: Mapped[int]

symbol: Mapped[str]

start_at: Mapped[datetime]

end_at: Mapped[datetime]

fetched_at: Mapped[datetime]

class trader.persistence.models.PositionSnapshot(**kwargs)

Bases: Base

One open position captured as part of an AccountSnapshot.

id: Mapped[int]

account_snapshot_id: Mapped[int]

ticker: Mapped[str]

quantity: Mapped[Decimal]

avg_entry_price: Mapped[Decimal]

current_price: Mapped[Decimal]

market_value: Mapped[Decimal]

unrealized_pl: Mapped[Decimal]

account_snapshot: Mapped[AccountSnapshot]

class trader.persistence.models.RoundTrip(**kwargs)

Bases: Base

One completed position: opened, closed, and what it earned.

A round trip spans two trades, so realized P/L cannot live on either Trade row without making the other misleading. Keeping it here makes “what did this decision earn?” a lookup rather than a self-join.

Matched on the position going flat rather than on order pairs, because Alpaca produces partial fills: a 29-share buy can fill 20 then 9, and an order-pair rule mismatches every time that happens.

id: Mapped[int]

symbol: Mapped[str]

strategy_id: Mapped[str | None]

entry_trade_id: Mapped[int | None]

exit_trade_id: Mapped[int | None]

decision_id: Mapped[int | None]

quantity: Mapped[Decimal]

entry_price: Mapped[Decimal]

exit_price: Mapped[Decimal]

realized_pl: Mapped[Decimal]

opened_at: Mapped[datetime]

closed_at: Mapped[datetime]

is_open: Mapped[bool]

class trader.persistence.models.SimEquitySnapshot(**kwargs)

Bases: Base

The simulated equity curve, one row per stepped bar (issue #33).

What yields max drawdown and total return in trader sim-report. The unique constraint on (portfolio_id, bar_ts) is the second of two idempotency mechanisms (SimPortfolio.last_bar_ts is the first) — a step asked to reapply an already-applied bar hits this constraint and no-ops rather than doubling the curve.

id: Mapped[int]

portfolio_id: Mapped[int]

bar_ts: Mapped[datetime]

equity: Mapped[Decimal]

class trader.persistence.models.SimOrder(**kwargs)

Bases: Base

A pending simulated limit buy (issue #33).

Mirrors the real pipeline’s own unfilled-limit-buy state, which is deliberately modelled rather than assumed away — filling instantly at the current price would both overstate results (you always get in) and erase the exact in-between state that let the real pipeline double-order a symbol against the live account.

decision_id is nullable so a fill’s provenance survives (which real decision proposed this simulated buy), without forcing every order to trace to one — a defensive None is cheaper than failing the whole step over a lookup that should always succeed but is not load-bearing if it doesn’t.

id: Mapped[int]

portfolio_id: Mapped[int]

decision_id: Mapped[int | None]

symbol: Mapped[str]

limit_price: Mapped[Decimal]

quantity: Mapped[Decimal]

created_bar_ts: Mapped[datetime]

status: Mapped[str]

“pending” | “filled” | “cancelled”. A plain string, not an enum column, matching Trade.status’s own convention elsewhere in this file.

class trader.persistence.models.SimPortfolio(**kwargs)

Bases: Base

One simulated portfolio per configured strategy (issue #33).

Forward-only and structurally isolated from real money: nothing in simulation/ imports brokers/ or execution/, and no flag on trades or round_trips marks a row as simulated — the isolation is that simulated state has nowhere else to live but these five tables.

last_bar_ts is the idempotency guard: a cycle runs roughly every 15 minutes against a daily bar, so the step is asked to run far more often than there is new data, and must apply each bar exactly once. None means no bar has ever been applied yet, not “applied at time zero”.

id: Mapped[int]

strategy_id: Mapped[str]

starting_cash: Mapped[Decimal]

cash: Mapped[Decimal]

last_bar_ts: Mapped[datetime | None]

class trader.persistence.models.SimPosition(**kwargs)

Bases: Base

One open simulated position (issue #33).

high_water_mark is the trail’s own ratcheted peak — tested against on the prior value each step, per the design doc’s pessimistic-ordering rule, never the value just written this same step. last_price is tracked separately from high_water_mark (issue #33’s own addition beyond the design doc’s bare field list): an equity snapshot needs a valuation for every open position on every stepped bar, including a bar where this particular symbol had no trade of its own (a holiday, a thin listing) and so was never marked or tested this step.

entry_confidence (issue #83) is the signal_confidence off the Decision whose BUY opened this position — captured once, at fill time, rather than re-derived later, because the position can outlive the bar range a later query might search. Float, not DecimalText: it mirrors decisions.inputs_json[“signal_confidence”]/Candidate.confidence, both plain float throughout this codebase (llm/prompt.py clamps every model confidence into 0..1), never money. None when the opening decision recorded no confidence (a rule strategy) or could not be found — simulation/step.py’s own eviction mirror excludes such a holder from consideration entirely, the same “unknown is not zero” rule pipeline/run_once.py’s _DiscoveredHolder uses on the real path.

id: Mapped[int]

portfolio_id: Mapped[int]

symbol: Mapped[str]

quantity: Mapped[Decimal]

entry_price: Mapped[Decimal]

high_water_mark: Mapped[Decimal]

last_price: Mapped[Decimal]

opened_at: Mapped[datetime]

entry_confidence: Mapped[float | None]

class trader.persistence.models.SimRoundTrip(**kwargs)

Bases: Base

One closed simulated position: opened, closed, and what it earned (issue #33).

exit_reason (“trailing_stop” | “strategy_sell”) is the whole point of this table’s existence separate from a bare P&L number: if a strategy’s returns come entirely from the trail firing, its entry logic is worthless while its total P&L looks respectable, and nothing else this app records would show that. trader sim-report reports both the full ledger and a stop-excluded view from this one column.

id: Mapped[int]

portfolio_id: Mapped[int]

decision_id: Mapped[int | None]

symbol: Mapped[str]

quantity: Mapped[Decimal]

entry_price: Mapped[Decimal]

exit_price: Mapped[Decimal]

realized_pl: Mapped[Decimal]

opened_at: Mapped[datetime]

closed_at: Mapped[datetime]

exit_reason: Mapped[str]

class trader.persistence.models.Trade(**kwargs)

Bases: Base

An order the app submitted.

Every lifecycle column is declared here rather than added when first needed, on purpose: create_all adds missing tables but never a missing column, there is no Alembic, and this table goes from empty to populated in Slice 2 Plan B. A column not declared before the first order costs either Alembic or the local history.

id: Mapped[int]

ticker: Mapped[str]

side: Mapped[str]

quantity: Mapped[Decimal]

price: Mapped[Decimal | None]

submitted_at: Mapped[datetime]

strategy_id: Mapped[str | None]

is_paper: Mapped[bool]

alpaca_order_id: Mapped[str | None]

status: Mapped[str | None]

filled_qty: Mapped[Decimal | None]

filled_avg_price: Mapped[Decimal | None]

filled_at: Mapped[datetime | None]

updated_at: Mapped[datetime | None]

settled_at: Mapped[datetime | None]

class trader.persistence.models.UtcDateTime(*args, **kwargs)

Bases: TypeDecorator

Store tz-aware datetimes as naive UTC; return them tz-aware.

SQLite has no timezone support, so a plain DateTime(timezone=True) column silently returns a naive value, and comparing that to an aware datetime raises TypeError. Normalizing on the way in and re-attaching UTC on the way out keeps every timestamp comparable.

Note the astimezone call: a non-UTC input must be converted, not merely stripped, or its instant is silently corrupted.

Parameters:

• args (Any)

• kwargs (Any)

impl

alias of DateTime

process_bind_param(value, dialect)

Convert (never merely strip) an aware datetime to naive UTC for storage.

Raises:

ValueError – value is naive (tzinfo is None) — guessing the zone would silently corrupt the timeline, so a naive input is refused rather than assumed to already be UTC.

Parameters:

• value (datetime | None)

• dialect (object)

Return type:

datetime | None

process_result_value(value, dialect)

Re-attach UTC to the naive value read back from storage, or None.

Parameters:

• value (datetime | None)

• dialect (object)

Return type:

datetime | None

class trader.persistence.models.WatchlistEvent(**kwargs)

Bases: Base

A ticker added to or removed from the watchlist, and by whom.

id: Mapped[int]

occurred_at: Mapped[datetime]

ticker: Mapped[str]

action: Mapped[str]

source: Mapped[str]

note: Mapped[str | None]