Implement linear task chains and recurring maintenance buckets

Backend, web timeline, and Android widget wiring for the last two
unimplemented items from doot-future-task-scheduling-ideas.

Chains: task_chains table + chain_id/chain_position/chain_unlocked on
native_tasks (migration 026), WIP-limit-1 advancement hooked into
CompleteNativeTask, locked tasks excluded from all date-based queries,
5 new /api/widget/chains* endpoints, an N/M position badge on web and
Android widget rows.

Buckets: maintenance_buckets table + bucket_id/bucket_state/
bucket_last_active_at on native_tasks (migration 027),
staleness-then-priority selection scoring, a new RunBucketCycleCheck
scheduler loop, 5 new endpoints including the distinct Defer action, a
Defer button on web and Android widget rows.

Also corrected stale "not yet approved" status headers on the two
already-shipped specs this work depended on (labels/projects, budgets/
availability) -- their headers were never updated after implementation.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01EZ7ikw2ukGJFTHE3bJS7zL

1 files changed, 142 insertions, 0 deletions

diff --git a/internal/store/chains.go b/internal/store/chains.go
new file mode 100644
index 0000000..4f51b3c
--- /dev/null
+++ b/internal/store/chains.go
@@ -0,0 +1,142 @@
+package store
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"task-dashboard/internal/config"
+	"task-dashboard/internal/models"
+)
+
+// defaultChainProjectColor is used for the project a chain auto-creates for
+// itself, since the chain-creation API takes no color -- unlike
+// HandleWidgetProjectsCreate, where the client always supplies one.
+const defaultChainProjectColor = "#8B5CF6"
+
+// CreateChain creates a backing project (per the design's "a chain is
+// effectively a project with strict sequential unlocking"), a task_chains
+// row, and one native_tasks row per title in taskTitles. Position 0 is
+// created unlocked with due_date = now; the rest are locked with no due
+// date. All in one transaction so a partial chain never exists.
+func (s *Store) CreateChain(name string, taskTitles []string) (*models.Chain, error) {
+	if len(taskTitles) == 0 {
+		return nil, fmt.Errorf("chain must have at least one task")
+	}
+
+	project, err := s.CreateProject(name, defaultChainProjectColor)
+	if err != nil {
+		return nil, err
+	}
+
+	chainID := newTaskID()
+	now := config.Now()
+
+	tx, err := s.db.Begin()
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = tx.Rollback() }()
+
+	if _, err := tx.Exec(`
+		INSERT INTO task_chains (id, project_id, status, created_at) VALUES (?, ?, 'active', ?)
+	`, chainID, project.ID, now); err != nil {
+		return nil, err
+	}
+
+	for i, title := range taskTitles {
+		unlocked := i == 0
+		var dueDate *time.Time
+		if unlocked {
+			dueDate = &now
+		}
+		if _, err := tx.Exec(`
+			INSERT INTO native_tasks (id, content, project_name, project_id, priority, due_date, chain_id, chain_position, chain_unlocked, created_at, updated_at)
+			VALUES (?, ?, ?, ?, 1, ?, ?, ?, ?, ?, ?)
+		`, newTaskID(), title, project.Name, project.ID, dueDate, chainID, i, unlocked, now, now); err != nil {
+			return nil, err
+		}
+	}
+
+	if err := tx.Commit(); err != nil {
+		return nil, err
+	}
+
+	return &models.Chain{ID: chainID, ProjectID: project.ID, Status: "active", CreatedAt: now}, nil
+}
+
+// GetChain returns a single chain by id, or ErrNativeTaskNotFound.
+func (s *Store) GetChain(id string) (*models.Chain, error) {
+	var c models.Chain
+	err := s.db.QueryRow(`
+		SELECT id, project_id, status, created_at FROM task_chains WHERE id = ?
+	`, id).Scan(&c.ID, &c.ProjectID, &c.Status, &c.CreatedAt)
+	if err == sql.ErrNoRows {
+		return nil, ErrNativeTaskNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &c, nil
+}
+
+// GetChainTasks returns every task in the chain, locked and unlocked both,
+// ordered by chain_position -- the full checklist view.
+func (s *Store) GetChainTasks(chainID string) ([]models.Task, error) {
+	rows, err := s.db.Query(`
+		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, estimated_minutes,
+		       chain_id, chain_position, chain_unlocked,
+		       bucket_id, bucket_state, bucket_last_active_at
+		FROM native_tasks
+		WHERE chain_id = ?
+		ORDER BY chain_position ASC
+	`, chainID)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = rows.Close() }()
+	return scanNativeTasks(rows)
+}
+
+// SetChainStatus updates a chain's status (active/paused/abandoned/completed).
+// Returns ErrNativeTaskNotFound if id doesn't match any row.
+func (s *Store) SetChainStatus(id, status string) error {
+	result, err := s.db.Exec(`UPDATE task_chains SET status = ? WHERE id = ?`, status, id)
+	if err != nil {
+		return err
+	}
+	return checkRowsAffected(result)
+}
+
+// advanceChain is called from CompleteNativeTask when the just-completed
+// task has a chain_id set. Per the design, a paused chain does not
+// auto-advance -- it must be explicitly resumed first. Completing the last
+// position marks the chain completed instead of advancing.
+func (s *Store) advanceChain(chainID string, completedPosition int) error {
+	chain, err := s.GetChain(chainID)
+	if err != nil {
+		return err
+	}
+	if chain.Status == "paused" {
+		return nil
+	}
+
+	now := config.Now()
+	result, err := s.db.Exec(`
+		UPDATE native_tasks SET chain_unlocked = 1, due_date = ?, updated_at = ?
+		WHERE chain_id = ? AND chain_position = ?
+	`, now, now, chainID, completedPosition+1)
+	if err != nil {
+		return err
+	}
+	affected, err := result.RowsAffected()
+	if err != nil {
+		return err
+	}
+	if affected == 0 {
+		// No next position -- the completed task was the last in the chain.
+		return s.SetChainStatus(chainID, "completed")
+	}
+	return nil
+}