# TaskManager

A task management module backed by SQLite via Sqler with priorities, due dates, tags, subtasks, recurrence, and 60+ query functions.

## Overview

`TaskManager` provides a full-featured task management system persisted in SQLite. Tasks support four statuses (`todo`, `in_progress`, `blocked`, `completed`), integer priorities, due dates, deferral windows (`after_at`), expiration (`expires_at`), JSON-encoded tags, parent-child relationships for subtask hierarchies, and recurring tasks via the `recur` column.

All user-facing time fields use **Unix seconds**. The `id` and `updated_at` fields are auto-generated by Sqler in **milliseconds**.

Query functions are organized into 11 categories: status, due dates, waiting/expiry, priority, completion, tags, subtasks, blocking, dashboard, analytics, and data integrity.

## Database Schema

Stored in `:tasks_db` (`data/tasks.sqlite`), table `tasks`:

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | INTEGER | auto (ms) | Primary key, millisecond timestamp |
| `updated_at` | INTEGER | auto (ms) | Last update timestamp (optimistic locking) |
| `title` | TEXT | — | Task title (NOT NULL) |
| `priority` | INTEGER | `0` | Higher = more important |
| `note` | TEXT | NULL | Freeform notes |
| `after_at` | INTEGER | NULL | Unix seconds — task hidden before this time |
| `expires_at` | INTEGER | NULL | Unix seconds — task hidden after this time |
| `completed_at` | INTEGER | NULL | Unix seconds — when the task was completed |
| `waiting_for` | TEXT | NULL | Reason the task is blocked |
| `status` | TEXT | `'todo'` | One of: `todo`, `in_progress`, `blocked`, `completed` |
| `due_at` | INTEGER | NULL | Unix seconds — when the task should be done |
| `tags` | TEXT | NULL | JSON array of strings, e.g. `["#work", "#home"]` |
| `parent_id` | INTEGER | NULL | ID of parent task (for subtask hierarchies) |
| `recur` | TEXT | NULL | Recurrence pattern (e.g. `"daily"`, `"every 3 days"`) |

### Indexes

- `idx_tasks_status` — status
- `idx_tasks_due_at` — due_at
- `idx_tasks_priority` — priority
- `idx_tasks_parent_id` — parent_id
- `idx_tasks_after_at` — after_at
- `idx_tasks_expires_at` — expires_at
- `idx_tasks_completed_at` — completed_at

## Configuration

The database is started in `Mcp.Application` as a supervised Sqler instance:

```elixir
# In children list:
{Sqler, name: "tasks", register: :tasks_db}

# Post-startup schema init:
TaskManager.setup_database(:tasks_db)
```

All public functions accept an optional `db` parameter (defaults to `:tasks_db`) for testing or multi-database scenarios.

## Public API

### setup_database/1

Initialize the tasks table and indexes. Idempotent — safe to call on every startup.

```elixir
TaskManager.setup_database()
TaskManager.setup_database(:my_custom_db)
```

### create/2

Create a new task. Defaults: `status: "todo"`, `priority: 0`. Tags can be passed as a list (auto-encoded to JSON).

```elixir
{:ok, id} = TaskManager.create(%{
  title: "Write docs",
  priority: 7,
  due_at: 1738900800,
  tags: ["#work", "#writing"]
})
```

**Accepted fields:** `title`, `priority`, `note`, `after_at`, `expires_at`, `waiting_for`, `status`, `due_at`, `tags`, `parent_id`, `recur`

**Returns:** `{:ok, id}` where `id` is the auto-generated millisecond timestamp.

### get/2

Fetch a single task by ID, returned as a map with string keys.

```elixir
{:ok, task} = TaskManager.get(id)
task["title"]  # => "Write docs"
task["tags"]   # => ["#work", "#writing"]  (auto-decoded from JSON)

{:error, :not_found} = TaskManager.get(999)
```

### update/3

Update fields on an existing task. Uses optimistic locking internally — fetches current `updated_at` before updating.

