max / goingson

1.1 KB · 28 lines History Blame Raw

1	-- Enforce at most one running timer per user at the schema level.
2	--
3	-- `start_timer` checks "is a session already running?" and inserts inside a
4	-- DEFERRED transaction whose read takes no write lock, so two concurrent calls
5	-- could both pass the check and insert (ultra-fuzz Run #27 TOCTOU). The guarding
6	-- index was non-unique, so nothing stopped the double insert. Make it a partial
7	-- UNIQUE index instead.
8
9	-- First, resolve any pre-existing duplicates so the unique index can be created:
10	-- keep the most recently started running session per user, close the rest with a
11	-- zero-length window (ended_at = started_at).
12	UPDATE time_sessions
13	SET ended_at = started_at
14	WHERE ended_at IS NULL
15	AND id NOT IN (
16	SELECT id FROM (
17	SELECT id,
18	ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at DESC, id DESC) AS rn
19	FROM time_sessions
20	WHERE ended_at IS NULL
21	)
22	WHERE rn = 1
23	);
24
25	DROP INDEX IF EXISTS idx_time_sessions_active;
26	CREATE UNIQUE INDEX idx_time_sessions_active
27	ON time_sessions(user_id) WHERE ended_at IS NULL;
28