// SQLite Durable Object holding the latest positions snapshot, the cohort and a compact per-coin history (26 h) for
// flows. Written by the cron refresh (worker.js scheduled) and read by paid requests. No KV anywhere.
import { DurableObject } from 'cloudflare:workers';

const HIST_KEEP_MS = 26 * 3600_000;
const TARGETS = [3600_000, 4 * 3600_000, 24 * 3600_000];

export class SnapStore extends DurableObject {
  constructor(ctx, env) {
    super(ctx, env);
    this.sql = ctx.storage.sql;
    this.sql.exec('CREATE TABLE IF NOT EXISTS kv (k TEXT PRIMARY KEY, v TEXT NOT NULL, at INTEGER NOT NULL)');
    this.sql.exec('CREATE TABLE IF NOT EXISTS hist (at INTEGER PRIMARY KEY, coins TEXT NOT NULL)');
  }
  get(k) { const r = this.sql.exec('SELECT v, at FROM kv WHERE k = ?', k).toArray()[0]; return r ? { v: JSON.parse(r.v), at: r.at } : null; }
  set(k, v, at) { this.sql.exec('INSERT INTO kv (k, v, at) VALUES (?, ?, ?) ON CONFLICT (k) DO UPDATE SET v = excluded.v, at = excluded.at', k, JSON.stringify(v), at); }
  /** Store one refresh result. cohortRecord is only passed when it changed. */
  put(snapshot, cohortRecord) {
    if (!snapshot || !Number.isFinite(snapshot.at)) return false;
    this.set('snapshot', snapshot, snapshot.at);
    if (cohortRecord) this.set('cohort', cohortRecord, cohortRecord.at);
    const compact = Object.fromEntries(Object.entries(snapshot.coins || {}).map(([c, a]) => [c, { net: a.net, ln: a.ln, sn: a.sn, tl: a.tl, ts: a.ts }]));
    this.sql.exec('INSERT OR REPLACE INTO hist (at, coins) VALUES (?, ?)', snapshot.at, JSON.stringify(compact));
    this.sql.exec('DELETE FROM hist WHERE at < ?', snapshot.at - HIST_KEEP_MS);
    this.sql.exec("DELETE FROM kv WHERE k = 'lock'");
    return true;
  }
  /** Refresh lock (cron and lazy refresh never overlap). True = caller may refresh. */
  lock(now, ttlMs = 120_000) {
    const l = this.get('lock');
    if (l && now - l.at < ttlMs) return false;
    this.set('lock', 1, now); return true;
  }
  unlock() { this.sql.exec("DELETE FROM kv WHERE k = 'lock'"); return true; }
  cohort() { return this.get('cohort')?.v || null; }
  /** Latest snapshot, cohort list and the history samples closest to 1 h, 4 h and 24 h before it. */
  read() {
    const snap = this.get('snapshot')?.v || null;
    const cohort = this.get('cohort')?.v || null;
    const hist = [];
    if (snap) {
      for (const ago of TARGETS) {
        const t = snap.at - ago;
        const r = this.sql.exec('SELECT at, coins FROM hist WHERE at < ? ORDER BY ABS(at - ?) LIMIT 1', snap.at, t).toArray()[0];
        if (r && !hist.some((h) => h.at === r.at)) hist.push({ at: r.at, coins: JSON.parse(r.coins) });
      }
    }
    return { snapshot: snap, cohort: cohort?.cohort || [], hist };
  }
}

agentgate-source: code mirror on Cloudflare Pages. All repos