fix: lazy session resolver and one-time session_id backfill

src/db.py

@@ -31,8 +31,12 @@ def init_db(db_path: str) -> sqlite3.Connection:
             quantity INTEGER,
             price REAL,
             pnl REAL DEFAULT 0.0,
+            strategy_pnl REAL DEFAULT 0.0,
+            fx_pnl REAL DEFAULT 0.0,
             market TEXT DEFAULT 'KR',
             exchange_code TEXT DEFAULT 'KRX',
+            session_id TEXT DEFAULT 'UNKNOWN',
+            selection_context TEXT,
             decision_id TEXT,
             mode TEXT DEFAULT 'paper'
         )
@@ -53,6 +57,32 @@ def init_db(db_path: str) -> sqlite3.Connection:
         conn.execute("ALTER TABLE trades ADD COLUMN decision_id TEXT")
     if "mode" not in columns:
         conn.execute("ALTER TABLE trades ADD COLUMN mode TEXT DEFAULT 'paper'")
+    session_id_added = False
+    if "session_id" not in columns:
+        conn.execute("ALTER TABLE trades ADD COLUMN session_id TEXT DEFAULT 'UNKNOWN'")
+        session_id_added = True
+    if "strategy_pnl" not in columns:
+        conn.execute("ALTER TABLE trades ADD COLUMN strategy_pnl REAL DEFAULT 0.0")
+    if "fx_pnl" not in columns:
+        conn.execute("ALTER TABLE trades ADD COLUMN fx_pnl REAL DEFAULT 0.0")
+    # Backfill legacy rows where only pnl existed before split accounting columns.
+    conn.execute(
+        """
+        UPDATE trades
+        SET strategy_pnl = pnl, fx_pnl = 0.0
+        WHERE pnl != 0.0
+          AND strategy_pnl = 0.0
+          AND fx_pnl = 0.0
+        """
+    )
+    if session_id_added:
+        conn.execute(
+            """
+            UPDATE trades
+            SET session_id = 'UNKNOWN'
+            WHERE session_id IS NULL OR session_id = ''
+            """
+        )
 
     # Context tree tables for multi-layered memory management
     conn.execute(
@@ -171,8 +201,11 @@ def log_trade(
     quantity: int = 0,
     price: float = 0.0,
     pnl: float = 0.0,
+    strategy_pnl: float | None = None,
+    fx_pnl: float | None = None,
     market: str = "KR",
     exchange_code: str = "KRX",
+    session_id: str | None = None,
     selection_context: dict[str, any] | None = None,
     decision_id: str | None = None,
     mode: str = "paper",
@@ -187,24 +220,37 @@ def log_trade(
         rationale: AI decision rationale
         quantity: Number of shares
         price: Trade price
-        pnl: Profit/loss
+        pnl: Total profit/loss (backward compatibility)
+        strategy_pnl: Strategy PnL component
+        fx_pnl: FX PnL component
         market: Market code
         exchange_code: Exchange code
+        session_id: Session identifier (if omitted, auto-derived from market)
         selection_context: Scanner selection data (RSI, volume_ratio, signal, score)
         decision_id: Unique decision identifier for audit linking
         mode: Trading mode ('paper' or 'live') for data separation
     """
     # Serialize selection context to JSON
     context_json = json.dumps(selection_context) if selection_context else None
+    resolved_session_id = _resolve_session_id(market=market, session_id=session_id)
+    if strategy_pnl is None and fx_pnl is None:
+        strategy_pnl = pnl
+        fx_pnl = 0.0
+    elif strategy_pnl is None:
+        strategy_pnl = pnl - float(fx_pnl or 0.0) if pnl != 0.0 else 0.0
+    elif fx_pnl is None:
+        fx_pnl = pnl - float(strategy_pnl) if pnl != 0.0 else 0.0
+    if pnl == 0.0 and (strategy_pnl or fx_pnl):
+        pnl = float(strategy_pnl) + float(fx_pnl)
 
     conn.execute(
         """
         INSERT INTO trades (
             timestamp, stock_code, action, confidence, rationale,
-            quantity, price, pnl, market, exchange_code, selection_context, decision_id,
+            quantity, price, pnl, strategy_pnl, fx_pnl,
-            mode
+            market, exchange_code, session_id, selection_context, decision_id, mode
         )
-        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
         """,
         (
             datetime.now(UTC).isoformat(),
@@ -215,8 +261,11 @@ def log_trade(
             quantity,
             price,
             pnl,
+            strategy_pnl,
+            fx_pnl,
             market,
             exchange_code,
+            resolved_session_id,
             context_json,
             decision_id,
             mode,
@@ -225,6 +274,21 @@ def log_trade(
     conn.commit()
 
 
+def _resolve_session_id(*, market: str, session_id: str | None) -> str:
+    if session_id:
+        return session_id
+    try:
+        from src.core.order_policy import classify_session_id
+        from src.markets.schedule import MARKETS
+
+        market_info = MARKETS.get(market)
+        if market_info is not None:
+            return classify_session_id(market_info)
+    except Exception:
+        pass
+    return "UNKNOWN"
+
+
 def get_latest_buy_trade(
     conn: sqlite3.Connection, stock_code: str, market: str
 ) -> dict[str, Any] | None:

tests/test_db.py

@@ -155,6 +155,9 @@ def test_mode_column_exists_in_schema() -> None:
     cursor = conn.execute("PRAGMA table_info(trades)")
     columns = {row[1] for row in cursor.fetchall()}
     assert "mode" in columns
+    assert "session_id" in columns
+    assert "strategy_pnl" in columns
+    assert "fx_pnl" in columns
 
 
 def test_mode_migration_adds_column_to_existing_db() -> None:
@@ -182,6 +185,13 @@ def test_mode_migration_adds_column_to_existing_db() -> None:
                 decision_id TEXT
             )"""
         )
+        old_conn.execute(
+            """
+            INSERT INTO trades (
+                timestamp, stock_code, action, confidence, rationale, quantity, price, pnl
+            ) VALUES ('2026-01-01T00:00:00+00:00', 'AAPL', 'SELL', 90, 'legacy', 1, 100.0, 123.45)
+            """
+        )
         old_conn.commit()
         old_conn.close()
 
@@ -190,6 +200,132 @@ def test_mode_migration_adds_column_to_existing_db() -> None:
         cursor = conn.execute("PRAGMA table_info(trades)")
         columns = {row[1] for row in cursor.fetchall()}
         assert "mode" in columns
+        assert "session_id" in columns
+        assert "strategy_pnl" in columns
+        assert "fx_pnl" in columns
+        migrated = conn.execute(
+            "SELECT pnl, strategy_pnl, fx_pnl, session_id FROM trades WHERE stock_code='AAPL' LIMIT 1"
+        ).fetchone()
+        assert migrated is not None
+        assert migrated[0] == 123.45
+        assert migrated[1] == 123.45
+        assert migrated[2] == 0.0
+        assert migrated[3] == "UNKNOWN"
         conn.close()
     finally:
         os.unlink(db_path)
+
+
+def test_log_trade_stores_strategy_and_fx_pnl_separately() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="AAPL",
+        action="SELL",
+        confidence=90,
+        rationale="fx split",
+        pnl=120.0,
+        strategy_pnl=100.0,
+        fx_pnl=20.0,
+        market="US_NASDAQ",
+        exchange_code="NASD",
+    )
+    row = conn.execute(
+        "SELECT pnl, strategy_pnl, fx_pnl FROM trades ORDER BY id DESC LIMIT 1"
+    ).fetchone()
+    assert row is not None
+    assert row[0] == 120.0
+    assert row[1] == 100.0
+    assert row[2] == 20.0
+
+
+def test_log_trade_backward_compat_sets_strategy_pnl_from_pnl() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="005930",
+        action="SELL",
+        confidence=80,
+        rationale="legacy",
+        pnl=50.0,
+        market="KR",
+        exchange_code="KRX",
+    )
+    row = conn.execute(
+        "SELECT pnl, strategy_pnl, fx_pnl FROM trades ORDER BY id DESC LIMIT 1"
+    ).fetchone()
+    assert row is not None
+    assert row[0] == 50.0
+    assert row[1] == 50.0
+    assert row[2] == 0.0
+
+
+def test_log_trade_partial_fx_input_does_not_infer_negative_strategy_pnl() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="AAPL",
+        action="SELL",
+        confidence=70,
+        rationale="fx only",
+        pnl=0.0,
+        fx_pnl=10.0,
+        market="US_NASDAQ",
+        exchange_code="NASD",
+    )
+    row = conn.execute(
+        "SELECT pnl, strategy_pnl, fx_pnl FROM trades ORDER BY id DESC LIMIT 1"
+    ).fetchone()
+    assert row is not None
+    assert row[0] == 10.0
+    assert row[1] == 0.0
+    assert row[2] == 10.0
+
+
+def test_log_trade_persists_explicit_session_id() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="AAPL",
+        action="BUY",
+        confidence=70,
+        rationale="session test",
+        market="US_NASDAQ",
+        exchange_code="NASD",
+        session_id="US_PRE",
+    )
+    row = conn.execute("SELECT session_id FROM trades ORDER BY id DESC LIMIT 1").fetchone()
+    assert row is not None
+    assert row[0] == "US_PRE"
+
+
+def test_log_trade_auto_derives_session_id_when_not_provided() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="005930",
+        action="BUY",
+        confidence=70,
+        rationale="auto session",
+        market="KR",
+        exchange_code="KRX",
+    )
+    row = conn.execute("SELECT session_id FROM trades ORDER BY id DESC LIMIT 1").fetchone()
+    assert row is not None
+    assert row[0] != "UNKNOWN"
+
+
+def test_log_trade_unknown_market_falls_back_to_unknown_session() -> None:
+    conn = init_db(":memory:")
+    log_trade(
+        conn=conn,
+        stock_code="X",
+        action="BUY",
+        confidence=70,
+        rationale="unknown market",
+        market="MARS",
+        exchange_code="MARS",
+    )
+    row = conn.execute("SELECT session_id FROM trades ORDER BY id DESC LIMIT 1").fetchone()
+    assert row is not None
+    assert row[0] == "UNKNOWN"