From 54e2f162c03ff7c732751ef35c266093c842c7bc Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Wed, 15 Jul 2026 18:06:03 +0000 Subject: feat(tasks): add projects/labels schema, wire project_id through native tasks project_id joins the existing labels JSON column as series-level metadata: CreateNextIteration copies both onto every new recurring occurrence, matching content/description/priority's existing behavior. --- internal/models/types.go | 22 ++++++++--- internal/store/native_tasks.go | 26 ++++++------- internal/store/native_tasks_test.go | 70 ++++++++++++++++++++++++++++++++++ migrations/024_labels_and_projects.sql | 20 ++++++++++ 4 files changed, 120 insertions(+), 18 deletions(-) create mode 100644 migrations/024_labels_and_projects.sql diff --git a/internal/models/types.go b/internal/models/types.go index fdde455..4a923a8 100644 --- a/internal/models/types.go +++ b/internal/models/types.go @@ -29,6 +29,23 @@ type Task struct { NextOccurrenceOverride *time.Time `json:"next_occurrence_override,omitempty"` } +// Project is a lightweight grouping for doot-native tasks. Exactly one +// project per task (Task.ProjectID); labels handle cross-cutting tagging. +type Project struct { + ID string `json:"id"` + Name string `json:"name"` + Color string `json:"color"` + CreatedAt time.Time `json:"created_at"` + Archived bool `json:"archived"` +} + +// LabelColor maps a label's free-text name to its display color. A label +// with no matching row here just has no color assigned yet. +type LabelColor struct { + Name string `json:"name"` + Color string `json:"color"` +} + // Meal represents a meal from PlanToEat type Meal struct { ID string `json:"id"` @@ -126,11 +143,6 @@ type Card struct { URL string `json:"url"` } -// Project represents a project (used for source config grouping) -type Project struct { - ID string `json:"id"` - Name string `json:"name"` -} // CalendarEvent represents a Google Calendar event type CalendarEvent struct { diff --git a/internal/store/native_tasks.go b/internal/store/native_tasks.go index 90cf275..1f898aa 100644 --- a/internal/store/native_tasks.go +++ b/internal/store/native_tasks.go @@ -23,7 +23,7 @@ var ErrNativeTaskNotFound = errors.New("native task not found") // GetNativeTasks returns all non-completed native tasks. func (s *Store) GetNativeTasks() ([]models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks WHERE completed = 0 @@ -42,7 +42,7 @@ func (s *Store) GetNativeTasks() ([]models.Task, error) { // without double-counting against that separate fetch. func (s *Store) GetNativeTasksByDateRange(start, end time.Time) ([]models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks WHERE completed = 0 AND due_date IS NOT NULL AND due_date >= ? AND due_date < ? @@ -63,7 +63,7 @@ func (s *Store) GetNativeTasksByDateRange(start, end time.Time) ([]models.Task, // ComputeDaySection to be marked IsOverdue. func (s *Store) GetOverdueNativeTasks(before time.Time) ([]models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks WHERE completed = 0 AND due_date IS NOT NULL AND due_date < ? @@ -79,7 +79,7 @@ func (s *Store) GetOverdueNativeTasks(before time.Time) ([]models.Task, error) { // GetUndatedNativeTasks returns non-completed native tasks with no due date. func (s *Store) GetUndatedNativeTasks() ([]models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks WHERE completed = 0 AND due_date IS NULL @@ -95,7 +95,7 @@ func (s *Store) GetUndatedNativeTasks() ([]models.Task, error) { // GetNativeTaskByID returns a single native task by id, or ErrNativeTaskNotFound. func (s *Store) GetNativeTaskByID(id string) (*models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks WHERE id = ? @@ -118,9 +118,9 @@ func (s *Store) GetNativeTaskByID(id string) (*models.Task, error) { func (s *Store) CreateNativeTask(task models.Task) error { labelsJSON, _ := json.Marshal(task.Labels) _, err := s.db.Exec(` - INSERT INTO native_tasks (id, content, description, project_name, due_date, priority, labels, created_at, updated_at) - VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) - `, task.ID, task.Content, task.Description, task.ProjectName, task.DueDate, task.Priority, string(labelsJSON)) + INSERT INTO native_tasks (id, content, description, project_name, project_id, due_date, priority, labels, created_at, updated_at) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) + `, task.ID, task.Content, task.Description, task.ProjectName, task.ProjectID, task.DueDate, task.Priority, string(labelsJSON)) return err } @@ -245,17 +245,17 @@ func (s *Store) CreateNextIteration(old models.Task) error { labelsJSON, _ := json.Marshal(old.Labels) _, err := s.db.Exec(` INSERT INTO native_tasks ( - id, content, description, project_name, due_date, priority, labels, + id, content, description, project_name, project_id, due_date, priority, labels, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override, created_at, updated_at ) - SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP WHERE NOT EXISTS ( SELECT 1 FROM native_tasks WHERE recurrence_series_id = ? AND (due_date > ? OR (due_date = ? AND created_at > ?)) ) - `, newTaskID(), old.Content, old.Description, old.ProjectName, nextDue, old.Priority, string(labelsJSON), + `, newTaskID(), old.Content, old.Description, old.ProjectName, old.ProjectID, nextDue, old.Priority, string(labelsJSON), old.RecurrenceFreq, old.RecurrenceInterval, formatWeekdays(old.RecurrenceWeekdays), old.RecurrenceSeriesID, old.RecurrenceSeriesID, old.DueDate, old.DueDate, old.CreatedAt) return err @@ -321,7 +321,7 @@ func (s *Store) SetNextOccurrenceOverride(id string, date time.Time) error { // ignored recurring task doesn't block its successor from appearing. func (s *Store) GetSeriesNeedingNextIteration(now time.Time) ([]models.Task, error) { rows, err := s.db.Query(` - SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at, + SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at, recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override FROM native_tasks t1 WHERE recurrence_series_id != '' @@ -384,7 +384,7 @@ func scanNativeTasks(rows interface { var weekdaysStr string var nextOverrideStr string if err := rows.Scan( - &t.ID, &t.Content, &t.Description, &t.ProjectName, &dueDateStr, &t.Priority, &t.Completed, &labelsJSON, &t.CreatedAt, + &t.ID, &t.Content, &t.Description, &t.ProjectName, &t.ProjectID, &dueDateStr, &t.Priority, &t.Completed, &labelsJSON, &t.CreatedAt, &t.RecurrenceFreq, &t.RecurrenceInterval, &weekdaysStr, &t.RecurrenceSeriesID, &nextOverrideStr, ); err != nil { return nil, err diff --git a/internal/store/native_tasks_test.go b/internal/store/native_tasks_test.go index c362eb7..6e93bca 100644 --- a/internal/store/native_tasks_test.go +++ b/internal/store/native_tasks_test.go @@ -30,6 +30,7 @@ func newNativeTasksTestStore(t *testing.T) *Store { content TEXT NOT NULL, description TEXT DEFAULT '', project_name TEXT DEFAULT '', + project_id TEXT DEFAULT '', due_date DATETIME, priority INTEGER DEFAULT 1, completed BOOLEAN DEFAULT 0, @@ -490,3 +491,72 @@ func TestAdvanceDueRecurringTasks_NonRecurringTask_NeverTouched(t *testing.T) { t.Errorf("expected 0 iterations created (no recurring tasks in fixture), got %d", n) } } + +func TestGetNativeTasks_ParsesProjectID(t *testing.T) { + s := newNativeTasksTestStore(t) + if _, err := s.db.Exec(`INSERT INTO native_tasks (id, content, project_id) VALUES ('proj-1', 'Task with project', 'project-abc')`); err != nil { + t.Fatal(err) + } + + tasks, err := s.GetNativeTasks() + if err != nil { + t.Fatalf("GetNativeTasks: %v", err) + } + var found *models.Task + for i := range tasks { + if tasks[i].ID == "proj-1" { + found = &tasks[i] + } + } + if found == nil { + t.Fatal("expected to find proj-1") + } + if found.ProjectID != "project-abc" { + t.Errorf("ProjectID = %q, want %q", found.ProjectID, "project-abc") + } +} + +func TestCreateNativeTask_PersistsProjectID(t *testing.T) { + s := newNativeTasksTestStore(t) + task := models.Task{ID: "new-1", Content: "New task", ProjectID: "project-xyz"} + if err := s.CreateNativeTask(task); err != nil { + t.Fatalf("CreateNativeTask: %v", err) + } + + got, err := s.GetNativeTaskByID("new-1") + if err != nil { + t.Fatal(err) + } + if got.ProjectID != "project-xyz" { + t.Errorf("ProjectID = %q, want %q", got.ProjectID, "project-xyz") + } +} + +func TestCreateNextIteration_InheritsProjectIDAndLabels(t *testing.T) { + s := newNativeTasksTestStore(t) + if _, err := s.db.Exec(` + INSERT INTO native_tasks (id, content, due_date, recurrence_freq, recurrence_interval, recurrence_series_id, project_id, labels) + VALUES ('rec-1', 'Water plants', '2026-07-13', 'weekly', 1, 'series-1', 'project-abc', '["chore","garden"]') + `); err != nil { + t.Fatal(err) + } + + if err := s.CompleteNativeTask("rec-1"); err != nil { + t.Fatalf("CompleteNativeTask: %v", err) + } + + var nextID string + if err := s.db.QueryRow(`SELECT id FROM native_tasks WHERE recurrence_series_id = 'series-1' AND id != 'rec-1'`).Scan(&nextID); err != nil { + t.Fatal(err) + } + next, err := s.GetNativeTaskByID(nextID) + if err != nil { + t.Fatal(err) + } + if next.ProjectID != "project-abc" { + t.Errorf("next iteration ProjectID = %q, want %q", next.ProjectID, "project-abc") + } + if len(next.Labels) != 2 || next.Labels[0] != "chore" || next.Labels[1] != "garden" { + t.Errorf("next iteration Labels = %v, want [chore garden]", next.Labels) + } +} diff --git a/migrations/024_labels_and_projects.sql b/migrations/024_labels_and_projects.sql new file mode 100644 index 0000000..9e5235c --- /dev/null +++ b/migrations/024_labels_and_projects.sql @@ -0,0 +1,20 @@ +-- Lightweight projects (exactly one per task) and label colors for +-- doot-native tasks. Labels themselves already exist as a JSON column on +-- native_tasks; this only adds color metadata for them, plus the new +-- projects concept. +CREATE TABLE projects ( + id TEXT PRIMARY KEY, + name TEXT NOT NULL, + color TEXT NOT NULL, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + archived BOOLEAN DEFAULT 0 +); + +CREATE TABLE labels ( + name TEXT PRIMARY KEY, + color TEXT NOT NULL +); + +ALTER TABLE native_tasks ADD COLUMN project_id TEXT DEFAULT ''; + +CREATE INDEX IF NOT EXISTS idx_native_tasks_project ON native_tasks(project_id); -- cgit v1.2.3