# Package: `internal/storage`

## Overview

The `storage` package provides the SQLite persistence layer for Claudomator. It exposes a `DB` struct that manages all reads and writes to the database, runs schema migrations automatically on `Open`, and enforces valid state transitions when updating task states.

Key characteristics:
- **Auto-migration**: `Open` creates all tables and indexes if they do not exist, then applies additive column migrations idempotently (duplicate-column errors are silently ignored).
- **WAL mode**: The database is opened with `?_journal_mode=WAL&_busy_timeout=5000` for concurrent read safety and reduced lock contention.
- **State enforcement**: `UpdateTaskState` performs the state change inside a transaction and calls `task.ValidTransition` before committing.
- **Cascading deletes**: `DeleteTask` removes a task, all its descendant subtasks, and all their execution records in a single transaction using a recursive CTE.

---

## Schema

### `tasks` table

| Column           | Type      | Description                                              |
|------------------|-----------|----------------------------------------------------------|
| `id`             | `TEXT PK` | UUID; primary key.                                       |
| `name`           | `TEXT`    | Human-readable task name. Not null.                      |
| `description`    | `TEXT`    | Optional longer description.                             |
| `config_json`    | `TEXT`    | JSON blob — serialised `AgentConfig` struct.             |
| `priority`       | `TEXT`    | `"high"`, `"normal"`, or `"low"`. Default `"normal"`.   |
| `timeout_ns`     | `INTEGER` | Timeout as nanoseconds (`time.Duration`). Default `0`.   |
| `retry_json`     | `TEXT`    | JSON blob — serialised `RetryConfig` struct.             |
| `tags_json`      | `TEXT`    | JSON blob — serialised `[]string`.                       |
| `depends_on_json`| `TEXT`    | JSON blob — serialised `[]string` of task IDs.          |
| `parent_task_id` | `TEXT`    | ID of parent task; null for root tasks.                  |
| `state`          | `TEXT`    | Current `State` value. Default `"PENDING"`.              |
| `created_at`     | `DATETIME`| Creation timestamp (UTC).                                |
| `updated_at`     | `DATETIME`| Last-update timestamp (UTC).                             |
| `rejection_comment` | `TEXT` | Reviewer comment set by `RejectTask`; nullable.         |
| `question_json`  | `TEXT`    | Pending agent question set by `UpdateTaskQuestion`; nullable. |

**JSON blob columns**: `config_json`, `retry_json`, `tags_json`, `depends_on_json`.

**Indexes**: `idx_tasks_state` (state), `idx_tasks_parent_task_id` (parent_task_id).

### `executions` table

| Column        | Type      | Description                                                   |
|---------------|-----------|---------------------------------------------------------------|
| `id`          | `TEXT PK` | UUID; primary key.                                            |
| `task_id`     | `TEXT`    | Foreign key → `tasks.id`.                                     |
| `start_time`  | `DATETIME`| When the execution started (UTC).                             |
| `end_time`    | `DATETIME`| When the execution ended (UTC); null while running.           |
| `exit_code`   | `INTEGER` | Process exit code; 0 = success.                               |
| `status`      | `TEXT`    | Execution status string (mirrors task state at completion).   |
| `stdout_path` | `TEXT`    | Path to the stdout log file under `~/.claudomator/executions/`. |
| `stderr_path` | `TEXT`    | Path to the stderr log file under `~/.claudomator/executions/`. |
| `artifact_dir`| `TEXT`    | Directory containing output artifacts.                        |
| `cost_usd`    | `REAL`    | Total API spend for this execution in USD.                    |
| `error_msg`   | `TEXT`    | Error description if the execution failed.                    |
| `session_id`  | `TEXT`    | Claude `--session-id` value; used to resume interrupted runs. |

**Indexes**: `idx_executions_status`, `idx_executions_task_id`, `idx_executions_start_time`.

---

## `DB` Struct Methods

### Lifecycle

#### `Open(path string) (*DB, error)`
Opens the SQLite database at `path`, runs auto-migration, and returns a `*DB`. The connection string appends `?_journal_mode=WAL&_busy_timeout=5000`.

#### `(*DB) Close() error`
Closes the underlying database connection.

---

### Task CRUD

#### `(*DB) CreateTask(t *task.Task) error`
Inserts a new task. `AgentConfig`, `RetryConfig`, `Tags`, and `DependsOn` are serialised as JSON blobs; `Timeout` is stored as nanoseconds.

#### `(*DB) GetTask(id string) (*task.Task, error)`
Retrieves a single task by ID. Returns an error wrapping `sql.ErrNoRows` if not found.

