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