"Vibe-Trading: Your Personal Trading Agent"
Python ai-agentalgorithmic-tradingbacktestingfintechllmmcpmulti-agentpythonquantitative-financetrading
"""SQLite persistence for immutable portfolio snapshots and FX cache."""
from __future__ import annotations
import json
import sqlite3
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any
from src.config.paths import get_runtime_root
def _derived_totals_are_complete(payload: dict[str, Any]) -> bool:
"""Reject snapshots whose derived Binance total omitted an unpriced asset."""
for account in payload.get("accounts", []):
if account.get("broker") != "binance":
continue
unpriced_count = account.get("unpriced_position_count")
if unpriced_count is not None:
if int(unpriced_count or 0) > 0:
return False
continue
# Compatibility for snapshots written before account-level counts were
# stored: only Binance positions affect a derived account total.
if any(
row.get("broker") == "binance" and not row.get("priced")
for row in payload.get("positions", [])
):
return False
return True
class PortfolioStore:
"""SQLite-backed history of immutable portfolio snapshots plus an FX cache."""
def __init__(self, path: Path | None = None) -> None:
"""Open (and create when missing) the snapshot database.
Args:
path: Database file path. Defaults to
``portfolio/portfolio.sqlite3`` under the runtime root
(``~/.vibe-trading``).
"""
self.path = path or (get_runtime_root() / "portfolio" / "portfolio.sqlite3")
self.path.parent.mkdir(parents=True, exist_ok=True)
self._init_schema()
def _connect(self) -> sqlite3.Connection:
connection = sqlite3.connect(self.path)
connection.row_factory = sqlite3.Row
return connection
def _init_schema(self) -> None:
with self._connect() as db:
db.executescript("""
CREATE TABLE IF NOT EXISTS portfolio_snapshots (
id TEXT PRIMARY KEY,
created_at TEXT NOT NULL,
complete INTEGER NOT NULL,
total_usd TEXT NOT NULL,
total_cny TEXT NOT NULL,
payload TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_portfolio_snapshots_created
ON portfolio_snapshots(created_at DESC);
CREATE TABLE IF NOT EXISTS portfolio_fx_quotes (
id INTEGER PRIMARY KEY CHECK (id = 1), payload TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS portfolio_fx_cache (
base TEXT NOT NULL,
quote TEXT NOT NULL,
rate TEXT NOT NULL,
fetched_at TEXT NOT NULL,
PRIMARY KEY (base, quote)
);
""")
def save_snapshot(self, payload: dict[str, Any]) -> None:
"""Append one immutable snapshot.
Args:
payload: The full snapshot envelope produced by
:meth:`src.portfolio.service.PortfolioService.refresh`.
"""
totals = payload["totals"]
with self._connect() as db:
db.execute(
"""
INSERT INTO portfolio_snapshots
(id, created_at, complete, total_usd, total_cny, payload)
VALUES (?, ?, ?, ?, ?, ?)
""",
(
payload["snapshot_id"],
payload["created_at"],
int(payload["complete"]),
str(totals["usd"]),
str(totals["cny"]),
json.dumps(payload, ensure_ascii=False, separators=(",", ":")),
),
)
def latest(self, *, complete_only: bool = False) -> dict[str, Any] | None:
"""Return the most recent stored snapshot.
Args:
complete_only: Restrict the lookup to snapshots in which every
enabled source refreshed successfully.
Returns:
The snapshot envelope, or ``None`` when nothing matches.
"""
where = "WHERE complete = 1" if complete_only else ""
limit = 2000 if complete_only else 1
with self._connect() as db:
rows = db.execute(
f"SELECT payload FROM portfolio_snapshots {where} ORDER BY created_at DESC LIMIT ?",
(limit,),
).fetchall()
for row in rows:
payload = json.loads(row["payload"])
if not complete_only or _derived_totals_are_complete(payload):
return payload
return None
def get(self, snapshot_id: str) -> dict[str, Any] | None:
"""Return one immutable snapshot by id.
Args:
snapshot_id: Exact persisted snapshot identifier.
Returns:
The stored snapshot envelope, or None when the id is absent.
"""
with self._connect() as db:
row = db.execute(
"SELECT payload FROM portfolio_snapshots WHERE id = ? LIMIT 1",
(str(snapshot_id),),
).fetchone()
return json.loads(row["payload"]) if row else None
def history(
self,
limit: int = 180,
*,
complete_only: bool = True,
valuation_version: int | None = None,
) -> list[dict[str, Any]]:
"""Return snapshot totals over time, oldest first.
Args:
limit: Maximum number of snapshots to read, clamped to 1..2000.
complete_only: Restrict the series to snapshots in which every
enabled source refreshed successfully. Charting a mix of
complete and partial snapshots would draw a value drop that
never happened.
valuation_version: Restrict the series to snapshots produced by
one valuation methodology. Legacy rows are retained but cannot
create false changes beside a newer methodology.
Returns:
One row per snapshot with id, timestamp, completeness and totals.
"""
row_limit = max(1, min(int(limit), 2000))
where = "WHERE complete = 1" if complete_only else ""
with self._connect() as db:
rows = db.execute(
f"""
SELECT id, created_at, complete, total_usd, total_cny, payload
FROM portfolio_snapshots
{where}
ORDER BY created_at DESC LIMIT ?
""",
(2000 if valuation_version is not None or complete_only else row_limit,),
).fetchall()
history = []
for row in rows:
payload = json.loads(row["payload"])
if valuation_version is not None or complete_only:
if valuation_version is not None and payload.get("valuation_version") != valuation_version:
continue
if complete_only and not _derived_totals_are_complete(payload):
continue
total_hkd = None
try:
usd_hkd = Decimal(str((payload.get("fx") or {}).get("usd_hkd")))
usd = Decimal(row["total_usd"])
if usd_hkd.is_finite() and usd_hkd > 0 and usd.is_finite():
total_hkd = str(usd * usd_hkd)
except (InvalidOperation, ValueError, TypeError):
pass # Historical snapshots without FX stay explicitly unpriced.
history.append(
{
"id": row["id"],
"created_at": row["created_at"],
"complete": row["complete"],
"total_usd": row["total_usd"],
"total_cny": row["total_cny"],
"total_hkd": total_hkd,
"total_eur": (str(Decimal(row["total_usd"]) * Decimal(str(payload["fx"]["rates"]["EUR"])))
if payload.get("fx", {}).get("rates", {}).get("EUR") else None),
}
)
if len(history) >= row_limit:
break
return list(reversed(history))
def latest_successful_source(self, source_id: str) -> dict[str, Any] | None:
"""Return the newest successfully read payload for one configured source.
Only accounts with status ``ok`` qualify, so a run of failed refreshes
keeps pointing back at the original successful observation and its real
``last_success_at`` timestamp. The payload is used to report *when* a
failed source was last healthy; it is never added back into a snapshot's
totals.
Args:
source_id: The configured source id (the local connection id).
Returns:
A dict with ``created_at``, ``account`` and ``positions`` for the
newest successful observation, or ``None`` if there is none.
"""
with self._connect() as db:
rows = db.execute("""
SELECT created_at, payload
FROM portfolio_snapshots
ORDER BY created_at DESC LIMIT 2000
""").fetchall()
for row in rows:
payload = json.loads(row["payload"])
account = next(
(
item
for item in payload.get("accounts", [])
if (item.get("source_id") or item.get("broker")) == source_id
and item.get("status") == "ok"
),
None,
)
if account is None:
continue
return {
"created_at": str(account.get("last_success_at") or row["created_at"]),
"account": account,
"positions": [
item
for item in payload.get("positions", [])
if (item.get("source_id") or item.get("broker")) == source_id
],
}
return None
def save_fx(self, base: str, quote: str, rate: str, fetched_at: str) -> None:
"""Cache one FX rate so a later outage still has a usable number.
Args:
base: Base currency code, e.g. ``USD``.
quote: Quote currency code, e.g. ``CNY``.
rate: The rate, stored as text to avoid binary float drift.
fetched_at: ISO-8601 timestamp of the successful fetch.
"""
with self._connect() as db:
db.execute(
"""
INSERT INTO portfolio_fx_cache (base, quote, rate, fetched_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(base, quote) DO UPDATE SET
rate = excluded.rate, fetched_at = excluded.fetched_at
""",
(base, quote, rate, fetched_at),
)
def load_fx(self, base: str, quote: str) -> tuple[str, str] | None:
"""Read the last cached rate for one currency pair.
Args:
base: Base currency code, e.g. ``USD``.
quote: Quote currency code, e.g. ``CNY``.
Returns:
``(rate, fetched_at)`` or ``None`` when the pair was never cached.
"""
with self._connect() as db:
row = db.execute(
"SELECT rate, fetched_at FROM portfolio_fx_cache WHERE base = ? AND quote = ?",
(base, quote),
).fetchone()
return (str(row["rate"]), str(row["fetched_at"])) if row else None
def save_fx_quote(self, payload: dict[str, Any]) -> None:
"""Atomically cache a whole observation, preserving its rate date."""
with self._connect() as db:
db.execute("INSERT OR REPLACE INTO portfolio_fx_quotes VALUES (1, ?)", (json.dumps(payload),))
def load_fx_quote(self) -> dict[str, Any] | None:
"""Read one complete cached observation, never a mixture of rate dates."""
with self._connect() as db:
row = db.execute("SELECT payload FROM portfolio_fx_quotes WHERE id = 1").fetchone()
return json.loads(row[0]) if row else None