Files
Enterprise-Knowledge-Base/migrations/versioned/000064_message_audit.up.sql
2026-07-15 14:00:16 +08:00

41 lines
2.8 KiB
SQL

-- Migration: 000064_message_audit
-- Description: Persist per-message source metadata, delivery correlation, retrieval scope, and model token usage.
DO $$ BEGIN RAISE NOTICE '[Migration 000064] Adding message audit metadata'; END $$;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_platform VARCHAR(20) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_channel_id VARCHAR(36) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_message_id VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_delivery_id VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_user_id VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_chat_id VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_thread_id VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS searched_knowledge_base_ids JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS token_usage JSONB;
CREATE INDEX IF NOT EXISTS idx_messages_request_role
ON messages (request_id, role);
CREATE INDEX IF NOT EXISTS idx_messages_source_channel
ON messages (source_platform, source_channel_id)
WHERE source_platform != '';
CREATE INDEX IF NOT EXISTS idx_messages_source_message
ON messages (source_platform, source_message_id)
WHERE source_message_id != '';
CREATE INDEX IF NOT EXISTS idx_messages_source_delivery
ON messages (source_platform, source_delivery_id)
WHERE source_delivery_id != '';
CREATE INDEX IF NOT EXISTS idx_messages_source_user
ON messages (source_platform, source_user_id)
WHERE source_user_id != '';
COMMENT ON COLUMN messages.source_platform IS 'Concrete source platform such as dingtalk; channel remains im for compatibility';
COMMENT ON COLUMN messages.source_channel_id IS 'WeKnora im_channels.id that received the message';
COMMENT ON COLUMN messages.source_message_id IS 'External platform message identifier';
COMMENT ON COLUMN messages.source_delivery_id IS 'External delivery or card identifier used to correlate callbacks';
COMMENT ON COLUMN messages.source_user_id IS 'External platform user identifier for this individual message';
COMMENT ON COLUMN messages.source_chat_id IS 'External platform conversation or group identifier';
COMMENT ON COLUMN messages.source_thread_id IS 'External platform thread identifier when available';
COMMENT ON COLUMN messages.searched_knowledge_base_ids IS 'Knowledge-base search scope, retained even when retrieval has zero hits';
COMMENT ON COLUMN messages.token_usage IS 'Answer-generation token usage: prompt, completion, total, and cached tokens';
DO $$ BEGIN RAISE NOTICE '[Migration 000064] Message audit metadata ready'; END $$;