Class: TrakFlow::Storage::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/trak_flow/storage/database.rb

Overview

SQLite database layer for fast local queries This is the gitignored working copy that provides millisecond response times

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_path = nil) ⇒ Database

Returns a new instance of Database.



10
11
12
13
14
 
# File 'lib/trak_flow/storage/database.rb', line 10

def initialize(db_path = nil)
  @db_path = db_path || TrakFlow.database_path
  @db = nil
  @dirty = false
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



8
9
10
 
# File 'lib/trak_flow/storage/database.rb', line 8

def db
  @db
end

Instance Method Details

#add_comment(comment) ⇒ Object

Comment operations



220
221
222
223
224
225
 
# File 'lib/trak_flow/storage/database.rb', line 220

def add_comment(comment)
  comment.validate!
  @db[:comments].insert(comment_to_row(comment))
  mark_dirty!
  comment
end

#add_dependency(dependency) ⇒ Object

Dependency operations



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/trak_flow/storage/database.rb', line 112

def add_dependency(dependency)
  dependency.validate!
  detect_cycle!(dependency)

  # Check if dependency already exists (idempotent operation)
  existing = @db[:dependencies].where(
    source_id: dependency.source_id,
    target_id: dependency.target_id,
    type: dependency.type
  ).first

  return dependency if existing

  @db[:dependencies].insert(dependency_to_row(dependency))
  rebuild_blocked_cache!
  mark_dirty!
  dependency
end

#add_label(label) ⇒ Object

Label operations



168
169
170
171
172
173
174
175
176
177
 
# File 'lib/trak_flow/storage/database.rb', line 168

def add_label(label)
  label.validate!

  existing = @db[:labels].where(task_id: label.task_id, name: label.name).first
  return Models::Label.from_hash(existing) if existing

  @db[:labels].insert(label_to_row(label))
  mark_dirty!
  label
end

#all_labels ⇒ Object 



191
192
193
 
# File 'lib/trak_flow/storage/database.rb', line 191

def all_labels
  @db[:labels].distinct.select_map(:name).sort
end

#all_task_ids ⇒ Object 



106
107
108
 
# File 'lib/trak_flow/storage/database.rb', line 106

def all_task_ids
  @db[:tasks].select_map(:id)
end

#blocked_tasks ⇒ Object 



247
248
249
250
251
252
253
254
255
 
# File 'lib/trak_flow/storage/database.rb', line 247

def blocked_tasks
  blocked_ids = @db[:blocked_tasks].select_map(:task_id)

  @db[:tasks]
    .where(id: blocked_ids)
    .where(ephemeral: false)
    .where(plan: false)
    .map { |row| Models::Task.from_hash(row) }
end

#blocking_dependencies(issue_id) ⇒ Object 



159
160
161
162
163
164
 
# File 'lib/trak_flow/storage/database.rb', line 159

def blocking_dependencies(issue_id)
  @db[:dependencies]
    .where(target_id: issue_id)
    .where(type: Models::Dependency::BLOCKING_TYPES)
    .map { |row| Models::Dependency.from_hash(row) }
end

#child_tasks(parent_id) ⇒ Object

Child tasks (for epics)



268
269
270
271
272
 
# File 'lib/trak_flow/storage/database.rb', line 268

def child_tasks(parent_id)
  @db[:tasks].where(parent_id: parent_id).map do |row|
    Models::Task.from_hash(row)
  end
end

#clear! ⇒ Object 



364
365
366
367
368
369
370
371
 
# File 'lib/trak_flow/storage/database.rb', line 364

def clear!
  @db[:tasks].delete
  @db[:dependencies].delete
  @db[:labels].delete
  @db[:comments].delete
  @db[:blocked_tasks].delete
  mark_dirty!
end

#close ⇒ Object 



22
23
24
25
 
# File 'lib/trak_flow/storage/database.rb', line 22

def close
  @db&.disconnect
  @db = nil
end

#connect ⇒ Object 



16
17
18
19
20
 
# File 'lib/trak_flow/storage/database.rb', line 16

def connect
  @db = Sequel.sqlite(@db_path)
  setup_schema
  self
end

#connected? ⇒ Boolean

Returns:

  • (Boolean) 


27
28
29
 
# File 'lib/trak_flow/storage/database.rb', line 27

def connected?
  !@db.nil?
end

#create_child_task(parent_id, attrs) ⇒ Object 



274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/trak_flow/storage/database.rb', line 274

def create_child_task(parent_id, attrs)
  parent = find_task!(parent_id)
  child_count = @db[:tasks].where(parent_id: parent_id).count
  child_id = IdGenerator.generate_child_id(parent_id, child_count + 1)

  task = Models::Task.new(attrs.merge(id: child_id, parent_id: parent_id))
  create_task(task)

  dep = Models::Dependency.new(
    source_id: parent_id,
    target_id: child_id,
    type: "parent-child"
  )
  add_dependency(dep)

  task
end

#create_task(task) ⇒ Object

Task operations



45
46
47
48
49
50
51
52
53
54
 
# File 'lib/trak_flow/storage/database.rb', line 45

