1-- name: CreateConversation :one
  2INSERT INTO conversations (conversation_id, slug, user_initiated, cwd, model)
  3VALUES (?, ?, ?, ?, ?)
  4RETURNING *;
  5
  6-- name: GetConversation :one
  7SELECT * FROM conversations
  8WHERE conversation_id = ?;
  9
 10-- name: GetConversationBySlug :one
 11SELECT * FROM conversations
 12WHERE slug = ?;
 13
 14-- name: ListConversations :many
 15SELECT * FROM conversations
 16WHERE archived = FALSE AND parent_conversation_id IS NULL
 17ORDER BY updated_at DESC
 18LIMIT ? OFFSET ?;
 19
 20-- name: ListArchivedConversations :many
 21SELECT * FROM conversations
 22WHERE archived = TRUE
 23ORDER BY updated_at DESC
 24LIMIT ? OFFSET ?;
 25
 26-- name: SearchConversations :many
 27SELECT * FROM conversations
 28WHERE slug LIKE '%' || ? || '%' AND archived = FALSE AND parent_conversation_id IS NULL
 29ORDER BY updated_at DESC
 30LIMIT ? OFFSET ?;
 31
 32-- name: SearchConversationsWithMessages :many
 33-- Search conversations by slug OR message content (user messages and agent responses, not system prompts)
 34-- Includes both top-level conversations and subagent conversations
 35SELECT DISTINCT c.* FROM conversations c
 36LEFT JOIN messages m ON c.conversation_id = m.conversation_id AND m.type IN ('user', 'agent')
 37WHERE c.archived = FALSE
 38  AND (
 39    c.slug LIKE '%' || ? || '%'
 40    OR json_extract(m.user_data, '$.text') LIKE '%' || ? || '%'
 41    OR m.llm_data LIKE '%' || ? || '%'
 42  )
 43ORDER BY c.updated_at DESC
 44LIMIT ? OFFSET ?;
 45
 46-- name: SearchArchivedConversations :many
 47SELECT * FROM conversations
 48WHERE slug LIKE '%' || ? || '%' AND archived = TRUE
 49ORDER BY updated_at DESC
 50LIMIT ? OFFSET ?;
 51
 52-- name: UpdateConversationSlug :one
 53UPDATE conversations
 54SET slug = ?, updated_at = CURRENT_TIMESTAMP
 55WHERE conversation_id = ?
 56RETURNING *;
 57
 58-- name: UpdateConversationTimestamp :exec
 59UPDATE conversations
 60SET updated_at = CURRENT_TIMESTAMP
 61WHERE conversation_id = ?;
 62
 63-- name: DeleteConversation :exec
 64DELETE FROM conversations
 65WHERE conversation_id = ?;
 66
 67-- name: CountConversations :one
 68SELECT COUNT(*) FROM conversations WHERE archived = FALSE AND parent_conversation_id IS NULL;
 69
 70-- name: CountArchivedConversations :one
 71SELECT COUNT(*) FROM conversations WHERE archived = TRUE;
 72
 73-- name: ArchiveConversation :one
 74UPDATE conversations
 75SET archived = TRUE, updated_at = CURRENT_TIMESTAMP
 76WHERE conversation_id = ?
 77RETURNING *;
 78
 79-- name: UnarchiveConversation :one
 80UPDATE conversations
 81SET archived = FALSE, updated_at = CURRENT_TIMESTAMP
 82WHERE conversation_id = ?
 83RETURNING *;
 84
 85-- name: UpdateConversationCwd :one
 86UPDATE conversations
 87SET cwd = ?, updated_at = CURRENT_TIMESTAMP
 88WHERE conversation_id = ?
 89RETURNING *;
 90
 91
 92-- name: CreateSubagentConversation :one
 93INSERT INTO conversations (conversation_id, slug, user_initiated, cwd, parent_conversation_id)
 94VALUES (?, ?, FALSE, ?, ?)
 95RETURNING *;
 96
 97-- name: GetSubagents :many
 98SELECT * FROM conversations
 99WHERE parent_conversation_id = ?
100ORDER BY created_at ASC;
101
102-- name: GetConversationBySlugAndParent :one
103SELECT * FROM conversations
104WHERE slug = ? AND parent_conversation_id = ?;
105
106-- name: UpdateConversationModel :exec
107UPDATE conversations
108SET model = ?
109WHERE conversation_id = ? AND model IS NULL;