-- 000005_answer_feedback: user feedback on answers via DingTalk card -- Each (tenant_key, channel, answer_message_id, actor_id) holds the current -- feedback state: up or down with optional reason_code for down. -- Depends on: 000004_delivery_audit (and its 000003 message-audit baseline). CREATE TABLE IF NOT EXISTS answer_feedback ( id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, channel TEXT NOT NULL, answer_message_id TEXT NOT NULL REFERENCES messages(id), turn_id TEXT NOT NULL REFERENCES turns(id), actor_id TEXT NOT NULL REFERENCES channel_actors(id), value TEXT NOT NULL CHECK (value IN ('up', 'down')), reason_code TEXT NOT NULL DEFAULT '' CHECK (reason_code IN ('', 'incorrect', 'not_resolved', 'irrelevant', 'unclear', 'outdated', 'other')), source TEXT NOT NULL CHECK (source IN ('dingtalk_card')), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (tenant_key, channel, answer_message_id, actor_id), CHECK (value != 'up' OR reason_code = '') ); -- Queries that filter by value or reason_code within a tenant use these indexes. CREATE INDEX IF NOT EXISTS idx_answer_feedback_tenant_value_updated ON answer_feedback (tenant_key, value, updated_at DESC); CREATE INDEX IF NOT EXISTS idx_answer_feedback_tenant_reason_updated ON answer_feedback (tenant_key, reason_code, updated_at DESC); -- A DingTalk outTrackId must identify exactly one delivered card answer. CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_card_provider_unique ON messages (tenant_key, channel, provider_message_id) WHERE provider_message_id != '' AND delivery_method = 'card';