def create_task(task)
  task.validate!
  existing_ids = @db[:tasks].select_map(:id)
  task.id ||= IdGenerator.generate(existing_ids: existing_ids)
  task.update_content_hash!

  @db[:tasks].insert(task_to_row(task))
  mark_dirty!
  task
end

#delete_task(id) ⇒ Object 



79
80
81
82
83
84
85
 
# File 'lib/trak_flow/storage/database.rb', line 79

def delete_task(id)
  @db[:tasks].where(id: id).delete
  @db[:labels].where(task_id: id).delete
  @db[:dependencies].where(source_id: id).or(target_id: id).delete
  @db[:comments].where(task_id: id).delete
  mark_dirty!
end

#dirty? ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
 
# File 'lib/trak_flow/storage/database.rb', line 31

def dirty?
  @dirty
end

#find_comments(task_id) ⇒ Object 



227
228
229
230
231
 
# File 'lib/trak_flow/storage/database.rb', line 227

def find_comments(task_id)
  @db[:comments].where(task_id: task_id).order(:created_at).map do |row|
    Models::Comment.from_hash(row)
  end
end

#find_dependencies(issue_id, direction: :both) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/trak_flow/storage/database.rb', line 141

def find_dependencies(issue_id, direction: :both)
  deps = []

  if direction == :both || direction == :outgoing
    @db[:dependencies].where(source_id: issue_id).each do |row|
      deps << Models::Dependency.from_hash(row)
    end
  end

  if direction == :both || direction == :incoming
    @db[:dependencies].where(target_id: issue_id).each do |row|
      deps << Models::Dependency.from_hash(row)
    end
  end

  deps
end

#find_ephemeral_workflows ⇒ Object

Ephemeral task operations



333
334
335
336
337
 
# File 'lib/trak_flow/storage/database.rb', line 333

def find_ephemeral_workflows
  @db[:tasks].where(ephemeral: true).map do |row|
    Models::Task.from_hash(row)
  end
end

#find_labels(task_id) ⇒ Object 



185
186
187
188
189
 
# File 'lib/trak_flow/storage/database.rb', line 185

def find_labels(task_id)
  @db[:labels].where(task_id: task_id).map do |row|
    Models::Label.from_hash(row)
  end
end

#find_plan_tasks(plan_id) ⇒ Object 



300
301
302
303
304
 
# File 'lib/trak_flow/storage/database.rb', line 300

def find_plan_tasks(plan_id)
  @db[:tasks].where(parent_id: plan_id).order(Sequel.asc(:priority), :title).map do |row|
    Models::Task.from_hash(row)
  end
end

#find_plans ⇒ Object

Plan operations



294
295
296
297
298
 
# File 'lib/trak_flow/storage/database.rb', line 294

def find_plans
  @db[:tasks].where(plan: true).order(:title).map do |row|
    Models::Task.from_hash(row)
  end
end

#find_task(id) ⇒ Object 



56
57
58
59
60
61
 
# File 'lib/trak_flow/storage/database.rb', line 56

def find_task(id)
  row = @db[:tasks].where(id: id).first
  return nil unless row

  Models::Task.from_hash(row)
end

#find_task!(id) ⇒ Object

Raises:



63
64
65
66
67
68
 
# File 'lib/trak_flow/storage/database.rb', line 63

def find_task!(id)
  task = find_task(id)
  raise TaskNotFoundError, "Task not found: #{id}" unless task

  task
end

#find_workflow_tasks(workflow_id) ⇒ Object 



325
326
327
328
329
 
# File 'lib/trak_flow/storage/database.rb', line 325

def find_workflow_tasks(workflow_id)
  @db[:tasks].where(parent_id: workflow_id).order(Sequel.asc(:priority), :title).map do |row|
    Models::Task.from_hash(row)
  end
end

#find_workflows(plan_id: nil) ⇒ Object

Workflow operations



316
317
318
319
320
321
322
323
 
# File 'lib/trak_flow/storage/database.rb', line 316

def find_workflows(plan_id: nil)
  dataset = @db[:tasks].where(plan: false)
  dataset = dataset.exclude(source_plan_id: nil).exclude(source_plan_id: "")
  dataset = dataset.where(source_plan_id: plan_id) if plan_id
  dataset.order(Sequel.desc(:created_at)).map do |row|
    Models::Task.from_hash(row)
  end
end

#garbage_collect_ephemeral(max_age_hours: 24) ⇒ Object 



339
340
341
342
343
344
345
 
# File 'lib/trak_flow/storage/database.rb', line 339

def garbage_collect_ephemeral(max_age_hours: 24)
  cutoff = Time.now.utc - (max_age_hours * 60 * 60)
  old_ephemeral = @db[:tasks].where(ephemeral: true).where { created_at < cutoff }.select_map(:id)

  old_ephemeral.each { |id| delete_task(id) }
  old_ephemeral.size
end

#get_state(task_id, dimension) ⇒ Object 



211
212
213
214
215
216
 
# File 'lib/trak_flow/storage/database.rb', line 211

