姚章浩
84 lines
3.6 KiB
SQL
84 lines
3.6 KiB
SQL
-- 000003_message_audit: unified identity and message audit log
|
|
-- Adds channel_actors, channel_conversations, messages tables
|
|
-- and extends turns with agent_id, request_message_id, answer_message_id
|
|
|
|
-- ============================================================
|
|
-- 1. Channel Actors
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS channel_actors (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
external_actor_id TEXT NOT NULL,
|
|
actor_type TEXT NOT NULL CHECK (actor_type IN ('user','bot','support')),
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_key, channel, external_actor_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 2. Channel Conversations
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS channel_conversations (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
external_conversation_id TEXT NOT NULL,
|
|
conversation_type TEXT NOT NULL CHECK (conversation_type IN ('direct','group','support','web')),
|
|
title TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_key, channel, external_conversation_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 3. Messages
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
conversation_id TEXT NOT NULL REFERENCES channel_conversations(id),
|
|
actor_id TEXT REFERENCES channel_actors(id),
|
|
external_message_id TEXT NOT NULL DEFAULT '',
|
|
correlation_id TEXT NOT NULL,
|
|
direction TEXT NOT NULL CHECK (direction IN ('inbound','outbound','internal')),
|
|
role TEXT NOT NULL CHECK (role IN ('user','assistant','support','system')),
|
|
message_type TEXT NOT NULL CHECK (message_type IN ('text','image','rich_text','card','feedback')),
|
|
text_content TEXT NOT NULL DEFAULT '',
|
|
media_json JSONB NOT NULL DEFAULT '[]'::JSONB,
|
|
reply_to_message_id TEXT REFERENCES messages(id),
|
|
status TEXT NOT NULL CHECK (status IN ('received','processing','generated','sent','failed')),
|
|
actor_name_snapshot TEXT NOT NULL DEFAULT '',
|
|
provider_created_at TIMESTAMPTZ,
|
|
safe_metadata_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Partial unique index: only enforce uniqueness when external_message_id is non-empty
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_external_unique
|
|
ON messages (tenant_key, channel, external_message_id)
|
|
WHERE external_message_id != '';
|
|
|
|
-- Index for tenant + conversation time-series
|
|
CREATE INDEX IF NOT EXISTS idx_messages_tenant_conv_created
|
|
ON messages (tenant_key, conversation_id, created_at DESC);
|
|
|
|
-- Index for correlation lookups
|
|
CREATE INDEX IF NOT EXISTS idx_messages_correlation
|
|
ON messages (tenant_key, correlation_id);
|
|
|
|
-- ============================================================
|
|
-- 4. Extend turns table
|
|
-- ============================================================
|
|
ALTER TABLE turns
|
|
ADD COLUMN IF NOT EXISTS request_message_id TEXT REFERENCES messages(id);
|
|
|
|
ALTER TABLE turns
|
|
ADD COLUMN IF NOT EXISTS answer_message_id TEXT REFERENCES messages(id);
|
|
|
|
ALTER TABLE turns
|
|
ADD COLUMN IF NOT EXISTS agent_id TEXT NOT NULL DEFAULT ''; |