```elixir
{:ok, new_updated_at} = TaskManager.update(id, %{
  priority: 9,
  status: "in_progress"
})
```

**Accepted fields:** `title`, `priority`, `note`, `after_at`, `expires_at`, `waiting_for`, `status`, `due_at`, `tags`, `parent_id`, `completed_at`, `recur`

### complete/2

Convenience wrapper — sets `status: "completed"` and `completed_at` to the current Unix time.

```elixir
{:ok, _} = TaskManager.complete(id)
```

### delete/2

Delete a task by ID.

```elixir
{:ok, 1} = TaskManager.delete(id)
```

## Status Queries

| Function | Description |
|----------|-------------|
| `open_tasks/1` | All tasks with status `todo` or `in_progress` |
| `blocked_tasks/1` | All tasks with status `blocked` |
| `completed_tasks/1` | All tasks with status `completed` |
| `in_progress_tasks/1` | All tasks with status `in_progress` |
| `todo_without_due/1` | Status `todo` with no `due_at` set |

```elixir
TaskManager.open_tasks()
# => [%{"title" => "Write docs", "status" => "todo", ...}, ...]
```

## Due Date Queries

All due date queries filter for open tasks (`todo` or `in_progress`) unless noted.

| Function | Description | Sort |
|----------|-------------|------|
| `due_today/1` | Due between start and end of today (UTC) | priority DESC |
| `due_next_hour/1` | Due between now and now+1h | due_at ASC |
| `due_next_30_minutes/1` | Due between now and now+30min | due_at ASC |
| `due_this_week/1` | Due between today and today+7 days | due_at ASC |
| `due_tomorrow/1` | Due between tomorrow start and tomorrow end | priority DESC |
| `overdue/1` | `due_at` in the past, not completed (any non-completed status) | due_at ASC |
| `no_due_date/1` | Open tasks with NULL `due_at` | — |

```elixir
TaskManager.due_today()
TaskManager.overdue()
```

## Waiting / Expiry Queries

| Function | Description |
|----------|-------------|
| `currently_waiting/1` | `after_at` is in the future (task is deferred) |
| `wait_ended_today/1` | `after_at` passed between start of today and now |
| `expiring_today/1` | `expires_at` falls within today |
| `expired_never_completed/1` | `expires_at` in the past, `completed_at` is NULL |
| `becoming_active_24h/1` | `after_at` between now and now+24h |

```elixir
TaskManager.currently_waiting()
TaskManager.becoming_active_24h()
```

## Priority Queries

| Function | Description |
|----------|-------------|
| `open_by_priority/1` | Open tasks sorted by priority DESC |
| `due_next_hour_by_priority/1` | Open tasks due in the next hour, priority DESC |
| `highest_priority_open/1` | Single highest-priority open task. Returns `{:ok, task}` or `{:error, :not_found}` |
| `top_5_priority_this_week/1` | Top 5 open tasks due this week by priority |
| `overdue_by_priority/1` | Overdue tasks sorted by priority DESC |
| `blocked_by_priority/1` | Blocked tasks sorted by priority DESC |

```elixir
{:ok, task} = TaskManager.highest_priority_open()
task["title"]  # => "Urgent fix"
```

## Completion Queries

| Function | Description |
|----------|-------------|
| `completed_today/1` | `completed_at` falls within today |
| `completed_this_week/1` | `completed_at` within the last 7 days |
| `completed_last_30_days/1` | `completed_at` within the last 30 days |
| `completed_late/1` | `completed_at > due_at` (finished after deadline) |
| `completed_early/1` | `completed_at < due_at` (finished before deadline) |
| `completed_per_day_last_7/1` | Aggregate: `[[date_string, count], ...]` for last 7 days |

```elixir
TaskManager.completed_per_day_last_7()
# => [["2026-02-07", 3], ["2026-02-06", 5], ...]
```

## Tag Queries

