-- 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.last_reasoning_effort,

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.dynamic_tools,

updated_chats.organization_id,

updated_chats.plan_mode,

updated_chats.client_type,

updated_chats.last_turn_summary,

updated_chats.summary,

updated_chats.summary_generated_at,

updated_chats.snapshot_version,

updated_chats.history_version,

updated_chats.queue_version,

updated_chats.generation_attempt,

updated_chats.retry_state,

updated_chats.retry_state_version,

updated_chats.runner_id,

updated_chats.requires_action_deadline_at,

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,

updated_chats.context_aggregate_hash,

updated_chats.context_dirty_since,

updated_chats.context_dirty_resources,

updated_chats.context_error,

updated_chats.compaction_requested_at

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

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.last_reasoning_effort,

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.dynamic_tools,

updated_chats.organization_id,

updated_chats.plan_mode,

updated_chats.client_type,

updated_chats.last_turn_summary,

updated_chats.summary,

updated_chats.summary_generated_at,

updated_chats.snapshot_version,

updated_chats.history_version,

updated_chats.queue_version,

updated_chats.generation_attempt,

updated_chats.retry_state,

updated_chats.retry_state_version,

updated_chats.runner_id,

updated_chats.requires_action_deadline_at,

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,

updated_chats.context_aggregate_hash,

updated_chats.context_dirty_since,

updated_chats.context_dirty_resources,

updated_chats.context_error,

updated_chats.compaction_requested_at

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: BackfillChatMessagesSearchTsv :execrows

-- Backfills chat_messages.search_tsv for pending rows, newest first.

-- The WHERE clause must match the predicate of

-- idx_chat_messages_search_tsv_pending exactly so the partial index

-- serves this query.

WITH batch AS (

SELECT id FROM chat_messages

WHERE search_tsv IS NULL

AND deleted = false

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

AND role IN ('user', 'assistant')

ORDER BY id DESC

LIMIT @batch_size::int

)

UPDATE chat_messages cm

-- NULL means "pending", empty tsvector means "backfilled, no text".

SET search_tsv = COALESCE(

to_tsvector('simple', chat_message_search_text(cm.content)),

''::tsvector)

FROM batch WHERE cm.id = batch.id;

-- name: GetChatByID :one

SELECT *

FROM chats_expanded

WHERE id = @id::uuid;

-- name: GetChatFamilyIDsByRootID :many

-- Returns the chat IDs of every chat in a family (root + all children)

-- in deterministic order. The id parameter must be the root id; the

-- query does not walk up from a child.

SELECT id

FROM chats

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

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

-- 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

-- Ordered by id to match the @after_id cursor. created_at is the transaction

-- start time, so it can disagree with append order when a transaction takes the

-- chat row lock later than one that started after it.

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;

-- name: GetChatMessagesByRevisionForStream :many

-- Stream deltas and reset snapshots must use the same message order.

SELECT

*

FROM

chat_messages

WHERE

chat_id = @chat_id::uuid

AND revision > @after_revision::bigint

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

ORDER BY

id 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

-- The compaction boundary and final ordering must use the same key so tool

-- results remain after their assistant calls.

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

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

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

(

(NOT @owned_only::boolean AND NOT @shared_only::boolean)

OR (@owned_only::boolean AND chats_expanded.owner_id = @viewer_id::uuid)

OR (

@shared_only::boolean

AND chats_expanded.owner_id != @viewer_id::uuid

AND (

chats_expanded.user_acl ? (@shared_with_user_id::uuid)::text

OR chats_expanded.group_acl ?| @shared_with_group_ids::text[]

)

)

)

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

-- websearch_to_tsquery accepts quoted phrases, OR, and -negation;

-- the 'simple' config folds case and skips stemming.

AND CASE

WHEN @search::text != '' THEN (

-- Served by idx_chats_title_fts.

to_tsvector('simple', chats_expanded.title) @@ websearch_to_tsquery('simple', @search)

-- Served by idx_chat_diff_statuses_pr_title_fts.

OR EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND to_tsvector('simple', cds.pull_request_title) @@ websearch_to_tsquery('simple', @search)

)

-- The WHERE clause must repeat the predicate of the partial index

-- idx_chat_messages_search_tsv so the planner can use it. Additional

-- filters should still be fine.

OR EXISTS (

SELECT 1

FROM chat_messages cm

WHERE cm.chat_id = chats_expanded.id

AND cm.search_tsv IS NOT NULL

AND cm.deleted = false

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

AND cm.role IN ('user', 'assistant')

AND cm.search_tsv @@ websearch_to_tsquery('simple', @search)

)

-- Skip an explicit pr_number lookup unless the search is a valid bigint.

