bvandeusen
258bc1f75c
New `internal/gc` package with a single Worker that runs all five lifecycle / retention sweeps from the 2026-06-02 drift audit on a 1-hour tick. Each sweep is small, idempotent (re-running on already-clean rows is a no-op), and logs its affected-row count. Sweeps (Scribe parent #552): - **#566** GcCloseStalePlayEvents — play_events rows opened > 24h ago that never got a play_ended (client crash, network drop). Synthesizes ended_at from duration_played_ms when known, falls back to now() so the row stops looking "open" to downstream filters (ended_at IS NULL). - **#565** GcClosePlaySessionsWithNoRecentEvents — play_sessions with last_event_at older than 6h get ended_at = last_event_at ("user moved on"); empty sessions older than 1h get closed too (stale handshakes from clients that never recorded a play). The audit caught that the column was added but never populated by any writer — every session row was "open" forever, breaking downstream dedup queries that assume closed semantics. - **#567** GcExpireScrobbleQueueFailedRows — drops scrobble_queue rows in status='failed' older than 14 days. The worker stops retrying after maxAttempts so these otherwise accumulate forever on a persistent ListenBrainz outage / revoked token. - **#574** GcResetStuckSystemPlaylistRuns — flips system_playlist_runs.in_flight back to false on rows whose last_run_at is older than 10 minutes. Catches goroutine-panic wedges where the generator died between SET in_flight=true and SET in_flight=false; the duplicate-prevention check refuses to start a fresh regen while in_flight, so a stuck row would otherwise deadlock all future regens for that user. Records "stuck-row auto-reset by gc" in last_error so the operator can tell auto-reset from a recent real failure. - **#575** GcDeleteExpiredPasswordResets — deletes expired password_resets rows. Unused expired rows go after a 1h grace (gives the operator time to debug an active reset attempt); used rows are kept 7 days for audit. Wiring: - main.go `go gcWorker.Run(ctx)` alongside the other periodic workers (scrobble, similarity, lidarr). - tickOnce fires once at start so a freshly-deployed server does its initial sweep without waiting a full tick, matching the scrobble worker pattern. - Errors per sweep are logged but do NOT abort the remaining ones — a transient pgx error from one query shouldn't prevent the others from running. Tests: - 4 integration tests, one per UPDATE/DELETE sweep, that seed rows-to-sweep + rows-to-leave-alone and assert the right rows changed state. Skip unless MINSTREL_TEST_DATABASE_URL is set (mirrors the api package pattern). - Empty-tables no-op smoke test. - Run() cancellation honoured (no spinning goroutine at test-runner exit). That's all five remaining server-side lifecycle findings from the audit. The Android LOCAL_USER_ID hardcode (#576) is a separate refactor that needs auth-store wiring and stays in the queue.
71 lines
3.3 KiB
SQL
71 lines
3.3 KiB
SQL
-- Background garbage-collector / lifecycle queries. All five address
|
|
-- drift findings from the 2026-06-02 audit (Scribe parent #552):
|
|
-- #565 #566 #567 #574 #575. Sweeper runs on a long tick (default 1h)
|
|
-- so per-query cost is amortised; each is idempotent (re-running on
|
|
-- already-closed/-deleted rows is a no-op).
|
|
|
|
-- name: GcCloseStalePlayEvents :execrows
|
|
-- #566: play_events rows opened more than 24h ago that never got a
|
|
-- play_ended. Client crashed mid-track, network dropped, etc. We
|
|
-- synthesize ended_at = started_at + duration_played_ms when present,
|
|
-- otherwise leave duration_played_ms null and stamp ended_at = now()
|
|
-- so the row stops looking "open" for downstream queries that filter
|
|
-- ended_at IS NULL. Doesn't touch was_skipped (we genuinely don't
|
|
-- know if the user skipped).
|
|
UPDATE play_events
|
|
SET ended_at = COALESCE(
|
|
started_at + (duration_played_ms * INTERVAL '1 millisecond'),
|
|
now()
|
|
)
|
|
WHERE ended_at IS NULL
|
|
AND started_at < now() - INTERVAL '24 hours';
|
|
|
|
-- name: GcClosePlaySessionsWithNoRecentEvents :execrows
|
|
-- #565: play_sessions.ended_at was added but never populated by any
|
|
-- writer. Close sessions whose last_event_at is older than 6h —
|
|
-- treating that as "user moved on" the same way audio_service does
|
|
-- after grace periods. Sessions with NO events (track_count = 0)
|
|
-- older than 1h are also closed (stale handshakes from clients that
|
|
-- never recorded a play). ended_at is set to last_event_at so the
|
|
-- session's duration reads naturally.
|
|
UPDATE play_sessions
|
|
SET ended_at = COALESCE(last_event_at, started_at)
|
|
WHERE ended_at IS NULL
|
|
AND (
|
|
(track_count > 0 AND last_event_at < now() - INTERVAL '6 hours')
|
|
OR
|
|
(track_count = 0 AND started_at < now() - INTERVAL '1 hour')
|
|
);
|
|
|
|
-- name: GcExpireScrobbleQueueFailedRows :execrows
|
|
-- #567: scrobble_queue rows that have been in status='failed' for
|
|
-- more than 14 days. The worker stops retrying after maxAttempts;
|
|
-- failed rows accumulate forever otherwise. CASCADE from play_events
|
|
-- already drops the row when the underlying event is deleted, so this
|
|
-- only handles persistent failures (token revoked, etc.).
|
|
DELETE FROM scrobble_queue
|
|
WHERE status = 'failed'
|
|
AND enqueued_at < now() - INTERVAL '14 days';
|
|
|
|
-- name: GcResetStuckSystemPlaylistRuns :execrows
|
|
-- #574: system_playlist_runs.in_flight = true can wedge on a
|
|
-- goroutine panic between SET in_flight=true and SET in_flight=false.
|
|
-- The duplicate-prevention check refuses to start a fresh regen while
|
|
-- in_flight, so a stuck row blocks all future regens for that user.
|
|
-- Reset rows where last_run_at is older than 10 minutes (regens
|
|
-- shouldn't take that long).
|
|
UPDATE system_playlist_runs
|
|
SET in_flight = false,
|
|
last_error = COALESCE(last_error, 'stuck-row auto-reset by gc')
|
|
WHERE in_flight = true
|
|
AND last_run_at < now() - INTERVAL '10 minutes';
|
|
|
|
-- name: GcDeleteExpiredPasswordResets :execrows
|
|
-- #575: password_resets accumulates expired + used rows forever. The
|
|
-- validation path already rejects them; this just keeps the table
|
|
-- from growing unbounded. Used rows are kept for 7 days for audit;
|
|
-- unused expired rows go immediately.
|
|
DELETE FROM password_resets
|
|
WHERE (used_at IS NOT NULL AND used_at < now() - INTERVAL '7 days')
|
|
OR (used_at IS NULL AND expires_at < now() - INTERVAL '1 hour');
|