Skip to main content

max / goingson

5.1 KB · 138 lines History Blame Raw
1 -- Time tracking: estimated/actual minutes on tasks, time sessions table.
2 -- Sessions track start/stop periods per task, supporting multiple sessions
3 -- and pause/resume. A cached actual_minutes column on tasks avoids re-aggregation.
4
5 ALTER TABLE tasks ADD COLUMN estimated_minutes INTEGER;
6 ALTER TABLE tasks ADD COLUMN actual_minutes INTEGER NOT NULL DEFAULT 0;
7
8 CREATE TABLE time_sessions (
9 id TEXT PRIMARY KEY NOT NULL,
10 task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
11 user_id TEXT NOT NULL REFERENCES users(id),
12 started_at TEXT NOT NULL,
13 ended_at TEXT,
14 duration_minutes INTEGER,
15 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
16 );
17
18 CREATE INDEX idx_time_sessions_task ON time_sessions(task_id);
19 CREATE INDEX idx_time_sessions_user ON time_sessions(user_id);
20 CREATE INDEX idx_time_sessions_started ON time_sessions(started_at);
21 CREATE INDEX idx_time_sessions_active ON time_sessions(user_id) WHERE ended_at IS NULL;
22
23 -- Update sync triggers for tasks to include new columns.
24
25 DROP TRIGGER IF EXISTS sync_trg_tasks_insert;
26 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_insert
27 AFTER INSERT ON tasks
28 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
29 BEGIN
30 INSERT INTO sync_changelog (table_name, op, row_id, data)
31 VALUES ('tasks', 'INSERT', NEW.id, json_object(
32 'id', NEW.id,
33 'project_id', NEW.project_id,
34 'description', NEW.description,
35 'status', NEW.status,
36 'priority', NEW.priority,
37 'due', NEW.due,
38 'tags', NEW.tags,
39 'urgency', NEW.urgency,
40 'recurrence', NEW.recurrence,
41 'created_at', NEW.created_at,
42 'user_id', NEW.user_id,
43 'recurrence_parent_id', NEW.recurrence_parent_id,
44 'source_email_id', NEW.source_email_id,
45 'snoozed_until', NEW.snoozed_until,
46 'waiting_for_response', NEW.waiting_for_response,
47 'waiting_since', NEW.waiting_since,
48 'expected_response_date', NEW.expected_response_date,
49 'scheduled_start', NEW.scheduled_start,
50 'scheduled_duration', NEW.scheduled_duration,
51 'is_focus', NEW.is_focus,
52 'focus_set_at', NEW.focus_set_at,
53 'contact_id', NEW.contact_id,
54 'milestone_id', NEW.milestone_id,
55 'completed_at', NEW.completed_at,
56 'estimated_minutes', NEW.estimated_minutes,
57 'actual_minutes', NEW.actual_minutes
58 ));
59 END;
60
61 DROP TRIGGER IF EXISTS sync_trg_tasks_update;
62 CREATE TRIGGER IF NOT EXISTS sync_trg_tasks_update
63 AFTER UPDATE ON tasks
64 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
65 BEGIN
66 INSERT INTO sync_changelog (table_name, op, row_id, data)
67 VALUES ('tasks', 'UPDATE', NEW.id, json_object(
68 'id', NEW.id,
69 'project_id', NEW.project_id,
70 'description', NEW.description,
71 'status', NEW.status,
72 'priority', NEW.priority,
73 'due', NEW.due,
74 'tags', NEW.tags,
75 'urgency', NEW.urgency,
76 'recurrence', NEW.recurrence,
77 'created_at', NEW.created_at,
78 'user_id', NEW.user_id,
79 'recurrence_parent_id', NEW.recurrence_parent_id,
80 'source_email_id', NEW.source_email_id,
81 'snoozed_until', NEW.snoozed_until,
82 'waiting_for_response', NEW.waiting_for_response,
83 'waiting_since', NEW.waiting_since,
84 'expected_response_date', NEW.expected_response_date,
85 'scheduled_start', NEW.scheduled_start,
86 'scheduled_duration', NEW.scheduled_duration,
87 'is_focus', NEW.is_focus,
88 'focus_set_at', NEW.focus_set_at,
89 'contact_id', NEW.contact_id,
90 'milestone_id', NEW.milestone_id,
91 'completed_at', NEW.completed_at,
92 'estimated_minutes', NEW.estimated_minutes,
93 'actual_minutes', NEW.actual_minutes
94 ));
95 END;
96
97 -- Sync triggers for time_sessions table.
98
99 CREATE TRIGGER IF NOT EXISTS sync_trg_time_sessions_insert
100 AFTER INSERT ON time_sessions
101 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
102 BEGIN
103 INSERT INTO sync_changelog (table_name, op, row_id, data)
104 VALUES ('time_sessions', 'INSERT', NEW.id, json_object(
105 'id', NEW.id,
106 'task_id', NEW.task_id,
107 'user_id', NEW.user_id,
108 'started_at', NEW.started_at,
109 'ended_at', NEW.ended_at,
110 'duration_minutes', NEW.duration_minutes,
111 'created_at', NEW.created_at
112 ));
113 END;
114
115 CREATE TRIGGER IF NOT EXISTS sync_trg_time_sessions_update
116 AFTER UPDATE ON time_sessions
117 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
118 BEGIN
119 INSERT INTO sync_changelog (table_name, op, row_id, data)
120 VALUES ('time_sessions', 'UPDATE', NEW.id, json_object(
121 'id', NEW.id,
122 'task_id', NEW.task_id,
123 'user_id', NEW.user_id,
124 'started_at', NEW.started_at,
125 'ended_at', NEW.ended_at,
126 'duration_minutes', NEW.duration_minutes,
127 'created_at', NEW.created_at
128 ));
129 END;
130
131 CREATE TRIGGER IF NOT EXISTS sync_trg_time_sessions_delete
132 AFTER DELETE ON time_sessions
133 WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
134 BEGIN
135 INSERT INTO sync_changelog (table_name, op, row_id, data)
136 VALUES ('time_sessions', 'DELETE', OLD.id, NULL);
137 END;
138