Self-Learning Model

CREATE TABLE learning_outcomes (
  id                       TEXT PRIMARY KEY,
  token_address            TEXT NOT NULL,   -- FK → tokens
  composite_score_id       TEXT,            -- FK → composite_scores
  composite_score_at_alert INTEGER NOT NULL,
  signal_scores_at_alert   TEXT NOT NULL DEFAULT '{}',  -- JSON: dimension scores + research notes
  price_at_alert           REAL,
  measured_at              TEXT,
  hours_elapsed            INTEGER,
  price_at_measure         REAL,
  price_change_pct         REAL,
  outcome_label            TEXT CHECK(outcome_label IN (
                             'strong_positive','mild_positive',
                             'neutral','mild_negative','dump')),
  outcome_magnitude        REAL,
  created_at               TEXT NOT NULL DEFAULT (datetime('now'))
);

-- outcome_label values and their numeric mappings:
-- 'strong_positive' → token performed very well   → 1.0
-- 'mild_positive'   → token performed OK          → 0.7
-- 'neutral'         → no significant movement     → 0.5
-- 'mild_negative'   → token declined              → 0.3
-- 'dump'            → rug / hard dump             → 0.0

const LABEL_TO_NUMERIC: Record<string, number> = {
  strong_positive: 1.0,
  mild_positive:   0.7,
  neutral:         0.5,
  mild_negative:   0.3,
  dump:            0.0,
};

// X matrix columns (features) — all normalized 0.0 to 1.0:
// [fee_score/100, dev_score/100, tech_score/100, novelty_score/100]

// Y vector (targets): numeric outcomes from LABEL_TO_NUMERIC

// Minimum 30 samples required before any weight update runs.
// If insufficient data: weights stay at current version, update skipped.

// agents/learning.ts — vanilla TypeScript ridge regression
const LAMBDA = 0.1;

// Fit raw coefficients on the given rows: w = (XᵀX + λnI)⁻¹ Xᵀy
function fitWeights(data: NumericRow[]): number[] {
  const n = data.length;
  const p = 4;  // [fee, dev, tech, novelty]

  const X = data.map(r => [
    (r.fee_opportunity_score ?? 50) / 100,
    (r.developer_score       ?? 50) / 100,
    (r.tech_research_score   ?? 50) / 100,
    (r.novelty_score         ?? 50) / 100,
  ]);
  const y = data.map(r => r.outcome_label);  // numeric target

  const XtX = Array.from({ length: p }, (_, i) =>
    Array.from({ length: p }, (__, j) => X.reduce((s, row) => s + row[i] * row[j], 0)));
  for (let i = 0; i < p; i++) XtX[i][i] += LAMBDA * n;  // ridge penalty (× n)

  const Xty = Array.from({ length: p }, (_, i) =>
    X.reduce((s, row, k) => s + row[i] * y[k], 0));

  return gaussianElim(XtX, Xty);  // raw w[0..3]
}

// Shipped weights are clipped to [0.05, 0.60] and normalised to sum to 1.
function postProcessWeights(raw: number[]): number[] {
  const clipped = raw.map(w => Math.max(0.05, Math.min(0.60, w)));
  const sum = clipped.reduce((a, b) => a + b, 0);
  return clipped.map(w => w / sum);
}

