[Phase 3] Implement system analytics dashboard for admins

Description

Create an admin-only analytics dashboard showing system-wide metrics, usage trends, and health indicators.

Dependencies

• Issue #52 (Role-based access control)
• Issue #53 (Admin RPC functions)

Requirements

Page Location: apps/web/src/app/(authenticated)/admin/analytics/page.tsx

Metrics to Display:

1. System Overview (Last 30 Days):

• Total users (with growth %)
• Total files processed
• Total segments processed
• Total LLM cost
• Average cost per file

1. Usage Trends:

• LLM requests over time (chart)
• Cost over time (chart)
• File uploads over time (chart)

1. Performance Metrics:

• Cache hit rate (%)
• Average QE score
• Job success rate (%)
• Average processing time per file

1. Resource Usage:

• Current hour rate limit status (all users)
• Top 10 users by cost
• Most common error types

Server Component Pattern:

import { createRouteHandlerClient } from '@/lib/supabase';
import { requireAdmin } from '@/lib/auth-utils';

export default async function AnalyticsPage() {
  await requireAdmin();
  
  const supabase = createRouteHandlerClient();
  
  const [overview, trends, performance] = await Promise.all([
    supabase.rpc('get_system_overview_admin'),
    supabase.rpc('get_usage_trends_admin', { p_days: 30 }),
    supabase.rpc('get_performance_metrics_admin')
  ]);
  
  return (
    <div className="space-y-6">
      <SystemOverviewCards data={overview.data} />
      <UsageTrendsChart data={trends.data} />
      <PerformanceMetricsPanel data={performance.data} />
    </div>
  );
}

Required RPCs (add to migration 20251108000004):

CREATE OR REPLACE FUNCTION get_system_overview_admin()
RETURNS TABLE (
  total_users BIGINT,
  user_growth_pct NUMERIC,
  total_files BIGINT,
  total_segments BIGINT,
  total_cost_usd NUMERIC,
  avg_cost_per_file NUMERIC
) SECURITY DEFINER AS $$
BEGIN
  -- Verify admin
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;
  
  RETURN QUERY
  SELECT 
    COUNT(DISTINCT p.id)::BIGINT as total_users,
    -- Growth calculation (last 30 days vs previous 30)
    (
      (COUNT(DISTINCT p.id) FILTER (WHERE p.created_at >= NOW() - INTERVAL '30 days'))::NUMERIC /
      NULLIF(COUNT(DISTINCT p.id) FILTER (WHERE p.created_at >= NOW() - INTERVAL '60 days' AND p.created_at < NOW() - INTERVAL '30 days'), 0) - 1
    ) * 100 as user_growth_pct,
    COUNT(DISTINCT f.id)::BIGINT as total_files,
    COUNT(s.id)::BIGINT as total_segments,
    COALESCE(SUM(l.cost_usd), 0) as total_cost_usd,
    COALESCE(SUM(l.cost_usd) / NULLIF(COUNT(DISTINCT f.id), 0), 0) as avg_cost_per_file
  FROM profiles p
  LEFT JOIN files f ON f.created_by = p.id
  LEFT JOIN segments s ON s.file_id = f.id
  LEFT JOIN llm_usage l ON l.user_id = p.id AND l.created_at >= NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_performance_metrics_admin()
RETURNS TABLE (
  cache_hit_rate NUMERIC,
  avg_qe_score NUMERIC,
  job_success_rate NUMERIC,
  avg_processing_time_seconds NUMERIC
) SECURITY DEFINER AS $$
BEGIN
  -- Verify admin
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;
  
  RETURN QUERY
  SELECT 
    -- Cache hit rate from llm_usage
    (COUNT(*) FILTER (WHERE (l.details->>'cached')::boolean = true)::NUMERIC / NULLIF(COUNT(*), 0)) * 100 as cache_hit_rate,
    -- Average QE score from segments
    AVG((r.result->>'qe_score')::NUMERIC) as avg_qe_score,
    -- Job success rate
    (COUNT(*) FILTER (WHERE j.status = 'completed')::NUMERIC / NULLIF(COUNT(*), 0)) * 100 as job_success_rate,
    -- Average processing time
    AVG(EXTRACT(EPOCH FROM (j.completed_at - j.started_at))) as avg_processing_time_seconds
  FROM llm_usage l
  CROSS JOIN segments s
  LEFT JOIN results r ON r.segment_id = s.id
  CROSS JOIN jobs j
  WHERE l.created_at >= NOW() - INTERVAL '30 days'
    AND j.created_at >= NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;

UI Components Needed

1. SystemOverviewCards: 6 stat cards with growth indicators
2. UsageTrendsChart: Line chart using recharts or similar
3. PerformanceMetricsPanel: Grid of metric cards with color coding
4. TopUsersTable: Top 10 users by cost

Acceptance Criteria

• [ ] All system metrics display correctly
• [ ] Charts render with proper data
• [ ] Admin-only access enforced
• [ ] Performance metrics update based on 30-day window
• [ ] Color coding for health indicators (green/yellow/red)

Testing

• [ ] Verify calculations match database
• [ ] Test with empty database
• [ ] Test with large dataset (1000+ users)
• [ ] Verify admin access only

Phase

Phase 3: Admin Features