keep only the twenty redeemed codes the machines list shows, so kepr.db stops growing with every sign-in (v3.0.74)

@@ -1,6 +1,6 @@11 {22 	"name": "kepr",3-	"version": "3.0.73",3+	"version": "3.0.74",44 	"private": true,55 	"type": "module",66 	"scripts": {

@@ -4,6 +4,7 @@44 import { type Html, html } from "../pages/html.ts";55 import { type PageContext, page, plural } from "../pages/layout.ts";66 import { backups, newestBackupTarball } from "../server/backup.ts";7+import { MACHINES_LISTED } from "../server/connect.ts";78 import { formToken } from "../server/login.ts";89 910 export type AdminContext = PageContext & { sync: SyncTarget; dataDir: string; canConnect: boolean };@@ -66,16 +67,16 @@6667 	return total;6768 }6869 69-/** The last 20 machines that redeemed a code, and how to take their access away again. */70+/** The last machines that redeemed a code, and how to take their access away again. */7071 function connectedMachines(ctx: AdminContext): Html | "" {7172 	const rows = ctx.kepr72-		.query<{ machine: string; used_at: number }, []>(73+		.query<{ machine: string; used_at: number }, [number]>(7374 			`SELECT machine, used_at FROM connect_codes7475 			 WHERE used_at IS NOT NULL7576 			 ORDER BY used_at DESC76-			 LIMIT 20`,77+			 LIMIT ?`,7778 		)78-		.all();79+		.all(MACHINES_LISTED);7980 	if (rows.length === 0) return "";8081 	const led = rows.map((row) => {8182 		const at = new Date(row.used_at * 1000).toISOString();

@@ -9,6 +9,8 @@99 // No I, O, 0 or 1: a code is read off a screen and typed into another machine.1010 const ALPHABET = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789";1111 const CODE_SECONDS = 600;12+/** How many redeemed codes the status page lists, and so how many kepr.db keeps. */13+export const MACHINES_LISTED = 20;1214 const ATTEMPTS_PER_ADDRESS = 10;1315 const ATTEMPT_WINDOW_MS = 3600000;1416 // One body for every failure — unknown, expired, used, malformed and rate-limited alike — so a@@ -134,6 +136,18 @@134136 			)135137 			.run(at, sha256(submitted), at);136138 		if (redeemed.changes !== 1) return noCode(c);139+		ctx.kepr140+			.query(141+				`DELETE FROM connect_codes142+				 WHERE used_at IS NOT NULL143+				 AND code_hash NOT IN (144+					SELECT code_hash FROM connect_codes145+					WHERE used_at IS NOT NULL146+					ORDER BY used_at DESC147+					LIMIT ?148+				 )`,149+			)150+			.run(MACHINES_LISTED);137151 		return c.json({138152 			endpoint: bucket.endpoint,139153 			bucket: bucket.name,

@@ -330,7 +330,7 @@330330 331331 describe.serial("connected machines in admin", () => {332332 	test("lists redeemed machines newest first, at most twenty, with revocation guidance", async () => {333-		const { app, browser } = await connected();333+		const { app, browser, kepr } = await connected();334334 		for (let i = 0; i < 22; i++) {335335 			const { code } = await issue(browser, `machine-${i}`);336336 			const response = await redeem(app, code, `10.0.0.${i + 1}`);@@ -345,5 +345,13 @@345345 		expect(html).not.toContain("machine-0 ");346346 		expect(html).toContain("machine-21");347347 		expect(html.indexOf("machine-21")).toBeLessThan(html.indexOf("machine-20"));348+		// Only what the list can show is kept: the two oldest redemptions are gone from kepr.db.349+		const kept = kepr350+			.query<{ machine: string }, []>(351+				"SELECT machine FROM connect_codes WHERE used_at IS NOT NULL ORDER BY used_at",352+			)353+			.all();354+		expect(kept.length).toBe(20);355+		expect(kept[0]?.machine).toBe("machine-2");348356 	});349357 });