mirror of
https://github.com/screentinker/screentinker.git
synced 2026-06-29 09:23:16 -06:00
29a8896aa8
The per-device insert-time prune (deviceSocket.js) only ever touches a device that is actively inserting, so it misses two paths: removed/idle devices whose rows linger forever, and heartbeat.js's offline_timeout insert that bypasses logDeviceStatus entirely. The reporter's 1.2M-row bloat accumulated UNDER a 7-day per-device prune for exactly this reason. - pruneStatusLog() (db/database.js): a GLOBAL time-range sweep across ALL devices, modeled on the play_logs prune. Run once on startup (recovers a bloated table right after deploy) and on the heartbeat interval (services/heartbeat.js). - STATUS_LOG_RETENTION_DAYS env, default 3 (lower than the old hardcoded 7d; the dashboard only shows a 24h uptime window, so 2-3d is ample for diagnostics). - Deliberately NO per-device row cap: Step 3's throttle already bounds how fast a storming device can generate status rows, so a cap would add sweep complexity for little gain (noted for later if needed). - NO VACUUM / auto_vacuum here (kept off the hot path); space reclaim is left as a separate decision (see report). test: deterministic in-process unit test proves the sweep deletes over-retention rows across all devices — including a device absent from the devices table and an offline_timeout row — while keeping recent rows; idempotent on an empty table. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
49 lines
2.4 KiB
JavaScript
49 lines
2.4 KiB
JavaScript
'use strict';
|
|
|
|
// #142 step 4 — global device_status_log retention sweep. Deterministic, in-process
|
|
// (no server/port). Isolate the DB and set retention BEFORE requiring the module
|
|
// (config reads env at load; database.js initialises a DB on load).
|
|
|
|
const os = require('node:os');
|
|
const path = require('node:path');
|
|
const crypto = require('node:crypto');
|
|
process.env.DATA_DIR = path.join(os.tmpdir(), 'st-statusprune-' + crypto.randomBytes(4).toString('hex'));
|
|
process.env.STATUS_LOG_RETENTION_DAYS = '2';
|
|
|
|
const { test } = require('node:test');
|
|
const assert = require('node:assert/strict');
|
|
const { db, pruneStatusLog } = require('../db/database');
|
|
|
|
test('global sweep deletes rows older than retention across ALL devices, keeps recent', () => {
|
|
db.exec('DELETE FROM device_status_log'); // clean slate
|
|
const old = db.prepare("INSERT INTO device_status_log (device_id, status, timestamp) VALUES (?, ?, strftime('%s','now') - ?)");
|
|
|
|
// 5 days old (> 2d retention): an active device, a device NOT in the devices
|
|
// table (removed/idle — what the per-device insert-time prune never revisits),
|
|
// and the heartbeat offline_timeout status that bypasses logDeviceStatus.
|
|
old.run('live-dev', 'online', 5 * 86400);
|
|
old.run('removed-idle-dev', 'offline', 5 * 86400);
|
|
old.run('hb-dev', 'offline_timeout', 5 * 86400);
|
|
// recent (< retention): must survive, regardless of device existence / status.
|
|
old.run('live-dev', 'online', 0);
|
|
old.run('hb-dev', 'offline_timeout', 3600);
|
|
|
|
assert.equal(db.prepare('SELECT COUNT(*) c FROM device_status_log').get().c, 5, 'seeded 5 rows');
|
|
|
|
const deleted = pruneStatusLog();
|
|
assert.equal(deleted, 3, 'the 3 over-retention rows pruned (incl. removed-idle + offline_timeout paths)');
|
|
|
|
const remaining = db.prepare('SELECT device_id, status FROM device_status_log ORDER BY device_id').all();
|
|
assert.equal(remaining.length, 2);
|
|
// both survivors are the recent rows; no old row of any device/status survived
|
|
assert.deepEqual(remaining.map(r => r.device_id).sort(), ['hb-dev', 'live-dev']);
|
|
const oldestNow = db.prepare("SELECT MIN(timestamp) m FROM device_status_log").get().m;
|
|
const cutoff = Math.floor(Date.now() / 1000) - 2 * 86400;
|
|
assert.ok(oldestNow >= cutoff, 'no surviving row is older than the retention cutoff');
|
|
});
|
|
|
|
test('sweep is safe and idempotent on an empty/already-clean table', () => {
|
|
db.exec('DELETE FROM device_status_log');
|
|
assert.equal(pruneStatusLog(), 0, 'nothing to delete -> 0, no throw');
|
|
});
|