weichenandCursor
1abafa3176
Persist workflow_instance_id, stage_instance_id, scope_kind on chat messages and task runs; resolve scope from live instance for chat and document tasks so refresh can apply consistent stage authorization. Co-authored-by: Cursor <cursoragent@cursor.com>
80 lines
3.0 KiB
SQL
80 lines
3.0 KiB
SQL
-- +goose Up
|
|
-- 受控回填历史消息作用域（零正文删除）
|
|
|
|
-- 1. 任务消息：通过 task_runs 唯一匹配回填 workflow_stage
|
|
UPDATE chat_messages cm
|
|
SET
|
|
scope_kind = 'workflow_stage',
|
|
workflow_instance_id = tr.workflow_instance_id,
|
|
stage_instance_id = tr.stage_instance_id,
|
|
stage_key = tr.workflow_stage_key,
|
|
metadata = cm.metadata || jsonb_build_object(
|
|
'scope_kind', 'workflow_stage',
|
|
'workflow_instance_id', tr.workflow_instance_id,
|
|
'stage_instance_id', tr.stage_instance_id,
|
|
'stage_key', tr.workflow_stage_key
|
|
)
|
|
FROM task_runs tr
|
|
WHERE cm.scope_kind = 'legacy_unscoped'
|
|
AND cm.deleted_at IS NULL
|
|
AND cm.metadata->>'task_key' IS NOT NULL
|
|
AND cm.metadata->>'task_key' = tr.task_key
|
|
AND cm.session_id = tr.session_id
|
|
AND tr.workflow_instance_id IS NOT NULL
|
|
AND tr.stage_instance_id IS NOT NULL
|
|
AND tr.workflow_stage_key IS NOT NULL;
|
|
|
|
-- 2. 激活前 owner 私聊：项目有 live 实例且消息创建于激活之前
|
|
UPDATE chat_messages cm
|
|
SET
|
|
scope_kind = 'pre_activation',
|
|
workflow_instance_id = pwi.id,
|
|
metadata = cm.metadata || jsonb_build_object(
|
|
'scope_kind', 'pre_activation',
|
|
'workflow_instance_id', pwi.id
|
|
)
|
|
FROM chat_sessions cs
|
|
JOIN projects p ON p.id = cs.project_id
|
|
JOIN project_workflow_instances pwi ON pwi.project_id = p.id AND pwi.status = 'active'
|
|
WHERE cm.session_id = cs.session_id
|
|
AND cm.scope_kind = 'legacy_unscoped'
|
|
AND cm.deleted_at IS NULL
|
|
AND cm.metadata->>'task_key' IS NULL
|
|
AND pwi.activated_at IS NOT NULL
|
|
AND cm.created_at < pwi.activated_at
|
|
AND cs.user_id IS NOT NULL
|
|
AND cm.user_id = cs.user_id;
|
|
|
|
-- 3. 已有 metadata.stage_key 但缺 scope_kind 的普通聊天（live 实例存在）
|
|
UPDATE chat_messages
|
|
SET
|
|
scope_kind = 'workflow_stage',
|
|
stage_key = chat_messages.metadata->>'stage_key',
|
|
workflow_instance_id = src.workflow_instance_id,
|
|
stage_instance_id = src.stage_instance_id,
|
|
metadata = chat_messages.metadata || jsonb_build_object(
|
|
'scope_kind', 'workflow_stage',
|
|
'workflow_instance_id', src.workflow_instance_id,
|
|
'stage_instance_id', src.stage_instance_id
|
|
)
|
|
FROM (
|
|
SELECT cm.id AS message_id, pwi.id AS workflow_instance_id, psi.id AS stage_instance_id
|
|
FROM chat_messages cm
|
|
INNER JOIN chat_sessions cs ON cm.session_id = cs.session_id
|
|
INNER JOIN project_workflow_instances pwi ON pwi.project_id = cs.project_id AND pwi.status = 'active'
|
|
INNER JOIN project_stage_instances psi ON psi.workflow_instance_id = pwi.id
|
|
AND psi.stage_key = cm.metadata->>'stage_key'
|
|
WHERE cm.scope_kind = 'legacy_unscoped'
|
|
AND cm.deleted_at IS NULL
|
|
AND cm.metadata->>'stage_key' IS NOT NULL
|
|
AND cm.metadata->>'stage_key' <> ''
|
|
AND cm.metadata->>'task_key' IS NULL
|
|
) AS src
|
|
WHERE chat_messages.id = src.message_id;
|
|
|
|
-- +goose Down
|
|
-- 回填不可逆；Down 仅重置为 legacy_unscoped（不含正文删除）
|
|
UPDATE chat_messages
|
|
SET scope_kind = 'legacy_unscoped'
|
|
WHERE scope_kind IN ('workflow_stage', 'pre_activation');
|