| 1 | import { UNCONFIRMED_DAYS } from "./early-access.js"; |
| 2 | |
| 3 | /** |
| 4 | * Rows D1 doesn't need to keep, deleted a bounded batch per table each hour so the tables stop |
| 5 | * growing with every request, notification and job (scalability evaluation, 2026-10-14): |
| 6 | * |
| 7 | * - sessions a day past their expiry (a session is refused once expired; the day keeps a just-expired |
| 8 | * one visible on the sessions page meanwhile); |
| 9 | * - notifications older than NOTIFICATION_RETENTION_DAYS, as GitHub drops them after five months; |
| 10 | * - hosted compute records (lib/quotas.ts) ended more than COMPUTE_RETENTION_DAYS ago, past any month |
| 11 | * the usage pages show; |
| 12 | * - early access requests (lib/early-access.ts) never confirmed within UNCONFIRMED_DAYS of their last |
| 13 | * submission, and the form's daily-limit rows once they are a day old. |
| 14 | */ |
| 15 | export const NOTIFICATION_RETENTION_DAYS = 180; |
| 16 | export const COMPUTE_RETENTION_DAYS = 400; |
| 17 | /** Rows deleted per table per run: an hourly run keeps up with 120,000 new rows a day per table. */ |
| 18 | export const RETENTION_BATCH = 5000; |
| 19 | |
| 20 | export async function pruneExpired(db: D1Database, at = new Date(), batch = RETENTION_BATCH): Promise<Record<"sessions" | "notifications" | "compute_usage" | "early_access" | "early_access_attempts", number>> { |
| 21 | const ago = (days: number) => new Date(at.getTime() - days * 86400_000).toISOString(); |
| 22 | const [sessions, notifications, compute, early, attempts] = await db.batch([ |
| 23 | db.prepare("DELETE FROM sessions WHERE id IN (SELECT id FROM sessions WHERE expires_at < ? LIMIT ?)").bind(ago(1), batch), |
| 24 | db.prepare("DELETE FROM notifications WHERE id IN (SELECT id FROM notifications WHERE created_at < ? LIMIT ?)").bind(ago(NOTIFICATION_RETENTION_DAYS), batch), |
| 25 | db.prepare("DELETE FROM compute_usage WHERE id IN (SELECT id FROM compute_usage WHERE ended_at < ? LIMIT ?)").bind(ago(COMPUTE_RETENTION_DAYS), batch), |
| 26 | db.prepare("DELETE FROM early_access WHERE id IN (SELECT id FROM early_access WHERE state = 'unconfirmed' AND updated_at < ? LIMIT ?)").bind(ago(UNCONFIRMED_DAYS), batch), |
| 27 | db.prepare("DELETE FROM early_access_attempts WHERE id IN (SELECT id FROM early_access_attempts WHERE created_at < ? LIMIT ?)").bind(ago(1), batch), |
| 28 | ]); |
| 29 | return { |
| 30 | sessions: sessions!.meta.changes ?? 0, |
| 31 | notifications: notifications!.meta.changes ?? 0, |
| 32 | compute_usage: compute!.meta.changes ?? 0, |
| 33 | early_access: early!.meta.changes ?? 0, |
| 34 | early_access_attempts: attempts!.meta.changes ?? 0, |
| 35 | }; |
| 36 | } |