-- name: ArchiveChatByID :many

WITH updated_chats AS (

UPDATE chats

SET archived = true, pin_order = 0, updated_at = NOW()

WHERE id = @id::uuid OR root_chat_id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chats.id,

updated_chats.owner_id,

updated_chats.workspace_id,

updated_chats.title,

updated_chats.status,

updated_chats.worker_id,

updated_chats.started_at,

updated_chats.heartbeat_at,

updated_chats.created_at,

updated_chats.updated_at,

updated_chats.parent_chat_id,

updated_chats.root_chat_id,

updated_chats.last_model_config_id,

updated_chats.archived,

updated_chats.last_error,

updated_chats.mode,

updated_chats.mcp_server_ids,

updated_chats.labels,

updated_chats.build_id,

updated_chats.agent_id,

updated_chats.pin_order,

updated_chats.last_read_message_id,

updated_chats.last_injected_context,

updated_chats.dynamic_tools,

updated_chats.organization_id,

updated_chats.plan_mode,

updated_chats.client_type,

updated_chats.last_turn_summary,

COALESCE(root.user_acl, updated_chats.user_acl) AS user_acl,

COALESCE(root.group_acl, updated_chats.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

updated_chats

LEFT JOIN chats root ON root.id = COALESCE(updated_chats.root_chat_id, updated_chats.parent_chat_id)

JOIN visible_users owner ON owner.id = updated_chats.owner_id

)

SELECT *

FROM chats_expanded

ORDER BY (chats_expanded.id = @id::uuid) DESC, chats_expanded.created_at ASC, chats_expanded.id ASC;

-- name: UnarchiveChatByID :many

-- Unarchives a chat (and its children). Stale file references are

-- handled automatically by FK cascades on chat_file_links: when

-- dbpurge deletes a chat_files row, the corresponding

-- chat_file_links rows are cascade-deleted by PostgreSQL.

WITH updated_chats AS (

UPDATE chats SET

archived = false,

updated_at = NOW()

WHERE id = @id::uuid OR root_chat_id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chats.id,

updated_chats.owner_id,

updated_chats.workspace_id,

updated_chats.title,

updated_chats.status,

updated_chats.worker_id,

updated_chats.started_at,

updated_chats.heartbeat_at,

updated_chats.created_at,

updated_chats.updated_at,

updated_chats.parent_chat_id,

updated_chats.root_chat_id,

updated_chats.last_model_config_id,

updated_chats.archived,

updated_chats.last_error,

updated_chats.mode,

updated_chats.mcp_server_ids,

updated_chats.labels,

updated_chats.build_id,

updated_chats.agent_id,

updated_chats.pin_order,

updated_chats.last_read_message_id,

updated_chats.last_injected_context,

updated_chats.dynamic_tools,

updated_chats.organization_id,

updated_chats.plan_mode,

updated_chats.client_type,

updated_chats.last_turn_summary,

COALESCE(root.user_acl, updated_chats.user_acl) AS user_acl,

COALESCE(root.group_acl, updated_chats.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

updated_chats

LEFT JOIN chats root ON root.id = COALESCE(updated_chats.root_chat_id, updated_chats.parent_chat_id)

JOIN visible_users owner ON owner.id = updated_chats.owner_id

)

SELECT *

FROM chats_expanded

ORDER BY (chats_expanded.id = @id::uuid) DESC, chats_expanded.created_at ASC, chats_expanded.id ASC;

-- name: PinChatByID :exec

WITH target_chat AS (

SELECT

id,

owner_id

FROM

chats

WHERE

id = @id::uuid

),

-- Under READ COMMITTED, concurrent pin operations for the same

-- owner may momentarily produce duplicate pin_order values because

-- each CTE snapshot does not see the other's writes. The next

-- pin/unpin/reorder operation's ROW_NUMBER() self-heals the

-- sequence, so this is acceptable.

ranked AS (

SELECT

c.id,

ROW_NUMBER() OVER (ORDER BY c.pin_order ASC, c.id ASC) :: integer AS next_pin_order

FROM

chats c

JOIN

target_chat ON c.owner_id = target_chat.owner_id

WHERE

c.pin_order > 0

AND c.archived = FALSE

AND c.id <> target_chat.id

),

updates AS (

SELECT

ranked.id,

ranked.next_pin_order AS pin_order

FROM

ranked

UNION ALL

SELECT

target_chat.id,

COALESCE((

SELECT

MAX(ranked.next_pin_order)

FROM

ranked

), 0) + 1 AS pin_order

FROM

target_chat

)

UPDATE

chats c

SET

pin_order = updates.pin_order

FROM

updates

WHERE

c.id = updates.id;

-- name: UnpinChatByID :exec

WITH target_chat AS (

SELECT

id,

owner_id

FROM

chats

WHERE

id = @id::uuid

),

ranked AS (

SELECT

c.id,

ROW_NUMBER() OVER (ORDER BY c.pin_order ASC, c.id ASC) :: integer AS current_position

FROM

chats c

JOIN

target_chat ON c.owner_id = target_chat.owner_id

WHERE

c.pin_order > 0

AND c.archived = FALSE

),

target AS (

SELECT

ranked.id,

ranked.current_position

FROM

ranked

WHERE

ranked.id = @id::uuid

),

updates AS (

SELECT

ranked.id,

CASE

WHEN ranked.id = target.id THEN 0

WHEN ranked.current_position > target.current_position THEN ranked.current_position - 1

ELSE ranked.current_position

END AS pin_order

FROM

ranked

CROSS JOIN

target

)

UPDATE

chats c

SET

pin_order = updates.pin_order

FROM

updates

WHERE

c.id = updates.id;

-- name: UpdateChatPinOrder :exec

WITH target_chat AS (

SELECT

id,

owner_id

FROM

chats

WHERE

id = @id::uuid

),

ranked AS (

SELECT

c.id,

ROW_NUMBER() OVER (ORDER BY c.pin_order ASC, c.id ASC) :: integer AS current_position,

COUNT(*) OVER () :: integer AS pinned_count

FROM

chats c

JOIN

target_chat ON c.owner_id = target_chat.owner_id

WHERE

c.pin_order > 0

AND c.archived = FALSE

),

target AS (

SELECT

ranked.id,

ranked.current_position,

LEAST(GREATEST(@pin_order::integer, 1), ranked.pinned_count) AS desired_position

FROM

ranked

WHERE

ranked.id = @id::uuid

),

updates AS (

SELECT

ranked.id,

CASE

WHEN ranked.id = target.id THEN target.desired_position

WHEN target.desired_position < target.current_position

AND ranked.current_position >= target.desired_position

AND ranked.current_position < target.current_position THEN ranked.current_position + 1

WHEN target.desired_position > target.current_position

AND ranked.current_position > target.current_position

AND ranked.current_position <= target.desired_position THEN ranked.current_position - 1

ELSE ranked.current_position

END AS pin_order

FROM

ranked

CROSS JOIN

target

)

UPDATE

chats c

SET

pin_order = updates.pin_order

FROM

updates

WHERE

c.id = updates.id;

-- name: SoftDeleteChatMessagesAfterID :exec

UPDATE

chat_messages

SET

deleted = true

WHERE

chat_id = @chat_id::uuid

AND id > @after_id::bigint;

-- name: SoftDeleteChatMessageByID :exec

UPDATE

chat_messages

SET

deleted = true

WHERE

id = @id::bigint;

-- name: GetChatByID :one

SELECT *

FROM chats_expanded

WHERE id = @id::uuid;

-- name: GetChatACLByID :one

SELECT

user_acl AS users,

group_acl AS groups

FROM

chats

WHERE

id = @id::uuid;

-- name: UpdateChatACLByID :exec

UPDATE

chats

SET

user_acl = @user_acl,

group_acl = @group_acl

WHERE

id = @id::uuid;

-- name: GetChatMessageByID :one

SELECT

*

FROM

chat_messages

WHERE

id = @id::bigint

AND deleted = false;

-- name: GetChatMessagesByChatID :many

SELECT

*

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND id > @after_id::bigint

AND visibility IN ('user', 'both')

AND deleted = false

ORDER BY

created_at ASC;

-- name: GetChatMessagesByChatIDAscPaginated :many

SELECT

*

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND id > @after_id::bigint

AND visibility IN ('user', 'both')

AND deleted = false

ORDER BY

id ASC

LIMIT

COALESCE(NULLIF(@limit_val::int, 0), 50);

-- name: GetChatMessagesByChatIDDescPaginated :many

SELECT

*

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND CASE

WHEN @before_id::bigint > 0 THEN id < @before_id::bigint

ELSE true

END

AND CASE

WHEN @after_id::bigint > 0 THEN id > @after_id::bigint

ELSE true

END

AND visibility IN ('user', 'both')

AND deleted = false

ORDER BY

id DESC

LIMIT

COALESCE(NULLIF(@limit_val::int, 0), 50);

-- name: GetChatUserPromptsByChatID :many

-- Returns the concatenated text of each user-visible user prompt in a

-- chat, newest first. Used by the composer to populate the up/down

-- arrow prompt-history cycle. Non-text parts (tool calls, files,

-- attachments, ...) are excluded; messages whose text payload is

-- entirely whitespace are dropped so cycling never lands on a blank

-- entry. The jsonb_typeof guard skips legacy V0 rows whose content is

-- a scalar JSON string (predates migration 000434) so the lateral

-- jsonb_array_elements never raises "cannot extract elements from a

-- scalar". Backed by idx_chat_messages_user_prompts.

SELECT

cm.id,

string_agg(part->>'text', '' ORDER BY ordinality)::text AS text

FROM

chat_messages cm,

jsonb_array_elements(cm.content) WITH ORDINALITY AS t(part, ordinality)

WHERE

cm.chat_id = @chat_id::uuid

AND cm.role = 'user'

AND cm.deleted = false

AND cm.visibility IN ('user', 'both')

AND jsonb_typeof(cm.content) = 'array'

AND part->>'type' = 'text'

GROUP BY

cm.id

HAVING

string_agg(part->>'text', '') ~ '\S'

ORDER BY

cm.id DESC

LIMIT

COALESCE(NULLIF(@limit_val::int, 0), 500);

-- name: GetChatMessagesForPromptByChatID :many

WITH latest_compressed_summary AS (

SELECT

id

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND compressed = TRUE

AND deleted = false

AND visibility = 'model'

ORDER BY

created_at DESC,

id DESC

LIMIT

1

)

SELECT

*

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND visibility IN ('model', 'both')

AND deleted = false

AND (

(

role = 'system'

AND compressed = FALSE

)

OR (

compressed = FALSE

AND (

NOT EXISTS (

SELECT

1

FROM

latest_compressed_summary

)

OR id > (

SELECT

id

FROM

latest_compressed_summary

)

)

)

OR id = (

SELECT

id

FROM

latest_compressed_summary

)

)

ORDER BY

created_at ASC,

id ASC;

-- name: GetChats :many

WITH cursor_chat AS (

SELECT

pin_order,

updated_at,

id

FROM chats

WHERE id = @after_id

)

SELECT

sqlc.embed(chats_expanded),

EXISTS (

SELECT 1 FROM chat_messages cm

WHERE cm.chat_id = chats_expanded.id

AND cm.role = 'assistant'

AND cm.deleted = false

AND cm.id > COALESCE(chats_expanded.last_read_message_id, 0)

) AS has_unread

FROM

chats_expanded

WHERE

CASE

WHEN @owned_only::boolean THEN chats_expanded.owner_id = @viewer_id::uuid

ELSE true

END

AND CASE

WHEN @shared_only::boolean THEN chats_expanded.owner_id != @viewer_id::uuid

ELSE true

END

AND CASE

WHEN sqlc.narg('archived') :: boolean IS NULL THEN true

ELSE chats_expanded.archived = sqlc.narg('archived') :: boolean

END

AND CASE

-- Cursor pagination: the last element on a page acts as the cursor.

-- The 4-tuple matches the ORDER BY below. All columns sort DESC

-- (pin_order is negated so lower values sort first in DESC order),

-- which lets us use a single tuple < comparison.

WHEN @after_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN (

(CASE WHEN chats_expanded.pin_order > 0 THEN 1 ELSE 0 END, -chats_expanded.pin_order, chats_expanded.updated_at, chats_expanded.id) < (

SELECT

CASE WHEN cursor_chat.pin_order > 0 THEN 1 ELSE 0 END,

-cursor_chat.pin_order,

cursor_chat.updated_at,

cursor_chat.id

FROM

cursor_chat

)

)

ELSE true

END

AND CASE

WHEN sqlc.narg('label_filter')::jsonb IS NOT NULL THEN chats_expanded.labels @> sqlc.narg('label_filter')::jsonb

ELSE true

END

-- Match chats whose linked diff URL (e.g. a pull request URL)

-- equals the given value, case-insensitively. The URL may live on

-- a delegated sub-agent's diff status, so we surface the root chat

-- when any descendant matches.

AND CASE

WHEN sqlc.narg('diff_url')::text IS NOT NULL THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

JOIN chats c2 ON c2.id = cds.chat_id

WHERE cds.url IS NOT NULL

AND cds.url <> ''

AND LOWER(cds.url) = LOWER(sqlc.narg('diff_url')::text)

AND (c2.id = chats_expanded.id OR c2.root_chat_id = chats_expanded.id)

)

ELSE true

END

-- Filter by title substring (case-insensitive). Applied when the

-- caller provides a non-empty title_query.

AND CASE

WHEN @title_query :: text != '' THEN chats_expanded.title ILIKE '%' || @title_query || '%'

ELSE true

END

AND CASE

WHEN sqlc.narg('has_unread')::boolean IS NOT NULL THEN (

EXISTS (

SELECT 1 FROM chat_messages cm

WHERE cm.chat_id = chats_expanded.id

AND cm.role = 'assistant'

AND cm.deleted = false

AND cm.id > COALESCE(chats_expanded.last_read_message_id, 0)

)

) = sqlc.narg('has_unread')::boolean

ELSE true

END

-- Filter by pull request status. Unlike the diff_url filter above,

-- this intentionally checks only the root chat's own diff status.

-- Child chats share the same workspace and git branch as their

-- parent, so gitsync populates identical PR state on both; traversing

-- descendants would be redundant.

AND CASE

WHEN COALESCE(array_length(@pull_request_statuses::text[], 1), 0) > 0 THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND (

CASE

WHEN cds.pull_request_state = 'open' AND cds.pull_request_draft THEN 'draft'

WHEN cds.pull_request_state = 'open' THEN 'open'

ELSE cds.pull_request_state

END

) = ANY(@pull_request_statuses::text[])

)