OR CASE

WHEN @search ~ '^[0-9]{1,18}$' THEN EXISTS (

SELECT 1

FROM chat_diff_statuses cds

WHERE cds.chat_id = chats_expanded.id

AND cds.pr_number IS NOT NULL

AND cds.pr_number = @search::bigint

)

ELSE false

END

)

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 (

id,

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 (

COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()),

@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.last_reasoning_effort,

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.dynamic_tools,

inserted_chat.organization_id,

inserted_chat.plan_mode,

inserted_chat.client_type,

inserted_chat.last_turn_summary,

inserted_chat.summary,

inserted_chat.summary_generated_at,

inserted_chat.snapshot_version,

inserted_chat.history_version,

inserted_chat.queue_version,

inserted_chat.generation_attempt,

inserted_chat.retry_state,

inserted_chat.retry_state_version,

inserted_chat.runner_id,

inserted_chat.requires_action_deadline_at,

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,

inserted_chat.context_aggregate_hash,

inserted_chat.context_dirty_since,

inserted_chat.context_dirty_resources,

inserted_chat.context_error,

inserted_chat.compaction_requested_at

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

-- Returns the inserted rows in input array order. Ids are allocated before the

-- insert and the k-th smallest is assigned to input index k, so callers may

-- index the result positionally.

WITH batch AS (

SELECT

(

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

) AS last_model_config_id,

(

SELECT NULLIF(val, '')::chat_reasoning_effort

FROM UNNEST(@reasoning_effort::text[])

WITH ORDINALITY AS t(val, ord)

WHERE val != ''

ORDER BY ord DESC

LIMIT 1

) AS last_reasoning_effort

),

updated_chat AS (

UPDATE

chats

SET

last_model_config_id = COALESCE(batch.last_model_config_id, chats.last_model_config_id),

last_reasoning_effort = COALESCE(batch.last_reasoning_effort, chats.last_reasoning_effort)

FROM batch

WHERE

chats.id = @chat_id::uuid

AND (

chats.last_model_config_id IS DISTINCT FROM COALESCE(batch.last_model_config_id, chats.last_model_config_id)

OR chats.last_reasoning_effort IS DISTINCT FROM COALESCE(batch.last_reasoning_effort, chats.last_reasoning_effort)

)

),

allocated AS MATERIALIZED (

-- Numbering the ids by value, rather than by the order nextval produced

-- them, is what makes ordinal k always the k-th smallest id. MATERIALIZED

-- is redundant while nextval is volatile, and pins that if it changes.

SELECT

id,

(ROW_NUMBER() OVER (ORDER BY id))::int AS ord

FROM (

SELECT nextval('chat_messages_id_seq') AS id

FROM generate_series(1, cardinality(@role::chat_message_role[]))

) s

),

inserted AS (

INSERT INTO chat_messages (

id,

chat_id,

created_by,

model_config_id,

reasoning_effort,

role,

content,

content_version,

visibility,

input_tokens,

output_tokens,

total_tokens,

reasoning_tokens,

cache_creation_tokens,

cache_read_tokens,

context_limit,

compressed,

runtime_ms

)

SELECT

allocated.id,

@chat_id::uuid,

NULLIF((@created_by::uuid[])[allocated.ord], '00000000-0000-0000-0000-000000000000'::uuid),

NULLIF((@model_config_id::uuid[])[allocated.ord], '00000000-0000-0000-0000-000000000000'::uuid),

NULLIF((@reasoning_effort::text[])[allocated.ord], '')::chat_reasoning_effort,

(@role::chat_message_role[])[allocated.ord],

(@content::text[])[allocated.ord]::jsonb,

(@content_version::smallint[])[allocated.ord],

(@visibility::chat_message_visibility[])[allocated.ord],

NULLIF((@input_tokens::bigint[])[allocated.ord], 0),

NULLIF((@output_tokens::bigint[])[allocated.ord], 0),

NULLIF((@total_tokens::bigint[])[allocated.ord], 0),

NULLIF((@reasoning_tokens::bigint[])[allocated.ord], 0),

NULLIF((@cache_creation_tokens::bigint[])[allocated.ord], 0),

NULLIF((@cache_read_tokens::bigint[])[allocated.ord], 0),

NULLIF((@context_limit::bigint[])[allocated.ord], 0),

(@compressed::boolean[])[allocated.ord],

NULLIF((@runtime_ms::bigint[])[allocated.ord], 0)

FROM allocated

RETURNING *

)

SELECT *

FROM inserted

ORDER BY id;

-- 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.last_reasoning_effort,

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.dynamic_tools,

updated_chat.organization_id,