1/// <reference types="@cloudflare/workers-types" />2/**3 * Daily TTL sweep (cron): purge trashed scripts past their 14-day window,4 * delete expired unstarred scripts/sims, then GC stars whose target is gone5 * and blobs no longer referenced anywhere.6 *7 * Order matters and everything runs in one db.batch() (a single transaction):8 * 1. trash purge (deleted_at + 14 d elapsed — stars do NOT protect these;9 * the owner asked for deletion. Their sims keep working via blob pins,10 * mirroring the old hard-delete semantics.)11 * 2. expired scripts (script_versions rows cascade via FK)12 * 3. expired sims (sim_scripts/sim_results cascade via FK) — public sims13 * with at least one still-LISTED participant are retained: they are the14 * proof/history backing Browse and the leaderboard.15 * 4. orphaned stars (stars have no FK to their target)16 * 5. orphaned blobs (unreferenced by scripts, script_versions, sim_scripts)17 */18import type { Env } from './env';19import { nowSec, TRASH_SECONDS } from './util';20import { listedWhere } from './db';21import { deleteSources, putMetricsGzIfAbsent, putSource, replayKeyFor } from './r2blobs';22import { computeSimMetrics } from './simrun';2324/** Exported for tests: snapshot/assert these without a D1 instance. */25export const SWEEP_SQL = {26 trashPurge: `DELETE FROM scripts27 WHERE deleted_at IS NOT NULL AND deleted_at < ?1`,28 expiredScripts: `DELETE FROM scripts29 WHERE expires_at IS NOT NULL AND expires_at < ?130 AND NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'script' AND stars.target_id = scripts.id)`,31 expiredSims: `DELETE FROM sims32 WHERE expires_at IS NOT NULL AND expires_at < ?133 AND NOT EXISTS (SELECT 1 FROM stars WHERE stars.kind = 'sim' AND stars.target_id = sims.id)34 AND NOT (sims.visibility = 'public' AND EXISTS (35 SELECT 1 FROM sim_scripts ss JOIN scripts sc ON sc.id = ss.script_id36 WHERE ss.sim_id = sims.id AND ${listedWhere('sc')}))`,37 orphanStars: `DELETE FROM stars38 WHERE (kind = 'script' AND NOT EXISTS (SELECT 1 FROM scripts WHERE scripts.id = stars.target_id))39 OR (kind = 'sim' AND NOT EXISTS (SELECT 1 FROM sims WHERE sims.id = stars.target_id))`,40 orphanBlobs: `DELETE FROM blobs41 WHERE NOT EXISTS (SELECT 1 FROM scripts WHERE scripts.hash = blobs.hash)42 AND NOT EXISTS (SELECT 1 FROM script_versions WHERE script_versions.hash = blobs.hash)43 AND NOT EXISTS (SELECT 1 FROM sim_scripts WHERE sim_scripts.hash = blobs.hash)`,44} as const;4546export interface SweepResult {47 trashed: number;48 scripts: number;49 sims: number;50 stars: number;51 blobs: number;52}5354export async function runSweep(env: Env): Promise<SweepResult> {55 const now = nowSec();56 // Snapshot which blob hashes are about to become orphans so their R257 // objects can be GCed after the rows go (best-effort; a failed delete58 // leaks an immutable object, nothing more).59 const orphanRows = await env.DB60 .prepare(SWEEP_SQL.orphanBlobs.replace('DELETE FROM blobs', 'SELECT hash FROM blobs'))61 .all<{ hash: string }>();62 const [trashed, scripts, sims, stars, blobs] = await env.DB.batch([63 env.DB.prepare(SWEEP_SQL.trashPurge).bind(now - TRASH_SECONDS),64 env.DB.prepare(SWEEP_SQL.expiredScripts).bind(now),65 env.DB.prepare(SWEEP_SQL.expiredSims).bind(now),66 env.DB.prepare(SWEEP_SQL.orphanStars),67 env.DB.prepare(SWEEP_SQL.orphanBlobs),68 ]);69 try {70 await deleteSources(env, orphanRows.results.map((r) => r.hash));71 } catch (err) {72 console.warn('R2 orphan GC failed (will retry next sweep):', err);73 }74 return {75 trashed: trashed?.meta.changes ?? 0,76 scripts: scripts?.meta.changes ?? 0,77 sims: sims?.meta.changes ?? 0,78 stars: stars?.meta.changes ?? 0,79 blobs: blobs?.meta.changes ?? 0,80 };81}8283/**84 * Move legacy inline payloads (pre-R2 rows) into the bucket, a bounded batch85 * per nightly run. Sources: blobs.source -> src/<hash>.js. Metrics:86 * sim_metrics.data -> metrics/<replayKey>.json.gz keyed by the sim's replay87 * identity. Rows flip to thin pointers once the bytes are in R2.88 */89export async function backfillR2(env: Env): Promise<{ sources: number; metrics: number }> {90 let sources = 0;91 const blobRows = await env.DB92 .prepare(`SELECT hash, source FROM blobs WHERE in_r2 = 0 AND source <> '' LIMIT 40`)93 .all<{ hash: string; source: string }>();94 for (const row of blobRows.results) {95 await putSource(env, row.hash, row.source);96 await env.DB97 .prepare(`UPDATE blobs SET in_r2 = 1, source = '' WHERE hash = ?1`)98 .bind(row.hash)99 .run();100 sources++;101 }102103 let metrics = 0;104 const metricRows = await env.DB105 .prepare(`SELECT sim_id, data FROM sim_metrics WHERE in_r2 = 0 AND data IS NOT NULL LIMIT 20`)106 .all<{ sim_id: string; data: unknown }>();107 for (const row of metricRows.results) {108 const sim = await env.DB109 .prepare(`SELECT seed, config_json FROM sims WHERE id = ?1`)110 .bind(row.sim_id)111 .first<{ seed: string; config_json: string }>();112 if (!sim) continue;113 const refs = await env.DB114 .prepare(`SELECT hash FROM sim_scripts WHERE sim_id = ?1 ORDER BY slot`)115 .bind(row.sim_id)116 .all<{ hash: string }>();117 const key = await replayKeyFor(sim.seed, sim.config_json, refs.results.map((r) => r.hash));118 const d = row.data;119 const bytes =120 d instanceof Uint8Array ? d121 : d instanceof ArrayBuffer ? new Uint8Array(d)122 : Array.isArray(d) ? Uint8Array.from(d as number[])123 : null;124 if (bytes === null) continue;125 await putMetricsGzIfAbsent(env, key, bytes);126 await env.DB127 .prepare(`UPDATE sim_metrics SET in_r2 = 1, replay_key = ?2, data = NULL WHERE sim_id = ?1`)128 .bind(row.sim_id, key)129 .run();130 metrics++;131 }132 return { sources, metrics };133}134135/**136 * Rated sims whose create-time replay died (CPU limit, crash) never got137 * metrics or rating events, and public sims reject client backfill — so the138 * cron retries a bounded batch each night. Metrics-GET views retry too;139 * this catches the ones nobody looked at.140 */141export async function retryDeadPublicSims(env: Env, limit = 150): Promise<number> {142 const rows = await env.DB143 .prepare(144 `SELECT id FROM sims145 WHERE visibility = 'public'146 AND created_at < ?1147 AND NOT EXISTS (SELECT 1 FROM rating_events WHERE rating_events.sim_id = sims.id)148 ORDER BY created_at DESC LIMIT ?2`,149 )150 .bind(nowSec() - 900, limit)151 .all<{ id: string }>();152 let retried = 0;153 for (const row of rows.results) {154 await dispatchCompute(env, row.id);155 retried++;156 }157 return retried;158}159160/**161 * Run a sim compute in its OWN invocation. A replay can eat most of a CPU162 * budget by itself, so the cron never computes inline — it GETs the sim's163 * metrics endpoint on our own origin, whose 404 path schedules the compute164 * in that request's waitUntil. Inline fallback only when SELF_ORIGIN is165 * unset (tests/local).166 */167export async function dispatchCompute(env: Env, simId: string): Promise<void> {168 if (env.SELF_ORIGIN !== undefined && env.SELF_ORIGIN !== '') {169 try {170 await fetch(`${env.SELF_ORIGIN}/api/sims/${simId}/metrics`, {171 headers: { accept: 'application/json' },172 });173 return;174 } catch (err) {175 console.warn('compute dispatch failed, falling back inline:', simId, err);176 }177 }178 await computeSimMetrics(env, simId);179}