姚章浩
49 lines
2.4 KiB
SQL
49 lines
2.4 KiB
SQL
-- 000007_coverage_decisions up migration
|
|
-- Creates coverage_decisions and support_cases tables for B3.1 persistence.
|
|
|
|
CREATE TABLE IF NOT EXISTS coverage_decisions (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
turn_id TEXT NOT NULL REFERENCES turns(id),
|
|
evaluator_version TEXT NOT NULL DEFAULT 'v1',
|
|
verdict TEXT NOT NULL CHECK (verdict IN ('covered', 'uncovered')),
|
|
reason_code TEXT NOT NULL CHECK (reason_code IN (
|
|
'covered_has_references',
|
|
'uncovered_no_references',
|
|
'uncovered_explicit_no_answer'
|
|
)),
|
|
reference_count INTEGER NOT NULL DEFAULT 0 CHECK (reference_count >= 0),
|
|
top_score DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_key, channel, turn_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_coverage_decisions_tenant_channel
|
|
ON coverage_decisions (tenant_key, channel, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_coverage_decisions_verdict
|
|
ON coverage_decisions (verdict) WHERE verdict = 'uncovered';
|
|
|
|
CREATE TABLE IF NOT EXISTS support_cases (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
turn_id TEXT NOT NULL REFERENCES turns(id),
|
|
coverage_decision_id TEXT NOT NULL REFERENCES coverage_decisions(id),
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'notifying', 'notification_failed', 'notified', 'resolved')),
|
|
notification_attempts INTEGER NOT NULL DEFAULT 0 CHECK (notification_attempts >= 0),
|
|
last_notification_failure_code TEXT NOT NULL DEFAULT '',
|
|
last_notification_at TIMESTAMPTZ,
|
|
notified_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_key, channel, turn_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_support_cases_notification_pending
|
|
ON support_cases (status, created_at, id) WHERE status IN ('pending', 'notification_failed');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_support_cases_notifying_stale
|
|
ON support_cases (status, last_notification_at) WHERE status = 'notifying'; |