/project

Add a Project

Parse project name and optional flags (--client, --status, --priority, --target).

If --client is specified, look up the contact:

SELECT id FROM contacts WHERE name LIKE '%client_name%';

Run both statements in a single sqlite3 call (so last_insert_rowid() works correctly):

INSERT INTO projects (name, description, client_id, status, priority, start_date, target_date)
VALUES (?, ?, ?, ?, ?, date('now'), ?);
INSERT INTO activity_log (entity_type, entity_id, action, details)
VALUES ('project', last_insert_rowid(), 'created', json_object('name', ?));

Confirm with project details. If client was linked, mention the connection.

List Projects

SELECT p.id, p.name, p.status, p.priority, p.target_date, c.name as client
FROM projects p LEFT JOIN contacts c ON p.client_id = c.id
WHERE p.status NOT IN ('completed', 'cancelled')
ORDER BY
  CASE p.priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END,
  p.updated_at DESC;

Include task counts per project:

SELECT project_id, status, COUNT(*) as count FROM tasks GROUP BY project_id, status;

Add a Task

INSERT INTO tasks (project_id, title, status, priority)
VALUES (?, ?, 'todo', ?);
INSERT INTO activity_log (entity_type, entity_id, action, details)
VALUES ('project', ?, 'task_added', json_object('task', ?));

Edit a Project

Update specified fields, set updated_at = datetime('now'), log the change.

Update Task Status

UPDATE tasks SET status = ?, updated_at = datetime('now'),
  completed_at = CASE WHEN ? = 'done' THEN datetime('now') ELSE NULL END
WHERE id = ?;