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