max / goingson

3.3 KB · 73 lines History Blame Raw

1	-- The Problems inbox: a local mirror of problems pulled from external sources.
2	--
3	-- GoingsOn is the list of SOLUTIONS (tasks). Other systems (wam tickets, /audit
4	-- and /fuzz findings, later PoM alerts) are lists of PROBLEMS. A problem is a
5	-- candidate a human promotes into a task; nothing here auto-creates one.
6	--
7	-- This is a cached mirror, not the record of truth: adapters pull from each
8	-- source and upsert on (source, source_ref). That is why the table is absent
9	-- from the SyncKit manifest in src-tauri/src/syncstore/manifest.rs. Rows rebuild
10	-- from their source, so syncing them would replicate regenerable data. The
11	-- non-regenerable part is the triage decision, which lives in `status` and
12	-- `promoted_task_id` and survives a re-pull because upserts do not touch it.
13	--
14	-- Urgency is not stored. It is the painhours score, computed from pain, scale,
15	-- and age by the shared `painhours` crate, so the ranking matches wam's.
16
17	CREATE TABLE problems (
18	id TEXT PRIMARY KEY NOT NULL,
19	user_id TEXT NOT NULL,
20
21	-- Provenance. `source` names the adapter ('wam', 'audit', 'fuzz'), and
22	-- `source_ref` is the id within it. Together they are the upsert key, the
23	-- same idea as go-mcp's `source:` task tags.
24	source TEXT NOT NULL,
25	source_ref TEXT NOT NULL,
26
27	title TEXT NOT NULL,
28	body TEXT NOT NULL DEFAULT '',
29
30	-- The painhours inputs, 1-5 guesstimates. Clamped on read, so a source that
31	-- sends nonsense degrades to the nearest valid factor.
32	pain INTEGER NOT NULL DEFAULT 3,
33	scale INTEGER NOT NULL DEFAULT 3,
34
35	-- Open \| Promoted \| Dismissed \| Resolved. Anything but Open is settled and
36	-- stops climbing the list (see settled_at).
37	status TEXT NOT NULL DEFAULT 'Open',
38
39	project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
40	tags TEXT NOT NULL DEFAULT '[]',
41
42	-- Set when the problem is promoted; the backlink from problem to solution.
43	-- ON DELETE SET NULL so deleting the task reopens triage rather than losing
44	-- the problem.
45	promoted_task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
46
47	-- The SOURCE's creation instant, not ours: age drives the painhours score,
48	-- so a ticket filed weeks ago must not read as new because we pulled it
49	-- today.
50	created_at TEXT NOT NULL,
51	updated_at TEXT NOT NULL,
52
53	-- The instant the problem left the open set (promoted, dismissed, or
54	-- resolved upstream). Freezes the age anchor so settled problems stop
55	-- climbing. NULL while Open.
56	settled_at TEXT,
57
58	-- Last pull that saw this problem in its source. Lets a source-gone row be
59	-- shown as stale rather than silently deleted.
60	last_seen_at TEXT NOT NULL
61	);
62
63	-- The upsert key. A re-pull updates the existing row instead of duplicating it.
64	CREATE UNIQUE INDEX idx_problems_provenance ON problems(user_id, source, source_ref);
65
66	-- The inbox query: open problems for a user, optionally narrowed by source or
67	-- project. Final ranking is by painhours, which is time-dependent and therefore
68	-- computed in Rust after the fetch (as wam does), not sorted here.
69	CREATE INDEX idx_problems_status ON problems(user_id, status);
70	CREATE INDEX idx_problems_source ON problems(user_id, source);
71	CREATE INDEX idx_problems_project ON problems(project_id);
72	CREATE INDEX idx_problems_promoted_task ON problems(promoted_task_id);
73