ELSE true

END

-- Filter by PR number (exact match on chat's diff status).

AND CASE

WHEN @pr_number::int != 0 THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND cds.pr_number = @pr_number

)

ELSE true

END

-- Filter by repository (substring match on remote origin or PR URL).

AND CASE

WHEN @repo_query::text != '' THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND (

cds.git_remote_origin ILIKE '%' || @repo_query || '%'

OR cds.url ILIKE '%' || @repo_query || '%'

)

)

ELSE true

END

-- Filter by pull request title (case-insensitive substring).

AND CASE

WHEN @pr_title_query::text != '' THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND cds.pull_request_title ILIKE '%' || @pr_title_query || '%'

)

ELSE true

END

-- Paginate over root chats only. Children are fetched

-- separately via GetChildChatsByParentIDs and embedded under

-- each parent. Other callers that need the full set should

-- use a narrower query (e.g. GetChatsByWorkspaceIDs).

AND chats_expanded.parent_chat_id IS NULL

-- Authorize Filter clause will be injected below in GetAuthorizedChats

-- @authorize_filter

ORDER BY

-- Pinned chats (pin_order > 0) sort before unpinned ones. Within

-- pinned chats, lower pin_order values come first. The negation

-- trick (-pin_order) keeps all sort columns DESC so the cursor