def get_state(task_id, dimension)
  label = @db[:labels].where(task_id: task_id).where(Sequel.like(:name, "#{dimension}:%")).first
  return nil unless label

  label[:name].split(":", 2).last
end

#import_tasks(tasks) ⇒ Object

Bulk operations



349
350
351
352
353
354
355
356
357
358
359
360
361
362
 
# File 'lib/trak_flow/storage/database.rb', line 349

def import_tasks(tasks)
  @db.transaction do
    tasks.each do |task|
      existing = find_task(task.id)
      if existing
        update_task(task) if task.content_hash != existing.content_hash
      else
        @db[:tasks].insert(task_to_row(task))
      end
    end
  end
  rebuild_blocked_cache!
  mark_dirty!
end

#list_tasks(filters = {}) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/trak_flow/storage/database.rb', line 87

def list_tasks(filters = {})
  dataset = @db[:tasks]

  dataset = apply_status_filter(dataset, filters[:status])
  dataset = apply_priority_filter(dataset, filters)
  dataset = apply_type_filter(dataset, filters[:type])
  dataset = apply_assignee_filter(dataset, filters[:assignee])
  dataset = apply_text_filters(dataset, filters)
  dataset = apply_date_filters(dataset, filters)
  dataset = apply_null_filters(dataset, filters)
  dataset = dataset.where(ephemeral: false) unless filters[:include_ephemeral]
  dataset = dataset.where(plan: false) unless filters[:include_plans]
  dataset = dataset.exclude(status: "tombstone") unless filters[:include_tombstones]

  dataset.order(Sequel.asc(:priority), Sequel.desc(:updated_at)).map do |row|
    Models::Task.from_hash(row)
  end
end

#mark_as_plan(task_id) ⇒ Object 



306
307
308
309
310
311
312
 
# File 'lib/trak_flow/storage/database.rb', line 306

def mark_as_plan(task_id)
  task = find_task!(task_id)
  task.plan = true
  task.status = "open"
  task.ephemeral = false
  update_task(task)
end

#mark_clean! ⇒ Object 



39
40
41
 
# File 'lib/trak_flow/storage/database.rb', line 39

def mark_clean!
  @dirty = false
end

#mark_dirty! ⇒ Object 



35
36
37
 
# File 'lib/trak_flow/storage/database.rb', line 35

def mark_dirty!
  @dirty = true
end

#ready_tasks ⇒ Object

Ready work detection



235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/trak_flow/storage/database.rb', line 235

def ready_tasks
  blocked_ids = @db[:blocked_tasks].select_map(:task_id)

  @db[:tasks]
    .where(status: "open")
    .where(ephemeral: false)
    .where(plan: false)
    .exclude(id: blocked_ids)
    .order(Sequel.asc(:priority), Sequel.desc(:updated_at))
    .map { |row| Models::Task.from_hash(row) }
end

#remove_dependency(source_id, target_id, type: nil) ⇒ Object 



131
132
133
134
135
136
137
138
139
 
# File 'lib/trak_flow/storage/database.rb', line 131

def remove_dependency(source_id, target_id, type: nil)
  dataset = @db[:dependencies].where(source_id: source_id, target_id: target_id)
  dataset = dataset.where(type: type) if type

  deleted = dataset.delete
  rebuild_blocked_cache! if deleted.positive?
  mark_dirty! if deleted.positive?
  deleted
end

#remove_label(task_id, name) ⇒ Object 



179
180
181
182
183
 
# File 'lib/trak_flow/storage/database.rb', line 179

def remove_label(task_id, name)
  deleted = @db[:labels].where(task_id: task_id, name: name).delete
  mark_dirty! if deleted.positive?
  deleted
end

#set_state(task_id, dimension, value, reason: nil) ⇒ Object 



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
# File 'lib/trak_flow/storage/database.rb', line 195

def set_state(task_id, dimension, value, reason: nil)
  prefix = "#{dimension}:"
  @db[:labels].where(task_id: task_id).where(Sequel.like(:name, "#{prefix}%")).delete

  label = Models::Label.new(task_id: task_id, name: "#{dimension}:#{value}")
  add_label(label)

  if reason
    task = find_task!(task_id)
    task.notes = "#{task.notes}\n[State] #{dimension}=#{value}: #{reason}".strip
    update_task(task)
  end

  label
end

#stale_tasks(days: 30, status: nil) ⇒ Object

Stale tasks



259
260
261
262
263
264
 
# File 'lib/trak_flow/storage/database.rb', line 259

def stale_tasks(days: 30, status: nil)
  cutoff = Time.now.utc - (days * 24 * 60 * 60)
  dataset = @db[:tasks].where(ephemeral: false).where(plan: false).where { updated_at < cutoff }
  dataset = dataset.where(status: status) if status
  dataset.order(:updated_at).map { |row| Models::Task.from_hash(row) }
end

#update_task(task) ⇒ Object 



70
71
72
73
74
75
76
77
 
# File 'lib/trak_flow/storage/database.rb', line 70

def update_task(task)
  task.validate!
  task.touch!

  @db[:tasks].where(id: task.id).update(task_to_row(task))
  mark_dirty!
  task
end