Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.
JavaScript 3d-generationai-agentsanimationavatardigital-humangenerative-aiglbgltfimage-to-3dllmmcpmcp-servermodel-context-protocolsolanatext-to-3dthreejsweb-componentswebglwebxrx402
// @ts-check
// GET /api/cron/db-retention — keep the database under its storage cap.
//
// Why this exists
// ---------------
// The platform runs on a Neon branch with a hard project-size cap (512 MB on the
// free tier). Two families of tables grow without bound and, left alone, march the
// branch straight into that cap — at which point Postgres raises SQLSTATE 53100
// ("could not extend file because project size limit … exceeded") and every WRITE
// path starts failing (the production incident this cron closes out):
//
// 1. The pump.fun intel firehose. `pump_coin_intel` ingests ~15–20k new mints a
// day (≈30 MB/day), and its mint-keyed satellites (`pump_coin_wallets`,
// `coin_smart_money`, `smart_money_scored`, `pump_coin_outcomes`,
// `oracle_conviction`, `oracle_conviction_history`) grow in lockstep — ~60 MB
// a day across the family. Nothing pruned it, so it accreted until the branch
// was full.
// 2. `avatar_regen_jobs`. Each reconstruct job's `params` carries the multi-MB
// base64 SOURCE images. The live path drops them once a job leaves
// reconstruction (reconstruct-finalize.js), but terminal (done/failed) jobs
// that took another route kept them — 346 rows were holding 43 MB.
//
// What it does
// ------------
// Runs on a schedule and, idempotently + bounded so a single tick can never run
// away past the function's maxDuration:
//
// A. FIREHOSE RETENTION. Deletes every mint (and its satellite rows) older than
// the retention window. The window self-tunes: normally PUMP_INTEL_RETENTION_
// DAYS, but when the branch is over the high-water mark it tightens to
// PUMP_INTEL_MIN_RETENTION_DAYS so the cap is never actually hit. This is the
// self-healing valve — it sheds the oldest firehose data under storage
// pressure and relaxes again once space is reclaimed. The engine's own judge
// window (smart-money-rollup.js) resolves coins within a day or two of launch,
// so a multi-day window keeps everything load-bearing; wallet reputation
// (`wallet_reputation`, wallet-keyed, the durable output) and the win/loss
// ground truth (`pumpfun_graduations`) are never touched.
// A4. AUTOPILOT RUN LOGS. The buyback/distribute crons record one row per coin
// per tick. Successful runs carry a tx_signature and are kept forever; the
// `failed`/`skipped` diagnostic rows are pruned on the firehose window.
// B. AVATAR JOB HYGIENE. Strips the base64 source images from terminal jobs past
// a day old, and deletes terminal jobs past 30 days.
// C. VACUUM. Plain VACUUM on the tables it pruned so the freed pages become
// reusable and Neon's storage GC can return them.
// D. COMPACTION. Plain VACUUM never shrinks the relation FILES, so on Neon
// pg_database_size stays high after a prune and the storage-pressure gate
// (isStoragePressured / requireWriteCapacity) can latch permanently: the
// July 2026 recurrence had 770 MB of dead file space across the pruned
// tables while every write-heavy cron sat skipped. When a tick starts
// under pressure, this step measures reclaimable space per managed table
// (pgstattuple_approx) and VACUUM FULLs the worst offenders, smallest
// first and bounded per tick, so the file space actually returns and the
// gate unlatches without a human.
// E. HOME ACTION-LOG RETENTION. home_action_log is the "what did my agent do in
// my house" trail, and read the other way round it is a behavioural record
// of a household: when someone came home, which rooms they lit, what time
// the bedroom light went off. Its window is therefore NOT a platform
// constant and NOT tunable by an env var here. It is per home and it is
// the owner's, stored on home_connections.action_log_retention_days
// (90 days by default). This step joins the log against its own home's
// setting so the whole policy stays in the database. Deliberately exempt
// from the storage-pressure valve in both directions: shortening someone's
// audit trail because our disk is full is not our call to make, and the
// rows are small enough that they cannot be the reason it is full.
// See docs/home-privacy.md and api/_lib/home/privacy.js.
//
// DELETE (not UPDATE) is used for the firehose because DELETE settles xmax in place
// and does NOT extend a relation file — it therefore succeeds even AT the cap,
// where an UPDATE (which writes a new tuple version, needing a fresh page) would
// itself fail with 53100. The image-strip UPDATE is guarded for the same reason.
//
// Everything here operates on the platform's OWN runtime launch/intel records at
// runtime and hardcodes no specific mint — generic retention plumbing, not an
// endorsement of any coin.
import { json, method, wrapCron } from '../_lib/http.js';
import { sql, isDbCapacityError } from '../_lib/db.js';
import { sendOpsAlert } from '../_lib/alerts.js';
import { requireCron } from '../_lib/cron-auth.js';
import { purgeExpiredActionLog } from '../_lib/home/privacy.js';
import { purgeExpiredMcpSessions } from '../_lib/mcp-clients.js';
// Mint-keyed satellites of pump_coin_intel, deleted before the master so no run
// orphans a satellite row. Every name here is a fixed constant (never user input),
// so splicing it into the DELETE text below is safe. Verified to carry a `mint`
// column. Deliberately EXCLUDES: wallet_reputation / smart_wallet_reputation
// (wallet-keyed accumulated output — the durable product value), and
// pumpfun_graduations (small, slow-growing win/loss ground truth the judge reads).
const FIREHOSE_SATELLITES = [
'pump_coin_wallets',
'coin_smart_money',
'smart_money_scored',
'pump_coin_outcomes',
'oracle_conviction',
'oracle_conviction_history',
];
// Bounds so a single tick stays well under the function's maxDuration. The cron
// re-runs on its schedule, so a large backlog (the first prune once data crosses
// the window, or a pressure-valve catch-up) is chewed through over several ticks.
const MINT_BATCH = 2000; // mints per cascade batch
const MAX_MINTS_PER_RUN = 40_000; // ceiling per tick across all batches
const REGEN_STRIP_BATCH = 200;
const REGEN_DELETE_BATCH = 500;
const REGEN_MAX_ITERS = 40;
const SERIES_BATCH = 5000; // rows per batch for time-keyed series prunes
const SERIES_MAX_PER_RUN = 50_000; // per-table ceiling per tick
const ORPHAN_BATCH = 5000; // orphaned satellite rows per table per tick
// Compaction bounds (section D). VACUUM FULL rewrites a table into a fresh file
// under an ACCESS EXCLUSIVE lock, cheap on Neon for these churn tables (a
// 500 MB table compacted in ~2 s in the July 2026 recovery) but never free, so
// only tables with real reclaimable space qualify and each tick rewrites at
// most a few. Smallest-first ordering matters near the hard cap: each rewrite
// needs headroom ≈ the table's LIVE size, so freeing the small ones first buys
// the room to rewrite the big ones.
const COMPACT_MIN_FREE_MB = 25; // ignore tables with less reclaimable space than this
const COMPACT_MIN_FREE_RATIO = 0.3; // ... or mostly-live files (rewrite buys little)
const COMPACT_MAX_TABLES = 3; // per tick; the cron re-runs on its schedule
// Time-keyed tables outside the mint-cascade family that still grow without
// bound. Each is pruned by its own timestamp column on the shared valve
// (window tightens to the floor under storage pressure). All names/columns are
// fixed constants — never user input — so splicing them into SQL text is safe.
//
// - pump_launch_snapshots: append-only jsonb time series (one row per paid
// snapshot, `launches` carries the full launch list) — July 2026 storage
// incident showed retention never touched it.
// - x402_autonomous_log: one row per autonomous x402 call, success AND
// failure, with jsonb response payloads. Money-adjacent (tx signatures),
// so it keeps the audit ledger's longer window.
// - sniper_coin_sentiment / token_intel_risk: keyed (mint, network) so they
// grow with the mint universe; their gates treat stale rows as absent, so
// rows past the firehose window are dead weight.
// - x402_spent_payments: one small row per honoured x402 payment proof, the
// durable replay guard behind api/_lib/x402/spent-payments.js. Its window is
// `spent` — FIXED at 90 days and deliberately exempt from the storage-
// pressure valve, because shortening it is exactly what re-opens the replay
// hole the table exists to close. The rows are tiny (a hash, a route, an
// amount), so it cannot be the reason the branch is under pressure.
//
// DELIBERATELY UNMANAGED, and the single biggest table this cron does NOT prune:
// x402_self_facilitator_log (632 MB as of 2026-08-10, third largest in the
// branch). Every other big grower above is churn; this one is the settle book.
// It is excluded because /api/x402-ring aggregates it with NO time filter for
// its `lifetime` period (api/x402-ring.js, sinceFor() returns null), so any
// retention window silently shrinks published lifetime settlement and fee
// totals. Every other reader is short-window (healthz 24h, fee-audit and
// wallet-fee-meter current-day, ring-reconciliation LOOKBACK_HOURS,
// sponsor-runway X402_SPONSOR_BURN_WINDOW_DAYS), so a window is technically
// safe for them. Pruning it is therefore a product decision about the public
// revenue figures, not a mechanical retention one: get owner sign-off on what
// `lifetime` should mean (and roll the pruned totals into a summary row first)
// before adding it here.
const TIME_SERIES_TABLES = [
{ table: 'pump_launch_snapshots', tsColumn: 'ts', windowKind: 'firehose' },
{ table: 'x402_autonomous_log', tsColumn: 'ts', windowKind: 'audit' },
{ table: 'sniper_coin_sentiment', tsColumn: 'checked_at', windowKind: 'firehose' },
{ table: 'token_intel_risk', tsColumn: 'checked_at', windowKind: 'firehose' },
{ table: 'x402_spent_payments', tsColumn: 'created_at', windowKind: 'spent' },
// One row per coin the Sentiment Scout ever put on its board. Trade receipts
// cite the first sighting, so it keeps the audit window, not the firehose one.
{ table: 'sentiment_scout_reads', tsColumn: 'first_scouted_at', windowKind: 'audit' },
// The raw MCP session join table (api/_lib/mcp-clients.js). Only the daily
// aggregate in mcp_client_daily is kept, so a session idle for 30 days
// goes. Fixed, like `spent`: the window is a privacy promise in
// docs/ops/mcp-clients.md, not a storage valve.
{ table: 'mcp_client_sessions', tsColumn: 'last_seen_at', windowKind: 'mcpSessions' },
];
// ── Autopilot run logs ────────────────────────────────────────────────────────
// The buyback / distribute crons append one row per coin per tick, whatever the
// outcome. Successful runs carry a `tx_signature` — an on-chain money record we
// keep indefinitely (the /autopilot history reads them). Unsuccessful ones carry
// only a diagnostic: `failed` (SDK/RPC error) or `skipped` (vault empty or under
// threshold). Those are pure churn, and a persistent failure loop mints them at
// the cron's cadence: a PumpAgent argument-order bug produced 228k `failed` rows
// (~40k/day, 59 MB) between 2026-06-15 and 2026-07-10 before anyone noticed,
// because a failing run recorded itself instead of alerting.
//
// Prune only the diagnostic rows, on the self-tightening firehose window. Rows
// with a tx_signature are never touched, so no burn or payout record is lost.
const RUN_LOG_TABLES = [
{ table: 'pump_buyback_runs', tsColumn: 'created_at' },
{ table: 'pump_distribute_runs', tsColumn: 'created_at' },
];
// Raw MCP client sessions live 30 days; the daily aggregate stays.
const MCP_SESSION_RETENTION_DAYS = 30;
function clampInt(raw, min, max, dflt) {
const n = Number.parseInt(String(raw ?? ''), 10);
if (!Number.isFinite(n)) return dflt;
return Math.min(max, Math.max(min, n));
}
async function dbSizeMb() {
const [{ mb }] = await sql`SELECT (pg_database_size(current_database()) / 1048576.0)::int AS mb`;
return Number(mb) || 0;
}
async function tableExists(name) {
const [{ reg }] = await sql`SELECT to_regclass(${'public.' + name}) AS reg`;
return reg != null;
}
// ── A. Firehose retention (cascade prune older than cutoffDays) ───────────────
async function pruneFirehose(cutoffDays) {
if (!(await tableExists('pump_coin_intel'))) return { mints: 0, perTable: {}, liveSatellites: [] };
// Resolve satellite existence once (a fresh deploy may not have every table).
const liveSatellites = [];
for (const t of FIREHOSE_SATELLITES) {
if (await tableExists(t)) liveSatellites.push(t);
}
const perTable = {};
let totalMints = 0;
const maxBatches = Math.ceil(MAX_MINTS_PER_RUN / MINT_BATCH);
for (let i = 0; i < maxBatches; i++) {
const olds = await sql`
SELECT mint FROM pump_coin_intel
WHERE first_seen_at < now() - ${cutoffDays} * interval '1 day'
LIMIT ${MINT_BATCH}
`;
if (!olds.length) break;
const mints = olds.map((r) => r.mint);
for (const t of liveSatellites) {
// t is a fixed constant from FIREHOSE_SATELLITES — safe to splice; the
// mint list is bound as $1. DELETE settles xmax in place (no file
// extension), so it works even at the cap.
const del = await sql(`DELETE FROM ${t} WHERE mint = ANY($1) RETURNING mint`, [mints]);
perTable[t] = (perTable[t] || 0) + del.length;
}
const delIntel = await sql`DELETE FROM pump_coin_intel WHERE mint = ANY(${mints}) RETURNING mint`;
perTable['pump_coin_intel'] = (perTable['pump_coin_intel'] || 0) + delIntel.length;
totalMints += mints.length;
if (olds.length < MINT_BATCH) break;
}
return { mints: totalMints, perTable, liveSatellites };
}
// ── A2. Orphaned-satellite sweep ──────────────────────────────────────────────
// The cascade prune only deletes satellite rows for mints it selects FROM
// pump_coin_intel — satellite rows whose master row is already gone (written
// after the master was pruned, or from a partial historical run) leak forever.
// Sweep them with a bounded anti-join per satellite. DELETE settles xmax in
// place, so this too is safe at the cap.
async function pruneOrphanedSatellites(liveSatellites) {
if (!(await tableExists('pump_coin_intel'))) return {};
const perTable = {};
for (const t of liveSatellites) {
const del = await sql(
`DELETE FROM ${t} WHERE mint IN (
SELECT s.mint FROM ${t} s
LEFT JOIN pump_coin_intel p ON p.mint = s.mint
WHERE p.mint IS NULL
LIMIT $1
) RETURNING mint`,
[ORPHAN_BATCH],
);
if (del.length > 0) perTable[t] = del.length;
}
return perTable;
}
// ── A3. Time-keyed series retention ───────────────────────────────────────────
async function pruneTimeSeries(cutoffs) {
const perTable = {};
for (const { table, tsColumn, windowKind } of TIME_SERIES_TABLES) {
if (!(await tableExists(table))) continue;
const cutoffDays = cutoffs[windowKind];
let deleted = 0;
const maxBatches = Math.ceil(SERIES_MAX_PER_RUN / SERIES_BATCH);
for (let i = 0; i < maxBatches; i++) {
// table/tsColumn are fixed constants from TIME_SERIES_TABLES; only the
// numeric bounds are parameters. Bounded subselect + DELETE works at cap.
const del = await sql(
`DELETE FROM ${table} WHERE ctid IN (
SELECT ctid FROM ${table}
WHERE ${tsColumn} < now() - $1 * interval '1 day'
LIMIT $2
) RETURNING 1`,
[cutoffDays, SERIES_BATCH],
);
deleted += del.length;
if (del.length < SERIES_BATCH) break;
}
if (deleted > 0) perTable[table] = deleted;
}
return perTable;
}
// ── A4. Autopilot run-log retention (diagnostic rows only) ────────────────────
// Deletes `failed`/`skipped` rows past the firehose window. `tx_signature IS
// NULL` is belt-and-braces: a row that somehow reached a terminal-diagnostic
// status while still carrying a signature keeps its on-chain record.
async function pruneRunLogs(cutoffDays) {
const perTable = {};
for (const { table, tsColumn } of RUN_LOG_TABLES) {
if (!(await tableExists(table))) continue;
let deleted = 0;
const maxBatches = Math.ceil(SERIES_MAX_PER_RUN / SERIES_BATCH);
for (let i = 0; i < maxBatches; i++) {
// table/tsColumn are fixed constants from RUN_LOG_TABLES; only the numeric
// bounds are parameters. Bounded subselect + DELETE works at cap.
const del = await sql(
`DELETE FROM ${table} WHERE ctid IN (
SELECT ctid FROM ${table}
WHERE ${tsColumn} < now() - $1 * interval '1 day'
AND status IN ('failed', 'skipped')
AND tx_signature IS NULL
LIMIT $2
) RETURNING 1`,
[cutoffDays, SERIES_BATCH],
);
deleted += del.length;
if (del.length < SERIES_BATCH) break;
}
if (deleted > 0) perTable[table] = deleted;
}
return perTable;
}
// ── Storage visibility ────────────────────────────────────────────────────────
// When the branch is pinned above the high-water mark, the single most useful
// diagnostic is WHERE the space lives. Report the largest relations so the ops
// alert (and the cron's JSON body) names the offenders instead of just the total.
async function topRelationsBySize(limit = 15) {
try {
const rows = await sql`
SELECT relname AS table, (pg_total_relation_size(c.oid) / 1048576.0)::numeric(10,1) AS mb
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT ${limit}
`;
return rows.map((r) => ({ table: r.table, mb: Number(r.mb) }));
} catch {
return []; // visibility is best-effort — never fails the prune
}
}
// ── B2. x402 payment audit-log retention ──────────────────────────────────────
// The payment audit ledger (x402_audit_log) grows one row per paid request on hot
// routes (e.g. /api/x402/dance-tip). Unbounded it both marches toward the storage
// cap and slows every dashboard aggregate (getPaymentStats full-scans it). Keep a
// generous window — long enough for revenue reporting — then prune by created_at,
// which the table's (event_type, created_at) indexes make cheap. Kept far longer
// than the intel firehose because these are money records, not churn; it tightens
// to a floor only under storage pressure via the shared valve.
const AUDIT_BATCH = 5000;
const AUDIT_MAX_PER_RUN = 100_000;
async function pruneAuditLog(cutoffDays) {
if (!(await tableExists('x402_audit_log'))) return { deleted: 0 };
let deleted = 0;
const maxBatches = Math.ceil(AUDIT_MAX_PER_RUN / AUDIT_BATCH);
for (let i = 0; i < maxBatches; i++) {
// DELETE settles xmax in place (no file extension), so it works even at the
// cap. Bounded subselect so a single statement never scans the whole table.
const del = await sql`
DELETE FROM x402_audit_log
WHERE id IN (
SELECT id FROM x402_audit_log
WHERE created_at < now() - ${cutoffDays} * interval '1 day'
LIMIT ${AUDIT_BATCH}
) RETURNING id
`;
deleted += del.length;
if (del.length < AUDIT_BATCH) break;
}
return { deleted };
}
// B2b. Pending-settlement rows (x402_pending_settlements).
//
// A row exists to carry ONE x402 settlement whose transaction was broadcast
// before its outcome was known (api/_lib/x402/pending-settlements.js). Once
// /api/cron/x402-settlement-reconcile has recorded the chain's verdict the row is
// history: the money record itself lives in x402_self_facilitator_log and
// x402_audit_log, which have their own, longer windows.
//
// Only RESOLVED rows are prunable, and the cutoff is deliberately not the shared
// storage valve: an unresolved row is a payment nobody has accounted for yet, and
// deleting it under storage pressure would lose the only handle on it. Those stay
// until the chain answers, however long that takes.
const PENDING_SETTLEMENT_KEEP_DAYS = 30;
async function prunePendingSettlements() {
if (!(await tableExists('x402_pending_settlements'))) return { deleted: 0 };
const del = await sql`
DELETE FROM x402_pending_settlements
WHERE state <> 'pending'
AND resolved_at IS NOT NULL
AND resolved_at < now() - ${PENDING_SETTLEMENT_KEEP_DAYS} * interval '1 day'
RETURNING key
`;
return { deleted: del.length };
}
// ── B. avatar_regen_jobs hygiene ──────────────────────────────────────────────
async function pruneRegenJobs() {
if (!(await tableExists('avatar_regen_jobs'))) return { stripped: 0, deleted: 0 };
// Delete terminal jobs past 30 days first (DELETE frees space without
// extending a file, so it is safe even under storage pressure).
let deleted = 0;
for (let i = 0; i < REGEN_MAX_ITERS; i++) {
const del = await sql`
DELETE FROM avatar_regen_jobs
WHERE job_id IN (
SELECT job_id FROM avatar_regen_jobs
WHERE status IN ('done', 'failed') AND created_at < now() - interval '30 days'
LIMIT ${REGEN_DELETE_BATCH}
) RETURNING job_id
`;
deleted += del.length;
if (del.length < REGEN_DELETE_BATCH) break;
}
// Strip the multi-MB base64 source images from terminal jobs older than a day —
// never read for a finished job. This is an UPDATE (rewrites the row), so it can
// need to extend a file; if the branch is at the cap the DELETE prune above will
// have freed space, but guard anyway so a capacity blip degrades to "skip the
// strip this tick" rather than failing the whole cron.
let stripped = 0;
try {
for (let i = 0; i < REGEN_MAX_ITERS; i++) {
const upd = await sql`
UPDATE avatar_regen_jobs
SET params = (params - 'images') - 'image', updated_at = now()
WHERE job_id IN (
SELECT job_id FROM avatar_regen_jobs
WHERE status IN ('done', 'failed')
AND created_at < now() - interval '1 day'
AND (params ? 'images' OR params ? 'image')
LIMIT ${REGEN_STRIP_BATCH}
) RETURNING job_id
`;
stripped += upd.length;
if (upd.length < REGEN_STRIP_BATCH) break;
}
} catch (err) {
if (!isDbCapacityError(err)) throw err;
}
return { stripped, deleted };
}
// ── D. Bounded compaction under storage pressure ──────────────────────────────
// Every table this cron manages is a compaction candidate; nothing outside the
// retention set is ever rewritten (a VACUUM FULL on a hot product table would
// block its writers behind an exclusive lock).
const COMPACT_CANDIDATES = [
'pump_coin_intel',
...FIREHOSE_SATELLITES,
...TIME_SERIES_TABLES.map((t) => t.table),
...RUN_LOG_TABLES.map((t) => t.table),
'x402_audit_log',
'avatar_regen_jobs',
];
/**
* Pure target selection so the bounds are unit-testable. Keeps tables whose
* reclaimable space clears both the absolute and the ratio floor, orders them
* smallest file first (each rewrite needs headroom ≈ live size, so free the
* cheap space before attempting the big files near a hard cap), and caps how
* many one tick may rewrite.
*
* @param {object} a
* @param {Array<{table:string, tableMb:number, freeMb:number}>} a.candidates
* @param {number} a.minFreeMb
* @param {number} a.minFreeRatio
* @param {number} a.maxTables
*/
export function pickCompactionTargets({ candidates, minFreeMb, minFreeRatio, maxTables }) {
return candidates
.filter(
(c) =>
c.tableMb > 0 &&
c.freeMb >= minFreeMb &&
c.freeMb / c.tableMb >= minFreeRatio,
)
.sort((a, b) => a.tableMb - b.tableMb)
.slice(0, Math.max(0, maxTables));
}
// Measure reclaimable space per candidate and VACUUM FULL the picks. Wholly
// best-effort: pgstattuple missing, a lock we can't get, or a rewrite failing
// mid-tick degrades to "less compacted this tick", never a failed cron.
async function compactTables({ minFreeMb, maxTables }) {
const result = { measured: 0, compacted: [], skipped: [] };
try {
// Idempotent; Neon ships pgstattuple as an installable extension.
await sql`CREATE EXTENSION IF NOT EXISTS pgstattuple`;
} catch (err) {
result.skipped.push({ reason: 'pgstattuple_unavailable', detail: err?.message?.slice(0, 120) });
return result;
}
const candidates = [];
for (const t of COMPACT_CANDIDATES) {
if (!(await tableExists(t))) continue;
try {
// t is a fixed constant from the retention config above, safe to splice.
const [r] = await sql(`SELECT * FROM pgstattuple_approx('public.${t}'::regclass)`);
candidates.push({
table: t,
tableMb: Number(r.table_len) / 1048576,
freeMb: (Number(r.approx_free_space) + Number(r.dead_tuple_len)) / 1048576,
});
} catch {
/* a table we cannot measure is a table we do not rewrite */
}
}
result.measured = candidates.length;
const targets = pickCompactionTargets({
candidates,
minFreeMb,
minFreeRatio: COMPACT_MIN_FREE_RATIO,
maxTables,
});
for (const target of targets) {
const t0 = Date.now();
try {
await sql(`VACUUM FULL ${target.table}`);
const [after] = await sql(
`SELECT (pg_total_relation_size('${target.table}') / 1048576.0)::numeric(10,1) AS mb`,
);
result.compacted.push({
table: target.table,
before_mb: Math.round(target.tableMb),
after_mb: Number(after.mb),
took_ms: Date.now() - t0,
});
} catch (err) {
result.skipped.push({ table: target.table, reason: err?.message?.slice(0, 120) });
}
}
return result;
}
// ── C. Best-effort VACUUM of the tables we pruned ─────────────────────────────
async function vacuumTables(names) {
for (const t of names) {
try {
// Plain VACUUM (never FULL): FULL rewrites the whole table into a fresh
// file, needing free space ≈ the table's live size — the one thing we lack
// near the cap. Plain VACUUM marks dead tuples reusable and lets Neon's
// storage GC return the space. Single-statement over the HTTP driver runs
// in autocommit, so VACUUM's no-transaction rule is satisfied.
await sql(`VACUUM ${t}`);
} catch {
/* best-effort — reclaim happens on the next autovacuum regardless */
}
}
}
export default wrapCron(async (req, res) => {
if (!method(req, res, ['GET', 'POST'])) return;
if (!requireCron(req, res)) return;
const started = Date.now();
const retentionDays = clampInt(process.env.PUMP_INTEL_RETENTION_DAYS, 2, 365, 14);
const minDays = clampInt(process.env.PUMP_INTEL_MIN_RETENTION_DAYS, 1, retentionDays, 3);
const highWaterMb = clampInt(process.env.DB_RETENTION_HIGH_WATER_MB, 128, 100_000, 470);
// Audit ledger keeps its own, longer window (money records): default 90 days,
// tightening to a 30-day floor while the branch is over the high-water mark.
const auditDays = clampInt(process.env.X402_AUDIT_RETENTION_DAYS, 7, 3650, 90);
const auditMinDays = clampInt(process.env.X402_AUDIT_MIN_RETENTION_DAYS, 1, auditDays, 30);
// Spent-payment proofs: 90 days, FIXED. No pressure-tightened floor — a
// shorter window is a shorter replay-protection window, and the rows are too
// small to matter for storage. The 30-day lower bound stops a typo'd env var
// from silently shrinking the guard to hours.
const spentDays = clampInt(process.env.X402_SPENT_RETENTION_DAYS, 30, 3650, 90);
const sizeBeforeMb = await dbSizeMb();
const underPressure = sizeBeforeMb >= highWaterMb;
// Self-healing valve: tighten the window to the floor while the branch is over
// the high-water mark, so the cap is never actually reached; relax to the full
// window once GC has returned the freed space and size drops back under it.
const cutoffDays = underPressure ? minDays : retentionDays;
const auditCutoffDays = underPressure ? auditMinDays : auditDays;
const firehose = await pruneFirehose(cutoffDays);
const orphans = await pruneOrphanedSatellites(firehose.liveSatellites);
const series = await pruneTimeSeries({
firehose: cutoffDays,
audit: auditCutoffDays,
spent: spentDays,
mcpSessions: MCP_SESSION_RETENTION_DAYS,
});
const runLogs = await pruneRunLogs(cutoffDays);
const audit = await pruneAuditLog(auditCutoffDays);
const pendingSettlements = await prunePendingSettlements();
const regen = await pruneRegenJobs();
// E. Per-home action-log retention. Best-effort: the home tables may not exist
// on a branch that has not run the Home migrations, and a failure here must
// not cost the platform its storage sweep.
let homeActionLog = { deleted: 0, batches: 0, homes: 0 };
if (await tableExists('home_action_log')) {
try {
homeActionLog = await purgeExpiredActionLog();
} catch (err) {
homeActionLog = { deleted: 0, batches: 0, homes: 0, error: err?.message?.slice(0, 160) };
}
}
// F. MCP client analytics: raw session rows expire after 30 days, the daily
// aggregates stay. Best-effort for the same reason as E.
let mcpSessions = { deleted: 0 };
if (await tableExists('mcp_client_sessions')) {
try {
mcpSessions = await purgeExpiredMcpSessions();
} catch (err) {
mcpSessions = { deleted: 0, error: err?.message?.slice(0, 160) };
}
}
// VACUUM only tables we actually deleted from this tick.
const touched = Object.keys(firehose.perTable).filter((t) => firehose.perTable[t] > 0);
for (const t of Object.keys(orphans)) if (!touched.includes(t)) touched.push(t);
for (const t of Object.keys(series)) if (!touched.includes(t)) touched.push(t);
for (const t of Object.keys(runLogs)) if (!touched.includes(t)) touched.push(t);
if (audit.deleted > 0) touched.push('x402_audit_log');
if (pendingSettlements.deleted > 0) touched.push('x402_pending_settlements');
if (regen.deleted > 0 || regen.stripped > 0) touched.push('avatar_regen_jobs');
if (homeActionLog.deleted > 0) touched.push('home_action_log');
if (mcpSessions.deleted > 0) touched.push('mcp_client_sessions');
await vacuumTables(touched);
// D. Under pressure, actually shrink the files (plain VACUUM cannot) so the
// storage gate unlatches. Off-switch and bounds are env-tunable.
let compaction = { measured: 0, compacted: [], skipped: [] };
const compactEnabled = process.env.DB_COMPACT_ENABLED !== '0';
if (underPressure && compactEnabled) {
compaction = await compactTables({
minFreeMb: clampInt(process.env.DB_COMPACT_MIN_FREE_MB, 1, 10_000, COMPACT_MIN_FREE_MB),
maxTables: clampInt(process.env.DB_COMPACT_MAX_TABLES, 0, 20, COMPACT_MAX_TABLES),
});
}
const sizeAfterMb = await dbSizeMb();
const topTables = await topRelationsBySize();
// One deduped signal when the valve engages so ops knows storage is tight and a
// Neon plan bump (for a longer history window) is worth considering. Neon's GC
// is not instant, so sizeAfter may still read high right after a prune — that's
// expected; the space returns within the branch's history-retention window.
if (underPressure) {
const seriesDeleted = Object.values(series).reduce((a, b) => a + b, 0);
const orphansDeleted = Object.values(orphans).reduce((a, b) => a + b, 0);
const runLogsDeleted = Object.values(runLogs).reduce((a, b) => a + b, 0);
const topLine = topTables
.slice(0, 5)
.map((t) => `${t.table} ${t.mb}MB`)
.join(', ');
const compactLine = compaction.compacted.length
? ` Compacted: ${compaction.compacted.map((c) => `${c.table} ${c.before_mb}→${c.after_mb}MB`).join(', ')}.`
: '';
sendOpsAlert(
'db retention pressure valve engaged',
`db ${sizeBeforeMb}MB ≥ high-water ${highWaterMb}MB, tightened firehose retention to ${minDays}d; pruned ${firehose.mints} mints, ${seriesDeleted} series rows, ${runLogsDeleted} autopilot run-log rows, ${orphansDeleted} orphaned satellite rows.${compactLine} Largest tables: ${topLine || 'n/a'}. Raise the Neon storage plan (or DB_RETENTION_HIGH_WATER_MB / PUMP_INTEL_RETENTION_DAYS) for a longer window.`,
{ signature: 'db:retention-pressure' },
);
}
return json(res, 200, {
ok: true,
size_before_mb: sizeBeforeMb,
size_after_mb: sizeAfterMb,
high_water_mb: highWaterMb,
under_pressure: underPressure,
retention_days: retentionDays,
cutoff_days: cutoffDays,
audit_retention_days: auditDays,
audit_cutoff_days: auditCutoffDays,
spent_retention_days: spentDays,
firehose: { mints: firehose.mints, perTable: firehose.perTable },
orphans,
series,
run_logs: runLogs,
audit,
pending_settlements: pendingSettlements,
regen,
home_action_log: homeActionLog,
mcp_client_sessions: mcpSessions,
vacuumed: touched,
compaction,
top_tables: topTables,
took_ms: Date.now() - started,
});
});