supabase_analytics_tables.sql

-- =============================================================
-- Supabase Table Schemas for Analytics Storage
-- =============================================================
-- Run this SQL in the Supabase SQL Editor to mirror the SQLite
-- analytics schema (tool usage, red flags, RAG searches, queries)
-- =============================================================

CREATE TABLE IF NOT EXISTS tool_usage_events (
    id BIGSERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    user_id TEXT,
    tool_name TEXT NOT NULL,
    "timestamp" BIGINT NOT NULL,
    latency_ms INTEGER,
    tokens_used INTEGER,
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_tool_usage_tenant_timestamp
    ON tool_usage_events (tenant_id, "timestamp");


CREATE TABLE IF NOT EXISTS redflag_violations (
    id BIGSERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    user_id TEXT,
    rule_id TEXT NOT NULL,
    rule_pattern TEXT,
    severity TEXT NOT NULL,
    matched_text TEXT,
    confidence DOUBLE PRECISION,
    message_preview TEXT,
    "timestamp" BIGINT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_redflag_tenant_timestamp
    ON redflag_violations (tenant_id, "timestamp");


CREATE TABLE IF NOT EXISTS rag_search_events (
    id BIGSERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    query TEXT NOT NULL,
    hits_count INTEGER,
    avg_score DOUBLE PRECISION,
    top_score DOUBLE PRECISION,
    "timestamp" BIGINT NOT NULL,
    latency_ms INTEGER
);

CREATE INDEX IF NOT EXISTS idx_rag_search_tenant_timestamp
    ON rag_search_events (tenant_id, "timestamp");


CREATE TABLE IF NOT EXISTS agent_query_events (
    id BIGSERIAL PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    user_id TEXT,
    message_preview TEXT,
    intent TEXT,
    tools_used JSONB,
    total_tokens INTEGER,
    total_latency_ms INTEGER,
    success BOOLEAN DEFAULT TRUE,
    "timestamp" BIGINT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_agent_query_tenant_timestamp
    ON agent_query_events (tenant_id, "timestamp");


-- =============================================================
-- Optional: Enable Row Level Security and service-role policy
-- =============================================================
ALTER TABLE tool_usage_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE redflag_violations ENABLE ROW LEVEL SECURITY;
ALTER TABLE rag_search_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_query_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role can manage tool usage"
    ON tool_usage_events FOR ALL
    USING (true) WITH CHECK (true);

CREATE POLICY "Service role can manage red flags"
    ON redflag_violations FOR ALL
    USING (true) WITH CHECK (true);

CREATE POLICY "Service role can manage rag searches"
    ON rag_search_events FOR ALL
    USING (true) WITH CHECK (true);

CREATE POLICY "Service role can manage agent queries"
    ON agent_query_events FOR ALL
    USING (true) WITH CHECK (true);

-- =============================================================
-- After running this script restart your FastAPI/MCP services
-- so they detect the Supabase analytics backend.
-- =============================================================