// Every Sunday at 02:00 UTC — agents/learning.ts
async function runWeightUpdate(env: Env) {
  // 1. Pull up to the 500 most-recent labelled outcomes (min 30 required).
  //    No date window — recency is bounded by LIMIT, not by week.
  const outcomes = await env.DB.prepare(`
    SELECT cs.fee_opportunity_score, cs.developer_score,
           cs.tech_research_score,   cs.novelty_score,
           lo.outcome_label
    FROM learning_outcomes lo
    JOIN composite_scores cs ON cs.id = lo.composite_score_id
    WHERE lo.outcome_label IS NOT NULL
      AND cs.fee_opportunity_score IS NOT NULL
      AND cs.developer_score IS NOT NULL
    ORDER BY lo.created_at DESC LIMIT 500
  `).all();

  if (outcomes.results.length < 30) {  // MIN_TRAINING_SAMPLES
    console.log('Insufficient training data — skipping weight update');
    return;
  }

  // 2. Map labels → numeric targets
  const mapped = outcomes.results.map(r => ({
    ...r, outcome_label: LABEL_TO_NUMERIC[r.outcome_label as string] ?? 0.5,
  }));

  // 3. Fit shipped weights on ALL data, then clip + normalise
  const weights = postProcessWeights(fitWeights(mapped));

  // 4. Report generalization via out-of-fold cross-validation (can be negative)
  const cvR2 = crossValR2(mapped);  // stored in validation_r2

  // 5. Insert new weights version, deactivate old.
  //    PK is an AUTOINCREMENT 'version' — no id/UUID column.
  await env.DB.prepare('UPDATE scoring_weights SET is_active = 0 WHERE is_active = 1').run();
  await env.DB.prepare(`
    INSERT INTO scoring_weights (
      version, is_active, fee_weight, dev_weight, tech_weight, novelty_weight,
      training_sample_size, validation_r2, notes, activated_at
    ) VALUES (?, 1, ?, ?, ?, ?, ?, ?, ?, ?)
  `).bind(nextVersion, ...weights, outcomes.results.length, cvR2, notes, now).run();
}

// Out-of-fold CV R² — honest generalization estimate (may be negative).
function crossValR2(data: NumericRow[], k = 5): number {
  const n = data.length;
  if (n < 2) return 0;
  if (n < 2 * k) k = Math.max(2, Math.floor(n / 3));  // no empty folds

  // Deterministic shuffle (seeded) → reproducible for the same dataset.
  const rand = mulberry32(0x5e58a7);
  const idx = data.map((_, i) => i);
  for (let i = idx.length - 1; i > 0; i--) {
    const j = Math.floor(rand() * (i + 1));
    [idx[i], idx[j]] = [idx[j], idx[i]];
  }
  const foldOf: number[] = [];
  idx.forEach((orig, pos) => { foldOf[orig] = pos % k; });

  const actual: number[] = [], predicted: number[] = [];
  for (let fold = 0; fold < k; fold++) {
    const train = data.filter((_, i) => foldOf[i] !== fold);
    const test  = data.filter((_, i) => foldOf[i] === fold);
    if (!train.length || !test.length) continue;
    const w = fitWeights(train);              // same LAMBDA = 0.1, × n
    for (const r of test) {
      actual.push(r.outcome_label);
      predicted.push(features(r).reduce((a, xi, j) => a + xi * w[j], 0));
    }
  }

  // Single R² across ALL held-out predictions. NOT clamped to ≥ 0.
  const mean  = actual.reduce((a, b) => a + b, 0) / actual.length;
  const ssTot = actual.reduce((s, y) => s + (y - mean) ** 2, 0);
  if (ssTot < 1e-9) return 0;
  const ssRes = actual.reduce((s, y, i) => s + (y - predicted[i]) ** 2, 0);
  return 1 - ssRes / ssTot;
}

-- scoring_weights table (version history):
CREATE TABLE scoring_weights (
  version              INTEGER PRIMARY KEY AUTOINCREMENT,
  is_active            INTEGER NOT NULL DEFAULT 0,
  fee_weight           REAL DEFAULT 0.200,
  dev_weight           REAL DEFAULT 0.300,
  tech_weight          REAL DEFAULT 0.300,
  novelty_weight       REAL DEFAULT 0.200,
  dev_follower_weight  REAL DEFAULT 0.400,
  dev_ecosystem_weight REAL DEFAULT 0.300,
  dev_activity_weight  REAL DEFAULT 0.200,
  dev_alignment_weight REAL DEFAULT 0.100,
  training_sample_size INTEGER,
  validation_r2        REAL,          -- 5-fold out-of-fold CV R² (may be < 0)
  notes                TEXT,
  activated_at         TEXT
);