Sydnee nightly — persistence audit — 2026-04-16 P0 findings: 0 P1 findings: 2 Risks: 3 - Area: persistence - Day of week: Thursday (3) - Files/sources scanned: 5 (bot.py, core/database.py, core/schema.sql, core/persistence.py, docs/strategy_decisions.md) - Recent fixes cross-checked: 350bd05 (persistence overhaul, 2026-04-15), a6be962 (trimmed_3/obv/extreme_since_trim) - Bugs found (P0): 0 - Bugs found (P1): 2 - Risks noted: 3 --- Full report (dev branch): https://github.com/kanex1/sydnee.signals/blob/dev/docs/audit_2026-04-16_persistence.md Reply FROM [email protected] to [email protected] to request fixes, e.g.: "code_task on sydnee-signals-dev: apply fix for the P0 about RVOL threshold in bot.py" Sydnee Agent will propose + you APPROVE (or plain 'approve') + auto-push to dev. --- Full audit below (first 12 KB) --- # Nightly Audit 2026-04-16 — PERSISTENCE ## Summary - Area: persistence - Day of week: Thursday (3) - Files/sources scanned: 5 (bot.py, core/database.py, core/schema.sql, core/persistence.py, docs/strategy_decisions.md) - Recent fixes cross-checked: 350bd05 (persistence overhaul, 2026-04-15), a6be962 (trimmed_3/obv/extreme_since_trim) - Bugs found (P0): 0 - Bugs found (P1): 2 - Risks noted: 3 --- ## Findings ### BUG [P1]: `ensure_trade_state_columns()` missing 6 classification columns **File:** `core/database.py:173` **Pattern:** Schema migration gap — columns in schema.sql not in ALTER TABLE migration **Evidence:** `schema.sql` (added in 350bd05) now contains: ```sql trigger_type, trigger_direction, rsi_at_entry, bias_15m, bias_15m_rsi, sentiment_agrees, size_pct, regime_at_entry ``` All 8 are included in `INSERT INTO trades` (database.py:108). `ensure_trade_state_columns()` runs on every startup to backfill missing columns on older DBs, but it only adds the state-machine columns (`trimmed_1/2/3`, `obv_be_applied`, `extreme_since_trim`, `last_eod_trim_time`, `pending_reversal`, `force_trim_pending/2`, `force_trim_reason`) — not the classification columns. On any DB deployed before the prior classification column additions (which predate 350bd05), the INSERT will fail silently (caught by `_safe` → `logger.warning("DB insert_trade failed for %s")`). Trade is placed in IBKR but has no DB row — lost completely on restart. **Impact:** Silent data loss on INSERT for any DB where classification columns don't exist. Dev DB is fine (columns added ad-hoc), but a restored backup or cloned DB would fail every trade INSERT. **Fix:** Add 6 ALTER TABLE lines to `ensure_trade_state_columns()` in `core/database.py`: ```python "ALTER TABLE trades ADD COLUMN IF NOT EXISTS trigger_type VARCHAR(30) DEFAULT ''", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS trigger_direction VARCHAR(10) DEFAULT ''", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS rsi_at_entry DECIMAL(8,4)", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS bias_15m VARCHAR(10) DEFAULT ''", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS bias_15m_rsi DECIMAL(8,4)", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS sentiment_agrees BOOLEAN", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS size_pct INT", "ALTER TABLE trades ADD COLUMN IF NOT EXISTS regime_at_entry VARCHAR(30) DEFAULT ''", ``` --- ### BUG [P1]: `_eod_trim2`/`_eod_trim3` suffix stripper matches `_trim2` — wrong `parent_id` **File:** `bot.py:5038–5071` (suffix-strip loop + elif chain in `_close_trade`) **Pattern:** Incorrect string suffix matching — multi-EOD trim suffix contains `_trim2` **Evidence:** Suffix-strip loop (bot.py ~5038): ```python for suf in ("_trim3", "_eod_trim", "_trim2", "_trim"): if parent_id.endswith(suf): parent_id = parent_id[:-len(suf)] break ``` For `trade_id = "TSLA_abc_eod_trim2"`: - `endswith("_eod_trim")` → **False** (ends in `2`) - `endswith("_trim2")` → **True** → `parent_id = "TSLA_abc_eod"` (wrong) Then: `elif trade.trade_id.endswith("_trim2"):` → True → `self.db.update_trade("TSLA_abc_eod", trimmed_2=True)` — writes to a nonexistent trade_id. **Functional impact:** Low — `eod_trimmed=True` was already written to the correct parent at fire time (`_update_trade_field` in EOD trim loop, bot.py ~2439). The spurious write targets a nonexistent row (PostgreSQL UPDATE no-ops on no match). But the stale comment at line ~5063 (`# no DB column for trim3 yet`) adds confusion since `trimmed_3` IS now a real column. **Fix:** Add numbered EOD variants before `_trim2` in the suffix list: ```python for suf in ("_trim3", "_eod_trim3", "_eod_trim2", "_eod_trim", "_trim2", "_trim"): ``` And update the stale comment: `pass # trimmed_3 already written at fire-site (line ~2936)` --- ### RISK: `position_size_pct` always 100.0 — never populated by bot **File:** `core/database.py:96`, `bot.py:779–797` **Pattern:** Default parameter silently masking missing argument **Evidence:** `insert_trade()` signature: `position_size_pct: float = 100.0` `_add_trade()` call in bot.py never passes `position_size_pct`: ```python self.db.insert_trade( ... size_pct=meta.get("size_pct"), # correct column # position_size_pct not passed → defaults to 100.0 ) ``` Every row in the `trades` table has `position_size_pct = 100` regardless of actual size. The parallel column `size_pct` (INT) correctly holds the real value. Any analytics, reporting, or SQL query that uses `position_size_pct` instead of `size_pct` will see flat 100% for all trades — including off-hours entries that should be 25% and close-hour entries at 10%. **Fix:** Either pass `position_size_pct=meta.get("size_pct", 100)` to keep columns in sync, or deprecate `position_size_pct` and document `size_pct` as the authoritative field. --- ### RISK: `vix_at_entry` / `vix_at_exit` not in schema, never persisted **File:** `bot.py:772–774, 827`, `core/persistence.py:19` (Trade dataclass) **Pattern:** In-memory-only state on Trade object **Evidence:** `core/persistence.py` Trade dataclass has `vix_at_entry`, `vix_at_exit` fields. Bot sets them: ```python trade.vix_at_entry = self.vix.current_vix() if self.vix else None # _add_trade, line 772 t.vix_at_exit = _vix_exit # _close_trade_in_store, line 827 ``` Neither column exists in `core/schema.sql` or `core/database.py`. Not in `INSERT INTO trades`, not in `close_trade` UPDATE, not in `ensure_trade_state_columns`. Lost on restart; close log shows `VIX=N/A` for any resumed trade. **Impact:** Logging only — not used in any trade logic. Minor data loss for analytics (VIX regime analysis at entry/exit). **Fix:** Add columns to schema and migrate; or remove from Trade dataclass if not worth persisting. Low priority, post-go-live. --- ### RISK: `trade._setup_mode` not persisted, not restored on load **File:** `bot.py:4303` (set), no restore in `_load_trades_from_db` **Pattern:** In-memory-only attribute, no DB column **Evidence:** ```python trade._setup_mode = setup_mode # set in _open_trade, line 4303 ``` Not in schema.sql, not in `ensure_trade_state_columns`, not in the restore loop (bot.py:680–754). `getattr(trade, '_setup_mode', None)` returns `None` for any trade reloaded after restart. **Impact:** Used in push notifications only (line 4362). No trade logic depends on it. Minor cosmetic loss. --- ## OK (checked, working or recently fixed) - **`_trimmed` / `_trimmed_2` / `_trimmed_3`** — persisted via `_update_trade_field(trimmed_1/2/3=True)` at fire site; restored from `row["trimmed_1/2/3"]` in `_load_trades_from_db`. ✓ - **`_eod_trimmed`** — persisted at fire site (line 2439–2442); restored (line 719–720). ✓ - **`_obv_be_applied`** — persisted at OBV-BE apply site (line 2659/2661); restored (line 725–726). ✓ - **`_extreme_since_trim`** — persisted every bar it updates (line 3073/3082); restored (line 730–731). ✓ - **`_last_eod_trim_time`** — persisted immediately after EOD trim (line 2439); restored (line 734–735). ✓ - **`_pending_reversal`** — persisted at all 8 arm/cancel/consume sites; restored (line 738–739). ✓ - **`_rsi_force_trim` / `_rsi_force_trim2`** — persisted via `force_trim_pending/force_trim2_pending`; restored (line 742–745). ✓ - **`_force_trim_reason`** — persisted alongside force-trim flag; restored (line 746–747). ✓ - **`_trim_price` / `_trim2_price`** — derived from `trim1_price`/`trim2_price` (persisted at INSERT); restored (line 749–752). ✓ - **`_eod_trim_count`** — intentionally lazy: `None` on restore → rebuilt from DB child trade count on first use (line 2406–2413). ✓ - **`_close_order_kwargs`** — manual limit close state; ephemeral by design (user resubmits after restart). ✓ - **schema.sql vs INSERT columns** — all 22 INSERT columns now in schema.sql after 350bd05. ✓ - **`ensure_trade_state_columns()` state columns** — all 13 state-machine columns covered by ALTER TABLE migration. ✓ - **Deferred close dedup** — `seen_close_ids` set prevents double-close on restart (bot.py ~3617–3626). ✓ - **Prior P0 (schema.sql drift)** — fixed in 350bd05. Already addressed. - **Prior P1 (trim flag write lag, _pending_reversal, _rsi_force_trim, _last_eod_trim_time, _force_trim_reason)** — all fixed in 350bd05. Already addressed.