Blame · Line-by-line history

`0061_pr_live_sessions.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0061_pr_live_sessions.sqlBlame52 lines · 1 contributor

Normal view Raw

3c03977Claude	1	-- Live co-editing on PRs — presence + cursor + content sync.
	2	--
	3	-- A `pr_live_sessions` row represents a single browser tab (or agent
	4	-- runtime) actively editing a PR's description, comments, or inline
	5	-- diff annotations. The SSE endpoint at /api/v2/pulls/:prId/live
	6	-- fans presence + cursor + content patches out to every subscriber
	7	-- on the same PR.
	8	--
	9	-- Either `user_id` OR `agent_session_id` is non-null (XOR-ish at the
	10	-- app layer; the DB just keeps both nullable so the same table can
	11	-- represent humans and AI agents in one stream). `color` is a stable
	12	-- per-user hue picked deterministically by the lib so concurrent tabs
	13	-- of the same user share a colour and the cursor ribbon stays steady.
	14	--
	15	-- `cursor_position` is JSON shaped like:
	16	-- { "field": "description" \| "comment_<uuid>" \| "line_<path>:<n>",
	17	-- "range": { "start": number, "end": number } }
	18	-- We keep this opaque at the DB layer so future field types (e.g. a
	19	-- new "review_summary" textarea) don't require a migration.
	20	--
	21	-- Lifecycle:
	22	-- joined_at — when the session was first registered.
	23	-- last_seen_at — touched by every heartbeat (15s cadence from the
	24	-- client) and every cursor/edit broadcast.
	25	-- status — 'active' \| 'idle' (>60s no heartbeat) \| 'left'
	26	-- (>5m no heartbeat or explicit leave).
	27	-- The autopilot pr-live-cleanup task transitions stale rows; the SSE
	28	-- subscriber also lazily updates status when it notices a stale peer.
	29
	30	CREATE TABLE IF NOT EXISTS pr_live_sessions (
	31	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	32	pr_id uuid NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
	33	user_id uuid REFERENCES users(id) ON DELETE CASCADE,
	34	agent_session_id uuid REFERENCES agent_sessions(id) ON DELETE CASCADE,
	35	cursor_position jsonb,
	36	color text NOT NULL,
	37	joined_at timestamptz NOT NULL DEFAULT now(),
	38	last_seen_at timestamptz NOT NULL DEFAULT now(),
	39	status text NOT NULL DEFAULT 'active'
	40	);
	41
	42	-- Active-presence lookup: "who is here on PR X right now?" — answered
	43	-- in O(1) by this partial index. Status transitions to 'left' drop the
	44	-- row from the index automatically.
	45	CREATE INDEX IF NOT EXISTS pr_live_sessions_active_pr
	46	ON pr_live_sessions (pr_id, last_seen_at)
	47	WHERE status <> 'left';
	48
	49	-- Stale-sweep lookup: the autopilot cleanup task scans rows whose
	50	-- last_seen_at is older than the idle/left thresholds.
	51	CREATE INDEX IF NOT EXISTS pr_live_sessions_status_seen
	52	ON pr_live_sessions (status, last_seen_at);