-- tuple < comparison works with uniform direction.

CASE WHEN chats_expanded.pin_order > 0 THEN 1 ELSE 0 END DESC,

-chats_expanded.pin_order DESC,

chats_expanded.updated_at DESC,

chats_expanded.id DESC

OFFSET @offset_opt

LIMIT

-- The chat list is unbounded and expected to grow large.

-- Default to 50 to prevent accidental excessively large queries.

COALESCE(NULLIF(@limit_opt :: int, 0), 50);

-- name: GetChildChatsByParentIDs :many

-- Fetches child chats of the given parents, optionally filtered by

-- archive state (NULL = all, true/false = match). The archive

-- invariant (parent archived implies child archived) is enforced

-- at write time, not here.

SELECT

sqlc.embed(chats_expanded),

EXISTS (

SELECT 1 FROM chat_messages cm

WHERE cm.chat_id = chats_expanded.id

AND cm.role = 'assistant'

AND cm.deleted = false

AND cm.id > COALESCE(chats_expanded.last_read_message_id, 0)

) AS has_unread

FROM

chats_expanded

WHERE

chats_expanded.parent_chat_id = ANY(@parent_ids :: uuid[])

AND CASE

WHEN sqlc.narg('archived') :: boolean IS NULL THEN true

ELSE chats_expanded.archived = sqlc.narg('archived') :: boolean

