41 lines
2.8 KiB
SQL
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 $$;
|