Tags are stored as a JSON array (e.g. `["#work", "#home"]`). Tag queries use SQLite's `json_each()` for precise matching.

| Function | Signature | Description |
|----------|-----------|-------------|
| `open_tagged/2` | `open_tagged(tag, db)` | Open tasks containing the given tag |
| `open_tagged_due_today/2` | `open_tagged_due_today(tag, db)` | Open tasks with tag, due today |
| `all_tags/1` | `all_tags(db)` | Flat list of all distinct tag values |
| `tag_distribution/1` | `tag_distribution(db)` | `[[tag, count], ...]` across open tasks |
| `high_priority_tagged/3` | `high_priority_tagged(tag, min_priority \\ 5, db)` | Open tasks with tag and priority >= threshold |
| `blocked_tagged/2` | `blocked_tagged(tag, db)` | Blocked tasks with tag (includes `waiting_for`) |

```elixir
TaskManager.open_tagged("#work")
TaskManager.high_priority_tagged("#home", 3)
TaskManager.all_tags()
# => ["#home", "#quick_win", "#work"]
```

## Subtask Queries

Tasks form a hierarchy via `parent_id`. A task with `parent_id: nil` is a top-level task.

| Function | Signature | Description |
|----------|-----------|-------------|
| `top_level_tasks/1` | `top_level_tasks(db)` | Tasks with NULL `parent_id` |
| `subtasks_of/2` | `subtasks_of(parent_id, db)` | Children of a given parent |
| `parents_with_incomplete_subtasks/1` | — | Parents that have at least one non-completed child |
| `parents_all_subtasks_complete/1` | — | Incomplete parents whose children are all completed |
| `subtask_count_per_parent/1` | — | `[[parent_id, count], ...]` |
| `orphan_subtasks/1` | — | Subtasks whose `parent_id` references a non-existent task |

```elixir
{:ok, parent_id} = TaskManager.create(%{title: "Sprint 1"})
{:ok, _} = TaskManager.create(%{title: "Task A", parent_id: parent_id})
{:ok, _} = TaskManager.create(%{title: "Task B", parent_id: parent_id})

TaskManager.subtasks_of(parent_id)
# => [%{"title" => "Task A", ...}, %{"title" => "Task B", ...}]
```

## Blocking Queries

| Function | Description |
|----------|-------------|
| `blocked_with_reason/1` | All blocked tasks (includes `waiting_for` field) |
| `blocked_more_than_7_days/1` | Blocked tasks with `updated_at` older than 7 days (ms comparison) |
| `waiting_for_not_blocked/1` | Tasks with `waiting_for` set but status is NOT `blocked` (inconsistency) |
| `blocked_by_duration/1` | Blocked tasks sorted by `updated_at` ASC (longest-blocked first) |

```elixir
TaskManager.blocked_with_reason()
# => [%{"title" => "Deploy feature", "waiting_for" => "staging approval", ...}]
```

## Dashboard Queries

| Function | Description |
|----------|-------------|
| `todays_agenda/1` | Open tasks due today, not waiting, not expired, sorted by priority DESC |
| `next_up/1` | Open tasks with no due date, sorted by priority DESC, limit 10 |
| `becoming_actionable_today/1` | Tasks whose `after_at` falls within today |
| `stale_tasks/1` | `in_progress` tasks with `updated_at` older than 7 days |
| `updated_last_hour/1` | All tasks with `updated_at` within the last hour |
| `daily_summary/1` | Returns a map with keys: `completed_today`, `overdue`, `due_today`, `due_tomorrow`, `blocked` |

```elixir
summary = TaskManager.daily_summary()
# => %{
#   completed_today: [...],
#   overdue: [...],
#   due_today: [...],
#   due_tomorrow: [...],
#   blocked: [...]
# }
```

## Analytics Queries

