// SQLite-backed Durable Object holding the usage counters (free plan: SQLite DOs, no KV write quota involved).
// One instance ("global") per Worker; Stats.flush() sends batched {day: {event: n}} maps; upserts are atomic.
import { DurableObject } from 'cloudflare:workers';

export class StatsCounter extends DurableObject {
  constructor(ctx, env) {
    super(ctx, env);
    this.sql = ctx.storage.sql;
    this.sql.exec('CREATE TABLE IF NOT EXISTS c (day TEXT NOT NULL, ev TEXT NOT NULL, n INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (day, ev))');
    // 0.1.2: anonymized need= aggregates (see needlog.js). q is already normalized (no PII); no IP/wallet columns.
    this.sql.exec('CREATE TABLE IF NOT EXISTS needs (day TEXT NOT NULL, q TEXT NOT NULL, n INTEGER NOT NULL DEFAULT 0, no_match INTEGER NOT NULL DEFAULT 0, low_match INTEGER NOT NULL DEFAULT 0, no_ours INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (day, q))');
  }
  addNeeds(pending) {
    for (const [d, qs] of Object.entries(pending || {})) {
      if (!/^\d{4}-\d{2}-\d{2}$/.test(d)) continue;
      for (const [q, c] of Object.entries(qs || {})) {
        if (typeof q !== 'string' || q.length < 2 || q.length > 120) continue;
        const v = (k) => Math.max(0, Math.floor(Number(c?.[k]) || 0));
        if (!v('n')) continue;
        this.sql.exec('INSERT INTO needs (day, q, n, no_match, low_match, no_ours) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT (day, q) DO UPDATE SET n = n + excluded.n, no_match = no_match + excluded.no_match, low_match = low_match + excluded.low_match, no_ours = no_ours + excluded.no_ours', d, q, v('n'), v('no_match'), v('low_match'), v('no_ours'));
      }
    }
    this.sql.exec("DELETE FROM needs WHERE day < date('now', '-90 days')"); // retention: 90 days
    return true;
  }
  topNeeds(since, limit = 500) {
    return this.sql.exec('SELECT q, SUM(n) AS n, SUM(no_match) AS no_match, SUM(low_match) AS low_match, SUM(no_ours) AS no_ours FROM needs WHERE day >= ? GROUP BY q ORDER BY (SUM(no_match) + SUM(low_match)) DESC, SUM(n) DESC LIMIT ?', String(since || ''), Math.min(1000, Math.max(1, Number(limit) || 500))).toArray();
  }
  add(pending) {
    for (const [d, counts] of Object.entries(pending || {})) {
      if (!/^\d{4}-\d{2}-\d{2}$/.test(d)) continue;
      for (const [ev, n] of Object.entries(counts || {})) {
        const k = Number(n);
        if (!/^[a-z0-9_:\-]{1,64}$/i.test(ev) || !Number.isFinite(k) || k <= 0) continue;
        this.sql.exec('INSERT INTO c (day, ev, n) VALUES (?, ?, ?) ON CONFLICT (day, ev) DO UPDATE SET n = n + excluded.n', d, ev, Math.floor(k));
      }
    }
    return true;
  }
  rows(since) {
    return this.sql.exec('SELECT day, ev, n FROM c WHERE day >= ? ORDER BY day DESC', String(since || '')).toArray();
  }
}

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