"Vibe-Trading: Your Personal Trading Agent"
Python ai-agentalgorithmic-tradingbacktestingfintechllmmcpmulti-agentpythonquantitative-financetrading
"""SQLite-backed strategy/factor artifact store.
Satisfies ``StrategyStoreProtocol``. Uses WAL mode for concurrent reads,
FK constraints for referential integrity, and ``PRAGMA user_version``
for schema migrations.
"""
from __future__ import annotations
import json
import sqlite3
import threading
import uuid
from contextlib import contextmanager
from datetime import datetime, timezone
from functools import wraps
from pathlib import Path
from typing import Callable, Sequence, TypeVar
from src.strategy_store.models import (
Artifact,
ArtifactStatus,
ArtifactType,
BenchCategory,
BenchResult,
DecaySignal,
DecaySnapshot,
ModelTier,
ValidationStatus,
validate_validation_status_transition,
)
# ---------------------------------------------------------------------------
# Constants
# ---------------------------------------------------------------------------
_DEFAULT_DB_PATH = Path.home() / ".vibe-trading" / "strategy_store.db"
# Schema version introduced by the model-governance migration (adds the
# developer/owner/validator/approver/model_version/artifact_version/
# model_tier/intended_use/limitations/validation_status/validation_date
# columns to `artifacts`). Bumping PRAGMA user_version documents intent;
# the migration itself is idempotent via a column-existence check so it is
# safe to run unconditionally on every open regardless of the stamped
# version (covers DBs that were hand-edited or partially migrated).
# Schema version 3 adds ``derived_from`` on ``artifacts``.
_SCHEMA_VERSION_ADAPTATION = 3
_ADAPTATION_COLUMNS: dict[str, str] = {
"derived_from": "TEXT",
}
# column_name -> DDL type/default fragment used by ALTER TABLE ADD COLUMN.
_GOVERNANCE_COLUMNS: dict[str, str] = {
"developer": "TEXT",
"owner": "TEXT",
"validator": "TEXT",
"approver": "TEXT",
"model_version": "TEXT",
"artifact_version": "TEXT",
"model_tier": "TEXT",
"intended_use": "TEXT",
"limitations": "TEXT",
"validation_status": "TEXT DEFAULT 'unvalidated'",
"validation_date": "TEXT",
}
# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
def _now_iso() -> str:
"""Return the current UTC time as an ISO-8601 string."""
return datetime.now(timezone.utc).isoformat()
def _new_artifact_id() -> str:
"""Generate a unique artifact identifier."""
return f"art_{uuid.uuid4().hex[:12]}"
def _json_dumps(value: object) -> str:
"""Serialize *value* to a compact JSON string."""
return json.dumps(value, ensure_ascii=False, sort_keys=True)
def _json_loads(value: str | None, default: object) -> object:
"""Deserialize a JSON string, returning *default* when empty or corrupt."""
if not value:
return default
try:
return json.loads(value)
except (json.JSONDecodeError, TypeError):
return default
def _default_db_path() -> Path:
"""Return the configured strategy-store database path."""
from src.config.accessor import get_env_config
raw_path = get_env_config().paths.vibe_trading_strategy_store_db_path.strip()
if raw_path:
return Path(raw_path).expanduser()
return _DEFAULT_DB_PATH
F = TypeVar("F", bound=Callable)
def _synchronized(method: F) -> F:
"""Serialize access to the shared SQLite connection."""
@wraps(method)
def wrapper(self, *args, **kwargs): # type: ignore[no-untyped-def]
with self._lock:
return method(self, *args, **kwargs)
return wrapper # type: ignore[return-value]
# ---------------------------------------------------------------------------
# SqliteStrategyStore
# ---------------------------------------------------------------------------
class SqliteStrategyStore:
"""SQLite-backed strategy/factor artifact store.
Satisfies ``StrategyStoreProtocol``. Uses WAL mode for concurrent reads,
FK constraints for referential integrity, and ``PRAGMA user_version``
for schema migrations.
"""
def __init__(self, db_path: Path | str | None = None) -> None:
"""Initialize the strategy store.
Args:
db_path: SQLite database path. When omitted,
``VIBE_TRADING_STRATEGY_STORE_DB_PATH`` can override the
default ``~/.vibe-trading/strategy_store.db``.
"""
self.db_path = (
Path(db_path) if db_path is not None else _default_db_path()
)
self.db_path.parent.mkdir(parents=True, exist_ok=True)
self._conn = sqlite3.connect(str(self.db_path), check_same_thread=False)
self._conn.row_factory = sqlite3.Row
self._conn.execute("PRAGMA foreign_keys=ON")
self._conn.execute("PRAGMA busy_timeout=5000")
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA synchronous=NORMAL")
self._lock = threading.RLock()
self._init_db()
# -- Schema bootstrap ---------------------------------------------------
def _init_db(self) -> None:
"""Create schema if needed, set PRAGMAs."""
with self._lock:
self._conn.executescript(
"""
CREATE TABLE IF NOT EXISTS artifacts (
id TEXT PRIMARY KEY,
type TEXT NOT NULL CHECK(type IN ('factor', 'strategy')),
name TEXT NOT NULL,
source_paper TEXT,
source_url TEXT,
formula_latex TEXT,
theme TEXT,
columns_required TEXT,
decay_horizon INTEGER DEFAULT 20,
signal_definition TEXT,
entry_rules TEXT,
exit_rules TEXT,
position_sizing TEXT,
universe TEXT NOT NULL,
signal_engine_path TEXT,
run_dir TEXT,
hypothesis_id TEXT,
derived_from TEXT,
status TEXT NOT NULL DEFAULT 'created'
CHECK(status IN (
'created','benching','active',
'monitoring','decayed','disabled')),
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
disabled_at TEXT,
disabled_reason TEXT,
UNIQUE(name, universe)
);
CREATE TABLE IF NOT EXISTS bench_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artifact_id TEXT NOT NULL
REFERENCES artifacts(id) ON DELETE CASCADE,
bench_type TEXT NOT NULL
CHECK(bench_type IN ('initial','periodic','manual')),
ic_mean REAL,
ic_std REAL,
ir REAL,
ic_positive_ratio REAL,
t_stat REAL,
sharpe REAL,
annual_return REAL,
max_drawdown REAL,
calmar REAL,
category TEXT
CHECK(category IN (
'alive','reversed','dead',
'confirmed_alive','noise')),
train_start TEXT,
train_end TEXT,
test_start TEXT,
test_end TEXT,
run_dir TEXT,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS adaptation_bindings (
artifact_id TEXT PRIMARY KEY REFERENCES artifacts(id) ON DELETE CASCADE,
contract TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS decay_snapshots (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artifact_id TEXT NOT NULL
REFERENCES artifacts(id) ON DELETE CASCADE,
rolling_ic_mean REAL,
rolling_ir REAL,
baseline_ic_mean REAL,
ic_ratio REAL,
decay_signal TEXT
CHECK(decay_signal IN (
'healthy','warning','decayed','critical')),
consecutive_warnings INTEGER DEFAULT 0,
detail TEXT,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_artifacts_status
ON artifacts(status);
CREATE INDEX IF NOT EXISTS idx_artifacts_type
ON artifacts(type);
CREATE INDEX IF NOT EXISTS idx_bench_artifact
ON bench_history(artifact_id, created_at);
CREATE INDEX IF NOT EXISTS idx_decay_artifact
ON decay_snapshots(artifact_id, created_at);
"""
)
if self._conn.execute("PRAGMA user_version").fetchone()[0] < 1:
self._conn.execute("PRAGMA user_version=1")
self._migrate_artifact_columns()
version = self._conn.execute("PRAGMA user_version").fetchone()[0]
if version < _SCHEMA_VERSION_ADAPTATION:
self._conn.execute(f"PRAGMA user_version={_SCHEMA_VERSION_ADAPTATION}")
self._conn.commit()
def _migrate_artifact_columns(self) -> None:
"""Add optional ``artifacts`` columns if missing.
Idempotent: checks ``PRAGMA table_info`` before each
``ALTER TABLE ADD COLUMN``.
"""
existing_columns = {
row["name"] for row in self._conn.execute("PRAGMA table_info(artifacts)")
}
extra = {**_GOVERNANCE_COLUMNS, **_ADAPTATION_COLUMNS}
for column, ddl in extra.items():
if column not in existing_columns:
self._conn.execute(f"ALTER TABLE artifacts ADD COLUMN {column} {ddl}")
# -- Write transaction --------------------------------------------------
@contextmanager
def _write_transaction(self):
"""Open an immediate write transaction for cross-connection safety."""
self._conn.execute("BEGIN IMMEDIATE")
try:
yield
except Exception:
self._conn.rollback()
raise
else:
self._conn.commit()
# -- Row converters -----------------------------------------------------
@staticmethod
def _row_to_artifact(row: sqlite3.Row) -> Artifact:
"""Convert a database row to an ``Artifact`` dataclass."""
return Artifact(
id=row["id"],
type=ArtifactType(row["type"]),
name=row["name"],
universe=row["universe"],
source_paper=row["source_paper"],
source_url=row["source_url"],
formula_latex=row["formula_latex"],
theme=tuple(_json_loads(row["theme"], [])),
columns_required=tuple(_json_loads(row["columns_required"], [])),
decay_horizon=row["decay_horizon"],
signal_definition=row["signal_definition"],
entry_rules=row["entry_rules"],
exit_rules=row["exit_rules"],
position_sizing=row["position_sizing"],
signal_engine_path=row["signal_engine_path"],
run_dir=row["run_dir"],
hypothesis_id=row["hypothesis_id"],
derived_from=row["derived_from"],
status=ArtifactStatus(row["status"]),
created_at=row["created_at"],
updated_at=row["updated_at"],
disabled_at=row["disabled_at"],
disabled_reason=row["disabled_reason"],
developer=row["developer"],
owner=row["owner"],
validator=row["validator"],
approver=row["approver"],
model_version=row["model_version"],
artifact_version=row["artifact_version"],
model_tier=ModelTier(row["model_tier"]) if row["model_tier"] else None,
intended_use=row["intended_use"],
limitations=row["limitations"],
validation_status=(
ValidationStatus(row["validation_status"])
if row["validation_status"]
else ValidationStatus.UNVALIDATED
),
validation_date=row["validation_date"],
)
@staticmethod
def _row_to_bench_result(row: sqlite3.Row) -> BenchResult:
"""Convert a database row to a ``BenchResult`` dataclass."""
category = row["category"]
return BenchResult(
id=row["id"],
artifact_id=row["artifact_id"],
bench_type=row["bench_type"],
ic_mean=row["ic_mean"],
ic_std=row["ic_std"],
ir=row["ir"],
ic_positive_ratio=row["ic_positive_ratio"],
t_stat=row["t_stat"],
sharpe=row["sharpe"],
annual_return=row["annual_return"],
max_drawdown=row["max_drawdown"],
calmar=row["calmar"],
category=BenchCategory(category) if category else None,
train_start=row["train_start"],
train_end=row["train_end"],
test_start=row["test_start"],
test_end=row["test_end"],
run_dir=row["run_dir"],
created_at=row["created_at"],
)
@staticmethod
def _row_to_decay_snapshot(row: sqlite3.Row) -> DecaySnapshot:
"""Convert a database row to a ``DecaySnapshot`` dataclass."""
signal = row["decay_signal"]
return DecaySnapshot(
id=row["id"],
artifact_id=row["artifact_id"],
rolling_ic_mean=row["rolling_ic_mean"],
rolling_ir=row["rolling_ir"],
baseline_ic_mean=row["baseline_ic_mean"],
ic_ratio=row["ic_ratio"],
decay_signal=DecaySignal(signal) if signal else None,
consecutive_warnings=row["consecutive_warnings"],
detail=row["detail"],
created_at=row["created_at"],
)
# -- Artifact CRUD ------------------------------------------------------
@_synchronized
def register_artifact(self, artifact: Artifact, *, adaptation_binding: dict | None = None) -> str:
"""Register a new artifact. Returns the artifact_id."""
# A brand-new record has no prior validation history, so its implicit
# "current" state is UNVALIDATED — registering directly with
# validation_status=APPROVED would skip the VALIDATED step.
validate_validation_status_transition(
ValidationStatus.UNVALIDATED, artifact.validation_status
)
now = _now_iso()
artifact_id = artifact.id or _new_artifact_id()
created_at = artifact.created_at or now
try:
with self._write_transaction():
self._conn.execute(
"""
INSERT INTO artifacts (
id, type, name, source_paper, source_url, formula_latex,
theme, columns_required, decay_horizon, signal_definition,
entry_rules, exit_rules, position_sizing, universe,
signal_engine_path, run_dir, hypothesis_id, derived_from, status,
created_at, updated_at, disabled_at, disabled_reason,
developer, owner, validator, approver, model_version,
artifact_version, model_tier, intended_use, limitations,
validation_status, validation_date
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
(
artifact_id,
artifact.type.value,
artifact.name,
artifact.source_paper,
artifact.source_url,
artifact.formula_latex,
_json_dumps(list(artifact.theme)),
_json_dumps(list(artifact.columns_required)),
artifact.decay_horizon,
artifact.signal_definition,
artifact.entry_rules,
artifact.exit_rules,
artifact.position_sizing,
artifact.universe,
artifact.signal_engine_path,
artifact.run_dir,
artifact.hypothesis_id,
artifact.derived_from,
artifact.status.value,
created_at,
now,
artifact.disabled_at,
artifact.disabled_reason,
artifact.developer,
artifact.owner,
artifact.validator,
artifact.approver,
artifact.model_version,
artifact.artifact_version,
artifact.model_tier.value if artifact.model_tier else None,
artifact.intended_use,
artifact.limitations,
artifact.validation_status.value,
artifact.validation_date,
),
)
if adaptation_binding is not None:
self._conn.execute(
"INSERT INTO adaptation_bindings VALUES (?, ?)",
(artifact_id, _json_dumps(adaptation_binding)),
)
except sqlite3.IntegrityError as exc:
if "artifacts.name" in str(exc):
raise ValueError(
f"artifact '{artifact.name}' already exists in "
f"universe '{artifact.universe}'"
) from exc
raise
return artifact_id
@_synchronized
def get_artifact(self, artifact_id: str) -> Artifact | None:
"""Get a single artifact by ID. Returns ``None`` if not found."""
row = self._conn.execute(
"SELECT * FROM artifacts WHERE id = ?", (artifact_id,)
).fetchone()
return self._row_to_artifact(row) if row else None
@_synchronized
def get_adaptation_binding(self, artifact_id: str) -> dict | None:
"""Return the immutable input contract of a prepared adaptation."""
row = self._conn.execute(
"SELECT contract FROM adaptation_bindings WHERE artifact_id = ?", (artifact_id,)
).fetchone()
return json.loads(row["contract"]) if row else None
@_synchronized
def list_artifacts(
self,
*,
type: ArtifactType | None = None,
status: ArtifactStatus | None = None,
universe: str | None = None,
limit: int = 100,
) -> Sequence[Artifact]:
"""List artifacts with optional filters, newest first."""
clauses: list[str] = []
params: list[object] = []
if type is not None:
clauses.append("type = ?")
params.append(type.value)
if status is not None:
clauses.append("status = ?")
params.append(status.value)
if universe is not None:
clauses.append("universe = ?")
params.append(universe)
where = ""
if clauses:
where = "WHERE " + " AND ".join(clauses)
rows = self._conn.execute(
f"SELECT * FROM artifacts {where} ORDER BY created_at DESC, rowid DESC LIMIT ?",
[*params, limit],
).fetchall()
return [self._row_to_artifact(row) for row in rows]
@_synchronized
def update_status(
self,
artifact_id: str,
status: ArtifactStatus,
*,
reason: str | None = None,
) -> Artifact | None:
"""Transition an artifact to a new status.
Sets ``disabled_at`` when transitioning to ``DISABLED``.
Clears it when transitioning away.
Returns the updated artifact, or ``None`` if not found.
"""
existing = self.get_artifact(artifact_id)
if existing is None:
return None
now = _now_iso()
disabled_at: str | None = None
disabled_reason: str | None = None
if status is ArtifactStatus.DISABLED:
disabled_at = now
disabled_reason = reason if reason is not None else existing.disabled_reason
elif existing.status is ArtifactStatus.DISABLED:
# Clear disabled fields when moving out of DISABLED.
disabled_at = None
disabled_reason = None
else:
# Preserve existing disabled fields.
disabled_at = existing.disabled_at
disabled_reason = existing.disabled_reason
with self._write_transaction():
self._conn.execute(
"""
UPDATE artifacts
SET status = ?, updated_at = ?, disabled_at = ?, disabled_reason = ?
WHERE id = ?
""",
(status.value, now, disabled_at, disabled_reason, artifact_id),
)
return self.get_artifact(artifact_id)
@_synchronized
def update_artifact(self, artifact: Artifact) -> Artifact | None:
"""Replace an artifact record. Returns updated artifact or ``None``."""
existing = self.get_artifact(artifact.id)
if existing is None:
return None
# Structural enforcement of the validation state machine: an
# unvalidated model can never be updated straight into APPROVED.
validate_validation_status_transition(
existing.validation_status, artifact.validation_status
)
now = _now_iso()
with self._write_transaction():
self._conn.execute(
"""
UPDATE artifacts
SET type = ?, name = ?, source_paper = ?, source_url = ?,
formula_latex = ?, theme = ?, columns_required = ?,
decay_horizon = ?, signal_definition = ?,
entry_rules = ?, exit_rules = ?, position_sizing = ?,
universe = ?, signal_engine_path = ?, run_dir = ?,
hypothesis_id = ?, derived_from = ?, status = ?, updated_at = ?,
disabled_at = ?, disabled_reason = ?,
developer = ?, owner = ?, validator = ?, approver = ?,
model_version = ?, artifact_version = ?, model_tier = ?,
intended_use = ?, limitations = ?, validation_status = ?,
validation_date = ?
WHERE id = ?
""",
(
artifact.type.value,
artifact.name,
artifact.source_paper,
artifact.source_url,
artifact.formula_latex,
_json_dumps(list(artifact.theme)),
_json_dumps(list(artifact.columns_required)),
artifact.decay_horizon,
artifact.signal_definition,
artifact.entry_rules,
artifact.exit_rules,
artifact.position_sizing,
artifact.universe,
artifact.signal_engine_path,
artifact.run_dir,
artifact.hypothesis_id,
artifact.derived_from,
artifact.status.value,
now,
artifact.disabled_at,
artifact.disabled_reason,
artifact.developer,
artifact.owner,
artifact.validator,
artifact.approver,
artifact.model_version,
artifact.artifact_version,
artifact.model_tier.value if artifact.model_tier else None,
artifact.intended_use,
artifact.limitations,
artifact.validation_status.value,
artifact.validation_date,
artifact.id,
),
)
return self.get_artifact(artifact.id)
# -- Bench history ------------------------------------------------------
@staticmethod
def _bench_values(result: BenchResult) -> dict[str, object]:
"""Column values of a bench row other than ``id`` and ``created_at``.
One list serves both the insert and the duplicate check, so a column
added to one cannot be missing from the other.
"""
return {
"artifact_id": result.artifact_id,
"bench_type": result.bench_type,
"ic_mean": result.ic_mean,
"ic_std": result.ic_std,
"ir": result.ir,
"ic_positive_ratio": result.ic_positive_ratio,
"t_stat": result.t_stat,
"sharpe": result.sharpe,
"annual_return": result.annual_return,
"max_drawdown": result.max_drawdown,
"calmar": result.calmar,
"category": result.category.value if result.category else None,
"train_start": result.train_start,
"train_end": result.train_end,
"test_start": result.test_start,
"test_end": result.test_end,
"run_dir": result.run_dir,
}
@_synchronized
def record_bench(self, result: BenchResult) -> int:
"""Record a bench result. Returns the result ID."""
with self._write_transaction():
return self._insert_bench(result)
def _insert_bench(self, result: BenchResult) -> int:
"""Insert within the caller's transaction."""
values = self._bench_values(result)
values["created_at"] = result.created_at or _now_iso()
columns = ", ".join(values)
placeholders = ", ".join("?" for _ in values)
cursor = self._conn.execute(
f"INSERT INTO bench_history ({columns}) VALUES ({placeholders})",
list(values.values()),
)
return cursor.lastrowid # type: ignore[return-value]
@_synchronized
def record_bench_once(self, result: BenchResult) -> int | None:
"""Atomically record a measurement, returning None for a duplicate.
BEGIN IMMEDIATE serializes this check and insert across connections.
Directory, timestamp, bench label and grading do not change evidence.
"""
with self._write_transaction():
if self.has_bench_result(result):
return None
return self._insert_bench(result)
@_synchronized
def get_bench_history(
self,
artifact_id: str,
*,
limit: int = 50,
bench_types: Sequence[str] | None = None,
) -> Sequence[BenchResult]:
"""Get bench history for an artifact, newest first.
Args:
artifact_id: Artifact whose bench rows are returned.
limit: Maximum number of rows.
bench_types: When given, only rows of these bench types are
returned, and ``limit`` applies after the filter.
Returns:
Matching bench results, newest first.
"""
type_clause = ""
params: list[object] = [artifact_id]
if bench_types is not None:
types = list(bench_types)
if not types:
return []
type_clause = f"AND bench_type IN ({', '.join('?' for _ in types)})"
params.extend(types)
params.append(limit)
rows = self._conn.execute(
f"""
SELECT * FROM bench_history
WHERE artifact_id = ? {type_clause}
ORDER BY created_at DESC, id DESC
LIMIT ?
""",
params,
).fetchall()
return [self._row_to_bench_result(row) for row in rows]
@_synchronized
def has_bench_result(self, result: BenchResult) -> bool:
"""Return whether an identical bench row is already stored.
``IS`` rather than ``=`` makes two missing values compare equal, so a
metric absent from both rows still counts as a match.
Args:
result: Candidate measurement; location, time and grading metadata
are ignored.
Returns:
``True`` when a stored measurement has the same artifact, windows and metrics.
"""
values = self._bench_values(result)
for metadata in ("run_dir", "bench_type", "category"):
values.pop(metadata)
where = " AND ".join(f"{column} IS ?" for column in values)
row = self._conn.execute(
f"SELECT 1 FROM bench_history WHERE {where} LIMIT 1",
list(values.values()),
).fetchone()
return row is not None
# -- Decay snapshots ----------------------------------------------------
@_synchronized
def record_decay_snapshot(self, snapshot: DecaySnapshot) -> int:
"""Record a decay monitoring snapshot. Returns the snapshot ID."""
now = _now_iso()
created_at = snapshot.created_at or now
signal_value = snapshot.decay_signal.value if snapshot.decay_signal else None
with self._write_transaction():
cursor = self._conn.execute(
"""
INSERT INTO decay_snapshots (
artifact_id, rolling_ic_mean, rolling_ir,
baseline_ic_mean, ic_ratio, decay_signal,
consecutive_warnings, detail, created_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
(
snapshot.artifact_id,
snapshot.rolling_ic_mean,
snapshot.rolling_ir,
snapshot.baseline_ic_mean,
snapshot.ic_ratio,
signal_value,
snapshot.consecutive_warnings,
snapshot.detail,
created_at,
),
)
return cursor.lastrowid # type: ignore[return-value]
@_synchronized
def get_decay_history(
self, artifact_id: str, *, limit: int = 20
) -> Sequence[DecaySnapshot]:
"""Get decay snapshots for an artifact, newest first."""
rows = self._conn.execute(
"""
SELECT * FROM decay_snapshots
WHERE artifact_id = ?
ORDER BY created_at DESC, id DESC
LIMIT ?
""",
(artifact_id, limit),
).fetchall()
return [self._row_to_decay_snapshot(row) for row in rows]