feat: Phase 3 — stories data model, ValidStoryTransition, storage CRUD, API endpoints

- internal/task/story.go: Story struct, StoryState constants, ValidStoryTransition
- internal/task/task.go: add StoryID field
- internal/storage/db.go: stories table + story_id on tasks migrations; CreateStory,
  GetStory, ListStories, UpdateStoryStatus, ListTasksByStory; update all task
  SELECT/INSERT to include story_id; scanTask extended with sql.NullString for story_id;
  added modernc timestamp format to GetMaxUpdatedAt
- internal/storage/sqlite_cgo.go + sqlite_nocgo.go: build-tag based driver selection
  (mattn/go-sqlite3 with CGO, modernc.org/sqlite pure-Go fallback) so tests run
  without a C compiler
- internal/api/stories.go: GET/POST /api/stories, GET /api/stories/{id},
  GET/POST /api/stories/{id}/tasks (auto-wires depends_on chain),
  PUT /api/stories/{id}/status (validates transition)
- internal/api/server.go: register all story routes
- go.mod/go.sum: add modernc.org/sqlite pure-Go dependency

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

1 files changed, 95 insertions, 9 deletions

diff --git a/internal/storage/db.go b/internal/storage/db.go
index 8f834b2..24a6cd3 100644
--- a/internal/storage/db.go
+++ b/internal/storage/db.go
@@ -8,7 +8,6 @@ import (
 	"time"
 
 	"github.com/thepeterstone/claudomator/internal/task"
-	_ "github.com/mattn/go-sqlite3"
 )
 
 type DB struct {
@@ -119,6 +118,18 @@ func (s *DB) migrate() error {
 			created_at DATETIME NOT NULL,
 			updated_at DATETIME NOT NULL
 		)`,
+		`CREATE TABLE IF NOT EXISTS stories (
+			id TEXT PRIMARY KEY,
+			name TEXT NOT NULL,
+			project_id TEXT NOT NULL DEFAULT '',
+			branch_name TEXT NOT NULL DEFAULT '',
+			deploy_config TEXT NOT NULL DEFAULT '',
+			validation_json TEXT NOT NULL DEFAULT '',
+			status TEXT NOT NULL DEFAULT 'PENDING',
+			created_at DATETIME NOT NULL,
+			updated_at DATETIME NOT NULL
+		)`,
+		`ALTER TABLE tasks ADD COLUMN story_id TEXT`,
 	}
 	for _, m := range migrations {
 		if _, err := s.db.Exec(m); err != nil {
@@ -156,24 +167,24 @@ func (s *DB) CreateTask(t *task.Task) error {
 	}
 
 	_, err = s.db.Exec(`
-		INSERT INTO tasks (id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at)
-		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+		INSERT INTO tasks (id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, story_id)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
 		t.ID, t.Name, t.Description, t.ElaborationInput, t.Project, t.RepositoryURL, string(configJSON), string(t.Priority),
 		t.Timeout.Duration.Nanoseconds(), string(retryJSON), string(tagsJSON), string(depsJSON),
-		t.ParentTaskID, string(t.State), t.CreatedAt.UTC(), t.UpdatedAt.UTC(),
+		t.ParentTaskID, string(t.State), t.CreatedAt.UTC(), t.UpdatedAt.UTC(), t.StoryID,
 	)
 	return err
 }
 
 // GetTask retrieves a task by ID.
 func (s *DB) GetTask(id string) (*task.Task, error) {
-	row := s.db.QueryRow(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json FROM tasks WHERE id = ?`, id)
+	row := s.db.QueryRow(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json, story_id FROM tasks WHERE id = ?`, id)
 	return scanTask(row)
 }
 
 // ListTasks returns tasks matching the given filter.
 func (s *DB) ListTasks(filter TaskFilter) ([]*task.Task, error) {
-	query := `SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json FROM tasks WHERE 1=1`
+	query := `SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json, story_id FROM tasks WHERE 1=1`
 	var args []interface{}
 
 	if filter.State != "" {
@@ -209,7 +220,7 @@ func (s *DB) ListTasks(filter TaskFilter) ([]*task.Task, error) {
 
 // ListSubtasks returns all tasks whose parent_task_id matches the given ID.
 func (s *DB) ListSubtasks(parentID string) ([]*task.Task, error) {
-	rows, err := s.db.Query(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json FROM tasks WHERE parent_task_id = ? ORDER BY created_at ASC`, parentID)
+	rows, err := s.db.Query(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json, story_id FROM tasks WHERE parent_task_id = ? ORDER BY created_at ASC`, parentID)
 	if err != nil {
 		return nil, err
 	}
@@ -262,7 +273,7 @@ func (s *DB) ResetTaskForRetry(id string) (*task.Task, error) {
 	}
 	defer tx.Rollback() //nolint:errcheck
 
-	t, err := scanTask(tx.QueryRow(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json FROM tasks WHERE id = ?`, id))
+	t, err := scanTask(tx.QueryRow(`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json, story_id FROM tasks WHERE id = ?`, id))
 	if err != nil {
 		if err == sql.ErrNoRows {
 			return nil, fmt.Errorf("task %q not found", id)
@@ -407,6 +418,8 @@ func (s *DB) GetMaxUpdatedAt() (time.Time, error) {
 		"2006-01-02T15:04:05Z07:00",
 		time.RFC3339,
 		"2006-01-02 15:04:05",
+		"2006-01-02 15:04:05 +0000 UTC",
+		"2006-01-02 15:04:05.999999999 +0000 UTC",
 	}
 	for _, f := range formats {
 		parsed, err := time.Parse(f, t.String)
@@ -849,8 +862,9 @@ func scanTask(row scanner) (*task.Task, error) {
 		questionJSON       sql.NullString
 		summary            sql.NullString
 		interactionsJSON   sql.NullString
+		storyID            sql.NullString
 	)
-	err := row.Scan(&t.ID, &t.Name, &t.Description, &elaborationInput, &project, &repositoryURL, &configJSON, &priority, &timeoutNS, &retryJSON, &tagsJSON, &depsJSON, &parentTaskID, &state, &t.CreatedAt, &t.UpdatedAt, &rejectionComment, &questionJSON, &summary, &interactionsJSON)
+	err := row.Scan(&t.ID, &t.Name, &t.Description, &elaborationInput, &project, &repositoryURL, &configJSON, &priority, &timeoutNS, &retryJSON, &tagsJSON, &depsJSON, &parentTaskID, &state, &t.CreatedAt, &t.UpdatedAt, &rejectionComment, &questionJSON, &summary, &interactionsJSON, &storyID)
 	t.ParentTaskID = parentTaskID.String
 	t.ElaborationInput = elaborationInput.String
 	t.Project = project.String
@@ -858,6 +872,7 @@ func scanTask(row scanner) (*task.Task, error) {
 	t.RejectionComment = rejectionComment.String
 	t.QuestionJSON = questionJSON.String
 	t.Summary = summary.String
+	t.StoryID = storyID.String
 	if err != nil {
 		return nil, err
 	}
@@ -1127,3 +1142,74 @@ func (s *DB) UpsertProject(p *task.Project) error {
 	)
 	return err
 }
+
+// CreateStory inserts a new story.
+func (s *DB) CreateStory(st *task.Story) error {
+	now := time.Now().UTC()
+	_, err := s.db.Exec(
+		`INSERT INTO stories (id, name, project_id, branch_name, deploy_config, validation_json, status, created_at, updated_at)
+         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+		st.ID, st.Name, st.ProjectID, st.BranchName, st.DeployConfig, st.ValidationJSON, string(st.Status), now, now,
+	)
+	return err
+}
+
+// GetStory retrieves a story by ID.
+func (s *DB) GetStory(id string) (*task.Story, error) {
+	row := s.db.QueryRow(`SELECT id, name, project_id, branch_name, deploy_config, validation_json, status, created_at, updated_at FROM stories WHERE id = ?`, id)
+	st := &task.Story{}
+	var status string
+	if err := row.Scan(&st.ID, &st.Name, &st.ProjectID, &st.BranchName, &st.DeployConfig, &st.ValidationJSON, &status, &st.CreatedAt, &st.UpdatedAt); err != nil {
+		return nil, err
+	}
+	st.Status = task.StoryState(status)
+	return st, nil
+}
+
+// ListStories returns all stories ordered by creation time descending.
+func (s *DB) ListStories() ([]*task.Story, error) {
+	rows, err := s.db.Query(`SELECT id, name, project_id, branch_name, deploy_config, validation_json, status, created_at, updated_at FROM stories ORDER BY created_at DESC`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var stories []*task.Story
+	for rows.Next() {
+		st := &task.Story{}
+		var status string
+		if err := rows.Scan(&st.ID, &st.Name, &st.ProjectID, &st.BranchName, &st.DeployConfig, &st.ValidationJSON, &status, &st.CreatedAt, &st.UpdatedAt); err != nil {
+			return nil, err
+		}
+		st.Status = task.StoryState(status)
+		stories = append(stories, st)
+	}
+	return stories, rows.Err()
+}
+
+// UpdateStoryStatus updates the status of a story.
+func (s *DB) UpdateStoryStatus(id string, status task.StoryState) error {
+	now := time.Now().UTC()
+	_, err := s.db.Exec(`UPDATE stories SET status = ?, updated_at = ? WHERE id = ?`, string(status), now, id)
+	return err
+}
+
+// ListTasksByStory returns all tasks associated with a story, ordered by creation time ascending.
+func (s *DB) ListTasksByStory(storyID string) ([]*task.Task, error) {
+	rows, err := s.db.Query(
+		`SELECT id, name, description, elaboration_input, project, repository_url, config_json, priority, timeout_ns, retry_json, tags_json, depends_on_json, parent_task_id, state, created_at, updated_at, rejection_comment, question_json, summary, interactions_json, story_id FROM tasks WHERE story_id = ? ORDER BY created_at ASC`,
+		storyID,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var tasks []*task.Task
+	for rows.Next() {
+		t, err := scanTaskRows(rows)
+		if err != nil {
+			return nil, err
+		}
+		tasks = append(tasks, t)
+	}
+	return tasks, rows.Err()
+}