2026-05-05 22:37:13 +02:00
|
|
|
-- updated_at is the retention clock used by DeleteOldChatDebugRuns.
|
|
|
|
|
-- Set it on every write to keep retention semantics correct.
|
2026-04-13 15:06:06 +02:00
|
|
|
-- name: InsertChatDebugRun :one
|
|
|
|
|
INSERT INTO chat_debug_runs (
|
|
|
|
|
chat_id,
|
|
|
|
|
root_chat_id,
|
|
|
|
|
parent_chat_id,
|
|
|
|
|
model_config_id,
|
|
|
|
|
trigger_message_id,
|
|
|
|
|
history_tip_message_id,
|
|
|
|
|
kind,
|
|
|
|
|
status,
|
|
|
|
|
provider,
|
|
|
|
|
model,
|
|
|
|
|
summary,
|
|
|
|
|
started_at,
|
|
|
|
|
updated_at,
|
|
|
|
|
finished_at
|
|
|
|
|
)
|
|
|
|
|
VALUES (
|
|
|
|
|
@chat_id::uuid,
|
|
|
|
|
sqlc.narg('root_chat_id')::uuid,
|
|
|
|
|
sqlc.narg('parent_chat_id')::uuid,
|
|
|
|
|
sqlc.narg('model_config_id')::uuid,
|
|
|
|
|
sqlc.narg('trigger_message_id')::bigint,
|
|
|
|
|
sqlc.narg('history_tip_message_id')::bigint,
|
|
|
|
|
@kind::text,
|
|
|
|
|
@status::text,
|
|
|
|
|
sqlc.narg('provider')::text,
|
|
|
|
|
sqlc.narg('model')::text,
|
|
|
|
|
COALESCE(sqlc.narg('summary')::jsonb, '{}'::jsonb),
|
|
|
|
|
COALESCE(sqlc.narg('started_at')::timestamptz, NOW()),
|
|
|
|
|
COALESCE(sqlc.narg('updated_at')::timestamptz, NOW()),
|
|
|
|
|
sqlc.narg('finished_at')::timestamptz
|
|
|
|
|
)
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: UpdateChatDebugRun :one
|
|
|
|
|
-- Uses COALESCE so that passing NULL from Go means "keep the
|
2026-04-17 16:27:53 +02:00
|
|
|
-- existing value." This is intentional: debug rows follow a
|
2026-04-13 15:06:06 +02:00
|
|
|
-- write-once-finalize pattern where fields are set at creation
|
2026-04-17 16:27:53 +02:00
|
|
|
-- or finalization and never cleared back to NULL. The @now
|
|
|
|
|
-- parameter keeps updated_at under the caller's clock.
|
2026-05-05 22:37:13 +02:00
|
|
|
-- updated_at is also the retention clock used by DeleteOldChatDebugRuns.
|
2026-04-17 16:27:53 +02:00
|
|
|
--
|
|
|
|
|
-- finished_at is enforced as write-once at the SQL level: once
|
|
|
|
|
-- populated it cannot be overwritten by a later call. Callers
|
|
|
|
|
-- that issue a summary or status refresh after the run has
|
|
|
|
|
-- already finalized therefore cannot corrupt the original
|
|
|
|
|
-- completion timestamp, which keeps duration and ordering
|
|
|
|
|
-- calculations stable regardless of how many times the row is
|
|
|
|
|
-- updated.
|
2026-04-13 15:06:06 +02:00
|
|
|
UPDATE chat_debug_runs
|
|
|
|
|
SET
|
|
|
|
|
root_chat_id = COALESCE(sqlc.narg('root_chat_id')::uuid, root_chat_id),
|
|
|
|
|
parent_chat_id = COALESCE(sqlc.narg('parent_chat_id')::uuid, parent_chat_id),
|
|
|
|
|
model_config_id = COALESCE(sqlc.narg('model_config_id')::uuid, model_config_id),
|
|
|
|
|
trigger_message_id = COALESCE(sqlc.narg('trigger_message_id')::bigint, trigger_message_id),
|
|
|
|
|
history_tip_message_id = COALESCE(sqlc.narg('history_tip_message_id')::bigint, history_tip_message_id),
|
|
|
|
|
status = COALESCE(sqlc.narg('status')::text, status),
|
|
|
|
|
provider = COALESCE(sqlc.narg('provider')::text, provider),
|
|
|
|
|
model = COALESCE(sqlc.narg('model')::text, model),
|
|
|
|
|
summary = COALESCE(sqlc.narg('summary')::jsonb, summary),
|
2026-04-17 16:27:53 +02:00
|
|
|
finished_at = COALESCE(finished_at, sqlc.narg('finished_at')::timestamptz),
|
|
|
|
|
updated_at = @now::timestamptz
|
2026-04-13 15:06:06 +02:00
|
|
|
WHERE id = @id::uuid
|
|
|
|
|
AND chat_id = @chat_id::uuid
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: InsertChatDebugStep :one
|
2026-04-17 16:27:53 +02:00
|
|
|
-- The CTE atomically locks the parent run via UPDATE, bumps its
|
2026-10-20 22:32:25 +02:00
|
|
|
-- updated_at, and enforces the finalization guard: if the run is already
|
2026-04-17 16:27:53 +02:00
|
|
|
-- finished, the UPDATE returns zero rows, the INSERT gets no source
|
|
|
|
|
-- rows, and sql.ErrNoRows is returned. The UPDATE also serializes
|
2026-10-20 22:54:18 +02:00
|
|
|
-- with a concurrent run finalization under READ COMMITTED isolation.
|
2026-10-21 10:23:28 +02:00
|
|
|
-- A zero @step_number numbers the step after the run's highest step.
|
|
|
|
|
-- The row lock does not make that number unique, because the statement
|
|
|
|
|
-- snapshot predates the lock wait. Callers hold LockIDChatDebugRunSteps
|
|
|
|
|
-- from an earlier statement of the same transaction, so the snapshot
|
|
|
|
|
-- includes every committed step.
|
2026-04-17 16:27:53 +02:00
|
|
|
WITH locked_run AS (
|
|
|
|
|
UPDATE chat_debug_runs
|
|
|
|
|
SET updated_at = COALESCE(sqlc.narg('updated_at')::timestamptz, NOW())
|
|
|
|
|
WHERE id = @run_id::uuid
|
|
|
|
|
AND chat_id = @chat_id::uuid
|
|
|
|
|
AND finished_at IS NULL
|
|
|
|
|
RETURNING chat_id
|
|
|
|
|
)
|
2026-04-13 15:06:06 +02:00
|
|
|
INSERT INTO chat_debug_steps (
|
|
|
|
|
run_id,
|
|
|
|
|
chat_id,
|
|
|
|
|
step_number,
|
|
|
|
|
operation,
|
|
|
|
|
status,
|
|
|
|
|
history_tip_message_id,
|
|
|
|
|
assistant_message_id,
|
|
|
|
|
normalized_request,
|
|
|
|
|
normalized_response,
|
|
|
|
|
usage,
|
|
|
|
|
attempts,
|
|
|
|
|
error,
|
|
|
|
|
metadata,
|
|
|
|
|
started_at,
|
|
|
|
|
updated_at,
|
|
|
|
|
finished_at
|
|
|
|
|
)
|
|
|
|
|
SELECT
|
|
|
|
|
@run_id::uuid,
|
2026-04-17 16:27:53 +02:00
|
|
|
locked_run.chat_id,
|
2026-10-21 10:23:28 +02:00
|
|
|
COALESCE(
|
|
|
|
|
NULLIF(@step_number::int, 0),
|
|
|
|
|
(SELECT COALESCE(MAX(s.step_number), 0) + 1 FROM chat_debug_steps s WHERE s.run_id = @run_id::uuid)
|
|
|
|
|
),
|
2026-04-13 15:06:06 +02:00
|
|
|
@operation::text,
|
|
|
|
|
@status::text,
|
|
|
|
|
sqlc.narg('history_tip_message_id')::bigint,
|
|
|
|
|
sqlc.narg('assistant_message_id')::bigint,
|
|
|
|
|
COALESCE(sqlc.narg('normalized_request')::jsonb, '{}'::jsonb),
|
|
|
|
|
sqlc.narg('normalized_response')::jsonb,
|
|
|
|
|
sqlc.narg('usage')::jsonb,
|
|
|
|
|
COALESCE(sqlc.narg('attempts')::jsonb, '[]'::jsonb),
|
|
|
|
|
sqlc.narg('error')::jsonb,
|
|
|
|
|
COALESCE(sqlc.narg('metadata')::jsonb, '{}'::jsonb),
|
|
|
|
|
COALESCE(sqlc.narg('started_at')::timestamptz, NOW()),
|
|
|
|
|
COALESCE(sqlc.narg('updated_at')::timestamptz, NOW()),
|
|
|
|
|
sqlc.narg('finished_at')::timestamptz
|
2026-04-17 16:27:53 +02:00
|
|
|
FROM locked_run
|
2026-04-13 15:06:06 +02:00
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: UpdateChatDebugStep :one
|
|
|
|
|
-- Uses COALESCE so that passing NULL from Go means "keep the
|
2026-04-17 16:27:53 +02:00
|
|
|
-- existing value." This is intentional: debug rows follow a
|
2026-04-13 15:06:06 +02:00
|
|
|
-- write-once-finalize pattern where fields are set at creation
|
2026-04-17 16:27:53 +02:00
|
|
|
-- or finalization and never cleared back to NULL. The @now
|
2026-10-20 22:54:18 +02:00
|
|
|
-- parameter keeps updated_at under the caller's clock.
|
2026-04-13 15:06:06 +02:00
|
|
|
UPDATE chat_debug_steps
|
|
|
|
|
SET
|
|
|
|
|
status = COALESCE(sqlc.narg('status')::text, status),
|
|
|
|
|
history_tip_message_id = COALESCE(sqlc.narg('history_tip_message_id')::bigint, history_tip_message_id),
|
|
|
|
|
assistant_message_id = COALESCE(sqlc.narg('assistant_message_id')::bigint, assistant_message_id),
|
|
|
|
|
normalized_request = COALESCE(sqlc.narg('normalized_request')::jsonb, normalized_request),
|
|
|
|
|
normalized_response = COALESCE(sqlc.narg('normalized_response')::jsonb, normalized_response),
|
|
|
|
|
usage = COALESCE(sqlc.narg('usage')::jsonb, usage),
|
|
|
|
|
attempts = COALESCE(sqlc.narg('attempts')::jsonb, attempts),
|
|
|
|
|
error = COALESCE(sqlc.narg('error')::jsonb, error),
|
|
|
|
|
metadata = COALESCE(sqlc.narg('metadata')::jsonb, metadata),
|
|
|
|
|
finished_at = COALESCE(sqlc.narg('finished_at')::timestamptz, finished_at),
|
2026-04-17 16:27:53 +02:00
|
|
|
updated_at = @now::timestamptz
|
2026-04-13 15:06:06 +02:00
|
|
|
WHERE id = @id::uuid
|
|
|
|
|
AND chat_id = @chat_id::uuid
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatDebugRunsByChatID :many
|
|
|
|
|
-- Returns the most recent debug runs for a chat, ordered newest-first.
|
|
|
|
|
-- Callers must supply an explicit limit to avoid unbounded result sets.
|
|
|
|
|
SELECT *
|
|
|
|
|
FROM chat_debug_runs
|
|
|
|
|
WHERE chat_id = @chat_id::uuid
|
|
|
|
|
ORDER BY started_at DESC, id DESC
|
|
|
|
|
LIMIT @limit_val::int;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatDebugRunByID :one
|
|
|
|
|
SELECT *
|
|
|
|
|
FROM chat_debug_runs
|
|
|
|
|
WHERE id = @id::uuid;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatDebugStepsByRunID :many
|
|
|
|
|
SELECT *
|
|
|
|
|
FROM chat_debug_steps
|
|
|
|
|
WHERE run_id = @run_id::uuid
|
|
|
|
|
ORDER BY step_number ASC, started_at ASC;
|
|
|
|
|
|
|
|
|
|
-- name: DeleteChatDebugDataByChatID :execrows
|
2026-04-20 12:27:16 +02:00
|
|
|
-- The started_before bound prevents retried cleanup from deleting
|
|
|
|
|
-- runs created by a replacement turn that races ahead of the retry
|
|
|
|
|
-- window (for example, after an unarchive races with a pending
|
|
|
|
|
-- archive-cleanup retry).
|
2026-04-13 15:06:06 +02:00
|
|
|
DELETE FROM chat_debug_runs
|
2026-04-20 12:27:16 +02:00
|
|
|
WHERE chat_id = @chat_id::uuid
|
|
|
|
|
AND started_at < @started_before::timestamptz;
|
2026-04-13 15:06:06 +02:00
|
|
|
|
|
|
|
|
-- name: DeleteChatDebugDataAfterMessageID :execrows
|
2026-04-20 12:27:16 +02:00
|
|
|
-- Deletes debug runs (and their cascaded steps) whose message IDs
|
|
|
|
|
-- exceed the cutoff. The started_before bound prevents retried
|
|
|
|
|
-- cleanup from deleting runs created by a replacement turn that
|
|
|
|
|
-- raced ahead of the retry window.
|
2026-04-13 15:06:06 +02:00
|
|
|
WITH affected_runs AS (
|
|
|
|
|
SELECT DISTINCT run.id
|
|
|
|
|
FROM chat_debug_runs run
|
|
|
|
|
WHERE run.chat_id = @chat_id::uuid
|
2026-04-20 12:27:16 +02:00
|
|
|
AND run.started_at < @started_before::timestamptz
|
2026-04-13 15:06:06 +02:00
|
|
|
AND (
|
|
|
|
|
run.history_tip_message_id > @message_id::bigint
|
|
|
|
|
OR run.trigger_message_id > @message_id::bigint
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
UNION
|
|
|
|
|
|
|
|
|
|
SELECT DISTINCT step.run_id AS id
|
|
|
|
|
FROM chat_debug_steps step
|
2026-04-20 12:27:16 +02:00
|
|
|
JOIN chat_debug_runs run ON run.id = step.run_id
|
|
|
|
|
AND run.chat_id = step.chat_id
|
2026-04-13 15:06:06 +02:00
|
|
|
WHERE step.chat_id = @chat_id::uuid
|
2026-04-20 12:27:16 +02:00
|
|
|
AND run.started_at < @started_before::timestamptz
|
2026-04-13 15:06:06 +02:00
|
|
|
AND (
|
|
|
|
|
step.assistant_message_id > @message_id::bigint
|
|
|
|
|
OR step.history_tip_message_id > @message_id::bigint
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM chat_debug_runs
|
|
|
|
|
WHERE chat_id = @chat_id::uuid
|
|
|
|
|
AND id IN (SELECT id FROM affected_runs);
|
|
|
|
|
|
2026-05-05 22:37:13 +02:00
|
|
|
-- updated_at is the retention clock, so the window starts after the run
|
|
|
|
|
-- stops being written to.
|
|
|
|
|
-- Intentionally no finished_at IS NOT NULL guard: abandoned in-flight rows
|
|
|
|
|
-- older than the cutoff are also purged.
|
|
|
|
|
-- name: DeleteOldChatDebugRuns :execrows
|
|
|
|
|
WITH deletable AS (
|
|
|
|
|
SELECT id, chat_id
|
|
|
|
|
FROM chat_debug_runs
|
|
|
|
|
WHERE updated_at < @before_time::timestamptz
|
|
|
|
|
ORDER BY updated_at ASC
|
|
|
|
|
LIMIT @limit_count::int
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM chat_debug_runs
|
|
|
|
|
USING deletable
|
|
|
|
|
WHERE chat_debug_runs.id = deletable.id
|
|
|
|
|
AND chat_debug_runs.chat_id = deletable.chat_id;
|