1 files changed, 122 insertions, 0 deletions
diff --git a/makima/src/db/repository.rs b/makima/src/db/repository.rs
index 9a1bf2d..6656b79 100644
--- a/makima/src/db/repository.rs
+++ b/makima/src/db/repository.rs
@@ -4293,3 +4293,125 @@ pub async fn get_notification_count_for_task(
     .map_err(RepositoryError::Database)?;
     Ok(result.0)
 }
+
+/// Get all notifications for a red team task.
+pub async fn get_notifications_for_red_team(
+    pool: &PgPool,
+    red_team_task_id: Uuid,
+) -> Result<Vec<RedTeamNotification>, RepositoryError> {
+    sqlx::query_as::<_, RedTeamNotification>(
+        r#"
+        SELECT *
+        FROM red_team_notifications
+        WHERE red_team_task_id = $1
+        ORDER BY created_at DESC
+        "#,
+    )
+    .bind(red_team_task_id)
+    .fetch_all(pool)
+    .await
+    .map_err(RepositoryError::Database)
+}
+
+/// Get all notifications for a contract.
+pub async fn get_notifications_for_contract(
+    pool: &PgPool,
+    contract_id: Uuid,
+) -> Result<Vec<RedTeamNotification>, RepositoryError> {
+    sqlx::query_as::<_, RedTeamNotification>(
+        r#"
+        SELECT *
+        FROM red_team_notifications
+        WHERE contract_id = $1
+        ORDER BY created_at DESC
+        "#,
+    )
+    .bind(contract_id)
+    .fetch_all(pool)
+    .await
+    .map_err(RepositoryError::Database)
+}
+
+/// Get unacknowledged notifications for a contract.
+pub async fn get_unacknowledged_notifications(
+    pool: &PgPool,
+    contract_id: Uuid,
+) -> Result<Vec<RedTeamNotification>, RepositoryError> {
+    sqlx::query_as::<_, RedTeamNotification>(
+        r#"
+        SELECT *
+        FROM red_team_notifications
+        WHERE contract_id = $1 AND acknowledged = FALSE
+        ORDER BY created_at DESC
+        "#,
+    )
+    .bind(contract_id)
+    .fetch_all(pool)
+    .await
+    .map_err(RepositoryError::Database)
+}
+
+/// Acknowledge a red team notification.
+pub async fn acknowledge_notification(
+    pool: &PgPool,
+    notification_id: Uuid,
+) -> Result<RedTeamNotification, RepositoryError> {
+    sqlx::query_as::<_, RedTeamNotification>(
+        r#"
+        UPDATE red_team_notifications
+        SET acknowledged = TRUE, acknowledged_at = NOW()
+        WHERE id = $1
+        RETURNING *
+        "#,
+    )
+    .bind(notification_id)
+    .fetch_one(pool)
+    .await
+    .map_err(RepositoryError::Database)
+}
+
+/// Get active work tasks in a contract that can be monitored by the red team.
+/// Returns tasks that are not supervisors, not red team tasks, and not completed/cancelled/failed.
+pub async fn get_active_work_tasks_for_contract(
+    pool: &PgPool,
+    contract_id: Uuid,
+    owner_id: Uuid,
+) -> Result<Vec<Task>, RepositoryError> {
+    sqlx::query_as::<_, Task>(
+        r#"
+        SELECT
+            t.id, t.owner_id, t.contract_id, t.name, t.description, t.plan, t.result,
+            t.status, t.daemon_id, t.parent_task_id, t.progress_summary, t.retry_count,
+            t.worktree_path, t.tool_key, t.created_at, t.updated_at, t.started_at,
+            t.version, t.is_supervisor, COALESCE(t.is_red_team, false) as is_red_team,
+            t.repository_url, t.base_branch, t.target_branch, t.merge_mode,
+            t.completion_action, t.completion_action_result, t.total_cost_usd,
+            t.total_turns, t.continue_from_task_id, t.queued_at, t.depth
+        FROM tasks t
+        WHERE t.contract_id = $1
+          AND t.owner_id = $2
+          AND t.is_supervisor = FALSE
+          AND COALESCE(t.is_red_team, false) = FALSE
+          AND t.status NOT IN ('done', 'cancelled', 'failed')
+        ORDER BY t.created_at DESC
+        "#,
+    )
+    .bind(contract_id)
+    .bind(owner_id)
+    .fetch_all(pool)
+    .await
+    .map_err(RepositoryError::Database)
+}
+
+/// Check if a task is a red team task.
+pub async fn is_red_team_task(pool: &PgPool, task_id: Uuid) -> Result<bool, RepositoryError> {
+    let result: Option<(bool,)> = sqlx::query_as(
+        "SELECT COALESCE(is_red_team, false) FROM tasks WHERE id = $1",
+    )
+    .bind(task_id)
+    .fetch_optional(pool)
+    .await
+    .map_err(RepositoryError::Database)?;
+
+    Ok(result.map(|(v,)| v).unwrap_or(false))
+}