| Function | Returns | Description |
|----------|---------|-------------|
| `count_by_status/1` | `[[status, count], ...]` | Open task count per status |
| `count_by_tag/1` | `[[tag, count], ...]` | Task count per tag (all tasks) |
| `avg_completion_time/1` | `{:ok, seconds}` | Average seconds from creation to completion |
| `count_overdue/1` | `integer` | Number of overdue tasks |
| `count_expiring_48h/1` | `integer` | Number of tasks expiring in the next 48 hours |
| `priority_distribution/1` | `[[priority, count], ...]` | Open task count per priority level |

```elixir
TaskManager.count_by_status()
# => [["todo", 12], ["in_progress", 3], ["blocked", 2]]

TaskManager.count_overdue()
# => 4
```

## Data Integrity Queries

These queries detect inconsistencies in the task data.

| Function | Description |
|----------|-------------|
| `completed_at_but_not_completed/1` | `completed_at` is set but status is not `completed` |
| `completed_but_no_completed_at/1` | Status is `completed` but `completed_at` is NULL |
| `expires_before_due/1` | `expires_at < due_at` (expires before it's due) |
| `past_waiting_still_blocked/1` | `after_at` is in the past but status is still `blocked` |
| `empty_title/1` | Tasks with NULL or empty string title |

```elixir
TaskManager.completed_but_no_completed_at()
# => [%{"title" => "Forgot timestamp", "status" => "completed", ...}]
```

## Return Format

- **Task query functions** return a list of maps with **string keys**: `[%{"id" => ..., "title" => ..., "tags" => [...], ...}]`
- **Tags** are auto-decoded from JSON to Elixir lists on read
- **Aggregate functions** (`count_by_status`, `tag_distribution`, `completed_per_day_last_7`, `subtask_count_per_parent`) return raw `[[value, count], ...]` lists
- **Scalar functions** (`count_overdue`, `count_expiring_48h`) return a single integer
- **`avg_completion_time`** returns `{:ok, float}` (seconds)
- **`highest_priority_open`** returns `{:ok, task_map}` or `{:error, :not_found}`
- **`daily_summary`** returns a map with atom keys pointing to lists of task maps

## Recurrence

Tasks can have a `recur` field with a pattern string (e.g. `"daily"`, `"weekly"`, `"every monday,wednesday"`). Recurrence is handled by `TaskRecurrence`.

### Recurrence Queries

| Function | Description |
|----------|-------------|
| `recurring_tasks/1` | Open tasks with `recur IS NOT NULL` |
| `recurring_due_today/2` | Recurring open tasks due today |
| `next_actionable/2` | Single highest-priority actionable task (not deferred, not expired) |

```elixir
TaskManager.recurring_tasks()
# => [%{"title" => "meditate", "recur" => "daily", ...}]

{:ok, task} = TaskManager.next_actionable(:tasks_db, "Asia/Ho_Chi_Minh")
task["title"]  # => "deploy v2.0"
```

### complete_recurring/3

Complete a task and auto-spawn the next recurring instance if `recur` is set.

```elixir
{:ok, result} = TaskManager.complete_recurring(task_id, "Asia/Ho_Chi_Minh")
# => {:ok, %{completed: %{...}, next: %{"recur" => "daily", "due_at" => <tomorrow>, ...}}}
```

The next instance inherits: `title`, `priority`, `tags`, `note`, `parent_id`, `recur`. It starts as `todo` with a shifted `due_at` calculated by `TaskRecurrence.next_due_at/3`.

If the task has no `recur` field, behaves identically to `complete/2`.

## Dependencies

| Module | Purpose |
|--------|---------|
| `Sqler` | SQLite database wrapper for persistence |
| `Jason` | JSON encoding/decoding for tags |
| `TaskRecurrence` | Recurrence pattern engine for `complete_recurring/3` |

## Related Documentation

- [TaskCapture](task_capture.md) — natural language parser for rapid task creation
- [TaskRecurrence](task_recurrence.md) — recurrence engine
- [TaskRest](task_rest.md) — REST adapter with capture, daily plan, snooze

---

*Source: `lib/task_manager.ex` — Last updated: 2026-02-21*