# Spec: Sweep Results Database ## Goal Accumulate GPU sweep results across all runs into a persistent database. Over time this becomes a rich dataset for: - **Data-driven range tuning**: which param values consistently appear in top results → tighten ranges there - **Sign stability**: does `i_w_dxy` always go negative across timeframes/assets? → can be locked - **Cross-asset patterns**: which param combos are robust vs overfitted to one asset - **Regime discovery**: are there multiple distinct param "modes" that all work well? - **Future ML use**: train a lightweight model to predict good starting points --- ## Format: SQLite | Concern | CSV | SQLite | |---|---|---| | Schema evolution (new params) | New column, old rows get NaN | `ALTER TABLE ADD COLUMN`, same effect | | Deduplication | Requires pandas merge on key columns | `INSERT OR IGNORE` on primary key | | Querying (e.g. top-100 by asset+timeframe) | Pandas read+filter (fine at <1M rows) | Native SQL, faster at scale | | File inspection | Open in Excel/VSCode directly | Requires DB browser or Python | | Portability | Simple, no dependencies | Requires sqlite3 (stdlib) | **Decision: SQLite.** Deduplication and indexed querying justify the small complexity overhead. `sqlite3` is stdlib. Database file: `results/sweep_database.db` --- ## Row Retention Policy Store **all CPU-verified rows with `pnl_dd_ratio > 0`** (not just the winner, and not a hard top-N cap). Rationale: stability and commonality analysis requires seeing the *distribution* of param values across many results — not just the peak. If `i_w_dxy` is negative in 300 of the top 300 results for BTC-1D, that's a very different signal than if it's negative in only 3 out of 5. In practice, far fewer than 5000 candidates per iteration will pass the drawdown cap (40%), so growth is naturally bounded. If it becomes a concern, add a floor: `pnl_dd_ratio >= median(all positive results this iteration)`. --- ## Schema ```sql CREATE TABLE IF NOT EXISTS sweep_results ( -- Row identity id INTEGER PRIMARY KEY AUTOINCREMENT, asset TEXT NOT NULL, timeframe TEXT NOT NULL, schema_ver INTEGER NOT NULL DEFAULT 1, -- bump when param set changes recorded_at TEXT NOT NULL, -- ISO timestamp of this write run_id TEXT, -- UUID per auto_optimize_loop.py invocation train_start TEXT, -- e.g. "2015-01-01" (config at time of run) train_end TEXT, -- e.g. "2024-12-31" (holdout boundary) score_start TEXT, -- e.g. "2019-01-01" -- GPU metrics (always present) calmar_ratio REAL, sortino_ratio REAL, sharpe_ratio REAL, -- CPU-verified metrics pnl_dd_ratio REAL, -- optimization metric; NULL = not verified pnl_dd_percentile REAL, -- 0-100, rank within this iteration's verified results total_pnl_pct REAL, max_drawdown REAL, total_trades INTEGER, pct_in_market REAL, -- Thresholds i_long_entry_activation_threshold REAL, i_long_exit_activation_threshold REAL, i_long_exit_activation_confirmation_threshold REAL, i_use_long_exit_confirmation INTEGER, -- 0/1 i_use_long_entry_confirmation INTEGER, i_trailing_stop_threshold REAL, -- Core weights i_w_stoch REAL, i_w_macd_pred REAL, i_w_osc REAL, i_w_macd_bullish REAL, i_w_m3_momentum REAL, i_m3_momentum_period REAL, i_w_m2_tiny REAL, i_w_rsid_osc REAL, i_w_stoch_div_osc REAL, i_w_vwap_div_osc REAL, i_w_stoch_peaking REAL, i_w_stoch_bottoming REAL, i_w_m3_div_osc REAL, i_w_m2_div_osc REAL, i_w_m2_div_osc_noOffset REAL, -- Candlestick i_w_bearish_engulfing REAL, i_w_bullish_hammer REAL, i_w_bullish_engulfing REAL, i_w_shooting_star REAL, i_cs_body_quality_ratio REAL, i_cs_confidence_scaling_factor REAL, i_cs_bull_eng_scaling_factor REAL, -- Macro i_w_btc_spx_corr REAL, i_w_dxy REAL, i_w_vix REAL, i_w_btc_dom REAL, i_w_us10y REAL, i_w_spy REAL, i_w_gold REAL ); CREATE INDEX IF NOT EXISTS idx_asset_tf ON sweep_results (asset, timeframe); CREATE INDEX IF NOT EXISTS idx_pnl_dd ON sweep_results (pnl_dd_ratio); CREATE INDEX IF NOT EXISTS idx_run_id ON sweep_results (run_id); CREATE INDEX IF NOT EXISTS idx_schema_ver ON sweep_results (schema_ver); ``` --- ## Schema Evolution (Adding New Params) 1. `ALTER TABLE sweep_results ADD COLUMN i_w_newparam REAL` — old rows get NULL 2. Bump `schema_ver` constant in code 3. For analysis, use `WHERE schema_ver >= N` to compare apples-to-apples Old rows remain valid for all existing-param analysis. Only wipe if a param fundamentally changes *meaning* (e.g., weight scale changes from [-100,100] to [-1,1]). --- ## Wiping / Resetting ```python # Delete one combo: conn.execute("DELETE FROM sweep_results WHERE asset=? AND timeframe=?", (asset, tf)) # Delete old schema versions: conn.execute("DELETE FROM sweep_results WHERE schema_ver < ?", (current_ver,)) # Delete a specific run: conn.execute("DELETE FROM sweep_results WHERE run_id=?", (run_id,)) ``` Expose via CLI flag on `auto_optimize_loop.py`: `--reset-db [ASSET TIMEFRAME]` --- ## Integration Point Add `write_to_sweep_db(verified_results, asset, timeframe, run_id)` call at the end of `verify_top_results` in [auto_optimize_loop.py](../auto_optimize_loop.py), after sorting but before returning the winner. Compute `pnl_dd_percentile` from the verified results list before writing: ```python scores = [r.get("P&L/DD Ratio", 0) for r in verified_results if r.get("P&L/DD Ratio", 0) > 0] scores_sorted = sorted(scores) for r in verified_results: s = r.get("P&L/DD Ratio", 0) if s > 0: r["pnl_dd_percentile"] = 100 * scores_sorted.index(s) / max(len(scores_sorted)-1, 1) ``` --- ## Interesting Things to Extract ### 1. Sign Stability — which weights have a "correct" direction Identify params that are almost always positive or almost always negative across top results. A weight that's 95% negative across all combos and top deciles is effectively a known constant; future optimization could lock its sign and narrow its range. ```sql -- Sign consistency for all weight params (top-quartile results, all combos) SELECT AVG(CASE WHEN i_w_dxy > 0 THEN 1.0 ELSE 0.0 END) AS dxy_pct_pos, AVG(CASE WHEN i_w_vix > 0 THEN 1.0 ELSE 0.0 END) AS vix_pct_pos, AVG(CASE WHEN i_w_btc_spx_corr > 0 THEN 1.0 ELSE 0.0 END) AS spxcorr_pct_pos, -- repeat for all i_w_* ... COUNT(*) as n FROM sweep_results WHERE pnl_dd_percentile >= 75 AND schema_ver = 1; ``` *Potential insight*: macro signals like DXY and VIX likely have a stable sign (bearish DXY → bullish BTC). Confirming this from thousands of search results would let you lock the sign and double the effective search density on that param. --- ### 2. Param Stability Across Timeframes (within one asset) Which params shift systematically as timeframe increases? Which are flat? ```python # Pull into pandas, compute std per param per timeframe df = pd.read_sql(""" SELECT timeframe, i_w_stoch, i_w_macd_pred, i_w_dxy, ... FROM sweep_results WHERE asset='COINBASE_BTCUSD' AND pnl_dd_percentile >= 50 """, conn) df.groupby("timeframe")[weight_cols].agg(["mean","std"]).T ``` *Potential insight*: some weights may be stable across 4H/6H/8H but shift on 1D. Those stable ones could be shared across a multi-timeframe strategy, reducing the search space. --- ### 3. Cross-Asset Universality Which params are consistent across BTC, ETH, SOL, LINK? Universal params might represent genuine market microstructure signals rather than asset-specific noise. ```sql -- For each param, compute range of mean values across assets (lower = more universal) SELECT asset, AVG(i_w_vix) AS vix_mean, AVG(i_w_dxy) AS dxy_mean, COUNT(*) AS n FROM sweep_results WHERE timeframe='1D' AND pnl_dd_percentile >= 75 GROUP BY asset; ``` *Potential insight*: a param where all 4 assets agree on sign/magnitude is a candidate for a "universal" weight that gets fixed, freeing the optimizer to focus on asset-specific params. --- ### 4. Multi-Modal Distributions — are there distinct strategies hiding in the data? Some params might have two valid operating modes (e.g., `i_w_stoch` might cluster around -80 OR +80 — both work but represent different strategies). K-means on the top-N results per combo would reveal this. ```python from sklearn.cluster import KMeans df_top = df[df["pnl_dd_percentile"] >= 90] kmeans = KMeans(n_clusters=3) df_top["cluster"] = kmeans.fit_predict(df_top[weight_cols].dropna()) df_top.groupby("cluster")[weight_cols + ["pnl_dd_ratio"]].mean() ``` *Potential insight*: if cluster A (bearish-leaning weights) and cluster B (momentum-following weights) both yield high P&L/DD, you effectively have two distinct strategies. You'd want to compare their out-of-sample behavior on 2025 data separately. **Spec implication**: store enough rows (all positive, not top-N) so clusters aren't artificially truncated. --- ### 5. Degenerate State Detection Flag param sets that are likely to be degenerate regardless of their score: - Entry threshold ≈ Exit threshold (within 10 units) → the ETH 4H bug - Entry threshold < Exit threshold → exits before entry can fire logically - Total trades > 500 in scoring window → noise trading ```sql SELECT asset, timeframe, COUNT(*) as degenerate_rows FROM sweep_results WHERE ABS(i_long_entry_activation_threshold - i_long_exit_activation_threshold) < 10 OR i_long_entry_activation_threshold < i_long_exit_activation_threshold OR total_trades > 500 GROUP BY asset, timeframe; ``` *Potential insight*: if a high fraction of stored results for a combo are degenerate, the param ranges for that combo's thresholds need constraining. Could also be used to automatically add a constraint to the params JSON. **Spec implication**: degenerate flag could be computed at write time and stored as `is_degenerate INTEGER` (0/1) to make filtering cheaper. --- ### 6. Diminishing Returns — at what P&L/DD does param "lock-in" happen? If you sort results by pnl_dd_ratio and look at how the *variance* of each param changes as you move from all positive results toward only top-decile results, you can find the threshold where param choices "solidify." Below that threshold, anything goes; above it, the optimizer is converging on a real signal. ```python thresholds = [0, 10, 50, 100, 200, 500] for t in thresholds: subset = df[df["pnl_dd_ratio"] >= t] print(t, subset[weight_cols].std().mean()) # mean std across all params ``` *Potential insight*: if variance drops sharply above pnl_dd_ratio=50, results above 50 are reliably different from noise. Results below 50 aren't worth learning from. --- ### 7. Suggested Range Tightening Given the distribution of a param in top-quartile results, suggest a tighter range that covers 90% of the mass while reducing the search space. ```python q05 = df[df["pnl_dd_percentile"] >= 75]["i_w_dxy"].quantile(0.05) q95 = df[df["pnl_dd_percentile"] >= 75]["i_w_dxy"].quantile(0.95) # New range suggestion: [q05, q95] vs current [-100, 100] ``` After enough runs, this gives data-driven justification to tighten a [-100, 100] range to, say, [-80, -20], doubling the effective search density on that param. --- --- ## Dashboard: `mine_sweep_db.py` ### Primary Requirement: Claude Visibility The dashboard output **must be machine-readable plain text** so Claude can read it directly with the Read tool and provide analysis. A web UI or image-based chart defeats this purpose. Output: `results/sweep_dashboard.md` — a markdown report overwritten on each run. The user opens it in VSCode; Claude reads it with Read and explains what it means. Optionally also pretty-print to stdout so it's visible during a terminal session. --- ### Invocation ```bash # On demand: python3 mine_sweep_db.py # Specific combo only: python3 mine_sweep_db.py --asset COINBASE_BTCUSD --timeframe 1D # Auto-called at end of auto_optimize_loop.py (optional, ~5s overhead): python3 auto_optimize_loop.py --data data/COINBASE_BTCUSD-1D.csv --hours 0.5 --dashboard ``` --- ### Data Sufficiency Tiers Before showing any analysis, the dashboard prominently rates the data quality per analysis type: | Tier | Rows per combo | What's reliable | |---|---|---| | 🔴 **SPARSE** (< 50) | Don't act on anything. Directional signals are noise at this scale. | | 🟡 **EMERGING** (50–200) | Sign direction hypotheses only. No range tightening yet. | | 🟢 **USABLE** (200–500) | Sign stability and range tightening suggestions are actionable. | | 🟢🟢 **RICH** (500+) | Multi-modal clustering and cross-asset universality are reliable. | The dashboard also estimates **runs needed to reach the next tier**, based on the observed rows-per-iteration rate for that combo: ``` BTC-1D: 47 rows 🔴 SPARSE — need ~5 more batch runs to reach USABLE SOL-12H: 312 rows 🟢 USABLE — sufficient for sign stability and range suggestions ``` --- ### Dashboard Sections #### 0. Header + Data Inventory ``` # Sweep Database Dashboard Generated: 2026-03-14 15:42:00 DB: results/sweep_database.db (schema_ver=1) ## Data Inventory | Asset | TF | Rows | Runs | Last Updated | Tier | |----------------|-----|------|------|--------------------|----------| | COINBASE_BTCUSD| 1D | 312 | 5 | 2026-03-14 12:00 | 🟢 USABLE | | COINBASE_BTCUSD| 4H | 31 | 2 | 2026-03-14 08:00 | 🔴 SPARSE | | ... ``` #### 1. Sign Stability (🟡+ required) For each weight param, across all combos with sufficient data, what % of positive-P&L/DD rows have a positive value? Values near 0% or 100% indicate a "known direction." ``` ## Sign Stability (top-quartile results, all combos with ≥50 rows) ⚠️ 6/20 combos excluded — SPARSE tier | Param | % Positive | N | Confidence | Verdict | |---------------------|------------|------|-------------|----------------------------| | i_w_dxy | 4.1% | 1823 | HIGH | 🔒 Lock NEGATIVE (strong) | | i_w_vix | 12.3% | 891 | MEDIUM | ⬇️ Likely negative | | i_w_btc_spx_corr | 48.7% | 543 | LOW | ❓ No clear direction | | i_w_stoch | 61.2% | 2104 | HIGH | ❓ No clear direction | ``` Confidence is based on sample size and how far % is from 50%. "Lock" verdict only if % is ≤5% or ≥95% AND N ≥ 200. #### 2. Cross-Asset Universality (🟢+ required per asset) Shows mean weight value per asset on 1D timeframe (or whichest has most data). Low spread across assets = universal signal. ``` ## Cross-Asset Universality (1D, top-quartile, combos with ≥200 rows) | Param | BTC mean | ETH mean | SOL mean | LINK mean | Spread | Universal? | |-----------------|----------|----------|----------|-----------|--------|------------| | i_w_dxy | -58.3 | -61.2 | -55.7 | -60.1 | 5.5 | ✅ Yes | | i_w_vix | -33.1 | -12.4 | +8.9 | -41.2 | 50.1 | ❌ No | ``` #### 3. Param Stability Across Timeframes (🟢+ required) Per asset, how much does each param's mean shift from 4H → 1D? ``` ## Timeframe Stability (COINBASE_BTCUSD, top-quartile) ⚠️ Only showing combos with ≥200 rows. 4H excluded (SPARSE). | Param | 6H mean | 8H mean | 12H mean | 1D mean | Stable? | |-----------------|---------|---------|----------|---------|---------| | i_w_macd_bullish| +72 | +68 | +74 | +71 | ✅ Yes | | i_w_stoch | -43 | +12 | +61 | +80 | ❌ Shifts| ``` #### 4. Range Tightening Suggestions (🟢+ required, top-quartile rows) ``` ## Range Tightening Suggestions ⚠️ Only for combos at USABLE tier or above. Treat as hypotheses — review before applying. COINBASE_BTCUSD 1D (312 rows): i_w_dxy: current [-100, 100] → suggested [-90, -20] (covers 90% of top-quartile mass) i_w_stoch: current [-100, 100] → no change (bimodal — see Multi-Modal section) ``` #### 5. Multi-Modal Alerts + Parameter Splitting Candidates (🟢🟢 required) ``` ## Multi-Modal Alerts (combos with ≥500 rows only) BINANCE_SOLUSD 1D: i_w_stoch appears BIMODAL (cluster A: mean=-72, cluster B: mean=+65) → Two distinct strategies may exist. Evaluate both on 2025 holdout before locking direction. COINBASE_BTCUSD 1D: i_m3_momentum_period BIMODAL — good results cluster at period≈1 AND period≈72 → PARAMETER SPLIT CANDIDATE: see below. ``` **Parameter Splitting** is the highest-value action a multi-modal finding can suggest. When a parameter with a "period" or "lookback" dimension shows strong bimodal clustering at values X and Y, rather than forcing a winner-take-all choice (narrowing the range to one cluster), a better option is to split it into **two independent features computed at both periods**, each with its own optimizable weight: ``` # Before: one param, one period, optimizer must choose score += w_m3_momentum * m3_momentum(period=i_m3_momentum_period) # After: two params, two periods, optimizer learns both contributions independently score += w_m3_momentum_fast * m3_momentum(period=1) score += w_m3_momentum_slow * m3_momentum(period=72) ``` This fits naturally into the existing activation score architecture — every other indicator already contributes independently with its own weight. A split indicator is just two more additive terms. The optimizer can then discover that `w_m3_momentum_fast` should be large and `w_m3_momentum_slow` should be small (or vice versa, or both contribute, or one is actually zero). It doesn't have to pick one. The database dashboard would flag this as: ``` PARAMETER SPLIT CANDIDATE: i_m3_momentum_period Cluster A: period ≈ 1 (N=241, mean P&L/DD=183) Cluster B: period ≈ 72–85 (N=178, mean P&L/DD=156) Gap: values 5–65 produce almost no positive results (N=12) Action: add i_w_m3_momentum_fast (period=1) and i_w_m3_momentum_slow (period=72) as separate features in library_activation_scores.py + params JSON ⚠️ Requires strategy code change — not just a params JSON update ``` The dashboard surface this only when: - A period/lookback param is bimodal (two clear clusters with a gap between them) - Both clusters have meaningful N (≥50 rows each) - The gap region is sparse (< 20% of the cluster density) Other candidates beyond period params: any weight param that clusters near both ends of its range (e.g., `i_w_stoch` clustering at -80 AND +75) may also be a split candidate, but the interpretation is different — it means the indicator contributes bullishly in some regimes and bearishly in others. A split there would be a regime-conditioned feature, which is more complex to implement. #### 6. Degenerate Result Summary ``` ## Degenerate Results (entry ≈ exit threshold, or >500 trades) COINBASE_ETHUSD 4H: 847/952 rows degenerate (89%) — threshold param ranges need constraining COINBASE_BTCUSD 1D: 12/312 rows degenerate (4%) — normal ``` #### 7. Action Summary The final section distills everything into a short plain-English action list: ``` ## Recommended Actions (REVIEW BEFORE APPLYING) HIGH CONFIDENCE: [ ] Lock i_w_dxy to NEGATIVE range: change [-100,100] to [-100,-5] in all params JSONs Basis: 95.9% negative across 1823 rows, consistent across all 4 assets MEDIUM CONFIDENCE (wait for more data): [ ] Consider narrowing i_long_entry_activation_threshold to [200, 400] for BTC-1D Basis: 90th percentile range from 312 rows — borderline USABLE tier NOT YET ACTIONABLE: [ ] BTC-4H: only 31 rows (SPARSE). Run 5+ more batch runs before drawing conclusions. [ ] Multi-modal i_w_stoch in SOL-1D: needs 500+ rows for reliable clustering (currently 312) ``` --- ## Implementation Order 1. `write_to_sweep_db()` utility + schema creation in `auto_optimize_loop.py` 2. Call from `verify_top_results` — write all positive pnl_dd_ratio rows with percentile 3. Add `run_id` (UUID) generated at start of each `main()` call 4. Add `--reset-db [ASSET TIMEFRAME]` CLI flag 5. Implement `mine_sweep_db.py` with sections 0–4 + action summary 6. Add `--dashboard` flag to `auto_optimize_loop.py` to run it at end of each full run 7. After sufficient data (🟢🟢 RICH on ≥3 combos), add sections 5–6 (clustering, degenerate) --- ## Out of Scope (for now) - Automatic range tightening written back to params JSON (human reviews action summary first) - Implementing parameter splits (requires strategy code change; dashboard flags candidates, human implements) - ML model trained on DB features - CPU verification of unverified GPU rows already in DB - Charts/graphs (output is plain text markdown for Claude + VSCode readability)