1 files changed, 135 insertions, 0 deletions
diff --git a/makima/src/db/repository.rs b/makima/src/db/repository.rs
index b7c5af1..8055488 100644
--- a/makima/src/db/repository.rs
+++ b/makima/src/db/repository.rs
@@ -4402,3 +4402,138 @@ pub async fn get_notification_count_for_task(
     .map_err(RepositoryError::Database)?;
     Ok(result.0)
 }
+
+// =============================================================================
+// Supervisor Status API Helpers
+// =============================================================================
+
+/// Get supervisor status for a contract.
+/// Returns combined information from supervisor_states and tasks tables.
+pub async fn get_supervisor_status(
+    pool: &PgPool,
+    contract_id: Uuid,
+    owner_id: Uuid,
+) -> Result<Option<SupervisorStatusInfo>, sqlx::Error> {
+    // Query to get supervisor status by joining supervisor_states with tasks
+    sqlx::query_as::<_, SupervisorStatusInfo>(
+        r#"
+        SELECT
+            ss.task_id,
+            COALESCE(t.status, 'unknown') as supervisor_state,
+            ss.phase,
+            t.progress_summary as current_activity,
+            ss.pending_task_ids,
+            ss.last_activity as last_heartbeat,
+            t.status as task_status,
+            t.daemon_id IS NOT NULL as is_running
+        FROM supervisor_states ss
+        JOIN tasks t ON t.id = ss.task_id
+        WHERE ss.contract_id = $1
+          AND t.owner_id = $2
+        "#,
+    )
+    .bind(contract_id)
+    .bind(owner_id)
+    .fetch_optional(pool)
+    .await
+}
+
+/// Internal struct to hold supervisor status query result
+#[derive(Debug, Clone, sqlx::FromRow)]
+pub struct SupervisorStatusInfo {
+    pub task_id: Uuid,
+    pub supervisor_state: String,
+    pub phase: String,
+    pub current_activity: Option<String>,
+    #[sqlx(try_from = "Vec<Uuid>")]
+    pub pending_task_ids: Vec<Uuid>,
+    pub last_heartbeat: chrono::DateTime<chrono::Utc>,
+    pub task_status: String,
+    pub is_running: bool,
+}
+
+/// Get supervisor activity history from history_events table.
+/// This provides a timeline of supervisor activities that can serve as "heartbeats".
+pub async fn get_supervisor_activity_history(
+    pool: &PgPool,
+    contract_id: Uuid,
+    limit: i32,
+    offset: i32,
+) -> Result<Vec<SupervisorActivityEntry>, sqlx::Error> {
+    sqlx::query_as::<_, SupervisorActivityEntry>(
+        r#"
+        SELECT
+            created_at as timestamp,
+            COALESCE(event_subtype, 'activity') as state,
+            event_data->>'activity' as activity,
+            (event_data->>'progress')::INTEGER as progress,
+            COALESCE(phase, 'unknown') as phase,
+            CASE
+                WHEN event_data->'pending_task_ids' IS NOT NULL
+                THEN ARRAY(SELECT jsonb_array_elements_text(event_data->'pending_task_ids'))::UUID[]
+                ELSE ARRAY[]::UUID[]
+            END as pending_task_ids
+        FROM history_events
+        WHERE contract_id = $1
+          AND event_type IN ('supervisor', 'phase', 'task')
+        ORDER BY created_at DESC
+        LIMIT $2 OFFSET $3
+        "#,
+    )
+    .bind(contract_id)
+    .bind(limit)
+    .bind(offset)
+    .fetch_all(pool)
+    .await
+}
+
+/// Internal struct to hold supervisor activity entry
+#[derive(Debug, Clone, sqlx::FromRow)]
+pub struct SupervisorActivityEntry {
+    pub timestamp: chrono::DateTime<chrono::Utc>,
+    pub state: String,
+    pub activity: Option<String>,
+    pub progress: Option<i32>,
+    pub phase: String,
+    #[sqlx(try_from = "Vec<Uuid>")]
+    pub pending_task_ids: Vec<Uuid>,
+}
+
+/// Count total supervisor activity history entries for a contract.
+pub async fn count_supervisor_activity_history(
+    pool: &PgPool,
+    contract_id: Uuid,
+) -> Result<i64, sqlx::Error> {
+    let result: (i64,) = sqlx::query_as(
+        r#"
+        SELECT COUNT(*)
+        FROM history_events
+        WHERE contract_id = $1
+          AND event_type IN ('supervisor', 'phase', 'task')
+        "#,
+    )
+    .bind(contract_id)
+    .fetch_one(pool)
+    .await?;
+    Ok(result.0)
+}
+
+/// Update supervisor state last_activity timestamp.
+/// This acts as a "sync" operation to refresh the supervisor's heartbeat.
+pub async fn sync_supervisor_state(
+    pool: &PgPool,
+    contract_id: Uuid,
+) -> Result<Option<SupervisorState>, sqlx::Error> {
+    sqlx::query_as::<_, SupervisorState>(
+        r#"
+        UPDATE supervisor_states
+        SET last_activity = NOW(),
+            updated_at = NOW()
+        WHERE contract_id = $1
+        RETURNING *
+        "#,
+    )
+    .bind(contract_id)
+    .fetch_optional(pool)
+    .await
+}