1 files changed, 92 insertions, 7 deletions

diff --git a/makima/src/db/repository.rs b/makima/src/db/repository.rs
index b41c74c..f91bfaa 100644
--- a/makima/src/db/repository.rs
+++ b/makima/src/db/repository.rs
@@ -1189,6 +1189,86 @@ pub async fn list_tasks_for_owner(
     .await
 }
 
+// =============================================================================
+// Tmp directive — per-owner scratchpad
+// =============================================================================
+
+/// Get the owner's tmp directive, creating it on the fly if absent. Idempotent
+/// thanks to the partial unique index on (owner_id) WHERE is_tmp.
+///
+/// We try an INSERT first with ON CONFLICT DO NOTHING; if a row was inserted
+/// it's returned, otherwise we fall back to a SELECT for the row some other
+/// request just created (or one that already existed).
+pub async fn get_or_create_tmp_directive(
+    pool: &PgPool,
+    owner_id: Uuid,
+) -> Result<Directive, sqlx::Error> {
+    // Try insert first. RETURNING fires only if a row was actually written;
+    // if the partial unique index trips (a tmp directive already exists)
+    // we get None and fall through to the SELECT.
+    let inserted = sqlx::query_as::<_, Directive>(
+        r#"
+        INSERT INTO directives
+            (owner_id, title, goal, status, reconcile_mode, is_tmp)
+        VALUES
+            ($1, 'tmp', '', 'idle', 'auto', true)
+        ON CONFLICT DO NOTHING
+        RETURNING *
+        "#,
+    )
+    .bind(owner_id)
+    .fetch_optional(pool)
+    .await?;
+
+    if let Some(d) = inserted {
+        return Ok(d);
+    }
+
+    // Pre-existing or just-created-by-someone-else: fetch.
+    sqlx::query_as::<_, Directive>(
+        r#"SELECT * FROM directives WHERE owner_id = $1 AND is_tmp = true LIMIT 1"#,
+    )
+    .bind(owner_id)
+    .fetch_one(pool)
+    .await
+}
+
+/// Find every tmp directive (across owners). Used by the 30-day expiry
+/// sweep — we need to know which directives are scratchpads so we know
+/// which tasks to age out.
+pub async fn list_all_tmp_directives(
+    pool: &PgPool,
+) -> Result<Vec<Directive>, sqlx::Error> {
+    sqlx::query_as::<_, Directive>(
+        r#"SELECT * FROM directives WHERE is_tmp = true"#,
+    )
+    .fetch_all(pool)
+    .await
+}
+
+/// Delete tasks attached to a tmp directive that are older than 30 days.
+/// Returns the number of rows deleted (informational; we log it).
+///
+/// We only sweep top-level tasks (parent_task_id IS NULL) — subtasks die
+/// when their parent dies via the FK cascade.
+pub async fn delete_expired_tmp_tasks(
+    pool: &PgPool,
+    tmp_directive_id: Uuid,
+) -> Result<u64, sqlx::Error> {
+    let result = sqlx::query(
+        r#"
+        DELETE FROM tasks
+         WHERE directive_id = $1
+           AND parent_task_id IS NULL
+           AND created_at < NOW() - INTERVAL '30 days'
+        "#,
+    )
+    .bind(tmp_directive_id)
+    .execute(pool)
+    .await?;
+    Ok(result.rows_affected())
+}
+
 /// List ephemeral tasks attached to a directive — tasks with `directive_id`
 /// set but no `directive_step_id`. These are the "spinoff" tasks the user
 /// created via the directive folder context menu, distinct from
@@ -1223,14 +1303,15 @@ pub async fn list_ephemeral_directive_tasks_for_owner(
     .await
 }
 
-/// List "orphan" top-level tasks for an owner — tasks that are NOT attached
-/// to a directive and NOT a subtask of another task. These surface in the
-/// document-mode sidebar under a top-level `tmp/` folder. Hidden tasks
-/// excluded.
-pub async fn list_orphan_tasks_for_owner(
+/// List top-level tasks attached to the owner's tmp directive. These are
+/// the scratchpad / orphan tasks surfaced under the sidebar's `tmp/`
+/// folder. Auto-creates the tmp directive if it doesn't exist yet so the
+/// caller never has to handle "no tmp directive".
+pub async fn list_tmp_tasks_for_owner(
     pool: &PgPool,
     owner_id: Uuid,
 ) -> Result<Vec<TaskSummary>, sqlx::Error> {
+    let tmp = get_or_create_tmp_directive(pool, owner_id).await?;
     sqlx::query_as::<_, TaskSummary>(
         r#"
         SELECT
@@ -1243,13 +1324,14 @@ pub async fn list_orphan_tasks_for_owner(
         FROM tasks t
         LEFT JOIN contracts c ON t.contract_id = c.id
         WHERE t.owner_id = $1
+          AND t.directive_id = $2
           AND t.parent_task_id IS NULL
-          AND t.directive_id IS NULL
           AND COALESCE(t.hidden, false) = false
         ORDER BY t.priority DESC, t.created_at DESC
         "#,
     )
     .bind(owner_id)
+    .bind(tmp.id)
     .fetch_all(pool)
     .await
 }
@@ -5066,7 +5148,9 @@ pub async fn get_directive_with_steps_for_owner(
     }
 }
 
-/// List all directives for an owner with step counts.
+/// List all directives for an owner with step counts. Excludes the per-owner
+/// tmp directive (the scratchpad surface; surfaced via the sidebar's
+/// dedicated `tmp/` folder, not the regular directive list).
 pub async fn list_directives_for_owner(
     pool: &PgPool,
     owner_id: Uuid,
@@ -5093,6 +5177,7 @@ pub async fn list_directives_for_owner(
             WHERE directive_id = d.id
         ) s ON true
         WHERE d.owner_id = $1
+          AND d.is_tmp = false
         ORDER BY d.created_at DESC
         "#,
     )