3 files changed, 164 insertions, 0 deletions
diff --git a/internal/store/native_tasks_test.go b/internal/store/native_tasks_test.go
index 6e93bca..0117bf5 100644
--- a/internal/store/native_tasks_test.go
+++ b/internal/store/native_tasks_test.go
@@ -46,6 +46,17 @@ func newNativeTasksTestStore(t *testing.T) *Store {
 	`); err != nil {
 		t.Fatal(err)
 	}
+	if _, err := db.Exec(`
+		CREATE TABLE projects (
+			id TEXT PRIMARY KEY,
+			name TEXT NOT NULL,
+			color TEXT DEFAULT '',
+			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+			archived BOOLEAN DEFAULT 0
+		)
+	`); err != nil {
+		t.Fatal(err)
+	}
 	if _, err := db.Exec(`INSERT INTO native_tasks (id, content) VALUES ('real-1', 'Real task')`); err != nil {
 		t.Fatal(err)
 	}
diff --git a/internal/store/projects.go b/internal/store/projects.go
new file mode 100644
index 0000000..a05656f
--- /dev/null
+++ b/internal/store/projects.go
@@ -0,0 +1,67 @@
+package store
+
+import (
+	"database/sql"
+
+	"task-dashboard/internal/models"
+)
+
+// CreateProject inserts a new project and returns it.
+func (s *Store) CreateProject(name, color string) (*models.Project, error) {
+	id := newTaskID()
+	if _, err := s.db.Exec(`INSERT INTO projects (id, name, color) VALUES (?, ?, ?)`, id, name, color); err != nil {
+		return nil, err
+	}
+	return s.GetProjectByID(id)
+}
+
+// GetProjects returns all non-archived projects, alphabetically by name.
+func (s *Store) GetProjects() ([]models.Project, error) {
+	rows, err := s.db.Query(`
+		SELECT id, name, color, created_at, archived FROM projects WHERE archived = 0 ORDER BY name ASC
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = rows.Close() }()
+
+	var projects []models.Project
+	for rows.Next() {
+		var p models.Project
+		if err := rows.Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived); err != nil {
+			return nil, err
+		}
+		projects = append(projects, p)
+	}
+	return projects, rows.Err()
+}
+
+// GetProjectByID returns a single project by id, or ErrNativeTaskNotFound.
+func (s *Store) GetProjectByID(id string) (*models.Project, error) {
+	var p models.Project
+	err := s.db.QueryRow(`
+		SELECT id, name, color, created_at, archived FROM projects WHERE id = ?
+	`, id).Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived)
+	if err == sql.ErrNoRows {
+		return nil, ErrNativeTaskNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &p, nil
+}
+
+// SetTaskProject sets or clears a task's project (projectID = "" clears
+// it). Returns ErrNativeTaskNotFound if id doesn't match any task row --
+// deliberately does NOT validate that projectID itself exists, mirroring
+// this codebase's existing tolerance for a stale/empty foreign reference
+// (e.g. RecurrenceSeriesID is never validated against another table either).
+func (s *Store) SetTaskProject(id, projectID string) error {
+	result, err := s.db.Exec(`
+		UPDATE native_tasks SET project_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
+	`, projectID, id)
+	if err != nil {
+		return err
+	}
+	return checkRowsAffected(result)
+}
diff --git a/internal/store/projects_test.go b/internal/store/projects_test.go
new file mode 100644
index 0000000..beb1aa9
--- /dev/null
+++ b/internal/store/projects_test.go
@@ -0,0 +1,86 @@
+package store
+
+import (
+	"errors"
+	"testing"
+)
+
+func TestCreateProject_ReturnsCreatedProject(t *testing.T) {
+	s := newNativeTasksTestStore(t)
+
+	project, err := s.CreateProject("Sailing prep", "#3B82F6")
+	if err != nil {
+		t.Fatalf("CreateProject: %v", err)
+	}
+	if project.ID == "" {
+		t.Error("expected a generated ID")
+	}
+	if project.Name != "Sailing prep" {
+		t.Errorf("Name = %q, want %q", project.Name, "Sailing prep")
+	}
+	if project.Color != "#3B82F6" {
+		t.Errorf("Color = %q, want %q", project.Color, "#3B82F6")
+	}
+	if project.Archived {
+		t.Error("expected a newly created project to not be archived")
+	}
+}
+
+func TestGetProjects_ExcludesArchived(t *testing.T) {
+	s := newNativeTasksTestStore(t)
+	active, err := s.CreateProject("Active project", "#111111")
+	if err != nil {
+		t.Fatal(err)
+	}
+	archived, err := s.CreateProject("Archived project", "#222222")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if _, err := s.db.Exec(`UPDATE projects SET archived = 1 WHERE id = ?`, archived.ID); err != nil {
+		t.Fatal(err)
+	}
+
+	projects, err := s.GetProjects()
+	if err != nil {
+		t.Fatalf("GetProjects: %v", err)
+	}
+	if len(projects) != 1 || projects[0].ID != active.ID {
+		t.Fatalf("expected only the active project, got %+v", projects)
+	}
+}
+
+func TestGetProjectByID_UnknownID_ReturnsErrNotFound(t *testing.T) {
+	s := newNativeTasksTestStore(t)
+	_, err := s.GetProjectByID("does-not-exist")
+	if !errors.Is(err, ErrNativeTaskNotFound) {
+		t.Fatalf("expected ErrNativeTaskNotFound, got %v", err)
+	}
+}
+
+func TestSetTaskProject_UpdatesProjectID(t *testing.T) {
+	s := newNativeTasksTestStore(t)
+	project, err := s.CreateProject("Test project", "#333333")
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	if err := s.SetTaskProject("real-1", project.ID); err != nil {
+		t.Fatalf("SetTaskProject: %v", err)
+	}
+
+	task, err := s.GetNativeTaskByID("real-1")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if task.ProjectID != project.ID {
+		t.Errorf("ProjectID = %q, want %q", task.ProjectID, project.ID)
+	}
+}
+
+func TestSetTaskProject_UnknownID_ReturnsErrNotFound(t *testing.T) {
+	s := newNativeTasksTestStore(t)
+	err := s.SetTaskProject("does-not-exist", "")
+	if !errors.Is(err, ErrNativeTaskNotFound) {
+		t.Fatalf("expected ErrNativeTaskNotFound, got %v", err)
+	}
+}