max / goingson

3.3 KB · 76 lines History Blame Raw

1	-- Task status tokens: at-a-glance provenance/state markers attached to a task.
2	--
3	-- A status token is a typed marker whose `state` maps to a colour indicator on the
4	-- task (neutral when a task has none, amber for `Pending`, green for `Complete`). The
5	-- first `kind` is `commit` — a repo-qualified `<repo>@<shortsha>` reference (e.g.
6	-- `deox@7c236fca8`, bare short SHAs are not unique across the ecosystem's repos) that
7	-- is `Pending` until pushed and `Complete` once pushed. The abstraction is
8	-- deliberately general so later kinds (e.g. `attachment`) register alongside it. At
9	-- most one token per task may be `is_primary` — the one that resolved the task.
10	--
11	-- Row ids are deterministic (UUID v5 of `<task_id>:<kind>:<reference>`, see
12	-- core::models::task::StatusToken::deterministic_id), so recording the same token
13	-- twice — locally or from two devices — collapses to one row through the sync
14	-- changelog's ON CONFLICT(id) apply. That is why there is no UNIQUE(task_id, kind,
15	-- reference) index: such an index would raise a constraint the id-keyed upsert cannot
16	-- absorb when a second device's row (new id, same triple) arrives on pull.
17
18	CREATE TABLE task_status_tokens (
19	id TEXT PRIMARY KEY NOT NULL,
20	task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
21	kind TEXT NOT NULL,
22	reference TEXT NOT NULL,
23	state TEXT NOT NULL DEFAULT 'Pending',
24	is_primary INTEGER NOT NULL DEFAULT 0,
25	position INTEGER NOT NULL DEFAULT 0,
26	created_at TEXT NOT NULL DEFAULT (datetime('now'))
27	);
28
29	CREATE INDEX idx_task_status_tokens_task_id ON task_status_tokens(task_id);
30
31	-- ── sync triggers (id, task_id, kind, reference, state, is_primary, position, created_at) ──
32	-- Emitted json_object keys must equal the apply-side SYNCED_COLUMNS whitelist for
33	-- `task_status_tokens`; the trigger-vs-whitelist round-trip test keeps the two in step.
34
35	CREATE TRIGGER IF NOT EXISTS sync_trg_task_status_tokens_insert
36	AFTER INSERT ON task_status_tokens
37	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
38	BEGIN
39	INSERT INTO sync_changelog (table_name, op, row_id, data)
40	VALUES ('task_status_tokens', 'INSERT', NEW.id, json_object(
41	'id', NEW.id,
42	'task_id', NEW.task_id,
43	'kind', NEW.kind,
44	'reference', NEW.reference,
45	'state', NEW.state,
46	'is_primary', NEW.is_primary,
47	'position', NEW.position,
48	'created_at', NEW.created_at
49	));
50	END;
51
52	CREATE TRIGGER IF NOT EXISTS sync_trg_task_status_tokens_update
53	AFTER UPDATE ON task_status_tokens
54	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
55	BEGIN
56	INSERT INTO sync_changelog (table_name, op, row_id, data)
57	VALUES ('task_status_tokens', 'UPDATE', NEW.id, json_object(
58	'id', NEW.id,
59	'task_id', NEW.task_id,
60	'kind', NEW.kind,
61	'reference', NEW.reference,
62	'state', NEW.state,
63	'is_primary', NEW.is_primary,
64	'position', NEW.position,
65	'created_at', NEW.created_at
66	));
67	END;
68
69	CREATE TRIGGER IF NOT EXISTS sync_trg_task_status_tokens_delete
70	AFTER DELETE ON task_status_tokens
71	WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
72	BEGIN
73	INSERT INTO sync_changelog (table_name, op, row_id, data)
74	VALUES ('task_status_tokens', 'DELETE', OLD.id, NULL);
75	END;
76