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