Skip to main content

max / goingson

6.0 KB · 106 lines History Blame Raw
1 -- Task dependencies: the blocking DAG.
2 --
3 -- One relation, one direction: `blocker_id` must reach Completed before
4 -- `blocked_id` can be started. Nothing else lives here. GoingsOn already has a
5 -- second task-to-task link in `subtasks.linked_task_id`, and it means something
6 -- different: that is containment, a checklist item whose tick mirrors another
7 -- task. Overloading it to also mean blocking would have made "is this task
8 -- available" unanswerable without knowing which of the two a given row meant.
9 --
10 -- There is deliberately no `kind` column. A second edge type gets designed when
11 -- there is a second edge type, not speculatively, and a nullable discriminator
12 -- nothing reads is a column every query has to remember to filter on.
13 --
14 -- Ownership rides through `tasks`, the same as subtasks, annotations, and
15 -- task_status_tokens: no `user_id` here, and every read joins to the task it
16 -- hangs off. One place decides who owns an edge, and it is the place that owns
17 -- the endpoints.
18 --
19 -- The row id is deterministic (UUID v5 over `<blocker_id>:<blocked_id>`), the
20 -- trick `task_status_tokens` uses. Two devices that draw the same edge offline
21 -- generate the same id, so the changelog's id-keyed upsert collapses them into
22 -- one row instead of two rows saying the same thing.
23 --
24 -- Acyclicity is NOT enforceable here. SQLite has no deferred graph constraint,
25 -- and more to the point sync can manufacture a cycle out of two individually
26 -- legal edges: device A adds X blocks Y while device B adds Y blocks X, and
27 -- each is fine until they meet. So the repository rejects a cycle-closing edge
28 -- on write, and every traversal carries a visited set and treats a task inside
29 -- a cycle as blocked. A cycle is a thing to report and repair, never a hang.
30
31 CREATE TABLE task_dependencies (
32 id TEXT PRIMARY KEY NOT NULL,
33 blocked_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
34 blocker_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
35 created_at TEXT NOT NULL DEFAULT (datetime('now')),
36 group_id TEXT,
37
38 -- A task blocking itself is a cycle of length one, the only cycle cheap
39 -- enough to refuse in the schema. The rest is the repository's job.
40 CHECK (blocked_id <> blocker_id)
41 );
42
43 -- The edge identity. Redundant with the deterministic primary key by
44 -- construction, and kept anyway: it is what makes the redundancy checkable, so
45 -- a caller that ever generates a random id fails loudly here instead of
46 -- quietly double-drawing an edge.
47 CREATE UNIQUE INDEX idx_task_dependencies_edge
48 ON task_dependencies(blocked_id, blocker_id);
49
50 -- Both directions are hot. "What blocks this task" walks blocked_id (covered by
51 -- the unique index above) and "what does finishing this release" walks
52 -- blocker_id, which needs its own.
53 CREATE INDEX idx_task_dependencies_blocker ON task_dependencies(blocker_id);
54
55 -- The task's position in the graph, cached on the task.
56 --
57 -- All three fall out of one traversal, so they are written by one recompute and
58 -- read without a join. That is the whole reason they are columns: the blocked
59 -- badge on a task row, the `--depth` filter, and the sort all want graph facts
60 -- on every row of a list, and a recursive CTE per row is not a thing a list
61 -- query can afford.
62 --
63 -- block_depth 0 when nothing incomplete blocks this task, so it is ready
64 -- to start now. Otherwise the LONGEST chain of incomplete
65 -- blockers ahead of it, which is the number of sequential
66 -- steps before it can begin. Longest and not shortest,
67 -- because a task waits for all of its blockers, not the
68 -- first one to clear.
69 -- unblocks_count How many tasks are transitively downstream, which is what
70 -- finishing this one frees. The critical-path signal.
71 -- in_cycle Whether the task sits on a dependency cycle. A cycle cannot
72 -- be drawn through the repository, but sync can merge one out
73 -- of two individually legal edges, and a task on one can never
74 -- become ready. It is a column rather than an inference so the
75 -- broken graphs are queryable and can be surfaced for repair
76 -- instead of just reading as permanently blocked.
77 -- graph_urgency The sort term derived from the three above.
78 --
79 -- `urgency` stays exactly what it always was: a pure function of the task's own
80 -- fields, written at task-write time, and synced. The graph term is deliberately
81 -- a separate column rather than folded into it. Blocked-ness is a property of
82 -- the whole reachable subgraph, so folding it in would mean every completion
83 -- rewrites the stored urgency of everything downstream, and two devices would
84 -- then contest a column neither of them meaningfully edited.
85 --
86 -- All three are local caches, absent from the sync manifest. `task_dependencies`
87 -- is synced and task statuses are synced, so every device derives identical
88 -- values from identical inputs. Replicating the derivation as well would be
89 -- storing a regenerable, and would invite the two copies to disagree.
90 --
91 -- Zero is the correct value for all three on a task with no edges, which is why
92 -- the backfill is the default and there is no UPDATE below. Installs with
93 -- existing tasks have no edges yet, so every row is already right.
94 ALTER TABLE tasks ADD COLUMN block_depth INTEGER NOT NULL DEFAULT 0;
95 ALTER TABLE tasks ADD COLUMN unblocks_count INTEGER NOT NULL DEFAULT 0;
96 ALTER TABLE tasks ADD COLUMN in_cycle INTEGER NOT NULL DEFAULT 0;
97 ALTER TABLE tasks ADD COLUMN graph_urgency REAL NOT NULL DEFAULT 0.0;
98
99 -- The ready-work query: everything a user can start right now, which is every
100 -- mode of /threads except the ones that deliberately look further back.
101 CREATE INDEX idx_tasks_block_depth ON tasks(user_id, block_depth);
102
103 -- Sorting reads `urgency + graph_urgency`, so neither single-column index
104 -- serves it. This is the one the task list actually uses.
105 CREATE INDEX idx_tasks_effective_urgency ON tasks(user_id, (urgency + graph_urgency) DESC);
106