姚章浩
32 lines
1.6 KiB
SQL
32 lines
1.6 KiB
SQL
-- 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'; |