ai-operator/migrations/003_audit_tables.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS ai_calls (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text NOT NULL UNIQUE,
    asterisk_channel_id text,
    route text NOT NULL DEFAULT 'unknown',
    caller_number_masked text,
    language text,
    region_code text,
    state text,
    started_at timestamptz NOT NULL,
    ended_at timestamptz,
    duration_ms bigint,
    end_reason text,
    handoff_status text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_calls_started_at ON ai_calls(started_at);
CREATE INDEX IF NOT EXISTS idx_ai_calls_route ON ai_calls(route);
CREATE INDEX IF NOT EXISTS idx_ai_calls_language_region ON ai_calls(language, region_code);
CREATE INDEX IF NOT EXISTS idx_ai_calls_state ON ai_calls(state);

CREATE TABLE IF NOT EXISTS ai_call_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text NOT NULL,
    event_type text NOT NULL,
    event_source text NOT NULL,
    state_before text,
    state_after text,
    severity text NOT NULL DEFAULT 'info',
    message text,
    reason_code text,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_call_events_call_id_created_at ON ai_call_events(call_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_call_events_type ON ai_call_events(event_type);
CREATE INDEX IF NOT EXISTS idx_ai_call_events_severity ON ai_call_events(severity);

CREATE TABLE IF NOT EXISTS ai_transcript_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text NOT NULL,
    speaker text NOT NULL CHECK (speaker IN ('user','assistant','system','tool')),
    event_type text NOT NULL,
    language text,
    text_redacted text,
    text_raw_encrypted text,
    text_hash text,
    char_count int NOT NULL DEFAULT 0,
    redaction_applied boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_transcript_events_call_id_created_at ON ai_transcript_events(call_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_transcript_events_speaker ON ai_transcript_events(speaker);
CREATE INDEX IF NOT EXISTS idx_ai_transcript_events_language ON ai_transcript_events(language);

CREATE TABLE IF NOT EXISTS ai_tool_audit (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text NOT NULL,
    tool_call_id text,
    tool_name text NOT NULL,
    state text,
    language text,
    region_code text,
    allowed boolean NOT NULL,
    denied boolean NOT NULL DEFAULT false,
    reason_code text,
    args_redacted jsonb NOT NULL DEFAULT '{}',
    result_redacted jsonb NOT NULL DEFAULT '{}',
    duration_ms bigint,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_tool_audit_call_id_created_at ON ai_tool_audit(call_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_tool_audit_tool_name ON ai_tool_audit(tool_name);
CREATE INDEX IF NOT EXISTS idx_ai_tool_audit_denied ON ai_tool_audit(denied);

CREATE TABLE IF NOT EXISTS ai_kb_audit (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text,
    query_redacted text NOT NULL,
    language text NOT NULL,
    region_code text NOT NULL,
    result_count int NOT NULL DEFAULT 0,
    top_score double precision,
    cross_language_fallback_used boolean NOT NULL DEFAULT false,
    citations_count int NOT NULL DEFAULT 0,
    no_answer boolean NOT NULL DEFAULT false,
    duration_ms bigint,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_kb_audit_call_id_created_at ON ai_kb_audit(call_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_kb_audit_language_region ON ai_kb_audit(language, region_code);
CREATE INDEX IF NOT EXISTS idx_ai_kb_audit_no_answer ON ai_kb_audit(no_answer);

CREATE TABLE IF NOT EXISTS ai_handoff_audit (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text NOT NULL,
    handoff_id text,
    mode text NOT NULL,
    status text NOT NULL,
    reason_code text,
    transfer_attempted boolean NOT NULL DEFAULT false,
    transfer_succeeded boolean NOT NULL DEFAULT false,
    target_redacted text,
    summary_redacted text,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS idx_ai_handoff_audit_call_id_created_at ON ai_handoff_audit(call_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_handoff_audit_status ON ai_handoff_audit(status);
CREATE INDEX IF NOT EXISTS idx_ai_handoff_audit_mode ON ai_handoff_audit(mode);

CREATE TABLE IF NOT EXISTS ai_provider_audit (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text,
    provider text NOT NULL,
    event_type text NOT NULL,
    severity text NOT NULL DEFAULT 'info',
    error_redacted text,
    input_audio_bytes bigint NOT NULL DEFAULT 0,
    output_audio_bytes bigint NOT NULL DEFAULT 0,
    events_received bigint NOT NULL DEFAULT 0,
    events_sent bigint NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS ai_media_audit (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    call_id text,
    event_type text NOT NULL,
    severity text NOT NULL DEFAULT 'info',
    codec text,
    inbound_frames bigint NOT NULL DEFAULT 0,
    inbound_bytes bigint NOT NULL DEFAULT 0,
    outbound_frames bigint NOT NULL DEFAULT 0,
    outbound_bytes bigint NOT NULL DEFAULT 0,
    xoff_count bigint NOT NULL DEFAULT 0,
    xon_count bigint NOT NULL DEFAULT 0,
    error_redacted text,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS ai_audit_retention_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    dry_run boolean NOT NULL DEFAULT true,
    status text NOT NULL,
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    calls_deleted int NOT NULL DEFAULT 0,
    events_deleted int NOT NULL DEFAULT 0,
    transcripts_deleted int NOT NULL DEFAULT 0,
    tool_audit_deleted int NOT NULL DEFAULT 0,
    error text,
    metadata jsonb NOT NULL DEFAULT '{}'
);