END

ORDER BY

chats_expanded.created_at DESC,

chats_expanded.id DESC;

-- name: InsertChat :one

WITH inserted_chat AS (

INSERT INTO chats (

organization_id,

owner_id,

workspace_id,

build_id,

agent_id,

parent_chat_id,

root_chat_id,

last_model_config_id,

title,

mode,

plan_mode,

status,

mcp_server_ids,

labels,

dynamic_tools,

client_type

) VALUES (

@organization_id::uuid,

@owner_id::uuid,

sqlc.narg('workspace_id')::uuid,

sqlc.narg('build_id')::uuid,

sqlc.narg('agent_id')::uuid,

sqlc.narg('parent_chat_id')::uuid,

sqlc.narg('root_chat_id')::uuid,

@last_model_config_id::uuid,

@title::text,

sqlc.narg('mode')::chat_mode,

sqlc.narg('plan_mode')::chat_plan_mode,

@status::chat_status,

COALESCE(@mcp_server_ids::uuid[], '{}'::uuid[]),

COALESCE(sqlc.narg('labels')::jsonb, '{}'::jsonb),

sqlc.narg('dynamic_tools')::jsonb,

@client_type::chat_client_type

)

RETURNING *

),

chats_expanded AS (

SELECT

inserted_chat.id,

inserted_chat.owner_id,

inserted_chat.workspace_id,

inserted_chat.title,

inserted_chat.status,

inserted_chat.worker_id,

inserted_chat.started_at,

inserted_chat.heartbeat_at,

inserted_chat.created_at,

inserted_chat.updated_at,

inserted_chat.parent_chat_id,

inserted_chat.root_chat_id,

inserted_chat.last_model_config_id,

inserted_chat.archived,

inserted_chat.last_error,

inserted_chat.mode,

inserted_chat.mcp_server_ids,

inserted_chat.labels,

inserted_chat.build_id,

inserted_chat.agent_id,

inserted_chat.pin_order,

inserted_chat.last_read_message_id,

inserted_chat.last_injected_context,

inserted_chat.dynamic_tools,

inserted_chat.organization_id,

inserted_chat.plan_mode,

inserted_chat.client_type,

inserted_chat.last_turn_summary,

COALESCE(root.user_acl, inserted_chat.user_acl) AS user_acl,

COALESCE(root.group_acl, inserted_chat.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

inserted_chat

LEFT JOIN chats root ON root.id = COALESCE(inserted_chat.root_chat_id, inserted_chat.parent_chat_id)

JOIN visible_users owner ON owner.id = inserted_chat.owner_id

)

SELECT *

FROM chats_expanded;

-- name: InsertChatMessages :many

WITH updated_chat AS (

UPDATE

chats

SET

last_model_config_id = (

SELECT val

FROM UNNEST(@model_config_id::uuid[])

WITH ORDINALITY AS t(val, ord)

WHERE val != '00000000-0000-0000-0000-000000000000'::uuid

ORDER BY ord DESC

LIMIT 1

)

WHERE

id = @chat_id::uuid

AND EXISTS (

SELECT 1

FROM UNNEST(@model_config_id::uuid[])

WHERE unnest != '00000000-0000-0000-0000-000000000000'::uuid

)

AND chats.last_model_config_id IS DISTINCT FROM (

SELECT val

FROM UNNEST(@model_config_id::uuid[])

WITH ORDINALITY AS t(val, ord)

WHERE val != '00000000-0000-0000-0000-000000000000'::uuid

ORDER BY ord DESC

LIMIT 1

)

)

INSERT INTO chat_messages (

chat_id,

created_by,

model_config_id,

role,

content,

content_version,

visibility,

input_tokens,

output_tokens,

total_tokens,

reasoning_tokens,

cache_creation_tokens,

cache_read_tokens,

context_limit,

compressed,

total_cost_micros,

runtime_ms,

provider_response_id

)

SELECT

@chat_id::uuid,

NULLIF(UNNEST(@created_by::uuid[]), '00000000-0000-0000-0000-000000000000'::uuid),

NULLIF(UNNEST(@model_config_id::uuid[]), '00000000-0000-0000-0000-000000000000'::uuid),

UNNEST(@role::chat_message_role[]),

UNNEST(@content::text[])::jsonb,

UNNEST(@content_version::smallint[]),

UNNEST(@visibility::chat_message_visibility[]),

NULLIF(UNNEST(@input_tokens::bigint[]), 0),

NULLIF(UNNEST(@output_tokens::bigint[]), 0),

NULLIF(UNNEST(@total_tokens::bigint[]), 0),

NULLIF(UNNEST(@reasoning_tokens::bigint[]), 0),

NULLIF(UNNEST(@cache_creation_tokens::bigint[]), 0),

NULLIF(UNNEST(@cache_read_tokens::bigint[]), 0),

NULLIF(UNNEST(@context_limit::bigint[]), 0),

UNNEST(@compressed::boolean[]),

NULLIF(UNNEST(@total_cost_micros::bigint[]), 0),

NULLIF(UNNEST(@runtime_ms::bigint[]), 0),

NULLIF(UNNEST(@provider_response_id::text[]), '')

RETURNING

*;

-- name: UpdateChatMessageByID :one

UPDATE

chat_messages

SET

model_config_id = COALESCE(sqlc.narg('model_config_id')::uuid, model_config_id),

content = sqlc.narg('content')::jsonb

WHERE

id = @id::bigint

RETURNING

*;

-- name: UpdateChatByID :one

WITH updated_chat AS (

UPDATE

chats

SET

title = @title::text,

updated_at = NOW()

WHERE

id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chat.id,

updated_chat.owner_id,

updated_chat.workspace_id,

updated_chat.title,

updated_chat.status,

updated_chat.worker_id,

updated_chat.started_at,

updated_chat.heartbeat_at,

updated_chat.created_at,

updated_chat.updated_at,

updated_chat.parent_chat_id,

updated_chat.root_chat_id,

updated_chat.last_model_config_id,

updated_chat.archived,

updated_chat.last_error,

updated_chat.mode,

updated_chat.mcp_server_ids,

updated_chat.labels,

updated_chat.build_id,

updated_chat.agent_id,

updated_chat.pin_order,

updated_chat.last_read_message_id,

updated_chat.last_injected_context,

updated_chat.dynamic_tools,

updated_chat.organization_id,

updated_chat.plan_mode,

updated_chat.client_type,

updated_chat.last_turn_summary,

COALESCE(root.user_acl, updated_chat.user_acl) AS user_acl,

COALESCE(root.group_acl, updated_chat.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

updated_chat

LEFT JOIN chats root ON root.id = COALESCE(updated_chat.root_chat_id, updated_chat.parent_chat_id)

JOIN visible_users owner ON owner.id = updated_chat.owner_id

)

SELECT *

FROM chats_expanded;

-- name: UpdateChatTitleByID :one

WITH updated_chat AS (

UPDATE

chats

SET

-- NOTE: updated_at is intentionally NOT touched here to avoid

-- changing list ordering when a user renames an older chat

-- out-of-band.

title = @title::text

WHERE

id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chat.id,

updated_chat.owner_id,

updated_chat.workspace_id,

updated_chat.title,

updated_chat.status,

updated_chat.worker_id,

updated_chat.started_at,

updated_chat.heartbeat_at,

updated_chat.created_at,

updated_chat.updated_at,

updated_chat.parent_chat_id,

updated_chat.root_chat_id,

updated_chat.last_model_config_id,

updated_chat.archived,

updated_chat.last_error,

updated_chat.mode,

updated_chat.mcp_server_ids,

updated_chat.labels,

updated_chat.build_id,

updated_chat.agent_id,

updated_chat.pin_order,

updated_chat.last_read_message_id,

updated_chat.last_injected_context,

updated_chat.dynamic_tools,

updated_chat.organization_id,

updated_chat.plan_mode,

updated_chat.client_type,

updated_chat.last_turn_summary,

COALESCE(root.user_acl, updated_chat.user_acl) AS user_acl,

COALESCE(root.group_acl, updated_chat.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

updated_chat

LEFT JOIN chats root ON root.id = COALESCE(updated_chat.root_chat_id, updated_chat.parent_chat_id)

JOIN visible_users owner ON owner.id = updated_chat.owner_id

)

SELECT *

FROM chats_expanded;

-- name: UpdateChatPlanModeByID :one

WITH updated_chat AS (

UPDATE

chats

SET

-- NOTE: updated_at is intentionally NOT touched here to avoid changing list ordering.

plan_mode = sqlc.narg('plan_mode')::chat_plan_mode

WHERE

id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chat.id,

updated_chat.owner_id,

updated_chat.workspace_id,

updated_chat.title,

updated_chat.status,

updated_chat.worker_id,

updated_chat.started_at,

updated_chat.heartbeat_at,

updated_chat.created_at,

updated_chat.updated_at,

updated_chat.parent_chat_id,

updated_chat.root_chat_id,

updated_chat.last_model_config_id,

updated_chat.archived,

updated_chat.last_error,

updated_chat.mode,

updated_chat.mcp_server_ids,

updated_chat.labels,

updated_chat.build_id,

updated_chat.agent_id,

updated_chat.pin_order,

updated_chat.last_read_message_id,

updated_chat.last_injected_context,

updated_chat.dynamic_tools,

updated_chat.organization_id,

updated_chat.plan_mode,

updated_chat.client_type,

updated_chat.last_turn_summary,

COALESCE(root.user_acl, updated_chat.user_acl) AS user_acl,

COALESCE(root.group_acl, updated_chat.group_acl) AS group_acl,

owner.username AS owner_username,

owner.name AS owner_name

FROM

updated_chat

LEFT JOIN chats root ON root.id = COALESCE(updated_chat.root_chat_id, updated_chat.parent_chat_id)

JOIN visible_users owner ON owner.id = updated_chat.owner_id

)

SELECT *

FROM chats_expanded;

-- name: UpdateChatLastModelConfigByID :one

WITH updated_chat AS (

UPDATE

chats

SET

-- NOTE: updated_at is intentionally NOT touched here to avoid changing list ordering.

last_model_config_id = @last_model_config_id::uuid

WHERE

id = @id::uuid

RETURNING *

),

chats_expanded AS (

SELECT

updated_chat.id,

updated_chat.owner_id,

updated_chat.workspace_id,

updated_chat.title,

updated_chat.status,

updated_chat.worker_id,

updated_chat.started_at,

updated_chat.heartbeat_at,

updated_chat.created_at,

updated_chat.updated_at,

updated_chat.parent_chat_id,