#### `(*DB) ListTasks(filter TaskFilter) ([]*task.Task, error)`
Returns tasks matching `filter`, ordered by `created_at DESC`. See [TaskFilter](#taskfilter).

#### `(*DB) ListSubtasks(parentID string) ([]*task.Task, error)`
Returns all tasks whose `parent_task_id` equals `parentID`, ordered by `created_at ASC`.

#### `(*DB) UpdateTask(id string, u TaskUpdate) error`
Replaces editable fields (`Name`, `Description`, `Config`, `Priority`, `TimeoutNS`, `Retry`, `Tags`, `DependsOn`) and resets `state` to `PENDING`. Returns an error if the task does not exist.

#### `(*DB) DeleteTask(id string) error`
Deletes a task, all its descendant subtasks (via recursive CTE), and all their execution records in a single transaction. Returns an error if the task does not exist.

---

### State Management

#### `(*DB) UpdateTaskState(id string, newState task.State) error`
Atomically updates a task's state inside a transaction. Calls `task.ValidTransition` to reject illegal moves before applying the change.

#### `(*DB) RejectTask(id, comment string) error`
Sets the task's state to `PENDING` and stores `comment` in `rejection_comment`. Does not go through the state-machine validator (direct update).

#### `(*DB) ResetTaskForRetry(id string) (*task.Task, error)`
Validates that the current state can transition to `QUEUED`, clears `Agent.Type` and `Agent.Model` so the task can be re-classified, sets `state = QUEUED`, and persists the changes in a transaction. Returns the updated task.

#### `(*DB) UpdateTaskQuestion(taskID, questionJSON string) error`
Stores a JSON-encoded agent question on the task. Pass an empty string to clear the question after it has been answered.

---

### Execution CRUD

#### `(*DB) CreateExecution(e *Execution) error`
Inserts a new execution record.

#### `(*DB) GetExecution(id string) (*Execution, error)`
Retrieves a single execution by ID.

#### `(*DB) ListExecutions(taskID string) ([]*Execution, error)`
Returns all executions for a task, ordered by `start_time DESC`.

#### `(*DB) GetLatestExecution(taskID string) (*Execution, error)`
Returns the most recent execution for a task.

#### `(*DB) UpdateExecution(e *Execution) error`
Updates a completed execution record (end time, exit code, status, cost, paths, session ID).

#### `(*DB) ListRecentExecutions(since time.Time, limit int, taskID string) ([]*RecentExecution, error)`
Returns executions since `since`, joined with the task name, ordered by `start_time DESC`. If `taskID` is non-empty only executions for that task are included. Returns `RecentExecution` values (see below).

---

## `TaskFilter`

```go
type TaskFilter struct {
    State task.State
    Limit int
}
```

| Field   | Type         | Description                                           |
|---------|--------------|-------------------------------------------------------|
| `State` | `task.State` | If non-empty, only tasks in this state are returned.  |
| `Limit` | `int`        | Maximum number of results. `0` means no limit.        |

---

## `Execution` Struct

```go
type Execution struct {
    ID              string
    TaskID          string
    StartTime       time.Time
    EndTime         time.Time
    ExitCode        int
    Status          string
    StdoutPath      string
    StderrPath      string
    ArtifactDir     string
    CostUSD         float64
    ErrorMsg        string
    SessionID       string   // persisted: claude --session-id for resume
    ResumeSessionID string   // in-memory only: set when creating a resume execution
    ResumeAnswer    string   // in-memory only: human answer forwarded to agent
}
```

| Field             | Persisted | Description                                                           |
|-------------------|-----------|-----------------------------------------------------------------------|
| `ID`              | Yes       | UUID; primary key.                                                    |
| `TaskID`          | Yes       | ID of the owning task.                                                |
| `StartTime`       | Yes       | When execution started (UTC).                                         |
| `EndTime`         | Yes       | When execution ended (UTC); zero while still running.                 |
| `ExitCode`        | Yes       | Process exit code (0 = success).                                      |
| `Status`          | Yes       | Status string at completion.                                          |
| `StdoutPath`      | Yes       | Absolute path to the stdout log file.                                 |
| `StderrPath`      | Yes       | Absolute path to the stderr log file.                                 |
| `ArtifactDir`     | Yes       | Directory containing output artifacts produced by the agent.         |
| `CostUSD`         | Yes       | Total API spend for this run in USD.                                  |
| `ErrorMsg`        | Yes       | Error description if the run failed.                                  |
| `SessionID`       | Yes       | Claude session ID; stored to allow resuming an interrupted session.   |
| `ResumeSessionID` | No        | Session to resume; set in-memory when creating a resume execution.    |
| `ResumeAnswer`    | No        | Human answer to a blocked question; forwarded to the agent in-memory. |

---

## File Layout

All runtime data lives under `~/.claudomator/`:

```
~/.claudomator/
├── claudomator.db          # SQLite database (WAL mode)
└── executions/             # Execution log files
    ├── <execution-id>.stdout
    └── <execution-id>.stderr
```

The paths are configured by `internal/config`:

| Config field | Value                                |
|--------------|--------------------------------------|
| `DBPath`     | `~/.claudomator/claudomator.db`      |
| `LogDir`     | `~/.claudomator/executions/`         |

Both directories are created automatically on first run if they do not exist.