1 files changed, 71 insertions, 0 deletions
diff --git a/internal/storage/db.go b/internal/storage/db.go
index 1a0e74f..8f834b2 100644
--- a/internal/storage/db.go
+++ b/internal/storage/db.go
@@ -109,6 +109,16 @@ func (s *DB) migrate() error {
 		)`,
 		`CREATE INDEX IF NOT EXISTS idx_agent_events_agent ON agent_events(agent)`,
 		`CREATE INDEX IF NOT EXISTS idx_agent_events_timestamp ON agent_events(timestamp)`,
+		`CREATE TABLE IF NOT EXISTS projects (
+			id TEXT PRIMARY KEY,
+			name TEXT NOT NULL,
+			remote_url TEXT NOT NULL DEFAULT '',
+			local_path TEXT NOT NULL DEFAULT '',
+			type TEXT NOT NULL DEFAULT 'web',
+			deploy_script TEXT NOT NULL DEFAULT '',
+			created_at DATETIME NOT NULL,
+			updated_at DATETIME NOT NULL
+		)`,
 	}
 	for _, m := range migrations {
 		if _, err := s.db.Exec(m); err != nil {
@@ -1056,3 +1066,64 @@ func timeOrNull(t *time.Time) interface{} {
 	}
 	return t.UTC()
 }
+
+// CreateProject inserts a new project.
+func (s *DB) CreateProject(p *task.Project) error {
+	now := time.Now().UTC()
+	_, err := s.db.Exec(
+		`INSERT INTO projects (id, name, remote_url, local_path, type, deploy_script, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
+		p.ID, p.Name, p.RemoteURL, p.LocalPath, p.Type, p.DeployScript, now, now,
+	)
+	return err
+}
+
+// GetProject retrieves a project by ID.
+func (s *DB) GetProject(id string) (*task.Project, error) {
+	row := s.db.QueryRow(`SELECT id, name, remote_url, local_path, type, deploy_script FROM projects WHERE id = ?`, id)
+	p := &task.Project{}
+	if err := row.Scan(&p.ID, &p.Name, &p.RemoteURL, &p.LocalPath, &p.Type, &p.DeployScript); err != nil {
+		return nil, err
+	}
+	return p, nil
+}
+
+// ListProjects returns all projects.
+func (s *DB) ListProjects() ([]*task.Project, error) {
+	rows, err := s.db.Query(`SELECT id, name, remote_url, local_path, type, deploy_script FROM projects ORDER BY name`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var projects []*task.Project
+	for rows.Next() {
+		p := &task.Project{}
+		if err := rows.Scan(&p.ID, &p.Name, &p.RemoteURL, &p.LocalPath, &p.Type, &p.DeployScript); err != nil {
+			return nil, err
+		}
+		projects = append(projects, p)
+	}
+	return projects, rows.Err()
+}
+
+// UpdateProject updates an existing project.
+func (s *DB) UpdateProject(p *task.Project) error {
+	now := time.Now().UTC()
+	_, err := s.db.Exec(
+		`UPDATE projects SET name = ?, remote_url = ?, local_path = ?, type = ?, deploy_script = ?, updated_at = ? WHERE id = ?`,
+		p.Name, p.RemoteURL, p.LocalPath, p.Type, p.DeployScript, now, p.ID,
+	)
+	return err
+}
+
+// UpsertProject inserts or updates a project by ID (used for seeding).
+func (s *DB) UpsertProject(p *task.Project) error {
+	now := time.Now().UTC()
+	_, err := s.db.Exec(
+		`INSERT INTO projects (id, name, remote_url, local_path, type, deploy_script, created_at, updated_at)
+		 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
+		 ON CONFLICT(id) DO UPDATE SET name=excluded.name, remote_url=excluded.remote_url,
+		   local_path=excluded.local_path, type=excluded.type, deploy_script=excluded.deploy_script, updated_at=excluded.updated_at`,
+		p.ID, p.Name, p.RemoteURL, p.LocalPath, p.Type, p.DeployScript, now, now,
+	)
+	return err
+}