"""Repository for strategy evaluations (requirements §10).
Every evaluation is recorded, including holds and guardrail rejections. A table
holding only the trades that happened cannot answer "why did nothing happen on
Tuesday" — which is the question a later review most often needs.
"""
import json
import logging
from collections.abc import Sequence
from datetime import UTC, date, datetime, time, timedelta
from sqlalchemy import select
from sqlalchemy.orm import Session, sessionmaker
from trader.decisions import StoredDecision
from trader.persistence.models import Decision
__all__ = ["DecisionRepository"]
_logger = logging.getLogger("trader.persistence.decisions")
[docs]
class DecisionRepository:
"""Writes and reads the `decisions` table."""
def __init__(self, session_factory: sessionmaker[Session]) -> None:
self._session_factory = session_factory
[docs]
def record(
self,
decided_at: datetime,
strategy_id: str,
ticker: str,
action: str,
reasoning: str,
trade_id: int | None = None,
inputs: dict[str, object] | None = None,
rejection_reason: str | None = None,
outcome_note: str | None = None,
) -> int:
"""Record one evaluation. Returns the new row id.
`reasoning` is what the decider said (WHY — its rationale, always);
`inputs` is what it saw. Keeping them in separate columns is what lets
a new prompt be re-run against the same input months later and the
two outputs compared.
`outcome_note` (issue #25) is WHAT happened — an execution summary
("Bought 26 at limit 374.47") for a decision that actually placed an
order, `None` for a hold/rejection/error, which have nothing to
execute. Kept apart from `reasoning` on purpose: before this field
existed, callers appended the execution summary onto `reasoning`
itself for a buy/sell, so reading "why" for a filled trade actually
answered "what happened" — the two are different questions.
`rejection_reason` (issue #21) names *which* gate produced `action`
when it diverged from the strategy's own signal — `price_floor`
versus `discovery_cap`, say, both rendered identically as
`action='rejected'` otherwise. `None` on every row where nothing
overrode the signal, which is most of them.
`default=str` on the dump means a `Decimal` in `inputs` serialises
rather than raising at the boundary.
"""
decision = Decision(
decided_at=decided_at,
strategy_id=strategy_id,
ticker=ticker,
action=action,
reasoning=reasoning,
outcome_note=outcome_note,
trade_id=trade_id,
inputs_json=None if inputs is None else json.dumps(inputs, default=str),
rejection_reason=rejection_reason,
)
with self._session_factory() as session:
session.add(decision)
session.commit()
return decision.id
[docs]
def get(self, decision_id: int) -> Decision | None:
"""One decision by id."""
with self._session_factory() as session:
return session.get(Decision, decision_id)
[docs]
def latest_decision(self, strategy_id: str, ticker: str) -> StoredDecision | None:
"""The newest decision this strategy recorded for this ticker.
Satisfies `trader.decisions.DecisionMemory`, which is what
`LlmStrategy` holds — the strategy never sees a session or this table's
column names.
Ordered by `decided_at` **and then `id`**, both descending. Every
`decisions` row in one cycle shares that cycle's `decided_at`, and the
pipeline writes exactly one row per `(symbol, strategy)` per cycle, so
in production the timestamp alone resolves it — but two rows sharing a
timestamp must still order deterministically rather than by whatever the
database happens to return, or a reuse gate reading this would flap
between two answers for reasons nothing records.
`ticker` matches **exactly**, with no case folding. The pipeline records
the same string it evaluates, so a mismatch cannot arise from a symbol
travelling through it; if one ever did, this returns `None` and the
caller does its work the slow way, which is the safe direction. Case
folding here would instead cost the index and buy a guarantee nothing
needs.
Never raises on ordinary data. A row with no `inputs_json`, or one whose
JSON does not parse, or one whose JSON is not an object, yields `None`:
the caller reads this to decide whether it may *skip* work, so "I could
not tell" has to degrade into doing the work.
"""
statement = (
select(Decision)
.where(Decision.strategy_id == strategy_id, Decision.ticker == ticker)
.order_by(Decision.decided_at.desc(), Decision.id.desc())
.limit(1)
)
with self._session_factory() as session:
row = session.scalars(statement).first()
if row is None or row.inputs_json is None:
return None
try:
inputs = json.loads(row.inputs_json)
except ValueError as exc:
# `json.loads` raises `JSONDecodeError`, a `ValueError`. Logged
# rather than swallowed silently: an unparseable row is a real
# defect somewhere upstream, and it would otherwise present only
# as "the reuse gate never fires", which looks like a quiet
# feature rather than a broken one.
_logger.warning(
"decision %d (%s/%s) has unparseable inputs_json: %s",
row.id,
strategy_id,
ticker,
exc,
)
return None
if not isinstance(inputs, dict):
# A bare list or string is valid JSON and not a decision record.
_logger.warning(
"decision %d (%s/%s) inputs_json is %s, not an object",
row.id,
strategy_id,
ticker,
type(inputs).__name__,
)
return None
return StoredDecision(
decision_id=row.id,
decided_at=row.decided_at,
action=row.action,
reasoning=row.reasoning or "",
inputs=inputs,
)
[docs]
def latest_for_ticker(self, ticker: str, on: date | None = None) -> Decision | None:
"""The most recent decision for `ticker`, optionally on one UTC day.
For the chat CLI's decision-explain path (issue #14): a user names a
symbol and, optionally, a date rather than a row id, and this answers
that query directly rather than making every caller filter
`recent()` client-side.
`ticker` matches exactly, with no case folding — same discipline as
`latest_decision`; the caller is responsible for upper-casing a
symbol typed by a person. `on` bounds the *UTC calendar day*
`[00:00, 24:00)`, matching `decided_at`'s own timezone rather than
the caller's local one, so "the decision on 2026-08-10" means the
same day for anyone reading `decided_at` back later.
"""
statement = select(Decision).where(Decision.ticker == ticker)
if on is not None:
start = datetime.combine(on, time.min, tzinfo=UTC)
statement = statement.where(
Decision.decided_at >= start,
Decision.decided_at < start + timedelta(days=1),
)
statement = statement.order_by(
Decision.decided_at.desc(), Decision.id.desc()
).limit(1)
with self._session_factory() as session:
return session.scalars(statement).first()
[docs]
def recent(self, limit: int = 20) -> list[Decision]:
"""The most recent decisions, newest first."""
statement = (
select(Decision)
.order_by(Decision.decided_at.desc(), Decision.id.desc())
.limit(limit)
)
with self._session_factory() as session:
return list(session.scalars(statement))
[docs]
def for_strategy_since(
self, strategy_id: str, since: datetime, *, until: datetime | None = None
) -> list[Decision]:
"""Every decision this strategy recorded after `since`, oldest first.
For the shadow-portfolio simulator (issue #33): it needs to know
which symbols this strategy touched at all since its last stepped
bar (to build the relevant-symbol set), and then, per bar, every row
recorded inside that bar's `[bar_ts, bar_ts + bar_width)` span (via
`until`) — `since` inclusive, `until` exclusive, matching this
project's other half-open-window conventions (`load_bars`,
`NewsArchiveRepository.load_items`).
Ordered oldest-first, unlike every other method in this file: the
simulator's "last row per symbol wins within a bar" rule needs to
iterate forward and overwrite, not read a single newest row.
"""
statement = select(Decision).where(
Decision.strategy_id == strategy_id, Decision.decided_at >= since
)
if until is not None:
statement = statement.where(Decision.decided_at < until)
statement = statement.order_by(Decision.decided_at.asc(), Decision.id.asc())
with self._session_factory() as session:
return list(session.scalars(statement))
[docs]
def by_trade_ids(self, trade_ids: Sequence[int]) -> dict[int, Decision]:
"""The decision that produced each trade, keyed by `trade_id`.
Not every trade has one: a protective stop firing, or any other
broker-side fill this app did not itself decide (a manual trade, an
`acceptance_forced_buy`-style test), never went through `record()`.
Callers must read a missing key as "no recorded decision", not as a
gap to raise on — same discipline `AlpacaBroker.get_order_by_id`
uses for a 404.
"""
if not trade_ids:
return {}
statement = select(Decision).where(Decision.trade_id.in_(trade_ids))
with self._session_factory() as session:
rows = list(session.scalars(statement))
return {row.trade_id: row for row in rows if row.trade_id is not None}